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_booksmybooks
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_bookmybooks
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; /
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
Grant Zhou目前居住在加拿大，他是瀚高北美研究院的负责人，并领导PostgreSQL公司开发团队，团队成员来自加拿大，中国和巴基斯坦。他同时担任PostgreSQL中国分会国际顾问委员会的秘书长，也很自豪能成为PostgresConf的组织者和亚洲联络人。他在阿尔卡特朗讯（诺基亚）公司工作了十多年，在高可用性、实时电信系统、数据库技术和Unix/Linux编程方面拥有丰富的经验。
2021年12月，该团队宣布了IvorySQL数据库的第一个可用版本，这是目前唯一一款基于PostgreSQL、兼容Oracle的开源数据库。2022年2月28日，基于PostgreSQL 14.2发布了IvorySQL 1.2。
