Execute Immediate - What is Static SQL and Native Dynamic SQL

 
--====================================================
EXECUTE IMMEDIATE statement
--====================================================
 
SQL:-
 
--==========================================================================
STATIC SQL :- a statement which does not change during runtime.
--==========================================================================
 
EX:-
 
select * from xx_emp
 
DECLARE
V_COUNT NUMBER;
BEGIN
select COUNT(*) INTO V_COUNT from xx_emp where deptno = 10;
dbms_output.put_line ('count is : '|| V_COUNT);
END;
 
 
update xx_emp set Comm = 100 where deptno = 10;
 
DECLARE
BEGIN
update xx_emp set Comm = 100 where deptno = 10;
dbms_output.put_line ('Updated Rows are : '|| SQL%ROWCOUNT);
END;
 
select * from  xx_emp where deptno = 40
 
DECLARE
BEGIN
delete from xx_emp where deptno = 40;
dbms_output.put_line ('Deleted Rows are : '|| SQL%ROWCOUNT);
END;
 
 
CREATE TABLE XX_EMP1 AS
select * from xx_emp
 
INSERT INTO XX_EMP1
select * from XX_EMP
 
TRUNCATE TABLE XX_EMP1
 
select * from XX_EMP1
 
 
BEGIN
TRUNCATE TABLE XX_EMP1;
END
 
--==========================================================================
DYNAMIC SQL :- Dynamic SQL is a programming methodology for generating and running SQL statements at run time.
--==========================================================================
 
EX:-
 
select * from xx_emp
 
CREATE OR REPLACE PROCEDURE XX_TEST_EXECUTE_IMMEDIATE_PROC (V_DEPT_NO NUMBER)  AS
 
--DECLARE
V_COUNT NUMBER;
BEGIN
select COUNT(*) INTO V_COUNT from xx_emp where deptno = V_DEPT_NO;
dbms_output.put_line ('count is : '|| V_COUNT);
END;
 
 
 
BEGIN
XX_TEST_EXECUTE_IMMEDIATE_PROC ( 20);
END;
 
 
 
 
CREATE OR REPLACE PROCEDURE XX_TEST_EXECUTE_IMMEDIATE_PROC (V_DEPT_NO NUMBER, V_TABLE_NAME VARCHAR2)  AS
--DECLARE
V_COUNT NUMBER;
BEGIN
select COUNT(*) INTO V_COUNT from V_TABLE_NAME where deptno = V_DEPT_NO;
dbms_output.put_line ('count is : '|| V_COUNT);
END;
 
 
 
DROP PROCEDURE XX_TEST_EXECUTE_IMMEDIATE_PROC
 
 
 
 
CREATE OR REPLACE PROCEDURE XX_TEST_EXECUTE_IMMEDIATE_PROC (V_DEPT_NO NUMBER, V_TABLE_NAME VARCHAR2)  AS
--DECLARE
V_COUNT NUMBER;
V_SQL VARCHAR2(500);
--V_DEPARTMENT NUMBER := 10;
BEGIN
 
V_SQL := 'select COUNT(*) from '||V_TABLE_NAME||' where deptno = :DEPT';
 
Execute Immediate V_SQL INTO V_COUNT USING V_DEPT_NO ;
 
dbms_output.put_line ('Total Number of Employee in Department '||V_DEPT_NO||' is : '|| V_COUNT);
END;
 
 
BEGIN
XX_TEST_EXECUTE_IMMEDIATE_PROC ( 20,'XX_DEPT');
END;
 
 
select * from  XX_DEPT
 
 
 
DECLARE
V_COUNT NUMBER;
V_SQL VARCHAR2(500);
V_DEPARTMENT NUMBER := 10;
BEGIN
 
V_SQL := 'select COUNT(*) from xx_emp where deptno = :V_DEPT_NO';
Execute Immediate V_SQL INTO V_COUNT USING V_DEPARTMENT ;
dbms_output.put_line ('Total Number of Employee in Department '||V_DEPARTMENT||' is : '|| V_COUNT);
END;
 
 
 
DECLARE
V_COUNT NUMBER;
V_SQL VARCHAR2(500);
V_DEPARTMENT NUMBER := 10;
BEGIN
 
V_SQL := 'UPDATE XX_EMP SET COMM = 100 WHERE DEPTNO = :V_DEPT_NO1';
Execute Immediate V_SQL USING V_DEPARTMENT ;
dbms_output.put_line ('Total Number of Updated Rows are : '|| SQL%ROWCOUNT);
END;
 
select * from XX_EMP where deptno = 10
 
 
DECLARE
V_COUNT NUMBER;
V_SQL VARCHAR2(500);
V_DEPARTMENT NUMBER := 40;
BEGIN
 
V_SQL := 'delete from xx_emp where deptno = :V_DEPT_NO2';
Execute Immediate V_SQL USING V_DEPARTMENT ;
dbms_output.put_line ('Total Number of Deleted Rows are : '|| SQL%ROWCOUNT);
END;
 
 
 
BEGIN
Execute Immediate 'DROP TABLE XX_EMP1';
END;
 
select * from XX_EMP1
 
 
--====================================================
Advantages of Native Dynamic SQL
--====================================================
1. it provides flexibility to run or change SQL at run time to USER.
2. we can run DDL/DML/Dynamic Query during any PLSQL Block.
 
 
x