可以的:
首先创建一个包Pk_Fhjpackage,在包中声明:CREATE OR REPLACE PACKAGE pkg_RCTest AS TYPE RcType IS REF CURSOR;
PROCEDURE getRc(p_name VARCHAR2,p_id NUMBER, p_rc OUT RcType);
END pkg_RCTest;CREATE OR REPLACE PACKAGE BODY pkg_RCTest AS
PROCEDURE getRc(p_name VARCHAR2, p_id NUMBER, p_rc OUT RcType)
IS
sqlstr VARCHAR2 (500);
BEGIN
IF p_id = 0 THEN
OPEN p_rc FOR
SELECT * FROM ipiln.t_parameters;
ELSE
sqlstr := 'select * FROM '||p_name;
OPEN p_rc FOR sqlstr USING p_id;
END IF;
END getRc;
END pkg_RCTest;在存储过程Sp_Getinfo中:
增加一个in out 参数: p_RC out pkg_RCTest.RcType;
Open p_RC from SQL_Query语句测试如下:
DECLARE
v_RC pkg_RCTest.RcType;
w_name VARCHAR2(64);
w_address VARCHAR2(100);
RecID NUMBER(3);
BEGIN
--pkg_RCTest.GetRC('', 0, v_RC);
RecID := 0;
ipiln.Sp_Getinfo(v_RC);
LOOP
FETCH v_RC INTO w_name,w_address;
EXIT WHEN v_RC%NOTFOUND;
dbms_output.put_line(TO_CHAR(RecID)||' '||w_name ||','||w_address);
RecID := RecID +1;
END LOOP;
END;是这个意思吧?
首先创建一个包Pk_Fhjpackage,在包中声明:CREATE OR REPLACE PACKAGE pkg_RCTest AS TYPE RcType IS REF CURSOR;
PROCEDURE getRc(p_name VARCHAR2,p_id NUMBER, p_rc OUT RcType);
END pkg_RCTest;CREATE OR REPLACE PACKAGE BODY pkg_RCTest AS
PROCEDURE getRc(p_name VARCHAR2, p_id NUMBER, p_rc OUT RcType)
IS
sqlstr VARCHAR2 (500);
BEGIN
IF p_id = 0 THEN
OPEN p_rc FOR
SELECT * FROM ipiln.t_parameters;
ELSE
sqlstr := 'select * FROM '||p_name;
OPEN p_rc FOR sqlstr USING p_id;
END IF;
END getRc;
END pkg_RCTest;在存储过程Sp_Getinfo中:
增加一个in out 参数: p_RC out pkg_RCTest.RcType;
Open p_RC from SQL_Query语句测试如下:
DECLARE
v_RC pkg_RCTest.RcType;
w_name VARCHAR2(64);
w_address VARCHAR2(100);
RecID NUMBER(3);
BEGIN
--pkg_RCTest.GetRC('', 0, v_RC);
RecID := 0;
ipiln.Sp_Getinfo(v_RC);
LOOP
FETCH v_RC INTO w_name,w_address;
EXIT WHEN v_RC%NOTFOUND;
dbms_output.put_line(TO_CHAR(RecID)||' '||w_name ||','||w_address);
RecID := RecID +1;
END LOOP;
END;是这个意思吧?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货