1. Static SQL and dynamic SQL
Oracle compiler PL/SQL program blocks are divided into two types: one is the early compilation (early binding), that is, the SQL statement has been determined during program compilation, and most of the compilation situations belong to this type; the other is late binding, that is, an SQL statement 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 adopts the latter compiling method.
This article mainly discusses the development of dynamic SQL and provides some practical development skills at the end.
2. dynamic SQL Program 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:
Copy codeThe Code 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, and the parameters in the runtime and dynamic SQL statements (in fact, placeholders 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, you can create three processes (both use dynamic SQL) to achieve the following:
Process 1:
Copy codeThe Code is as follows:
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:
Copy codeThe Code is as follows:
Create or replace procedure find_info (p_id number)
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 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:
Copy codeThe Code is as follows:
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.
3. dynamic SQL statement development skills
According to the previous analysis, the execution of dynamic SQL is a result of a loss of system ability in exchange for its flexibility. Therefore, it is necessary to optimize it to a certain extent. Based on the actual development experience, the author provides some development skills, it should be noted that many experiences here are not limited to dynamic SQL, but also some are suitable for static SQL, which will be marked in the description.
Tip 1: Try to use similar SQL statements, so that Oracle itself caches the SQL statement directly through the sharing pool in SGA, then, when you execute similar statements in the next time, you can directly call the parsed statements in the cache to improve the execution efficiency.
Tip 2: When a set unit is involved, use batch editing whenever possible. For example, if you want to raise a salary of 100 for employees with IDs of 101 and 10%, the salary should generally be in the following format:
Copy codeThe Code is as follows:
Declare
Type num_list is varray (20) of number;
V_id num_list: = num_list (100,101 );
Begin
...
For I in v_id.first... v_id.last loop
...
Execute immediate 'Update emp
Set = salesary * 1.2
Where id =: 1'
Using v_id (I );
End loop;
End;
For the above processing, when the amount of data is large, it will appear relatively slow, then if the use of Batch encoding, the entire set is first imported into the SQL engine for processing, this is much more efficient than individual processing. The code for batch editing is as follows:
Copy codeThe Code is as follows:
Declare
Type num_list is varray (20) of number;
V_id num_list: = num_list (100,101 );
Begin
...
Forall I in v_id.first... v_id.last loop
...
Execute immediate 'Update emp
Set = salesary * 1.2
Where id =: 1'
Using v_id (I );
End loop;
End;
Forall is used for batch editing. Here we will summarize the batch editing process:
1) if insert, delete, update, and other statements in a loop reference a set element, you can move it to a forall statement.
2) If the select into, fetch into, or returning into clause references a set, use the bulk collect clause for merge.
3) If possible, use the host array to implement passing parameters between the program and the database server.
Tip 3: Use the NOCOPY compiler to improve PL/SQL Performance. By default, parameters of the out and in out types are transmitted by value. However, for parameter transfer of large object types or set types, the expected loss is great. To reduce the loss, the reference transmission method can be used, that is to say, this effect can be achieved by referencing the NOCOPY keyword during parameter declaration. For example, to create a process:
Copy codeThe Code is as follows:
Create or replace procedure test (p_object in nocopy square)... end;
Square is a large object type. In this way, only an address is passed, rather than the entire object. Obviously, this process also improves the efficiency.
4. Summary
This article discusses the compilation principle, development process, and development skills of dynamic SQL. After introducing this article, I believe that readers have a general understanding of dynamic SQL program development, lay a good foundation for future in-depth work.
