oracle 存储过程学习

一、无参存储过程语法
（1）编写存储过程
create or replace procedure print_Time
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(SYSDATE);
END print_Time;

（2）运行编译

（3）调用使用

A）SQL窗口执行：
DECLARE
BEGIN
print_Time();
END;

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

二、带参存储过程实例
（1）编写存储过程
create or replace procedure print_Time2(in_callDate in varchar2)
IS
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)
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;

说明：

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

（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)
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;
/
结果如下：

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 '创建日期';

insert into Z_TEST_USER (ID, USERNAME, PASSWORD, CITY, CREATE_TIME)
values (5, 'eee', 'eee', '北京', to_date('25-10-2018 00:02:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into Z_TEST_USER (ID, USERNAME, PASSWORD, CITY, CREATE_TIME)
values (1, 'aaa', 'aaa', '上海', to_date('25-10-2018 02:08:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into Z_TEST_USER (ID, USERNAME, PASSWORD, CITY, CREATE_TIME)
values (2, 'bbb', 'bbb', '上海', to_date('25-10-2018 05:00:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into Z_TEST_USER (ID, USERNAME, PASSWORD, CITY, CREATE_TIME)
values (3, 'ccc', 'ccc', '合肥', to_date('25-10-2018 04:02:01', 'dd-mm-yyyy hh24:mi:ss'));
insert into Z_TEST_USER (ID, USERNAME, PASSWORD, CITY, CREATE_TIME)
values (4, 'ddd', 'ddd', '合肥', to_date('25-10-2018 09:00:01', 'dd-mm-yyyy hh24:mi:ss'));

commit;

2，日志表创建
drop table Z_TEST_USER_LOG cascade constraints;
create table Z_TEST_USER_LOG
(
ID NUMBER not null,
CITY VARCHAR2(20),
COUNT NUMBER,
COUNT_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_LOG.ID
is '用户ID';
comment on column Z_TEST_USER_LOG.CITY
is '城市';
comment on column Z_TEST_USER_LOG.COUNT
is '注册个数';
comment on column Z_TEST_USER_LOG.COUNT_TIME
is '统计时间';

commit;