共计 4581 个字符,预计需要花费 12 分钟才能阅读完成。
概述
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) 时,它依然应用包所有者的特权。