PL/SQL脚本小例子

--删除所有ncr表的数据

begin
for x in (select table_name from user_tables where table_name like 'NCR_%' ) loop
execute immediate 'delete ' || x.table_name ; 
commit;
end loop ; 
end ;



--给所有的NCR表加字段
--注意空格

begin
for x in (select table_name from user_tables where table_name like 'NCR_%' ) loop
execute immediate  'ALTER TABLE '||  x.table_name || ' ADD LDSID  VARCHAR2(100)';
commit;
end loop ; 
end ;



--修改ncr的字段类型

begin
for x in (select table_name from user_tables where table_name like 'NCR_%' ) loop
execute immediate  'alter table '||  x.table_name || ' modify (USERID varchar2(100))';
commit;
end loop ; 
end ;



-- 循环修改address的值

declare 
numStr VARCHAR2(100) := 'hehe';
numStr2 number;
begin
for x in (select address  from dept  ) loop
select instr(x.address,'8080') into numStr2 from dual;
select substr(x.address,numStr2) into numStr from dual;
UPDATE dept SET address = 'http://192.168.88.32:'||numStr where address = x.address;
commit;
end loop ; 
end ;



--循环新建字段

declare
  aa integer :=1;
begin
  while aa<51
  loop
  execute immediate  ' ALTER TABLE  CAL_CREDITREPORTCINFO  ADD '||  ' CCRCI_'||aa  || ' VARCHAR2(100)';
  aa:=aa+1;
  end loop;
end;