Shulou(Shulou.com)06/01 Report--

Comparison of Oracle dynamic SQL and static SQL

1. Static SQLSQL and dynamic SQL

Oracle compiling PL/SQL blocks are divided into two types: one is pre-binding (early binding), that is, SQL statements are determined during program compilation, and most compilations fall into this type. The other is late binding (late binding), that is, the SQL statement can only be established at the run stage, for example, when the query condition is input by the user, then the SQL engine of Oracle can not determine the program statement at the compilation time, and can only be submitted to the SQL engine for processing after the user enters certain query conditions. In general, static SQL is compiled in the former way, while dynamic SQL is compiled in the latter way.

2. Dynamic SQL program development

Having understood the principle of dynamic SQL compilation, we have mastered its basic development idea. Since dynamic SQL is a kind of "uncertain" SQL, its execution has its corresponding characteristics. Execute immediate statements are provided in Oracle to execute dynamic SQL, and the syntax is as follows:

Excute immediate dynamic SQL statement using bind parameter list returning into output parameter list

This sentence is explained as follows:

1) dynamic SQL refers to DDL and uncertain DML (i.e. DML with parameters)

2) the binding parameter list is the input parameter list, that is, its type is in, which is bound to the parameters in the dynamic SQL statement (actually placeholders, which can be understood as formal parameters in the function) at run time.

3) the output parameter list is the parameter list returned after the execution of the dynamic SQL statement.

4) because dynamic SQL is determined at run time, compared with static, it will lose more system performance in exchange for its flexibility.

In order to better illustrate the process of its development, here is an example:

Let's set the other table of the database, and its data is as follows:

ID

NAME

SAL

ten

Scott

3000

twenty

Tom

5000

thirty

Jerry

4500

Request:

1. Create the table and enter the appropriate data.

2. According to a specific ID, you can query its name and salary information.

3. Query the corresponding employee information according to the salary that is greater than a specific salary.

Based on the previous requirements, you can create three procedures, all using dynamic SQL, to implement:

Procedure 1: (create a table and insert data)

18:16:27 SCOTT@ prod > create or replace procedure p1 as18:16:36 2 flag number; 18:16:36 3 begin18:16:36 4 select count (*) into flag from all_tables where table_name='T1';18:16:36 5 if (flag=0) then 18:16:36 6 execute immediate 'create table T1 (id number,name varchar2 (10), sal number)'; 18:16:36 7 else18:16:36 8 insert into T1 values 18:16:36 9 insert into T1 values; 18:16:37 10 insert into T1 values; 18:16:37 11 end if;18:16:37 12 end p1 * 18 end if;18:16:37 38 13 / Procedure created.Elapsed: 00 end if;18:16:37 00.2018 16 SCOTT@ prod 40 SCOTT@ prod > exec p1 scape PLL procedure successfully completed.Elapsed: 00 SCOTT@ prod 0018 SCOTT@ prod 00.18 select * from T1 ID NAME SAL- 10 scott 3000 20 tom 5000 30 jerry 4500Elapsed: 00 tom 00.0118 SCOTT@ prod >

Process 2: (query user information by id)

18:40:24 SCOTT@ prod > create or replace procedure p2 (p_id number) as18:40:26 2 v_name varchar2 (10); 18:40:26 3 v_sal number;18:40:26 4 begin18:40:26 5 execute immediate 'select name,sal from T1 where id=:1' into where id=:1' into dbms_output.put_line dbms_output.put_line (v_name | |' Salary is:'| | to_char (v_sal)) 18:40:26 7 exception18:40:26 8 when others then18:40:26 9 dbms_output.put_line ('No Data Found'); 18:40:26 10 end p2 * 18 * Tom Salary is: 5000PL/SQL procedure successfully completed.Elapsed: 00 exec 0018 40 SCOTT@ prod > exec p2 (30); jerry Salary is: 4500PL/SQL procedure successfully completed.Elapsed: 00 00 0 0218 40 SCOTT@ prod >

Process 3: (query employees whose salary is greater than a certain value)

18:48:59 SCOTT@ prod > create or replace procedure p3 (p_sal number) as18:50:55 2 r_t1 T1% Rowtypex 1850 r_t1 55 3 type c_type is ref cursor;18:50:56 4 C1 ciliary typescape 1850 where sal 56 5 begin18:50:56 6 open C1 for '18:50:56 7 select * from t118 where sal >: 1: 18 where sal 50 using 56 9 using salvation 1850 loop18:50:56 11 fetch c1 into r_t1 18:50:56 12 exit when C1% notorious 18 50 to_char p_sal 56 13 dbms_output.put_line ('Salary higher' | | to_char (p_sal) | | 'Name is:'); 18:50:56 14 dbms_output.put_line (' ID is'| | to_char (r_t1.id) | | 'Name is:' | | r_t1.name); 18:50:56 15 end loop;18:50:56 16 close c1ten 18 to_char 5056 17 end p3 18:50:57 18 / Procedure created.Elapsed: 0000 Name is:ID is 00.1218 50 SCOTT@ prod > exec p3 (2000); Salary higher 2000 Name is:ID is 10 Name is: scottSalary higher 2000 Name is:ID is 20 Name is: tomSalary higher 2000 Name is:ID is 30 Name is: jerryPL/SQL procedure successfully completed.Elapsed: 0000VOO 00.0218 Name is:ID is 51V 15 SCOTT@ prod >

Note: the dynamic SQL statement in procedure 2 uses the placeholder ": 1", which is actually equivalent to the formal parameter of the function, using ":" as the prefix, and then using the using statement to replace: 1 at run time with p_id, where p_id is equivalent to the argument in the function. In addition, the cursor opened in process 3 is a dynamic cursor, which also belongs to the category of dynamic SQL, and the whole process of compilation and development is very similar to that executed by execute immediate.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.