Introduction to Oracle cursor extraction-related data syntax

This article mainly introduces the actual operation scheme of Oracle cursor extraction related data. First, we need to get a row of related data from Oracle cursor. I personally suggest you use the FETCH Command. After each data extraction, the Oracle 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 is an introduction to Oracle cursor data extraction. I hope you will get some benefits.

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