首页 » shell » Shell调用Oracle存储过程解决方案

Shell调用Oracle存储过程解决方案

 

一.导读
在做数据移行的时候,业务部分单字段大字节数据需要使用oracle的存储过程进行导出CSV格式,这部分用到了shell处理,今天将一些心得分享给大家。
二.实验环境
Oracle 11g Release 2 (11.2)
三.要点解析
Sqlplus里调用存储过程有exec和call两种方式,下面我们来利用shell来对这两种方式进行简单分析。
1.使用方法
exec方式:

exec pro_name(参数1,参数2,参数n);

call方式:

call pro_name(参数1,参数2,参数n);

2.区别分析
(1).exec只能在sqlplus中使用,属于sqlplus中的命令,当调用的存储过程没有参数的时候既可以直接调用也可以带上括号调用。
例:exec pro_name 或者exec pro_name()
(2).call属于SQL命令,使用时不局限于sqlplus中使用,调用时必须带上括号。
例:call pro_name()
四.案例演示
1.Shell测调用:

sqlplus / as sysdba <<EOF
exec PRO_GETNAME();
exit;
EOF

2.存储过程(以生成CSV为例):

CREATE OR REPLACE PROCEDURE PRO_GETNAME AS
  P_QUERY VARCHAR2(4000);
  P_OUTPUT UTL.FILE.FILE_TYPE;
  P_CURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
  P_COL NUMBER:=0;
  P_COLVALUE VARCHAR2(4000);
  P_TAB DBMS_SQL.DESC_TAB2;
  P_SEPARATOR VARCHAR2(1);
  P_STATUS INTEGER;
BEGIN
  P_QUERY := 'SELECT id,name FROM XIANGQUBA_USER';
  --打开文件
  P_OUTPUT:=UTL_FILE.FOPEN('DIR_USER_PATH', 'xiangquba_user.csv','W', '32767');
  --解析并执行SQL语句
  DBMS_SQL.PARSE(P_CURSOR, P_QUERY,DBMS_SQL.NATIVE);
  DBMS_SQL.DESCRIBE_COLUMNS2(P_CURSOR, P_COL,P_TAB);
  --遍历表头
  FOR I IN 1 .. P_COL LOOP
    UTL_FILE.PUT(P_OUTPUT, P_SEPARATOR||'"'|| P_TAB(I).COL_NAME||'"');
    DBMS_SQL.DEFINE_COLUMN(P_CURSOR,I, P_COLVALUE,32767);
    P_SEPARATOR:= ',';
  END LOOP;
  UTL_FILE.NEW_LINE(P_OUTPUT);
  --执行QUERY
  P_STATUS:=DBMS_SQL.EXECUTE(P_CURSOR);
  --遍历表体
  WHILE (DBMS_SQL.FETCH_ROWS(P_CURSOR)>0) LOOP
    P_SEPARATOR:= '';
    FOR I IN 1 .. P_COL LOOP
      DBMS_SQL.COLUMN_VALUE(P_COL,I, P_COLVALUE);
      UTL_FILE.PUT(P_OUTPUT, P_SEPARATOR||'"'||TRIM(BOTH '' FROM REPLACE(P_COLVALUE,'"','""'))||'"');
      P_SEPARATOR :=',';
      UTL_FILE.NEW_LINE(P_OUTPUT);
    END LOOP;
  END LOOP;
  --关闭游标
  DBMS_SQL.CLOSE_CURSOR(P_CURSOR);
  --关闭文件
  UTL_FILE.FCLOSE(P_OUTPUT);
    EXCEPTION
      WHEN OTHERS THEN
        IF DBMS_SQL.IS_OPEN(P_CURSOR) THEN
          DBMS_SQL.CLOSE_CURSOR(P_CURSOR);  
        END IF;
  END PRO_GETNAME;

五.参照文献
https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/toc.htm

原文链接:Shell调用Oracle存储过程解决方案,转载请注明来源!

0