提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
Oracle 动态SQL代码块
前言
提示:这里可以添加本文要记录的大概内容:
代码块中,是不能直接运行DDL语句的,如果要运行DDL(数据操作语言,包含create、alter、drop、truncate等)这种类型的语句,就必须要使用动态SQL的方法。
**动态SQL:**使用动态方式,可以执行任何类型的SQL语句。
提示:以下是本篇文章正文内容,下面案例可供参考
基础语法
通过使用execute immediate
来声明SQL语句,需要用单引号将要执行的语句引起来。示例如下:
execute immediate 'sql执行语句'; --给SQL语句加上单引号
示例1:
create table emp_112 as select * from emp; --复制建表
select * from emp_112;
---------------------------------------------
declare
begin
execute immediate 'truncate table emp_112'; --清空表emp_112
end;
----------------------------------------------
select * from emp_112; --清空之后查看表格
示例2:指定清空数据表
如果变量是使用在DDL语句中,那么需要使用 字符串 拼接的方式,来拼接整个sql语句。
示例代码如下:
declare
tb varchar2(20);
begin
tb:='&表名';
execute immediate 'TRUNCATE table '||tb; --注意空格问题
commit;
end;
注:在进行字符串拼接时,需要注意单词之间的空格问题
示例3:批量修改表字段长度
表字段信息表:user_tab_columns
表信息表:user_tables
1.查看用户拥有的表信息 user_tables
select * from user_tables;
2.查看用户拥有的表的字段信息 user_tab_columns
select * from user_tab_columns;
3.批量修改表字段信息
declare
cursor m is --定义一个游标,用于查找需要修改的表字段信息
select ul.TABLE_NAME,ul.COLUMN_NAME,ul.DATA_TYPE,ul.DATA_LENGTH
from user_tables u
inner join user_tab_columns ul
on u.TABLE_NAME = ul.TABLE_NAME
and u.PARTITIONED = 'NO' --分区表的数据类型不能修改---取PARTITIONED='NO'的表;
where ul.DATA_TYPE = 'VARCHAR2'
and ul.DATA_LENGTH <200;
begin
for i in m loop --利用for循环,逐条读取需要修改的表和表字段信息
--dbms_output.put_line('alter table '||i.table_name||' modify '||i.column_name||' varchar2(200)'); --打印修改语句
execute immediate
'alter table '||i.TABLE_NAME||' modify '||i.COLUMN_NAME||' VARCHAR2(200)'; --执行表修改语句
end loop;
end;
实例3:批量修改表名
declare
cursor m is select TABLE_NAME from user_tables where TABLE_NAME like '%20230822%'; 模糊查询需要修改的表名
begin
for i in m loop
execute immediate
'alter table '||i.TABLE_NAME||' rename to '||regexp_replace(i.TABLE_NAME,'20230822',''); --正则匹配替换的方式修改表名
end loop;
end;
实例4:如何批量处理掉备份的表
declare
cursor m is select TABLE_NAME from user_tables where TABLE_NAME like '%20230822%'; --模糊查询需要删除的表名
begin
for i in m loop
execute immediate
'drop table '||i.TABLE_NAME; --使用drop的方式删除表名
end loop;
end;