Shulou(Shulou.com)06/01 Report--

-what is a cursor?

A cursor is a memory workspace of SQL, defined by the system or the user in the form of variables. The purpose of cursors is to temporarily store blocks extracted from the database. In popular terms, a cursor is a result set. Cursors are divided into explicit cursors and implicit cursors.

1. The syntax of the four steps of explicit cursor processing:

1. Define cursor: CURSOR cursor_name [(parameter_name datatype)] IS select_statement

2. Open the cursor: OPEN cursor_name

3. Extract data: FETCH cursor_name INTO variable1 [, variable2,...]

4. Close the cursor: CLOSE cursor_name.

Code demonstration: query the employee number, name and position information of all employees. DECLARE-define cursors CURSOR emp_cursor IS SELECT empno, ename, job FROM emp; v_empno emp.empno%TYPE; v_ename emp.ename%TYPE; v_job emp.job%TYPE;BEGIN-open cursors, execute query OPEN emp_cursor;-extract data LOOP FETCH emp_cursor INTO v_empno, v_ename, v_job DBMS_OUTPUT.PUT_LINE ('employee number:' | | v_empno | |', name'| | v_ename | |', position'| | v_job);-when will you exit the loop? % FOUND,%NOTFOUND EXIT WHEN emp_cursor%NOTFOUND;-- EXIT WHEN NOT emp_cursor%FOUND; END LOOP;-- instructions for closing the cursor CLOSE emp_cursor;END;FETCH statement: 1. Return the record pointed to by the current pointer; 2. Point the pointer to the next record. 2. Four properties of explicit cursors

1.% FOUND: this property is used to detect whether data exists in the cursor result set, and returns TRUE if it does.

2.% NOTFOUND: this property is used to detect whether data exists in the result set, and returns TRUE if it does not exist.

3.% ISOPEN: this property is used to detect whether the cursor has been opened, and returns TURE if it has been opened.

4.% ROWCOUNT: this property is used to return the actual number of rows that have been extracted. (e.g. EXIT WHEN emp_cursor%ROWCOUNT=5;)

Example: raise the salary according to the professional title of the employee, the president increases by 1000 yuan, the manager by 500 yuan, and other employees by 300 yuan.

DECLARE-define cursor CURSOR emp01_cursor IS SELECT empno, job FROM emp01; v_empno emp01.empno%TYPE; v_job emp01.job%TYPE;BEGIN-open cursor, execute query OPEN emp01_cursor;-extract data LOOP FETCH emp01_cursor INTO v_empno, v_job IF v_job = 'PRESIDENT' THEN UPDATE emp01 SET sal = sal + 1000 WHERE empno = vain empnos; ELSIF v_job =' MANAGER' THEN UPDATE emp01 SET sal = sal + 500 WHERE empno = vandalism empnos; ELSE UPDATE emp01 SET sal = sal + 300 WHERE empno = vandalism empnos; END IF When will you exit the cycle? % FOUND,%NOTFOUND EXIT WHEN NOT emp01_cursor%FOUND; END LOOP; COMMIT; CLOSE emp01_cursor;-- syntax for closing cursor END;3, cursor FOR loop a, cursor FOR loop when using a cursor FOR loop, Oracle implicitly opens the cursor, extracts data, and closes the cursor. The syntax is as follows: FOR record_name IN cursor_name (or you can use a subquery) LOOP statement; END LOOP; code demonstration: query the employee's employee number, name, and position. BEGIN FOR emp_record IN (SELECT empno, ename, job FROM emp) LOOP DBMS_OUTPUT.put_line ('employee number:' | | emp_record.empno | |, name'| |', position'| | emp_record.job); END LOOP;END; example: the salary increases according to the professional title of the employee, the president increases by 1000 yuan, the manager increases by 500 yuan, and other employees increase by 300 yuan. By using parameter cursors, different cursor result sets can be generated using different parameter values. DECLARE CURSOR emp_cursor (dno NUMBER) IS SELECT empno, ename, job FROM emp WHERE deptno = dno;BEGIN FOR emp_record IN emp_cursor (10) LOOP DBMS_OUTPUT.put_line ('employee number:' | emp_record.empno | |', name:'| | emp_record.ename | |', position:'| | emp_record.job); END LOOP;END Implicit cursors are cursors implicitly created by the system. Mainly used for non-query statements, such as modify, delete and other operations, then the Oracle system automatically set cursors for these operations and create its workspace, for implicit cursor operations, such as definition, open, value and close operations, are automatically completed by the Oracle system, without the need for user operations. The name of the implicit cursor is SQL, which is defined by the Oracle system. B. details of implicit cursors such as DML operations and single-line SELECT statements use implicit cursors, they are: insert operation: INSERT, update operation: UPDATE, delete operation: DELECT, single-line query operation: SELECT. INTO . When the system uses an implicit cursor, the state and result of the operation can be understood through the properties of the implicit cursor, and then the flow of the program can be controlled. It is important to note that you can always access only the cursor properties of the previous DML operation or single-line SELECT operation through the SQL cursor name. The properties of the implicit cursor are: SQL%FOUNDSQL%NOTFOUNDSQL%ISOPENSQL%ROWCOUNT code demonstration: update the salary of the specified employee according to the employee number entered by the user. Use cursors to modify or delete data 1: lock the extracted data at the row level in order not to change the row being processed (query) by another user, oracle provides a FOR UPDATE clause to lock the selected row. If the current user locks the selected row, other users cannot modify the selected row until the current user data is submitted. Therefore, if you create a cursor that needs to be updated or deleted, it must have a FOR UPDATE clause. The FOR UPDATE clause locks the data extracted from the cursor at the row level so that other users' sessions cannot update the rows of data in the current cursor during the session update. Syntax format: CURSOR cursor_name IS select_statement FOR UPDATE [OF column_reference] [NOWAIT]; UPDATE table_name SET column =. WHERE CURRENT OF cursor_name;DELETE FROM table_name WHERE CURRENT OF cursor_name; example: raise the salary according to the professional title of the employee, the president increases by 1000 yuan, the manager by 500 yuan, and other employees by 300 yuan. 6. Use cursors to modify or delete data 2: deadlock status occurs when other users are modifying the selected row, and the current user also locks the current row. Use the of clause to add row sharing locks on a specific table when the cursor subquery involves multiple tables, if you add a row sharing lock on a particular table, you need to use the of clause. Requirements: enter the department number, display the name of the department and the names of the employees, and delete these employees under the department. DELETE FROM emp01 WHERE CURRENT OF emp01_cursor; END LOOP; COMMIT;END

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.