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.
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
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;
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;
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!