Oracle Database Cursor Usage Encyclopedia

SQL is the language used to access Oracle databases, and Pl/sql expands and strengthens the functionality of SQL, while introducing stronger program logic. Pl/sql supports DML commands and SQL transaction control statements. DDL is not supported in Pl/sql, which means that a table or any other object cannot be created in a PL/SQL program block. A better Pl/sql program is to execute DDL commands in Pl/sql blocks using built-in packages such as Dbms_sql or execute the Execute immediate command, pl/sql the compiler to guarantee object references and user permissions.

Here we discuss various DDL and TCL statements for accessing the Oracle database.

Inquire

The SELECT statement is used to query data from the database, and when the SELECT statement is used in Pl/sql, it is used with the INTO clause, and the return value of the query is given to the variable in the INTO clause, and the declaration of the variable is in Delcare. The SELECT into syntax is as follows:

SELECT [DISTICT|ALL]{*|column[,column,...]}
INTO (variable[,variable,...] |record)
FROM {table|(sub-query)}[alias]
WHERE............

The SELECT statement in Pl/sql returns only one row of data. If you have more than one row of data, use an explicit cursor (we'll do the discussion of the cursor later), and in the INTO clause we want to have a variable with the same number of columns in the SELECT clause. INTO clause can also be a record variable.

%type Property

In Pl/sql, variables and constants can be declared as built-in or user-defined data types to refer to a column name while inheriting his data type and size. This dynamic assignment method is useful, such as the data type and size of the column referenced by the variable, and if the%type is used, the user does not have to modify the code, otherwise the code must be changed.

Cases:

v_empno SCOTT.EMP.EMPNO%TYPE;
v_salary EMP.SALARY%TYPE;

Not only the column names can use%type, but also variables, cursors, records, or declared constants can use%type. This is useful for variables that define the same data type.

DELCARE
V_A NUMBER(5):=10;
V_B V_A%TYPE:=15;
V_C V_A%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE
('V_A='||V_A||'V_B='||V_B||'V_C='||V_C);
END
SQL>/
V_A=10 V_B=15 V_C=
PL/SQL procedure successfully completed.
SQL>

Other DML statements

The DML statements for other operational data are: INSERT, UPDATE, delete, and lock TABLE, which have the same syntax in Pl/sql as in SQL. We've discussed the use of DML statements before and we don't repeat them here. You can use any variable declared in the Declare section in a DML statement, and if it is a nested block, pay attention to the scope of the variable.

Cases:

CREATE OR REPLACE PROCEDURE FIRE_EMPLOYEE (pempno in number)
　AS
　　v_ename EMP.ENAME%TYPE;
　BEGIN
　　SELECT ename INTO v_ename
　　FROM emp
　　WHERE empno=p_empno;
　　INSERT INTO FORMER_EMP(EMPNO,ENAME)
　　VALUES (p_empno,v_ename);
　　DELETE FROM emp
　　WHERE empno=p_empno;
　　UPDATE former_emp
　　SET date_deleted=SYSDATE
　　WHERE empno=p_empno;
　　EXCEPTION
　　　WHEN NO_DATA_FOUND THEN
　　　DBMS_OUTPUT.PUT_LINE('Employee Number Not Found!');
　END