Oracle存储过程基础与案例

通过对Oracle存储过程的学习与研究，并记录了下来，后面会有例子。
首先还是先看看基础语法吧：

                        Oracle存储过程 一：oracle 存储过程的基本语法1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字(    参数1 IN NUMBER,    参数2 IN NUMBER) IS变量1 INTEGER :=0;变量2 DATE;BEGIN--xxxEND 存储过程名字【注意：】如果没有or replace语句，则仅仅是新建一个存储过程。如果系统存在该存储过程，则会报错。Create or replace procedure 如果系统中没有此存储过程就新建一个，如果系统中有此存储过程则把原来删除掉，重新创建一个存储过程。 参数的数据类型只需要指明类型名即可，不需要指定宽度。 参数的宽度由外部调用者决定。过程可以有参数，也可以没有参数 变量声明块：紧跟着的as (is )关键字，可以理解为pl/sql的declare关键字，用于声明变量。 变量声明块用于声明该存储过程需要用到的变量，它的作用域为该存储过程。另外这里声明的变量必须指定宽度。遵循PL/SQL的变量声明规范。 过程语句块：从begin 关键字开始为过程的语句块。存储过程的具体逻辑在这里来实现。 异常处理块：关键字为exception ，为处理语句产生的异常。该部分为可选 结束块：由end关键字结果。2.SELECT INTO STATEMENT  将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条  记录，否则抛出异常(如果没有记录抛出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 IS 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;  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整个记录，利用游标的话就另当别论了。  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...into...语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。   可以在该语法之前，先利用select count(*) from 查看数据库中是否存在该记录，如果存在，再利用select...into...4.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错 select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid;-- 正确运行select af.keynode into kn from APPFOUNDATION af 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(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 Object doInHibernate(Session session)                            throws HibernateException, SQLException {                        CallableStatement cs = session                                .connection()                                .prepareCall("{call modifyapppnumber_remain(?)}");                        cs.setString(1, foundationid);                        cs.execute();                        return null;                    }                });例子：首先建表：create table tab_1(       id varchar(11) primary key,       name varchar(10),       password varchar(10))添加数据:insert into TAB_1 (ID, NAME, PASSWORD)values ('1', '小米', '123');insert into TAB_1 (ID, NAME, PASSWORD)values ('2', '小明', '452');insert into TAB_1 (ID, NAME, PASSWORD)values ('3', '小华', '145');无返回值：

                            **过程测试一**

create or replace procedure test isbeginUpdate tab_1 t set t.name='小明' where t.name='小华'end test【经过测试发现：create or replace procedure test is这里可以使用’is’ 或者’as’】

报错：PROCEDURE ZYSMS.TEST 编译错误错误：PL/SQL: ORA-00933: SQL 命令未正确结束行：3文本：Update tab_1 t set t.name='小李' where t.name='小明'错误：PL/SQL: SQL Statement ignored行：3文本：Update tab_1 t set t.name='小李' where t.name='小明'错误：PLS-00103: 出现符号 "end-of-file"在需要下列之一时：        ;行：4文本：end test解决过程：是由于更新SQL语句后没有加分号”;” 接着执行：create or replace procedure test isbeginUpdate tab_1 t set t.name='小明' where t.name='小华';end test报错：PROCEDURE ZYSMS.TEST 编译错误错误：PLS-00103: 出现符号 "end-of-file"在需要下列之一时：        ;       符号 ";" 被替换为 "end-of-file" 后继续。行：4文本：end test

解决过程：由于在end test(当前你的存储过程名) 后没有加结束的分号”;”。

运行：编译成功！

Test存储过程代码：create or replace procedure test isbegin       update tab_1 t set t.name='小明' where t.name='小华';end test;测试代码：    public static void main(String[] args) throws SQLException {        Statement stmt = null;        ResultSet rs = null;        Connection conn = null;        try {          Class.forName(driver);          conn = DriverManager.getConnection(url,name, password);          CallableStatement proc = null;          proc = conn.prepareCall("{ call test() }"); //设置存储过程          proc.execute();//执行        }        catch (SQLException ex2) {          ex2.printStackTrace();        }        catch (Exception ex2) {          ex2.printStackTrace();        }        finally{          try {            if(rs != null){              rs.close();              if(stmt!=null){                stmt.close();              }              if(conn!=null){                conn.close();              }            }          }          catch (SQLException ex1) {          }        }     }  

运行结果：

                                 **过程测试二**

带返回值：

【1】返回指定参数：
Test2存储过程代码：
create or replace procedure test2(var_id in number, var_out_name out varchar2) as
begin
select t.name into var_out_name from tab_1 t where t.id=var_id;
end test2;

测试代码：
public static void main(String[] args) throws SQLException {
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, name, password);
CallableStatement proc = null;
proc = conn.prepareCall(“{ call test2(?,?) }”); //设置存储过程
proc.setString(1, “2”);//设置第一个参数输入参数
proc.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);//第二个参数输出参数,是VARCHAR类型的
proc.execute();//执行
String var_out_name = proc.getString(2);//获得输出参数
System.out.println(“名字 : “+var_out_name);
}
catch (SQLException ex2) {
ex2.printStackTrace();
}
catch (Exception ex2) {
ex2.printStackTrace();
}
finally{
try {
if(rs != null){
rs.close();
if(stmt!=null){
stmt.close();
}
if(conn!=null){
conn.close();
}
}
}
catch (SQLException ex1) {
}
}
}
【注意事项：】
1.对于存储过程的输入参数，使用setXXX；对于输出参数，使用registerOutParameter
,问号的顺序要对应，同时需要考虑类型。
2.取出存储过程返回值的方法是CallableStatement提供的getXX()注意输出参数的位置，
同时也需要考虑输出参数的类型。

运行结果：名字 : 小华                                **过程测试三**

【2】返回列表
由于Oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用package了，所以要分为两部分：
步骤一：新建一个程序包
create or replace package TESTPACKAGE as
type Test_CURSOR is ref cursor;
end TESTPACKAGE;

步骤二：到这可以创建存储过程：
create or replace procedure test3(p_cursor out TESTPACKAGE.Test_CURSOR) as
begin
open p_cursor for select * from tab_1;
end test3;

测试代码：
public static void main(String[] args) throws SQLException {
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, name, password);
CallableStatement proc = null;
proc = conn.prepareCall(“{ call test3(?) }”); //设置存储过程
proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
proc.execute();
rs = (ResultSet)proc.getObject(1);
while(rs.next())
{
System.out.println(” ID： ” + rs.getString(1) + ” 名字： “+rs.getString(2)+” 密码： “+rs.getString(3));
}
}
catch (SQLException ex2) {
ex2.printStackTrace();
}
catch (Exception ex2) {
ex2.printStackTrace();
}
finally{
try {
if(rs != null){
rs.close();
if(stmt!=null){
stmt.close();
}
if(conn!=null){
conn.close();
}
}
}
catch (SQLException ex1) {
}
}
}
运行结果：
ID： 1 名字： 小米 密码： 123
ID： 2 名字： 小华 密码： 452
ID： 3 名字： 小组 密码： 145

                              **过程测试四**

存储过程加入IF语句：

存储过程Test5:
先创建包：【在下篇文章详细介绍Oracle-Package】
create or replace package TESTPACKAGE as
type Test_CURSOR is ref cursor;
end TESTPACKAGE;

再建存储过程：
create or replace procedure test5(var_id in varchar2,var_outs out TESTPACKAGE.Test_CURSOR) as
id varchar2(10):=’01’;
begin
if id=var_id then
begin
open var_outs for select * from tab_1;
end;
end if;
end test5;

测试代码：
public static void main(String[] args) throws SQLException {
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url,name,password);
CallableStatement proc = null;
proc = conn.prepareCall(“{ call test5(?,?) }”); //设置存储过程
proc.setString(1,”01”);
proc.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
proc.execute();//执行
rs=(ResultSet) proc.getObject(2);
while(rs.next()){
System.out.println(” ID： ” + rs.getString(1) + ” 名字： “+rs.getString(2)+” 密码： “+rs.getString(3));
}
}
catch (SQLException ex2) {
ex2.printStackTrace();
}
catch (Exception ex2) {
ex2.printStackTrace();
}
finally{
try {
if(rs != null){
rs.close();
if(stmt!=null){
stmt.close();
}
if(conn!=null){
conn.close();
}
}
}
catch (SQLException ex1) {
}
}
}
这里id值设置为“01”【正确值】时运行结果：

这里id值设置为“02”【错误值】时运行结果：

运行报：Ref 游标无效

                              **过程测试五**

调存储过程时如果传List集合测试：test6存储过程：【1】CREATE OR REPLACE TYPE tables_array AS VARRAY(100) OF VARCHAR2(32);【2】create or replace procedure test6(arr_t in tables_array) asbegin    for i in arr_t.first .. arr_t.last loop   -- insert  into test_table values(arr_t(i));    update tab_1 t set t.password='001' where t.id=arr_t(i);  end loop;  commit;end test6;【3】测试代码：public static void main(String[] args) throws SQLException {        Statement stmt = null;        ResultSet rs = null;        Connection conn = null;        try {          Class.forName(driver);          conn = DriverManager.getConnection(url,name,password);          CallableStatement proc = null;          proc = conn.prepareCall("{ call test6(?) }"); //设置存储过程          ArrayDescriptor descriptor =  ArrayDescriptor.createDescriptor("TABLES_ARRAY",conn);          List list=new ArrayList<>();          list.add("1");          list.add("3");          ARRAY array = new ARRAY(descriptor,conn,list.toArray());            proc.setArray(1, array);          proc.execute();//执行        }        catch (SQLException ex2) {          ex2.printStackTrace();        }        catch (Exception ex2) {          ex2.printStackTrace();        }        finally{          try {            if(rs != null){              rs.close();              if(stmt!=null){                stmt.close();              }              if(conn!=null){                conn.close();              }            }          }          catch (SQLException ex1) {          }        }     }  

运行结果：

我的技术群这里给大家分享下：472148690

在下一篇我会分享一个大批量数据插入的性能测试。