PL/SQL中使用动态SQL编程
在PL/SQL程序设计过程中,会遇到很多必须使用动态sql的地方,oracle系统所提供的DMBS_SQL包可以帮助你解决问题。
(一)介绍
DBMS_SQL系统包提供了很多函数及过程,现在简要阐述其中使用频率较高的几种:
function open_cursor:打开一个动态游标,并返回一个整型;
procedure close_cursor(c in out integer);关闭一个动态游标,参数为open_cursor所打开的游标;
procedure parse(c in integer, statement in varchar2, language_flag in integer):对动态游标所提供的sql语句进行解析,参数C表示游标,statement为sql语句,language-flag为解析sql语句所用oracle版本,一般有V6,V7跟native(在不明白所连database版本时,使用native);
procedure define_column(c in integer, position in integer, column any datatype, [column_size in integer]):定义动态游标所能得到的对应值,其中c为动态游标,positon为对应动态sql中的位置(从1开始),column为该值所对应的变量,可以为任何类型,column_size只有在column为定义长度的类型中使用如VARCHAR2,CHAR等(该过程有很多种情况,此处只对一般使用到的类型进行表述);
function execute(c in integer):执行游标,并返回处理一个整型,1表示成功,0表示失败,代表处理结果(对insert,delete,update才有意义,而对select语句而言可以忽略);
function fetch_rows(c in integer):对游标进行循环取数据,并返回一个整数,为0时表示已经取到游标末端;
procedure column_value(c in integer, position in integer, value):将所取得的游标数据赋值到相应的变量,c为游标,position为位置,value则为对应的变量;
procedure bind_variable(c in integer, name in varchar2, value):定义动态sql语句(DML)中所对应字段的值,c为游标,name为字段名称,value为字段的值;
以上是在程序中经常使用到的几个函数及过程,其他函数及过程请参照oracle所提供定义语句dbmssql.sql
(二)一般过程
对于一般的select操作,如果使用动态的sql语句则需要进行以下几个步骤:
open cursor--->parse--->define column--->excute--->fetch rows--->close cursor;
而对于dml操作(insert,update)则需要进行以下几个步骤:
open cursor--->parse--->bind variable--->execute--->close cursor;
对于delete操作只需要进行以下几个步骤:
open cursor--->parse--->execute--->close cursor;
(三)实例应用
1. declare
v_cid integer;
v_updatestr varchar2(100);
v_rowupdated integer;
begin
v_cid:=dbms_sql.open_cursor;
v_updatestr:='update emp set comm=400 where empno=7499';
dbms_sql.parse(v_cid,v_updatestr,dbms_sql.native);
v_rowupdated:=dbms_sql.execute(v_cid);
dbms_sql.close_cursor(v_cid);
exception
when others then
dbms_sql.close_cursor(v_cid);
raise;
end;
-----------------------
2.create or replace function updatecomm(p_comm emp.comm%type, p_empno emp.empno%type
return integer as
v_cid integer;
v_updatestr varchar2(100);
v_rowupdated integer;
begin
v_cid:=dbms_sql.open_cursor;
v_updatestr:='update emp set comm=:comm where empno=:empno';
dbms_sql.parse(v_cid,v_updatestr,dbms_sql.native);
dbms_sql.bind_variable(v_cid,'comm','p_comm');
dbms_sql.bind_variable(v_cid,'empno','p_empno');
v_rowupdated:=dbms_sql.execute(v_cid);
dbms_sql.close_cursor(v_cid);
return p_rowsupdated;
exception
when others then
dbms_sql.close_cursor(v_cid);
raise;
end;
调用--
declare
a integer;
begin
a:=updatecomm(5000,a);
dbms_output.put_line(a);
end;
----------------------
3.create or replace procedure dynamiccopy(p_deptno1 emp.deptno%type default null,p_deptno2 emp.deptno%type default null)
as
v_cid integer;
v_select varchar2(100);
v_empno char(4);
v_ename varchar2(10);
v_deptno char(2);
v_dummy integer;
begin
v_cid:=dbms_sql.open_cursor;
v_select:='select empno,ename,deptno from emp where deptno in(:d1,:d2)';
dbms_sql.parse(v_cid,v_select,dbms_sql.native);
dbms_sql.bind_variable(v_cid,'d1',p_deptno1);
dbms_sql.bind_variable(v_cid,'d2',p_deptno2);
dbms_sql.define_column(v_cid,1,v_empno,4);
dbms_sql.define_column(v_cid,2,v_ename,10);
dbms_sql.define_column(v_cid,3,v_deptno,2);
v_dummy:=dbms_sql.execute(v_cid);
loop
if dbms_sql.fetch_rows(v_cid)=0 then
exit;
end if;
dbms_sql.column_value(v_cid,1,v_empno);
dbms_sql.column_value(v_cid,2,v_ename);
dbms_sql.column_value(v_cid,3,v_deptno);
insert into emp1(empno,ename,deptno) values(v_empno,v_ename,v_deptno);
end loop;
dbms_sql.close_cursor(v_cid);
commit;
exception
when others then
dbms_sql.close_cursor(v_cid);
raise;
end;
-----------------------
4.DDL语句:DDL中联编变量是非法的,即使在解析后不能够调用bind_variable过程。另外,DDL解析后立即执行,不需要调用EXECUTE过程,即使调用了也没有用。
create or replace procedure recreatetable(p_table in varchar2,p_description in varchar2)
as
v_cursor number;
v_createstring varchar2(100);
v_dropstring varchar2(100);
begin
v_cursor:=dbms_sql.open_cursor;
v_dropstring:='drop table'||p_table;
begin
dbms_sql.parse(v_cursor,v_dropstring,dbms_sql.v7);
exception
when others then
if sqlcode!=-942 then
raise;
end if;
end;
v_createstring:='create table'||p_table||p_description;
dbms_sql.parse(v_cursor,v_createstring,dbms_sql.native);
dbms_sql.close_cursor(v_cursor);
exception
when others then
dbms_sql.close_cursor(v_cursor);
raise;
end;
----------------------
5.自己的实例
PACKAGE BODY GSOL_INQUIRE_ALL
AS
/* In use by Expansion to indicate completion of item population by both Verified and Unverified suppliers */
PROCEDURE PROCESS_ITEM_EXPANSION_STATUS(in_inquiry_id IN inquire_all.inquiry_id%TYPE,
in_item_populate_status IN inquire_all.item_populate_status%TYPE DEFAULT 'Y',
in_supplier_cnt IN inquire_all.supplier_cnt%TYPE,
in_product_cnt IN inquire_all.product_cnt%TYPE)
IS
v_CurMonth number;
v_PrevMonth number;
v_cursor number;
v_row number;
v_sqlStr varchar2(500);
BEGIN
v_CurMonth := to_number(to_char(sysdate, 'MM'));
IF (v_CurMonth=1)
THEN
v_PrevMonth := 12;
ELSE
v_PrevMonth := v_CurMonth -1;
END IF;
v_sqlStr := ' SET ITEM_POPULATE_STATUS = '|| ''''|| in_item_populate_status || ''''|| ', ITEM_POPULATE_DATE = sysdate, '
|| ' SUPPLIER_CNT = ' || in_supplier_cnt || ', PRODUCT_CNT = ' || in_product_cnt || ' WHERE INQUIRY_ID = '||in_inquiry_id
|| ' AND FORM_COMPLETE_STATUS = ''Y''';
/* update supplier/product count in current month's table */
BEGIN
v_cursor := dbms_sql.open_cursor;
dbms_sql.parse(v_cursor,'UPDATE INQUIRE_ALL_' || v_CurMonth || v_sqlStr, dbms_sql.NATIVE);
v_row := dbms_sql.execute(v_cursor);
dbms_sql.close_cursor(v_cursor);
EXCEPTION
WHEN others THEN
dbms_sql.close_cursor(v_cursor);
RAISE_APPLICATION_ERROR(-20200,'Error in updating table [INQUIRE_ALL_' || v_CurMonth ||'] SQLERROR: ' || SQLERRM(sqlcode));
END;
/* update supplier/product count in previous month's tables */
BEGIN
v_cursor := dbms_sql.open_cursor;
dbms_sql.parse(v_cursor, 'UPDATE INQUIRE_ALL_' || v_PrevMonth || v_sqlStr, dbms_sql.NATIVE);
v_row := dbms_sql.execute(v_cursor);
dbms_sql.close_cursor(v_cursor);
EXCEPTION
WHEN others THEN
dbms_sql.close_cursor(v_cursor);
RAISE_APPLICATION_ERROR(-20200,'Error in updating table [INQUIRE_ALL_' || v_PrevMonth ||'] SQLERROR: ' || SQLERRM(sqlcode));
END;
COMMIT;
EXCEPTION
WHEN others THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20200, 'Error updating table inquire_all_xx for population');
END PROCESS_ITEM_EXPANSION_STATUS;
//其他procedure...
END GSOL_INQUIRE_ALL;
说明:简单的说,本例子,是用来更新本月和上月的数据。
1.v_sqlStr := ' SET ITEM_POPULATE_STATUS = '|| ''''|| in_item_populate_status || ''''|| ', ITEM_POPULATE_DATE = sysdate, '
|| ' SUPPLIER_CNT = ' || in_supplier_cnt || ', PRODUCT_CNT = ' || in_product_cnt || ' WHERE INQUIRY_ID = '||in_inquiry_id
|| ' AND FORM_COMPLETE_STATUS = ''Y''';中,in_item_populate_status 为character,两边要加上两个单引号,如后面的FORM_COMPLETE_STATUS = ''Y'' 一样.当传的参数为character的时候要注意。
2.在dbms_sql.parse(v_cursor, queryString, dbms_sql.NATIVE); 的queryString 中,不可以有运算.例如v_PrevMonth,必须提前计算,不可以直接queryString中用 v_CurMonth-1
3.raise error: RAISE_APPLICATION_ERROR(-20200, 'Error updating table inquire_all_xx for population');)自定义error.
RAISE_APPLICATION_ERROR(errorCode,description);
这样的error在java中调用时,可以被打印.
(转)RAISE_APPLICATION_ERROR 的声明:
可能不是很多人知道 RAISE_APPLICATION_ERROR 的用途是什么,虽然从字面上已经猜到这个函数是干什么用的。
其实 RAISE_APPLICATION_ERROR 是将应用程序专有的错误从服务器端转达到客户端应用程序。
RAISE_APPLICATION_ERROR 的声明:
PROCEDURE RAISE_APPLICATION_ERROR( error_number_in IN NUMBER, error_msg_in IN VARCHAR2);
里面的错误代码和内容,都是自定义的。说明是自定义,当然就不是系统中已经命名存在的错误类别,是属于一种自定义事务错误类型,才调用此函数。
error_number_in 之容许从 -20000 到 -20999 之间,这样就不会与 ORACLE 的任何错误代码发生冲突。
error_msg_in 的长度不能超过 2K,否则截取 2K。
举个例吧:
阻止小于18岁的用户增加到数据库 employee 表中
CREATE OR REPALCE TRIGGER minimun_age_check
BEFORE INSERT ON employee
FOR EACH ROW
BEGIN
IF ADD_MONTHS( :new.birth_date, 18*12) > SYSDATE
THEN
RAISE_APPLICATION_ERROR(-20001, 'Employees must at least eighteen years of age.');
END IF;
END;
在客户端,你可以写一个类似下面的程序,来测试一下。
DECLARE
no_babies_allowed EXCEPTION;
/*将名称与用于触发器中的错误号码关联起来*/
PRAGMA EXCEPTION_INIT(no_babies_allowed, -20001);
BEGIN
INSERT INTO employee ....;
EXCEPTION
WHEN no_babies_allowed
THEN
/*
|| SQLERRM 将传递给内置过程 RAISE_APPLICATION_ERROR 的消息返回
*/
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
4. DBMS_OUTPUT.PUT_LINE 的使用, 这里没有用到,但是有时候可以用。找了个例子。
SET serveroutput on
DECLARE
/* Declare variables to be used in this block. */
v_Num1 NUMBER := 1;
v_Num2 NUMBER := 2;
v_String1 VARCHAR2(50) := 'Hello World!';
v_String2 VARCHAR2(50) := '-- This message brought to you by PL/SQL!';
v_OutputStr VARCHAR2(50);
BEGIN
INSERT INTO temp_table (num_col, char_col)
VALUES (v_Num1, v_String1);
INSERT INTO temp_table (num_col, char_col)
VALUES (v_Num2, v_String2);
SELECT char_col
INTO v_OutputStr
FROM temp_table
WHERE num_col = v_Num1;
DBMS_OUTPUT.PUT_LINE(v_OutputStr);
SELECT char_col
INTO v_OutputStr
FROM temp_table
WHERE num_col = v_Num2;
DBMS_OUTPUT.PUT_LINE(v_OutputStr);
5. 关于Exception,本例中间block有捕获Exception,最后还有一个.做testing的时候,我把最后那个Exception comment掉,否则,前面的如果有错,以后只到最后那个Exception才停止。下面是转载,原因有些相近。
(转自su_fisher的ORACLE专栏)异常结束后继续执行收藏
当你运行一个过程,例如:
Declear
Begin
DELETE TABLE t_a where .....;
UPDATE t_b set .....;
DELETE TABLE t_c where .....;
Exception
WHEN OTHERS THEN
..............;
End;
如果其中一步发生异常,但我依然想继续执行下面的程序,按照上面的代码是不可能的。只要发生异常程序只会去到 Exception 的块中。
那怎么办呢?
只要你将程序改成:
Declear
Begin
begin
DELETE TABLE t_a where .....;
Exception
WHEN OTHERS THEN
null;
end;
begin
UPDATE t_b set .....;
Exception
WHEN OTHERS THEN
null;
end;
begin
DELETE TABLE t_c where .....;
Exception
WHEN OTHERS THEN
null;
end;
Exception
WHEN OTHERS THEN
..............;
End;
现在就可以实现你想要的程序流程了。
第一步,删除表 t_a 时,如果发生异常,程序就会进入匿名块的异常处理,而且异常处理并不进行任何处理,之后程序将进行 UPDATE t_b 的步骤。
6.testing/调用 :
在SQL Navigator中,exec GSOL_INQUIRE_ALL.PROCESS_ITEM_EXPANSION_STATUS(8000003045213,'I',1067,1077);
shell scripts中也如此.