Plsql Cursor Note one

Principle: Plsql The block executes the query and data manipulation DML, Oracle assigns it a context area (Contextarea), and the cursor is a pointer to the context area, so manipulating the cursor is a lot faster than manipulating the pointer.

Goal:

1) using the display cursor and cursor properties

2) using parameter cursors

3) updating or deleting data using display cursors

4) using the cursor for loop

5) using cursor variables

6) Use Fetch * Bulk collect into statement and cursor expression to process multiple rows and columns

Personal understanding must be flexible use, cursors and composite tables, is the operation of a single multi-column or more than one row, a row of multiple rows, and then to select the corresponding table or cursor, the beginning is not ripe to use must first think, with this processing line, this can help you recall the previous memory of the cursor knowledge, one by one to exclude and to find It's going to be soon when it's ripe.

Personal understanding implicit cursors only the kind of statements that handle select INTO and DML are, that is, single-line data there's still a question.

Display cursors handle multiple rows of single or multiple columns of data.

"Show Cursors"

Use the display cursor to remember a few steps: Define, open, traverse, close

DECLARE CURSOR cursor_name is select_statement;

Open cursor_name;

Fetch cursor_name into Variable1,variable2 ...

FETCH cursor_name Bulk collect into Collect1,collect2 ...

Close cursor_name;

"Cursor Properties"

The Display cursor property is used to return the execution information for the displayed cursor, including%isopen,%found,%notfound,%rowcount, which must be noted separately from the Plsql exception area such as Not_data_found, too_many_rows, etc.

Eg:fetch * into can only process one statement at a time, in order to handle multiple loops that need to be used

Open emp_cursor;

Loop

Fetch emp_cursor into v_ename,v_sal;

Exit when Emp_cursor%notfound;

Dbms_output.put_line (v_ename| | V_sal);

End Loop;

Close emp_cursor;

End

Fetch * Bulk collect into extract all data

Open emp_cursor;

Fetch Emp_cursor bulk collect into ename_table;

For I in 1..ename_table.count loop

Dbms_output.put_line (Ename_table (i));

End Loop;

End; Personal understanding this approach only works when dealing with multiple rows in a column

Another way is to fetch * bulk collect into limit rows, which is how many rows can be entered.

To define a record variable with a cursor

Cursor Emp_cursor is a select ename,sal from EMP;

Emp_record Emp_cursor%rowtype:

Fetch emp_cursor into Emp_record;

Dbms_output.put_line (emp_record.sal);

"Parameter Cursor"

The parameter cursor is certainly the name of the cursor must be an input parameter, this parameter can only specify the data type, no length, in the cursor subquery in the WHERE clause reference the parameter, otherwise lost the meaning of the cursor.

Declare

Cursor Emp_cursor (no number) is

Select Ename,sal from emp where deptno=no;

Is that each time you enter a different no value, you get a different result.

"Cursor Update delete data This item is more complex and prone to problems, and subsequent additions are not recommended"

"For Loop of Cursors"

The For loop of the cursor implicitly opens the cursor, extracts the data and closes the cursor, extracting the data once per loop

For Record_name in Cursor_name Loop

Statement1;

Statement2;

..

End Loop;

Declare

CURSOR Emp_cursor is a select ename,sal from EMP;

Begin

For Emp_record in Emp_cursor loop

Dbms_output.put_line (emp_cursor%rowcount| | Emp_record.ename);

End Loop;

End

Shorthand for

For Emp_record in (select Ename,sal from emp) loop

Dbms_output.put_line (Emp_record.ename);

End Loop;

Displays the difference between a cursor and a cursor variable, indicating when the cursor definition is indicated by a select and the cursor variable definition is not required Select, can be opened when the specified select is open emp_cursor for select_statement;

"Cursor Variable"

In order to use a cursor variable, you must use the reference type REF CURSOR

Type ref_type_name is REF CURSOR [return return_type]

Cursor_variable Ref_type_name;

Cursor_variable is the cursor variable name

Open cursor

Open cursor_variable for select_statement;

Extracting data

Fetch cursor_variable into Variable1,variable2 ...

Fetch cursor_variable Bulk collect into Collect1,collect2.

Close Cursors

Close cursor_variable

Eg: a cursor variable that defines a reference type

Declare

Type Emp_cursor_type REF CURSOR;

Emp_cursor Emp_cursor_type;

Emp_record Emp_cursor%rowtype;

Begin

Open Emp_cursor for SELECT * from EMP;

Loop

Fetch emp_cursor into Emp_record;

Exit when Emp_cursor%notfound;

Dbms_output.put_line (Emp_record.ename);

End Loop;

End;

Eg: if a return clause is specified when defining a reference type, then the returned must match the defined type.

Declare

Type Emp_record_type is record (

Name Varchar2 (ten), salary number (6,2)

);

Type Emp_cursor_type is REF CURSOR return emp_cursor_type; With the returned reference variable

Emp_cursor Emp_cursor_type;

Emp_record Emp_record_type;

Begin

Open Emp_cursor for select ename,sal from EMP where deptno=20;

Loop

Fetch emp_cursor into Emp_record;

Exit when Emp_cursor%notfound;

Dbms_output.put_lin (emp_cursor%rowtype| | Emp_record.name);

End Loop;

End

This article from "Yun Weibang" blog, declined reprint!

Plsql Cursor Note one