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!!请帮忙啊!!
--第一种办法使用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;--后面的一致
....