ORACLE PL SQL函数及触发器练习

--函数传入参数的方式
--测试给形参传值的位置表示法
CREATE OR REPLACE FUNCTION f1(a NUMBER, b NUMBER, c NUMBER, d NUMBER)
RETURN NUMBER
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE(a||' '||b||' '||c||' '||d);
  RETURN a+b+c+d;
END;
/
DECLARE
  e NUMBER;
BEGIN
  e := f1(1,2,3,4);
END;
--测试给形参传值的名称表示法
DECLARE
  e NUMBER;
BEGIN
  e := f1(a=>1,c=>2,d=>3,b=>4);
END;
--测试有一个形参使用名称表示法，后面的都必须也使用名称表示法
DECLARE
  e NUMBER;
BEGIN
  e := f1(1,c=>2,d=>3,b=>4);
END;
--测试必须给所有参数都赋值!
DECLARE
  e NUMBER;
BEGIN
  e := f1(1,c=>2,d=>3); --短一个参数，报错
END;
-- 测试参数的默认值!!
CREATE OR REPLACE FUNCTION f1(a NUMBER DEFAULT 1, b NUMBER DEFAULT 2, c NUMBER DEFAULT 3, d NUMBER DEFAULT 4)
RETURN NUMBER
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE(a||' '||b||' '||c||' '||d);
  RETURN a+b+c+d;
END;
/
DECLARE
  e NUMBER;
BEGIN
  e := f1(c=>2, b=>3); --此时赋值需要用 =>
END;

--创建一个存储过程，接受2个部门编号，输出人数较多的部门名。
CREATE OR REPLACE PROCEDURE ff(p_deptno1 NUMBER, p_deptno2 NUMBER)
IS
   n1 NUMBER;
   n2 NUMBER;
   v1 VARCHAR2(100);
   v2 VARCHAR2(100);
BEGIN
  
         SELECT COUNT(1)  INTO n1 FROM emp
         WHERE p_deptno1 = deptno  ;
  
         SELECT COUNT(1) INTO n2 FROM emp
         WHERE p_deptno2 = deptno ;
  
  SELECT dname INTO v1 FROM dept WHERE deptno = p_deptno1; 
  SELECT dname INTO v2 FROM dept WHERE deptno = p_deptno2;
  
  IF n1>n2 THEN DBMS_OUTPUT.PUT_LINE(v1);
  ELSE DBMS_OUTPUT.PUT_LINE(v2);
  END IF;
END;
/
BEGIN
  ff(20, 30);
END;

--创建一个存储过程，接受两个员工编号，输出月薪较高的员工名。
CREATE OR REPLACE PROCEDURE ff(p_empno1 NUMBER, p_empno2 NUMBER)
IS
   n1 NUMBER;
   n2 NUMBER;
   v1 VARCHAR2(100);
   v2 VARCHAR2(100);
BEGIN
  
         SELECT sal  INTO n1 FROM emp
         WHERE p_empno1 = empno  ;
  
         SELECT sal INTO n2 FROM emp
         WHERE p_empno2 = empno ;
  
  SELECT ename INTO v1 FROM emp WHERE empno = p_empno1; 
  SELECT ename INTO v2 FROM emp WHERE empno = p_empno2;
  
  IF n1>n2 THEN DBMS_OUTPUT.PUT_LINE(v1);
  ELSE DBMS_OUTPUT.PUT_LINE(v2);
  END IF;
END;
/
BEGIN
  ff(7369, 7788);
END;

--1. 创建一个存储过程，接受2个部门编号，输出平均月薪较低的部门名字。
CREATE OR REPLACE PROCEDURE f5(p_deptno1 NUMBER, p_deptno2 NUMBER)
IS
   n1 NUMBER;
   n2 NUMBER;
   v1 VARCHAR2(100);
   v2 VARCHAR2(100);
BEGIN
  
         SELECT AVG(sal)  INTO n1 FROM emp
         WHERE p_deptno1 = deptno  ;
  
         SELECT AVG(sal) INTO n2 FROM emp
         WHERE p_deptno2 = deptno ;
  
  SELECT dname INTO v1 FROM dept WHERE deptno = p_deptno1; 
  SELECT dname INTO v2 FROM dept WHERE deptno = p_deptno2;
  
  IF n1>n2 THEN DBMS_OUTPUT.PUT_LINE(v2);
  ELSE DBMS_OUTPUT.PUT_LINE(v1);
  END IF;
END;
/
BEGIN
  f5(20, 10);
END;

--2. 创建一个存储过程，接受2个员工编号，返回员工所在部门人数较多的部门名。
CREATE OR REPLACE PROCEDURE f11(p_empno1 NUMBER, p_empno2 NUMBER)
IS
   n1 NUMBER;
   n2 NUMBER;
   v1 VARCHAR2(100);
   v2 VARCHAR2(100);
   
   v3 VARCHAR2(100);
   v4 VARCHAR2(100);
BEGIN
  
         SELECT deptno  INTO v1 FROM emp
         WHERE p_empno1 = empno  ;
  
         SELECT deptno INTO v2 FROM emp
         WHERE p_empno2 = empno ;
         
         SELECT deptno  INTO v1 FROM emp
         WHERE p_empno1 = empno  ;
  
         SELECT deptno INTO v2 FROM emp
         WHERE p_empno2 = empno ;
  
  SELECT COUNT(*) INTO n1 FROM emp WHERE empno = v1; 
  SELECT COUNT(*) INTO n2 FROM emp WHERE empno = v2;
  
  IF n1>n2 THEN DBMS_OUTPUT.PUT_LINE(v1);
  ELSE DBMS_OUTPUT.PUT_LINE(v2);
  END IF;
END;
/
BEGIN
  ff(7369, 7788);
END;

--触发器练习
CREATE OR REPLACE TRIGGER t1
AFTER INSERT ON new_emp
BEGIN
     DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, '插入一条语句');
END;
/
CREATE OR REPLACE TRIGGER t2
AFTER DELETE ON new_emp
BEGIN
     DBMS_OUTPUT.PUT_LINE(SYSDATE'删除一条语句');
END;
/
CREATE OR REPLACE TRIGGER t3
AFTER UPDATE ON new_emp
BEGIN
     DBMS_OUTPUT.PUT_LINE('更新一条语句');
END;
/

SELECT * FROM new_emp;
INSERT INTO new_emp (empno, ename, sal) VALUES (9111, 'DDYY', 800);
UPDATE new_emp SET sal = sal +100 WHERE empno = 9111;
DELETE FROM new_emp WHERE empno = 9111;

/*10. 测试 
                insert    delete    update
    :new    有值    没值    有值
    :old    没值    有值    有值*/

CREATE OR REPLACE TRIGGER t10
AFTER INSERT OR DELETE OR UPDATE ON new_emp FOR EACH ROW
BEGIN
     DBMS_OUTPUT.PUT_LINE('新表的sal: '||:new.sal);
     DBMS_OUTPUT.PUT_LINE('旧表的sal: '||:old.sal);
END;
/
INSERT INTO new_emp (empno, ename, sal) VALUES (9111, 'DDYY', 800);
UPDATE new_emp SET sal = sal +100 WHERE empno = 9111;
DELETE FROM new_emp WHERE empno = 9111;

--11. 利用 before 触发器，把每次加入dept表的dname都替换为一个固定值..
CREATE OR REPLACE TRIGGER t10
BEFORE INSERT ON new_emp FOR EACH ROW
BEGIN
     :new.sal := 1000;
     DBMS_OUTPUT.PUT_LINE('新表的sal: '||:new.sal);
     DBMS_OUTPUT.PUT_LINE('旧表的sal: '||:old.sal);
END;
/
INSERT INTO new_emp (empno, ename, sal) VALUES (9111, 'DDYY', 800);
DELETE FROM new_emp WHERE empno =9111;
SELECT * FROM new_emp;

-- 12. 利用 before 触发器，制作出主键自增长的效果.
CREATE OR REPLACE TRIGGER t10
BEFORE INSERT ON new_emp FOR EACH ROW
DECLARE
     n_e NUMBER;
BEGIN
     SELECT MAX(empno) INTO n_e FROM new_emp;
     :new.empno := n_e+1;
     DBMS_OUTPUT.PUT_LINE('新表的empno: '||:new.empno);
     DBMS_OUTPUT.PUT_LINE('旧表的empno: '||:old.empno);
END;
/
INSERT INTO new_emp (empno, ename, sal) VALUES (9111, 'DDYY', 800);

SELECT * FROM new_emp ORDER BY empno;