项目12 创建图书借阅网站项目存储过程、函数及程序包

12.2 任务1——存储过程

--- 12.2.1 无参存储过程 ---

--- 1.创建存储过程 ---

-- 例12.1 --
CONN SYS /BOOKBORROW AS SYSDBA;
GRANT CREATE PROCEDURE TO BOOKUSER;
CONN BOOKUSER /BOOKUSER;
CREATE PROCEDURE BOOK_PROC
    AS
        BEGIN
        DBMS_OUTPUT.PUT_LINE('欢迎使用图书借阅网站');
    END BOOK_PROC;
/
-- 创建序列 --

--- 2.调用存储过程 ---

-- 例12.2 --
SET ServerOutput ON;
EXEC BOOK_PROC;
-- 使用EXEC命令调用存储过程BOOK_PROC --

-- 例12.3 --
SET SERVEROUT ON
BEGIN
    BOOK_PROC;
END;
/
-- 使用匿名PL/SQL程序块调用存储过程BOOK_PROC --

--- 3.删除存储过程 ---

-- 例12.4 --
DROP PROCEDURE BOOK_PROC;
-- 删除存储过程BOOK_PROC --

--- 12.2.2 有参存储过程 ---

--- 1.带有单个输入参数的存储过程 ---

-- 例12.5 --
CREATE OR REPLACE PROCEDURE SETUSERPWD(CID IN NUMBER)
AS
BEGIN
    UPDATE USERS SET PWD = 222222 WHERE ID = CID;
END;
/
-- 创建带有输入参数的存储过程SETUSERPWD，此过程的功能是将表USERS中指定用户的密码设置为222222 --

-- 例12.6 --
SELECT ID,PWD FROM USERS WHERE ID = 3;
-- 查看表中数据 --
EXEC SETUSERPWD(3);
-- 调用存储过程 --
SELECT ID,PWD FROM USERS WHERE ID = 3;
-- 查看表中数据 --
SET SERVEROUT ON
BEGIN
    SETUSERPWD(2);
END;
/
-- 从匿名PL/SQL程序块中直接调用 --
DROP PROCEDURE SETUSERPWD;
-- 删除存储过程 --

--- 2.带有输入/输出参数的存储过程 ---

-- 例12.7 --
CREATE OR REPLACE PROCEDURE USERS_NAME
    (
        I_ID IN NUMBER,
        O_NAME OUT USERS.NAME % TYPE
    )
AS
BEGIN
    SELECT NAME INTO O_NAME
    FROM USERS
    WHERE ID = I_ID;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    O_NAME:='NULL';
END USERS_NAME;
/
-- 创建存储过程，根据输入的用户ID查看用户的姓名，如果没有指定的客户ID号，则姓名返回NULL --
VARIABLE NAME CHAR(10);
EXEC USERS_NAME(2,:NAME);
-- 调用带有输出参数的存储过程，需要先定义变量，用来接收输出参数的值 --
SET SERVEROUT ON
DECLARE
NAME USERS.NAME % TYPE;
BEGIN
USERS_NAME(2,NAME);
DBMS_OUTPUT.PUT_LINE('NAME = '||NAME);
END;
/
-- 匿名块调用过程 --

--- 3.带有IN OUT参数的存储过程 ---

-- 例12.8 --
CREATE OR REPLACE
PROCEDURE SWAP
(
    V_PARM1 IN OUT NUMBER,
    V_PARM2 IN OUT NUMBER
)
AS
L_TEMP NUMBER;
BEGIN
L_TEMP:=V_PARM1;
V_PARM1:=V_PARM2;
V_PARM2:=L_TEMP;
END SWAP;
/
-- 创建存储过程，实现两个数的交换 --
DECLARE
L_NUM1 NUMBER:=111;
L_NUM2 NUMBER:=222;
BEGIN
DBMS_OUTPUT.PUT_LINE('交换前的变量值');
DBMS_OUTPUT.PUT_LINE('L_NUM1='||L_NUM1);
DBMS_OUTPUT.PUT_LINE('L_NUM2='||L_NUM2);
SWAP (L_NUM1,L_NUM2);
DBMS_OUTPUT.PUT_LINE('交换后的变量值');
DBMS_OUTPUT.PUT_LINE('L_NUM1='||L_NUM1);
DBMS_OUTPUT.PUT_LINE('L_NUM2='||L_NUM2);
END;
/
-- 执行结果 --

12.3 任务2——函数

--- 12.3.2 创建函数 ---

-- 例12.9 --
CREATE OR REPLACE FUNCTION BOOKBORROWNUM
    (ID IN NUMBER)
    RETURN NUMBER
AS
    NUM NUMBER;
BEGIN
    SELECT COUNT(ISBN)INTO NUM FROM BOOKBORROW
    WHERE ISBN = ID;
    RETURN NUM;
END BOOKBORROWNUM;
/
-- 创建函数BOOKBORROWNUM()，此函数的功能是统计指定图书被借阅的次数 --

--- 12.3.3 调用函数 ---

-- 例12.10 --
SET SERVEROUT ON;
BEGIN
    DBMS_OUTPUT.PUT_LINE(BOOKBORROWNUM('9787720005319'));
END;
/
-- 调用函数BOOKBORROWNUM()，查询指定图书的借阅总次数 --

--- 12.3.4 删除函数 ---

DROP FUCTION BOOKBORROWNUM;
-- 删除函数BOOKBORROWNUM; --

12.4 任务3——程序包

--- 12.4.2 创建程序包 ---

-- 例12.12 --
CREATE OR REPLACE PACKAGE BOOKPACK
    IS
        PROCEDURE SETUSERPWD(CID IN NUMBER);
        FUNCTION BOOKBORROWNUM(ID IN NUMBER)
            RETURN Number;
    END BOOKPACK;
/
-- 创建程序包BOOKPACK --

-- 例12.13 --
CREATE PACKAGE BODY BOOKPACK
IS
    PROCEDURE SETUSERPWD(CID IN NUMBER)
    AS
    BEGIN
        UPDATE USERS SET PWD = 222222 WHERE ID = CID;
    END SETUSERPWD;
    FUNCTION BOOKBORROWNUM
        (ID IN NUMBER)
        RETURN NUMBER
    AS
        NUM NUMBER;
    BEGIN
        SELECT COUNT(ISBN) INTO NUM FROM BOOKBORROW
        WHERE ISBN = ID;
        RETURN NUM;
    END BOOKBORROWNUM;
END BOOKPACK;
/
-- 创建程序包BOOKPACK的包体部分 --

--- 12.4.4 删除程序包 ---

-- 例12.15 --
DROP PACKAGE BODY BOOKPACK;
-- 删除程序包体BOOKPACK --

-- 例12.16 --
DROP PACKAGE BOOKPACK;
-- 删除程序包规范BOOKPACK --