What is static and dynamic SQL?
Static SQL and Dynamic SQL are two ways of writing SQL statements in PL/SQL, a procedural language extension for SQL developed by Oracle Corporation. The principal difference between these two types of SQL lies in the way SQL statements are prepared and executed.
Static SQL is the SQL that is known at compile-time. It involves SQL statements written directly into the PL/SQL code and doesn't change at runtime. This implies that the full text of the SQL statement is known when the application is compiled. Because of this, the Oracle database can optimize the execution plan for these SQL statements when the PL/SQL program is compiled.
The benefits of Static SQL include:
- Efficiency: Since the database knows the SQL statement at compile time, it can work out the most efficient way to access the data requested by the SQL statement.
- Security: Static SQL in PL/SQL supports declarative roles and privileges, providing granular control over who can access or modify data.
- Compile-time syntax check: If there's any error in the SQL statement, it will be caught at compile time.
In contrast, Dynamic SQL involves SQL statements that are not fully known until runtime. They are constructed dynamically as the program is running, usually in response to user input or changes in the database. This kind of SQL is used when the structure of a query or DML operation (such as INSERT, UPDATE, DELETE, or SELECT) is not known in advance or needs to be constructed on the fly. Dynamic SQL is constructed using string manipulation and then passed to the database for parsing and execution.
The benefits of Dynamic SQL include:
- Flexibility: Dynamic SQL can handle almost any SQL task, even when the specifics of the task are not known until runtime.
- Runtime construction: SQL queries can be constructed based on user input or the state of the database.
However, Dynamic SQL also comes with certain challenges:
- Performance: The database might not optimize dynamic SQL as well as static SQL. Each unique SQL statement requires a new execution plan, which can be slower to create than reusing the plan for a static SQL statement.
- Security: Dynamic SQL can be susceptible to SQL Injection attacks if not properly handled, as it involves constructing SQL statements using string manipulation techniques.
In summary, while static SQL is efficient and safe, it lacks the flexibility of dynamic SQL. Conversely, dynamic SQL offers great flexibility, but requires careful handling to maintain performance and security. The choice between static and dynamic SQL will depend on the specific needs of your PL/SQL program.
The SQL in a cursor can be static or dynamic. In a static cursor, the user defines the exact syntax of the
SELECT statement, and execution of the statement does not change its syntax. On the other hand, you can build dynamic SQL with the DBMS_SQL package.
In a dynamic SQL, the exact syntax of the
SELECT statement is not defined at compilation time. It is generated when Oracle executes the PL/SQL block, however.
Using Native Dynamic SQL and DBMS_SQL
You can use native dynamic SQL to create general-purpose procedures and to execute DDL commands within PL/SQL.
You can use dynamic SQL to execute SQL commands that are built at run time rather than at program-creation time within stored procedural objects (such as packages) and in anonymous PL/SQL
blocks. For example, you can create procedures that create tables, that take table names as input parameters, or that take where clauses as input parameters.
Oracle PL/SQL Programming
Using EXECUTE IMMEDIATE
The script in the following example creates a procedure called ANYSTRING. The ANYSTRING procedure has a single input variable: a SQL command. When you execute the ANYSTRING procedure, you pass it the SQL command to execute; the SQL command may be either a DML command (such as select or insert) or a DDL command (such as create table).
create or replace procedure ANYSTRING(String IN VARCHAR2) AS
EXECUTE IMMEDIATE (String);
You can execute the ANYSTRING procedure via the execute command, as shown in the upcoming examples of DDL and DML commands.
Note that the strings are not terminated with semicolons. If you are going to run DDL commands through dynamic SQL, you must have been granted the necessary system privileges explicitly and not via roles.
If you do not have the CREATE TABLE system privilege explicitly granted to you, the following command will fail with an
ORA-01031: insufficient privileges error:
execute ANYSTRING('create table CD (Artist VARCHAR2(25), Title VARCHAR2(25)) ');
execute ANYSTRING('insert into CD values(''Glenn Gould'', ''Goldberg Variations'') ');
The first command in the preceding listing executes the following SQL command:
create table CD
The second command in the listing inserts a record into the CD table:
insert into CD values ('Glenn Gould', 'Goldberg Variations');
If the SQL statements passed to ANYSTRING contain quotes,
each quote should be replaced with a set of two quotes, as shown in the preceding execution of ANYSTRING for the insert command.
After each command, you will receive the following response:
PL/SQL procedure successfully completed.
Differences between static and dynamic SQL
Static and dynamic SQL are each appropriate for different circumstances. You should consider the differences between the two when determining whether static SQL or dynamic SQL is best for your application.
Flexibility of static SQL with host variables
The source form of a static SQL statement is embedded within an application program written in a host language like PL/I.
The statement is prepared before the program is executed and the operational form of the statement persists beyond the execution of the program.
Static SQL statements in a source program must be processed before the program is compiled. This processing can be accomplished through the precompiler or the coprocessor. The precompiler or the coprocessor checks the syntax of the SQL statements, turns them into host language comments, and generates host language statements to invoke .
The preparation of an SQL application program includes precompilation, the preparation of its static SQL statements, and compilation of the modified source program