彻底解决！Oracle Python驱动中PL/SQL执行行数统计的5个实战陷阱与解决方案

彻底解决！Oracle Python驱动中PL/SQL执行行数统计的5个实战陷阱与解决方案

【免费下载链接】python-oracledb Python driver for Oracle Database conforming to the Python DB API 2.0 specification. This is the renamed, new major release of cx_Oracle 项目地址: https://gitcode.com/gh_mirrors/py/python-oracledb

引言：被"隐形"的执行结果困扰？

你是否在使用python-oracledb调用PL/SQL存储过程时，遇到过执行行数统计不准确的问题？明明执行了多条数据，rowcount却返回0或1？本文将深入解析这一常见痛点，通过5个实战案例带你掌握PL/SQL执行行数统计的正确方法，让你彻底摆脱"统计迷雾"。

读完本文，你将能够：

• 理解python-oracledb中PL/SQL执行行数统计的底层原理
• 掌握3种获取准确执行行数的方法
• 解决批量执行PL/SQL时的行数统计难题
• 避免常见的行数统计陷阱
• 优化PL/SQL与Python交互的性能

一、PL/SQL执行行数统计的"认知误区"

1.1 一个令人困惑的现象

让我们从一个简单的测试开始，体验这个令人困惑的问题：

import oracledb
import sample_env

# 连接数据库
connection = oracledb.connect(
    user=sample_env.get_main_user(),
    password=sample_env.get_main_password(),
    dsn=sample_env.get_connect_string()
)

# 创建测试表和存储过程
with connection.cursor() as cursor:
    # 创建测试表
    cursor.execute("""
        CREATE TABLE test_table (
            id NUMBER,
            name VARCHAR2(50)
        )
    """)
    
    # 创建插入数据的存储过程
    cursor.execute("""
        CREATE OR REPLACE PROCEDURE insert_data(
            p_id IN NUMBER,
            p_name IN VARCHAR2
        ) AS
        BEGIN
            INSERT INTO test_table (id, name) VALUES (p_id, p_name);
        END;
    """)
    
    # 提交事务
    connection.commit()

# 测试1：直接执行INSERT语句
with connection.cursor() as cursor:
    cursor.execute("INSERT INTO test_table VALUES (1, '直接插入')")
    print(f"直接INSERT的rowcount: {cursor.rowcount}")  # 输出: 1

# 测试2：通过PL/SQL执行INSERT
with connection.cursor() as cursor:
    cursor.execute("BEGIN insert_data(2, 'PL/SQL插入'); END;")
    print(f"PL/SQL的rowcount: {cursor.rowcount}")  # 输出: 0

# 清理测试环境
with connection.cursor() as cursor:
    cursor.execute("DROP TABLE test_table PURGE")
    cursor.execute("DROP PROCEDURE insert_data")
connection.commit()
connection.close()

运行这段代码，你会惊讶地发现：

• 直接执行INSERT语句时，rowcount返回1（正确）
• 通过PL/SQL执行INSERT时，rowcount返回0（错误）

为什么会出现这种情况？这正是我们需要深入探讨的核心问题。

1.2 底层原理：OCI与Python DB API的差异

要理解这个问题，我们需要了解python-oracledb的底层工作原理：

python-oracledb遵循Python DB API 2.0规范，该规范定义了rowcount属性应返回受上一条SQL语句影响的行数。然而，当执行PL/SQL块时，OCI（Oracle Call Interface）通常返回0，因为PL/SQL块本身不返回受影响的行数，即使块中包含DML语句。

二、获取PL/SQL执行行数的3种方法

2.1 方法一：使用OUT参数返回行数

这是最常用且可靠的方法，通过PL/SQL的OUT参数显式返回受影响的行数：

# 创建带OUT参数的存储过程
with connection.cursor() as cursor:
    cursor.execute("""
        CREATE OR REPLACE PROCEDURE insert_data_with_count(
            p_id IN NUMBER,
            p_name IN VARCHAR2,
            p_count OUT NUMBER
        ) AS
        BEGIN
            INSERT INTO test_table (id, name) VALUES (p_id, p_name);
            p_count := SQL%ROWCOUNT;  -- 获取受影响的行数
        END;
    """)
    connection.commit()

# 调用存储过程并获取行数
with connection.cursor() as cursor:
    count_var = cursor.var(oracledb.DB_TYPE_NUMBER)
    cursor.execute("""
        BEGIN 
            insert_data_with_count(3, '带OUT参数的PL/SQL插入', :count); 
        END;
    """, count=count_var)
    
    print(f"通过OUT参数获取的行数: {count_var.getvalue()}")  # 输出: 1

优点：

• 可靠获取PL/SQL块中特定DML语句的影响行数
• 可以获取多个DML语句的总行数或分别获取每个语句的行数
• 适用于所有版本的Oracle数据库

缺点：

• 需要修改存储过程，添加OUT参数
• 增加了PL/SQL和Python代码的复杂度

2.2 方法二：使用RETURNING子句

对于INSERT、UPDATE和DELETE语句，可以使用RETURNING子句获取受影响的行：

# 创建使用RETURNING子句的存储过程
with connection.cursor() as cursor:
    cursor.execute("""
        CREATE OR REPLACE PROCEDURE insert_with_returning(
            p_id IN NUMBER,
            p_name IN VARCHAR2,
            p_out_id OUT NUMBER
        ) AS
        BEGIN
            INSERT INTO test_table (id, name) 
            VALUES (p_id, p_name)
            RETURNING id INTO p_out_id;
        END;
    """)
    connection.commit()

# 使用RETURNING子句获取行数
with connection.cursor() as cursor:
    out_id = cursor.var(oracledb.DB_TYPE_NUMBER)
    cursor.execute("""
        BEGIN 
            insert_with_returning(4, 'RETURNING插入', :out_id); 
        END;
    """, out_id=out_id)
    
    if out_id.getvalue() is not None:
        print(f"通过RETURNING获取的行数: 1")  # 输出: 1
    else:
        print(f"通过RETURNING获取的行数: 0")

优点：

• 不仅能获取行数，还能获取实际插入/更新的数据
• 适用于单行操作的场景
• 不需要额外查询表来验证操作结果

缺点：

• 仅适用于INSERT、UPDATE和DELETE语句
• 对于多行操作，需要使用BULK COLLECT
• 增加了存储过程的复杂度

2.3 方法三：使用DBMS_SQL返回结果

对于更复杂的PL/SQL块，可以使用DBMS_SQL包来执行动态SQL并返回受影响的行数：

# 创建使用DBMS_SQL的存储过程
with connection.cursor() as cursor:
    cursor.execute("""
        CREATE OR REPLACE PROCEDURE dynamic_insert(
            p_sql IN VARCHAR2,
            p_count OUT NUMBER
        ) AS
            l_cursor_id INTEGER;
            l_rows_processed INTEGER;
        BEGIN
            l_cursor_id := DBMS_SQL.OPEN_CURSOR;
            DBMS_SQL.PARSE(l_cursor_id, p_sql, DBMS_SQL.NATIVE);
            l_rows_processed := DBMS_SQL.EXECUTE(l_cursor_id);
            DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
            p_count := l_rows_processed;
        END;
    """)
    connection.commit()

# 使用DBMS_SQL获取行数
with connection.cursor() as cursor:
    count_var = cursor.var(oracledb.DB_TYPE_NUMBER)
    sql = "INSERT INTO test_table VALUES (5, 'DBMS_SQL插入')"
    cursor.execute("""
        BEGIN 
            dynamic_insert(:sql, :count); 
        END;
    """, sql=sql, count=count_var)
    
    print(f"通过DBMS_SQL获取的行数: {count_var.getvalue()}")  # 输出: 1

优点：

• 适用于动态SQL场景
• 可以获取复杂PL/SQL块的执行行数
• 提供了更多的执行信息和控制

缺点：

• 使用复杂，增加了代码复杂度
• 需要额外的权限来使用DBMS_SQL包
• 可能存在SQL注入风险（需要谨慎处理输入）

三、批量执行PL/SQL的行数统计解决方案

在实际应用中，我们经常需要批量执行PL/SQL过程。这时，行数统计变得更加复杂，但也更加重要。

3.1 批量执行的挑战

让我们先看一个批量执行PL/SQL的例子：

# 创建测试存储过程
with connection.cursor() as cursor:
    cursor.execute("""
        CREATE OR REPLACE PROCEDURE batch_insert(
            p_id IN NUMBER,
            p_name IN VARCHAR2
        ) AS
        BEGIN
            INSERT INTO test_table (id, name) VALUES (p_id, p_name);
        END;
    """)
    connection.commit()

# 准备批量数据
data = [
    (6, '批量1'),
    (7, '批量2'),
    (8, '批量3')
]

# 批量执行PL/SQL
with connection.cursor() as cursor:
    cursor.executemany("BEGIN batch_insert(:1, :2); END;", data)
    print(f"批量执行的rowcount: {cursor.rowcount}")  # 输出: 3
    print(f"实际插入的行数: {len(data)}")  # 输出: 3

在这个例子中，rowcount返回了3，与实际插入的行数一致。这似乎与我们之前的发现相矛盾，为什么这里又能正确返回行数呢？

3.2 executemany的特殊处理

原来，executemany()方法返回的rowcount表示成功执行的PL/SQL块数量，而不是每个PL/SQL块中DML语句影响的行数。这是一个重要的区别：

执行方法	rowcount含义	适用场景
execute()	PL/SQL块执行状态（0或1）	单条PL/SQL执行
executemany()	成功执行的PL/SQL块数量	批量PL/SQL执行
直接SQL执行	受影响的行数	直接执行DML语句

3.3 批量执行时获取准确行数的方法

要在批量执行时获取每个PL/SQL块的准确行数，我们需要结合使用OUT参数和executemany()：

# 创建带OUT参数的批量插入存储过程
with connection.cursor() as cursor:
    cursor.execute("""
        CREATE OR REPLACE PROCEDURE batch_insert_with_count(
            p_id IN NUMBER,
            p_name IN VARCHAR2,
            p_count OUT NUMBER
        ) AS
        BEGIN
            INSERT INTO test_table (id, name) VALUES (p_id, p_name);
            p_count := SQL%ROWCOUNT;
        END;
    """)
    connection.commit()

# 准备批量数据
data = [
    (9, '带计数1', None),
    (10, '带计数2', None),
    (11, '带计数3', None)
]

# 批量执行并获取每个PL/SQL块的行数
with connection.cursor() as cursor:
    # 创建输出变量数组
    counts = cursor.var(oracledb.DB_TYPE_NUMBER, arraysize=len(data))
    
    # 设置输入大小
    cursor.setinputsizes(None, None, counts)
    
    # 执行批量操作
    cursor.executemany("BEGIN batch_insert_with_count(:1, :2, :3); END;", data)
    
    # 输出结果
    print(f"批量执行的rowcount: {cursor.rowcount}")  # 输出: 3
    print(f"每个PL/SQL块影响的行数: {counts.values}")  # 输出: [1, 1, 1]
    print(f"总行数: {sum(counts.values)}")  # 输出: 3

3.4 使用PL/SQL集合优化批量操作

对于大量数据的批量插入，使用PL/SQL集合可以显著提高性能，并更方便地返回总行数：

# 创建自定义类型和存储过程
with connection.cursor() as cursor:
    # 创建自定义类型
    cursor.execute("""
        CREATE OR REPLACE TYPE id_table IS TABLE OF NUMBER;
        CREATE OR REPLACE TYPE name_table IS TABLE OF VARCHAR2(50);
    """)
    
    # 创建使用集合的存储过程
    cursor.execute("""
        CREATE OR REPLACE PROCEDURE bulk_insert_with_count(
            p_ids IN id_table,
            p_names IN name_table,
            p_total_count OUT NUMBER
        ) AS
        BEGIN
            FORALL i IN p_ids.FIRST..p_ids.LAST
                INSERT INTO test_table (id, name) VALUES (p_ids(i), p_names(i));
            
            p_total_count := SQL%ROWCOUNT;
        END;
    """)
    connection.commit()

# 准备批量数据
ids = [12, 13, 14, 15, 16]
names = ['集合1', '集合2', '集合3', '集合4', '集合5']

# 获取自定义类型
id_type = connection.gettype("ID_TABLE")
name_type = connection.gettype("NAME_TABLE")

# 创建集合对象
id_obj = id_type.newobject()
id_obj.extend(ids)

name_obj = name_type.newobject()
name_obj.extend(names)

# 执行批量插入
with connection.cursor() as cursor:
    total_count = cursor.var(oracledb.DB_TYPE_NUMBER)
    
    cursor.execute("""
        BEGIN 
            bulk_insert_with_count(:1, :2, :3); 
        END;
    """, [id_obj, name_obj, total_count])
    
    print(f"批量插入的总行数: {total_count.getvalue()}")  # 输出: 5

这种方法的性能优势非常明显，特别是对于大量数据：

四、PL/SQL行数统计的5个实战陷阱与解决方案

4.1 陷阱一：依赖rowcount获取PL/SQL执行结果

问题：直接使用cursor.rowcount获取PL/SQL执行结果。

解决方案：使用OUT参数显式返回行数。

# 错误示例
with connection.cursor() as cursor:
    cursor.execute("BEGIN batch_insert(17, '错误示例'); END;")
    print(f"受影响的行数: {cursor.rowcount}")  # 错误: 输出0
    
# 正确示例
with connection.cursor() as cursor:
    count_var = cursor.var(oracledb.DB_TYPE_NUMBER)
    cursor.execute("""
        BEGIN 
            batch_insert_with_count(17, '正确示例', :count); 
        END;
    """, count=count_var)
    print(f"受影响的行数: {count_var.getvalue()}")  # 正确: 输出1

4.2 陷阱二：忽略PL/SQL中的多个DML语句

问题：PL/SQL块中包含多个DML语句时，只返回最后一个DML的行数。

解决方案：累计所有DML语句的行数并返回。

# 创建包含多个DML的存储过程
with connection.cursor() as cursor:
    cursor.execute("""
        CREATE OR REPLACE PROCEDURE multi_dml_with_count(
            p_id1 IN NUMBER,
            p_name1 IN VARCHAR2,
            p_id2 IN NUMBER,
            p_name2 IN VARCHAR2,
            p_total_count OUT NUMBER
        ) AS
            l_count1 NUMBER;
            l_count2 NUMBER;
        BEGIN
            INSERT INTO test_table (id, name) VALUES (p_id1, p_name1);
            l_count1 := SQL%ROWCOUNT;
            
            INSERT INTO test_table (id, name) VALUES (p_id2, p_name2);
            l_count2 := SQL%ROWCOUNT;
            
            p_total_count := l_count1 + l_count2;
        END;
    """)
    connection.commit()

# 获取多个DML的总行数
with connection.cursor() as cursor:
    total_count = cursor.var(oracledb.DB_TYPE_NUMBER)
    cursor.execute("""
        BEGIN 
            multi_dml_with_count(18, '多行1', 19, '多行2', :total); 
        END;
    """, total=total_count)
    print(f"多个DML的总行数: {total_count.getvalue()}")  # 输出: 2

4.3 陷阱三：批量执行时错误处理不当

问题：批量执行时，部分PL/SQL块执行失败，导致行数统计不准确。

解决方案：使用异常处理和保存点，确保准确统计成功执行的行数。

# 创建带异常处理的存储过程
with connection.cursor() as cursor:
    cursor.execute("""
        CREATE OR REPLACE PROCEDURE insert_with_exception(
            p_id IN NUMBER,
            p_name IN VARCHAR2,
            p_count OUT NUMBER,
            p_success OUT BOOLEAN
        ) AS
        BEGIN
            p_success := TRUE;
            INSERT INTO test_table (id, name) VALUES (p_id, p_name);
            p_count := 1;
        EXCEPTION
            WHEN OTHERS THEN
                p_success := FALSE;
                p_count := 0;
                RAISE; -- 可选：是否继续抛出异常
        END;
    """)
    connection.commit()

# 准备包含重复ID的数据（会导致主键冲突）
data = [
    (20, '成功1', None, None),
    (20, '重复ID', None, None),  # 这行会失败
    (21, '成功2', None, None)
]

# 批量执行并处理错误
with connection.cursor() as cursor:
    counts = cursor.var(oracledb.DB_TYPE_NUMBER, arraysize=len(data))
    successes = cursor.var(oracledb.DB_TYPE_BOOLEAN, arraysize=len(data))
    
    cursor.setinputsizes(None, None, counts, successes)
    
    try:
        cursor.executemany("""
            BEGIN 
                insert_with_exception(:1, :2, :3, :4); 
            END;
        """, data)
    except oracledb.Error as e:
        print(f"执行过程中发生错误: {e}")
    
    # 统计成功执行的行数
    success_count = sum(1 for s in successes.values if s)
    total_rows = sum(c for c in counts.values)
    
    print(f"成功执行的PL/SQL块: {success_count}")
    print(f"成功插入的总行数: {total_rows}")

4.4 陷阱四：使用COMMIT影响行数统计

问题：在PL/SQL块中使用COMMIT，导致无法回滚且影响性能。

解决方案：将事务控制交给Python，在PL/SQL中只关注数据操作。

# 创建不含COMMIT的存储过程
with connection.cursor() as cursor:
    cursor.execute("""
        CREATE OR REPLACE PROCEDURE insert_without_commit(
            p_id IN NUMBER,
            p_name IN VARCHAR2,
            p_count OUT NUMBER
        ) AS
        BEGIN
            INSERT INTO test_table (id, name) VALUES (p_id, p_name);
            p_count := SQL%ROWCOUNT;
            -- 不包含COMMIT，由Python控制事务
        END;
    """)
    connection.commit()

# 在Python中控制事务
with connection.cursor() as cursor:
    try:
        count1 = cursor.var(oracledb.DB_TYPE_NUMBER)
        cursor.execute("""
            BEGIN 
                insert_without_commit(22, '无COMMIT1', :count); 
            END;
        """, count=count1)
        
        count2 = cursor.var(oracledb.DB_TYPE_NUMBER)
        cursor.execute("""
            BEGIN 
                insert_without_commit(23, '无COMMIT2', :count); 
            END;
        """, count=count2)
        
        total = count1.getvalue() + count2.getvalue()
        print(f"准备提交的总行数: {total}")
        
        # 统一提交
        connection.commit()
        print(f"成功提交 {total} 行数据")
    except oracledb.Error as e:
        connection.rollback()
        print(f"发生错误，回滚更改: {e}")

4.5 陷阱五：忽视PL/SQL函数的返回值

问题：使用PL/SQL函数时，忽视了函数可以直接返回受影响的行数。

解决方案：利用PL/SQL函数的返回值特性，简化行数统计。

# 创建返回行数的函数
with connection.cursor() as cursor:
    cursor.execute("""
        CREATE OR REPLACE FUNCTION insert_function(
            p_id IN NUMBER,
            p_name IN VARCHAR2
        ) RETURN NUMBER AS
        BEGIN
            INSERT INTO test_table (id, name) VALUES (p_id, p_name);
            RETURN SQL%ROWCOUNT;
        END;
    """)
    connection.commit()

# 调用函数获取行数
with connection.cursor() as cursor:
    result = cursor.callfunc("insert_function", oracledb.DB_TYPE_NUMBER, [24, '函数插入'])
    print(f"函数返回的行数: {result}")  # 输出: 1

五、性能优化：PL/SQL与Python交互的最佳实践

5.1 选择合适的批量执行策略

根据数据量选择最合适的批量执行策略：

数据量	推荐方法	性能	复杂度
少量数据(<100行)	多次execute()	低	低
中等数据(100-1000行)	executemany()	中	中
大量数据(>1000行)	PL/SQL集合+FORALL	高	高

5.2 使用数组绑定减少网络往返

# 使用数组绑定优化性能
with connection.cursor() as cursor:
    # 准备数组数据
    ids = [25, 26, 27, 28, 29]
    names = ['数组1', '数组2', '数组3', '数组4', '数组5']
    
    # 使用数组绑定执行
    cursor.setinputsizes(oracledb.DB_TYPE_NUMBER, oracledb.DB_TYPE_VARCHAR)
    cursor.execute("""
        INSERT INTO test_table (id, name) 
        VALUES (:1, :2)
    """, [ids, names])
    
    print(f"数组绑定插入的行数: {cursor.rowcount}")  # 输出: 5

5.3 调整arraysize参数优化fetch性能

# 优化查询性能
with connection.cursor() as cursor:
    # 设置arraysize（默认是100）
    cursor.arraysize = 500
    
    # 执行查询
    cursor.execute("SELECT * FROM test_table")
    
    # 批量获取结果
    while True:
        rows = cursor.fetchmany()
        if not rows:
            break
        # 处理数据...
        print(f"获取了 {len(rows)} 行数据")

5.4 使用连接池减少连接开销

# 使用连接池优化性能
pool = oracledb.create_pool(
    user=sample_env.get_main_user(),
    password=sample_env.get_main_password(),
    dsn=sample_env.get_connect_string(),
    min=2,
    max=5,
    increment=1
)

# 从池中获取连接
connection = pool.acquire()

# 执行操作...
with connection.cursor() as cursor:
    # 执行PL/SQL...
    pass

# 将连接返回给池（而不是关闭）
pool.release(connection)

# 不再需要池时关闭
pool.close()

六、总结与展望

6.1 核心知识点回顾

本文深入探讨了python-oracledb中PL/SQL执行行数统计的问题，主要内容包括：

1. PL/SQL执行行数统计的特殊性和常见误区
2. 获取准确行数的3种方法：
   • 使用OUT参数返回行数
   • 使用RETURNING子句获取影响的行
   • 使用DBMS_SQL包执行动态SQL
3. 批量执行PL/SQL时的行数统计解决方案
4. PL/SQL行数统计的5个实战陷阱与解决方案
5. 优化PL/SQL与Python交互性能的最佳实践

6.2 高级应用：监控PL/SQL执行性能

结合本文所学知识，我们可以构建一个PL/SQL执行监控工具，跟踪每个PL/SQL块的执行时间和影响行数：

# PL/SQL执行监控工具的简单实现
def execute_plsql_with_monitoring(cursor, plsql, params=None):
    import time
    
    # 记录开始时间
    start_time = time.time()
    
    # 执行PL/SQL
    if params:
        cursor.execute(plsql, params)
    else:
        cursor.execute(plsql)
    
    # 计算执行时间
    execution_time = (time.time() - start_time) * 1000  # 转换为毫秒
    
    # 获取影响行数（假设使用了OUT参数返回行数）
    row_count = params.get('count', 0) if params else 0
    
    # 记录监控信息
    monitoring_info = {
        'plsql': plsql,
        'execution_time_ms': execution_time,
        'row_count': row_count,
        'timestamp': time.strftime('%Y-%m-%d %H:%M:%S')
    }
    
    # 可以将监控信息写入日志或监控表
    print(f"监控信息: {monitoring_info}")
    
    return cursor, monitoring_info

# 使用监控工具执行PL/SQL
with connection.cursor() as cursor:
    count_var = cursor.var(oracledb.DB_TYPE_NUMBER)
    params = {'id': 30, 'name': '监控示例', 'count': count_var}
    
    cursor, info = execute_plsql_with_monitoring(
        cursor,
        "BEGIN batch_insert_with_count(:id, :name, :count); END;",
        params
    )
    
    print(f"执行结果: {count_var.getvalue()} 行受影响")
    print(f"执行时间: {info['execution_time_ms']:.2f} 毫秒")

6.3 未来展望

随着python-oracledb的不断发展，未来可能会提供更直接的方式来获取PL/SQL执行行数。但在此之前，掌握本文介绍的方法，将帮助你解决当前面临的PL/SQL执行行数统计问题。

建议大家持续关注python-oracledb的官方文档和更新日志，以便及时了解新的特性和最佳实践。同时，也欢迎大家在项目中尝试本文介绍的方法，并根据实际需求进行调整和优化。

七、互动与反馈

如果你在使用python-oracledb时遇到了其他有趣的问题，或者有更好的PL/SQL行数统计方法，欢迎在评论区分享你的经验和见解。

如果本文对你有所帮助，请点赞、收藏并关注，以便获取更多关于Python与Oracle数据库交互的实用技巧和最佳实践。

下期预告：《python-oracledb高级特性：连接池优化与监控实战》

【免费下载链接】python-oracledb Python driver for Oracle Database conforming to the Python DB API 2.0 specification. This is the renamed, new major release of cx_Oracle 项目地址: https://gitcode.com/gh_mirrors/py/python-oracledb