--删除所有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;
PL/SQL脚本小例子
最新推荐文章于 2024-06-24 09:56:11 发布