cx_Oracle（python库）执行 Oracle 的存储过程或函数详解

文章	评论	标签
279	280	466

使用 python 的库 cx_Oracle 如何调用执行存储过程或函数呢？

思考这个问题的起源是我在研究使用 python 来执行一个拍摄 awr 快照的存储过程。

exec dbms_workload_repository.create_snapshot

在 PL/SQL Developer 的命令行窗口执行一下就可以拍摄一张 awr 报告了。那么回到 python 中，我们使用 cx_Oracle 库的时候，又该如何执行呢？

首先假定你已经知道 cx_Oracle 的基础使用，连接数据库，创建连接和创建游标 cursor。之后我们可以通过游标来进行操作。在 oracle 中，我们可以使用 exec 来执行存储过程等，但是在外部，我们就得用 call 来执行，且存储过程结尾一定要有小括号。比如：

sql = '''call dbms_workload_repository.create_snapshot()'''
cursor.execute(sql)

这样经过测试是可以执行的。除此之外，我们还找到库中的其他专门处理这项事务的命令：callproc() 和 callfunc()。

PL/SQL 是 Oracle 对 SQL 的过程语言扩展。PL/SQL 过程和函数在数据库中存储和运行。使用 PL/SQL 允许所有数据库应用程序重用逻辑，无论应用程序以何种方式访问数据库。许多与数据相关的操作在 PL/SQL 中的执行速度比将数据提取到一个程序中（例如，Python）然后再进行处理的速度快。

首先我们来看看存储过程如何调用，我们有如下存储过程：

create or replace procedure
myproc(v1_p in number, v2_p out number) as
begin
   v2_p := v1_p * 2;
end;

这个存储过程有一个入参有一个出参。则可以编写对应示例的 python 脚本如下（连接信息请换成你自己的）：

import cx_Oracle
con = cx_Oracle.connect('pythonhol/welcome@127.0.0.1/orcl')
cur = con.cursor()
# 创建一个 cx_Oracle 规定的变量类型
myvar = cur.var(cx_Oracle.NUMBER)
# 使用 callproc 方法调用，第一个参数为存储过程名，不用括号；第二个参数传入元组，分别传入入参和出参
cur.callproc('myproc', (123, myvar))
# 出参必须使用 .getvalue() 获取结果值
print(myvar.getvalue())
cur.close()
con.close()

很简单吧？那么如果是一个函数过程呢？我们来看一下这个：

create or replace function             
myfunc(d_p in varchar2, i_p in number) return number as              
begin              
  insert into ptab (mydata, myid) values (d_p, i_p);              
  return (i_p * 2);              
end;

这个函数定义了两个入参，一个返回值。则对应 python 脚本如下：

import cx_Oracle
con = cx_Oracle.connect('pythonhol/welcome@127.0.0.1/orcl')
cur = con.cursor()
# 使用 callfunc 方法调用，第一个参数为函数名，不用括号；第二个参数为返回值类型；第三个参数为元组，为所有入参
# 调用后的返回值保存到变量 res 中
res = cur.callfunc('myfunc', cx_Oracle.NUMBER, ('abc', 2))
# 可以直接打印返回值，直接用
print res
cur.close()
con.close()

对比这两类代码使用方法，我也明白了我应该如何调用我的函数或过程了。

首先我们看一下系统里定义的默认函数如下：

  -- *********************************** --
  --  DBMS_WORKLOAD_REPOSITORY Routines
  -- *********************************** --

  --
  -- create_snapshot()
  --   Creates a snapshot in the workload repository.
  --
  --   This routine will come in two forms: procedure and function.
  --   The function returns the snap_id for the snapshot just taken.
  --
  -- Input arguments:
  --   flush_level               - flush level for the snapshot:
  --                               either 'TYPICAL' or 'ALL'
  --
  -- Returns:
  --   NUMBER                    - snap_id for snapshot just taken.
  --

  PROCEDURE create_snapshot(flush_level IN VARCHAR2 DEFAULT 'TYPICAL'
                            );

  FUNCTION create_snapshot(flush_level IN VARCHAR2 DEFAULT 'TYPICAL'
                           )  RETURN NUMBER;

可以看到，这里定义了一个存储过程，一个函数，都是同名的。存储过程没有返回值，执行就好了。而函数入参跟存储过程是一样的，但有一个 NUMBER 类型的返回值，这个返回值会返回最新拍摄的快照 id 即 snap_id 的值。可以看到定义的存储过程或函数是有默认值的，我测试了一下，如果传入空值，是不允许的会报错的，报错内容为 ORA-20100: Invalid flush level. Level must be 'TYPICAL' or 'ALL'.。因此即使有定义默认值，你还是要传入，这里传入默认值 'TYPICAL' 即可。

于是我写了个调用函数如下（这里我们已经连接上 oracle 数据库了，这部分代码省略）：

def exec_create_snapshot():
    """执行生成快照"""
    func_name = '''dbms_workload_repository.create_snapshot'''
    try:
        # 执行调用 function，若为 procedure，则使用 callproc 命令
        res = cursor.callfunc(func_name, cx_Oracle.NUMBER, ('TYPICAL',))
        print("执行生成快照成功！生成的最新快照 ID：%s" % int(res))
    except Exception as e:
        print("执行生成快照报错，报错内容：%s" % e)

学以致用，真的非常棒！你也来练一下吧~

部分内容参考资料：https://www.oracle.com/technetwork/cn/tutorials/python-155134-zhs.html

此外，因为最近有这个需求，获取 Oracle 的 AWR 报告，要求多节点，多份，批量，手动 sql 比较麻烦，特开通了一个项目做个批量化脚本实现（最终效果 30 秒 - 1 分钟即可实现批量获取，超级快！）。里面就包含了这部分代码（虽然最后发现做批量取出不需要）。

完整项目源码如下（顺手发布一个自己的项目作品）：

此处内容需要评论回复后方可阅读

cx_Oracle（python库） 执行 Oracle 的存储过程或函数详解

小胖狐

cx_Oracle（python库）执行 Oracle 的存储过程或函数详解