Oracle Cursor Parsing

Source: Internet
Author: User

Tags: for Oracle result source Lite ora multi-line usage rule

This section explains the cursors in Oracle in detail. The example from this section is from the EMP Table Dept table under the Scott user in Oracle:

First, the cursor: 1, Concept: The essence of a cursor is a result set resultset, mainly used to temporarily store data extracted from the database block. II. Cursor Classification: 1, explicit cursors: User-defined, required actions: Defining cursors, opening cursors, extracting data, closing cursors, mainly for processing of query statements. Property:%found%notfound%isopen%rowcountexample: Print employee information for EMP table
DECLARE  CURSOREmp_cursor is SELECTEmpno,ename,job fromEMP; V_empno Emp.empno%TYPE; V_name Emp.ename%TYPE; V_job Emp.job%TYPE;BEGIN  OPENEmp_cursor; LOOPFETCHEmp_cursor intoV_empno,v_name,v_job; Dbms_output. Put_Line ('Employee number is:'||V_empno||'name is'||V_name||'Position:'||v_job); EXIT  whenEmp_cursor%NOTFOUND; ENDLOOP; CLOSEEmp_cursor;END;

Here strictly according to the writing rules of the display cursor: DECLARE emp_cursor defining the cursor open emp_cursor opening the cursor fetch emp_cursor into ... Extract data close emp_cursor the cursor is closed because the extracted data belongs to multiple lines, so it can be printed via loop loop. Example2: Checks if the cursor is open, if open displays the number of fetched rows
DECLARE  CURSOREmp_cursor is SELECTEmpno,ename,job fromEMP; V_empno Emp.empno%TYPE; V_name Emp.ename%TYPE; V_job Emp.job%TYPE;BEGIN  OPENEmp_cursor; LOOPFETCHEmp_cursor intoV_empno,v_name,v_job; EXIT  whenEmp_cursor%NOTFOUND; ENDLOOP; IFEmp_cursor%ISOPEN ThenDbms_output. Put_Line ('cursor is open'); Dbms_output. Put_Line ('Read the'||Emp_cursor%ROWCOUNT||'Line'); ELSEDbms_output. Put_Line ('Cursor not open'); END IF; CLOSEEmp_cursor;END;

The%isopen property determines whether the cursor is open and%rowcount determines the number of rows fetched. 2, implicit cursor: defined by the system and create a work area for it, and the implicit definition open extraction close, the cursor name of the implicit cursor is ' SQL ', the property and the display cursor is the same, mainly for the single-line SELECT statement or DML operation processing. Example: The user enters the employee number to modify the employee's salary, if successful, the print output success sign. To try not to change the original table, create a new table emp_new the same as the original table data:
CREATE TABLE emp_new  as SELECT *  from EMP;
BEGIN  UPDATEEmp_newSETSal=Sal+ - WHEREEmpno=&empno; IFSql%FOUND ThenDbms_output. Put_Line ('successfully modified'); COMMIT; ELSEDbms_output. Put_Line ('Modification Failed'); ROLLBACK; END IF;END;

Note here to delete the changes to do after the commit commit, if the operation fails, rollback rollback just now. 3. Parameter cursor: A cursor that joins a parameter when a cursor is defined, which can be used with a cursor for loop to quickly find the required data. Here's a look at the cursor for loop A, the cursor for loop: Implicit execution of open fetch closed data, the code is much leaner. Expression:for Table_record in Table_cursor LOOP STATEMENT; END LOOP; Example: Use the cursor for loop to print out employee information:
DECLARECURSOREmp_cursor is SELECTEmpno,ename,job fromEMP;BEGIN   forEmp_recordinchemp_cursor LOOP dbms_output. Put_Line ('Employee Number:'||Emp_record.empno||'Employee Name'||Emp_record.ename||'Staff Positions'||emp_record.job); ENDLOOP;END;

Here the cursor for loop eliminates the naming and assignment of variables for the data taken, and the code is much leaner if all printing does not have to write loop conditions. If you want to make your code more streamlined, you can remove the cursor declaration by introducing a subquery, as follows.
BEGIN   forEmp_recordinch(SELECTEmpno,ename,job fromEMP) LOOP Dbms_output. Put_Line ('Employee Number:'||Emp_record.empno||'Employee Name'||Emp_record.ename||'Staff Positions'||emp_record.job); ENDLOOP;END;
The code is more streamlined and the results are the same. is not a bit like an implicit cursor, but an implicit cursor is primarily used for the operation of single-line select and DML statements, noting the difference between 2 usage. The following continues the instance of the parameter cursor: Example: Enter the department number to print employee information:
DECLARECURSOREmp_cursor (DNO Number) is SELECTEmpno,ename,job fromEmpWHEREDeptno=DNO;BEGIN   forEmp_recordinchEmp_cursor (&DNO) LOOP Dbms_output. Put_Line ('Employee Number'||Emp_record.empno||'name'||Emp_record.ename||'Job Title'||emp_record.job); ENDLOOP;END;

Since there are parameters, there is bound to be a declaration of the cursor, which can quickly find the required data in conjunction with the cursor for loop. Iii. considerations for modifying Data using Cursors 1. When modifying data using cursors, Oracle provides a FOR UPDATE clause to lock the data to prevent others from modifying it when they manipulate the data themselves. Also, when you use Update or delete, you must use the WHERE current Of+name_cursor statement, and at the end, remember to commit. If it is a cascade operation, you can use the for update of to locking the related table. EXAMPLE1: President Employees plus 1000 salary, manager plus 500 salary
CREATE TABLE emp_new  as SELECT *  from EMP;
DECLARECURSOREmpnew_cursor is SELECTEname,job fromEmp_new for UPDATE;BEGIN   forEmpnew_recordinchempnew_cursor LOOP dbms_output. Put_Line ('name'||Empnew_record.ename||'Job Title'||empnew_record.job); IFEmpnew_record.job='President'  Then      UPDATEEmp_newSETSal=Sal+ + WHERE  Current  ofEmpnew_cursor; elsif empnew_record.job='MANAGER'  Then      UPDATEEmp_newSETSal=Sal+ - WHERE  Current  ofEmpnew_cursor; END IF; ENDLOOP; COMMIT;END;
SELECT *  from WHERE inch ('President','MANAGER'); SELECT *  from WHERE inch ('President','MANAGER');

You can see that there is a corresponding change in wages here.                                            At this point, the Oracle cursor parsing is complete, all in all, the cursor is just as part of the data we extracted from the database, we do a series of operations on this result set. 2018-09-07 16:15:34

Oracle Cursor Parsing

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

Tags Index: