In-depth high-performance dynamic Oracle SQL development (1)

Do you know about Oracle dynamic SQL development? Here is a brief introduction. You are welcome to join me in learning.

1. Static SQL and dynamic SQL

Oracle dynamic SQL development and compilation PL/SQL program blocks are divided into two types: one is the early-stage combination of early binding), that is, the SQL statement has been determined during program compilation, most compilation cases belong to this type; the other is the late binding later), that is, SQL statements can be created only in the running stage. For example, when the query condition is user input, therefore, the SQL engine of Oracle cannot determine the program statement during the compilation period. It can only be submitted to the SQL engine for processing after you enter certain query conditions. Generally, static SQL adopts the previous compiling method, while dynamic SQL Development adopts the latter compiling method.

2. Dynamic Oracle SQL Development

Understand the principles of dynamic SQL compilation, and master its basic development ideas. Since dynamic SQL is an "uncertain" SQL statement, its execution has its own characteristics. Oracle provides the Execute immediate statement to Execute dynamic SQL statements. The syntax is as follows:
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 uncertain DML, that is, DML with parameters)
2) The bound parameter list is the input parameter list, that is, the type is in. The parameters in the runtime and dynamic SQL statements are actually placeholders, which can be understood as formal parameters in the function).
3) the output parameter list is the list of parameters returned after the dynamic SQL statement is executed.
4) Because dynamic SQL statements are determined at runtime, compared with static SQL statements, it will lose some system resources in exchange for its flexibility.
To better illustrate the development process, the following example is provided:

Set the emp table of the database as follows:

ID NAME SALARY
100 Jacky 5600
101 Rose 3000
102 John 4500

Requirements:
1. Create the table and enter the corresponding data.
2. You can query the name and salary information based on the specific ID.
3. query the employee information based on the salary that exceeds the specified salary.
Based on the preceding requirements, three processes can be created using dynamic SQL:

Process 1:

 
 

  
  
Create or replace procedure create_table
  
  
Begin
  
  
Execute immediate'
  
  
Create table emp (id number,
  
  
Name varchar2 (10)
  
  
Salary number;) '; -- dynamic SQL is a DDL statement
  
  
Insert into emp
  
  
Values (100, 'jacky', 5600 );
  
  
Insert into emp
  
  
Values (101, 'Rose ', 3000 );
  
  
Insert into emp
  
  
Values (102, 'john', 4500 );
  
  
End create_table;
  
  
 
 
 

Process 2:

 
 

  
  
Create or replace procedure find_info (p_id number)
  
  
V_name varchar2 (10 );
  
  
V_salary number;
  
  
Begin
  
  
Execute immediate'
  
  
Select name, salary from emp
  
  
WhereId=: 1'
  
  
Using p_id
  
  
Returning into v_name, v_salary; -- dynamic SQL is a query statement
  
  
Dbms_output.put_line (v_name | 'revenue:' | to_char (v_salary ));
  
  
Exception
  
  
When others then
  
  
Dbms_output.put_line ('corresponding data not found ');
  
  
End find_info;
  
  
 
  
  
Process 3:
  
  
 
  
  
Create or replace procedure find_emp (p_salary number)
  
  
R_emp emp % rowtype;
  
  
Type c_type is ref cursor;
  
  
C1 c_type;
  
  
Begin
  
  
Open c1'
  
  
Select * from emp
  
  
Where salary>: 1'
  
  
Using p_salary;
  
  
Loop
  
  
Fetch c1 into r_emp;
  
  
Exit when c1 % notfound;
  
  
Dbms_output.put_line ('salary is higher than '| to_char (p_salary) |' EMPLOYEE :');
  
  
Dbms_output.put_line ('Id is 'to _ char (r_emp) |' its name is '| r_emp.name );
  
  
End loop;
  
  
Close c1;
  
  
End create_table;
  
  
 
 
 

Note: In process 2, the dynamic SQL statement uses the Placeholder ": 1". In fact, it is equivalent to the form parameter of the function and uses ":" As the prefix, then use the using statement to replace p_id with: 1 at the runtime. p_id is equivalent to the real parameter in the function. In addition, the cursor opened in process 3 is a dynamic cursor, which also belongs to the category of dynamic SQL. The entire compilation and development process is similar to the execute immediate execution process, which is not described here.