一个帖子标记为 "Oracle Package"

查看所有标记

IvorySQL 4.0 之兼容 Oracle 包功能设计思路解读

2025年1月9日 · 1 分钟阅读

IvorySQL Team

日前，IvorySQL 4.0 发布，该版本新增了兼容 Oracle 包功能的新特性。

为了大家能够更好地理解和使用 IvorySQL 4.0，本文将简要介绍实现此功能时的设计思路。

Oracle 的包是什么？

包是包含了逻辑上相关的 PL/SQL 类型、变量、常量、子过程、游标和异常的一个模式对象。包被编译并存储在数据库中，多个应用可以共享包的内容。

包总是有一个包规范，包规范中声明了公有对象，这些公有对象可以在包外被引用。

如果公有对象中包含了游标或子过程，则包必须有一个包体。包体必须定义公有游标和公有子过程的代码。包体也可以声明并定义私有对象，私有对象不能在包外被引用，但可用于包内使用。最后，包体可以有一个初始化部分，这部分用于初始化变量，做一些一次性的设置步骤和异常处理。修改包体的时候，可以不修改包规范或引用包的公有对象的数据库对象，因此可以认为包体是一个黑盒。

IvorySQL 中包的实现

从内容来看，包体与嵌套子过程类似，包规范只是定义包体对外的接口，因此，从实现角度来看，包的实现过程可以和嵌套子过程类似。

我们主要处理的工作有如下几个方面：包的创建、更新、实例化、删除以及外部过程对包规范中包对象的引用。

包的创建： 修改 psql 语法，使 psql 能将整个包的创建语句整体发到服务器，在服务器中增加包的创建语法，语法结构基本上和普通函数类似，因此与函数类似，无需在 SQL 端展开。包创建经过语法解析后走 DDL 流程，调用包的创建函数，将包的内容存储到系统表里面去。
包的更新： 在 SQL 端支持更新语法，经语法解析后，调用包的更新函数，更新系统表内容，调用 plisql_package_Handler 走一遍 pl_gram.y，并失效包规范元组或包体元组，这样避免在运行时编译包和包体。
包的删除： 需要在 SQL 端支持其删除语法，经语法解析后，调用包的删除函数，删除系统表该包的内容。
包的实例化： 在第一次引用包的时候，如果包的内容没有在内存中(具体来说是一个 hash 表，类似于 portal 的 hash 表存储)，则调用包的实例化函数，将包重新实例化，实例化其实是调用 PL/iSQL 端的 compile 函数，将包规范与包体重新编译，并将编译的结果放在当前进程的内存里，包的实例化应该是将包与包体的整体内容加载到内存中。
包对象引用： 在 parse 阶段，提供查找包规范中的变量，类型、子过程的接口，优先在本模式下查找包中类型，然后查找系统表中的类型，在查找子过程时，优先在嵌套函数、包中、系统表中查找。
包的失效与包的状态： 包中如果全是常量与类型，则包无状态，否则包是有状态的。包的状态在访问包的变量与函数时设置，在重建包时，会让包的本地实例失效，并且本地重新编译实例化，其他进程的包实例，如果包是有状态的，访问包中变量或类型，则首次访问报包的状态丢失错误，其后正常访问。

IvorySQL 中包的设计

新增的系统表与缓存

为了存储包体与包规范内容，新增了 2 个系统表：

系统表名称	作用
`pg_package.h`	存储包规范内容
`pg_package_body.h`	存储包体内容

对应的系统缓存则有 4 个：

系统缓存名称	作用
`PKGBODYOID`	根据包体的 OID 查找包体的元组
`PKGBODYPKGID`	根据包规范的 OID 查找包体的元组
`PKGNAMEARGSNSP`	根据包名和模式的 OID 查找包的元组
`PKGOID`	根据包规范的 OID 查找包的元组

包的实例化

包的实例化，类似于函数编译，是将用字符串定义的数据，转换成结构化数据。包的内容是由包规范和包体两部分构成，因此，包的编译需要进行一些特殊处理。增加相应的新函数分别编译包规范和包体，并将结果缓存到哈希表中。

另外，为了处理在删除包与包体的时候，本地缓存会失效，在创建包缓存的时候，注册一个包的失效函数，用来处理包的失效时，需要清除包的缓存状态。

/* register invalid cache */
CacheRegisterSyscacheCallback(PKGBODYOID, InvalidatePackageCacheCallback, (Datum) 0);
CacheRegisterSyscacheCallback(PKGOID, InvalidatePackageCacheCallback, (Datum) 0);

InvalidatePackageCacheCallback 将根据 hash 值，遍历 hash 表中的每一项，更新相应包的缓存状态。

包的缓存状态用一个 char 来表示，目前只用到三位 bit，0x01 表示包规范被更新了，0x02 表示包是否有包体，0x04 表示包体被更新了。

包中对象的引用

提供查找包中函数、类型、变量的接口，供 parse 阶段使用，以下是部分函数列表。

函数名称	参数	返回值	说明
`LookupPkgTypeByTypename`	`Const List* names`，`Bool missing_ok`	`PkgType*`	根据语法阶段构造类型名称列表，查看是否是包中的类型。
`LookupPkgVarByvarnames`	`Const List _names`, `Bool missing_ok`	`PkgVar*`	根据变量名称，查看是否是包中的变量
`LookupPkgEntryByTypename`	`const List *names`, `bool missing_ok`	`PkgEntry`	根据名称，返回包中属性（类型或变量）
`LookupPkgFunc`	`ParseState pstate`, `List fargs, FuncCall *fn`	`FuncExpr`	根据函数名称，查看是否是包中的函数

在 PL/iSQL 非包内函数使用包的类型时，只需将类型的地址引用过来，而使用变量时，则需做一份本地映射，当涉及到该类的变量赋值时，需要进行特殊处理。一般来说，主要是切换到包的内存上下文，然后调用包的赋值函数。

函数形参或返回值引用包的类型

该部分需要修改 pg_proc 系统表的结构，需要增加字段来记录参数类型和返回值类型的名称，故在系统表中增加参数类型名称和返回值类型名称两列即可解决。

类似于 proargnames，增加 protypenames 用于记录参数类型的类型名称，增加 rettypename 记录返回值类型的名称。只有当相关项引用包的类型时赋值，不然为空。

因为 protypnames 是一个 text 数组，因此当有一个函数参数是包中类型时，该数组就不为空，且参数类型为包的项是由 TypeName 结构字符序列化得到，其他非包的参数类型为空字符串。

standard 包

支持 sys 模式下的 standard 包，可以不用带包名进行访问包中规范的对象，用户可以自行创建 standard 包。

DISCARD PACKAGE 语法

该功能是为兼容 PostgreSQL 的 DISCARD 功能做的，目前 PostgreSQL 支持 DISCARD SEQUENCE、DISCARD TEMP、DISCARD PLAN 等用来删除当前 session 的序列、临时表以及计划等缓存，并且 DISCARD ALL 支持删除本 session 的 PORTAL、临时表、计划、序列等临时存储。

IvorySQL 支持 DISCARD PACKAGE 语法，并且在 DISCARD ALL 中调用函数删除本 session 的包缓存。

逻辑备份还原支持包

在 pg_dump 工具中，包功能也得到了支持，用户可以使用 pg_dump 对包功能在内的数据进行备份恢复。

总结

以上就是实现兼容 Oracle 包功能时的设计思路。

通过包的形式将相关的功能模块化，使得数据库的过程、函数、变量和其他编程元素组织在一起形成自包含单元，便于管理和维护。由于实现细节隐藏在包体中，提高了代码的安全性和可维护性。包体中的代码在第一次调用时被加载到内存中，后续调用可以直接使用，减少了解析和加载时间。

IvorySQL 功能点介绍--Package（包）

2022年5月27日 · 1 分钟阅读

IvorySQL Team

IvorySQL 是一个开源项目，该项目的核心目标之一是提供 oracle 兼容功能，以便在 IvorySQL 数据库服务器上使用 oracle 代码。

在 PostgreSQL 之上提供 Oracle 兼容是一项多维任务。从提供与 Oracle 兼容的 SQL 语法到添加对 PostgreSQL 中缺失或行为不同的数据类型的支持。 Oracle 兼容性的主要核心之一是在 PostgreSQL 中提供兼容的过程语言 PL（procedural language），它在功能和语法上与 Oracle 的 PL/SQL 兼容。

为此，IvorySQL 添加了一种新的 PL 语言 PL/iSQL，它接受、理解和执行 PL/SQL 语法。如您所知，Oracle PL/SQL 的核心特性之一是 PACKAGES（包）。Package（包）是 Oracle 中的一个模式对象，它包含一组相关功能的定义，是 Oracle 使用最广泛的特性之一。

因此，本文将介绍 Package（包），并举例说明如何用 IvorySQL 创建、使用和销毁 Oracle 样式的包。

包（Package）

那么什么是 Package(包)？包是 PL/iSQL 代码的分组，按逻辑程序单元划分。换句话说，一个包可以被认为是相关函数、过程、变量或游标的集合，这个集合可以通过通用公共名称来访问。

IvorySQL 内置 PL/iSQL 语言，可以接受、理解和执行 PL/SQL 代码。包使用相同的语言，并且有两个主要组件。

包规范（Specification）

包规范列出了可以从包外部访问的所有项目,例如函数、过程、变量和游标。这也称为公共规范。

包体（Body）

包体包含包规范中列出的所有函数和过程的实现。除此之外，它还可以实现更多的功能、过程或其他元素。

这些不在包规范中的元素将被视为包的私有成员，并且只能在包内引用它们。不允许外部访问。

包体也可以有一个特殊的代码块，称为初始化块。这是一个特殊的块，因为这个块在每个会话中只执行一次，在包第一次被访问的最开始。

让我们看一些代码示例，看看包是如何运行的。

示例

通用示例

首先让我们设置兼容模式，这样我们就可以访问 IvorysSQL 数据库中可用的 Oracle 兼容特性。

SET compatible_mode TO oracle;

CREATE TABLE books (
    id     INT, 
    title  VARCHAR2(100), 
    author VARCHAR2(100),
    price  NUMBER);

INSERT INTO books VALUES (10, 'The Hobbit', 'J. R. R. Tolkien', 10.0);
INSERT INTO books VALUES (11, 'Winnie-the-Pooh', 'A. A. Milne', 5.0);
INSERT INTO books VALUES (12, 'Peter Pan', 'James Matthew Barrie', 4.0);
INSERT INTO books VALUES (13, 'Charlie and the Chocolate Factory', 'Roald Dahl', 5.0);
INSERT INTO books VALUES (14, 'SThe Jungle Book', 'Rudyard Kipling', 9.0);
INSERT INTO books VALUES (15, 'The Little White Bird', 'James Matthew Barrie', 3.0);

让我们来创建一个简单的包。这个包声明了一个游标来列出所有可用的图书，并包含一些子程序来列出、添加和删除书籍。同时，它还声明了一些私有变量来计数和保存图书信息。

CREATE OR REPLACE PACKAGE mybooks AS
    CURSOR      booksinfo IS SELECT * from BOOKS;

    PROCEDURE list_books;
    FUNCTION add_book(title VARCHAR2(100), author VARCHAR2(100), 
         price NUMBER) RETURN bool;
    PROCEDURE remove_book(book_title VARCHAR2(100));
END;
ivorysql$# /
CREATE PACKAGE

CREATE OR REPLACE PACKAGE BODY mybooks AS

    -- declare private variables
    bookinfo    booksinfo%rowtype; 
    books_count INT;

    PROCEDURE list_books AS
    BEGIN
        OPEN booksinfo;
        RAISE INFO 'Book Info:';
        RAISE INFO '';
        LOOP
            FETCH booksinfo into bookinfo;
            EXIT WHEN NOT FOUND;

            RAISE INFO '  Name    = %', bookinfo.title;
            RAISE INFO '  Author  = %', bookinfo.author;
            RAISE INFO '  Price   = %', bookinfo.price;
            RAISE INFO '------------------------------';
        END LOOP;
        RAISE INFO 'Total Books   = %', books_count;
        CLOSE booksinfo;
    END;

    FUNCTION add_book(title VARCHAR2(100), author VARCHAR2(100),
        price NUMBER) RETURN bool AS
    BEGIN
        INSERT INTO BOOKS VALUES (
            books_count + 1,
            title,
            author,
            price);
      
        books_count := books_count + 1;
        RETURN true;

        EXCEPTION WHEN OTHERS THEN
            RETURN false;
    END;

    PROCEDURE remove_book(book_title VARCHAR2(100)) AS
    BEGIN
        DELETE FROM BOOKS WHERE title = book_title;

        EXCEPTION WHEN OTHERS THEN
            RAISE INFO 'Book % not found', book_title;
    END;

-- initializer block
BEGIN         
    books_count := 0;
    SELECT count(*) INTO books_count
        FROM BOOKS;
END;
ivorysql$# /
CREATE PACKAGE BODY

让我们检验这些数据。这匿名块试图访问该包的私有成员，这将会导致错误。

ivorysql=# DECLARE
ivorysql$#   nbooks int := 0;
ivorysql$# BEGIN
ivorysql$#     nbooks := mybooks.books_count;
ivorysql$#     RAISE INFO 'Total Books   = %', nbooks;
ivorysql$# END;
ivorysql$# /
2022-05-26 16:35:32.328 PKT [63554] ERROR:  package private variable ("mybooks.books_count") is not accessible

让我们使用包的子程序列出所有可用的书籍。 list_books mybooks

ivorysql=# BEGIN
ivorysql$#     mybooks.list_books;
ivorysql$# END;
ivorysql$# /
INFO:  Book Info:
INFO:  
INFO:    Name    = The Hobbit
INFO:    Author  = J. R. R. Tolkien
INFO:    Price   = 10
INFO:  ------------------------------
INFO:    Name    = Winnie-the-Pooh
INFO:    Author  = A. A. Milne
INFO:    Price   = 3
....
....
INFO:  Total Books   = 6
DO
ivorysql=#

让我们使用包的子程序添加一本新书。 add_book mybooks

DECLARE
  added bool;
BEGIN
    added := mybooks.add_book('The Cat in the Hat', 'Dr. Seuss', 10);
    IF added = TRUE THEN
        RAISE INFO 'new book added';
    END IF;
END;
/

删除包

可以使用以下命令删除包体或完成包：

DROP PACKAGE BODY [package name]

ivorysql=# DROP PACKAGE BODY mybooks;
DROP PACKAGE BODY

DROP PACKAGE [package name]

ivorysql=# DROP PACKAGE mybooks;
DROP PACKAGE

具有不同权限的包调用

我们将创建两个具有调用者和定义者权限的包，并查看结果以了解它们之间的区别。

ivorysql=> SELECT current_user;
 current_user 
--------------
 ivorysql
(1 row)

--创建具有 INVOKER 权限的包

CREATE OR REPLACE PACKAGE pkg_invrights AUTHID CURRENT_USER AS
    PROCEDURE curr_user;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg_invrights AS
    PROCEDURE curr_user AS
    BEGIN
        RAISE INFO 'Current User: %', current_user;
    END;
END;
/

--创建一个具有 DEFINER 权限的包

CREATE OR REPLACE PACKAGE pkg_defrights AUTHID DEFINER AS
    PROCEDURE curr_user;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg_defrights AS
    PROCEDURE curr_user AS
    BEGIN
        RAISE INFO 'Current User: %', current_user;
    END;
END;
/

让我们先查看具有调用程序权限的包。

ivorysql=> CALL pkg_invrights.curr_user;
INFO:  Current User: ivorysql
CALL

让我们来看看具有定义权限的包。

ivorysql=> CALL pkg_defrights.curr_user;
INFO:  Current User: ivorysql
CALL

让我们切换到另一个名为 ivuser 的用户。

ivorysql=> \c ivorysql ivuser;
You are now connected to database "ivorysql" as user "ivuser".

让我们先看看具有调用程序权限的包。

ivorysql=> CALL pkg_invrights.curr_user;
INFO:  Current User: ivuser
CALL

让我们看看具有定义权限的包。

ivorysql=> CALL pkg_defrights.curr_user;
INFO:  Current User: ivorysql
CALL

总结

如上所示，当调用具有调用者权限（pkg_invrights）的包时，它使用的是调用该包的用户的特权。但是，当调用（pkg_defrights）时，它仍然使用包所有者的特权。

Oracle 的包是什么？​

IvorySQL 中包的实现​

IvorySQL 中包的设计​

新增的系统表与缓存​

包的实例化​

包中对象的引用​

函数形参或返回值引用包的类型​

standard 包​

DISCARD PACKAGE 语法​

逻辑备份还原支持包​

总结​

通用示例​

删除包​

具有不同权限的包调用​