--函数传入参数的方式
--测试给形参传值的位置表示法
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;
