oracle存储过程

1.带有输入参数的存储过程
Create or replace procedure system.student_count_query(table_name in nvarchar,c out number)
 is
sqlstr nvarchar(50);
 Begin
 sqlstr:=’select count(*) from ’||table_name;
 Execute sqlstr into c;
 End;
 
2.在PLSQL中调用存储过程
set serverout on;
declare
 studentnum number;
 begin
 student_count_query(‘system.user’,studentnum);
 dbms_output.put_line(studentnum);
 end;

 
3.实例
定义存储过程:
create or replace procedure system.student_count_pro(table_name in varchar2,c out number,maxage out number,minage out number)
is
sqlstr varchar2(50);
begin
sqlstr:='select count(*)   from '||table_name;
--dbms_output.put_line(sqlstr);
execute immediate sqlstr into c;
execute immediate 'select max(sage) from system.student' into maxage;
select min(sage) into minage from system.student;
end  student_count_pro;

在PLSQL中调用存储过程:
declare
 c number;
 age number;
 minage number;
begin
 system.student_count_pro('system.student',c,age,minage);
 dbms_output.put_line(c);
 dbms_output.put_line(age);
 dbms_output.put_line(minage);
end;

Java调用存储过程实例:
 private String driver="oracle.jdbc.driver.OracleDriver";
 private String url="jdbc:oracle:thin:@localhost:1521:bbs";
 private String uname="system";
 private String pass="admin";
 private Connection con;
 private CallableStatement call;
 private boolean flag;
 
 public void init()
 {
  try {
   Class.forName(driver);
   con=DriverManager.getConnection(url,uname,pass);
   //调用存储过程
   call=con.prepareCall("{call STUDENT_COUNT_PRO(?,?,?,?)}");
   //给存储过程中的参数赋值
   call.setString(1,"system.student");
   
   call.registerOutParameter(2,Types.VARCHAR);
   call.registerOutParameter(3,Types.INTEGER);
   call.registerOutParameter(4,Types.INTEGER);
   
   flag=call.execute();
   
   System.out.println(flag);
   //输出返回值：使用call.getInt(num)方式获取
   //数值2、3、4并非任意的，而是和存储过程中的out列对应的
   System.out.println(call.getInt(2));
   System.out.println(call.getInt(3));
   System.out.println(call.getInt(4));
   
  } catch (ClassNotFoundException e) {
   // TODO 自动生成 catch 块
   e.printStackTrace();
  } catch (SQLException e) {
   // TODO 自动生成 catch 块
   e.printStackTrace();
  }  
 }
 
 

4.存储过程返回列表

由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，
列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了.所以要分两部分。
 
1>在SQL*PLUS中建一个程序包
CREATE OR REPLACE PACKAGE TESTPACKAGE  AS 
 TYPE Test_CURSOR IS REF CURSOR;
 procedure TESTC(cur_ref out Test_CURSOR);
end TESTPACKAGE;

2>建立存储过程，存储过程为：
create or replace package body TESTPACKAGE as
 procedure TESTC(cur_ref out Test_CURSOR) is
  begin  
   N cur_ref FOR SELECT * FROM T_TEST;
  end TESTC;
 END TESTPACKAGE;

可以看到，它是把游标（可以理解为一个指针），作为一个out 参数来返回值的。

JAVA程序如下：

package com.my.test;
import java.sql.*;
import java.io.OutputStream;
import java.io.Writer;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import oracle.jdbc.driver.*;

public class TestProcedureOne {
    public TestProcedureOne() {
      }
      public static void main(String[] args ){
        String driver = "oracle.jdbc.driver.OracleDriver";
        String strUrl = "jdbc:oracle:thin:@192.168.10.216:1521:ctbu";
        Statement stmt = null;
        ResultSet rs = null;
        Connection conn = null;
        CallableStatement cstmt = null;
  
        try {
          Class.forName(driver);
          conn = DriverManager.getConnection(strUrl,"databasename" "password");
          CallableStatement proc = null;
          proc = conn.prepareCall("{ call cqsb.TESTA(?,?) }");
          proc.setString(1, "100");
          proc.setString(2, "TestOne");
          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) {
          }
        }
      }
    }

 

$$$$$计算指定部门的工资总和，并统计其中的职工数量。

 

CREATE OR REPLACE PROCEDURE proc_demo(

Dept_no NUMBER DEFAULT 10,

Sal_sum OUT NUMBER,

Emp_count OUT NUMBER)

IS

BEGIN

SELECT SUM(sal), COUNT(*) INTO sal_sum, emp_count 

FROM emp WHERE deptno=dept_no;

EXCEPTION

   WHEN NO_DATA_FOUND THEN 

      DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');

   WHEN OTHERS THEN 

      DBMS_OUTPUT.PUT_LINE('发生其它错误!');

END proc_demo;

 

调用方法:

 DECLARE

V_num NUMBER;

V_sum NUMBER(8, 2);

BEGIN

Proc_demo(30, v_sum, v_num);

DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||’，人数：’||v_num);

Proc_demo(sal_sum => v_sum, emp_count => v_num);

DBMS_OUTPUT.PUT_LINE('10号部门工资总和：'||v_sum||’，人数：’||v_num);

END;

<!--EndFragment-->