1、创建表test,表定义如下:
empno number(2),
ename varchar2(10),
sal number(7,2)
empno是主健
2、创建包的声明test_pak,内容包含add_user过程、del_user过程(根据empno值删除用户)、add_sal函数(根据
empno值确定用户,增加员工工资,并返回该用户的工资)
3、创建包体,实现上述定义
4、调用上述的每部分,验证正确性
包体--------------------------------------------------
create or replace package body test_pak
IS
PROCEDURE add_user(
no test_zuoye.empno%TYPE,
nam test_zuoye.ename%TYPE,
sal test_zuoye.sal%TYPE
)
IS
BEGIN
INSERT INTO test_zuoye VALUES(no,nam,sal);
COMMIT;
END add_user;
PROCEDURE del_user(
no test_zuoye.empno%TYPE
)
IS
BEGIN
DELETE FROM test_zuoye WHERE empno = no;
COMMIT;
END del_user;
PROCEDURE add_sal (
no test_zuoye.empno%TYPE,
sasl test_zuoye.ename%TYPE,
salary OUT test_zuoye.sal%TYPE
)
IS
var test_zuoye.ename%TYPE;
BEGIN
UPDATE test_zuoye SET sal = sasl +sal
WHERE empno = no;
SELECT sal
INTO var
FROM test_zuoye WHERE empno = no;
salary := var;
EXCEPTION -------------------------------异常处理,如果没有找到数据打印下面的内容
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('没有找到数据');
END add_sal;
end test_pak;
包头-------------------------------------------------------
create or replace package test_pak IS
PROCEDURE add_user(
no test_zuoye.empno%TYPE,
nam test_zuoye.ename%TYPE,
sal test_zuoye.sal%TYPE
);
PROCEDURE del_user(
no test_zuoye.empno%TYPE
);
PROCEDURE add_sal (
no test_zuoye.empno%TYPE,
sasl test_zuoye.ename%TYPE,
salary OUT test_zuoye.sal%TYPE
);
end test_pak;
测试-------------------------------------------------------
declare
var test_zuoye.sal%TYPE;
begin
-- test_pak.add_user(2,'kitty',3000);
-- test_pak.del_user(1);
test_pak.add_sal(2,50,var);
end;
---------------------------当添加工资找不到数据室打印
没有找到数据