Scribd
Upload
2 views3 pages

Dynamic SQL

Dynamic SQL is a programming technique that allows the construction of SQL statements at runtime, useful when table or column names are unknown until execution. The 'EXECUTE IMMEDIATE' statement is used to execute dynamic SQL commands, including DDL, DML, DQL, and DCL. Examples include creating stored procedures to drop tables or other objects dynamically based on provided names.

Uploaded by

shakibscro
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2 views3 pages

Dynamic SQL

Dynamic SQL is a programming technique that allows the construction of SQL statements at runtime, useful when table or column names are unknown until execution. The 'EXECUTE IMMEDIATE' statement is used to execute dynamic SQL commands, including DDL, DML, DQL, and DCL. Examples include creating stored procedures to drop tables or other objects dynamically based on provided names.

Uploaded by

shakibscro
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

DYNAMIC SQL:

- DYNAMIC SQL IS A PROGRAMMING TECHNIQUE TO BUILD


SQL STATEMENTS AT RUNTIME.

EX:
DROP TABLE EMP; ------------->STATIC

EX:
TNAME = '&TNAME '
DROP TABLE TNAME; -------> DYNAMIC

- DYNAMIC SQL IS USEFULL 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.

EXECUTE IMMEDIATE:
- THIS STATEMENT IS USED TO EXECUTE DDL COMMANDS
(OR) DYNAMIC SQL COMMAND.
SYNTAX:
EXECUTE IMMEDIATE 'DYNAMIC SQL COMMAND';

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');
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 (T IN VARCHAR2,N
IN VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE 'DROP'||' '||T||' '||N;
END;
/
TESTING:
EXECUTE DROP_OBJ('TABLE','TEST');
EXECUTE DROP_OBJ('VIEW','V1');

You might also like