CREATE OR REPLACE PROCEDURE NET_SEQUENCE_INIT
AS
STR_TABLENAME VARCHAR2(256);
STRSQL VARCHAR2(1000);
PK_FIELD_NAME VARCHAR2(50);
CURSOR CUR IS
SELECT TABLE_NAME FROM NET_SEQUENCE;
BEGIN
OPEN CUR;
LOOP
BEGIN
FETCH CUR INTO STR_TABLENAME;
--STR_TABLENAME:='TSM_TAPE';
select b.column_name INTO PK_FIELD_NAME from all_constraints a,all_cons_columns b
where
a.owner=b.owner and
a.constraint_name=b.constraint_name and
a.CONSTRAINT_TYPE ='P' and
a.owner='user1' and
a.table_name=STR_TABLENAME;
SELECT 'UPDATE NET_SEQUENCE SET SERIAL_NUMBER = (SELECT nvl(MAX('||PK_FIELD_NAME||'),0) FROM '||STR_TABLENAME||') WHERE TABLE_NAME = '''||STR_TABLENAME||'''' INTO STRSQL FROM DUAL;
EXECUTE IMMEDIATE STRSQL;
END;
EXIT WHEN CUR%NOTFOUND;
END LOOP;
CLOSE CUR;
COMMIT;
END;如果STR_TABLENAME写死了就没有问题,可是接收游标FETCH 后的值就提示not data found!!请帮忙啊!!
AS
STR_TABLENAME VARCHAR2(256);
STRSQL VARCHAR2(1000);
PK_FIELD_NAME VARCHAR2(50);
CURSOR CUR IS
SELECT TABLE_NAME FROM NET_SEQUENCE;
BEGIN
OPEN CUR;
LOOP
BEGIN
FETCH CUR INTO STR_TABLENAME;
--STR_TABLENAME:='TSM_TAPE';
select b.column_name INTO PK_FIELD_NAME from all_constraints a,all_cons_columns b
where
a.owner=b.owner and
a.constraint_name=b.constraint_name and
a.CONSTRAINT_TYPE ='P' and
a.owner='user1' and
a.table_name=STR_TABLENAME;
SELECT 'UPDATE NET_SEQUENCE SET SERIAL_NUMBER = (SELECT nvl(MAX('||PK_FIELD_NAME||'),0) FROM '||STR_TABLENAME||') WHERE TABLE_NAME = '''||STR_TABLENAME||'''' INTO STRSQL FROM DUAL;
EXECUTE IMMEDIATE STRSQL;
END;
EXIT WHEN CUR%NOTFOUND;
END LOOP;
CLOSE CUR;
COMMIT;
END;如果STR_TABLENAME写死了就没有问题,可是接收游标FETCH 后的值就提示not data found!!请帮忙啊!!
解决方案 »
- to_number(to_char(p_stat_time,'w'))得到什么值
- 帮小弟一把
- 急求一sql,谢谢
- 高分请教高手!!ORACLE数据库如何做实时备份??请不吝赐教!!
- 请问查看用户名密码怎么做呢?在线等,谢!
- oracle 有没有像mssql 中的分离附加一样的操作呀? 在线等
- 查看ORACLE数据库的触发器
- 奇怪啊奇怪,为何我取得表占用的block数总是为0
- 从网上下载的Oracle9i简体中文版(三张碟),完成后是一大堆压缩格式的文件,没有一个类似setup之类的文件,如何安装啊!急!!!
- 一个常用的操作怎样作效率最高?(用Procedure)
- 求教一个比较怪异的SQL查询问题的解决方案!谢谢大家了!
- 请问:数据库绝本文件是什么?
--第一种办法使用exception来处理
................
FETCH CUR INTO STR_TABLENAME;
begin --fetch后这边添加
select b.column_name INTO PK_FIELD_NAME
from all_constraints a,all_cons_columns b
where a.owner=b.owner
and a.constraint_name=b.constraint_name
and a.CONSTRAINT_TYPE ='P'
and a.owner='user1'
and a.table_name=STR_TABLENAME;
exception
when no_data_found then
PK_FIELD_NAME :='';
--或者可以写空操作表示什么都不做,在发生例外时所要的操作你可自己决定
end ;--后面一致
SELECT 'UPDATE .....
.................
第二种方法,判断执行的数据是否存在:
................
FETCH CUR INTO STR_TABLENAME;
--fetch后这边添加
select count(*) into is_count_
from all_constraints a,all_cons_columns b
where
a.owner=b.owner and
a.constraint_name=b.constraint_name and
a.CONSTRAINT_TYPE ='P' and
a.owner='user1' and
a.table_name=STR_TABLENAME;
if is_count_ > 0 then
select b.column_name INTO PK_FIELD_NAME from all_constraints a,all_cons_columns b
where
a.owner=b.owner and
a.constraint_name=b.constraint_name and
a.CONSTRAINT_TYPE ='P' and
a.owner='user1' and
a.table_name=STR_TABLENAME;
else PK_FIELD_NAME := '';
end if;--后面的一致
....