14开发动态SQL

来源：互联网发布：程序员接单知乎编辑：程序博客网时间：2024/04/30 10:05

一：区别

静态SQL：在PL/SQL中直接嵌入的SQL语句。静态SQL性能优于动态SQL

动态SQl：在运行PL/SQL时动态输入的SQL语句。

二：动态SQL处理方法

1. EXECUTE IMMEDIATE语句

可以处理：DDL，DML，DCL以及单行SELECT语句。注：不能用于处理多行查询语句

2. OPEN-FOR，FETCH和CLOSE语句

使用OPEN-FOR语句打开游标，使用FETCH语句循环提取数据。

3. 使用批量动态SQL

使用EXECUTE IMMEDIATE语句

1.DCL

CREATE OR REPLACE PROCEDURE drop_table(table_name VARCHAR2)

sql_statementVARCHAR2(100);

BEGIN

sql_statement:='DROPTABLE'||table_name;

EXECUTE IMMEDIATEsql_statement;

END;

2.DML

DECLARE

sql_stat VARCHAR2(100);

BEGIN

sql_stat := 'UPDATE emp SETsal=sal*(1+:percent/100)'||'WHERE deptno=:dno';

EXECUTE IMMEDIATE sql_stat USING&1,&2;

END;

3.RETURNING语句

DECLARE

salaryNUMBER(6,2);

sql_statVARCHAR2(100);

BEGIN

sql_stat:='UPDATEemp SET sal=sal*(1+:percent/100)'

||'WHEREempno=:eno RETURNING sal INTO :salary';

EXECUTE IMMEDIATEsql_stat USING &1,&2

RETURNING INTOsalary;

dbms_output.put_line('新工资:'||salary);

END;

4.用游标的动态SQL

DECLARE

TYPE empcurtyp IS REF CURSOR;

emp_cv empcurtyp;

emp_record emp%ROWTYPE;

sql_stat VARCHAR2(100);

BEGIN

sql_stat:='SELECT * FROM emp WHEREdeptno=:dno';

OPEN emp_cv FOR sql_stat USING&dno;

LOOP

FETCH emp_cv INTO emp_record;

EXIT WHEN emp_cv%NOTFOUND;

dbms_output.put_line('雇员名:'||emp_record.ename||'，工资:'||emp_record.sal);

END LOOP;

CLOSE emp_cv;

END;

5.BULK COLLECT INTO

DECLARE

TYPE ename_table_type IS TABLE OF emp.ename%TYPE

INDEX BY BINARY_INTEGER;

TYPE sal_table_type IS TABLE OF emp.sal%TYPE

INDEX BY BINARY_INTEGER;

ename_table ename_table_type;

sal_table sal_table_type;

sql_stat VARCHAR2(100);

BEGIN

sql_stat:='UPDATE emp SETsal=sal*(1+:percent/100)'

||'WHERE deptno=:dno'

||'RETURNING ename,sal INTO :name,:salary';

EXECUTE IMMEDIATE sql_stat USING 10,10

RETURNING BULK COLLECT INTOename_table,sal_table;

FOR i IN 1..ename_table.COUNT LOOP

dbms_output.put_line('雇员'||ename_table(i)||'的新工资为'||sal_table(i));

END LOOP;

END;

6.在FETCH语句中使用BULK子句。

DECLARE

TYPE empcurtyp IS REF CURSOR;

emp_cv empcurtyp;

TYPE ename_table_type IS TABLE OF emp.ename%TYPE

INDEX BY BINARY_INTEGER;

ename_table ename_table_type;

sql_stat VARCHAR2(100);

BEGIN

sql_stat:='SELECT ename FROM emp WHERE job:=title';

OPEN emp_cv FOR sql_stat USING '&job';

FETCH emp_cv BULK COLLECT INTO ename_table;

FOR i IN 1..ename_table.COUNT LOOP

dbms_output.put_line(ename_table(i));

END LOOP;

CLOSE emp_cv;

END;

7.FORALL语句中使用BULK子句：可以为输入变量提供多个输入值。但只适用于DELETE，UPDATE，DELETE语句。

DECLARE

TYPE ename_table_type IS TABLE OF emp.ename%TYPE;

TYPE sal_table_type IS TABLE OF emp.sal%TYPE;

ename_table ename_table_type;

sal_table sal_table_type;

sql_stat VARCHAR2(100);

BEGIN

ename_table:=ename_table_type('SCOTT','SMITH','CLARK');

sql_stat:='UPDATE emp SET sal=sal*1.1 WHERE ename=:1'

||'RETURNING sal INTO :2';

FORALL i IN 1..ename_table.COUNT

EXECUTE IMMEDIATE sql_stat USING ename_table(i)

RETURNING BULK COLLECT INTO sal_table;

FOR j IN 1..ename_table.COUNT LOOP

dbms_output.put_line('雇员'||ename_table(j)||'的新工资为'||sal_table(j));

END LOOP;

END;

习题：

编写PL/SQL在动态SQL中使用BULK子句根据输入的多个订单号，更新交付日期当前日期，并返回每个订单对应的客户号。

DECLARE

2 sql_stat VARCHAR2(100);

3 TYPE ordid_table_type IS TABLE OF ord.ord_id%TYPE;

4 ordid_table ordid_table_type;

5 TYPE cid_table_type IS TABLE OF ord.customer_id%TYPE;

6 cid_table cid_table_type;

7 BEGIN

8 sql_stat:='UPDATE ord SET ship_date=SYSDATE'||

9 'WHERE ord_id=:b'||

10 'RETURNING customer_id INTO :c';

11 ordid_table:=ordid_table_type(&1,&2,&3);

12 FORALL i IN 1..ordid_table.COUNT

13 EXECUTE IMMEDIATE sql_stat USING ordid_table(i)

14 RETURNING BULK COLLECT INTO cid_table;

15 FOR i IN 1..cid_table.COUNT LOOP

16 dbms_output.put_line('订单:'||ordid_table(i)||',客户:'||cid_table(i));

17 END LOOP;

18 END;