oracle dynamic SQL usage details

  • 2021-01-18 06:43:06
  • OfStack

1. Static SQLSQL versus dynamic SQL
The PL/SQL blocks are divided into two types: 1 is pre-concatenation (early binding), that is, SQL statements are determined during program compilation, and most compilation cases are of this type; The other one is late binding, that is, SQL statements can only be established at runtime, for example, when the query condition is user input, SQL engine can not determine the program statement at compile time, can only be submitted to SQL engine for processing after the user input a certain query condition. In general, static SQL is compiled in the former way, while dynamic SQL is compiled in the latter way.
This paper mainly discusses the development of dynamic SQL, and finally gives some practical development skills.
2. Dynamic SQL program development
After understanding the principle of dynamic SQL compilation, we have mastered its basic development idea. Since dynamic SQL is an "indeterminate" SQL, its implementation has its own characteristics. The Execute immediate statement is provided to execute dynamic SQL. The syntax is as follows:

 
Excute immediate  dynamic SQL statements  using  Binding parameter list  returning into  Output parameter list ; For this 1 The statement reads as follows:  

1) Dynamic SQL refers to DDL and uncertain DML (that is, DML with parameters)
2) The binding parameter list is the input parameter list, that is, it is of type in and is bound at run time with the parameters in the dynamic SQL statement (actually placeholders, which can be interpreted as formal parameters in the function).
3) The output parameter list is the parameter list returned after the execution of dynamic SQL statement.
4) Because dynamic SQL is determined at runtime, compared with static, it will lose more system performance in exchange for its flexibility.
To better illustrate the process of its development, an example is listed below:
Assume that the emp table of the database has the following data:
ID NAME SALARY 100 Jacky 5600 101 Rose 3000 102 John 4500
Requirements:
1. Create the table and enter the corresponding data.
2. The name and salary information can be queried according to the specific ID.
3. Search for the appropriate employee information based on a salary that is greater than a specific salary.
According to the previous requirements, you can create three procedures (all using dynamic SQL) to implement:
Procedure 1:
 
create or replace procedure create_table as 
begin 
execute immediate ' 
create table emp(id number, 
name varchar2(10), 
salary number )'; -- dynamic SQL for DDL statements  
insert into emp 
values (100,'jacky',5600); 
insert into emp 
values (101,'rose',3000); 
insert into emp 
values (102,'john',4500); 
end create_table; 

The process of 2:
 
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 the query statement  
dbms_output.put_line(v_name ||' The income of: '||to_char(v_salary)) ;  
exception 
when others then 
dbms_output.put_line(' No corresponding data could be found ') ;  
end find_info; 

The process of 3:
 
create or replace procedure find_emp(p_salary number) as 
r_emp emp%rowtype; 
type c_type is ref cursor; 
c1 c_type; 
begin 
open c1 for ' 
select * from emp 
where salary >:1' 
using p_salary; 
loop 
fetch c1 into r_emp; 
exit when c1%notfound; 
dbms_output.put_line(' Salary greater than ' ||to_char(p_salary)||' The staff is: ' ); 
dbms_output.put_line('ID for 'to_char(r_emp)||'  His name is: '||r_emp.name); 
end loop; 
close c1; 
end create_table; 

Note: The dynamic SQL statement in procedure 2 uses the placeholder ":1", which is equivalent to the formal argument of the function, prefixed with ":", and then replaced with :1 at run time by the using statement, where p_id is equivalent to the argument in the function. In addition, the open cursor in procedure 3 is a dynamic cursor, which also falls under the category of dynamic SQL. The entire compilation and development process is very similar to that performed by execute immediate, so I won't go into it here.
3. Dynamic SQL statement development skills
Analysis to the front, the implementation of dynamic SQL is loss performance of the system for its flexibility, so it 1 degree of optimization is necessary, the author gives according to the practical development experience 1 some development skills, it should be pointed out that, a lot of experience here is not only limited to the dynamic SQL, some can also be applied to static SQL, mark will be given in the description.
Tip 1: Try to use a similar SQL statement, so that Oracle itself caches the SQL statement directly through the shared pool in SGA, so that the next time a similar statement is executed, it calls the parsed statement in the cache directly, so as to improve execution efficiency.
Tip 2: When it comes to collection units, try to use batch linking. For example, the salary of id 100 and 101 employees should be raised by 10%, which should be in the form of:
 
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 =salary*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 be relatively slow. So if batch processing is adopted, the whole set will be first passed into the SQL engine for processing, which is much more efficient than the single processing. The code for batch processing 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 =salary*1.2 
where id=:1 ' 
using v_id(i); 
end loop; 
end; 

Here is the use of ES106en for batch processing, here will be a summary of the batch processing situation:
1) If a collection element is referenced by an insert, delete, update, etc. statement executed within a loop, then it can be moved to a single forall statement.
2) If the select into, fetch into, or returning into clauses refer to a collection, the bulk collect clauses should be used for merging.
3) If possible, you should use the host array to pass parameters between the program and the database server.
Tip 3: Use the NOCOPY compiler to improve PL/SQL performance. By default, arguments to out and in are passed by value. However, for large object type or collection type parameter passing, the expected loss will be very large. In order to reduce the loss, we can use the method of passing by reference, that is, when making parameter declaration, we can refer to the NOCOPY keyword to achieve this effect. For example, create a procedure:
 
create or replace procedure test(p_object in nocopy square)...end; 

square is a large object type. So instead of passing the whole object, you just pass one address. Obviously, such treatment also improves efficiency.
4. summary
This paper discusses the compiling principle, development process and development skills of dynamic SQL. After the introduction of this paper, it is believed that readers will have a general understanding of dynamic SQL program development, and lay a good foundation for further work in the future.


Related articles: