What is the difference between dynamic sql and static sql?

Dynamic sql refers to statements that are constructed and executed at runtime. Whereas static sql statements are constructed when the pl/sql block containing the statement is compiled.

Why do we need dynamic sql?

• To execute DDL statements inside a pl/sql block, we need dynamic sql.
• Adhoc query updations for web based apps like specifying the order in which data is displayed could be done using dynamic sql.

How to integrate dynamic sql into our code?

Since Oracle 8i Database, Native Dynamic SQL(NDS) is introduced which is a simple and efficient way of integrating dynamic sql in our code and is integrated into PL/SQL through the EXECUTE IMMEDIATE and OPEN FOR statements.

Execute Immediate executes a SQL statement immediately. 

• Simple example:

BEGIN

EXECUTE IMMEDIATE 'CREATE INDEX item1 on items(name)';

END;

Its as simple as that!!

Quick tip: If the statement inside the quotes ends in a semicolon, it is treated as pl/sql, otherwise sql.

Syntax:

EXECUTE IMMEDIATE sql_string

[INTO {variable, variable...}]

[USING [bind argument]...];

Here:

sql_string is the sql statement or pl/sql block.

Into clause is used for single row queries to fetch the value returned by each column into corresponding variables.

Using clause is used to supply bind arguments for sql_string. We can specify parameter mode, which is always IN for sql statements.

How to execute multiple row dynamic queries?

There are 2 ways: EXECUTE IMMEDIATE with BULK COLLECT and OPEN FOR statement.

Oracle uses a feature that resembles cursors for working with multiple row dynamic queries. The OPEN FOR statement opens a cursor variable for sql query from which rows can be fetched like static cursors.

You can also use EXECUTE IMMEDIATE with BULK COLLECT to retreive multiple rows from a dynamic query.

Syntax of OPEN FOR:

OPEN {cursor_variable| :host_cursor_variable} FOR sql_string

[USING [bind argument]...];

Here:

cursor_variable is a weak cursor variable declared in pl/sql block.

:host_cursor_variable is a cursor variable declared in pl/sql host environment.

sql_string is the statement to be executed dynamically.

USING works same as with EXECUTE IMMEDIATE. 

• Simple example:

PROCEDURE emp_dependants(

emptable_var IN varchar2,

where_in IN varchar2)

IS

TYPE curtype_emp IS REF CURSOR;

cur_var curtype_emp;

BEGIN

OPEN cur_Var FOR

'SELECT * FROM' || emptable_var

'WHERE' || where_in;

...

END;

Disclaimer - Views expressed in this blog are author's own and do not necessarily represents the policies of aclnz.com