Not original
-------------------------------------------------- -------------------
From the sky
Using the query in the cursor for loop can define a query in the cursor for loop. Since there is no explicit declaration, the cursor has no name, the record name is defined by the cursor query.
DECALRE v_tot_salary EMP.SALARY% TYPE; BEGIN FOR r_dept IN (SELECT deptno, dname FROM dept ORDER BY deptno) LOOP DBMS_OUTPUT.PUT_LINE ( 'Department:' || r_dept.deptno || '-' || r_dept.dname); v_tot_salary : = 0; FOR r_emp IN (SELECT ename, salary FROM emp WHERE deptno = p_dept ORDER BY ename) LOOP DBMS_OUTPUT.PUT_LINE ( 'Name:' || v_ename || 'salary:' || v_salary); v_tot_salary: = v_tot_salary v_salary; end loop; dbms_output.put_line ('Toltal Salary for DEPT:' || V_TOT_SALARY); end loop;
Subproof in the cursor
The syntax is as follows:
Cursor c1 is select * from Empwhere Deptno Not in (Select Deptnofrom Deptwhere DNAME! = 'Accounting');
It can be seen that there is no difference between subqueries in SQL.
Update and deletion in the cursor
Update and delete statements can be updated or deleted in PL / SQL. Explicit cursors are only used in the case where multi-line data is needed. PL / SQL provides a way to perform delete or update records only using a cursor.
The WHERE CURRENT OF sub-string in the UPDATE or DELETE statement depends on the most recent data taken in the table to perform the Update or Delete operation. To use this method, you must use the for Update substring when the cursor is declared. When the conversation uses the for Update substring to open a camper, all the data rows that return to the set will be in a row-level, other objects. You can only query these data lines, you cannot perform Update, DELETE, or SELECT ... for Update operations.
grammar:
For update [of [Schema.] Table.Column [, [Schema.] Table.Column] .. [NOWAIT]
In multi-table queries, use the OF clause to lock specific tables, if the OF classes are ignored, then the data rows selected in all tables will be locked. If these data lines have been locked by other sessions, Oracle will wait until the data line is unlocked until the data line is unlocked.
The syntax using the WHERE CURRENT OF sub-string in Update and Delete is as follows:
Where {current of cursor_name | Search_Condition}
example:
DELCARECURSOR c1 IS SELECT empno, salaryFROM empWHERE comm IS NULLFOR UPDATE OF comm; v_comm NUMBER (10,2); BEGINFOR r1 IN c1 LOOPIF r1.salary <500 THENv_comm: = r1.salary * 0.25; ELSEIF r1.salary <1000 THENv_comm: = r1.salary * 0.20; Elseif R1.SAlary <3000 Thenv_comm: = r1.salary * 0.15; Elsev_comm: = r1.salary * 0.12; end if; update Emp; SET COMM = V_commwhere Current of C1L; end loop; end - -------------------------------------------------- ----------------------------------------------- through the cursor The data extracted in the work area can manipulate the data in the database, including modification and deletion. If you want to use the vernacker, you must add the for update of clause when defining the cursor; and when update or delete, you must add the WHERE CURRENT OF clause, the row is updated or deleted. A For Update clause will give the row to get a row of his lock.
