关于数据库:IvorySQL功能点介绍Package包

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(包)？包是 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) 时，它依然应用包所有者的特权。

关于数据库:IvorySQL功能点介绍Package包

概述

包（Package）

示例

通用示例

删除包

具备不同权限的包调用

总结