存储过程支持ddl的sql,
应该是可以的
应该是可以的
解决方案 »
- 请教应用程序通过odbc连接oracle数据库读取数据的问题!
- xml编码格式
- JDBC报错:“无法从套接字读取更多的数据!” 请高人指点!
- 远程oracle数据库能ping通但配置net时提示“测试失败-操作超时”?请教问题何在?
- 急!!!小女子菜,请各位高手帮忙看看这个一级菜的问题!
- 急问:在ORACLE数据库的视图v$archived_log里,为什么会出现stby1这行记录
- mssql-->oracle 9
- 各位网友请帮忙,有关trigger的问题,在线等。
- 如何查
- 请问如下条件下怎么写sql
- 请问Oracle是否支持将XML保存至某一列中,并且可以用XQuery查询?
- oracle可以生成不重复的数字吗?
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;
--如果你想用存储过程,这样写应该就可以了