Use of "ORACLE" Plsql Dynamic Cursors (11g)

#静态游标指的是程序执行的时候不需要再去解析sql语言, the parsing of SQL statements can be done at compile time.

Because the dynamic cursor contains parameters, parsing of the SQL statements must wait until the parameters are determined to complete.

From this point of view, the efficiency of static cursors is also higher than that of dynamic cursors.

#游标的相关概念:

Defined:

Cursor It is a server-side storage area that is available to the user, in this area

Stored is the result set that the user obtains through a query statement, the user by controlling this cursor area

Pointer to extract the data from the cursor and then perform the operation.

Essence:

Is the operation of the user on the server memory area on the remote client, which is provided by the database to the user

A pointer that allows the user to retrieve data from the memory area of the server.

#游标具有的属性:

1,%isopen (determines whether the cursor is turned on true or false)

2,%found (returns whether the data is extracted from the result set to True or false)

3,%notfound (returns whether the result set is not extracted to data true or false)

4,%rowcount (returns the actual number of rows that have been fetched so far)

#游标分类

First, static cursors

1. Implicit cursors:

For select: Into ... Statement, only one data can be fetched from the database at a time, and for this type of DML SQL statement, the implicit cursor

Select Update/insert/delete operation

2. Display cursors:

Defined and managed by programmers, for extracting multiple rows of data from a database, you need to use an explicit cursor

1) define CURSOR---Cursor [cursor name] is

2) Open Cursor---Open [cursor name]

3) operation Data---Fetch [cursor name]

4) Close cursor---Close [cursor name]

Second, ref cursors

1. REF cursor: A temporary object that dynamically associates the result set. This is the dynamic decision to execute the query at run time.
2. Ref CURSOR Action: To achieve the function of passing result sets between programs, using REF cursor can also implement bulk SQL, thereby improving SQL performance.
3. The difference between a static cursor and a REF CURSOR:

① static cursors are statically defined and REF cursors are dynamically correlated;
② the REF CURSOR variable is required to use a REF CURSOR.
③REF cursors can be passed as parameters, while static cursors are not possible.

4. REF CURSOR variable: is a variable that references a REF cursor type, pointing to a dynamically associated result set.
5. How to use ref Cursors:
① declares a REF cursor type and determines the REF cursor type;
⑴ strongly typed REF cursor: Specifies that the type of the Retrun TYPE,REF cursor variable must be the same as the return type.
syntax: type REF CURSOR name is REF CURSOR return result set returns record type;
⑵ weakly typed REF CURSOR: Does not specify return type, can match any type of cursor variable, and is used to get any result set.
syntax: Type REF CURSOR name is REF CURSOR;
② declaring a REF cursor type variable;
syntax: The variable name has declared a REF cursor type;
③ opens a REF cursor, associating the result set;
syntax: The Open REF cursor type variable for query statement returns a result set;
④ get record, operation record;
syntax: Fatch REF CURSOR name into temporary record type variable or attribute type variable list;
⑤ Close the cursor and completely release the resources;
syntax: Close REF CURSOR name;

Sql> declare
2 type emp_cursor is REF CURSOR;
3 my_cursor emp_cursor;
4 v_ename emp.ename%type;
5 v_ Sal Emp.sal%type;
6 V_deptno Dept.deptno%type: = &p_deptno;
7 V_dname Dept.dname%type;
8 begin
9 Select Dname to V_dname from dept where deptno = V_deptno;
Dbms_output.put_line (' Department: ' || V_dname);
One open my_cursor for
the ename, Sal from emp where deptno = V_deptno;
Loop
Fetch my_cursor< br> into V_ename, v_sal;
Exit when My_cursor%notfound;
Dbms_output.put_line ('--name: ' | | v_ename | | ' Salary: ' | | V_sal);
End Loop;
Close my_cursor;
End;
/
Enter value for p_deptno:30
Old 6:v_deptno dept.deptno%type: = &p_deptno;
New 6:v_deptno Dept.deptno%type: = 30;
Department:sales
--name:allensalary:1600
--name:wardsalary:1250
--name:martinsalary:1250
-- name:blakesalary:2850
--name:turnersalary:1500
--name:jamessalary:950

PL/SQL procedure successfully completed.

Use of "ORACLE" Plsql Dynamic Cursors (11g)