Cursor learning
One> The cursor is what: the vernier literal understanding is the cursor of the swim. Description Use the database language: The cursor is a location entity that maps on the result set. With the cursor user, you can access any row of data in the result set. After placing the cursor to a row, you can operate the line data. For example, data for extracting the current row, etc. 2> Classification of cursors: Explicit cursors and implicit cursors
Show the use of the cursor needs 4 steps: 1. Declare the cursor Cursor mycur (VARTYPE NUMBER) IS SELECT EMP_NO, EMP_ZC from CUS_EMP_BASIC WHERE COM_NO = VARTYPE
2. Open the cursor open mycur (000627) Note: 000627: Parameter 3. Read data fetch mycur ing varno, varprice; 4. Close the cursor close mycur; three> The property of the cursor Oracle cursor has 4 attributes:% isopen,% Found ,% Notfound,% ROWCOUNT% isopen determines if the cursor is opened, if the% isopen is equal to TRUE, otherwise equal to false% FOUND% NOTFOUND to determine whether the row in which the cursor is active, if it is valid, the% FoundD is equal to TRUE, otherwise, False% RowCount Returns the number of records read by the current location.
Four> Example: set serveroutput on; declare varno varchar2 (20); varprice varchar2 (20); CURSOR mycur (vartype number) is select emp_no, emp_zc from cus_emp_basic where com_no = vartype; begin if mycur% isopen = false then open mycur ( 000627); endiff; fetch mycur inTo varno, varprice; while mycur% Found loop dbms_output.put_line (varprice); if mycur% rowcount = 2 kiln = 2 kiln = 2 dam; Varprice; end loop; loose mycur;
The structure of the PL / SQL record and the structure in the C language are similar, and the logic unit consisting of a set of data items. The PL / SQL record does not save the retronomy, which is the same as the variable, save the memory space, when using the record, first define the record structure, then declare the record variable. You can view the PL / SQL record as a user-defined data type.
set serveroutput on; declare type person is record (empno cus_emp_basic.emp_no% type, empzc cus_emp_basic.emp_zc% type); person1 person; cursor mycur (vartype number) is select emp_no, emp_zc from cus_emp_basic where com_no = vartype; begin if mycur% Isopen = false kiln mycur (000627); end if; loop fetch mycur inTo Person1; exit when mycur% notfound; dbms_output.put_line ('Employees Number:' || Person1.empno || ', address:' || Person1. EMPZC); end loop; close mycur; end; typical cursor for loop cursor for looping a quick way of displaying a cursor, using the FOR loop sequentially read the row data in the result set, when the FORM cycle starts, the cursor is automatically opened (Do not need Open), automatically read the data of the cursor current line per cycle (not required), when exiting the for loop (no need to use a closed)
You cannot use the Open statement, the fetch statement, and the Close statement when using the cursor for cycle, otherwise an error will occur.
set serveroutput on; declare cursor mycur (vartype number) is select emp_no, emp_zc from cus_emp_basic where com_no = vartype; begin for person in mycur (000627) loop dbms_output.put_line ( 'employee number:' || person.emp_no || ', Address: '|| person.emp_zc); end loop;
