创建一个包,其中定义一个过程,可以查询得到编号为7900的员工的姓名ename、薪水sal、佣金comm、上司编号mgr
(通过OUT类型的参数将结果返回并打印)
(通过OUT类型的参数将结果返回并打印)
(注意包有包头部分的声明和包体部分的具体内容)
/*1.包的头部:*/
CREATE OR REPLACE PACKAGE wage_package123 IS
PROCEDURE query_emp
(p_no IN emp.empno%TYPE,
p_name OUT emp.ename%TYPE,
p_sal OUT emp.sal%TYPE,
p_comm OUT emp.comm%TYPE,
p_mgr out emp.mgr%type);
END wage_package123;
/
/*2.包体:*/
CREATE OR REPLACE PACKAGE BODY wage_package123
IS
PROCEDURE query_emp
(p_no IN emp.empno%TYPE,
p_name OUT emp.ename%TYPE,
p_sal OUT emp.sal%TYPE,
p_comm OUT emp.comm%TYPE,
p_mgr OUT emp.mgr%TYPE
)
IS
BEGIN
SELECT ename,sal,comm,mgr
INTO p_name,p_sal,p_comm,p_mgr
FROM emp
WHERE empno = p_no;
END query_emp;
end wage_package123;
/
/*3.执行*/
VARIABLE g_name VARCHAR2(25)
VARIABLE g_sal NUMBER
VARIABLE g_comm NUMBER
VARIABLE g_mgr NUMBER
EXECUTE wage_package123.query_emp(7900,:g_name,:g_sal,:g_comm,:g_mgr)
PRINT g_name
print g_sal
print g_comm
print g_mgr