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-->