PACKAGE CONTAINER.CONTAINER_ANNUL_SEAL 编译错误错误: PLS-00103: 出现符号 "TYPE"在需要下列之一时:
language
行: 17
文本: type decl_reg_no_type is table of p_transfer_cont.decl_reg_no%type;
这个上plsql的报错,我的存储过程在定义类型的时候报错
type decl_reg_no_type is table of p_transfer_cont.decl_reg_no%type;
type ent_cname_type is table of p_transfer_cont.ent_cname%type;
type goods_decl_no_type is table of p_transfer_cont.goods_decl_no%type;
type goods_model_type is table of p_transfer_cont.goods_model%type;
t_decl_reg_no decl_reg_no_type;
t_ent_cname ent_cname_type;
t_goods_decl_no goods_decl_no_type;
t_goods_model goods_model_type;
v_date date;
v_parameter varchar2(100); 我其他的存储过程这样写也没什么问题,但这个不好用。希望大家帮忙
language
行: 17
文本: type decl_reg_no_type is table of p_transfer_cont.decl_reg_no%type;
这个上plsql的报错,我的存储过程在定义类型的时候报错
type decl_reg_no_type is table of p_transfer_cont.decl_reg_no%type;
type ent_cname_type is table of p_transfer_cont.ent_cname%type;
type goods_decl_no_type is table of p_transfer_cont.goods_decl_no%type;
type goods_model_type is table of p_transfer_cont.goods_model%type;
t_decl_reg_no decl_reg_no_type;
t_ent_cname ent_cname_type;
t_goods_decl_no goods_decl_no_type;
t_goods_model goods_model_type;
v_date date;
v_parameter varchar2(100); 我其他的存储过程这样写也没什么问题,但这个不好用。希望大家帮忙
/*
name:
author:
date: 2007-06
desc: 用于事后核销封号
*/
AS
/*
type: 存储过程
desc: 用于事后核销封号
author:
date: 2007-06
*/
procedure sp_container_ANNUL_seal(p_out_result out int) --返回操作结果
is
type decl_reg_no_type is table of p_transfer_cont.decl_reg_no%type;
type ent_cname_type is table of p_transfer_cont.ent_cname%type;
type goods_decl_no_type is table of p_transfer_cont.goods_decl_no%type;
type goods_model_type is table of p_transfer_cont.goods_model%type;
t_decl_reg_no decl_reg_no_type;
t_ent_cname ent_cname_type;
t_goods_decl_no goods_decl_no_type;
t_goods_model goods_model_type;
v_date date;
v_parameter varchar2(100); --核销过期时间参数
CURSOR v_cur IS select decl_reg_no,ent_cname,goods_model,goods_decl_no
from p_transfer_cont t
where t.annul_flag ='0005' and t.goods_model_ is not null
and t.annul_rear is not null and t.makeup_date is null
and t.goods_model <>'0000' and t.seal is null
and t.annul_date < (v_date-v_parameter);
begin
select trunc(sysdate)
into v_date
from dual; select p.p_value --阀值
into v_parameter
from p_parameters p
where p.id = 3; open v_cur;
loop
fetch v_cur bulk collect into t_decl_reg_no, t_ent_cname,t_goods_decl_no,t_goods_model LIMIT 100;
forall i in 1 .. t_goods_decl_no.count
insert into p_operator_note (user_id,operate_date,ent_reg_code,ent_cname,whys)
values ('system',sysdate,t_decl_reg_no(i),t_ent_cname(i),'系统封号');
-- end loop;
exit when v_cur%notfound;
end loop; close v_cur; commit;
p_out_result := 1; exception
when others
then rollback;
p_out_result := 0;
end sp_container_ANNUL_seal;END container_ANNUL_seal;
这个是我的存储过程。
type ent_cname_type is table of p_transfer_cont.ent_cname%type;
type goods_decl_no_type is table of p_transfer_cont.goods_decl_no%type;
type goods_model_type is table of p_transfer_cont.goods_model%type;
t_decl_reg_no decl_reg_no_type;
t_ent_cname ent_cname_type;
t_goods_decl_no goods_decl_no_type;
t_goods_model goods_model_type;修改成=>
t_decl_reg_no p_transfer_cont.decl_reg_no%type;
t_ent_cname p_transfer_cont.ent_cname%type;
t_goods_decl_no p_transfer_cont.goods_decl_no%type;
t_goods_model p_transfer_cont.goods_model%type;
loop
fetch v_cur bulk collect into t_decl_reg_no, t_ent_cname,t_goods_decl_no,t_goods_model LIMIT 100;
forall i in 1 .. t_goods_decl_no.count
insert into p_operator_note (user_id,operate_date,ent_reg_code,ent_cname,whys)
values ('system',sysdate,t_decl_reg_no(i),t_ent_cname(i),'系统封号');
-- end loop;
exit when v_cur%notfound;
end loop;
主要就是把游标里面的东西插入
(user_id,operate_date,ent_reg_code,ent_cname,whys)
values ('system',sysdate,t_decl_reg_no(i),t_ent_cname(i),'系统封号');
现在只能插入一条记录。
/
insert into t1
select 1,1 from dual
union
select 2,2 from dual
union
select 3,3 from dual
union
select 4,4 from dual;
/
commit;
/
create table t2( cid int,cname int );
/
--测试
declare
type tcid is table of t1.cid%type;
type tcname is table of t1.cname%type;
fcid tcid;
fcname tcname;
cursor v_cur is select cid,cname from t1;
begin
open v_cur;
loop
fetch v_cur bulk collect into fcid,fcname LIMIT 100;
forall i in fcid.first..fcid.count
insert into t2 values(fcid(i),fcname(i));
exit when v_cur%notfound;
end loop;
close v_cur;
end;
--输出结果select * from t2
1 1
2 2
3 3
4 4
from p_transfer_cont t
where t.annul_flag ='0005' and t.goods_model_ is not null
and t.annul_rear is not null and t.makeup_date is null
and t.goods_model <>'0000' and t.seal is null
and t.annul_date < (v_date-v_parameter);最后一句 v_date - v_parameter有问题
CREATE OR REPLACE PACKAGE container_ANNUL_seal
/*
name:
author:
date: 2007-06
desc: 用于事后核销封号
*/
AS
/*
type: 存储过程
desc: 用于事后核销封号
author:
date: 2007-06
*/
procedure sp_container_ANNUL_seal(p_out_result out int) --返回操作结果
is
type decl_reg_no_type is table of p_transfer_cont.decl_reg_no%type;
type ent_cname_type is table of p_transfer_cont.ent_cname%type;
type goods_decl_no_type is table of p_transfer_cont.goods_decl_no%type;
type goods_model_type is table of p_transfer_cont.goods_model%type;
t_decl_reg_no decl_reg_no_type;
t_ent_cname ent_cname_type;
t_goods_decl_no goods_decl_no_type;
t_goods_model goods_model_type;
v_date date;
v_parameter varchar2(100); --核销过期时间参数 TYPE refCur IS REF CURSOR; /*加上这句*/
v_cur refCur; /*加上这句*/
/*
CURSOR v_cur IS select decl_reg_no,ent_cname,goods_model,goods_decl_no
from p_transfer_cont t
where t.annul_flag ='0005' and t.goods_model_ is not null
and t.annul_rear is not null and t.makeup_date is null
and t.goods_model <>'0000' and t.seal is null
and t.annul_date < (v_date-v_parameter);
*/
begin
select trunc(sysdate)
into v_date
from dual; select p.p_value --阀值
into v_parameter
from p_parameters p
where p.id = 3; /*原open v_cur改成如下形式*/
open v_cur for select decl_reg_no,ent_cname,goods_model,goods_decl_no
from p_transfer_cont t
where t.annul_flag ='0005' and t.goods_model_ is not null
and t.annul_rear is not null and t.makeup_date is null
and t.goods_model <>'0000' and t.seal is null
and t.annul_date < (:v_date-: v_parameter) using v_date, v_parameter; /*注意using*/
loop
fetch v_cur bulk collect into t_decl_reg_no, t_ent_cname,t_goods_decl_no,t_goods_model LIMIT 100;
forall i in 1 .. t_goods_decl_no.count
insert into p_operator_note (user_id,operate_date,ent_reg_code,ent_cname,whys)
values ('system',sysdate,t_decl_reg_no(i),t_ent_cname(i),'系统封号');
-- end loop;
exit when v_cur%notfound;
end loop; close v_cur; commit;
p_out_result := 1; exception
when others
then rollback;
p_out_result := 0;
end sp_container_ANNUL_seal;END container_ANNUL_seal;