从Python中的cx_Oracle PL/SQL调用返回变量 [英] Return variable from cx_Oracle PL/SQL call in Python

问题描述

我想在Python中通过cx_oracle执行Oracle PL/SQL语句.代码如下:

I want to execute an Oracle PL/SQL statement via cx_oracle in Python. Code looks like this:

db = cx_Oracle.connect(user, pass, dsn_tns)
cursor = db.cursor()

... 

sel = """
DECLARE
  c   NUMBER := 0.2;
  mn  NUMBER := 1.5;
  res NUMBER;
BEGIN
  res := c+mn/6.;
END;
"""
try:
  cursor.execute(sel) 
  print "PL/SQL successful executed ..."
except cx_Oracle.DatabaseError as e:
  err, = e.args
  print "\n".join([str(err.code),err.message,err.context])

代码正在正常运行，但是是否有机会将结果返回给Python?

The code is running without problems, but is there any chance to get the result back to Python?

推荐答案

您可以像这样将输入和输出变量绑定到块.

You can bind input and output variables to the block like so.

import cx_Oracle

SQL_BLOCK = '''
DECLARE
  v_first   NUMBER;
  v_second  NUMBER;
  v_result  NUMBER;
BEGIN
  v_first  := :i_first;   -- (1)
  v_second := :i_second;  -- (1)

  v_result := (v_first + v_second) / 2;

  :o_result := v_result;  -- (1)
END;
'''

with cx_Oracle.connect('hr/hr@xe') as db:
    cur = db.cursor()
    o_result = cur.var(cx_Oracle.NUMBER) # (2)
    cur.execute(SQL_BLOCK, i_first=23, i_second=55, o_result=o_result) # (3)
    res = o_result.getvalue()  # (4)
    print('Average of 23 and 55 is: {}'.format(res))

1. 在PL/SQL块中对输入和输出变量都使用常规绑定表示法(:)
2. 对于输出变量，请从光标(适当类型的变量)中获取一个变量
3. 在execute调用中提供输入变量的值以及(2)中的变量作为参数
4. 从输出变量中检索值

脚本应打印

Average of 23 and 55 is: 39.0

这篇关于从Python中的cx_Oracle PL/SQL调用返回变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！