你是用什么语言?我用过.net来调用
OleDbCommand myCommand=new OleDbCommand("pkg_test.get_data",oleConnect);
myCommand.CommandType=CommandType.StoredProcedure;
myCommand.Parameters.Add(new OleDbParameter ("@data",OleDbType.VarChar,10));
myCommand.Parameters["@data"].Value=s_firstname.Text;
myCommand.Prepare();
OleDbDataAdapter da = new OleDbDataAdapter(myCommand);
pkg_test//包
get_data//过程
OleDbCommand myCommand=new OleDbCommand("pkg_test.get_data",oleConnect);
myCommand.CommandType=CommandType.StoredProcedure;
myCommand.Parameters.Add(new OleDbParameter ("@data",OleDbType.VarChar,10));
myCommand.Parameters["@data"].Value=s_firstname.Text;
myCommand.Prepare();
OleDbDataAdapter da = new OleDbDataAdapter(myCommand);
pkg_test//包
get_data//过程
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结果并显示
loop
fetch w_rc into w_id,w_name,w_sex,w_address,w_postcode,w_birthday;
exit when w_rc%notfound;
dbms_output.put_line(w_name);
end loop;
end;
4、测试结果:
通过。
名称 空? 类型
----------------------------------------- -------- ----------------------------
ID NUMBER
XK_SX NUMBER
XK_WSX_DJ NUMBER
XK_WSX_XJ NUMBER
XK_WSX_HJ NUMBER
F_ZS NUMBER
F_WZS_DJ NUMBER
F_WZS_XJ NUMBER
F_WZS_HJ NUMBER
SW_CS_DJ NUMBER
SW_CS_XJ NUMBER
SW_CS_HJ NUMBER
BZ CHAR(100)
YY CHAR(4)
XZQ NUMBER
WRR2 NUMBER
WRR3 NUMBER
WRR2NAME CHAR(50)
WRR3NAME CHAR(50)
ADDVNM CHAR(9)2.在ORACLE中包的定义:CREATE OR REPLACE PACKAGE nb_supFind
AS
AS
TYPE myrctype IS REF CURSOR;
PROCEDURE RecSel( p_StartDate IN CHAR,
p_EndDate IN CHAR,
p_Region IN VARCHAR2, --分区内容
p_flag IN NUMBER, --标识是行政区/二级区/三级区
p_col IN VARCHAR2, --选中项目名
p_table IN VARCHAR2, --操作表名
v_ADDV OUT myrctype --作为输出的游标
);
END nb_supFind;CREATE OR REPLACE PROCEDURE NB_SURERFIND (
--参数声明
p_StartDate IN VIEW_NB_TYGL.YY%TYPE,
p_EndDate IN VIEW_NB_TYGL.YY%TYPE,
p_Region IN VARCHAR2, --分区内容
p_flag IN NUMBER, --标识是行政区/二级区/三级区
p_item IN NUMBER,
p_table IN VARCHAR2,
)
AS
PROCEDURE RecSel(
p_StartDate IN CHAR,
p_EndDate IN CHAR,
p_Region IN VARCHAR2, --分区内容
p_flag IN NUMBER, --标识是行政区/二级区/三级区
p_col IN VARCHAR2, --选中项目名
p_table IN VARCHAR2, --操作表名
v_ADDV OUT myrctype --作为输出的游标
) IS
strsql VARCHAR2(500);
BEGIN
IF p_flag = 1 then
if p_Region = 'NULL' then
strsql:='SELECT ADDVNM,YY,SUM('||p_col||')
FROM '||p_table||
' WHERE YY <= '||p_EndDate||' AND YY>='|| p_StartDate||'
GROUP BY ADDVNM,YY';
else
strsql:='SELECT ADDVNM,YY,SUM('||p_col||')
FROM '||p_table||
' WHERE YY <= TO_NUMBER('||p_EndDate||') AND YY>=TO_NUMBER('|| p_StartDate||') AND ADDVNM='''||p_Region||
''' GROUP BY ADDVNM,YY';
end if;
DBMS_OUTPUT.PUT_LINE(strsql);
--打开动态游标
OPEN v_ADDV FOR strsql;
END IF;
END RecSel;
END nb_supFind; 3.在SQL PLUS中测试:
set serveroutput on
DECLARE
p_rc NB_SUPFIND.myrctype;
str varchar2(50);
BEGIN
NB_SUPFIND.RecSel('2000','2000','北京',1,'YSQ_SL','VIEW_NB_QSXK',p_rc);
LOOP
FETCH p_rc INTO str;
DBMS_OUTPUT.PUT_LINE(str);
EXIT WHEN p_rc%NOTFOUND;
END LOOP;
END;成功!4.在ASP里的调用
Set Conn=Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("ADODB.Recordset")
'以下两句含在globo文件中
connstr="Provider=MSDAORA;Password=water;User ID=water;Data Source=oradb_netserver"
Session("connstr")=connstr
conn.Open Session("connstr") Set CMD=Server.CreateObject("ADODB.Command")
CMD.ActiveConnection = conn
CMD.CommandType = adCmdText
CMD.CommandTimeout = 0
CMD.Prepared = true CMD.CommandText = "{call nb_supFind.RecSel(?,?,?,?,?,?)}" 这里只写输入不写输出
CMD.Parameters.Append CMD.CreateParameter("p_StartDate",129,1,4,"2000")'开始年份
CMD.Parameters.Append CMD.CreateParameter("p_EndDate",129,1,4,EndYr) '结束年份
CMD.Parameters.Append CMD.CreateParameter("p_Region",129,1,9,Addv) '行政区名
CMD.Parameters.Append CMD.CreateParameter("p_flag",3,1,4,1) '标识分区
CMD.Parameters.Append CMD.CreateParameter("p_col",200,1,50,Item) '选中项目名
CMD.Parameters.Append CMD.CreateParameter("p_table",200,1,100,Tname) '得到表名 set rs.Source = CMD
on error resume next
set rs=CMD.Execute
if err.number <> 0 then
response.Write err.Description
response.End
end if
rs.Open()
pkg_test//包
get_data()//函数
"pkg_test.get_data"就可以调用了,你自己试试就知道了
在.NET无法写