oracle 存储过程调用

oracle存储过程
2011年02月11日 星期五 14:47

SQL中调用存储过程语句：
call procedure_name();
调用时”()”是不可少的，无论是有参数还是无参数。
定义对数据库过程的调用时    无参数过程:{ call procedure_name}    仅有输入参数的过程：{call procedure_name(?,?...)}     这里?表示输入参数，创建存储过程时用in表示输入参数
   仅有输出参数的过程:{ Call procedure_name(?,?...)}     这里的？表示输出参数，创建存储过程时用out表示输入参数
   既有输入参数又有输出参数的过程            {call procedure_name(?,?...)}     这里的？有表示输出参数的，也有表示输入参数的
下面将会对这4种情况分别举出实例！！！

参数过程实例
无参数存储过程：
create or replace procedure stu_proc as
pname varchar2(25);
begin
  select sname into pname from student where sno=1;
  dbms_output.put_line(pname);
end;
或者
create or replace procedure stu_proc as
pname student.sname%type;
begin
  select sname into p_name from student where sno=1;
  dbms_output.put_line(pname);
end;




参数过程实例
仅有输入参数的过程
create or replace procedure stu_proc1(pno in student.sno%type) as
pname varchar2(25);
begin
  select sname into pname from student where sno=pno;
  dbms_output.put_line(pname);
  end;

参数过程实例
仅有输出参数的存储过程
create or replace procedure stu_proc2(pname out student.sname%type) as
begin
  select sname into pname from student where sno=1;
  dbms_output.put_line(pname);
  end;
此种存储过程不能直接用call来调用，这种情况的调用将在下面oracle函数调用中说明

参数过程实例
有输入\输出参数的存储过程：
create or replace procedure stu_proc3
(pno in student.sno%type,pname out student.sname%type) as
begin
  select sname into pname from student where sno=pno;
  dbms_output.put_line(pname);
  end;
此种存储过程不能直接用call来调用，这种情况的调用将在下面oracle函数调用中说明

Oracle函数调用存储过程
我们已经学习了oracle函数，下面就针对参数的4种情况分别举出实例说明函数对存储过程的调用
函数调用存储过程实例
对无参数过程的调用：
--函数
create or replace function get_pname return varchar2 is
  pname varchar2(20);
  begin
   stu_proc;
   select sname into pname from student where sno=1;
    return pname;
    end;
--调用
declare
begin
   dbms_output.put_line('在PL/SQL中打印的结果：'||get_pname);
end;

函数调用存储过程实例
对有输入参数过程的调用：
create or replace function get_pname1(pno in number) return varchar2 is
  pname varchar2(20);
  begin
  stu_proc1(pno in student.sno%type)
  select sname into pname from student where  sno=pno;
    return pname;
    end;
--调用
declare
begin
   dbms_output.put_line('在PL/SQL中打印的结果：'||get_pname1(2));
end;

函数调用存储过程实例
对有输出参数过程的调用:
create or replace function get_pname2(pname out varchar2) return varchar2 is
  begin
  stu_proc2(pname out student.sname%type);
    return pname;
    end;
--调用
declare
pname student.sname%type;
begin
   dbms_output.put_line('在PL/SQL中打印的结果：'||get_pname2(pname));
end;

函数调用存储过程实例
对有输入\输出参数过程的调用:
create or replace function get_pname3(pno in number,pname out varchar2) return varchar2 is
  begin
stu_proc3(pno in student.sno%type,pname out student.sname%type);
    return pname;
    end;
--调用
declare
pname student.sname%type;
begin
   dbms_output.put_line('在PL/SQL中打印的结果：'||get_pname3(2,pname));
end;

JAVA调用数据库存储过程
前面我们已经讲述了有关oracle数据库的存储过程的几种形式，以及oracle函数对存储过程的调用，下面我将根据上面存储过程的实例来举出JAVA对oracle存储过程的调用
JAVA调用实例
仅有返回值的过程：
public static void main(String[] args) {
Connection conn=BBConnection.getConnection();
String sql="{call stu_proc2(?)}";
try {
CallableStatement statement=conn.prepareCall(sql);
statement.registerOutParameter(1,Types.VARCHAR);
statement.execute();
String pname=statement.getString(1);
System.out.println(pname);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

}
JAVA调用实例
既有输入参数又有输出参数的过程
public static void main(String[] args) {
        Connection conn=BBConnection.getConnection();
        String sql="{call stu_proc3(?,?)}";
        try {
                CallableStatement statement=conn.prepareCall(sql);
                statement.setInt(1, 1);
                statement.registerOutParameter(2,Types.VARCHAR);
                statement.execute();
                String pname=statement.getString(2);

                System.out.println(pname);
         } catch (SQLException e) {
               e.printStackTrace();
         }
}
JAVA调用实例
下面将举出无out参数的调用实例
这种参数不适于用在查询语句上，因为查询语句需要有返回值才能被JAVA调用
返回到 OUT 参数中的值可能会是JDBC NULL。当出现这种情形时，将对 JDBC NULL 值进行转换以使 getXXX 方法所返回的值为 null、0 或 false，这取决于getXXX 方法类型。对于 ResultSet 对象，要知道0或false是否源于JDBCNULL的唯一方法，是用方法wasNull进行检测。如果 getXXX 方法读取的最后一个值是 JDBC NULL，则该方法返回 true，否则返回 flase

JAVA调用实例
仅有参数的过程：
public static void main(String[] args) {
Connection conn=BBConnection.getConnection();
String sql="{call stu_proc1(?)}";
try {
CallableStatement statement=conn.prepareCall(sql);
statement.setInt(1, 1);
statement.execute();
System.out.println(statement.execute());
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

}
JAVA调用实例
无参数过程：
public static void main(String[] args) {
Connection conn=BBConnection.getConnection();
String sql="{call stu_proc()}";
try {
CallableStatement statement=conn.prepareCall(sql);
statement.execute();
System.out.println(statement.execute());
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}          
}
}
通过调用数据库函数调用存储过程
下面将举一个通过数据库函数来调用存储过程：
public static void main(String[] args) {
Connection conn=BBConnection.getConnection();
String sql="{?=call get_pname1(?)}";
try {
CallableStatement statement=conn.prepareCall(sql);
statement.registerOutParameter(1, Types.VARCHAR);
statement.setInt(2, 1);
statement.execute();
System.out.println(statement.getString(1));


} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
？表示函数return的值， get_pname1是数据库函数名
存储过程的out和in都是以参数传进，这就是函数和存储过程的区别之一
存储过程的异常处理
为了提高存储过程的健壮性，避免运行错误，当建立存储过程时应包含异常处理部分。
异常(EXCEPTION)是一种PL/SQL标识符，包括预定义异常、非预定义异常和自定义异常；
预定义异常是指由PL/SQL提供的系统异常；非预定义异常用于处理与预定义异常无关的Oracle错误(如完整性约束等)；自定义异常用于处理与Oracle错误的其他异常情况。
RAISE_APPLICATION_ERROR用于自定义错误消息，并且消息号必须在－20000～－20999之间
存储过程的异常处理实例
Oracle数据库中提供了一些异常处理的方法，下面通过一个实例来说明
create or replace procedure stu_proc6(pno in student.sno%type,pname out student.sname%type)
       is
       begin
          select sname into pname from student where sno=pno;
       EXCEPTION
                 when NO_DATA_FOUND then
                RAISE_APPLICATION_ERROR
(-20011,'ERROR：不存在！');
end;
Oracle提供的异常处理
     命名的系统异常          产生原因
ACCESS_INTO_NULL         未定义对象
CASE_NOT_FOUND         CASE 中若未包含相应的 WHEN ，并且没有设置
COLLECTION_IS_NULL         集合元素未初始化
CURSER_ALREADY_OPEN          游标已经打开
DUP_VAL_ON_INDEX          唯一索引对应的列上有重复的值
INVALID_CURSOR         在不合法的游标上进行操作
INVALID_NUMBER        内嵌的 SQL 语句不能将字符转换为数字
NO_DATA_FOUND          使用 select into 未返回行，或应用索引表未初始化的
TOO_MANY_ROWS         执行 select into 时，结果集超过一行
ZERO_DIVIDE             除数为 0
SUBSCRIPT_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系                    统包
ROWTYPE_MISMATCH         主游标变量与 PL/SQL 游标变量的返回类型不兼容
SELF_IS_NULL             使用对象类型时，在 null 对象上调用对象方法
STORAGE_ERROR         运行 PL/SQL 时，超出内存空间
SYS_INVALID_ID              无效的 ROWID 字符串
TIMEOUT_ON_RESOURCE         Oracle 在等待资源时超时
存储过程的异常处理实例
自定义异常处理：
create or replace procedure stu_proc7(pno in student.sno%type,
       pon in student.sno%type
       )
       is
           v_raise exception;
           v_name student.sname%type;
       begin
         if pno=101 then
           raise v_raise;
           end if;
           select sname into v_name from student where sno=111111;
       exception
           when v_raise then
             RAISE_APPLICATION_ERROR(-20010,'ERROR:not existed!');
           when no_data_found then
             RAISE_APPLICATION_ERROR(-20011,'ERROR：不存在！');
end;
存储过程的事务处理
事务用于确保数据的一致性，由一组相关的DML语句组成，该组DML语句所执行的操作要么全部确认，要么全部取消。
当执行事务操作(DML)时，Oracle会在被作用的表上加锁，以防止其他用户改变表结构，同时也会在被作用的行上加行锁，以防止其他事务在相应行上执行DML操作。
当执行事务提交或事务回滚时，Oracle会确认事务变化或回滚事务、结束事务、删除保存点、释放锁。
存储过程的事务处理
提交事务（COMMIT）确认事务变化，结束当前事务、删除保存点，释放锁，使得当前事务中所有未决的数据永久改变。
保存点（SAVEPOINT）在当前事务中，标记事务的保存点。
回滚事务（ROLLBACK）回滚整个事务，删除该事务所定义的所有保存点，释放锁，丢弃所有未决的数据改变。
回滚事务到指定的保存点（ROLLBACK TO SAVEPOINT）回滚当前事务到指定的保存点，丢弃该保存点创建后的任何改变，释放锁。
存储过程的事务处理
当执行DDL、DCL语句，或退出SQL*PLUS时，会自动提交事务；
事务期间应避免与使用者互动；
查询数据期间，尽量不要启动事务；
尽可能让事务持续地越短越好；
在事务中尽可能存取最少的数据量。

事务处理实例
存储过程事务处理实例：
create or replace procedure stu_proc8
       is
       begin
         insert into student values(102,'sky','m',22,'gong');
         savepoint savepoint1;
         insert into student values(102,'good','w',20,'wang');
         dbms_output.put_line('error');
         update student set sno=103 where sname='good';
         commit;
         exception
           when dup_val_on_index then
             rollback to savepoint savepoint1;
             RAISE_APPLICATION_ERROR(-20010,'ERROR：违反唯一索引约束！');
        end;
数据库函数和存储过程的包
创建包（package）
函数：
create or replace package 包名 as 函数
注意：as后可加多个函数
存储过程：
Create or replace package 包名 as 存储过程
注意：as后可加多个存储过程
包的调用
函数的包调用：
call 包名.函数名；
存储过程的包调用：
call 包名.存储过程名；
THE  END

评论

1 楼 liumm19870913 2012-05-09  

对有输入参数过程的调用
我试过那代码 ， 有问题呀，function 执行不下去。