oracle pl/sql

创建一个存储过程，以部门号为参数，返回该部门的人数和最高工资。

CREATE OR REPLACE PROCEDURE return_deptinfo( p_deptno emp.deptno%TYPE, p_avgsal OUT emp.sal%TYPE, p_count OUT emp.sal%TYPE)

AS

BEGIN

SELECT avg(sal),count(*) INTO p_avgsal,p_count FROM emp WHERE deptno=p_deptno;

EXCEPTION WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('The department don’’t exists!');

END r

eturn_deptinfo;

DECLARE
v_avgsal emp.sal%TYPE;
v_count NUMBER;
BEGIN
show_emp(20);
return_deptinfo(10,v_avgsal,v_count);
DBMS_OUTPUT.PUT_LINE(v_avgsal||' '||v_count);
END;

创建一个以部门号为参数，返回该部门最高工资的函数。
CREATE OR REPLACE FUNCTION return_maxsal
(p_deptno emp.deptno%TYPE)
RETURN emp.sal%TYPE
AS
v_maxsal emp.sal%TYPE;
BEGIN
SELECT max(sal) INTO v_maxsal FROM emp
WHERE deptno=p_deptno;
RETURN v_maxsal;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('The deptno is invalid!');
END return_maxsal;

DECLARE
v_sal emp.sal%TYPE;
BEGIN
FOR v_dept IN (SELECT DISTINCT deptno FROM emp)
LOOP
v_sal:=return_maxsal(v_dept.deptno);
DBMS_OUTPUT.PUT_LINE(v_dept.deptno||' '||v_sal);
END LOOP;
END;

包由包规范和包体两部分组成，在数据库中独立存储

创建一个软件包，包括2个变量、2个过程和1个异常。
CREATE OR REPLACE PACKAGE pkg_emp
AS
minsal NUMBER;
maxsal NUMBER;
e_beyondbound EXCEPTION;
PROCEDURE update_sal(
p_empno NUMBER, p_sal NUMBER);
PROCEDURE add_employee(
p_empno NUMBER,p_sal NUMBER);
END pkg_emp;

CREATE OR REPLACE PACKAGE BODY pkg_emp
AS
PROCEDURE update_sal(p_empno NUMBER, p_sal NUMBER)
AS
BEGIN
SELECT min(sal), max(sal) INTO minsal,maxsal FROM emp;
IF p_sal BETWEEN minsal AND maxsal THEN
UPDATE emp SET sal=p_sal WHERE empno=p_empno;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20000,'The employee doesn''t exist');
END IF;
ELSE
RAISE e_beyondbound;
END IF;
EXCEPTION
WHEN e_beyondbound THEN
DBMS_OUTPUT.PUT_LINE('The salary is beyond bound!');
END update_sal;

PROCEDURE add_employee(p_empno NUMBER,p_sal NUMBER)
AS
BEGIN
SELECT min(sal), max(sal) INTO minsal,maxsal FROM emp;
IF p_sal BETWEEN minsal AND maxsal THEN
INSERT INTO emp(empno,sal) VALUES(p_empno,p_sal);
ELSE
RAISE e_beyondbound;
END IF;
EXCEPTION
WHEN e_beyondbound THEN
DBMS_OUTPUT.PUT_LINE('The salary is beyond bound!');
END add_employee;
END pkg_emp;

在一个包中重载两个过程，分别以部门号和部门名称为参数，查询相应部门员工名、员工号信息
CREATE OR REPLACE PACKAGE pkg_overload
AS
PROCEDURE show_emp(p_deptno NUMBER);
PROCEDURE show_emp(p_dname VARCHAR2);
END pkg_overload;
CREATE OR REPLACE PACKAGE BODY pkg_overload
AS
PROCEDURE show_emp(p_deptno NUMBER)
AS
BEGIN
FOR v_emp IN (SELECT * FROM emp WHERE deptno=p_deptno) LOOP
DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||
v_emp.ename);
END LOOP;
END show_emp;

PROCEDURE show_emp(p_dname VARCHAR2)
AS
v_deptno NUMBER;
BEGIN
SELECT deptno INTO v_deptno FROM dept
WHERE dname=p_dname;
FOR v_emp IN (SELECT * FROM emp WHERE deptno=v_deptno) LOOP
DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||
v_emp.ename);
END LOOP;
END show_emp;
END pkg_overload;

创建一个触发器，禁止在休息日改变雇员信息，
create or replace trigger tr_sec_emp
before insert or update or delete on emp
begin
if to_char(sysdate,'DY') in ('星期六','星期日') then
raise_application_error(-20001,'不能在休息日修改员工信息');
end if;
end;