Oracle存储过程.docx
- 文档编号:10756144
- 上传时间:2023-05-27
- 格式:DOCX
- 页数:10
- 大小:18.42KB
Oracle存储过程.docx
《Oracle存储过程.docx》由会员分享,可在线阅读,更多相关《Oracle存储过程.docx(10页珍藏版)》请在冰点文库上搜索。
Oracle存储过程
(一)关于编写存储过程需要注意以下几点:
1>赋值符号:
=
逻辑等=
2>注释单行代码用--
注释代码块用/*---*/
3>字符串连接符||
4>变量在变量声明区内进行声明:
IS和BEGIN之间
5>变量声明方式通常有如下三种:
1)单个变量
mo_flagNUMBER:
=0;
contentTABLE.CONTENT%TYPE;
2)数组
TYPEdata_typeISTABLEOFVARCHAR2(10)INDEXBYBINARY_INTEGER;--引用形式为data_type(i)
TYPErule_typeISRECORD(data_idTABLE.ID%TYPE,
data_nameTABLE.NAME%TYPE
);--引用形式为rule_type
(i).data_id
6>经常用到的一个内置函数DECODE:
DECODE(id,1,'Southlake',
2,'SanFrancisco',
3,'NewJersey',
4,'Seattle',
'Non-domestic')
当id是1时,返回Southlake;是2时,返回SanFrancisco;依次,如果都不是时,返回Non-domestic
(二)PL/SQL块结构
1>触发器
CREATEORREPLACETRIGGERtrigger_name
AFTER|BEFOREINSERT|UPDATE|DELETEONtable_name
FOREACHROW
DECLARE
(声明部分)
BEGIN
(程序体)
EXCEPTION
(异常处理部分)
END;
2>存储过程
CREATEORREPLACEPROCEDUREname(参数)IS
(声明部分)
BEGIN
(程序体)
EXCEPTION
(异常处理部分)
END;
3>函数
CREATEORREPLACEFUNCTIONfunc_name(参数)RETURNdata_typeIS
(声明部分)
BEGIN
(程序体)
EXCEPTION
(异常处理部分)
END;
注:
DECLARE在触发器中进行变量声明的时候需要,存储过程和函数的变量声明时不需要
(三)应用和调试的时候需要注意以下几种应用:
1>SETSERVEROUTPUTONBUFFER20000;
用dbms_output.put_line('strin_content');可以在存储过程中输出信息,对存储过程进行调试;
只有将serveroutput变量设为on后,信息才能显示在屏幕上
2>EXECUTEprocedure_name;
执行一个存储过程
3>selectfunction()fromdual;
通过sql语句执行一个函数
4>DBMS_JOB.SUBMIT(job,'TEST_WARN_PROC;',sysdate,'sysdate+30/(24*60)');
表示这个Job的执行频率是每隔30分钟,DBMS_JOB.SUBMIT(任务号,要执行的存储过程,下次执行的时间,每次间隔的时间)
sysdate+x/24每隔x小时执行一次
sysdate+x/(24*60)每隔x分钟执行一次
sysdate+x/(24*60*60)每隔x秒执行一次
5>当报出错误信息的时候,其中指出的行号说明如下:
行号的计数从关键字DECLARE开始,如果没有则BEGIN语句的行号为1
1、用来插入大量测试数据的存储过程
CREATEORREPLACEPROCEDUREINSERTAMOUNTTEST
(
ST_NUMINNUMBER,
ED_NUMINNUMBER
)
IS
BEGIN
declare
inumber;
begin
FORiINST_NUM..ED_NUMLOOP
INSERTINTOtbvalues(i,i,'3','3','3',100,'0');
ENDLOOP;
end;
END;
运行:
sql>executeINSERTAMOUNTTEST(1,45000)--一次插入45000条测试数据
2、从存储过程中返回值
createorreplaceprocedurespaddflowdate
(
varAppTypeIdinvarchar2,
varFlowIdinvarchar2,
DateLengthinnumber,
ReturnValueoutnumber--返回值
)
is
begin
insertintotdvalues(varAppTypeId,varFlowId,DateLength)
returning1intoReturnValue;--返回值
commit;
exception
whenothersthen
rollback;
end;
存储过程的执行
sql>variabletestvaluenumber;
sql>executespaddflowdate('v','v',2,:
testvalue);
sql>print
就可以看到执行结果
3、用包实现存储过程返回游标:
createorreplacepackagetest_p
as
typeoutListisrefcursor;
PROCEDUREgetinfor(taxpayerListoutoutList);
endtest_p;
/
createorreplacepackagebodytest_pasPROCEDUREgetinfor(taxpayerListoutoutList)isbegin
OPENtaxpayerListFORselect*from
tdwheretag='0';
endgetinfor;
endtest_p;
/
运行:
setserverouton;--将输出工具打开
variablexrefcursor;
executetest_p.getinfor(:
x);
exectest_p.getinfor(:
x);
printx;
droppackagetest_p;
存储过程包含三部分:
声明,执行部分,异常。
可以有无参数程序和带参数存储过程。
无参程序语法
1 create or replace procedure NoParPro
2 as
;
3 begin
4
;
5 exception
6
;
7 end;
8
带参存储过程实例
1 create or replace procedure queryempname(sfindno emp.empno%type) as
2 sName emp.ename%type;
3 sjob emp.job%type;
4 begin
5 ....
7 exception
....
14 end;
15
带参数存储过程含赋值方式
1 create or replace procedure runbyparmeters (isal in emp.sal%type,
sname out varchar,sjob in out varchar)
2 as icount number;
3 begin
4 select count(*) into icount from emp where sal>isal and job=sjob;
5 if icount=1 then
6 ....
9 else
10 ....
12 end if;
13 exception
14 when too_many_rows then
15 DBMS_OUTPUT.PUT_LINE('返回值多于1行');
16 when others then
17 DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错!
');
18 end;
19
过程调用
方式一
1 declare
2 realsal emp.sal%type;
3 realname varchar(40);
4 realjob varchar(40);
5 begin
6 realsal:
=1100;
7 realname:
='';
8 realjob:
='CLERK';
9 runbyparmeters(realsal,realname,realjob); --必须按顺序
10 DBMS_OUTPUT.PUT_LINE(REALNAME||' '||REALJOB);
11 END;
12
方式二
1 declare
2 realsal emp.sal%type;
3 realname varchar(40);
4 realjob varchar(40);
5 begin
6 realsal:
=1100;
7 realname:
='';
8 realjob:
='CLERK';
9 runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob); --指定值对应变量顺序可变
10 DBMS_OUTPUT.PUT_LINE(REALNAME||' '||REALJOB);
11 END;
12
oracle存储过程的基本语法
1.基本结构
CREATEORREPLACEPROCEDURE存储过程名字
(
参数1INNUMBER,
参数2INNUMBER
)IS
变量1INTEGER:
=0;
变量2DATE;
BEGIN
END存储过程名字
2.SELECTINTOSTATEMENT
将select查询的结果存入到变量中,可以同时将多个列存储多个变量中,必须有一条
记录,否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
例子:
BEGIN
SELECTcol1,col2into变量1,变量2FROMtypestructwherexxx;
EXCEPTION
WHENNO_DATA_FOUNDTHEN
xxxx;
END;
...
3.IF判断
IFV_TEST=1THEN
BEGIN
dosomething
END;
ENDIF;
4.while循环
WHILEV_TEST=1LOOP
BEGIN
XXXX
END;
ENDLOOP;
5.变量赋值
V_TEST:
=123;
6.用forin使用cursor
...
IS
CURSORcurISSELECT*FROMxxx;
BEGIN
FORcur_resultincurLOOP
BEGIN
V_SUM:
=cur_result.列名1+cur_result.列名2
END;
ENDLOOP;
END;
7.带参数的cursor
CURSORC_USER(C_IDNUMBER)ISSELECTNAMEFROMUSERWHERETYPEID=C_ID;
OPENC_USER(变量值);
LOOP
FETCHC_USERINTOV_NAME;
EXITFETCHC_USER%NOTFOUND;
dosomething
ENDLOOP;
CLOSEC_USER;
8.用pl/sqldeveloperdebug
连接数据库后建立一个TestWINDOW
在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 存储 过程