Two identical queries to demonstrate the impact of changes : Cursor Value « Cursor « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Cursor
  3. Cursor Value

Two identical queries to demonstrate the impact of changes

    
SQL> CREATE TABLE emp (
  2    id         NUMBER PRIMARY KEY,
  3    fname VARCHAR2(50),
  4    lname  VARCHAR2(50)
  5  );

Table created.

SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (1, 'A', 'B');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (2, 'C', 'D');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (3, 'Enn', 'F');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (4, 'G', 'H');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (5, 'G', 'Z');

1 row created.

SQL>
SQL>
SQL> SET SERVEROUTPUT ON ESCAPE OFF
SQL>
SQL> DECLARE
  2
  3     fnameValue emp.fname%TYPE;
  4
  5     
  6
  7     CURSOR emp_cur1 IS SELECT fname FROM emp WHERE fname like 'Ron%';
  8
  9     CURSOR emp_cur2 IS SELECT fname FROM emp WHERE fname like 'Ron%';
 10
 11  BEGIN
 12
 13     OPEN emp_cur1;
 14     UPDATE emp SET fname = 'AAA' WHERE fname = 'Ron';
 15
 16     OPEN emp_cur2;
 17
 18     FETCH emp_cur1 INTO fnameValue;
 19     DBMS_OUTPUT.PUT_LINE(fnameValue);
 20
 21     FETCH emp_cur2 INTO fnameValue;
 22     DBMS_OUTPUT.PUT_LINE(fnameValue);
 23
 24     CLOSE emp_cur1;
 25     CLOSE emp_cur2;
 26
 27     ROLLBACK;
 28
 29  END;
 30  /

PL/SQL procedure successfully completed.

SQL> drop table emp;

Table dropped.








Related examples in the same category

1.Cursor with a single value
2.Update statement with cursor variable
3.Server-side cursor variables.
4.A cursor FOR loop.
5.Cursor Variable Example 2
6.Read full table data from a cursor
7.Single column cursor
8.Use Complex cursor to simplify the pl/sql logic
9.Use cursor to do full table scan
10.Use data referenced by cursor to update table
11.Use number functions when creating a cursor value
12.Form sentences from database data