 |
| Using Dynamic SQL |
01. Write down the Execution Flow of SQL.
All SQL statements go through some or all of the following stages:
a. Parse.
b. Bind.
c. Execute.
d. Fetch.
02. How to Working with Dynamic SQL?
Use dynamic SQL to create a SQL statement whose structure may change during run time.
Dynamic SQL:
a. Is constructed and stored as a character string, string variable, or string expression within the application.
b. Is a SQL statement with varying column data, or different conditions with or without placeholders (bind variables).
c. Enables DDL, DCL, or session-control statements to be written and executed from PL/SQL.
d. Is executed with Native Dynamic SQL statements or the DBMS_SQL package.
03. Write down about Native Dynamic SQL (NDS).
Native Dynamic SQL (NDS):
a. Provides native support for dynamic SQL directly in the PL/SQL language.
b. Provides the ability to execute SQL statements whose structure is unknown until execution time.
c. If the dynamic SQL statement is a SELECT statement that returns multiple rows, NDS gives you the following choices:
i. Use the EXECUTE IMMEDIATE statement with the BULK COLLECT INTO clause.
ii. Use the OPEN-FOR, FETCH, and CLOSE statements.
d. In Oracle Database 11g, NDS supports statements larger than 32 KB by accepting a CLOB argument.
0 Comments