【工作笔记】Oracle存储过程实战

本篇围绕几种模式：无参存储过程、带参存储过程、带参数存储过程含赋值方式、存储过程中游标定义使用、异常使用分别举例使用。

存储过程使用分为三步：编写存储过程->执行编译->调用使用

一、无参存储过程语法

（1）编写存储过程

create or replace procedure print_Time

BEGIN

DBMS_OUTPUT.PUT_LINE(SYSDATE);

END print_Time;

（2）运行编译

运行编译

（3）调用使用

A）SQL窗口执行：

DECLARE

BEGIN

print_Time();

END;

或

B)SQL命令窗口执行：exec print_Time;

exec print_Time

二、带参存储过程实例

（1）编写存储过程

create or replace procedure print_Time2(in_callDate in varchar2)

BEGIN

DBMS_OUTPUT.PUT_LINE(to_date(in_callDate, 'yyyy-MM-dd HH24:mi'));

--insert Log values(in_callDate);

END print_Time2;

（2）调用：

DECLARE

p_outval number;

p_inoutval VARCHAR2(10) := '~Hi~';

BEGIN

print_Time2('2018-10-22');

END;

三、带参数存储过程含赋值方式

（1）创建两张表：一张用户表（并插入数据），一张用于统计用户注册（脚本见附件）

创建两张表

（2）添加几条数据到用户表

添加几条数据到用户表

（3）现在写一个存储过程，完成每天凌晨统计前一天不同城市用户注册数量

首先按照常规写出查询语句：

SELECT u.city as cityNme, count(*) as userCount

FROM z_test_user u

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

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

GROUP BY u.city;

按照语法创建存储过程：

create or replace procedure PROC_STATISTICS_USER(inDate in varchar2)

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;

说明：a）黑色字体为创建存储过程规范格式（如不明天请看第一部分理论学习）。

b) 红色字体分为三步，第一定义变量包括类型，第二赋值，使用“:=”，第三插入使用变量

c) BEGIN之后是查询，并进行循环，格式：for 变量名 in() loop ... end loop;

d）查询语句按照时间条件并按城市名称进行分组查询城市名称，总数，然后赋值给变量cur_row，下文使用cur_row.city、cur_row.tatal进行取值。

e) insert into... 进行插入。

(4) 编译

编译

（5）调用执行

DECLARE

BEGIN

PROC_STATISTICS_USER('2018-10-25');

END;

结果如下：

结果

注：实际存储调用一般放在定时任务调用。

四、存储过程中游标定义使用

注：依然使用上面表数据作为演示，完成每天凌晨统计前一天不同城市用户注册数量

（1）创建存储过程

create or replace procedure PROC_STATISTICS_USER_CUR(inDate in varchar2)

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为子游标，就如数据和数组下标关系。

（2）编译执行及执行结果如下：

结果

五、异常使用

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

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)

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;

结果如下：

结果

SQL窗口：

SQL窗口

留下一个问题：循环里面错误处理（oracle只支持begin...end中放错误捕获）

附件：

1，用户表创建及添加数据：

drop table Z_TEST_USER cascade constraints;

create table Z_TEST_USER

(

ID NUMBER not null,

USERNAME VARCHAR2(20),

PASSWORD VARCHAR2(100),

CITY VARCHAR2(50),

CREATE_TIME DATE

)

tablespace USERS

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

next 8K

minextents 1

maxextents unlimited

);

comment on column Z_TEST_USER.ID

is '用户ID';

comment on column Z_TEST_USER.USERNAME

is '用户姓名（电话）';

comment on column Z_TEST_USER.PASSWORD

is '密码';

comment on column Z_TEST_USER.CITY

is '城市';

comment on column Z_TEST_USER.CREATE_TIME

is '创建日期';