本篇围绕几种模式:无参存储过程、带参存储过程、带参数存储过程含赋值方式、存储过程中游标定义使用、异常使用分别举例使用。
存储过程使用分为三步:编写存储过程->执行编译->调用使用
一、无参存储过程语法
(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;
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窗口:
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 '创建日期';
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;
参考资料:
http://blog.csdn.net/u013057786/article/details/17165623
https://www.cnblogs.com/liangyihui/p/5886760.html
https://bbs.csdn.net/topics/391912626?page=1
来源:讯飞技术 https://mp.weixin.qq.com/s/aymp9_40eqKR8IiV9U2ABQ
网友评论