各位大虾,小弟初次写oracle的存储过程,请帮忙看下:Create or replace package S_pkg
is
type ResultRows is ref cursor;
procedure pCt(staffId varchar2, startDate varchar2,
endDate varchar2, resultRows ResultRows);
end S_pkg;
/
create or replace package body S_pkg isprocedure pCt( staffId VARCHAR2,
startDate VARCHAR2,
endDate VARCHAR2,
resultRows out ResultRows
)
is
sDate VARCHAR2(7) := to_char(add_months(sysdate,-12),'yyyy-MM');
eDate VARCHAR2(7) := to_char(sysdate, 'yyyy-MM');
begin
if startDate is not null then
sDate := startDate;
end if;
if endDate is not null then
eDate := endDate;
end if; open resultRows for
select count(*) as counts,
p.provname as provname,
to_char(d.reg_date, 'yyyy-MM') as reg_date
from domain d, contact c, province p
where d.staff_id = staffId
and to_char(d.reg_date, 'yyyy-MM') > sDate
and to_char(d.reg_date, 'yyyy-MM') <= eDate
and d.registrant = c.contact_id
and c.sp = p.provinceid
group by p.provname, to_char(d.reg_date, 'yyyy-MM')
order by p.provname, to_char(d.reg_date, 'yyyy-MM');
end;
end S_pkg;
/
每次执行都会出现“子程序或游标'pCt'已在程序包说明中说明,必须在程序包体中对其进行定义。”
is
type ResultRows is ref cursor;
procedure pCt(staffId varchar2, startDate varchar2,
endDate varchar2, resultRows ResultRows);
end S_pkg;
/
create or replace package body S_pkg isprocedure pCt( staffId VARCHAR2,
startDate VARCHAR2,
endDate VARCHAR2,
resultRows out ResultRows
)
is
sDate VARCHAR2(7) := to_char(add_months(sysdate,-12),'yyyy-MM');
eDate VARCHAR2(7) := to_char(sysdate, 'yyyy-MM');
begin
if startDate is not null then
sDate := startDate;
end if;
if endDate is not null then
eDate := endDate;
end if; open resultRows for
select count(*) as counts,
p.provname as provname,
to_char(d.reg_date, 'yyyy-MM') as reg_date
from domain d, contact c, province p
where d.staff_id = staffId
and to_char(d.reg_date, 'yyyy-MM') > sDate
and to_char(d.reg_date, 'yyyy-MM') <= eDate
and d.registrant = c.contact_id
and c.sp = p.provinceid
group by p.provname, to_char(d.reg_date, 'yyyy-MM')
order by p.provname, to_char(d.reg_date, 'yyyy-MM');
end;
end S_pkg;
/
每次执行都会出现“子程序或游标'pCt'已在程序包说明中说明,必须在程序包体中对其进行定义。”
解决方案 »
- 跪求:使用distinct如何能在得到目标字段不重复值的同时返回其他字段值?
- 邪门了 创建表 哪错了
- 如果查找以前运行过的SQL?
- ora-01031:insufficient privileges问题
- (菜乌请教)如何查看已生成的表的脚本?
- plsql到底怎么导入dmp表呢,折磨一天了 ,求高手解答
- 有没有兄弟有Oracle 阴历转阳历的函数
- 请问谁能有关toad的帮助文档?
- 一个实在令我想不通的问题,关于update语句更新(oracle)
- 数据库怎么插入大数据字段……CLOB类型,小美求救
- 另一PL/SQL简单的Select问题,关于传入参数查询
- 各位大虾帮帮忙,在pl/sql developer中下列需求如何实现???????????????
Create or replace package S_pkg
is
type ResultRows is ref cursor;
procedure pCt(staffId varchar2, startDate varchar2,
endDate varchar2, resultRow ResultRows);
end S_pkg;
/
create or replace package body S_pkg is procedure pCt( staffId VARCHAR2,
startDate VARCHAR2,
endDate VARCHAR2,
resultRow out ResultRows
)
is
sDate VARCHAR2(7) := to_char(add_months(sysdate,-12),'yyyy-MM');
eDate VARCHAR2(7) := to_char(sysdate, 'yyyy-MM');
begin
if startDate is not null then
sDate := startDate;
end if;
if endDate is not null then
eDate := endDate;
end if; open resultRow for
select count(*) as counts,
p.provname as provname,
to_char(d.reg_date, 'yyyy-MM') as reg_date
from domain d, contact c, province p
where d.staff_id = staffId
and to_char(d.reg_date, 'yyyy-MM') > sDate
and to_char(d.reg_date, 'yyyy-MM') <= eDate
and d.registrant = c.contact_id
and c.sp = p.provinceid
group by p.provname, to_char(d.reg_date, 'yyyy-MM')
order by p.provname, to_char(d.reg_date, 'yyyy-MM');
end;
end S_pkg;
/
Line: 4
Line: 4
Create or replace package S_pkg
is
type ResultRows is ref cursor;
procedure pCt(staffId varchar2, startDate varchar2,
endDate varchar2, resultRow out ResultRows);
end S_pkg;
/
create or replace package body S_pkg is procedure pCt( staffId VARCHAR2,
startDate VARCHAR2,
endDate VARCHAR2,
resultRow out ResultRows
)
is
sDate VARCHAR2(7) := to_char(add_months(sysdate,-12),'yyyy-MM');
eDate VARCHAR2(7) := to_char(sysdate, 'yyyy-MM');
begin
if startDate is not null then
sDate := startDate;
end if;
if endDate is not null then
eDate := endDate;
end if; open resultRow for
select count(*) as counts,
p.provname as provname,
to_char(d.reg_date, 'yyyy-MM') as reg_date
from domain d, contact c, province p
where d.staff_id = staffId
and to_char(d.reg_date, 'yyyy-MM') > sDate
and to_char(d.reg_date, 'yyyy-MM') <= eDate
and d.registrant = c.contact_id
and c.sp = p.provinceid
group by p.provname, to_char(d.reg_date, 'yyyy-MM')
order by p.provname, to_char(d.reg_date, 'yyyy-MM');
end;
end S_pkg;
/
倒数第二行..............
group by p.provname, to_char(d.reg_date, 'yyyy-MM')
order by p.provname, to_char(d.reg_date, 'yyyy-MM');
end pCt;
end S_pkg;
这个pCt我记得可以不用写的,直接end就可以了,再试试。。