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

使用 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 分钟即可实现批量获取,超级快!)。里面就包含了这部分代码(虽然最后发现做批量取出不需要)。

完整项目源码如下(顺手发布一个自己的项目作品):

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

评论区
头像
    头像
    gao70
      

    感谢分享,非常有用

      头像
      gao70
        
    头像
    学习
      

    头像
    L.Y.
      

    学习了,解决问题,棒

    头像
    phillip
      

    学习了,解决了我很多问题

    头像
    phillip
      

    学习了,解决了我很多问题

    头像
    nihao
      

    头像
    果粒橙也能喝醉
      

    欸,回复完也看不了欸

    头像
    果粒橙也能喝醉
      

    学习了,解决了我很多问题,顺利下班,谢谢