动态调用存储过程代码实例.doc

这几天公司数据转换中用到了动态调用存储过程，自己做了一个最简单的练习,现看一下代码例子：代码大概描述：将要调用的包中的存储过程名全部存放在一张表中，循环取出，拼接成动态调用存储过程的调用语句，四个过程分别实现两个数的加、减、乘、除运算。定义包头：create or replace package PKG_T_CYP is - Author : ADMINISTRATOR - Created : 2012-5-22 9:22:16 - Purpose : - Public type declarations -type is ; - Public constant declarations - constant := ; -定义常量 DEF_OK CONSTANT NUMBER := 1; DEF_ERR CONSTANT NUMBER := -1; - Public variable declarations - ; -定义变量 A NUMBER := 10; B NUMBER := 11; - Public function and procedure declarations -定义存储过程,两个数相加 PROCEDURE Prc_t_plus(PRM_first IN VARCHAR2, PRM_second IN VARCHAR2, PRM_third OUT VARCHAR2, PRM_APPCODE OUT VARCHAR2, PRM_ERRORMSG OUT VARCHAR2); -定义存储过程,两个数相减 PROCEDURE Prc_t_minus(PRM_first IN VARCHAR2, PRM_second IN VARCHAR2, PRM_third OUT VARCHAR2, PRM_APPCODE OUT VARCHAR2, PRM_ERRORMSG OUT VARCHAR2); -定义存储过程,两个数相乘 PROCEDURE Prc_t_multiply(PRM_first IN VARCHAR2, PRM_second IN VARCHAR2, PRM_third OUT VARCHAR2, PRM_APPCODE OUT VARCHAR2, PRM_ERRORMSG OUT VARCHAR2); -定义存储过程,实现两个数相除 PROCEDURE Prc_t_divide(PRM_first IN VARCHAR2, PRM_second IN VARCHAR2, PRM_third OUT VARCHAR2, PRM_APPCODE OUT VARCHAR2, PRM_ERRORMSG OUT VARCHAR2); -此过程进行测试 PROCEDURE Prc_t_callpro(PRM_first IN VARCHAR2, PRM_second IN VARCHAR2, PRM_third OUT VARCHAR2, PRM_APPCODE OUT VARCHAR2, PRM_ERRORMSG OUT VARCHAR2); -function ( ) return ;end PKG_T_CYP; 实现包体：create or replace package body PKG_T_CYP is - Private type declarations -type is ; - Private constant declarations - constant := ; - Private variable declarations - ; - Function and procedure implementations /* function ( ) return is ; begin ; return(); end;*/ -存储过程实现 PROCEDURE Prc_t_plus(PRM_first IN VARCHAR2, PRM_second IN VARCHAR2, PRM_third OUT VARCHAR2, PRM_APPCODE OUT VARCHAR2, PRM_ERRORMSG OUT VARCHAR2) IS sumall NUMBER; BEGIN sumall := PRM_first + PRM_second; PRM_third := sumall; dbms_output.put_line(两个数的和是 : | PRM_third); PRM_APPCODE := DEF_OK; PRM_ERRORMSG := 成功!; EXCEPTION WHEN OTHERS THEN PRM_APPCODE := DEF_ERR; PRM_ERRORMSG := 失败!; RETURN; END Prc_t_plus; PROCEDURE Prc_t_minus(PRM_first IN VARCHAR2, PRM_second IN VARCHAR2, PRM_third OUT VARCHAR2, PRM_APPCODE OUT VARCHAR2, PRM_ERRORMSG OUT VARCHAR2) IS mins NUMBER; BEGIN mins := PRM_first - PRM_second; PRM_third := mins; dbms_output.put_line(两个数的差是 : | PRM_third); PRM_APPCODE := DEF_OK; PRM_ERRORMSG := 求两个数的差成功!; EXCEPTION WHEN OTHERS THEN PRM_APPCODE := DEF_ERR; PRM_ERRORMSG := 求两个数的差时出错!; END Prc_t_minus; PROCEDURE Prc_t_multiply(PRM_first IN VARCHAR2, PRM_second IN VARCHAR2, PRM_third OUT VARCHAR2, PRM_APPCODE OUT VARCHAR2, PRM_ERRORMSG OUT VARCHAR2) IS mul number; BEGIN mul := PRM_first * PRM_second; PRM_third := mul; Dbms_Output.put_line(两个数的积是: | PRM_third); PRM_APPCODE := DEF_OK; PRM_ERRORMSG := 求两个数的积是成功!; EXCEPTION WHEN OTHERS THEN PRM_APPCODE := DEF_ERR; PRM_ERRORMSG := 求两个数的积时出错!; END Prc_t_multiply; -调用此过程进行测试 PROCEDURE Prc_t_callpro(PRM_first IN VARCHAR2, PRM_second IN VARCHAR2, PRM_third OUT VARCHAR2, PRM_APPCODE OUT VARCHAR2, PRM_ERRORMSG OUT VARCHAR2) IS -ret number; BEGIN FOR pronamecur IN (select proname from dynamicpro) LOOP -动态调用存储过程 EXECUTE IMMEDIATE begin PKG_T_CYP.PRC_T_ | name | (:1,:2,:3,:4,:5); end; -参数列表赋值 USING IN PRM_first, IN PRM_second, OUT PRM_third, OUT PRM_APPCODE, OUT PRM_ERRORMSG; END LOOP; END Prc_t_callpro; PROCEDURE Prc_t_divide(PRM_first IN VARCHAR2, PRM_second IN VARCHAR2, PRM_third OUT VARCHAR2, PRM_APPCODE OUT VARCHAR2, PRM_ERRORMSG OUT VARCHAR2) IS -定义局部变量 devide number; BEGIN devide := PRM_first / PRM_second; PRM_third := devide; PRM_APPCODE := DEF_OK; PRM_ERRORMSG := 求两个数的商成功!; -异常处理 EXCEPTION WHEN ZERO_DIVIDE THEN PRM_APPCODE := DEF_ERR; PRM_ERRORMSG := 除数不能为零!; WHEN OTHERS THEN PRM_APPCODE := DEF_ERR; PRM_ERRORMSG := 求两个数的商失败!; END Prc_t_divide; -包头中可以不定义存储过程,直接在包体中定义实现 PROCEDURE Prc_t_test is begin DBMS_OUTPUT.put_line(ddd); END Prc_t_test;begin - Initialization -; dbms_output.put_line(ddd);end PKG_T_CYP;创建表DYNAMICPRO:create table DYNAMICPRO( PRONAME VARCHAR2(100)插入数据，PRONAME字体值就是包中定义的四个过程的名字：in