Development of high Performance dynamic SQL program based on Oracle

Source: Internet
Author: User

Absrtact: The development of dynamic SQL program is summarized, and some development techniques are given according to the author's actual development experience.

Key words: Dynamic Sql,pl/sql, high performance

1. static sqlsql and dynamic SQL

Oracle compiled PL/SQL program block is divided into two kinds: first, the previous early binding, that is, the SQL statement was established during the program compilation, most of the compilation is this type, and the other is the late-stage (late binding), That is, the SQL statement can only be established at run time, for example, when the query condition is entered by the user, the Oracle SQL engine cannot determine the program statement at compile time, and can submit it to the SQL engine for processing only after the user has entered a certain query condition. In general, static SQL takes the previous form of compilation, and dynamic SQL takes the latter form of compilation.

This paper mainly discusses the development of dynamic SQL, and finally gives some practical development techniques.

2. Dynamic SQL program Development

The principle of dynamic SQL compilation is understood, and the basic development idea is mastered. Since dynamic SQL is an "indeterminate" SQL, its execution has its own characteristics. The Execute immediate statement is provided in Oracle to execute dynamic SQL, 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 indeterminate DML (that is, DML with arguments)

2 The binding parameter list is the input parameter list, which is of type in type, and is bound at runtime with the arguments in the dynamic SQL statement (actually placeholders, which can be understood as the form parameters inside the function).

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

4 because dynamic SQL is determined at run time, it will lose some system performance in exchange for its flexibility relative to static.

To better illustrate the process of its development, here is an example:

Set the EMP table for the database with the following data:

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

Requirements:

1. Create the table and enter the appropriate data.

2. Information about its name and salary can be queried based on a specific ID.

3. The employee information is based on a query that is larger than a specific salary.

According to the previous requirements, you can create three separate processes (all using dynamic SQL) to achieve:

Process one:

create or replace procedure create_table as
begin
execute immediate '
create table emp(id number,
name varchar2(10)
salary number; )'; --动态SQL为DDL语句
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 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; --动态SQL为查询语句
dbms_output.put_line(v_name ||'的收入为:'||to_char(v_salary));
exception
when others then
dbms_output.put_line('找不到相应数据');
end find_info;

Process three:

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('薪水大于‘||to_char(p_salary)||’的员工为:‘);
dbms_output.put_line('ID为'to_char(r_emp)||' 其姓名为:'||r_emp.name);
end loop;
close c1;
end create_table;

Note: The dynamic SQL statement in procedure two uses the placeholder ": 1", which is equivalent to the form parameter of the function, uses ":" As the prefix, and then uses the using statement to replace the p_id at run time with: 1, where p_id is equivalent to the argument in the function. In addition, the cursor opened in process Three is a dynamic cursor, and it belongs to the category of dynamic SQL, and its entire compilation and development process is similar to that executed by execute immediate.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.