使用 PL 提升性能 | OceanBase 文档中心

使用 PL 提升性能

更新时间：2023-07-18 17:25:20

PL 怎样减少解析

PL 在数据库访问方面做了优化，能够缓存语句。比如，在 PL 里，如果关闭了一个游标，那么该游标将不能使用了，实际上 PL 还是保存游标打开状态并且缓存了它的语句。当需要再次使用缓存的语句时，PL 会使用相同的游标，从而避免了一次解析。

PL 只能缓存运行时不会变化的 SQL 语句。

关于 EXECUTE IMMEDIATE 语句

EXECUTE IMMEDIATE 语句在单个操作中构建和运行动态 SQL。最基本的语法格式为：

EXECUTE IMMEDIATE sql_statement

其中，sql_statement 字符串代表 SQL 语句。如果 sql_statement 具体值在每次 EXECUTE IMMEDIATE 运行时都一样，则 PL 可以缓存 EXECUTE IMMEDIATE 语句；如果 sql_statement 每次执行时值都不一样，则 PL 不能缓存这个 EXECUTE IMMEDIATE 语句。

关于 OPEN FOR 语句

OPEN FOR 语句的简单语法格式如下：

OPEN cursor_variable FOR query

应用程序能够为不同的查询打开游标变量，用完才关闭游标。因为 PL 只有在运行时才能决定不同查询的数量，所以 PL 不能缓存 OPEN FOR 语句。

如果您不需要游标变量，为了更好的性能和程序的便利性，建议使用声明的游标。

关于 Bulk SQL

Bulk SQL 减少了 PL 和 SQL 之间的交互次数，从而使用了较少的资源。

如果没有 Bulk SQL，通过 SQL 引擎从数据库每次获取一行数据，在 PL 里处理，然后返回给数据库 SQL 引擎；有了 Bulk SQL，一次可以从数据库获取一批记录，然后处理这批记录，再返回给数据库。

当需要从数据库获取很多行数据并在处理完后返还给数据库时，建议请使用Bulk SQL；如果不需要返回给数据库数据，则不需要 Bulk SQL。

下面示例通过对表 ware 循环，每次获取 100 行记录（在 Bulk FETCH 语句后加上 LIMIT 语句限制获取行数，这需要显式的游标），然后处理，再返回给数据库。

delimiter /
CREATE OR REPLACE PROCEDURE sp_bulk_sql_test
AS 
    TYPE T_IDS  IS TABLE OF number ;
    TYPE T_NAMES IS TABLE OF varchar2(50);
    l_id    T_IDS;
    l_first T_NAMES;
    l_last  T_NAMES;
    
    CURSOR c1 IS SELECT c_id, c_last, c_first FROM cust ;

    N   NUMBER := 100 ;
BEGIN
    OPEN c1 ;
    LOOP
        FETCH c1 BULK COLLECT INTO l_id, l_first, l_last LIMIT N;
    
        FOR i IN 1..l_id.COUNT
        LOOP 
            l_first(i)  := 'F-' || LPAD(to_char(l_id(i)),5,'0');
            l_last(i)   := 'L-' || LPAD(to_char(l_id(i)),5,'0');
        END LOOP;
    
        FORALL i IN 1..l_id.COUNT
            UPDATE cust SET c_first = l_first(i), c_last = l_last(i) WHERE c_id = l_id(i);
        EXIT WHEN c1%NOTFOUND;
        
        COMMIT;
    END LOOP;
    CLOSE c1;
END;
/
delimiter ;

obclient> call sp_bulk_sql_test();
Query OK, 0 rows affected (8.87 sec)