【SQL开源代码栏目提醒】:网学会员为需要SQL开源代码的朋友们搜集整理了procedure_function.sql相关资料,希望对各位网友有所帮助!
CREATE OR REPLACE PACKAGE BODY emppack AS
-- 以下是插入一个新雇员的过程
PROCEDURE insrec(pempno emp.empno%TYPE,pename emp.ename%TYPE,
pjob emp.job%TYPE,pmgr emp.mgr%TYPE,
phiredate emp.hiredate%TYPE,psal emp.sal%TYPE,
pcomm emp.comm%TYPE,pdeptno emp.deptno%TYPE)
IS
BEGIN
INSERT INTO emp VALUES(pempno,pename,pjob,pmgr,phiredate,
psal,pcomm,pdeptno);
DBMS_OUTPUT.PUT_LINE('1条记录被添加!');
END insrec;
-- 以下是删除一个现有雇员的过程
PROCEDURE delrec(pempno IN NUMBER)
IS
BEGIN
DELETE FROM emp WHERE empno=pempno;
DBMS_OUTPUT.PUT_LINE('1条记录被删除!');
END delrec;
-- 以下是显示指定雇员的整体薪水(薪水+佣金)的函数
FUNCTION selsal(pempno NUMBER) RETURN NUMBER
IS
vTotalSal NUMBER;
BEGIN
SELECT NVL(sal,0)+NVL(comm,0) INTO vTotalSal
FROM emp
WHERE empno=pempno;
RETURN vTotalSal;
END selsal;
-- 以下是显示指定雇员所在部门名称的函数
FUNCTION seldname(pempno NUMBER) RETURN VARCHAR2
IS
vdname dept.dname%TYPE;
BEGIN
SELECT dname INTO vdname
FROM emp,dept
WHERE empno=pempno AND emp.deptno=dept.deptno;
RETURN vdname;
END seldname;
END;
/