oracle存储过程的详细列子说明加分析

1、文章分类:数据库存储过程创建语法：（1）无参create or replace procedure 存储过程名as变量1 类型（值范围）;变量2 类型（值范围）;Begin .Exception .End;（2）带参create or replace procedure 存储过程名（param1 in type，param2 out type）as变量1 类型（值范围）;变量2 类型（值范围）;Begin Select count(*) into 变量1 from 表A where列名=param1； If (判断条件) then Select 列名 into 变量2 from 表A wher

2、e列名=param1； Dbms_output.Put_line(打印信息); Elseif (判断条件) then Dbms_output.Put_line(打印信息); Else Raise 异常名（NO_DATA_FOUND）; End if;Exception When others then Rollback;End;注意事项：1， 存储过程参数不带取值范围，in表示传入，out表示输出2， 变量带取值范围，后面接分号3， 在判断语句前最好先用count（*）函数判断是否存在该条操作记录4， 用select 。into。给变量赋值5， 在代码中抛异常用 raise+异常名以命名的异常

3、命名的系统异常 产生原因ACCESS_INTO_NULL 未定义对象CASE_NOT_FOUND CASE 中若未包含相应的 WHEN ，并且没有设置ELSE 时COLLECTION_IS_NULL 集合元素未初始化CURSER_ALREADY_OPEN 游标已经打开DUP_VAL_ON_INDEX 唯一索引对应的列上有重复的值INVALID_CURSOR 在不合法的游标上进行操作INVALID_NUMBER 内嵌的 SQL 语句不能将字符转换为数字NO_DATA_FOUND 使用 select into 未返回行，或应用索引表未初始化的 TOO_MANY_ROWS 执行 select int

4、o 时，结果集超过一行ZERO_DIVIDE 除数为 0SUBSCRIPT_BEYOND_COUNT 元素下标超过嵌套表或 VARRAY 的最大值SUBSCRIPT_OUTSIDE_LIMIT 使用嵌套表或 VARRAY 时，将下标指定为负数VALUE_ERROR 赋值时，变量长度不足以容纳实际数据LOGIN_DENIED PL/SQL 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码NOT_LOGGED_ON PL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据PROGRAM_ERROR PL/SQL 内部问题，可能需要重装数据字典 pl./SQL系统包R

5、OWTYPE_MISMATCH 宿主游标变量与 PL/SQL 游标变量的返回类型不兼容SELF_IS_NULL 使用对象类型时，在 null 对象上调用对象方法STORAGE_ERROR 运行 PL/SQL 时，超出内存空间SYS_INVALID_ID 无效的 ROWID 字符串TIMEOUT_ON_RESOURCE Oracle 在等待资源时超时 例子：1 create or replace procedure runbyparmeters (isal in emp.sal%type, sname out varchar,sjob in out varchar)2 as icount num

6、ber;3 begin4 select count(*) into icount from emp where sal>isal and job=sjob;5 if icount=1 then6 .9 else10 .12 end if;13 exception14 when too_many_rows then15 DBMS_OUTPUT.PUT_LINE('返回值多于1行');16 when others then17 DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');18 end;过程调用 方式一1 decl

7、are2 realsal emp.sal%type;3 realname varchar(40);4 realjob varchar(40);5 begin6 realsal:=1100;7 realname:=''8 realjob:='CLERK'9 runbyparmeters(realsal,realname,realjob); 必须按顺序10 DBMS_OUTPUT.PUT_LINE(REALNAME|' '|REALJOB);11 END;12 方式二1 declare2 realsal emp.sal%type;3 realname

8、 varchar(40);4 realjob varchar(40);5 begin6 realsal:=1100;7 realname:=''8 realjob:='CLERK'9 runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob); 指定值对应变量顺序可变10 DBMS_OUTPUT.PUT_LINE(REALNAME|' '|REALJOB);11 END;说明：（1）使用%TYPE在许多情况下，PL/SQL变量可以用来存储在数据库表中的数据。在这种情况下

9、，变量应该拥有与表列相同的类型。例如，students表的first_name列的类型为VARCHAR2(20),我们可以按照下述方式声明一个变量DECLAREv_FirstName VARCHAR2(20);但是如果first_name列的定义改变了会发生什么（比如说表改变了，first_name现在的类型变为VARCHAR2(25)）？那就会导致所 有使用这个列的PL/SQL代码都必须进行修改。如果你有很多的PL/SQL代码，这种处理可能是十分耗时和容易出错的。这时，你可以使用”%TYPE”属性而不是将变量类型硬性编码。（2）使用%ROWTYPE (相当于定义一个struct 来进行存放，

10、以对象来看对数据）在PL/SQL中将一个记录声明为具有相同类型的数据库行的作法是很常见的。PL/SQL提供了%ROWTYPE运算符，使得这样的操作更为方便。例如：DECLAREv_StudentRecord students%ROWTYPE;将定义一个记录，该记录中的字段将与students表中的列相对应。例如：declare v_jobs %rowtype;begin select * into v_jobs from where job_id ='&aa' dbms_output.put_line('序号'|v_jobs.

11、job_id ); dbms_output.put_line('名称'|v_jobs.job_title);end;执行，我们输入aa变量的值：AD_VP输出结果为：序号AD_VP名称Administration Vice President以下是代码片段： create or replace package PSH_GPRSSTREAMSTAT is- Author : ADMINISTRATOR- Created : 2004-12-8 10:56:01- Purpose : GPRS流量统计状态- 统计GPRS流量typeC_Cur is ref cursor;funct

12、ion Calcu_GPRSSTREAM return number;end PSH_GPRSSTREAMSTAT;-create or replace package body PSH_GPRSSTREAMSTAT isfunction Calcu_GPRSSTREAM return numberisc_IPPackHeadLen constant number := 40; - 定义IP包头长度CURSOR c_SPINFO isselect distinct spid from sh_spinfo where isactive = '0'c_MDTINFO C_Cur ;

13、v_MDTINFO number;v_UpTransContentLens number(20,0); - 存放当前GPRS终端上传转发的信息内容长度v_UpContentLens number(20,0);v_UpTotalLens number(20,0); - 累计GPRS终端上传的信息内容长度v_DownContentLens number(20,0);v_DownTotalLens number(20,0);newID number(20,0);begin- 初始化以下是代码片段： select max(statid) into newID from sh_gprsstreamsta

14、t;if (newID is null) thennewID := 1;end if;for v_SPINFO In c_SPINFO loop - 首先获取SPID- 其次遍历出与当前SPID对应的所有MDT以下是代码片段： open c_MDTINFo for select distinct mdtid from sh_mdtinfo where (isactive = '0') and (spid = v_SPINFO.spid);loopfetch c_MDTINFO into v_MDTINFO;exit when c_MDTINFO%notfound;v_UpCon

15、tentLens := 0;v_UpTransContentLens := 0;v_UpTotalLens := 0;v_DownContentLens := 0;v_DownTotalLens := 0;1、用来插入大量测试数据的存储过程CREATE OR REPLACE PROCEDURE INSERTAMOUNTTEST(ST_NUM IN NUMBER,ED_NUM IN NUMBER)ISBEGINdeclare i number;beginFOR i IN ST_NUM.ED_NUM LOOPINSERT INTO tb values(i,i,'3','3&

16、#39;,'3',100,'0');END LOOP;end;END;运行：sql>execute INSERTAMOUNTTEST(1,45000) - 一次插入45000条测试数据2、从存储过程中返回值create or replace procedure spaddflowdate(varAppTypeId in varchar2,varFlowId in varchar2,DateLength in number,ReturnValue out number -返回值)isbegininsert into td values(varAppTypeI

17、d,varFlowId,DateLength)returning 1 into ReturnValue; -返回值commit;exceptionwhen others thenrollback;end;存储过程的执行sql>variable testvalue number;sql>execute spaddflowdate('v','v',2,:testvalue);sql>print就可以看到执行结果 3、用包实现存储过程返回游标：create or replace package test_p as type outList is re

18、f cursor; PROCEDURE getinfor(taxpayerList out outList); end test_p; / create or replace package body test_p as PROCEDURE getinfor(taxpayerList out outList) is begin OPEN taxpayerList FOR select * from td where tag='0' end getinfor; end test_p; / 运行： set serverout on; -将输出工具打开 variable x refc

19、ursor; execute test_p.getinfor(:x);exec test_p.getinfor(:x); print x; drop package test_p;oracle 存储过程的基本语法1.基本结构CREATE OR REPLACE PROCEDURE 存储过程名字( 参数1 IN NUMBER, 参数2 IN NUMBER) IS变量1 INTEGER :=0;变量2 DATE;BEGINEND 存储过程名字2.SELECT INTO STATEMENT 将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条 记录，否则抛出异常(如果没有记

20、录抛出NO_DATA_FOUND) 例子： BEGIN SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx; EXCEPTION WHEN NO_DATA_FOUND THEN xxxx; END; .3.IF 判断 IF V_TEST=1 THEN BEGIN do something END; END IF;4.while 循环 WHILE V_TEST=1 LOOP BEGIN XXXX END; END LOOP;5.变量赋值 V_TEST := 123;6.用for in 使用cursor . IS CURSOR cur I

21、S SELECT * FROM xxx; BEGIN FOR cur_result in cur LOOP BEGIN V_SUM :=cur_result.列名1+cur_result.列名2 END; END LOOP; END;7.带参数的cursor CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID; OPEN C_USER(变量值); LOOP FETCH C_USER INTO V_NAME; EXIT FETCH C_USER%NOTFOUND; do something END LOOP;

22、 CLOSE C_USER;8.用pl/sql developer debug 连接数据库后建立一个Test WINDOW 在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试关于oracle存储过程的若干问题备忘1.在oracle中，数据表别名不能加as，如：select a.appname from appinfo a;- 正确select a.appname from appinfo as a;- 错误 也许，是怕和oracle中的存储过程中的关键字as冲突的问题吧2.在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别

23、论了。 select af.keynode into kn from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;- 有into，正确编译 select af.keynode from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;- 没有into，编译报错，提示：Compilation Error: PLS-00428: an INTO clause is expected in this SELECT statement3.在利用select

24、.into.语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。 可以在该语法之前，先利用select count(*) from 查看数据库中是否存在该记录，如果存在，再利用o.4.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错 select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid;- 正确运行select af.keynode into kn from APPFOUNDATION af

25、 where af.appid=appid and af.foundationid=foundationid;- 运行阶段报错，提示ORA-01422:exact fetch returns more than requested number of rows5.在存储过程中，关于出现null的问题假设有一个表A，定义如下：create table A(id varchar2(50) primary key not null,vcount number(8) not null,bid varchar2(50) not null - 外键 );如果在存储过程中，使用如下语句：select sum

26、(vcount) into fcount from A where bid='xxxxxx'如果A表中不存在bid="xxxxxx"的记录，则fcount=null(即使fcount定义时设置了默认值，如：fcount number(8):=0依然无效，fcount还是会变成null)，这样以后使用fcount时就可能有问题，所以在这里最好先判断一下：if fcount is null then fcount:=0;end if;这样就一切ok了。6.Hibernate调用oracle存储过程 this.pnumberManager.getHibernateTemplate().execute( new HibernateCallback() . public