SQL Support in PL/SQL

PL/SQL fully supports Data Manupulation statement (excluding EXPLAIN PLAN), transactional control statement, funtion, pseudocolumn and operators.

In addition to static SQL, PL/SQL also support dynamic SQL, which enables to execute Data defination, data control and session controla statements.

Data Manupulation with PL/SQL

CREATE TABLE emp_temp (
      empno         NUMBER(4) PRIMARY KEY,
      first_name    VARCHAR2(20),
      last_name     VARCHAR2(20)
      );
    
DECLARE
      v_first_name    emp_temp.first_name%TYPE;
      v_last_name     emp_temp.last_name%TYPE;
BEGIN
      INSERT INTO emp_temp ( empno,first_name,last_name)
                VALUES       (9999,'JOHN',  'ABRAHAM');
      DBMS_OUTPUT.PUT_LINE( 'SQL count after insert statement  - ' || SQL%ROWCOUNT);
                
      UPDATE emp_temp SET first_name = 'SANJAY',last_name   = 'DUTT'
                     WHERE empno      = 9999;

      DBMS_OUTPUT.PUT_LINE( 'SQL count after update statement  - ' || SQL%ROWCOUNT);          
    
      DELETE FROM emp_temp WHERE empno = 9999
      RETURNING first_name, last_name INTO v_first_name,v_last_name;

      DBMS_OUTPUT.PUT_LINE( 'SQL count after delete statement  - ' || SQL%ROWCOUNT);
    
      COMMIT;
    
      DBMS_OUTPUT.PUT_LINE( 'SQL count after commit statement  - ' || SQL%ROWCOUNT);
      DBMS_OUTPUT.put_line( v_first_name ||'  '||v_last_name || ' record deleted from emp_temp table'  );
EXCEPTION
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE( 'SQL Error - ' || sqlerrm);
END;

Output :

SQL count after insert statement  - 1
SQL count after update statement  - 1
SQL count after delete statement  - 1
SQL count after commit statement  - 0
SANJAY  DUTT record deleted from emp_temp table

    
DROP TABLE emp_temp;

 Pseudocolumn in PL/SQL

CREATE TABLE emp_temp (
      empno         NUMBER(4) PRIMARY KEY,
      first_name    VARCHAR2(20),
      last_name     VARCHAR2(20)
      );
 
CREATE  SEQUENCE empno_seq
       START  WITH 1
       INCREMENT BY 1;
      
     
DECLARE
      v_first_name    emp_temp.first_name%TYPE;
      v_last_name     emp_temp.last_name%TYPE;
      v_empno         emp_temp.empno%TYPE;
      v_rowid           varchar2(18);
BEGIN
      SELECT empno_seq.NEXTVAL INTO v_empno FROM DUAL;
      DBMS_OUTPUT.PUT_LINE ('Next Value : '||v_empno);

      SELECT empno_seq.CURRVAL INTO v_empno FROM DUAL;
      DBMS_OUTPUT.PUT_LINE ('Current Value : '||v_empno);
     
      INSERT INTO emp_temp ( empno,first_name,last_name)
             VALUES(empno_seq.nextval,'Sanjay','Dutt');
     
      SELECT ROWIDTOCHAR(rowid), empno,first_name,last_name
             INTO v_rowid,v_empno,v_first_name,v_last_name
      FROM emp_temp
      WHERE ROWNUM < 2;
     
      DBMS_OUTPUT.PUT_LINE (v_first_name ||' ' || v_last_name ||' EMP No ' || v_empno  || ' ROWID : ' || v_rowid);
     
      ROLLBACK;
EXCEPTION
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE( 'SQL Error - ' || sqlerrm);
END;

Result :
       Next Value : 1
       Current Value : 1
       Sanjay Dutt EMP No 2 ROWID : AAAM1IAAEAAAAG8AAA
     
DROP TABLE emp_temp;

DROP SEQUENCE empno_seq;