Oracle Cursor Usage Encyclopedia __oracle

The query 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.

The%type property can declare variables and constants as built-in or user-defined data types in Pl/sql 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.

Example:   v_empno SCOTT. Emp. empno%type;   v_salary EMP. salary%type;    Not only column names can use%type, but 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.    & nbsp;  sql>         Other DML statements       Other operational data DML statements are: INSERT, UPDATE, delete, and lock TABLE, the syntax for these statements in Pl/sql is the same 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.  

Example:   create OR REPLACE PROCEDURE fire_employee (pempno in number)     as      v_e Name 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! ');    &Nbsp;  end 

The result of a DML statement when a DML statement is executed, the result of the DML statement is saved in four cursor properties, which are used to control the process or to understand the state of the program. When you run a DML statement, Pl/sql opens a built-in cursor and processes the result, which is an area of memory that maintains the results of the query, which is opened when the DML statement is run, and closed when it is finished.    An implicit cursor uses only sql%found,sql%notfound,sql%rowcount three properties. Sql%found,sql%notfound is a Boolean, Sql%rowcount is an integer value. The values for Sql%found and sql%notfound before any DML statements are executed by Sql%found and sql%notfound are null, and after the DML statement is executed, the Sql%found property value will be:

. True:insert.  TRUE:D elete and update, at least one row is delete or update. . True:select into at least one row back when Sql%found is true, Sql%notfound is false.

Sql%rowcount the value of Sql%rowcount is null before any DML statements are executed, and for the SELECT INTO statement, if successful, the Sql%rowcount value is 1, and if not, Sql%rowcount Value is 0, and an exception no_data_found is generated.

Sql%isopen Sql%isopen is a Boolean value, true if the cursor is open, or false if the cursor is closed. Sql%isopen is always false for an implicit cursor, because an implicit cursor is opened at the time the DML statement is executed, and closes immediately at the end.

A transaction control statement transaction is a working logical unit that can include one or more DML statements, and things control helps users to ensure data consistency.       If any of the DML statements in the transaction control logical unit fail, the entire transaction is rolled back, and in Pl/sql the user can explicitly use the commit, ROLLBACK, SavePoint, and set transaction statements. The commit statement terminates the transaction, permanently saves the changes to the database while releasing all lock,rollback terminating the current transaction releasing all lock, but not saving any changes to the database, SavePoint is used to set the middle point, and when the transaction invokes too many database operations, Intermediate points are useful, set transaction are used to set transaction properties, such as read-write and isolation levels.

An explicit cursor requires an explicit cursor when the query returns more than one row, at which point the user cannot use the SELECT INTO statement. Pl/sql manages implicit cursors, which are open when the query starts, and when the query ends, the implicit cursor closes automatically. An explicit cursor is declared in the declaration portion of a pl/sql block, opened, fetched, and closed in the execution part or exception handling section.

Using cursors to make a declaration here, we refer to a cursor that usually refers to an explicit cursor, so there is no particular case from now on, and we are talking about a cursor that is an explicit cursor. To use a cursor in a program, you must first declare the cursor.

Declaration of cursor method: CURSOR cursor_name is select_statement;

The Pl/sql is an undeclared variable and cannot be assigned to a cursor name or used in an expression.

Example:      delcare      CURSOR c_emp is SELECT empno,ename,salary   & nbsp;  from emp      WHERE salary>2000      ORDER by ename;       ........      begin      A table can be a view in a SELECT statement in a cursor definition. You can also select all columns from multiple tables or views, or even use the *.        Open cursor   Use the value in the cursor before opening the cursor initialization query processing. The syntax for opening cursors is:      open cursor_name         cursor_ Name is the name of the cursor defined in the Declaration section.        Example:       OPEN c_emp;            closing cursors   grammars:       Close cursor_name       Example: & nbsp      Close c_emp; 

  Extract data from cursors       get one row of data from a cursor using the FETCH command. Each time the data is fetched, the cursor points to the next row in the result set. The syntax is as follows:       FETCH cursor_name into variable[,variable,...]        for each column of a cursor defined by a SELECT, the list of fetch variables should have a variable that corresponds to the same type of variable.  

Example:     SET serveriutput on     declare       v_ename EMP. ename%type;       v_salary EMP. salary%type;       CURSOR c_emp is SELECT ename,salary from emp;        begin         OPEN c_emp;             FETCH c_emp into v_ename,v_salary;               Dbms_output. Put_Line (' Salary of Employee ' | | | v_ename | | Is ' | | v_salary);            FETCH c_emp into v_ename,v_salary;              Dbms_output. Put_Line (' Salary of Employee ' | | | v_ename | | Is ' | | v_salary);            FETCH c_emp into v_ename,v_salary;              Dbms_output. Put_Line (' Salary of Employee ' | | | v_ename | | Is ' | | v_salary);         close c_emp;       end             This code is certainly very troublesome, if there are multiple rows to return results, you can use the loop and the cursor properties for the end of the loop condition, this way to extract data, The readability and simplicity of the program are greatly improved, and we use the loop to write down the above program:  SET serveriutput on  declare  v_ename EMP. ename%type;  v_salary EMP. salary%type;  CURSOR c_emp is SELECT ename,salary from EMP;   begin   open c_emp;      loop        FETCH c_emp Into v_ename,v_salary;        EXIT when c_emp%notfound;         Dbms_output. Put_Line (' Salary of Employee ' | | | v_ename | | Is ' | | v_salary);  end 

Record variables define a record variable use the type command and%rowtype, see related information For more information about%rowstype.      Record variables are used to extract rows of data from a cursor, and when a cursor chooses many columns, it is much more convenient to use a record than to declare a variable for each column. If you want to select all the columns in a table when you use%rowtype on a table and put the values taken from the cursor into the record, it is much safer to use * than to list all columns in the SELECT clause.

: SET serveriutput on DECLARE r_emp emp%rowtype;  CURSOR C_emp is a SELECT * from EMP;     BEGIN OPEN c_emp;       LOOP FETCH c_emp into r_emp;       EXIT when C_emp%notfound; Dbms_out. Put. Put_Line (' Salary of Employee ' | | r_emp.ename| | ' Is ' | |     R_emp.salary);   End LOOP;  Close c_emp; End;

%rowtype can also be defined with a cursor name, which means that the cursor must be declared first:

SET serveriutput on DECLARE CURSOR c_emp be SELECT ename,salary from EMP;  R_emp C_emp%rowtype;   BEGIN OPEN c_emp;      LOOP FETCH c_emp into r_emp;      EXIT when C_emp%notfound; Dbms_out. Put. Put_Line (' Salary of Employee ' | | r_emp.ename| | ' Is ' | |   R_emp.salary);   End LOOP;  Close c_emp; End;

Cursors with parameters are similar to stored procedures and functions, and parameters can be passed to cursors and used in queries. This is useful for handling situations where cursors are opened under certain conditions. Its syntax is as follows:

CURSOR cursor_name[(Parameter[,parameter],...)] is select_statement;

The syntax for defining parameters is as follows: Parameter_name [in] data_type[{:=| DEFAULT} value]

Unlike stored procedures, cursors can only accept passed values and cannot return values.     The parameter defines only the data type, no size. You can also set a default value for the parameter, and use the default value when no parameter values are passed to the cursor. The parameter defined in the cursor is only a placeholder, and it is not necessarily reliable to reference the parameter elsewhere.

Assign a value to a parameter when you open the cursor, the syntax is as follows:

OPEN Cursor_name[value[,value] ...]; The   parameter value can be either literal or variable.  

Example:  decalre  CURSOR c_dept is SELECT * to Dept ORDER by deptno;  CURSOR c_emp (p_dept VARACHAR2) is&nbsp ;  select ename,salary   from emp   where deptno=p_dept   ORDER by ename  r_dept dept%rowtype;  v_ename EMP. ename%type;  v_salary EMP. salary%type;  v_tot_salary EMP. salary%type;  begin   open c_dept;       loop           FETCH c_dept into r_dept;          EXIT when c_dept% notfound;          Dbms_output. Put_Line (' Department: ' | | | r_dept.deptno| | ') -'|| R_dept.dname);          v_tot_salary:=0;           OPEN c_emp (r_dept.deptno);               loop              &Nbsp;  FETCH c_emp into v_ename,v_salary;                  EXIT when c_emp%notfound;                  Dbms_output. Put_Line (' Name: ' | | | v_ename| | ' Salary: ' | | v_salary);                 v_tot_ salary:=v_tot_salary+v_salary;              End LOOP;