存储过程DDL.docx
- 文档编号:13394499
- 上传时间:2023-06-13
- 格式:DOCX
- 页数:11
- 大小:17.96KB
存储过程DDL.docx
《存储过程DDL.docx》由会员分享,可在线阅读,更多相关《存储过程DDL.docx(11页珍藏版)》请在冰点文库上搜索。
存储过程DDL
转载:
Oracle存储过程包含三部分:
过程声明,执行过程部分,存储过程异常。
Oracle存储过程可以有无参数存储过程和带参数存储过程。
一、无参程序过程语法
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
四、在Oracle中对存储过程的调用
过程调用方式一
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
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_IDNUMBERISSELECTNAMEFROMUSERWHERETYPEID=C_ID;
OPENC_USER(变量值;
LOOP
FETCHC_USERINTOV_NAME;
EXITFETCHC_USER%NOTFOUND;
dosomething
ENDLOOP;
CLOSEC_USER;
8.用pl/sqldeveloperdebug
连接数据库后建立一个TestWINDOW
在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试
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_IDNUMBERISSELECTNAMEFROMUSERWHERETYPEID=C_ID;
OPENC_USER(变量值;
LOOP
FETCHC_USERINTOV_NAME;
EXITFETCHC_USER%NOTFOUND;
dosomething
ENDLOOP;
CLOSEC_USER;
8.用pl/sqldeveloperdebug
连接数据库后建立一个TestWINDOW
在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试
关于oracle存储过程的若干问题备忘
1.在oracle中,数据表别名不能加as,如:
selecta.appnamefromappinfoa;--正确
selecta.appnamefromappinfoasa;--错误
也许,是怕和oracle中的存储过程中的关键字as冲突的问题吧
2.在存储过程中,select某一字段时,后面必须紧跟into,如果select整个记录,利用游标的话就另当别论了。
selectaf.keynodeintoknfromAPPFOUNDATIONafwhereaf.appid=aidandaf.foundationid=fid;--有into,正确编译
selectaf.keynodefromAPPFOUNDATIONafwhereaf.appid=aidandaf.foundationid=fid;--没有into,编译报错,提示:
Compilation
Error:
PLS-00428:
anINTOclauseisexpectedinthisSELECTstatement
3.在利用select...into...语法时,必须先确保数据库中有该条记录,否则会报出"nodatafound"异常。
可以在该语法之前,先利用selectcount(*from查看数据库中是否存在该记录,如果存在,再利用select...into...
4.在存储过程中,别名不能和字段名称相同,否则虽然编译可以通过,但在运行阶段会报错
selectkeynodeintoknfromAPPFOUNDATIONwhereappid=aidandfoundationid=fid;--正确运行
selectaf.keynodeintoknfromAPPFOUNDATIONafwhereaf.appid=appidandaf.foundationid=foundationid;--运行阶段报错,提示
ORA-01422:
exactfetchreturnsmorethanrequestednumberofrows
5.在存储过程中,关于出现null的问题
假设有一个表A,定义如下:
createtableA(
idvarchar2(50primarykeynotnull,
vcountnumber(8notnull,
bidvarchar2(50notnull--外键
;
如果在存储过程中,使用如下语句:
selectsum(vcountintofcountfromAwherebid='xxxxxx';
如果A表中不存在bid="xxxxxx"的记录,则fcount=null(即使fcount定义时设置了默认值,如:
fcountnumber(8:
=0依然无效,fcount还是会变成null,这样以后使用fcount时就可能有问题,所以在这里最好先判断一下:
iffcountisnullthen
fcount:
=0;
endif;
这样就一切ok了。
6.Hibernate调用oracle存储过程
this.pnumberManager.getHibernateTemplate(.execute(
newHibernateCallback(...{
publicObjectdoInHibernate(Sessionsession
throwsHibernateException,SQLException...{
CallableStatementcs=session
.connection(
.prepareCall("{callmodifyapppnumber_remain(?
}";
cs.setString(1,foundationid;
cs.execute(;
returnnull;
}
};
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 存储 过程 DDL