1、 to Jelly调用一个存储过程的例子：execute MY_PROCEDURE（ ONE PARAMETER）;存储过程（PROCEDURE）和函数（FUNCTION）的区别。 function有返回值，并且可以直接在Query中引用function和或者使用function的返回值。本质上没有区别,都是 PL/SQL 程序,都可以有返回值。最根本的区别是： 存储过程是命令,而函数是表达式的一部分。比如：select max（NAME） FROM但是不能 exec max（NAME） 如果此时max是函数。PACKAGE是function，procedure，variables 和sql
2、语句的组合。package允许多个procedure使用同一个变量和游标。创建 procedure的语法：CREATE OR REPLACE PROCEDURE schema.procedure （argument IN | OUT | IN OUT NO COPY datatype , argument IN | OUT | IN OUT NO COPY datatype. ） authid current_user | definer is | as pl/sql_subprogram_body | language java name String | c name, name libr
3、ary lib_nameSql 代码：CREATE PROCEDURE sam.credit （acc_no IN NUMBER, amount IN NUMBER） AS BEGIN UPDATE accounts SET balance = balance + amount WHERE account_id = acc_no; END;可以使用 create or replace procedure 语句， 这个语句的用处在于，你之前赋予的excute权限都将被保留。IN, OUT, IN OUT用来修饰参数。IN 表示这个变量必须被调用者赋值然后传入到PROCEDURE进行处理。OUT
4、表示PRCEDURE 通过这个变量将值传回给调用者。IN OUT 则是这两种的组合。authid代表两种权限：定义者权限（difiner right 默认），执行者权限（invoker right）。定义者权限说明这个procedure中涉及的表，视图等对象所需要的权限只要定义者拥有权限的话就可以访问。执行者权限则需要调用这个 procedure的用户拥有相关表和对象的权限。Oracle存储过程的基本语法1. 基本结构CREATE OR REPLACE PROCEDURE 存储过程名字（ 参数1 IN NUMBER, 参数2 IN NUMBER） AS变量1 INTEGER :=0;变量2 D
5、ATE;BEGINEND 存储过程名字2. SELECT INTO STATEMENT将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条 记录，否则抛出异常（如果没有记录抛出NO_DATA_FOUND）例子： SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx; EXCEPTION WHEN NO_DATA_FOUND THEN xxxx; .3. IF 判断IF V_TEST=1 THEN do something END IF;4. while 循环WHILE V_TEST=1 LOOP BEGINX
6、XXX END LOOP;5. 变量赋值V_TEST := 123;6. 用for in 使用cursor. IS CURSOR cur IS SELECT * FROM xxx;FOR cur_result in cur LOOPV_SUM :=cur_result.列名1+cur_result.列名2END;END LOOP;7. 带参数的cursorCURSOR C_USER（C_ID NUMBER） IS SELECT NAME FROM USER WHERE TYPEID=C_ID; OPEN C_USER（变量值）; LOOPFETCH C_USER INTO V_NAME;EXI
7、T FETCH C_USER%NOTFOUND; CLOSE C_USER;8. 用pl/sql developer debug 连接数据库后建立一个Test WINDOW 在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试9. Pl/Sql中执行存储过程在sql*plus中： declare -必要的变量声明，视你的过程而定 begin execute yourprocudure（parameter1,parameter2,.）; end / 在SQL/PLUS中调用存储过程，显示结果： SQLset serveoutput on-打开输出var info1 number;
8、-输出1 var info2 number; -输出2 declare var1 varchar2（20）; -输入1 var2 varchar2（20）; -输入2 var3 varchar2（20）; pro（var1,var2,var3,:info1,:info2）; / print info1;print info2;注：在EXECUTE IMMEDIATE STR语句是SQLPLUS中动态执行语句，它在执行中会自动提交，类似于DP中FORMS_DDL语句，在此语句中str是不能换行的,只能通过连接字符|，或着在在换行时加上-连接字符。关于Oracle存储过程的若干问题备忘1. 在Or
9、acle中，数据表别名不能加as。如：selecta.appnamefromappinfoa;- 正确as- 错误也许，是怕和Oracle中的存储过程中的关键字as冲突的问题吧2. 在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了。af.keynodeintoknAPPFOUNDATIONafwhereaf.appid=aidandaf.foundationid=fid;-有into，正确编译af没有into，编译报错，提示：CompilationError:PLS-00428:anINTOclauseisexpectedinthi
10、sSELECTstatement3. 在利用select.into.语法时，必须先确保数据库中有该条记录，否则会报出可以在该语法之前，先利用select count（*） from 查看数据库中是否存在该记录，如果存在，再利用select.into.4. 在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错keynodeappid=aidfoundationid=fid;正确运行af.appid=appidaf.foundationid=foundationid;运行阶段报错，提示：ORA-01422:exactfetchreturnsmorethanrequeste
11、dnumberofrows5. 在存储过程中，关于出现null的问题假设有一个表A，定义如下：createtableA（idvarchar2（50）primarykeynotnull,vcountnumber（8）bidnull外键如果在存储过程中，使用如下语句：sum（vcount）fcountAbid=xxxxxx;如果A表中不存在bid=xxxxxx的记录，则fcount=null（即使fcount定义时设置了默认值，如：fcount number（8）:=0依然无效，fcount还是会变成null），这样以后使用fcount时就可能有问题，所以在这里最好先判断一下：ifthenfcou
12、nt:endif;这样就一切ok了。6. Hibernate调用Oracle存储过程this.pnumberManager.getHibernateTemplate（）.execute（new HibernateCallback（） .public Object doInHibernate（Session session）throws HibernateException, SQLException . CallableStatement cs = session .connection（） .prepareCall（call modifyapppnumber_remain（?） cs.set
13、String（1, foundationid）; cs.execute（）;returnnull; ）;用Java调用Oracle存储过程总结一、 无返回值的存储过程测试表:- Create tablecreate table TESTTB ID VARCHAR2（30）, NAME VARCHAR2（30）tablespace BOM pctfree 10 initrans 1 maxtrans 255 storage （ initial 64K minextents 1 maxextents unlimited ）;例: 存储过程为（当然了，这就先要求要建张表TESTTB,里面两个字段（I
14、_ID，I_NAME）。）：CREATE OR REPLACE PROCEDURE TESTA（PARA1 IN VARCHAR2, PARA2 IN VARCHAR2） AS INSERT INTO BOM.TESTTB（ID, NAME） VALUES （PARA1, PARA2）;END TESTA;在Java里调用时就用下面的代码：package com.yiming.procedure.test;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;impo
15、rt java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class TestProcedureDemo1 public TestProcedureDemo1（） public static void main（String args） String driver = Oracle.jdbc.driver.OracleDriver String strUrl = jdbc:Oracle:thin:10.20.30.30:1521:vasms Statement stmt = null;
16、 ResultSet rs = null; Connection conn = null; CallableStatement proc = null; try Class.forName（driver）; conn = DriverManager.getConnection（strUrl, bom, proc = conn.prepareCall（ call BOM.TESTA（?,?） proc.setString（1, 100 proc.setString（2, TestOne proc.execute（）; catch （SQLException ex2） ex2.printStack
17、Trace（）; catch （Exception ex2） finally try if （rs != null） rs.close（）; if （stmt ! stmt.close（）; if （conn ! conn.close（）; catch （SQLException ex1） 二、 有返回值的存储过程（非列表）例：存储过程为：CREATE OR REPLACE PROCEDURE TESTB（PARA1 IN VARCHAR2, PARA2 OUT VARCHAR2） AS SELECT NAME INTO PARA2 FROM TESTTB WHERE ID = PARA1;E
18、ND TESTB;import java.sql.Types;public class TestProcedureDemo2 call BOM.TESTB（? proc.registerOutParameter（2, Types.VARCHAR）; String testPrint = proc.getString（2）; System.out.println（=testPrint=is= + testPrint）;注意，这里的proc.getString（2）中的数值2并非任意的，而是和存储过程中的out列对应的，如果out是在第一个位置，那就是proc.getString（1），如果是第三
19、个位置，就是proc.getString（3），当然也可以同时有多个返回值，那就是再多加几个out参数了。三、 返回列表由于Oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了.所以要分两部分，1. 建一个程序包。如下：CREATE OR REPLACE PACKAGE TESTPACKAGE AS TYPE TEST_CURSOR IS REF CURSOR;end TESTPACKAGE;2. 建立存储过程，存储过程为：CREATE OR REPLACE PROCEDURE TESTC（P_CURSOR out TESTPACKAGE.TEST_CURSOR） IS OPEN P_CURSOR FOR SELECT * FROM BOM.TESTTB;END TESTC;可以看到，它是把游标（可以理解为一个指针），作为一个out 参数来返回值的。在这里要注意，在执行前一定要先把Oracle的驱动包放到class路径里，否则会报错的。public class TestProcedureDemo3 call bom.testc（? proc.registe
