我正在尝试从表列表中获取行数,其中所有表的列都应具有 Col_year = 1994。
这在 sql 中是可能的,而无需在 python 中编写循环,因为它需要大量的数据库命中。
为此,我编写了如下程序:
DECLARE
l_counter NUMBER;
tot_counter NUMBER;
v_sql varchar2(10000);
cursor c1 is
select table_name from ALL_TABLES
where table_name in ("Table1", "Table2", "Table3");
BEGIN
tot_counter := 0;
FOR rec IN c1 LOOP
v_sql := 'select count(*) from '|| rec.table_name ||' where Col_year = 1994';
EXECUTE IMMEDIATE v_sql INTO l_counter;
tot_counter := tot_counter + l_counter;
DBMS_OUTPUT.PUT_LINE(rec.table_name || ' l_counter ' || l_counter || ' tot_counter ' || tot_counter);
END LOOP;
END;
如何从 python 中调用它?
情况是我无法在实际数据库中创建它,因为这只是临时目的。因此,我看起来像 cursor.executeproc() insteat of cursor.callproc()?这可能吗?
请您参考如下方法:
首先,您不需要单个 SQL 查询就可以完成的过程:
Oracle 11g R2 架构设置:
查询 1:
SELECT SUM(c)
FROM (
SELECT COUNT(*) c FROM Table1 WHERE Col_year = 1999
Union all
SELECT COUNT(*) c FROM Table2 WHERE Col_year = 1999
Union all
SELECT COUNT(*) c FROM Table3 WHERE Col_year = 1999
)
Results :
| SUM(C) |
|--------|
| 10 |
其次,这里有一些链接可以帮助您找到答案:
Return variable from cx_Oracle PL/SQL call in Python
cx_Oracle and output variables
以及如何去做(我无法测试)
cursor = connection.cursor()
lOutput = cursor.var(cx_Oracle.STRING)
cursor.execute("""
DECLARE
l_counter NUMBER;
tot_counter NUMBER;
v_sql varchar2(10000);
cursor c1 is
select table_name from ALL_TABLES
where table_name in (Table1, Table2, Table3);
BEGIN
tot_counter := 0;
FOR rec IN c1 LOOP
v_sql := 'select count(*) from '|| rec.table_name ||' where Col_year = 1994';
EXECUTE IMMEDIATE v_sql INTO l_counter;
tot_counter := tot_counter + l_counter;
DBMS_OUTPUT.PUT_LINE(rec.table_name || ' l_counter ' || l_counter || ' tot_counter ' || tot_counter);
END LOOP;
END;
""")
print lOutput
