DYNAMIC SQL:
=============
--> DYNAMIC SQL IS A PROGRAMMING TECHNIQUE TO BUILD SQL STATEMENTS AT RUNTIME.
-->Both compilation and execution happens at run time.It will be useful when we don't the values (of column or table name)till run time.
EX:
DROP TABLE EMP1;------------->STATIC SQL
SELECT * FROM EMP1; --STATIC SQL
-->Will compile at the compilation time and execute at run time.
EX:
*****
TNAME = '&TNAME '
DROP TABLE TNAME;-------> DYNAMIC
-->DYNAMIC SQL IS USEFUL WHEN WE DON'T KNOW TABLE NAME,COLUMN NAME UNTILL RUNTIME.
-->DYNAMIC SQL COMMANDS (DDL,DML,DQL,DCL) CAN BE EXECUTED BY USING
"EXECUTE IMMEDIATE" STATEMENT.
Methods of implementing dynamic SQL:
************************************\
EXECUTE IMMEDIATE:
===================
-->We have to use "execute immediate" for executing DDL commands inside PL/SQL.
-->If we want to execute SQL data definition such as CREATE.ALTER,DROP,TRUNCATE and data control statement like GRANT/REVOKE.
-->We can not directly use DDL statements like TRUNCATE/ALTER/DROP.
--> THIS STATEMENT IS USED TO EXECUTE DDL COMMANDS (OR) DYNAMIC SQL COMMAND.
SYNTAX:
=======
EXECUTE IMMEDIATE 'DYNAMIC SQL COMMAND';
--CREATE TABLE
/
BEGIN
CREATE TABLE XX_EMP(EMPNO NUMBER,ENAME VARCHAR2(50));
END;
--It will throw the error.
/
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE XX_EMP(EMPNO NUMBER,ENAME VARCHAR2(50))';
END;
/
DESC XX_EMP;
/
INSERT INTO XX_EMP VALUES (1,'ABC');
INSERT INTO XX_EMP VALUES (2,'DEF');
COMMIT;
--TRUNCATE TABLE
BEGIN
TRUNCATE TABLE XX_EMP;
END;
--IT WILL THROW THE ERROR
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE XX_EMP';
END;
/
--DCL COMMANDS
BEGIN
GRANT SELECT ON EMP TO HR;
END;
/
--GRANT
BEGIN
EXECUTE IMMEDIATE 'GRANT SELECT ON EMP TO HR';
END;
/
BEGIN
EXECUTE IMMEDIATE 'REVOKE SELECT ON EMP FROM HR';
END;
/
--CREATE TABLE
DECLARE
LV_TAB_NAME VARCHAR2(30) :='XX_DEMO';
COL1 VARCHAR2(30) :='SNO NUMBER';
col2 varchar2(30) :='SNAME VARCHAR2(50)';
SQL_STAT VARCHAR2(300);
BEGIN
SQL_STAT := ' CREATE TABLE '||LV_TAB_NAME||'('||COL1||','||COL2||')';
DBMS_OUTPUT.PUT_LINE(SQL_STAT);
EXECUTE IMMEDIATE SQL_STAT;
END;
/
--TRUNCATE
DECLARE
V_TAB_NAME VARCHAR2(20) :='EMP1';
BEGIN
TRUNCATE TABLE V_TAB;
END;
/
DECLARE
V_TAB_NAME VARCHAR2(20) :='EMP1';
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE '||V_TAB_NAME;
END;
/
--ALTER
DECLARE
V_TAB_NAME VARCHAR2(20) :='EMP1';
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE '||V_TAB_NAME||' MODIFY ENAME VARCHAR2(15)';
END;
/
-- DELETE
DECLARE
V_TAB VARCHAR2(50):='EMP1';
BEGIN
DELETE FROM ||V_TAB||WHERE EMPNO=7369;
END;
/
--IT WILL THROW THE ERROR.
/
DECLARE
V_TAB VARCHAR2(50):='EMP1';
BEGIN
EXECUTE IMMEDIATE ' DELETE FROM ' ||V_TAB||' WHERE EMPNO=7369 ';
END;
/
DECLARE
V_TAB VARCHAR2(50):='EMP1';
SQL_STAT VARCHAR2(300);
BEGIN
SQL_STAT := ' DELETE FROM ' ||V_TAB||' WHERE EMPNO=100 ';
DBMS_OUTPUT.PUT_LINE(SQL_STAT);
EXECUTE IMMEDIATE SQL_STAT;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('DELETED RECORDS SUCCESFULLY');
ELSE
DBMS_OUTPUT.PUT_LINE('DELETED RECORDS NOT SUCCESFULLY');
END IF;
END;
/
SELECT * FROM EMP1;
/
ROLLBACK;
/
create or replace procedure p1(p_empno number)
is
V_TAB VARCHAR2(50):='EMP1';
BEGIN
execute immediate 'DELETE FROM ' ||V_TAB||' WHERE EMPNO='||p_empno||' ';
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('DELETED RECORDS SUCCESFULLY');
ELSE
DBMS_OUTPUT.PUT_LINE('DELETED RECORDS NOT SUCCESFULLY');
END IF;
END;
/
EXEC P1(7000);
/
exec p1(7369);
/
create table emp2 as select * from emp;
/
create table emp3 as select * from emp;
/
create table emp4 as select * from emp;
/
CREATE OR REPLACE PROCEDURE P_DELETE(V_TAB IN VARCHAR2,P_EMPNO NUMBER)
IS
BEGIN
EXECUTE IMMEDIATE ' DELETE FROM '||V_TAB|| ' WHERE EMPNO='||p_empno||' ';
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('DELETED RECORDS SUCCESFULLY');
ELSE
DBMS_OUTPUT.PUT_LINE('DELETED RECORDS NOT SUCCESFULLY');
END IF;
END;
/
select * from emp4 where empno=7369;
/
exec P_DELETE('EMP4',7369);
/
DECLARE
V_TAB VARCHAR2(50):='EMP1';
V_COL_NAME VARCHAR2(50):='EMPNO';
BEGIN
DELETE FROM ||V_TAB|| WHERE ||V_COL_NAME|| =7369;
END;
/
--IT WILL THROW THE ERROR
/
DECLARE
V_TAB VARCHAR2(50):='EMP1';
V_COL_NAME VARCHAR2(50):='EMPNO';
BEGIN
EXECUTE IMMEDIATE ' DELETE FROM ' ||V_TAB|| ' WHERE ' ||V_COL_NAME|| ' =7369 ';
END;
/
DECLARE
V_DELETE VARCHAR2(100):='DELETE FROM';
V_TAB VARCHAR2(10):=' EMP1';
V_WHERE VARCHAR2(120):=' WHERE EMPNO=7369 ';
V_STMT VARCHAR2(100);
BEGIN
V_STMT:=V_DELETE||V_TAB||V_WHERE;
DBMS_OUTPUT.PUT_LINE(V_STMT);
EXECUTE IMMEDIATE V_STMT;
END;
/
EX:
CREATE A STORED PROCEDURE TO DROP A TABLE AT RUNTIME?
SOL:
CREATE OR REPLACE PROCEDURE DROP_TABLE(N IN VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE '||' '||N;
END;
/
TESTING:
EXECUTE DROP_TABLE('TEST');
(OR)
CREATE OR REPLACE PROCEDURE DROP_TABLE(N IN VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE '||' '||N||' '||'PURGE';
END;
TESTING:
EXECUTE DROP_TABLE('TEST');
/
create or replace procedure p1(p_tbl_name varchar2)
is
begin
execute immediate 'truncate table'||' '||p_tbl_name;
end;
/
exec p1('DEPT1');
/
EX:
CREATE A STORED PROCEDURE TO DROP ANY OBJECT AT RUNTIME?
EX:
DROP TABLE <TABLE NAME>;
DROP VIEW <VIEW NAME>;
DROP SEQUENCE <SEQUENCE NAME>;
DROP INDEX <INDEX NAME>;
DROP SYNONYM <SYNONYM NAME>;
SOL:
CREATE OR REPLACE PROCEDURE DROP_OBJ(P_OBJ_TYPE IN VARCHAR2,P_OBJ_NAME IN VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE 'DROP'||' '||P_OBJ_TYPE||' '||P_OBJ_NAME;
END;
/
TESTING:
EXECUTE DROP_OBJ('TABLE','TEST');
EXECUTE DROP_OBJ('VIEW','V1');
/
CREATE TABLE EMP10 AS SELECT * FROM EMP;
/
CREATE TABLE EMP100 AS SELECT * FROM EMP;
/
CREATE OR REPLACE PROCEDURE P1
IS
CURSOR C1 IS SELECT OBJECT_TYPE,OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_NAME IN ('EMP10','EMP101');
BEGIN
FOR I IN C1
LOOP
EXECUTE IMMEDIATE 'DROP'||' '||I.OBJECT_TYPE||' '||I.OBJECT_NAME;
END LOOP;
END;
/
EXEC P1;
/
CREATE TABLE EMP1 AS SELECT * FROM EMP;
/
CREATE TABLE EMP2 AS SELECT * FROM EMP;
/
CREATE TABLE EMP3 AS SELECT * FROM EMP;
/
CREATE TABLE EMP4 AS SELECT * FROM EMP;
/
CREATE TABLE EMP5 AS SELECT * FROM EMP;
/
CREATE OR REPLACE PROCEDURE DROP_OBJ(P_OBJ_TYPE IN VARCHAR2,P_OBJ_NAME IN VARCHAR2)
IS
CURSOR C1 IS SELECT OBJECT_TYPE,OBJECT_NAME FROM DBA_OBJECTS
WHERE OBJECT_TYPE=P_OBJ_TYPE AND OBJECT_NAME=P_OBJ_NAME;
BEGIN
FOR I IN C1
LOOP
EXECUTE IMMEDIATE 'DROP'||' '||I.OBJECT_TYPE||' '||I.OBJECT_NAME;
END LOOP;
END;
/
SELECT *
FROM ALL_OBJECTS
WHERE OBJECT_NAME='EMP5';
/
CREATE OR REPLACE PROCEDURE P_GEN_SEQ(P_SEQ_NAME IN VARCHAR2,START_VAR IN VARCHAR2,INCR_BY IN VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE ' CREATE SEQUENCE '|| P_SEQ_NAME ||' START WITH '||START_VAR||' INCREMENT BY '||INCR_BY;
END;
/
EXEC P_GEN_SEQ('DEMO_SEQ',100,1);
/
CREATE OR REPLACE PROCEDURE P_GEN_SEQ(P_SEQ_NAME IN VARCHAR2,START_VAR IN VARCHAR2,INCR_BY IN VARCHAR2,P_MAX_VAL IN NUMBER)
IS
SQL_STAT VARCHAR2(1000);
BEGIN
SQL_STAT := ' CREATE SEQUENCE '|| P_SEQ_NAME ||' START WITH '||START_VAR||' INCREMENT BY '||INCR_BY||' MAXVALUE '||P_MAX_VAL;
dbms_output.put_line(SQL_STAT);
EXECUTE IMMEDIATE SQL_STAT;
END;
/
CREATE SEQUENCE XYZ START WITH 1 INCREMENT BY 1 MAX VALUE 1000
/
EXEC P_GEN_SEQ('XYZ',1,1,1000);
/
SELECT *
FROM ALL_SEQUENCES
WHERE SEQUENCE_NAME='XYZ'
/
DECLARE
P_TABLE VARCHAR2(30):='EMP1';
BEGIN
EXECUTE IMMEDIATE ' UPDATE '||P_TABLE|| ' SET SAL =:B WHERE DEPTNO=:B' USING 6000,10;
END;
/
DECLARE
LV_TAB_NAME VARCHAR2(30):='EMP';
LV_EMP_NO NUMBER :=7369;
LV_EMP_DATA EMP%ROWTYPE;
SQL_STMT VARCHAR2(300);
BEGIN
SQL_STMT :=' SELECT * FROM '||LV_TAB_NAME||' WHERE EMPNO='||LV_EMP_NO;
DBMS_OUTPUT.PUT_LINE(SQL_STMT);
EXECUTE IMMEDIATE SQL_STMT INTO LV_EMP_DATA;
DBMS_OUTPUT.PUT_LINE(LV_EMP_DATA.ENAME||','||LV_EMP_DATA.SAL);
END;
/
No comments:
Post a Comment