一个简单的oracle存储过程
发布时间：2020-12-12 16:06:40 所属栏目：百科 来源：网络整理
导读：在写这个存储过程的时候自己只是对存储过程有一个简答的编程理解，觉得应该和大多数编程语言没什么区别吧，就试试喽 先说说需求吧！ 1、首先我需要获取表空间中的所有表名，并动态的去搜索遍历，所以不得不用到了存储过程（之前只是看过一些简单的存储过程，
|
在写这个存储过程的时候自己只是对存储过程有一个简答的编程理解，觉得应该和大多数编程语言没什么区别吧，就试试喽 先说说需求吧！ 1、首先我需要获取表空间中的所有表名，并动态的去搜索遍历，所以不得不用到了存储过程（之前只是看过一些简单的存储过程，没自己写过。。。） 2、在获得所有表名以后动态的遍历表中的所有字段(即所有的列)，搜素字段中的值是否包含中文的括号（'）'，‘（’）
既然决定了那就开始动手了!!! 首先,我要找到基本的存储过程格式（找了好久，为嘛这么坑....） create or replace procedure 过程名
as
变量名 变量类型;
-- 或者需要默认值的
变量名 变量类型 := 值;
begin
end 过程名;
实例： create or replace procedure searchClusInTableSpace
as
count11 number;
c_sql_pro1 varchar2(300);
lsTemp varchar2(50):='-1';
begin
end searchClusInTableSpace;
第二部，既然要遍历，就肯定要用到循环，那么oracle中的循环什么样呢？怎么办？当然是找了。。。 create or replace procedure 过程名
as
变量名 变量类型;
-- 或者需要默认值的
变量名 变量类型 := 值;
begin
for 变量名(用于循环的) in 集合
loop
基本操作，自己定义
end loop;
end 过程名;
实例： create or replace procedure searchClusInTableSpace
as
count11 number;
c_sql_pro1 varchar2(300);
lsTemp varchar2(50):='-1';
begin
for tb1 in (select
kk.table_name from user_tab_columns kk)
loop
... ...
end loop;
end searchClusInTableSpace;
第三部，我的内容因为要遍历到各表中的各个字段的内容，所以就要双层遍历了。。（真麻烦啊,唉。。。） create or replace procedure 过程名
as
变量名 变量类型;
-- 或者需要默认值的
变量名 变量类型 := 值;
begin
for 变量名1(用于循环的) in 集合1
loop
for 变量名2 in 集合2
loop
基本操作，自己定义
end loop;
end loop;
end 过程名;
实例： create or replace procedure searchClusInTableSpace
as
count11 number;
c_sql_pro1 varchar2(300);
lsTemp varchar2(50):='-1';
begin
for tb1 in (select
kk.table_name from user_tab_columns kk)
loop
for tbColumn in (select
COLUMN_NAME from user_tab_columns where table_name =tb1.table_name)
loop
... ...
end loop;
end loop;
end searchClusInTableSpace;
基本差不多了。。。。。。。。。。。。。。。。。。。。。。可是问题还是躲不过去，在代码中有一些想忽略的问题，让程序正常执行，怎么办？简单，做过程序的都知道-----异常处理嘛！！！！！！ create or replace procedure 过程名
as
变量名 变量类型;
-- 或者需要默认值的
变量名 变量类型 := 值;
begin
for 变量名1(用于循环的) in 集合1
loop
for 变量名2 in 集合2
loop
begin
基本操作，自己定义
exception
when 异常类型 then
dbms_output.put_line(打印信息);
--其他未知或预料不到的异常可以用others代替
when others then
DBMS_OUTPUT.put_line(打印信息);
end;
end loop;
end loop;
end 过程名;
实例： create or replace procedure 过程名
as
变量名 变量类型;
-- 或者需要默认值的
变量名 变量类型 := 值;
begin
for 变量名1(用于循环的) in 集合1
loop
for 变量名2 in 集合2
loop
begin
基本操作，自己定义
exception
--类型不匹配
when rowtype_mismatch then
dbms_output.put_line('type error!!!');
when others then
--截取报错信息的前60个字符
DBMS_OUTPUT.put_line('sqlerrm : ' ||substr(sqlerrm,1,60));
end;
end loop;
end loop;
end 过程名;
最后的最后，过程创建成功了，怎么跑起来嘞？泪奔。。。。。。还是要百度！ begin
searchClusInTableSpace;
end;
好了，下面是我写的过程，遍历所有表的所有字段，查找字段内容是否包含不合法的中文字符“（）”。。
--存储过程
--先获取所有的表名
--循环表名遍历表中字段
--select语句模糊匹配各表的各字段中包含特殊字符的语句个数
create or replace procedure searchClusInTableSpace
as
count11 number;
count12 number;
c_sql_pro1 varchar2(300);
c_sql_pro2 varchar2(300);
tb1 varchar2(50);
tbColumn varchar2(50);
lsTemp varchar2(50):='-1';
begin
for tb1 in (select
kk.table_name from user_tab_columns kk)
loop
for tbColumn in (select
COLUMN_NAME from user_tab_columns where table_name =tb1.table_name)
loop
begin
count11:=0;
count12:=0;
c_sql_pro1:='select count(*) from '||tb1.table_name||' where '||tbColumn.COLUMN_NAME||' like '||''''||'%）%'||'''' ;
execute immediate c_sql_pro1 into count11;
c_sql_pro2:='select count(*) from '||tb1.table_name||' where '||tbColumn.COLUMN_NAME||' like '||''''||'%（%'||'''';
execute immediate c_sql_pro2 into count12;
if (count11>0 or count12>0) and lsTemp != tb1.table_name then
dbms_output.put_line(tb1.table_name||','||tbColumn.COLUMN_NAME);
lsTemp:=tb1.table_name;
end if;
exception
when rowtype_mismatch then
dbms_output.put_line('type error!!!');
when others then
DBMS_OUTPUT.put_line('sqlerrm : ' ||substr(sqlerrm,60));
end;
end loop;
end loop;
end searchClusInTableSpace; （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |