介绍点相关知识:desc col; desc tab; select * from col where tname='YOUR_TNAME'; --表名一定要大写。可以使用编制存贮过程来处理。
--写一个对所有ID字段批量增加序列和触发器的存储过程 CREATE OR REPLACE PROCEDURE p_batch AUTHID CURRENT_USER IS c SYS_REFCURSOR; v_tablename VARCHAR2(30); v_colname VARCHAR2(30); BEGIN OPEN c FOR 'select table_name,column_name from user_tab_columns'; LOOP FETCH c INTO v_tablename, v_colname; EXIT WHEN c%NOTFOUND; IF v_colname = upper('id') THEN --创建序列 BEGIN EXECUTE IMMEDIATE 'drop sequence seq_' || v_tablename; EXCEPTION WHEN OTHERS THEN NULL; END; EXECUTE IMMEDIATE 'create sequence seq_' || v_tablename || ' start with 1 increment by 1'; --创建触发器 EXECUTE IMMEDIATE 'create or replace trigger tr_' || v_tablename || ' before insert on ' || v_tablename || ' for each row begin select seq_' || v_tablename || '.nextval into :new.id from dual; end;'; END IF; END LOOP; END;
Warning: PL/SQL procedure successfully completed with compilation errors 报这个错误
show error查看错误, 动态sql中,将最后一个分号去掉:... EXECUTE IMMEDIATE 'create or replace trigger tr_' || v_tablename || ' before insert on ' || v_tablename || ' for each row begin select seq_' || v_tablename || '.nextval into :new.id from dual; end';--这里的分号去掉了 ...
--进入SQL*Plus或者pl/sql developer工具命令窗口 SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 5月 24 22:37:32 2011Copyright (c) 1982, 2005, Oracle. All rights reserved. 连接到: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> --创建存储过程 SQL> CREATE OR REPLACE PROCEDURE p_batch AUTHID CURRENT_USER IS 2 c SYS_REFCURSOR; 3 v_tablename VARCHAR2(30); 4 v_colname VARCHAR2(30); 5 BEGIN 6 OPEN c FOR 'select table_name,column_name from user_tab_columns'; 7 LOOP 8 FETCH c 9 INTO v_tablename, v_colname; 10 EXIT WHEN c%NOTFOUND; 11 IF v_colname = upper('id') THEN 12 --创建序列 13 BEGIN 14 EXECUTE IMMEDIATE 'drop sequence seq_' || v_tablename; 15 EXCEPTION 16 WHEN OTHERS THEN 17 NULL; 18 END; 19 EXECUTE IMMEDIATE 'create sequence seq_' || v_tablename || 20 ' start with 1 increment by 1'; 21 --创建触发器 22 EXECUTE IMMEDIATE 'create or replace trigger tr_' || v_tablename || 23 ' before insert on ' || v_tablename || 24 ' for each row 25 begin 26 select seq_' || v_tablename || 27 '.nextval into :new.id from dual; 28 end;'; 29 END IF; 30 END LOOP; 31 END; 32 /过程已创建。SQL> --执行存储过程 SQL> exec p_batch;PL/SQL 过程已成功完成。SQL> --当前用户下所有ID列的表都建立完毕了
desc tab;
select * from col where tname='YOUR_TNAME'; --表名一定要大写。可以使用编制存贮过程来处理。
CREATE OR REPLACE PROCEDURE p_batch AUTHID CURRENT_USER IS
c SYS_REFCURSOR;
v_tablename VARCHAR2(30);
v_colname VARCHAR2(30);
BEGIN
OPEN c FOR 'select table_name,column_name from user_tab_columns';
LOOP
FETCH c
INTO v_tablename, v_colname;
EXIT WHEN c%NOTFOUND;
IF v_colname = upper('id') THEN
--创建序列
BEGIN
EXECUTE IMMEDIATE 'drop sequence seq_' || v_tablename;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
EXECUTE IMMEDIATE 'create sequence seq_' || v_tablename ||
' start with 1 increment by 1';
--创建触发器
EXECUTE IMMEDIATE 'create or replace trigger tr_' || v_tablename ||
' before insert on ' || v_tablename ||
' for each row
begin
select seq_' || v_tablename ||
'.nextval into :new.id from dual;
end;';
END IF;
END LOOP;
END;
报这个错误
动态sql中,将最后一个分号去掉:...
EXECUTE IMMEDIATE 'create or replace trigger tr_' || v_tablename ||
' before insert on ' || v_tablename ||
' for each row
begin
select seq_' || v_tablename ||
'.nextval into :new.id from dual;
end';--这里的分号去掉了
...
SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 5月 24 22:37:32 2011Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> --创建存储过程
SQL> CREATE OR REPLACE PROCEDURE p_batch AUTHID CURRENT_USER IS
2 c SYS_REFCURSOR;
3 v_tablename VARCHAR2(30);
4 v_colname VARCHAR2(30);
5 BEGIN
6 OPEN c FOR 'select table_name,column_name from user_tab_columns';
7 LOOP
8 FETCH c
9 INTO v_tablename, v_colname;
10 EXIT WHEN c%NOTFOUND;
11 IF v_colname = upper('id') THEN
12 --创建序列
13 BEGIN
14 EXECUTE IMMEDIATE 'drop sequence seq_' || v_tablename;
15 EXCEPTION
16 WHEN OTHERS THEN
17 NULL;
18 END;
19 EXECUTE IMMEDIATE 'create sequence seq_' || v_tablename ||
20 ' start with 1 increment by 1';
21 --创建触发器
22 EXECUTE IMMEDIATE 'create or replace trigger tr_' || v_tablename ||
23 ' before insert on ' || v_tablename ||
24 ' for each row
25 begin
26 select seq_' || v_tablename ||
27 '.nextval into :new.id from dual;
28 end;';
29 END IF;
30 END LOOP;
31 END;
32 /过程已创建。SQL> --执行存储过程
SQL> exec p_batch;PL/SQL 过程已成功完成。SQL> --当前用户下所有ID列的表都建立完毕了