What is the cursor of Oracle stored procedure?

In Oracle, the cursor is a memory workspace of SQL, defined by the system or the user in the form of a variable, used to temporarily store data blocks extracted from the database; the data is transferred from the database to the cursor variable Finally, the application decomposes the required data from the cursor variable and processes it.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

What is the cursor of oracle stored procedure

1. The use of cursor. I saw a paragraph that explains the concept very well, as follows:

A cursor is a memory workspace of SQL, defined in the form of a variable by the system or user. The purpose of a cursor is to temporarily store data blocks extracted from the database. In some cases, it is necessary to transfer data from tables stored on disk to computer memory for processing, and finally display the processing results or write them back to the database. In this way, the speed of data processing will be improved, otherwise frequent disk data exchange will reduce efficiency.

There are two types of cursors: explicit cursors and implicit cursors. The SELECT...INTO... query statement used in the aforementioned program can only extract one row of data from the database at a time. For this form of query and DML operation, the system will use an implicit cursor. But if you want to extract multiple rows of data, the programmer must define an explicit cursor and process it through cursor-related statements. An explicit cursor corresponds to a SELECT statement that returns multiple rows and columns.

Once the cursor is opened, the data is transferred from the database to the cursor variable, and then the application decomposes the required data from the cursor variable and processes it. In our operations of insert, update, delete and select value into variable, we use implicit cursors.

Implicit cursor attributes Return value type meaning:

• SQL%ROWCOUNT Integer represents the number of data rows successfully executed by the DML statement

• SQL%FOUND Boolean type A value of TRUE indicates that the insertion, deletion, update or single-row query operation is successful

• ##SQL%NOTFOUND Boolean type The value returned by the SQL%FOUND attribute is opposite

• SQL%ISOPEN Boolean True during DML execution, false after completion

2. Implicit cursor:

create or replace procedure prc_example (epo in number) as
BEGIN  
        UPDATE emp SET sal=sal+100 WHERE empno=epo;   
         IF SQL%FOUND THEN    
        DBMS_OUTPUT.PUT_LINE('成功修改雇员工资！');   
        COMMIT;    
        ELSE  
        DBMS_OUTPUT.PUT_LINE('修改雇员工资失败！');   
         END IF;    
        END; 
declare
e_number number;
begin
e_number:=7788;
prc_example(e_number);
 end;

3. Display the cursor:

Use the cursor to query the names of all employees numbered 10

create or replace procedure prc_example is
begin
    declare
    cursor emp_sor  is select ename,sal from emp where deptno=10;  
    cname emp.ename%type;     
    csal emp.sal%type;
 begin
   open emp_sor;       
   loop        
   fetch emp_sor into cname,csal;  --取游标的值给变量。             
   dbms_output.put_line('ename:'||cname);        
   exit when emp_sor%notfound;        
  end loop;         
  close emp_sor;     
 end;
end;

Recommended tutorial: "

Oracle Video Tutorial"

The above is the detailed content of What is the cursor of Oracle stored procedure?. For more information, please follow other related articles on the PHP Chinese website!