Oracle static SQL and dynamic SQL

1. Static SQL vs. dynamic SQL
The Oracle compiled PL/SQL program block is divided into two types:
The first is the pre-assembly (early binding), that is, the SQL statement during the program compilation has been determined, most of the compilation of this type;
The other is a late-linking (late binding), that is, the SQL statement can only be established during the run phase, for example, when the query condition is user input, then Oracle's SQL engine will not be at compile time to determine the program statement, Can only be submitted to the SQL engine for processing after the user has entered a certain query condition.

Typically, static SQL takes the previous compilation, whereas dynamic SQL uses the latter one.
　

2. Dynamic SQL program Development
Since dynamic SQL is an "indeterminate" SQL, its execution has its corresponding characteristics.
The Execute immediate statement is provided in Oracle to execute dynamic SQL with the following syntax:
Excute immediate Dynamic SQL statement using binding parameter list returning into output parameter list;
This statement is described as follows:
1) Dynamic SQL refers to DDL and indeterminate DML (that is, DML with parameters)
2) The binding parameter list is the input parameter list, which is the type in type, which is bound at run time with the parameters in the dynamic SQL statement (actually placeholders, which can be understood as the formal parameters inside the function).
3) The output parameter list is the list of parameters returned after the dynamic SQL statement executes.
4) Since dynamic SQL is determined at runtime, it can lose some system performance in exchange for its flexibility relative to static.

3. To better illustrate the process of its development, an example is listed below:
Set the database EMP table with the following data:
ID NAME SALARY
5600 Jacky
101 Rose 3000
102 John 4500
Requirements:
1. Create the table and enter the appropriate data.
2. Information about their name and salary can be queried based on a specific ID.
3. The employee information is based on a query that is larger than the specific salary.

Depending on the previous requirements, you can create three procedures, each using dynamic SQL, to achieve:
Process one:
Create or replace procedure create_table as
Begin
Execute immediate ' CREATE TABLE emp (ID number,name varchar2 (Ten), salary number;) '; --Dynamic SQL for DDL statements
INSERT into EMP values (+, ' Jacky ', 5600);
INSERT into EMP values (101, ' Rose ', 3000);
INSERT INTO EMP values (102, ' John ', 4500);
End create_table;
Process two:
Create or Replace procedure Find_info (p_id number) as
V_name VARCHAR2 (10);
V_salary number;
Begin
Execute immediate ' select Name,salary from emp where Id=:1 '
Using p_id
returning into V_name,v_salary; --Dynamic SQL for Query statements
Dbms_output.put_line (V_name | | ' The income is: ' | | To_char (v_salary));
exception
When others then
Dbms_output.put_line (' No corresponding data found ');
End Find_info;

Oracle static SQL and dynamic SQL