【工作筆記】Oracle存儲過程實戰(zhàn)

本篇圍繞幾種模式：無參存儲過程纺念、帶參存儲過程渠驼、帶參數(shù)存儲過程含賦值方式羡鸥、存儲過程中游標(biāo)定義使用蔑穴、異常使用分別舉例使用。

存儲過程使用分為三步：編寫存儲過程->執(zhí)行編譯->調(diào)用使用

一惧浴、無參存儲過程語法

（1）編寫存儲過程

create or replace procedure print_Time

BEGIN

? DBMS_OUTPUT.PUT_LINE(SYSDATE);

END print_Time;

（2）運行編譯

運行編譯

（3）調(diào)用使用

A）SQL窗口執(zhí)行：

DECLARE

BEGIN

? print_Time();

END;

或

B)SQL命令窗口執(zhí)行：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）調(diào)用：

DECLARE

? p_outval number;

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

BEGIN

? print_Time2('2018-10-22');

END;

三、帶參數(shù)存儲過程含賦值方式

（1）創(chuàng)建兩張表：一張用戶表（并插入數(shù)據(jù)）衷旅，一張用于統(tǒng)計用戶注冊（腳本見附件）

創(chuàng)建兩張表

（2）添加幾條數(shù)據(jù)到用戶表

添加幾條數(shù)據(jù)到用戶表

（3）現(xiàn)在寫一個存儲過程捐腿，完成每天凌晨統(tǒng)計前一天不同城市用戶注冊數(shù)量

首先按照常規(guī)寫出查詢語句：

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;

按照語法創(chuàng)建存儲過程：

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）黑色字體為創(chuàng)建存儲過程規(guī)范格式（如不明天請看第一部分理論學(xué)習(xí)）。

b) 紅色字體分為三步柿顶，第一定義變量包括類型茄袖，第二賦值，使用“:=”九串，第三插入使用變量

? ? c) BEGIN之后是查詢绞佩，并進行循環(huán)寺鸥，格式：for 變量名 in() loop ... end loop;

? d）查詢語句按照時間條件并按城市名稱進行分組查詢城市名稱，總數(shù)品山，然后賦值給變量cur_row胆建，下文使用cur_row.city、cur_row.tatal進行取值肘交。

? e) insert into... 進行插入笆载。

(4) 編譯

編譯

（5）調(diào)用執(zhí)行

DECLARE

BEGIN

? PROC_STATISTICS_USER('2018-10-25');

END;

結(jié)果如下：

結(jié)果

注：實際存儲調(diào)用一般放在定時任務(wù)調(diào)用。

四涯呻、存儲過程中游標(biāo)定義使用

注：依然使用上面表數(shù)據(jù)作為演示凉驻，完成每天凌晨統(tǒng)計前一天不同城市用戶注冊數(shù)量

（1）創(chuàng)建存儲過程

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; --賦值

? -- 有些時候，有其他參數(shù)需要從其他表中查詢下面給出一個舉例

? -- 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) 游標(biāo)使用格式：在is后Cursor 變量名 IS ... ;

? ? c) begin中使用：FOR c_row IN cur_row LOOP... END loop;賦值，插入等操作效诅，此時for中in是直接使用游標(biāo)進行胀滚。可以理解cur_row為父親游標(biāo)乱投，c_row為子游標(biāo)咽笼，就如數(shù)據(jù)和數(shù)組下標(biāo)關(guān)系。

（2）編譯執(zhí)行及執(zhí)行結(jié)果如下：

結(jié)果

五戚炫、異常使用

有時候執(zhí)行存儲過程會報錯剑刑，調(diào)用者需要知道執(zhí)行結(jié)果是否報錯，此時需要定義傳出參數(shù)双肤，并在異常塊進行賦值施掏。捕獲到異常之后:

1.記錄錯誤相關(guān)信息? 放入相關(guān)日志表? SQLCODE? SQLERRM

2.如果有事務(wù)相關(guān)的操作一般是要rollback

（1）創(chuàng)建存儲過程

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

? val int;? --定義一個整數(shù)變量

BEGIN

? out_code := 0;

? out_msg? := 'success';

? val := 0/0;? -- 賦值，使用0做為除數(shù)茅糜，執(zhí)行報異常

EXCEPTION

? when others then

? ? out_code := '-1';

? ? out_msg? := '統(tǒng)計發(fā)生異常' || substr(sqlerrm, 1, 100);

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

? ? rollback ;

END PROC_STATISTICS_USER_EX;

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

a）先執(zhí)行打開調(diào)試：set serverout on;

b) 再執(zhí)行：

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;

結(jié)果如下：

結(jié)果

SQL窗口：

SQL窗口

留下一個問題：循環(huán)里面錯誤處理（oracle只支持begin...end中放錯誤捕獲）

附件：

1其监，用戶表創(chuàng)建及添加數(shù)據(jù)：

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 '創(chuàng)建日期';