Oracle Practice--PL/SQL-based cursors

Cursors for getting Started with PL/SQL basics

PL/sql: Process Language (Procedure Language) and structured language (structured Query Language) A programming language that is a combination of Span style= "Color:rgb (51,51,51); line-height:26px; Font-family:calibri ">sql expansion, Support for a variety of data types, such as large objects and collection types, can use conditions and loops and other control statements, can create stored procedures, packages and triggers, etc., to the sql The execution of the statement adds program logic, with the oracle . Span style= "Color:rgb (51,51,51); font-family:arial; LINE-HEIGHT:26PX "> server and Oracle tools are tightly integrated, with portability, flexibility and security.

--------------------------------------------------------------------------------------------------------------- -----------------------
/*
cursors: Can be used to hold query results, to extract query results line by row, programmatic access to the data

Type:1. an implicit cursor; 2. display the cursor;3. referencing Cursors

*/

/*

in the PL/SQL used in DML automatically creates an implicit cursor when a statement is

an implicit cursor is automatically declared, opened, and closed with the name SQL

by checking the properties of an implicit cursor, you can obtain the most recently executed DML Statement of Information

The properties of an implicit cursor are:

1.%found–sql statement affects one or more rows as a TRUE

2.%notfound–sql statement does not affect any row as TRUE

3.%rowcount–sql number of rows affected by the statement

4.%isopen - Whether the cursor is open, always FALSE

*/

Create or Replace procedure SOPV (obj varchar2)       As-defines a stored procedure for outputting strings, simplifying writing begin dbms_output.put_line (obj);

Create or Replace procedure sopn (obj number)       As--defines a stored procedure for outputting number type begin Dbms_output.put_line (obj); end;

-- an 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 s Ql%notfound then--If there is no effect function, NotFound   SOPV (' not found! '); elsif Sql%found then   sopn (sql%rowcount); end If;end;

-- an implicit cursor 2

DECLARE v_name varchar2: = ' Ysjian '; Begin update emp Set ename = v_name where empno = 7369; If Sql%found then   SOPV (' update succeeded ');   SOPN (Sql%rowcount); elsif Sql%notfound then    SOPV (' no found! '); end if;end;

-- an implicit cursor 3

DECLARE    v_emp_rec Emp%rowtype;    V_empno Number: = ' &empno '; begin    SELECT * to V_emp_rec from EMP where empno=v_empno;    If Sql%notfound then       dbms_output.put_line (' not found ');    else       dbms_output.put_line (v_emp_rec.empno| | ' --' | | V_emp_rec.ename);    End If;    Exception      when no_data_found--exception type, no data is found then        dbms_output.put_line (' Data no found exception! '); End

-- Display Cursors 1 , keywords, cursor is Opoen fetch close

DECLARE v_emp Emp%rowtype; Cursor V_cur is--Here is different from the general variable declaration, the type is preceded by the name, the keyword cannot be used as,       open the cursor loop   fetch v_cur;--with the IS select *from Emp;begin open v_cur into v_emp;--extracting cursor   SOPV (v_emp.ename);   Exit when V_cur%notfound; End Loop; Close v_cur;--closes the cursor end;

-- Display Cursors 2 , for Looping through , Open, extract and close cursor operations are 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 Cursors 3 , with parameter

DECLARE   v_emp Emp%rowtype;   Cursor V_cur (parameter varchar2) is a select * from emp where ename = Parameter;begin for v_res in V_cur (' &ename: ') 
   
    loop       SOPV (v_res.ename);     End Loop;end;
   

-- Display Cursors 4 : Update data with cursor, keyword: Select ... for update

Select *from Emp;declare v_salnumber; Cursor Emp_cur is a select  * from EMP where  sal<1500 for update of Sal;--Update Sal field begin for CIN Emp_cur   Lo Op     v_sal: = c.sal;     Update emp Set sal = v_sal*1.2 where CURRENT of emp_cur;--refers to the row   end Loop;end;

--  REF cursors: Used to handle run-time dynamic execution SQL Enquiry

Declare type  EMP_REF is ref cursor;--declares a cursor type, here it is different oh  emp_cur emp_ref;--declares a cursor, type is the type defined above  v_emp emp% RowType;  V_sal Number: = ' & Enter Salary: '; begin  Open emp_cur for ' select * from emp where sal>:s '--here can be a string, which can be dynamically passed the value of the  The using v_sal;--gives the placeholder binding value  loop    fetch emp_cur into v_emp;    Exit when Emp_cur%notfound;    SOPV (v_emp.ename);  End Loop;  Close emp_cur;end;

make a simple summary of the cursor

1. Cursors are used to process data in a query result set

2. Cursor types are: Implicit cursors, explicit cursors, and REF cursors

3. An implicit cursor is automatically defined, opened, and closed by PL/SQL

4. Explicit cursors are used to process queries that return multiple rows

5. Explicit cursors can delete and update rows in the active set

6. To work with all records in the result set, you can use a circular cursor

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

*/

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