oracle存储过程学习经典[语法 实例 调用]

《oracle存储过程学习经典[语法 实例 调用]》由会员分享，可在线阅读，更多相关《oracle存储过程学习经典[语法 实例 调用]（22页珍藏版）》请在金锄头文库上搜索。

1、Oracle存储过程学习目录Oracle存储过程1Oracle存储过程根底知识1Oracle存储过程的根本语法2关于Oracle存储过程的假设干问题备忘41. 在Oracle中，数据表别名不能加as。42. 在存储过程中，select某一字段时，后面必须紧跟into,如果select整个记录，利用游标的话就另当别论了。53. 在利用select.into.语法时，必须先确保数据库中有该条记录，否那么会报出no datafound”异常。54. 在存储过程中，别名不能和字段名称相同，否那么虽然编译可以通过，但在运行阶段会报错55. 在存储过程中，关于出现null的问题56. Hibernate

2、调用 Oracle 存储过程6用Java 调用 Oracle 存储过程总结6一、无返回值的存储过程6二、有返回值的存储过程非列表)8三、返回列表9在存储过程中做简单动态查询11一、本地动态SQL12二、使用 DBMSSQL 包13Oracle存储过程调用Java方法16Oracle高效分页存储过程实例17Oracle存储过程根底知识商业规那么和业务逻辑可以通过程序存储在Oracle中，这个程序就是存储过程。存储过程是SQL, PL/SQL, Java语句的组合，它使你能将执行商业规那么的代码从你的应用 程序中移动到数据库。这样的结果就是，代码存储一次但是能够被多个程序使用。要创立一个过程对象(

3、procedural object),必须有CREATE PROCEDURE系统权限。如果 这个过程对象需要被其他的用户schema使用，那么你必须有CREATE ANY PROCEDURE权 限。执行procedure的时候,可能需要excute权限。或者EXCUTE ANY PROCEDURE权限。 如果单独赋予权限，如下例所示：grant execute on MYPROCEDURE to Jelly调用一个存储过程的例子：execute MYPROCEDURE( ONE PARAMETER);存储过程(PROCEDURE)和函数(FUNCTION)的区别。function有返PI值，并

4、且可以直接在Query中引用function和或者使用function的返p 值。本质上没有区别，都是PL/SQL程序，都可以有返回值。最根本的区别是：存储过程是命 令，而函数是表达式的一局部。比方：select max(NAME) FROM但是不能exec max(NAME)如果此时max是函数。PACKAGE 是 function, procedure, variables 和 sql 语句的组合。package 允许多个 procedure使用同一个变量和游标。创立procedure的语法:CREATE OR REPLACE PROCEDURE schema.procedure(argu

5、ment IN | OUT | IN OUT NO COPY datatype,argument IN | OUT | IN OUT NO COPY datatype.)authid ( currentuser | definer is | as ( pl/sqlsubprogrambody Ilanguage ( java name * String * | c name, name library libname Sql代码：CREATE PROCEDURE sam.credit (accno IN NUMBER, amount IN NUMBER) ASBEGINUPDATE accou

6、ntsSET balance = balance + amountWHERE accountid = accno;END;可以使用create or replace procedure语句，这个语句的用处在于，你之前赋予的excute 权限都将被保存。IN, OUT, IN OUT用来修饰参数。IN表示这个变量必须被调用者赋值然后传入到PROCEDURE进行处理。OUT表示PRCEDURE通过这个变量将值传回给调用者。IN OUT那么是这两种的组合。authid代表两种权限：定义者权限(difiner right默认),执行者权限(invoker right) o定义者权限说明这个proced

7、ure中涉及的表，视图等对象所需要的权限只要定义者拥有 权限的话就可以访问。执行者权限那么需要调用这个procedure的用户拥有相关表和对象的权限。Oracle存储过程的根本语法1. 根本结构CREATE OR REPLACE PROCEDURE 存储过程名字参数 1 IN NUMBER,参数 2 IN NUMBER）AS变量 1 INTEGER :=0;变量2 DATE;BEGINEND存储过程名字2. SELECT INTO STATEMENT将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否那么抛出异常（如果没有记录抛出NODATAFOUND）例子

8、：BEGINSELECT coll,col2 into 变量 1,变量 2 FROM typestruct where xxx;EXCEPTIONWHEN NO DATA FOUND THENxxxx;END;3.IF判断IF VTEST=1THENBEGINdo somethingEND;END IF;4. while 循环WHILE VTEST=1 LOOPBEGINXXXXEND;END LOOP;5. 变量赋值VTEST := 123;6. 用 for in 使用 cursorISCURSOR cur IS SELECT * FROM xxx;BEGINFOR curresult in

9、 cur LOOPBEGINVSUM :=curresult.列名 l+curresult.列名 2END;END LOOP;END;7. 带参数的cursorCURSOR CUSER(CID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=CID;OPEN CUSER(变量值)；LOOPFETCH CUSER INTO VNAME;EXIT FETCH CUSER%NOTFOUND;do somethingEND LOOP;CLOSE CUSER;8. 用 pl/sql developer debug连接数据库后建立一个Test WINDOW在窗口

10、输入调用SP的代码,F9开始debug,CTRL+N单步调试9. Pl/Sql中执行存储过程在 sql*plus 中：declare-必要的变量声明，视你的过程而定beginexecute yourprocudure(parameterl,parameter2,.);end在SQL/PLUS中调用存储过程，显示结果:SQLset serveoutput onSQLvar infol number;SQLvar info2 number;SQLdeclarevar1var2var3-翻开输出输出1-输出2varchar2(20);varchar2(20);varchar2(20);-输入1输入2

11、输入2BEGINpro(var15var2,var3,:info1，:info2);END;SQLprint infol;SQLprint info2;注：在EXECUTE IMMEDIATE STR语句是SQLPLUS中动态执行语句，它在执行中会自动 提交，类似于DP中FORMSDDL语句，在此语句中str是不能换行的，只能通过连接字符“| | 或着在在换行时加上“-“连接字符。关于Oracle存储过程的假设干问题备忘1-在Oracle中，数据表别名不能加as如:select a.appname from appinfo a;- 正确select a.appname from appinfo

12、 as a;- 错误也许，是怕和Oracle中的存储过程中的关键字as冲突的问题吧2在存储过程中，select某一字段时，后面必须紧跟into,如果select整个记录，利用游标的话就另当别论了。select af.keynode into knfrom APPFOUNDATION afwhere af.appid=aid and af.foundationid=fid; -有 into, 正确编译select af.keynodefrom APPFOUNDATION afwhere af.appid=aid and af.foundationid=fid;-没有 into , 编 译报错，

13、提示： Compilation Error: PLS-00428: an INTO clause is expected in this SELECT statement3. 在利用select.into.语法时，必须先确保数据库中有该条 记录，否那么会报出no data found异常。可以在该语法之前，先利用select count(*)from查看数据库中是否存在该记录，如果存 在，再利用 select.into.4. 在存储过程中，别名不能和字段名称相同，否那么虽然编译可以通过，但在运行阶段会报错select keynode into kn from APPFOUNDATION whe

14、re appid=aid and foundationid=fid;-正确运行select af.keynode into kn from APPFOUNDATION af where af.appid=appid and af.foundationid =foundationid;运行阶段报错，提示：ORA-O1422:exact fetch returns more than requested number of rows5. 在存储过程中，关于出现null的问题假设有一个表A,定义如下：create table A(id varchar2(50) primary key not nul

15、l, vcount number(8) not null, bid varchar2(50) not null -外键);如果在存储过程中，使用如下语句：select sum(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 thenfcount:=0;end if;这样就一-切ok 了。6. Hibernate调用Or