存储过程举例

存储过程编写

1、参数列表包含三部分

参数模式 参数名 参数类型

结构：

in stuname varchar(20)

参数模式：

in：该参数可以作为输入，也就是该参数需要调用方传入值

out：该参数可以作为输入，也可以作为返回值

inout：既可以作为输入又可以作为输出，也即该参数需要调用方传入值，又可以当成返回值

-- 定义变量

SET @n=2;

CALL proc_person_findById(@n); -- 存储过程的调用

SHOW PROCEDURE STATUS; -- 查看所有存储过程

SHOW CREATE PROCEDURE proc_person_findById; -- 查看具体存储过程

DROP PROCEDURE proc_person_findById; -- 删除存储过程

写法：

create procedure 存储过程名（参数列表）is

begin

逻辑

end

举例（for循环）：

create or replace procedure PROC_STATISTICS_USER(in Date in varchar2)

IS

cityName VARCHAR2(20);

userCount NUMBER(10);

BEGIN

for cur_row in (

SELECT u.city as city, count(*)as tatal FROM z_test_user u

WHERE u.create_time >= to_date(inDate, 'yyyy-MM-dd HH24:mi')

AND u.create_time < to_date(inDate, 'yyyy-MM-dd HH24:mi')+1

GROUP BY u.city ) loop

cityName := cur_row.city;

userCount:= cur_row.tatal;

insert into Z_TEST_USER_LOG values (SQL_Z_TEST_USER_LOG.NEXTVAL, cityName, userCount,SYSDATE);

commit;

end loop;

END PROC_STATISTICS_USER;

举例（数组下标展示）

create or replace procedure PROC_STATISTICS_USER_CUR(inDate in varchar2)

IS

cityName VARCHAR2(20); --定义变量

userCount NUMBER(10); --定义变量

-- storeName VARCHAR2(20); --1定义变量

Cursor cur_row IS

SELECT u.city as city, count(*)as tatal FROM z_test_user u

WHERE u.create_time >= to_date(inDate, 'yyyy-MM-dd HH24:mi')

AND u.create_time < to_date(inDate, 'yyyy-MM-dd HH24:mi')+1

GROUP BY u.city;

BEGIN

FOR c_row IN cur_row LOOP

cityName := c_row.city; --赋值

userCount:= c_row.tatal; --赋值

-- 有些时候，有其他参数需要从其他表中查询下面给出一个举例

-- select storeName

-- into storeName --2赋值

-- from STORE_INFO t

-- where t.STORE_NAME =c_row.city;

insert into Z_TEST_USER_LOG values (SQL_Z_TEST_USER_LOG.NEXTVAL, cityName, userCount,SYSDATE);

-- storeName -- 3 使用

commit;

END loop;

END PROC_STATISTICS_USER_CUR;

说明：a）首先看一下红色字体，storeName需要从其他表中查询，使用举例

b) 游标使用格式：在is后Cursor 变量名 IS ... ;

c) begin中使用：FOR c_row IN cur_row LOOP... END loop;赋值，插入等操作，此时for中in是直接使用游标进行。可以理解cur_row为父亲游标，c_row为子游标，就如数据和数组下标关系。

举例（异常）

有时候执行存储过程会报错，调用者需要知道执行结果是否报错，此时需要定义传出参数，并在异常块进行赋值。捕获到异常之后:

1.记录错误相关信息 放入相关日志表 SQLCODE SQLERRM

2.如果有事务相关的操作 一般是要rollback

（1）创建存储过程

create or replace procedure PROC_STATISTICS_USER_EX(inDate in varchar2, out_code out int, out_msg out varchar2)

IS

val int; --定义一个整数变量

BEGIN

out_code := 0;

out_msg := 'success';

val := 0/0; -- 赋值，使用0做为除数，执行报异常

EXCEPTION

when others then

out_code := '-1';

out_msg := '统计发生异常' || substr(sqlerrm, 1, 100);

dbms_output.put_line(out_code || '::'||out_msg);

rollback ;

END PROC_STATISTICS_USER_EX;

（2）使用：使用命令窗口或者SQL窗口

a）先执行打开调试：set serverout on;

b) 再执行：

DECLARE

out_code number;

out_msg VARCHAR2(100);

BEGIN

PROC_STATISTICS_USER_EX('2018-10-23', out_code, out_msg);

dbms_output.put_line(out_code || '::'||out_msg);

END;

举例（动态执行）

create or replace procedure DEBT_P_SJTB_SJTBYTHYW(ad_code in varchar2,

call_type in varchar2,

p_error out varchar2) as

begin

DEBT_P_SJTB_SJTBYTHYW_DW(ad_code,call_type,p_error);

DEBT_P_SJTB_SJTBYTHYW_NOTDW(ad_code,call_type,p_error);

EXCEPTION

when too_many_rows then

rollback;

dbms_output.put_line('执行错误');

end DEBT_P_SJTB_SJTBYTHYW;

create or replace procedure DEBT_P_SJTB_SJTBYTHYW_DW(ad_code in varchar2,

call_type in varchar2,

p_error out varchar2) as

V_SQL VARCHAR2(4000);

ADD_SQL CLOB;

V_EXC_TABLE VARCHAR2(100);

V_STARTTIME DATE; -- 开始时间

V_STIME VARCHAR2(20); -- 开始时间转化为字符串

V_ENDTIME DATE; -- 结束时间

V_ETIME VARCHAR2(20); -- 结束时间转化为字符串

V_MI_COUNT VARCHAR2(20);

CURSOR CURENABLE IS

SELECT UPPER(EXC_TABLE) EXC_TABLE

FROM DEBT_T_EXC_CONFIG_PARAM

WHERE EXC_TYPE = CALL_TYPE

AND IS_ENABLE = '1'

ORDER BY SORTNUM;

BEGIN

EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML'; -- 能够使当前SESSION并行获取数据。

V_STARTTIME := SYSDATE;

V_STIME := TO_CHAR(V_STARTTIME, 'YYYY-MM-DD HH24:MI:SS');

OPEN CURENABLE; -- 开始循环

LOOP

FETCH CURENABLE

INTO V_EXC_TABLE;

EXIT WHEN CURENABLE%NOTFOUND;

-- 插入前先删除

-- 债券资金支出申请表

begin

if v_exc_table = UPPER('GD_SJTB_BOND_FUND_PAY_APPLY') then

v_sql := 'delete from GD_BOND_FUND_PAY_APPLY';

execute immediate v_sql;

end if;

EXCEPTION

WHEN OTHERS THEN

p_error := '删除 GD_BOND_FUND_PAY_APPLY 表数据报错';

return;

end;

begin

if v_exc_table = UPPER('GD_SJTB_BOND_FUND_PAY_APPLY') then

add_sql := 'insert into GD_BOND_FUND_PAY_APPLY nologging

execute immediate add_sql;

commit;

end if;

EXCEPTION

WHEN OTHERS THEN

p_error := '插入 GD_BOND_FUND_PAY_APPLY 报错';

return;

end;