Updating two different tables with cursor oracle
In this scenario, the cursor result set can be limited using the traditional When we associate a SELECT statement with more than one table joined together to a cursor with a FOR UPDATE clause, we end up locking all the tables in the FROM clause of the SELECT statement, where we just need to lock a single table for our purpose.
The FOR UPDATE OF clause helps us in locking up the intended table rather all available tables.
Oracle defines an execution cycle to execute an SQL statement and associates a cursor with it.Single record and single column declare v_ename varchar2(50); Cursor Cur_ename is select empname from emp where empno = 10; begin Open Cur_ename; Fetch Cur_ename into v_ename; dbms_output.put_line(‘Employee Name –Bill In general case cursors are intended for multi-record queries.Just given above example for illustration purpose only. Multiple record and single column declare v_ename varchar2(20); Cursor Cur_ename is select empname from emp; begin Open Cur_ename; loop Fetch Cur_ename into v_ename; exit when Cur_ename%notfound; dbms_output.put_line(‘Employee Name –Paul This is a classic example.In case you want to read the previous post – Oracle Cursor – Basics All examples are tested in Oracle 11g Release 2 version.I used the classic DEPT and EMP tables to illustrate the examples SQL 1.
Search for updating two different tables with cursor oracle:
The following illustration shows the execution cycle of an explicit cursor: Let’s examine each step in detail.