在python SQL查询中声明一个oracle变量

Question

我正在将代码从SQL Server移植到Oracle。

DECLARE @DispatchCount int,
@HeadCount int;
SELECT @DispatchCount = (
select 200 -- business query would replace sample
);
select @HeadCount = (
select 50 -- business query would replace sample 
);
select @DispatchCount / @HeadCount;

I try oracle declare syntax.

DECLARE
head_count INTEGER;
BEGIN
select 100 as DUMMY into head_count from dual;
dbms_output.put_line(head_count);
END;

我正在查询一个只读oracle 11g数据库在python从cx_oracle:

cursor.execute(sql)
rows = cursor.fetchall()

这会在fetchall语句上抛出错误:'not a query'。是否有一种方法来声明变量在oracle将工作在SQL查询?

Answer 1

在oracle中，一种使用CTE的方法如下:

with cte as 
(select 200 as dispatchcount, 50 as headcount from dual)
select dispatchcount/headcount from cte

如果您希望两个变量在不同的表中，则使用多重cte，如下所示:

with cte1 as 
(select 200 as dispatchcount from dual),
cte2 as (select 50 as headcount from dual)
select dispatchcount/headcount from cte1 , cte2

Answer 2

是的，您可以使用准备好的SQL语句以及虚拟dual表，包括在元组中声明的参数的占位符，通过使用cursor.fetchone()考虑到您的情况只需要返回一行，例如

DispatchCount=200
HeadCount=50
sql  = """
SELECT :1 / :2
FROM dual
"""
cursor.execute(sql,(DispatchCount,HeadCount,))
rows = cursor.fetchone()

print(rows[0])

对于多行表，您也可以将fetchone()替换为fetchall()。