Ref cursor (Dynamic Cursor)

 

Reference variable--Variable used to store the value pointer

 

 Cursor variable(Ref cursor)

 

  When using a cursor, you do not need to specify the corresponding SELECT statement when defining the cursor.

 

 When the cursor is open (when the cursor is open), you need to specify the SELECT statement, so that a cursor is combined with a select statement.

 

Cursor instance:

 

1.Use pl/SQLYou can enter a department number and display the names and salaries of all employees in the department.

 

2.On the basis of 1, if an employee's salary is less than 200 yuan, an additional 100 yuan will be added.

 

Declare

 

--Define the cursor type

 

TypeSp_emp_copy_cursor Is Ref Cursor;

 

--Define a cursor variable

 

Test_cursor sp_emp_copy_cursor;

 

V_ename emp_copy.ename%Type;

 

V_sal emp_copy.sal%Type;

 

Begin

 

  --Combine test_cursor with a select statement

 

  OpenTest_cursor For Select Ename, Sal From Emp_copy WhereDeptno= &No;

 

  --Fethch)

 

  Loop

 

 Fetch Test_cursor Into V_ename, V_sal;

 

 --Determine whether test_cursor is null

 

 Exit When Test_cursor%Notfound;

 

Dbms_output.put_line('Name :'| V_ename |'Salary :'| V_sal );

 

  End Loop;

 

  --Close cursor

 

  CloseTest_cursor;

 

End;

 

 

From: http://blog.sina.com.cn/s/blog_62e75cd001015mkr.html