Oracle PL/SQL - Static SQL Statements

Introduction

In the following code, a PL/SQL anonymous block declares three PL/SQL variables and uses them in the static SQL statements INSERT, UPDATE, DELETE.

The block also uses the static SQL statement COMMIT.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- www  .  jav  a  2s. c  o m

SQL> CREATE TABLE emp(
  2  empid NUMBER(6),
  3  first_name VARCHAR2(20),
  4  last_name VARCHAR2(25),
  5  email VARCHAR2(25),
  6  phone_number VARCHAR2(20),
  7  hire_date DATE,
  8  job_id VARCHAR2(10),
  9  salary NUMBER(8,2),
 10  commission_pct NUMBER(2,2),
 11  manager_id NUMBER(6),
 12  department_id NUMBER(4)) ;
SQL>
SQL> INSERT INTO emp VALUES( 100, 'Steven', 'King', 'SKING', '123.123.4567', TO_DATE('17-JUN-1987', 'dd-MON-yyyy'), 'CODER', 24000, NULL, NULL, 90);
SQL>
SQL> DROP TABLE emp_temp;

SQL> CREATE TABLE emp_temp AS
  2    SELECT empid, first_name, last_name
  3    FROM emp;
SQL>
SQL> DECLARE
  2    emp_id          emp_temp.empid%TYPE := 299;
  3    emp_first_name  emp_temp.first_name%TYPE  := 'Bob';
  4    emp_last_name   emp_temp.last_name%TYPE   := 'Henry';
  5  BEGIN
  6    INSERT INTO emp_temp (empid, first_name, last_name)
  7    VALUES (emp_id, emp_first_name, emp_last_name);
  8
  9    UPDATE emp_temp
 10    SET first_name = 'Robert'
 11    WHERE empid = emp_id;
 12
 13    DELETE FROM emp_temp
 14    WHERE empid = emp_id
 15    RETURNING first_name, last_name
 16    INTO emp_first_name, emp_last_name;
 17
 18    COMMIT;
 19    DBMS_OUTPUT.PUT_LINE (emp_first_name || ' ' || emp_last_name);
 20  END;
 21   /
Robert Henry

PL/SQL procedure successfully completed.

SQL>