动态SQL编写教程

使用动态SQL是在编写PL/SQL过程时经常使用的方法之一。很多情况下,比如根据业务的需要,如果输入不同查询条件,则生成不同的执行

SQL查询语句,对于这种情况需要使用动态SQL来完成。再比如,对于分页的情况,对于不同的表,必定存在不同的字段,因此使用静态SQL则只

能针对某几个特定的表来形成分页。而使用动态的SQL,则可以对不同的表,不同的字段进行不同的分页。这些情况的处理通常都是用动态SQL来

完成。本文讲述了动态SQL的日常用法。

一、动态SQL和静态SQL

1.静态SQL

静态SQL通常用于完成可以确定的任务。比如传递部门号调用存储过程,返回该部门的所有雇员及薪水信息,则该语句为

SELECTename,salINTOlv_ename,lv_salFROMscott.empWHEREdeptno=&dno;

对于上述类似的DML语句在第一次运行时进行编译,而后续再次调用,则不再编译该过程。即一次编译,多次调用,使用的相同的执行

计划。此种方式被称之为使用的是静态的SQL。

2.动态SQL

动态SQL通常是用来根据不同的需求完成不同的任务。比如分页查询,对于表emp分页,需要使用字段雇员姓名,薪水,雇用日期,且按

薪水降序生成报表,每页显示行数据。而对于表sales,需要使用字段雇员名称,客户名称,销售数量,销售日期,且按销售日期升序

排列。以上两种情况,可以创建存储过程来对其进行分页,通过定义变量,根据输入不同的表名,字段名,排序方法来生成不同的SQL

语句。对于输入不同的参数,SQL在每次运行时需要事先对其编译。即多次调用则需要多次编译,此称之为动态SQL。

动态SQL语句通常存放在字符串变量中,且SQL语句可以包含占位符(使用冒号开头)。

也可以直接将动态SQL紧跟在EXECUTE IMMEDIATE语句之后,如EXECUTEIMMEDIATE’alter table emp enable row movement’

3.两者的异同

静态SQL为直接嵌入到PL/SQL中的代码,而动态SQL在运行时,根据不同的情况产生不同的SQL语句。

静态SQL为在执行前编译,一次编译,多次运行。动态SQL同样在执行前编译,但每次执行需要重新编译。

静态SQL可以使用相同的执行计划,对于确定的任务而言,静态SQL更具有高效性。但缺乏灵活性

动态SQL使用了不同的执行计划,效率不如静态SQL,但能够解决复杂的问题。

动态SQL容易产生SQL注入,为数据库安全带来隐患。

4.动态SQL语句的几种方法

a.使用EXECUTE IMMEDIATE语句

包括DDL语句,DCL语句,DML语句以及单行的SELECT语句。该方法不能用于处理多行查询语句。

b.使用OPEN-FOR,FETCH和CLOSE语句

对于处理动态多行的查询操作,可以使用OPEN-FOR语句打开游标,使用FETCH语句循环提取数据,最终使用CLOSE语句关闭游标。

c.使用批量动态SQL

即在动态SQL中使用BULK子句,或使用游标变量时在fetch中使用BULK,或在FORALL语句中使用BULK子句来实现。

d.使用系统提供的PL/SQL包DBMS_SQL来实现动态SQL,关于该方式请参考后续博文。

二、动态SQL的语法

下面是动态SQL常用的语法之一

EXECUTE IMMEDIATE dynamic_SQL_string

[INTO defined_variable1, defined_variable2, …]

[USING [IN | OUT | IN OUT] bind_argument1,bind_argument2,

…][{RETURNING | RETURN} field1, field2, … INTO bind_argument1,

bind_argument2, …]

1.语法描述

dynamic_SQL_string:存放指定的SQL语句或PL/SQL块的字符串变量

defined_variable1:用于存放单行查询结果,使用时必须使用INTO关键字,类似于使用SELECTenameINTOv_nameFROMscott.emp;

只不过在动态SQL时,将INTO defined_variable1移出到dynamic_SQL_string语句之外。

bind_argument1:用于给动态SQL语句传入或传出参数,使用时必须使用USING关键字,IN表示传入的参数,OUT表示传出的参数,

INOUT则既可以传入,也可传出。

RETURNING | RETURN子句也是存放SQL动态返回值的变量。

2.使用要点

a.EXECUTE IMMEDIATE执行DML时,不会提交该DML事务,需要使用显示提交(COMMIT)或作为EXECUTE IMMEDIATE自身的一部分。

b.EXECUTE IMMEDIATE执行DDL,DCL时会自动提交其执行的事务。

c.对于多行结果集的查询,需要使用游标变量或批量动态SQL,或者使用临时表来实现。

d.当执行SQL时,其尾部不需要使用分号,当执行PL/SQL代码时,其尾部需要使用分号。

f.动态SQL中的占位符以冒号开头,紧跟任意字母或数字表示。

三、动态SQL的使用(DDL,DCL,DML以及单行结果集)

1.使用EXECUTE IMMEDIATE处理DDL操作

下面是一个简单的DDL操作,将其封装在存储过程之中,通过传入表名来进行调用。

CREATEORREPLACEPROCEDUREtrunc_table(table_name VARCHAR2)–创建存储过程trunc_table

IS

sql_statement VARCHAR2(100);

BEGIN

sql_statement:=’TRUNCATE TABLE ‘||table_name;–为变量进行赋值,用于生成动态SQL语句

EXECUTEIMMEDIATE sql_statement;–使用EXECUTE IMMEDIATE执行动态SQL语句

END;

/

flasher@ORCL>createtabletb2–从scott.emp生产表tb2

2asselectempno,ename,sal,deptnofromscott.emp;

flasher@ORCL>selectcount(1)fromtb2;

COUNT(1)

———-

14

flasher@ORCL>exectrunc_table(‘tb2’);–调用存储过程来对表tb2进行truncate

flasher@ORCL>selectcount(1)fromtb2;–表tb2被清空

COUNT(1)

———-

0

flasher@ORCL>insertintotb2–重新为表tb2生成记录

2selectempno,ename,sal,deptnofromscott.emp;

flasher@ORCL>commit;

2.使用EXECUTE IMMEDIATE处理DCL操作

下面使用sys帐户创建存储过程grant_sys_priv用于给用户授予权限

sys@ORCL>conn sys/redhat@orclassysdba

CREATEORREPLACEPROCEDUREgrant_sys_priv(priv VARCHAR2,username VARCHAR2)

IS

sql_stat VARCHAR2(100);

BEGIN

sql_stat:=’GRANT ‘||priv||’ TO ‘||username;

EXECUTEIMMEDIATE sql_stat;

END;

/

sys@ORCL>execgrant_sys_priv(‘connect’,’usr1′);

3.使用EXECUTE IMMEDIATE处理DML操作

在使用EXECUTE IMMEDIATE处理DML操作时,分为几种情况,即不带输入参数,带输入参数,既有输入也有输出参数或返回参数等不同情

况,下面分别对其描述。

a.没有参数传入传出的DML语句

下面的示例中,使用动态SQL删除一条记录,且未使用参数传入。

flasher@ORCL>select*fromtb2whereempno=7900;–删除前

EMPNO ENAMESALDEPTNO

———- ———- ———- ———-

7900 JAMES95030

flasher@ORCL>DECLAREsql_stat VARCHAR2(100);

2BEGIN

3sql_stat:=’DELETE FROM flasher.tb2 WHERE empno=7900′;–使用动态SQL来删除记录

4EXECUTEIMMEDIATE sql_stat;

5END;

6/

flasher@ORCL>SELECT*FROMtb2whereempno=7900;–验证删除情况

norows selected

b.有参数传入的DML语句(使用USING子句)

对于使用了参数传入的动态SQL,需要使用USING子句来指明传入的参数。在下面的示例中,为表tb2插入一条记录,在DML语句中使

用了四个占位符(占位符用以冒号开头,紧跟任意字母或数字表示)。因此在使用EXECUTE IMMEDIATE使用USING子句为其指定其参数。

DECLARE–声明变量

sql_stat VARCHAR2(100);

lv_empno tb2.empno%TYPE:=7900;

lv_ename tb2.ename%TYPE:=’JAMES’;

lv_saltb2.sal%TYPE:=950;

BEGIN

sql_stat:=’INSERT INTO tb2 VALUES(:1,:2,:3,:4)’;–DML语句中使用了占位符

EXECUTEIMMEDIATE sql_stat USING lv_empno,lv_ename,lv_sal,30;–为占位符指定参数或值

COMMIT;

END;

/

flasher@ORCL>select*fromtb2whereempno=7900;–验证插入后的结果

EMPNO ENAMESALDEPTNO

———- ———- ———- ———-

7900 JAMES95030

c.处理包含returning子句的DML语句

下面的示例中,对表tb2进行更新,使用了两个占位符,一个是:percent,一个是:eno,因此在使用EXECUTE IMMEDIATE执行动态

DML时,需要使用USING子句且带两个输入参数。其次,动态DML中使用了RETURNING sal INTO :salary,因此EXECUTE IMMEDIATE后

也必须使用RETURNING INTO varialbe_name。

DECLARE

salary NUMBER(6,2);

sql_stat VARCHAR2(100);

BEGIN

sql_stat:=’UPDATE tb2 SET sal = sal * (1 + :percent / 100)’–更新sal列,使用占位符:percent

||’ WHERE empno = :eno RETURNING sal INTO :salary’;–使用了占位符:eno,:salary,以及RETURNING子句

EXECUTEIMMEDIATE sql_stat USING&1,&2 RETURNINGINTOsalary;–必须使用USING及RETURNING子句

COMMIT;

dbms_output.put_line(‘New salary: ‘||salary);

END;

/

Entervaluefor1:10

Entervaluefor2:7900

old7:EXECUTEIMMEDIATE sql_stat USING&1,&2 RETURNINGINTOsalary;

new7:EXECUTEIMMEDIATE sql_stat USING 10,7900 RETURNINGINTOsalary;

New salary:1045

d.处理包含检索值的单行查询

下面的示例中,使用SELECT查询获得单行结果集,使用了占位符:name,因此也需要使用USING子句为其传递参数

DECLARE

sql_stat VARCHAR2(100);

emp_record tb2%ROWTYPE;

BEGIN

sql_stat:=’SELECT * FROM tb2 WHERE ename = UPPER(:name)’;–动态SQL语句为单行DQL语句

EXECUTEIMMEDIATE sql_statINTOemp_record USING’&name’;–使用USING子句为其传递参数

DBMS_OUTPUT.PUT_LINE(‘The salary is ‘||emp_record.sal||’ for ‘||emp_record.ename);

END;

/

Entervaluefor1:james

old6:EXECUTEIMMEDIATE sql_statINTOemp_record USING’&1′;

new6:EXECUTEIMMEDIATE sql_statINTOemp_record USING’james’;

The salaryis1045forJAMES

四、动态SQL的使用(处理多行结果集的查询语句)

1.使用游标变量来循环提取数据,其主要流程为

定义游标变量

TYPEcursortypeISREFCURSOR;

cursor_variable cursortype;

打开游标变量

OPENcursor_variableFORdynamic_string

[USING bind_argument[,bind_argument]…]

循环提取数据

FETCHcursor_variableINTO{var1[,var2]…|record_variable};

EXITWHENcursor_variable%NOTFOUND

关闭游标变量

CLOSEcursor_variable;

2.使用游标变量处理查询多行结果集

下面的示例中,首先定义了一个游标类型,接下来定义游标变量,以及存放结果集的变量,动态查询语句将获得多个结果集。

OPENcursornameFORSELECT…时,其SELECT语句使用了字符串变量(动态SQL),其后紧跟USING子句。

DECLARE–游标,变量的声明

TYPEemp_cur_typeISREFCURSOR;

emp_cvemp_cur_type;

emp_recordtb2%ROWTYPE;

sql_statVARCHAR2(100);

v_dnoNUMBER:=&dno;

BEGIN

sql_stat:=’SELECT * FROM tb2 WHERE deptno = :dno’;–动态多行结果集查询语句

OPENemp_cvFORsql_stat USING v_dno;–OPEN时使用动态查询语句以及USING子句来传递参数

LOOP

FETCHemp_cvINTOemp_record;–从结果集中提取记录

EXITWHENemp_cv%NOTFOUND;

dbms_output.put_line(‘Employee name:’||emp_record.ename||’,Salary:’||emp_record.sal);

ENDLOOP;

CLOSEemp_cv;

END;

/

Employeename:Henry,Salary:

Employeename:JONES,Salary:

Employeename:ADAMS,Salary:

Employeename:FORD,Salary:

 

五、动态SQL的使用(FORALL及BULK子句的使用)

1.动态SQL中使用BULK子句的语法

EXECUTE IMMEDIATE dynamic_string–dynamic_string用于存放动态SQL字符串

[BULK COLLECT INTO define_variable[,define_variable…]]–存放查询结果的集合变量

[USING bind_argument[,argument…]]–使用参数传递给动态SQL

[{RETURNING | RETURN}–返回子句

BULK COLLECT INTO return_variable[,return_variable…]];–存放返回结果的集合变量

使用bulk collect into子句处理动态SQL中的多行查询可以加快处理速度,从而提高应用程序的性能。当使用bulk子句时,集合类型可

以是PL/SQL所支持的索引表、嵌套表和VARRY,但集合元素必须使用SQL数据类型。常用的三种语句支持BULK子句,分别为EXECUTE

IMMEDIATE,FETCH和FORALL。

2.使用EXECUTE IMMEDIATE结合BULK子句处理DML语句返回子句

下面的例子,首先定义了两个索引表类型以及其变量,接下来使用动态SQL语句来更新tb2的薪水,使用EXECUTE IMMEDIATE配合BULK

COLLECT INTO来处理结果集。

DECLARE

TYPEename_table_typeISTABLEOFtb2.ename%TYPEINDEXBYBINARY_INTEGER;–定义类型用于存放结果集

TYPEsal_table_typeISTABLEOFtb2.sal%TYPEINDEXBYBINARY_INTEGER;

ename_table ename_table_type;

sal_table sal_table_type;

sql_stat VARCHAR2(120);

v_percent NUMBER:=&percent;

v_dnoNUMBER:=&dno;

BEGIN

sql_stat:=’UPDATE tb2 SET sal = sal * (1 + :percent / 100)’–动态DML语句

||’ WHERE deptno = :dno’

||’ RETURNING ename, sal INTO :name, :salary’;–使用了RETURNING子句,有返回值

EXECUTEIMMEDIATE sql_stat USING v_percent,v_dno–执行动态SQL语句

RETURNINGBULKCOLLECTINTOename_table,sal_table;–使用BULK COLLECT INTO到集合变量

FORiIN1..ename_table.COUNT–使用FOR循环读取集合变量的结果

LOOP

DBMS_OUTPUT.PUT_LINE(‘Employee ‘||ename_table(i)||’ Salary is: ‘||sal_table(i));

ENDLOOP;

END;

/

Employee Henry Salaryis:1694

Employee JONES Salaryis:3841.75

Employee ADAMS Salaryis:1573

Employee FORD Salaryis:3872

3.使用EXECUTE IMMEDIATE结合BULK子句处理多行查询

下面示例中,与前一个示例相同,只不过其动态SQL有查询语句组成,且返回多个结果集,同样使用了BULK COLLECT INTO来传递结果。

DECLARE

TYPEename_table_typeISTABLEOFtb2.ename%TYPEINDEXBYBINARY_INTEGER;–定义类型用于存放结果集

TYPEsal_table_typeISTABLEOFtb2.sal%TYPEINDEXBYBINARY_INTEGER;

ename_table ename_table_type;

sal_table sal_table_type;

sql_stat VARCHAR2(100);

BEGIN

sql_stat:=’SELECT ename,sal FROM tb2 WHERE deptno = :dno’;–动态DQL语句,未使用RETURNING子句

EXECUTEIMMEDIATE sql_statBULKCOLLECTINTOename_table,sal_table USING&dno;–使用BULK COLLECT INTO

FORiIN1..ename_table.COUNT

LOOP

DBMS_OUTPUT.PUT_LINE(‘Employee ‘||ename_table(i)||’ Salary is: ‘||sal_table(i));

ENDLOOP;

END;

/

Employee Henry Salaryis:1694

Employee JONES Salaryis:3841.75

Employee ADAMS Salaryis:1573

Employee FORD Salaryis:4259.2

4.使用FETCH子句结合BULK子句处理多行结果集

下面的示例中首先定义了游标类型,游标变量以及复合类型,复合变量,接下来从动态SQL中OPEN游标,然后使用FETCH将结果存放到复

合变量中。即使用OPEN,FETCH代替了EXECUTE IMMEDIATE来完成动态SQL的执行。

DECLARE

TYPEempcurtypeISREFCURSOR;–定义游标类型及游标变量

emp_cv empcurtype;

TYPEename_table_typeISTABLEOFtb2.ename%TYPEINDEXBYBINARY_INTEGER;–定义结果集类型及变量

ename_table ename_table_type;

sql_statVARCHAR2(120);

BEGIN

sql_stat:=’SELECT ename FROM tb2 WHERE deptno = :dno’;–动态SQL字符串

OPENemp_cvFORsql_stat–从动态SQL中打开游标

USING&dno;

FETCHemp_cvBULKCOLLECT–使用BULK COLLECT INTO提取结果集

INTOename_table;

FORiIN1..ename_table.COUNTLOOP

DBMS_OUTPUT.PUT_LINE(‘Employee Name is ‘||ename_table(i));

ENDLOOP;

CLOSEemp_cv;

END;

/

EmployeeNameisHenry

EmployeeNameisJONES

EmployeeNameisADAMS

EmployeeNameisFORD

5.使用FORALL语句中使用BULK子句

下面是FORALL子句的语法

FORALL index IN lower bound..upper bound–FORALL循环计数

EXECUTE IMMEDIATE dynamic_string–结合EXECUTE IMMEDIATE来执行动态SQL语句

USING bind_argument|bind_argument(index)–绑定输入参数

[bind_argument | bind_argument(index)]…

[{RETURNING | RETURN} BULK COLLECT INTO bind_argument[,bind_argument…]];–绑定返回结果集

FORALL子句允许为动态SQL输入变量,但FORALL子句仅支持DML(INSERT,DELETE,UPDATE)语句,不支持动态的SELECT语句。

下面的示例中,首先声明了两个复合类型以及复合变量,接下来为复合变量ename_table赋值,以形成动态SQL语句。紧接着使用FORALL

子句结合EXECUTE IMMEDIATE来提取结果集。

DECLARE–定义复合类型及变量

TYPEename_table_typeISTABLEOFtb2.ename%TYPE;

TYPEsal_table_typeISTABLEOFtb2.sal%TYPE;

ename_table ename_table_type;

sal_table sal_table_type;

sql_stat VARCHAR2(100);

BEGIN

ename_table:=ename_table_type(‘BLAKE’,’FORD’,’MILLER’);–为复合类型赋值

sql_stat:=’UPDATE tb2 SET sal = sal * 1.1 WHERE ename = :1′–定义动态SQL语句

||’ RETURNING sal INTO :2′;

FORALL iIN1..ename_table.COUNT–为FORALL设定起始值

EXECUTEIMMEDIATE sql_stat USING ename_table(i)–使用EXECUTE IMMEDIATE结合RETURNING BULK COLLECT INTO获取结果集

RETURNINGBULKCOLLECTINTOsal_table;

FORjIN1..ename_table.COUNT

LOOP

DBMS_OUTPUT.PUT_LINE(‘The new salary is ‘||sal_table(j)||’ for ‘||ename_table(j));

ENDLOOP;

END;

/

The new salaryis3135forBLAKE

The new salaryis4259.2forFORD

The new salaryis1760forMILLER

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐