Oracle cursor usage and syntax encyclopedia ____oracle

Key Words:

an explicit cursor

When a query returns more than one row, an explicit cursor is required, 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. Explicit cursors are declared in the Declarations section of the Pl/sql block, open in the execution section or in the Exception handling section, take out the data, and close.

using Cursors

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

declaring Cursors

Grammar:

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.

Cases:

BEGIN

In a cursor definition, the SELECT statement does not necessarily need a table to be a view, a column that can be selected from more than one table or view, or even use the * to select all columns.

Open Cursor

You should first open the cursor and open the cursor initialization query processing before using the value in the cursor. The syntax for opening cursors is:

OPEN cursor_name

Cursor_name is the name of the cursor defined in the Declarations section.

Cases:

OPEN c_emp; Closes the cursor.

Grammar:

Close cursor_name

Cases:

Close c_emp; Extracts data from a cursor. Get a 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 the cursor defined by the Select, the list of fetch variables should have a variable corresponding to it, and the type of the variable will be the same.

Cases:

End

This code is undoubtedly very troublesome, if there are multiple rows to return results, you can use the loop and the cursor properties to end the loop condition, in this way to extract data, the program's readability and simplicity are greatly improved, the following we use the loop to write the above program:

End

record Variable

Define a record variable use the type command and%rowtype, for more information about%rowstype see related information.

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.

Key Words:

If you want to select all columns in a table when you use%rowtype on a table and put the values taken from the cursor into the record, the use of * in the SELECT clause is greater than the list of all columns.

Cases:

End;

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

Dbms_out. Put. Put_Line (/' Salary of employee/') End;

Cursors with Parameters

Similar to stored procedures and functions, you can pass parameters to a cursor and use it in a query. 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] ...];

Parameter values can be literals or variables.

Cases:

decalre CURSOR c_dept is SELECT * FROM Dept ORDER by Deptno; CURSOR c_emp (p_dept VARACHAR2) is SELECT ename,salary from emp WHERE deptno=p_dept Order by Enam e 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 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; Close c_emp; Dbms_output. Put_Line (/' Toltal Salary for dept:/' | | | v_tot_salary); End LOOP; Close c_dept; End;

cursor FOR loop

Most of the time we are in the process of designing a program by following these steps:

1, open the cursor.

2, start the cycle.

3. Take the value from the cursor.

4, the line is returned.

5, processing.

6, closed cycle.

7, close the cursor.

This type of code can be simply referred to as a cursor for looping. But there is a kind of loop that is not the same type, this is the for loop, the cursor for the for loop is declared as normal, and its advantage is that there is no need to explicitly open, close, fetch data, test the presence of data, define variables that hold data, and so on. The syntax for a cursor for loop is as follows:

Key Words:

End LOOP;

Here we use the For loop to rewrite the above example:

Dbms_output. Put_Line Dbms_output. Put_Line Dbms_output. Put_Line End;

using queries in a cursor for loop

A query can be defined in a cursor for loop, and the cursor has no name because it is not explicitly declared, and the record name is defined by a cursor query.

End;

subqueries in Cursors

The syntax is as follows:

WHERE dname!=/' accounting/');

You can see that there is no difference between subqueries in SQL.

updates and deletions in cursors

You can still update or delete rows of data using the update and DELETE statements in Pl/sql. An explicit cursor is used only if you need to obtain multiple rows of data. Pl/sql provides a way to delete or update records simply by using a cursor.

The WHERE current of substring in an UPDATE or DELETE statement specializes in the most recent data taken out of the table to perform an update or delete operation. To use this method, you must use a for update substring when declaring a cursor, and when a dialog uses a for update substring to open a cursor, all data rows in the returned set are in row-level (row-level) exclusive locks, and other objects can only query the rows of data and cannot UPDATE, delete, or select ... For update operation.

Grammar:

[NoWait]

In a multiple-table query, use the OF clause to lock a particular table, and if the of clause is omitted, the rows of data selected in all tables are locked. If these rows of data are already locked by another session, Oracle will normally wait until the data row unlocks.

The syntax for using the where current of substring in update and delete is as follows:

 where{current of Cursor_name|search_condition} 
 
 
    keywords:     
 
 
 
 
 
 Delcare 
 CURSOR C1 are SELECT empno,salary 
 from EMP 
 WHERE comm are NULL 
 for UPDATE of Comm; 
 V_comm number (10,2); 
 BEGIN 
 for R1 in C1 loop 
 IF r1.salary<500 THEN 
 v_comm:=r1.salary*0.25; 
 ELSEIF r1.salary<1000 THEN 
 v_comm:=r1.salary*0.20; 
 ELSEIF r1.salary<3000 THEN 
 v_comm:=r1.salary*0.15; 
 ELSE 
 v_comm:=r1.salary*0.12; 
 End IF; 
 UPDATE EMP; 
 SET Comm=v_comm 
 WHERE Current of c1l; 
 End LOOP; 
 End