Update the emp table, converting emp names to uppercase : Update « Insert Update Delete « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Insert Update Delete
3. Update

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> SET SERVEROUTPUT ON
SQL>
SQL>
SQL> DECLARE
  2
  3     v_rowid ROWID;
  4     v_rowcount NUMBER := 0;
  5
  6     CURSOR emp_cur1 IS SELECT rowid FROM emp WHERE id > 50;
  7
  8     CURSOR emp_cur2 IS SELECT rowid FROM emp WHERE id > 50;
  9
 10  BEGIN
 11
 12     OPEN emp_cur1;
 13
 14     DELETE FROM emp WHERE id > 50;
 15
 16     OPEN emp_cur2;
 17
 18     
 19     FETCH emp_cur1 INTO v_rowid;
 20     IF emp_cur1%ROWCOUNT > 0
 21     THEN
 22        DBMS_OUTPUT.PUT_LINE('Cursor 1 includes the deleted rows');
 23     ELSE
 24        DBMS_OUTPUT.PUT_LINE('Cursor 1 does not include the deleted rows');
 25     END IF;
 26
 27     v_rowcount := 0;
 28     
 29     FETCH emp_cur2 INTO v_rowid;
 30     IF emp_cur2%ROWCOUNT > 0
 31     THEN
 32        DBMS_OUTPUT.PUT_LINE('Cursor 2 includes the deleted rows');
 33     ELSE
 34        DBMS_OUTPUT.PUT_LINE('Cursor 2 does not include the deleted rows');
 35     END IF;
 36
 37     CLOSE emp_cur1;
 38     CLOSE emp_cur2;
 39
 40     ROLLBACK;
 41
 42  EXCEPTION
 43     WHEN OTHERS
 44     THEN
 45        DBMS_OUTPUT.PUT_LINE(sqlerrm);
 46  END;
 47  /
Cursor 1 does not include the deleted rows
Cursor 2 does not include the deleted rows

PL/SQL procedure successfully completed.

SQL>
SQL> SET ESCAPE OFF
SQL>
SQL> drop table emp;

Table dropped.

SQL>