1、概念:包实际是一种封装,里面可以包含常量、函数、存储过程等。
1.1、声明一个包
CREATE OR REPLACE PACKAGE packageAboutCicle
IS
--声明常量
PI CONSTANT NUMBER:=3.14;
--声明函数
FUNCTION f_getC(r NUMBER)
RETURN NUMBER;
--声明存储过程
PROCEDURE proc_area(r NUMBER);
END;
1.2、定义一个主体,也就是包体,实现包体要求的内容
CREATE OR REPLACE PACKAGE BODY packagebodyCicle
AS
FUNCTION f_getC(r NUMBER) RETURN NUMBER
IS
BEGIN
END;
PROCEDURE proc_area(r NUMBER)
IS
BEGIN
END;
END;
2、实例
银行转账
--2.1、声明一个包
CREATE OR REPLACE PACKAGE packageAcount
IS
--声明函数
FUNCTION get_remaining(name VARCHAR2)
RETURN NUMBER;
--声明存储过程
PROCEDURE transfer(fromName in varchar2, money in number, toName varchar2);
END;
--2.2、为包定义一个包体
CREATE OR REPLACE PACKAGE BODY packageAcount
AS
FUNCTION get_remaining(name VARCHAR2) RETURN NUMBER
IS
left_money NUMBER;
BEGIN
SELECT f_money INTO left_money
FROM t_account
WHERE f_name = name;
RETURN left_money;
END;
PROCEDURE transfer(fromName in varchar2, money in number, toName in varchar2)
IS
BEGIN
IF money > get_remaining(fromName) THEN
dbms_output.put_line('余额不足');
raise_application_error(-10001,'余额不足');
ELSIF money<1 THEN
dbms_output.put_line('转账金额要大于1元');
raise_application_error(-10002,'转账金额要大于1元');
ELSE
UPDATE t_account SET f_money = f_money + money WHERE f_name = toName;
UPDATE t_account SET f_money = f_money - money WHERE f_name = fromName;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('转账异常');
ROLLBACK;
END;
END;
--2.3、调用包
DECLARE
t_fromName varchar2(10);
t_money number;
t_toName varchar2(10);
BEGIN
t_fromName :='&请输入转账人';
t_money :='&请输入转账金额';
t_toName :='&请输入转账接收人';
packageAcount.transfer(t_fromName,t_money,t_toName);
END;