create or REPLACE PACKAGE P_CREASTAT
is
type myCursor is ref cursor;
PROCEDURE PBD_110STAT(STR_DW IN VARCHAR2, NUM_YEAR IN NUMBER)
end P_CREASTAT;Create Or Replace Package Body P_CREASTAT
is
PROCEDURE PBD_110STAT(STR_DW IN VARCHAR2, NUM_YEAR IN NUMBER)
as
Re_CURSOR myCursor;
BEGIN
OPEN Re_CURSOR FOR select * from T_S110STAT;
return Re_CURSOR;
END PBD_110STAT;
end P_CREASTATPrivate Sub Command3_Click()
Dim cmdAAA As New ADODB.Command
Dim rstTmp As ADODB.Recordset
Dim rstBK As New ADODB.Recordset
Set cmdAAA.ActiveConnection = m_Conn cmdAAA.CommandType = adCmdStoredProc cmdAAA.CommandText = "P_CREASTAT.PBD_110STAT"
cmdAAA.Parameters.Append cmdAAA.CreateParameter(, adBSTR, adParamInput)
cmdAAA.Parameters.Append cmdAAA.CreateParameter(, adNumeric, adParamInput) cmdAAA.Parameters(0).Value = "丰台"
cmdAAA.Parameters(1) = 2004 Set rstTmp = cmdAAA.ExecuteReader()
Set DataGrid1.DataSource = rstTmp 'rstTmp End Sub
is
type myCursor is ref cursor;
PROCEDURE PBD_110STAT(STR_DW IN VARCHAR2, NUM_YEAR IN NUMBER)
end P_CREASTAT;Create Or Replace Package Body P_CREASTAT
is
PROCEDURE PBD_110STAT(STR_DW IN VARCHAR2, NUM_YEAR IN NUMBER)
as
Re_CURSOR myCursor;
BEGIN
OPEN Re_CURSOR FOR select * from T_S110STAT;
return Re_CURSOR;
END PBD_110STAT;
end P_CREASTATPrivate Sub Command3_Click()
Dim cmdAAA As New ADODB.Command
Dim rstTmp As ADODB.Recordset
Dim rstBK As New ADODB.Recordset
Set cmdAAA.ActiveConnection = m_Conn cmdAAA.CommandType = adCmdStoredProc cmdAAA.CommandText = "P_CREASTAT.PBD_110STAT"
cmdAAA.Parameters.Append cmdAAA.CreateParameter(, adBSTR, adParamInput)
cmdAAA.Parameters.Append cmdAAA.CreateParameter(, adNumeric, adParamInput) cmdAAA.Parameters(0).Value = "丰台"
cmdAAA.Parameters(1) = 2004 Set rstTmp = cmdAAA.ExecuteReader()
Set DataGrid1.DataSource = rstTmp 'rstTmp End Sub
解决方案 »
- 关于oracle时间问题?
- 怎么取 count 的值给一个int型的变量呢
- 两表关联怎么取一表中的最早的版本记录的sql语句
- 帮忙解释以下的sql:::
- 求助:一个SQL错误
- 我只有redhat linux 9,在家里自学安装和管理oracle,用那个版本的oracle好呢?
- oracle的long,long raw数据类型的操作问题
- 高分求助解决问题:
- Oracle9i中shared memory realm does not exist是怎么回事??困扰好几个星期??Help Me??
- 谁有通过JBuilder的proceduredataset返回Oracle的数据集的例子。
- OCI怎么比OO4O还慢?(VC)
- 向懂ORACLE的求助!!
名称 空? 类型
----------------------------------------- -------- ----------------------------
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()
调试已通过