存储过程,函数,游标（Stored procedures, functions, cursors）.doc

存储过程,函数,游标（Stored procedures, functions, cursors） A, the cursor The cursor is a PL/SQL control structure that can be explicitly controlled for the processing of SQL statements to facilitate the processing of the data of the table. Ps. When the amount of data in the table is large, it is not recommended to use cursors (inefficient and resource-consuming), but it can be flexible with the data method. The cursor is a recorded pointer, which is fed back to the record of the table by using the cursor to update or delete the activity set. Cursor properties: % FOUND, % NOTFOUND, % ROWCOUNT, % ISOPEN 1. Explicit cursors An explicit cursor is a cursor that is explicitly declared by the user. Depending on the query defined in the cursor, the returned row set can contain zero or more rows, which are called activity sets. The cursor will point to the activity concentration. Explicit cursor manipulation: declare, open, retrieve records from cursor, and close. The SET SERVEROUTPUT ON. DECLARE CURSOR cur IS SELECT * FROM books. Myrecord books % ROWTYPE; The BEGIN The OPEN cur; LOOP The FETCH cur INTO books; EXIT the WHEN cur % NOTFOUND; Dbms_outout.put_line (book.id | | | | books. END LOOP; The CLOSE cur; The END; 1.1. Explicit cursor with parameters: parameters need not specify length or precision. 1.2. FOR circular cursor: use traversal mode to open, extract and close cursors automatically. (can you use % ROWCOUNT to get the number of rows extracted from the cursor?) DECLARE / * define the parameter cursor * / CURSOR cur_para varchar2 (id) IS SELECT books_name FROM books WHERE books_id = id; BGEIN / * call the parameter cursor and handle * / with a FOR loop FOR cur IN cur_para ( 0001 ) LOOP DBMS_OUTPUT. PUT_LINE (cur. Books_id | | | | cur. Books_id); END LOOP; The END; 1.3, use the display cursor to delete or update the record Definition: use SELECT... FOR UPDATE statement to indicate the lock of things; When executed: use the WHERE CURRENT OF curXXX clause to specify the cursor