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;

 

包由包規範和包體兩部分組成，在資料庫中隔離儲存區 (Isolated Storage)

建立一個軟體包，包括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;

 

oracle pl/sql