Oracle practice -- cursor based on pl/SQL

Basic PL/SQL entry cursor

PL/SQL: a programming Language combined with a Structured Language (Structured Query Language). It is an extension of SQL and supports multiple data types, such as large objects and Collection types, you can use control statements such as conditions and loops to create stored procedures, packages, and triggers, and add program logic to SQL statement execution, tightly integrated with Oracle servers and Oracle tools, it is portable, flexible, and secure.

Certificate --------------------------------------------------------------------------------------------------------------------------------------
/*
Cursor: it can be used to store query results, extract query results row by row, and access data programmatically.

Type: 1. Implicit cursor; 2. Display cursor; 3. Reference cursor

*/

/*

An implicit cursor is automatically created when DML statements are used in PL/SQL.

The implicit cursor is automatically declared, opened, and closed. Its name is SQL.

Check the properties of the implicit cursor to obtain information about the recently executed DML statements.

Implicit cursor attributes include:

1.% FOUND-the SQL statement affects TRUE for one or more rows.

2.% NOTFOUND-the SQL statement does not affect any row to TRUE.

3.% ROWCOUNT-number of rows affected by SQL statements

4.% ISOPEN-whether to open the cursor, always FALSE

*/

Create or replace procedure sopV (obj varchar2) as -- defines a stored procedure for outputting strings, simplifying the writing of begin dbms_output.put_line (obj); end;

Create or replace procedure sopN (obj number) as -- defines a stored procedure for outputting the number type begin dbms_output.put_line (obj); end;

-- Implicit cursor 1

Declare v_ename emp. ename % type; v_sq lvarchar2 (200); v_no number: = '& no.:'; begin v_ SQL: = 'select * from emp where empno =: no '; execute immediate v_ SQL using v_no; if SQL % notfound then -- if the function is not affected, notfound sopV ('not found! '); Elsif SQL % found then sopN (SQL % rowcount); end if; end;

--Implicit cursor2

Declare v_name varchar2 (20): = 'ysjian '; begin update emp set ename = v_name where empno = 7369; if SQL % found then sopV ('Update successful '); sopN (SQL % rowcount); elsif SQL % notfound then sopV ('not found! '); End if; end;

-- Implicit cursor 3

Declare v_emp_rec emp % rowtype; v_empno number: = '& empno'; begin select * into v_emp_rec from emp where empno = v_empno; if SQL % notfound then exist (' not found '); else locate (v_emp_rec.empno | '-->' | v_emp_rec.ename); end if; exception when no_data_found -- exception type. if no data is found, then dbms_output.put_line ('data no found exception! '); End;

--Display cursor1, Keyword,Cursor is opoen fetch close

Declare v_emp emp % rowtype; cursor v_cur is -- This is different from the general variable declaration. The type is sent before the name. The as keyword cannot be used, and is select * from emp; begin open v_cur; -- open the cursor loop fetch v_cur into v_emp; -- extract the cursor sopV (v_emp.ename); exit when v_cur % notfound; end loop; close v_cur; -- close the cursor end;

-- Show cursor 2, for loop traversal, open, extract and close the cursor operation is automatically completed

declare v_emp emp%rowtype; cursor v_cur is select  * from emp;begin for resin v_cur   loop     sopV(res.ename);   end loop;end；

--Display cursor3, With Parameters

declare   v_emp emp%rowtype;   cursor v_cur(parameter varchar2) is select * from emp where ename = parameter;begin for v_res in v_cur('&ename：')     loop       sopV(v_res.ename);     end loop;end;

--Display cursor4: Update data with a cursor. Keyword:Select... for update

Select * from emp; declare v_salnumber; cursor emp_cur is select * from emp where sal <1500 for update of sal; -- update sal field begin for cin emp_cur loop v_sal: = c. sal; update emp set sal = v_sal * 1.2 where current of emp_cur; -- end loop of the row referred to by the current cursor; end;

--REFCursor: used to process dynamic execution during runtimeSQLQuery

Declare type emp_ref is ref cursor; -- declare a cursor type, which is different here. emp_cur emp_ref; -- declare a cursor of the type v_emp emp % rowtype; v_sal number defined above: = '& input salary:'; begin open emp_cur for 'select * from emp where sal>: s' -- here it can be a string, that is, using v_sal that can dynamically pass values; -- bind the placeholder value loop fetch emp_cur into v_emp; exit when emp_cur % notfound; sopV (v_emp.ename); end loop; close emp_cur; end;

A simple summary of the cursor

1. The cursor is used to process data in the query result set.

2. There are two types of cursors: Implicit cursors, explicit cursors, and REF cursors.

3. Implicit cursors are automatically defined, opened, and closed by PL/SQL.

4. An explicit cursor is used to process queries that return multiple rows.

5. An explicit cursor can be used to delete and update rows in an active set.

6. Loop cursors can be used to process all records in the result set.

7. When declaring a REF cursor, you do not need to associate the SELECT statement with it.

*/

Source: http://blog.csdn.net/ysjian_pingcx/article/details/25645515