下面是我在另一个存储过程调用 pro_basic_inter_sequence_apps,可是编译不过去. create or replace procedure noahark.Pro_basic_inter_entity_apps as v_entitycode tbl_basic_inter_entity_apps.centitycode%type; v_entitytypename tbl_basic_inter_entity_apps.centityname%type; v_bisend tbl_basic_inter_entity_apps.bisend%type;
exec pro_basic_inter_sequence_apps; cursor c1 is select centitycode,casstypecode,ire from noahark.asstype_apps; beginopen c1; for i in 1..4 loop fetch c1 into v_entitycode,v_entitytypename,v_bisend; if v_bisend is null then v_bisend := 1; end if; insert into tbl_basic_inter_entity_apps(id,centitycode,centityname,ilevel) values( Pro_basic_inter_SEQUENCE_apps.nextv_entitycode,v_entitytypename,v_bisend); COMMIT; end loop; close c1; end;
create or replace procedure noahark.Pro_basic_inter_entity_apps as v_id tbl_basic_inter_entity_apps.centitycode%type; v_entitycode tbl_basic_inter_entity_apps.centitycode%type; v_entitytypename tbl_basic_inter_entity_apps.centityname%type; v_bisend tbl_basic_inter_entity_apps.bisend%type;
exec noahark.pro_basic_inter_sequence_apps; cursor c1 is select id,centitycode,casstypecode,ire from noahark.asstype_apps; beginopen c1; for i in 1..4 loop fetch c1 into v_id,v_entitycode,v_entitytypename,v_bisend; if v_bisend is null then v_bisend := 1; end if; insert into tbl_basic_inter_entity_apps(id,centitycode,centityname,ilevel) values(pro_basic_inter_sequence_apps.nextval, v_entitycode,v_entitytypename,v_bisend); COMMIT; end loop; close c1; end; 高手给指点指点哪里有错误.谢谢了.
insert into tbl_basic_inter_entity_apps(id,centitycode,centityname,ilevel) select pro_basic_inter_sequence_apps.nextval, centitycode,casstypecode, nvl(ire,1) from noahark.asstype_apps这样一句话就可以实现了 游标,存储过程都可以省了
icedut(冰)你有MSN吗?可以跟你联系一下吗?
因为我是刚用三四天的ORACLE,有些地方还用不好,希望向你学习学习.
create or replace procedure noahark.Pro_basic_inter_entity_apps as begin exec pro_basic_inter_sequence_apps; insert into tbl_basic_inter_entity_apps(id,centitycode,centityname,ilevel) select pro_basic_inter_sequence_apps.nextval, centitycode,casstypecode, nvl(ire,1) from noahark.asstype_apps;end; 这样写不行,编译不过去.
create or replace procedure noahark.Pro_basic_inter_entity_apps as v_id tbl_basic_inter_entity_apps.centitycode%type; v_entitycode tbl_basic_inter_entity_apps.centitycode%type; v_entitytypename tbl_basic_inter_entity_apps.centityname%type; v_bisend tbl_basic_inter_entity_apps.bisend%type; --创建序列 create or replace procedure Pro_basic_inter_SEQUENCE_apps as v_sql varchar(200); begin v_sql:='create sequence seq_name minvalue 1 maxvalue 9999999 start with 1increment by 1'; execute immediate v_sql; end pro_basic_inter_SEQUENCE_apps; exec pro_basic_inter_SEQUENCE_apps; --创建游标 cursor c1 is select pro_basic_inter_sequence_apps.nextval,centitycode,casstypecode,ire from noahark.asstype_apps; beginopen c1; for i in 1..4 loop fetch c1 into v_id,v_entitycode,v_entitytypename,v_bisend; if v_bisend is null then v_bisend := 1; end if; insert into tbl_basic_inter_entity_apps(id,centitycode,centityname,ilevel) values(v_id,v_entitycode,v_entitytypename,v_bisend); COMMIT; end loop; close c1; end;这个是我用游标写的存储过程,麻烦你帮我看看是哪里的错误?谢谢了
存储过程里面怎么还有一个存储过程create or replace procedure Pro_basic_inter_SEQUENCE_apps as v_sql varchar(200); begin v_sql:='create sequence seq_name minvalue 1 maxvalue 9999999 start with 1increment by 1'; execute immediate v_sql; end pro_basic_inter_SEQUENCE_apps; --创建存储过程exec pro_basic_inter_SEQUENCE_apps; --执行 insert into tbl_basic_inter_entity_apps(id,centitycode,centityname,ilevel) select seq_name.nextval, centitycode,casstypecode, nvl(ire,1) from noahark.asstype_apps; --应该就是你要的结果了吧
create or replace procedure noahark.Pro_basic_inter_entity_apps as begin insert into tbl_basic_inter_entity_apps(id,centitycode,centityname,ilevel) select seq_name.nextval, centitycode,casstypecode, nvl(ire,1) from noahark.asstype_apps;end; --如果你想用存储过程,这样写应该就可以了
CREATE OR REPLACE PROCEDURE ll_05 IS
v_sql varchar2(200);
BEGIN
v_Sql:='CREATE SEQUENCE S_ll_05 START WITH 1 MAXVALUE 999 MINVALUE 1';
execute immediate v_sql;
END ll_05;
/不过由于权限不够没有执行成功
v_sql varchar2(200);
BEGIN
v_Sql:='CREATE SEQUENCE S_ll_05 START WITH 1 MAXVALUE 999 MINVALUE 1';
execute immediate v_sql;
END ll_05;
/SQL> exec ll_05;PL/SQL procedure successfully completed.
即可
下面是我在另一个存储过程调用 pro_basic_inter_sequence_apps,可是编译不过去.
create or replace procedure noahark.Pro_basic_inter_entity_apps as v_entitycode tbl_basic_inter_entity_apps.centitycode%type;
v_entitytypename tbl_basic_inter_entity_apps.centityname%type;
v_bisend tbl_basic_inter_entity_apps.bisend%type;
exec pro_basic_inter_sequence_apps;
cursor c1 is
select centitycode,casstypecode,ire from noahark.asstype_apps;
beginopen c1;
for i in 1..4 loop
fetch c1 into v_entitycode,v_entitytypename,v_bisend;
if v_bisend is null then
v_bisend := 1;
end if;
insert into tbl_basic_inter_entity_apps(id,centitycode,centityname,ilevel) values( Pro_basic_inter_SEQUENCE_apps.nextv_entitycode,v_entitytypename,v_bisend);
COMMIT;
end loop;
close c1;
end;
v_entitycode tbl_basic_inter_entity_apps.centitycode%type;
v_entitytypename tbl_basic_inter_entity_apps.centityname%type;
v_bisend tbl_basic_inter_entity_apps.bisend%type;
exec noahark.pro_basic_inter_sequence_apps;
cursor c1 is
select id,centitycode,casstypecode,ire from noahark.asstype_apps;
beginopen c1;
for i in 1..4 loop
fetch c1 into v_id,v_entitycode,v_entitytypename,v_bisend;
if v_bisend is null then
v_bisend := 1;
end if;
insert into tbl_basic_inter_entity_apps(id,centitycode,centityname,ilevel) values(pro_basic_inter_sequence_apps.nextval, v_entitycode,v_entitytypename,v_bisend);
COMMIT;
end loop;
close c1;
end;
高手给指点指点哪里有错误.谢谢了.
是否pro_basic_inter_sequence_apps对象不存在
为什么要在存储过程里面创建序列阿
直接用脚本都创建好就行了
select pro_basic_inter_sequence_apps.nextval,
centitycode,casstypecode,
nvl(ire,1) from noahark.asstype_apps这样一句话就可以实现了
游标,存储过程都可以省了
exec pro_basic_inter_sequence_apps;
insert into tbl_basic_inter_entity_apps(id,centitycode,centityname,ilevel)
select pro_basic_inter_sequence_apps.nextval,
centitycode,casstypecode,
nvl(ire,1) from noahark.asstype_apps;end;
这样写不行,编译不过去.
现在只差这个ID号了.谢谢了.
insert into tbl_basic_inter_entity_apps(id,centitycode,centityname,ilevel)
select pro_basic_inter_sequence_apps.nextval,
centitycode,casstypecode,
nvl(ire,1) from noahark.asstype_apps;--编译的时候,pro_basic_inter_sequence_apps序列是不存在的
所以汇报错误
序列最好先创建
v_id tbl_basic_inter_entity_apps.centitycode%type;
v_entitycode tbl_basic_inter_entity_apps.centitycode%type;
v_entitytypename tbl_basic_inter_entity_apps.centityname%type;
v_bisend tbl_basic_inter_entity_apps.bisend%type;
--创建序列
create or replace procedure Pro_basic_inter_SEQUENCE_apps as
v_sql varchar(200);
begin
v_sql:='create sequence seq_name minvalue 1 maxvalue 9999999 start with 1increment by 1';
execute immediate v_sql;
end pro_basic_inter_SEQUENCE_apps;
exec pro_basic_inter_SEQUENCE_apps;
--创建游标
cursor c1 is
select pro_basic_inter_sequence_apps.nextval,centitycode,casstypecode,ire from noahark.asstype_apps;
beginopen c1;
for i in 1..4 loop
fetch c1 into v_id,v_entitycode,v_entitytypename,v_bisend;
if v_bisend is null then
v_bisend := 1;
end if;
insert into tbl_basic_inter_entity_apps(id,centitycode,centityname,ilevel) values(v_id,v_entitycode,v_entitytypename,v_bisend);
COMMIT;
end loop;
close c1;
end;这个是我用游标写的存储过程,麻烦你帮我看看是哪里的错误?谢谢了
v_sql varchar(200);
begin
v_sql:='create sequence seq_name minvalue 1 maxvalue 9999999 start with 1increment by 1';
execute immediate v_sql;
end pro_basic_inter_SEQUENCE_apps;
--创建存储过程exec pro_basic_inter_SEQUENCE_apps;
--执行
insert into tbl_basic_inter_entity_apps(id,centitycode,centityname,ilevel)
select seq_name.nextval,
centitycode,casstypecode,
nvl(ire,1) from noahark.asstype_apps;
--应该就是你要的结果了吧
begin
insert into tbl_basic_inter_entity_apps(id,centitycode,centityname,ilevel)
select seq_name.nextval,
centitycode,casstypecode,
nvl(ire,1) from noahark.asstype_apps;end;
--如果你想用存储过程,这样写应该就可以了