在Python中调用带有IN, OUT, IN/OUT参数和返回值的Oracle DB函数

Question

我已经给出了一个函数，可以称为以下，即使用'SQL Oracle开发人员':

--set serveroutput on size 2000;
declare
theId VARCHAR(20);
theKey VARCHAR(20) := '';
theName VARCHAR(20) := '';
theEmail VARCHAR(20) := '';
theDob DATE := '';
theVal NUMBER;
begin
theId := Package.FunctionName(
theKey,
theName,
theEmail,
theDob,
theVal
);
--DBMS_OUTPUT.PUT_LINE(theId);
--DBMS_OUTPUT.PUT_LINE(theKey);
--DBMS_OUTPUT.PUT_LINE(theVal);
exception
when others then
RAISE;
end;

现在，我应该在Python中使用cx_Oracle调用这个函数。

为了做到这一点，我试过下面的方法，但不能让它工作:

cursor = connection.cursor()
function = '''
BEGIN
theKey := inout_theKey;
theName := in_theName;
theEmail := in_theEmail;
theDob := in_theDob;
theVal := out_theVal;
theId := Package.FunctionName(theKey, theName theEmail, theDob, theVal)
:out_theId := theId;
:inout_theKey := theKey;
:out_theVal := theVal;
END;'''
out_theId = cursor.var(str)
out_theVal = cursor.var(int)
inout_theKey = cursor.var(str)
inout_theKey.setvalue(0, '')
cursor.execute(
function, 
inout_theKey=inout_theKey, 
in_theName='', 
in_theEmail='', 
in_theDob='', 
out_theId=out_theId, 
out_theVal=out_theVal)
_logger.debug(out_theId.getvalue())

失败，报错:

cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number

也许有人可以给我指出正确的方向，我的绑定是不正确的?

---

为了它的价值，我还尝试添加DECLARE语句到函数，即

function = '''
DECLARE 
theId VARCHAR(20) := out_theId;  
theKey VARCHAR(20) := inout_ttheKey; 
theName VARCHAR(20) := in_theName; 
theEmail VARCHAR(20) := in_theEmail; 
theDob DATE := in_theDob; 
theVal NUMBER := out_theVal;  
BEGIN  
theId := Package.FunctionName(theKey, theName, theEmail, theDob, theVal); 
:out_theId := theId; 
:inout_theKey := theKey; 
:out_theVal := theVal; 
END;'''

仍然为我提供相同的结果(illegal variable name/number)。

通过cx_Oracle连接数据库正在工作(与其他几个sql语句验证)。

Answer 1

显然有一个相当简单的解决方案:

cursor = connection.cursor()
function = 'Package.FunctionName'
inout_theKey = ''
in_theName = ''
in_theEmail = ''
in_theDob = ''
out_theVal = cursor.var(int)  # default type is str, else define the type like this
out_theId = cursor.callfunc(function, str, [
inout_theKey, 
in_theName, 
in_theEmail, 
in_theDob, 
out_theVal
])
_logger.debug(out_theId)
_logger.debug(inout_theKey)
_logger.debug(out_theVal)

本质上，您只需要提供函数名称(function)，预期返回值(本例中为str)以及根据callfunc调用的函数定义的in, OUT和in/OUT参数列表。

Ref: https://cx-oracle.readthedocs.io/en/latest/api_manual/cursor.html#Cursor.callfunc and https://cx-oracle.readthedocs.io/en/latest/user_guide/plsql_execution.html#plsqlfunc