存储过程学习基础

存储过程创建语法：

       create or replace procedure 存储过程名（param1 in type，param2 out type）

as

变量1 类型（值范围）;

变量2 类型（值范围）;

Begin

    Select count(*) into 变量1 from 表A where列名=param1；

    If (判断条件) then

       Select 列名 into 变量2 from 表A where列名=param1；

       Dbms_output。Put_line(‘打印信息’);

    Elsif (判断条件) then

       Dbms_output。Put_line(‘打印信息’);

    Else

       Raise 异常名（NO_DATA_FOUND）;

    End if;

Exception

    When others then

       Rollback;

End;

 

注意事项：

1，  存储过程参数不带取值范围，in表示传入，out表示输出

2，  变量带取值范围，后面接分号

3，  在判断语句前最好先用count（*）函数判断是否存在该条操作记录

4，  用select 。。。into。。。给变量赋值

5，  在代码中抛异常用 raise+异常名

 

 

以命名的异常

命名的系统异常                          产生原因

ACCESS_INTO_NULL                   未定义对象

CASE_NOT_FOUND                     CASE 中若未包含相应的 WHEN ，并且没有设置

ELSE 时

COLLECTION_IS_NULL                集合元素未初始化

CURSER_ALREADY_OPEN          游标已经打开

DUP_VAL_ON_INDEX                   唯一索引对应的列上有重复的值

INVALID_CURSOR                 在不合法的游标上进行操作

INVALID_NUMBER                       内嵌的 SQL 语句不能将字符转换为数字

NO_DATA_FOUND                        使用 select into 未返回行，或应用索引表未初始化的 

 

TOO_MANY_ROWS                      执行 select into 时，结果集超过一行

ZERO_DIVIDE                              除数为 0

SUBSCRIPT_BEYOND_COUNT     元素下标超过嵌套表或 VARRAY 的最大值

SUBSCRIPT_OUTSIDE_LIMIT       使用嵌套表或 VARRAY 时，将下标指定为负数

VALUE_ERROR                             赋值时，变量长度不足以容纳实际数据

LOGIN_DENIED                           PL/SQL 应用程序连接到 oracle 数据库时，提供了不

正确的用户名或密码

NOT_LOGGED_ON                       PL/SQL 应用程序在没有连接 oralce 数据库的情况下

访问数据

PROGRAM_ERROR                       PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL

系统包

ROWTYPE_MISMATCH                宿主游标变量与 PL/SQL 游标变量的返回类型不兼容

SELF_IS_NULL                             使用对象类型时，在 null 对象上调用对象方法

STORAGE_ERROR                        运行 PL/SQL 时，超出内存空间

SYS_INVALID_ID                         无效的 ROWID 字符串

TIMEOUT_ON_RESOURCE         Oracle 在等待资源时超时 

 

 

参数变量的设置

declare bb int ;
var_sta varchar(2);
begin
 
    loop
     select c_org into var_sta  from res_driver group by c_org;
     dbms_output.put_line(var_sta);
     end loop;
   /--   EXCEPTION
    /-- WHEN OTHERS THEN
     /--   dbms_output.put_line('执行出错了，老板！');

  end;

 

 

案例

create or replace procedure sp_java_staffarrangedata(arrangedate in varchar2,
    mycursor out sys_refcursor,mycursor2 out sys_refcursor,mycursor3 out sys_refcursor,
    monthStr out varchar2)
authid current_user
 as
  v_sql varchar2(200);
  v_sql1 varchar2(200);
  v_sql2 varchar2(200);
  v_sql3 varchar2(300);
  v_sql4 varchar2(300);
  lastMonth varchar2(10);
  arrDate date;
begin

  select to_char(add_months(to_date(arrangedate,'yyyy-mm'),-1),'yyyy-mm') res  into lastMonth from dual;
  select to_date(arrangedate,'yyyy-mm') retval into arrDate from dual;
  --1自动排班的时候，根据当前排班的月份，删除当前月份的上一次的排班数据
   v_sql:='delete from tml_admin_arrange_month_gather t where ';
   v_sql:= v_sql||' t.c_month='''||arrangedate||'''';
   execute immediate v_sql;
   commit;
  
   --2自动排班的时候，根据当前排班的月份，删除当前月份排班详情的所有数据
   v_sql:='delete from tml_admin_arrange_detail t where ';
   v_sql:= v_sql||' t.c_month='''||arrangedate||'''';
   execute immediate v_sql;
   commit;
 
  --3获取没有离职的清机员数据
  v_sql1:='select * from tml_admin t where t.c_status!=''3'' order by d_date desc ';
  open mycursor for(v_sql1);
 
  ---4获取老员工和新员工的搭档数据
  v_sql2:='select * from task_staff_partner_choose ';
  open mycursor2 for(v_sql2);
 
  --5获取上一个月的加班数据
   v_sql3:='select * from tml_admin_arrange_month_gather t ';
   v_sql3:= v_sql3||' where t.c_month='''||lastMonth||'''';
--   dbms_output.put_line('v_sql4:'||v_sql4);
   open mycursor3 for(v_sql3);
  
   --6获取排班月份的天数组合
   v_sql4:='select wm_concat(lpad(rownum,2,''0'')) day from dual ';
   v_sql4:= v_sql4||' connect by rownum<=to_char(last_day('''||arrDate||'''';
   v_sql4:= v_sql4||' ),''DD'') ';
--   dbms_output.put_line('v_sql4:'||v_sql4);
   execute immediate v_sql4 into monthStr;
 
end sp_java_staffarrangedata;

 

 

------------范例

CREATE OR REPLACE PROCEDURE fims.UP_BALANCE_TOTAL_COUNTRY
--///*利用外资分国别表平衡检查*/
( V_USER_DEPT_CODE          IN  VARCHAR2,
  V_REPORT_PERIOD           IN  VARCHAR2, --//要求格式YYYYMM
  V_DATA_TYPE               IN  VARCHAR2, --//数据类型：置为’2’，外商直接投资
  V_RetResult               OUT VARCHAR2,  --结果：’0’：失败，’1’：成功。
  V_RetMsg                  OUT VARCHAR2,
  RetCur                    OUT SYS_REFCURSOR
  )
AS
  V_DEPT_QUERY_CODE         VARCHAR2(10);
BEGIN

  V_RetResult := '1';
  V_RetMsg := '成功';
  V_DEPT_QUERY_CODE := UF_GET_QUERY_CODE(V_USER_DEPT_CODE);

  INSERT INTO GTMP_QUERY_SIC_BALANCE
         ( REPORT_PERIOD,DEPT_CODE,STAT_DEPT_CODE,ENTP_TYPE_CODE,
           ENTP_NUMBER,CONTRACT_INVESTMENT,ACTUAL_INVESTMENT )
  SELECT A.REPORT_PERIOD,A.DEPT_CODE,A.STAT_DEPT_CODE,A.ENTP_TYPE_CODE,
         NVL(SUM(A.ENTP_NUMBER),0),
         NVL(SUM(A.CONTRACT_INVESTMENT),0),
         NVL(SUM(A.ACTUAL_INVESTMENT),0)
  FROM T_FI_TOTAL_SUM A
  WHERE A.DEPT_CODE = V_USER_DEPT_CODE
  AND   A.REPORT_PERIOD = V_REPORT_PERIOD
  AND   SUBSTR(A.ENTP_TYPE_CODE,1,1) = V_DATA_TYPE
  GROUP BY A.REPORT_PERIOD,A.DEPT_CODE,A.STAT_DEPT_CODE,A.ENTP_TYPE_CODE;

  INSERT INTO GTMP_QUERY_SIC_BALANCE
         ( REPORT_PERIOD,DEPT_CODE,STAT_DEPT_CODE,ENTP_TYPE_CODE,
           ENTP_NUMBER_1,CONTRACT_INVESTMENT_1,ACTUAL_INVESTMENT_1 )
  SELECT A.REPORT_PERIOD,A.DEPT_CODE,A.STAT_DEPT_CODE,A.ENTP_TYPE_CODE,
         NVL(SUM(A.ENTP_NUMBER),0),
         NVL(SUM(A.CONTRACT_INVESTMENT),0),
         NVL(SUM(A.ACTUAL_INVESTMENT),0)
  FROM T_FI_TOTAL_COUNTRY A
  WHERE A.DEPT_CODE = V_USER_DEPT_CODE
  AND   A.REPORT_PERIOD = V_REPORT_PERIOD
  AND   SUBSTR(A.ENTP_TYPE_CODE,1,1) = V_DATA_TYPE
  GROUP BY A.REPORT_PERIOD,A.DEPT_CODE,A.STAT_DEPT_CODE,A.ENTP_TYPE_CODE;

 

  --//置各下级单位名称，合计行名称用“合计”
  UPDATE GTMP_QUERY_SIC_BALANCE A
  SET STAT_DEPT_NAME=(SELECT B.DEPT_NAME
                      FROM T_CODE_DEPT B
                      WHERE A.STAT_DEPT_CODE = B.DEPT_CODE);

  UPDATE GTMP_QUERY_SIC_BALANCE A
  SET ENTP_TYPE_NAME=(SELECT B.ENTP_TYPE_SHORT_NAME
                      FROM T_CODE_ENTP_TYPE B
                      WHERE A.ENTP_TYPE_CODE = B.ENTP_TYPE_CODE);

  OPEN RetCur FOR
       SELECT A.STAT_DEPT_NAME,A.ENTP_TYPE_NAME,
               NVL(SUM(A.ENTP_NUMBER),0),
               NVL(SUM(A.CONTRACT_INVESTMENT),0),
               NVL(SUM(A.ACTUAL_INVESTMENT),0),
               NVL(SUM(A.ENTP_NUMBER_1),0),
               NVL(SUM(A.CONTRACT_INVESTMENT_1),0),
               NVL(SUM(A.ACTUAL_INVESTMENT_1),0)
       FROM GTMP_QUERY_SIC_BALANCE A
       GROUP BY A.STAT_DEPT_NAME,A.ENTP_TYPE_NAME
       HAVING NVL(SUM(A.CONTRACT_INVESTMENT),0) <> NVL(SUM(A.CONTRACT_INVESTMENT_1),0)
       OR NVL(SUM(A.ACTUAL_INVESTMENT),0) <> NVL(SUM(A.ACTUAL_INVESTMENT_1),0);

 

END UP_BALANCE_TOTAL_COUNTRY;

 

---------------------------

CREATE OR REPLACE Procedure fims.UP_GET_TABLE_COL_STR_XH
(V_TableName in varchar2,
 V_ContainKey in varchar2,
 V_column_id in integer,
 V_Ret out varchar2)
as
V_columnName varchar2(30);
V_columnList sys_refcursor;
V_count integer;
Begin
  V_count := 0;
  open V_columnList for
  select column_name from user_tab_columns where table_name= V_TableName and column_id >= v_column_id order by column_id;

  Loop
     fetch V_columnList into V_columnName;
     exit when V_columnList%Notfound;
     V_count := V_count + 1;

     if (V_ContainKey = '0' and (V_count = 1 or V_Count = 2)) then
        V_count := v_count;
     else
        V_Ret := V_Ret || V_columnName || ',';
     end if;

  end loop;

  close V_columnList;

  v_ret := substr(v_ret,1,length(v_ret)-1);

End UP_GET_TABLE_COL_STR_XH;