1、建立测试表 CREATE TABLE student ( id NUMBER, name VARCHAR2(30), sex VARCHAR2(10), address VARCHAR2(100), postcode VARCHAR2(10), birthday DATE, photo LONG RAW ) / 2、建立带ref cursor定义的包和包体及函数: CREATE OR REPLACE package pkg_test as /* 定义ref cursor类型 不加return类型,为弱类型,允许动态sql查询, 否则为强类型,无法使用动态sql查询; */ type myrctype is ref cursor; --函数申明 function get(intID number) return myrctype; end pkg_test; / CREATE OR REPLACE package body pkg_test as --函数体 function get(intID number) return myrctype is rc myrctype; --定义ref cursor变量 sqlstr varchar2(500); begin if intID=0 then --静态测试,直接用select语句直接返回结果 open rc for select id,name,sex,address,postcode,birthday from student; else --动态sql赋值,用:w_id来申明该变量从外部获得 sqlstr := 'select id,name,sex,address,postcode,birthday from student where id=:w_id'; --动态测试,用sqlstr字符串返回结果,用using关键词传递参数 open rc for sqlstr using intid; end if; return rc; end get; end pkg_test; / 3、用pl/sql块进行测试: declare w_rc pkg_test.myrctype; --定义ref cursor型变量 --定义临时变量,用于显示结果 w_id student.id%type; w_name student.name%type; w_sex student.sex%type; w_address student.address%type; w_postcode student.postcode%type; w_birthday student.birthday%type; begin --调用函数,获得记录集 w_rc := pkg_test.get(1); --fetch结果并显示 fetch w_rc into w_id,w_name,w_sex,w_address,w_postcode,w_birthday; dbms_output.put_line(w_name); end; 4、测试结果: 通过。 在上述的online document中有相当多的内容,包括怎样在pro*c传递动态参数给存储过程、ref cusor的使用限制、open cursor for的4中不同方法等等。
以下是存储过程: create or replace procedure p_dtsgfztj_getsql_top(as_date1 in varchar2,as_date2 in varchar2,as_type in integer, as_resql out long,as_count out integer) is /* --自返回参数:as_resql varchar --按照所提供的参数信息获取构造的SQL语句 --参数:as_date1 开始时间 -- as_date2 结束时间 -- as_type 统计口径 1:公司 2.区域 3.区块 4.井别 */ type v_cursor is ref cursor; mycursor v_cursor; --声明游标变量(获得所有的区域编码) v_code long; --区域编码集合 v_temp long; --临时 v_temp1 long; --临时 v_temp2 long; --临时 v_temp3 long; --临时 v_temp4 long; --临时 v_sql long; --合计的最终sql v_val integer; --某一月份的天数 v_code1 varchar2(30); v_name varchar2(100); v_num integer; v_code2 varchar2(30); v_code3 varchar2(30); begin v_temp1 :=' '; v_temp3 :=' '; v_num :=0; if as_type = 1 then v_code :='select ''公司代码'' as gongsidm,''公司名称'' as gongsimc,''损失金额'' as sunshije,''损失进尺'' as sunshijc,''事故总次数'' as shiguzcs '; open mycursor for select distinct t.code,f_db_get_jcbmname(t.code) from (select SUBSTR(sglbbm,1,6) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,9) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,12) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,15) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,18) as code from p_sgfzcl ) t; loop fetch mycursor into v_code1,v_name ; exit when mycursor%NOTFOUND; v_num := v_num + 1; v_code2 :='sgcs'||trim(to_char(v_num,'0000')); v_code3 :='sgsj'||trim(to_char(v_num,'0000')); if v_code1 is null then v_code1 :=' ' ; v_name :='未命名'; end if; v_temp1 :=v_temp1||', '''||v_name||''' as '||v_code2; v_temp3 :=v_temp3||', '''||v_name||''' as '||v_code3; end loop; v_temp2 :=',''事故总时间'' as shiguzsj'; v_temp := v_temp1||v_temp2||v_temp3; v_code := v_code||v_temp;
v_sql := v_code||' from useunitcode a where a.sfscbz = 0 and rownum=1'; close mycursor; as_resql :=v_sql; as_count := 6 + 2*v_num; end if; if as_type = 2 then v_code :='select ''区域代码'' as quyudm,''区域名称'' as quyumc,''损失金额'' as sunshije,''损失进尺'' as sunshijc,''事故总次数'' as shiguzcs '; open mycursor for select distinct t.code,f_db_get_jcbmname(t.code) from (select SUBSTR(sglbbm,1,6) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,9) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,12) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,15) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,18) as code from p_sgfzcl ) t; loop fetch mycursor into v_code1,v_name ; exit when mycursor%NOTFOUND; v_num := v_num + 1; v_code2 :='sgcs'||trim(to_char(v_num,'0000')); v_code3 :='sgsj'||trim(to_char(v_num,'0000')); if v_code1 is null then v_code1 :=' ' ; v_name :='未命名'; end if; v_temp1 :=v_temp1||', '''||v_name||''' as '||v_code2; v_temp3 :=v_temp3||', '''||v_name||''' as '||v_code3; end loop; v_temp2 :=',''事故总时间'' as shiguzsj'; v_temp := v_temp1||v_temp2||v_temp3; v_code := v_code||v_temp;
v_sql := v_code||' from c_well a where rownum=1 '; close mycursor; as_resql :=v_sql; as_count := 6 + 2*v_num; end if;if as_type = 3 then v_code :='select ''区块代码'' as qukuaidm,''区块名称'' as qukuaimc,''损失金额'' as sunshije,''损失进尺'' as sunshijc,''事故总次数'' as shiguzcs '; open mycursor for select distinct t.code,f_db_get_jcbmname(t.code) from (select SUBSTR(sglbbm,1,6) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,9) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,12) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,15) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,18) as code from p_sgfzcl ) t; loop fetch mycursor into v_code1,v_name ; exit when mycursor%NOTFOUND; v_num := v_num + 1; v_code2 :='sgcs'||trim(to_char(v_num,'0000')); v_code3 :='sgsj'||trim(to_char(v_num,'0000')); if v_code1 is null then v_code1 :=' ' ; v_name :='未命名'; end if; v_temp1 :=v_temp1||', '''||v_name||''' as '||v_code2; v_temp3 :=v_temp3||', '''||v_name||''' as '||v_code3; end loop; v_temp2 :=',''事故总时间'' as shiguzsj'; v_temp := v_temp1||v_temp2||v_temp3; v_code := v_code||v_temp;
v_sql := v_code||' from c_well a where rownum=1 '; close mycursor; as_resql :=v_sql; as_count := 6 + 2*v_num; end if;
if as_type = 4 then v_code :='select ''井别代码'' as jingbiedm,''井别名称'' as jingbiemc,''损失金额'' as sunshije,''损失进尺'' as sunshijc,''事故总次数'' as shiguzcs '; open mycursor for select distinct t.code,f_db_get_jcbmname(t.code) from (select SUBSTR(sglbbm,1,6) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,9) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,12) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,15) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,18) as code from p_sgfzcl ) t; loop fetch mycursor into v_code1,v_name ; exit when mycursor%NOTFOUND; v_num := v_num + 1; v_code2 :='sgcs'||trim(to_char(v_num,'0000')); v_code3 :='sgsj'||trim(to_char(v_num,'0000')); if v_code1 is null then v_code1 :=' ' ; v_name :='未命名'; end if; v_temp1 :=v_temp1||', '''||v_name||''' as '||v_code2; v_temp3 :=v_temp3||', '''||v_name||''' as '||v_code3; end loop; v_temp2 :=',''事故总时间'' as shiguzsj'; v_temp := v_temp1||v_temp2||v_temp3; v_code := v_code||v_temp;
v_sql := v_code||' from c_well a where rownum=1'; close mycursor; as_resql :=v_sql; as_count := 6 + 2*v_num; end if; EXCEPTION WHEN OTHERS THEN as_resql :='null'; --异常处理信息 as_count :=6; end p_dtsgfztj_getsql_top;
不懂你!!!
CREATE TABLE student
(
id NUMBER,
name VARCHAR2(30),
sex VARCHAR2(10),
address VARCHAR2(100),
postcode VARCHAR2(10),
birthday DATE,
photo LONG RAW
)
/ 2、建立带ref cursor定义的包和包体及函数:
CREATE OR REPLACE
package pkg_test as
/* 定义ref cursor类型
不加return类型,为弱类型,允许动态sql查询,
否则为强类型,无法使用动态sql查询;
*/
type myrctype is ref cursor; --函数申明
function get(intID number) return myrctype;
end pkg_test;
/ CREATE OR REPLACE
package body pkg_test as
--函数体
function get(intID number) return myrctype is
rc myrctype; --定义ref cursor变量
sqlstr varchar2(500);
begin
if intID=0 then
--静态测试,直接用select语句直接返回结果
open rc for select id,name,sex,address,postcode,birthday from student;
else
--动态sql赋值,用:w_id来申明该变量从外部获得
sqlstr := 'select id,name,sex,address,postcode,birthday from student where id=:w_id';
--动态测试,用sqlstr字符串返回结果,用using关键词传递参数
open rc for sqlstr using intid;
end if; return rc;
end get; end pkg_test;
/ 3、用pl/sql块进行测试:
declare
w_rc pkg_test.myrctype; --定义ref cursor型变量 --定义临时变量,用于显示结果
w_id student.id%type;
w_name student.name%type;
w_sex student.sex%type;
w_address student.address%type;
w_postcode student.postcode%type;
w_birthday student.birthday%type; begin
--调用函数,获得记录集
w_rc := pkg_test.get(1); --fetch结果并显示
fetch w_rc into w_id,w_name,w_sex,w_address,w_postcode,w_birthday;
dbms_output.put_line(w_name);
end; 4、测试结果:
通过。 在上述的online document中有相当多的内容,包括怎样在pro*c传递动态参数给存储过程、ref cusor的使用限制、open cursor for的4中不同方法等等。
import java.util.Collection;
import java.util.ArrayList;
import java.text.ParseException;/**
*
* <p>Title: </p>
* <p>Description: 存储过程调用带参数,返回字符串/数字</p>
* <p>Copyright: Copyright (c) 2003</p>
* <p>Company: </p>
* @author
* @version 1.0
*/
public class Show {
private static Connection conn = null;
private static oracle.jdbc.OracleCallableStatement call = null;
private static CallableStatement callst = null;
private static ResultSet rs = null;
private static String url = "jdbc:oracle:thin:@192.168.100.145:1521:kdc";
private static String name = "liuyi";
private static int cout = 0;
private static String date = "2003-11-26"; public static void main(String[] args) throws ParseException {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url, "kdcerp2", "123");
callst = conn.prepareCall("{call p_dtsgfztj_getsql
callst.registerOutParameter(4, java.sql.Types.VARCHAR);
java.text.SimpleDateFormat sf=new java.text.SimpleDateFormat("yyyy-mm-dd") ;
callst.setString(1,"2002-11-26");
callst.setString(2,"2003-11-26");
callst.setString(3,"4");
rs = callst.executeQuery();
String temp = callst.getString(4); System.out.println(temp); }
catch (java.lang.ClassNotFoundException e) {
e.printStackTrace();
}
catch (SQLException e) {
System.out.println(e.toString());
}
}
}
create or replace procedure p_dtsgfztj_getsql_top(as_date1 in varchar2,as_date2 in varchar2,as_type in integer, as_resql out long,as_count out integer) is
/*
--自返回参数:as_resql varchar
--按照所提供的参数信息获取构造的SQL语句
--参数:as_date1 开始时间
-- as_date2 结束时间
-- as_type 统计口径
1:公司
2.区域
3.区块
4.井别
*/
type v_cursor is ref cursor;
mycursor v_cursor; --声明游标变量(获得所有的区域编码)
v_code long; --区域编码集合
v_temp long; --临时
v_temp1 long; --临时
v_temp2 long; --临时
v_temp3 long; --临时
v_temp4 long; --临时
v_sql long; --合计的最终sql
v_val integer; --某一月份的天数
v_code1 varchar2(30);
v_name varchar2(100);
v_num integer;
v_code2 varchar2(30);
v_code3 varchar2(30);
begin
v_temp1 :=' ';
v_temp3 :=' ';
v_num :=0;
if as_type = 1 then
v_code :='select ''公司代码'' as gongsidm,''公司名称'' as gongsimc,''损失金额'' as sunshije,''损失进尺'' as sunshijc,''事故总次数'' as shiguzcs ';
open mycursor for select distinct t.code,f_db_get_jcbmname(t.code) from (select SUBSTR(sglbbm,1,6) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,9) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,12) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,15) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,18) as code from p_sgfzcl ) t;
loop
fetch mycursor into v_code1,v_name ;
exit when mycursor%NOTFOUND;
v_num := v_num + 1;
v_code2 :='sgcs'||trim(to_char(v_num,'0000'));
v_code3 :='sgsj'||trim(to_char(v_num,'0000'));
if v_code1 is null then
v_code1 :=' ' ;
v_name :='未命名';
end if;
v_temp1 :=v_temp1||', '''||v_name||''' as '||v_code2;
v_temp3 :=v_temp3||', '''||v_name||''' as '||v_code3;
end loop;
v_temp2 :=',''事故总时间'' as shiguzsj';
v_temp := v_temp1||v_temp2||v_temp3;
v_code := v_code||v_temp;
v_sql := v_code||' from useunitcode a where a.sfscbz = 0 and rownum=1';
close mycursor;
as_resql :=v_sql;
as_count := 6 + 2*v_num;
end if;
if as_type = 2 then
v_code :='select ''区域代码'' as quyudm,''区域名称'' as quyumc,''损失金额'' as sunshije,''损失进尺'' as sunshijc,''事故总次数'' as shiguzcs ';
open mycursor for select distinct t.code,f_db_get_jcbmname(t.code) from (select SUBSTR(sglbbm,1,6) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,9) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,12) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,15) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,18) as code from p_sgfzcl ) t;
loop
fetch mycursor into v_code1,v_name ;
exit when mycursor%NOTFOUND;
v_num := v_num + 1;
v_code2 :='sgcs'||trim(to_char(v_num,'0000'));
v_code3 :='sgsj'||trim(to_char(v_num,'0000'));
if v_code1 is null then
v_code1 :=' ' ;
v_name :='未命名';
end if;
v_temp1 :=v_temp1||', '''||v_name||''' as '||v_code2;
v_temp3 :=v_temp3||', '''||v_name||''' as '||v_code3;
end loop;
v_temp2 :=',''事故总时间'' as shiguzsj';
v_temp := v_temp1||v_temp2||v_temp3;
v_code := v_code||v_temp;
v_sql := v_code||' from c_well a where rownum=1 ';
close mycursor;
as_resql :=v_sql;
as_count := 6 + 2*v_num;
end if;if as_type = 3 then
v_code :='select ''区块代码'' as qukuaidm,''区块名称'' as qukuaimc,''损失金额'' as sunshije,''损失进尺'' as sunshijc,''事故总次数'' as shiguzcs ';
open mycursor for select distinct t.code,f_db_get_jcbmname(t.code) from (select SUBSTR(sglbbm,1,6) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,9) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,12) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,15) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,18) as code from p_sgfzcl ) t;
loop
fetch mycursor into v_code1,v_name ;
exit when mycursor%NOTFOUND;
v_num := v_num + 1;
v_code2 :='sgcs'||trim(to_char(v_num,'0000'));
v_code3 :='sgsj'||trim(to_char(v_num,'0000'));
if v_code1 is null then
v_code1 :=' ' ;
v_name :='未命名';
end if;
v_temp1 :=v_temp1||', '''||v_name||''' as '||v_code2;
v_temp3 :=v_temp3||', '''||v_name||''' as '||v_code3;
end loop;
v_temp2 :=',''事故总时间'' as shiguzsj';
v_temp := v_temp1||v_temp2||v_temp3;
v_code := v_code||v_temp;
v_sql := v_code||' from c_well a where rownum=1 ';
close mycursor;
as_resql :=v_sql;
as_count := 6 + 2*v_num;
end if;
if as_type = 4 then
v_code :='select ''井别代码'' as jingbiedm,''井别名称'' as jingbiemc,''损失金额'' as sunshije,''损失进尺'' as sunshijc,''事故总次数'' as shiguzcs ';
open mycursor for select distinct t.code,f_db_get_jcbmname(t.code) from (select SUBSTR(sglbbm,1,6) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,9) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,12) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,15) as code from p_sgfzcl UNION select SUBSTR(sglbbm,1,18) as code from p_sgfzcl ) t;
loop
fetch mycursor into v_code1,v_name ;
exit when mycursor%NOTFOUND;
v_num := v_num + 1;
v_code2 :='sgcs'||trim(to_char(v_num,'0000'));
v_code3 :='sgsj'||trim(to_char(v_num,'0000'));
if v_code1 is null then
v_code1 :=' ' ;
v_name :='未命名';
end if;
v_temp1 :=v_temp1||', '''||v_name||''' as '||v_code2;
v_temp3 :=v_temp3||', '''||v_name||''' as '||v_code3;
end loop;
v_temp2 :=',''事故总时间'' as shiguzsj';
v_temp := v_temp1||v_temp2||v_temp3;
v_code := v_code||v_temp;
v_sql := v_code||' from c_well a where rownum=1';
close mycursor;
as_resql :=v_sql;
as_count := 6 + 2*v_num;
end if;
EXCEPTION
WHEN OTHERS THEN
as_resql :='null'; --异常处理信息
as_count :=6;
end p_dtsgfztj_getsql_top;