PL/SQL Lightweight version (iii)--Cursors and exception handling

Tag: Data success ROM cursor AMP employee _id Body while

First, the cursor

　　1. Concept

A cursor is a handle (handle) or pointer to a context . with cursors, PL/SQL can control the context area and what happens to the context area when the statement is processed.

　　2. Cursor Handling

　　　　Processing an explicit cursor

It consists of the following four steps:

　　　　　　1. Defining cursors

 cursor  cursor_name [  (parameter[, parameter  ]  ...) is   select_statement; the cursor parameter can only be an input parameter in the format: Parameter_name   [ "  DataType [ {:= | DEFAULT} expression   "  number  (4 ) , char  (10 ) are all wrong. 

　　　　　　2. Open the cursor

-- is to execute the SELECT statement corresponding to the cursor, place its query results in the workspace, and the pointer points to the header of the workspace, identifying the cursor result collection.  OPEN cursor_name[([Parameter=] value[, [parameter = >] [ value] ...)]; -- parameter support position notation and name notation

　　　　　　3. Extracting cursor data (put into a variable or record type)

FETCH  into | Record_variable};

　　　　　4. Close the cursor

CLOSE cursor_name;

　　　　　Cursor-related properties:

% FOUND Boolean property, TRUE if the record was last read successfully; % NOTFOUND Boolean property, opposite to %FOUND,%ISOPEN Boolean property, returns TRUE when the cursor is open;%ROWCOUNT A numeric property that returns the number of records that have been read from the cursor.

　　　Simple example:--while loop

--Print the wages of all employees on the 80 department doorDECLAREv_sal employees.salary%type; --1. Defining Cursors  CURSOREmp_sal_cursor is SELECTSalary fromEmployeesWHEREdepartment_id=  the;BEGIN  --2. Open the cursor  OPENEmp_sal_cursor; --3. Extracting cursor data (fetching the data pointing to the current cursor)  FETCHEmp_sal_cursor intov_sal; --Loop operation found an iterator-like Hasnext ()   whileEmp_sal_cursor%found LOOP dbms_output.put_line ('Salary:'||v_sal); --take one more time .        FETCHEmp_sal_cursor intov_sal; ENDLOOP; --4. Close the cursor   CLOSEEmp_sal_cursor;END;

　　Of course, you can use multiple variables and even use record types to manipulate them!

　Examples with parameters are as follows:

　　

　　For Loop for cursors:　　　

　　The PL/SQL language provides a cursor for loop statement that automatically executes the function of the cursor's OPEN, FETCH, CLOSE statement, and Loop statement ;
When the loop is entered, the cursor for Loop statement automatically opens the cursor and extracts the first row of cursor data when the program finishes processing the currently fetched number
When the next loop is entered, the cursor for loop automatically extracts the next row of data for the program to process, and when all the results in the result set are fetched
Ends the loop after the data row and automatically closes the cursor.

 for  in cursor_name[value[, Value]...] LOOP--  cursor Data processing code END   for For Index variables in a looping statement to access these column data

　　Example:

--Print the wages of all employees on the 80 department doorDECLAREv_sal employees.salary%type; --1. Defining Cursors  CURSOREmp_sal_cursor is SELECTsalary,employee_id fromEmployeesWHEREdepartment_id=  the;BEGIN   forCinchemp_sal_cursor LOOP Dbms_output.put_line ('Salary:'||C.salary||', employee_id:'||c.employee_id); ENDLOOP;END;

　　　　Handling an implicit cursor

　　Explicit cursors are primarily used to handle query statements, especially in cases where the query results are multiple records, and for non-query statements, such as modify, delete operations, the ORACLE system automatically sets cursors for these operations and creates their workspaces, which are called implicit cursors by the system implicitly created by the cursor. The name of an implicit cursor is SQL, which is defined by the ORACLE system. The operation of an implicit cursor, such as definition, opening, value, and shutdown, is done automatically by the ORACLE system without user processing. The user can only complete the corresponding operation through the related properties of the implicit cursor. In the client area of an implicit cursor, the data that is stored is the data contained in a newly processed SQL statement that is independent of the user-defined display cursor.

SQL%

　　　　Cursor Properties:

SQL%FOUND Boolean property, when the last read record was successfully returned, the value is true;sql%NOTFOUND boolean attribute, with %  ROWCOUNT%ISOPEN Boolean property, the value is always FALSE. SQL command closes implicit cursor immediately after execution

Example:

--Print employee InformationDECLAREv_sal Employees.last_name%type; --&v_id indicates that v_id will be entered by the externalv_id employees.employee_id%Type:= &v_id;BEGIN  UPDATEEmployeesSETSalary=Salary*1.2  WHEREemployee_id=v_id; IFSql%NOTFOUND ThenDbms_output.put_line ('no such person! '); END IF;END;

　　for more cursor correlation, refer to:https://www.cnblogs.com/xcre/archive/2012/03/28/2420597.html

PL/SQL Lightweight version (three)-cursors and exception handling