现有包
CREATE OR REPLACE PACKAGE PKG_AllConsumeBlock is
TYPE myrctype IS REF CURSOR;
PROCEDURE usp_AllConsumeBlock(cur_out OUT myrctype,StartMth char,FinishiMth char);
end PKG_AllConsumeBlock;包体
CREATE OR REPLACE PACKAGE BODY PKG_AllConsumeBlock AS
PROCEDURE usp_AllConsumeBlock
(
cur_out OUT myrctype,StartMth char,FinishiMth char
)
IS
BEGIN
OPEN cur_out FOR
SELECT * from 表名 where 条件 ;
END usp_AllConsumeBlock;
END PKG_AllConsumeBlock;请问该如何调用来进行查询?谢谢各位了
CREATE OR REPLACE PACKAGE PKG_AllConsumeBlock is
TYPE myrctype IS REF CURSOR;
PROCEDURE usp_AllConsumeBlock(cur_out OUT myrctype,StartMth char,FinishiMth char);
end PKG_AllConsumeBlock;包体
CREATE OR REPLACE PACKAGE BODY PKG_AllConsumeBlock AS
PROCEDURE usp_AllConsumeBlock
(
cur_out OUT myrctype,StartMth char,FinishiMth char
)
IS
BEGIN
OPEN cur_out FOR
SELECT * from 表名 where 条件 ;
END usp_AllConsumeBlock;
END PKG_AllConsumeBlock;请问该如何调用来进行查询?谢谢各位了
或者以查询的方式select PKG_AllConsumeBlock.PROCEDURE usp_AllConsumeBlock (有输入参数的填写)
from dual;
一楼的说的不错,顶一楼,动作太快了!
我用的下面的语句execute PKG_AllConsumeBlock.PROCEDURE usp_AllConsumeBlock ('200801','200902')
出错啊,提示ORA-00900: 无效 SQL 语句
SQL> var c refcursor
SQL> begin
2 PKG_AllConsumeBlock.PROCEDURE usp_AllConsumeBlock(:c, para1, para2);
3 end;
4 /PL/SQL procedure successfully completed.SQL> print :c
var c refcursor //申明一个变量c,类型是refcursor
//通过plsql块调用包里的存储过程
begin
PKG_AllConsumeBlock.PROCEDURE usp_AllConsumeBlock(:c, para1, para2); //:c就是代表刚才的变量,para1, para2就是需要输入的参数StartMth和FinishiMth的值
end;print :c //把变量c获取到的游标的内容打印在终端上,便于查看举个简单例子:
SQL> create or replace package pkg_refcursor is
2 type cur is ref cursor;
3 procedure returncur(c out cur, num int);
4 end pkg_refcursor;
5 / Package created.SQL> create or replace package body pkg_refcursor as
2 procedure returncur(c out cur, num int) is
3 begin
4 open c for select sysdate from dual;
5 end returncur;
6 end pkg_refcursor;
7 / Package body created.SQL> var c refcursor
SQL> begin
2 pkg_refcursor.returncur(:c, 1);
3 end;
4 /PL/SQL procedure successfully completed.SQL> print :cSYSDATE
-------------------
2009-02-18 09:36:33