Oracle cursor opening and closing

The following describes Oracle cursors. This article describes how to enable the Oracle cursor and the actual operations you want to perform. If you are interested in the actual operations, the following articles will provide you with relevant knowledge.

Open Oracle cursor

Before using the values in the cursor, dewengz should first open the cursor and open the cursor to initialize query processing. The syntax for opening a cursor is:

 
 

  
  
OPEN cursor_name  
 
 

Cursor_name is the cursor name defined in the Declaration section.

Example:

 
 

  
  
OPEN C_EMP;  
 
 

Close Oracle cursor

Syntax:

 
 

  
  
CLOSE cursor_name  
 
 

Example:

 
 

  
  
CLOSE C_EMP;  
 
 

Close Oracle cursor

Use the FETCH Command to obtain a row of data from the cursor. After each data extraction, the cursor points to the next row of the result set. Syntax:

 
 

  
  
FETCH cursor_name INTO variable[,variable,...]  
 
 

For each column of the Oracle cursor defined by SELECT, The FETCH Variable list should have a variable corresponding to it, and the variable type should be the same.

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 undoubtedly very troublesome. If multiple rows return results, you can use the loop and use the Oracle cursor attribute as the condition for ending the loop to extract data in this way, the readability and conciseness of the program are greatly improved. Next we will re-write the following program in a loop:

 
 

  
  
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  
 
 

The above content describes the specific Oracle cursor operations, hoping to help you in this regard.

Article by: http://www.programbbs.com/doc/class10-3.htm