oracle存储过程借助utl,Oracle存储过程实例入门分享讲解

Oracle存储过程实例入门分享讲解

Oracle存储过程实例入门分享讲解

上一篇我们已经介绍了如何通过ODBC导入表数据ODBC导入数据，本文则通过导入的这张表分享一下存储过程入门

存储过程和函数的区别就是，函数只能即用即配，适合简短配置的，但不适合批量操作和后台写入等，这是我对存储过程的简单理解，我认为Oracle存储过程和函数就类似于java，python等面向对象语言的类和函数

我们要处理一批表数据，把excel里的数据根据表名写成一个个的sql脚本，实现这个过程其实很简单，用shell，java的io，oracle的函数等等都可以，但本文用一个更能体现逼格的方法：存储过程来写，拿一张表来举例

这是表的数据，懒得截图了，用的上一个博客的图，但数据还是这个数据

我们要读取这个表TB2实现成这样的脚本

INSERT OVERWRITE TABLE OWNER1.TABLE1 PARTITION ( rfq='#RQ')SELECT  COL1,COL2,...,GETDATE() COLN

FROM OWNER2.TABLE1 where rfq='#RQ';

现在开始写存储过程

首先第一步是声明变量

DECLARE                --声明变量的关键字

colname VARCHAR2(500);        --变量以及数据类型

bzbm    VARCHAR2(300);

m       number;

m2      number;

ybm    VARCHAR2(300);

fhandle utl_file.file_type;       --fhandle是文件自定义名，utl_file.file_type是oracleutl_file包文件的属性，是关键字

CURSOR C_SAL IS

SELECT DISTINCT YBM 　　FROM tb2;    --CURSOR XXX IS  SELECT * FROM TABLE_NAME;   把选择出来的值赋予XXX

BEGIN

...

END    --BEGIN和END是脚本过程，流程关键字必备

第二步：开始写入脚本正文

BEGIN

fhandle := utl_file.fopen('EXP_DIR', 'XXX.sql', 'w',32767);   --:=是赋值，fopen可理解为java的io

FOR V_SAL IN C_SAL LOOP               --每个语言都有的for循环：FOR xxx in yyy LOOP

SELECT distinct bzbm 　INTO bzbm　FROM tb2 WHERE YBM = V_SAL.YBM;  --SELECT XXX INTO Y FROM  赋值Y

utl_file.PUT(fhandle, 'INSERT OVERWRITE TABLE OWNER1.'); --目标主题名,PUT是在这个文件按顺序写入东西

utl_file.PUT(fhandle, bzbm);

utl_file.PUT(fhandle, ' PARTITION ( rfq=''20170000'')SELECT  ');

SELECT max(yxh) into m from tb2 WHERE YBM = V_SAL.YBM;

FOR i IN 1 .. m LOOP

SELECT yzdm       into colname       from tb2       WHERE YBM = V_SAL.YBM         and yxh = i;

if i = 1 then

utl_file.PUT(fhandle, colname);

else

utl_file.PUT(fhandle, ',' || colname);

end if;

end loop;                        --在存储过程中每个if判断和每个loop循环都要end，类似于VB语言

utl_file.new_line( fhandle );      --new_line 增加行终止符，也就是换行的意思

utl_file.PUT(fhandle, '  FROM OWNER2.');                       ---源主题名

SELECT distinct jcztbm 　INTO ybm　FROM tb2 WHERE YBM = V_SAL.YBM;

utl_file.PUT(fhandle, YBM);                                  ---源表名

utl_file.PUT_LINE(fhandle, ' where rfq=''20170000'';'); --初始化分区

END LOOP;

utl_file.fclose(fhandle);    --记得fclose文件

END;

至此一个利用utl_file包写的存储过程就搞定了，

fhandle := utl_file.fopen('EXP_DIR', 'XXX.sql', 'w',32767);

这个是把文件输出到EXP_DIR文件夹里，所以在运行该存储过程之前记得先创建该文件夹，并且赋予权限，命令如下

create directory exp_dir as 'home/oracle/directory'; --oracle安装windows环境下可以用d:/directory等grant create any directory to public;

Oracle存储过程实例入门分享讲解相关教程