Oracle游标及应用实例(二)
name,procedurename,create_time) values(
SEQ_SPLIT_TABLE_HISTORY.NEXTVAL,TAB_NAME,'CREATE TABLE'||TAB_NAME,sysdate());
end loop;
end;
www.2cto.com
6、游标实例三
Sql代码
create or replace procedure SP_PKEY_AUTO_INCREMENT
as
--游标：获取所有Sequence的名称
cursor mycur is select SEQUENCE_NAME from user_sequences;
SM mycur%rowtype;
--表名
TABNAME varchar2(32);
--主键字段名
PKEY varchar2(20);
--游标：获取主键名称
cursor mycur_columns is select COLUMN_NAME from user_cons_columns cu,user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = TABNAME;
coulumn mycur_columns%rowtype;
begin
for SM in mycur loop
--表名
TABNAME:=REPLACE(SM.SEQUENCE_NAME,'SEQ_','');
--主键字段名
for coulumn in mycur_columns loop
PKEY := coulumn.COLUMN_NAME;
end loop;
--组合
dbms_output.put_line('ALTER TABLE '||TABNAME||' MODIFY COLUMN '||PKEY||' INT NOT NULL AUTO_INCREMENT;');
end loop;
end;
PS：
www.2cto.com
7、存储过程执行动态脚本：
Sql代码
CREATE OR REPLACE PROCEDURE SP_TRUN_TABLE_DATA_BY_NAME
(TAB_NAME in varchar2)
AS
SQLSTR varchar2(255);
BEGIN
SQLSTR := 'TRUNCATE TABLE '|| TAB_NAME;
execute immediate SQLSTR;
insert into split_table_history(int_id,split_name,procedurename,create_time) values(
SEQ_SPLIT_TABLE_HISTORY.NEXTVAL,TAB_NAME,'TRUNCATE TABLE'||TAB_NAME,sysdate());
END;