创建一个包,定义一个存储过程,得到编号为7900的员工的信息。
sqlplus system/password
alter user scott account unlock
alter user scott identified by tiger
conn scott/tiger
包的头部
CREATE OR REPLACE PACKAGE wage_packageyuchen 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_packageyuchenjia;
/
包体
CREATE OR REPLACE PACKAGE BODY wage_packageyuchenjia
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_packageyuchenjia;
/
执行
VARIABLE g_name VARCHAR2(25)
VARIABLE g_sal NUMBER
VARIABLE g_comm NUMBER
VARIABLE g_mgr number
execute wage_packageyuchenjia.query(7900,:g_name,:g_sal,:g_comm,:g_mgr)
print g_name
print g_sal
print g_comm
print g_mgr