dynamic sql.sql

-- DYNAMIC SQL
-- CREATE TABLE -- SELECT 
--SELECT * FROM JOBS;
DECLARE
V_SQL VARCHAR2(1028);
V_TABLE VARCHAR2(50) := 'JOB_TEMP';
V_JID JOBS.JOB_ID%TYPE;
V_JT JOBS.JOB_TITLE%TYPE;
V_MIS JOBS.MIN_SALARY%TYPE;
V_MAS JOBS.MAX_SALARY%TYPE;
TYPE JLIST IS TABLE OF JOBS%ROWTYPE;
JL JLIST;
JI JOBS%ROWTYPE;
N NUMBER(1) := 0;  
TYPE SLIST IS TABLE OF JOBS.MIN_SALARY%TYPE;
SL SLIST;

TYPE VC IS REF CURSOR;
C VC;
BEGIN
    -- DROP TABLE
   V_SQL := 'DROP TABLE '||V_TABLE;
   SELECT COUNT(*) INTO N FROM USER_TABLES UT WHERE UT.TABLE_NAME = V_TABLE;
   IF N = 1 THEN
      EXECUTE IMMEDIATE V_SQL;
   END IF;   
   
 
  -- CREATE TBLE
  BEGIN
  V_SQL := 'CREATE TABLE '||V_TABLE||' AS SELECT * FROM JOBS WHERE 1 = 2';
  EXECUTE IMMEDIATE V_SQL;
  END;
    
  -- INSERT INTO TABLE
  BEGIN
  FOR J IN (SELECT * FROM JOBS O WHERE O.MIN_SALARY <5000)LOOP
    V_JID := J.JOB_ID;
    V_JT:=UPPER(J.JOB_TITLE);
    V_MIS:=J.MIN_SALARY*1.1;
    V_MAS:= J.MAX_SALARY*1.1;
    V_SQL := 'BEGIN TEST_UTILITY.INSERT_JOB_TABLE(P_ID => :1,
                                           P_TITLE    => :2,
                                           P_MIN      => :3,
                                           P_MAX      => :4,
                                           TABLE_NAME => :5); END;';
                                           
                                           
    EXECUTE IMMEDIATE V_SQL 
    USING V_JID,V_JT,V_MIS,V_MAS,V_TABLE;
  END LOOP;
  END;
  
  --BULK COLLECT INTO IN DYNAMIC SQL
  BEGIN  
  V_SQL := 'SELECT * FROM '||V_TABLE|| ' WHERE MIN_SALARY < :1';
  EXECUTE IMMEDIATE V_SQL
  BULK COLLECT INTO JL USING IN 5000; 
  -- TEST
  FOR i IN JL.FIRST .. JL.LAST LOOP
    DBMS_OUTPUT.put_line(JL(I).JOB_TITLE);  
  END LOOP;
  END;
  
  -- SELECT INTO SINGLE LINE MAX MIN_SAL
  BEGIN
    V_SQL := 'SELECT MAX(MIN_SALARY) FROM '||V_TABLE;
    EXECUTE IMMEDIATE V_SQL INTO V_MIS;
  
    EXECUTE IMMEDIATE 'SELECT * FROM '||V_TABLE||' WHERE MIN_SALARY = :1'
    INTO JI USING IN V_MIS;
      
    DBMS_OUTPUT.put_line('SELECT '||JI.JOB_ID||' '||JI.MIN_SALARY);
  END;
  
  
  --delete record WITH RETURNING INTO
  BEGIN
    V_SQL := 'SELECT MIN(MIN_SALARY) FROM '||V_TABLE;
    EXECUTE IMMEDIATE V_SQL INTO V_MIS;
  
    v_sql := 'DELETE FROM '||V_TABLE|| ' WHERE MIN_SALARY = :1 RETURNING JOB_TITLE INTO :2';
    execute immediate v_sql
    USING IN V_MIS
    returning into V_JT; 
  
    DBMS_OUTPUT.put_line('DELETED '||V_JT);
  END;
  
    
  
  --update record with return into
  BEGIN
     V_SQL := 'UPDATE '||V_TABLE||' SET MAX_SALARY = MAX_SALARY * :1';
     EXECUTE IMMEDIATE V_SQL ||' RETURNING MAX_SALARY INTO :2'
     USING 1.2
     RETURNING BULK COLLECT INTO SL;
     
     FOR I IN SL.FIRST .. SL.LAST LOOP
       DBMS_OUTPUT.put_line(SL(I));
     END LOOP;  
  
  
  END; 
  
  -- REF cursor
  V_SQL := 'SELECT * FROM '||V_TABLE ||' WHERE MIN_SALARY < :1';
  OPEN C FOR V_SQL USING 3000;
  
  FETCH C BULK COLLECT INTO JL;
  FOR I IN JL.FIRST .. JL.LAST LOOP
    DBMS_OUTPUT.put_line(JL(I).JOB_TITLE||' '||JL(I).MIN_SALARY);
  END LOOP;
  
  CLOSE C;
  
  V_SQL := 'DROP TABLE '||V_TABLE;
  EXECUTE IMMEDIATE V_SQL;
  
END;
/
--SELECT * FROM JOB_TEMP;
--DROP TABLE JOB_TEMP;
-- RETURNING INTO ON STATIC SQL
/*DECLARE
V_JI JOBS%ROWTYPE;
TYPE JL IS TABLE OF JOBS%ROWTYPE;
JLIST JL;
V_SQL VARCHAR2(1024); 
BEGIN
  UPDATE JOB_TEMP SET MAX_SALARY = 5000 WHERE MIN_SALARY = 3300 RETURNING JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY INTO V_JI;
  
  UPDATE JOB_TEMP SET MAX_SALARY = 0 RETURNING JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY  BULK COLLECT INTO JLIST;
END;
 */