发表人:cedar_1982 | 发表时间: 2004年十二月27日, 12:34
//=============================================
// 定义包、及存储过程
// 请注意游标变量的声明和使用的方法
// 这里的游标是输出(out)参数
//=============================================
create or replace package audit is
-- Public type declarations, cursor variable
type outList is ref cursor;
PROCEDURE sp_audit_GetTaxpayerList(organize in varchar2, taxpayerList out outList);
end audit;
create or replace package body audit is
-- taxpayerList is cursor variable
PROCEDURE sp_audit_GetTaxpayerList(organize in varchar2, taxpayerList out outList)
is
begin
OPEN taxpayerList
FOR
select
strName , strTelephone
from "TB_PERSONINFO" a
WHERE a.organize = organize ;
end;
end audit;
//=============================================
// 下面是java 源码,从oracle存储过程读取记录集
// 注意一定要用oracle的jdbc API ( 可以从oracle网站下载 )
//=============================================
try
{
Connection conn = null;
ResultSet rs = null;
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:ora","net7b.com","net7b");
oracle.jdbc.OracleCallableStatement stmt = (oracle.jdbc.OracleCallableStatement)conn.prepareCall("{call
audit.sp_audit_GetTaxpayerList(?, ?)}");
stmt.setString( 1, "23201020100");
stmt.registerOutParameter( 2, oracle.jdbc.OracleTypes.CURSOR );
stmt.execute();
rs = stmt.getCursor( 2 );
for (int i = 0; rs.next();)
{
System.out.println(" ----["+ i +"]name:" + rs.getString(1) + "telephone:" + rs.getString(2));
}
}
catch (Exception ex)
{
ex.printStackTrace();
}
这个是用游标变量作为输入(in)参数的例子一部分。我不必废话,相信大家可以自己试着编写这样
的一个例子了。
PROCEDURE open_cv (generic_cv IN OUT GenericCurTyp,choice NUMBER) IS
BEGIN
IF choice = 1 THEN
OPEN generic_cv FOR SELECT * FROM emp;
ELSIF choice = 2 THEN
OPEN generic_cv FOR SELECT * FROM dept;
ELSIF choice = 3 THEN
OPEN generic_cv FOR SELECT * FROM salgrade;
END IF;
...
END;
创建一个简单的JAVA存储过程
发表人:cedar_1982 | 发表时间: 2004年十二月25日, 14:32
在网上找了一位大虾的文章,照着把作了下来,也把它写下来,省的忘了^_^
环境:
1、操作系统:Windows 2000 Server
2、数 据 库:Oracle 9i
3、安装路径:D:ORACLE
实现方法:
1、在D:底下创建一个简单的Hello.java文件
public class Hello{
public static void main (String args[])
{
System.out.println("Hello!just a test!");
}
}
2、javac编译:
javac Hello.java
3、查看结果:
-java Hello
Hello!just a test!
4、连接数据库,给scott赋create and directory 权限
conn / as sysdba;
grant create any directory to scott;
5、用scott登录
conn scott/tiger@oradb;
create or replace directory test_dir as 'D:';
create or replace java class using bfile(test_dir,'Hello.class');
6、查看是否存在
-select object_name,object_type from user_objects;
7、创建
create or replace procedure java_test as language java name 'Hello.main(java.lang.String[])';
8、设置sql/plus环境
set serveroutput on size 5000
call dbms_java.set_output(5000);
9、执行:
execute java_test;
显示:
Hello!just a test!
一个简单的java存储过程创建