存储过程

 --匿名块
 --存储过程
 --1.无参存储过程
 CREATE OR REPLACE PROCEDURE proc_1 IS
 BEGIN
   dbms_output.put_line('欢迎你：'||USER);
   dbms_output.put_line('现在是：'||to_char(SYSDATE,'yyyy-MM-dd'));
 END proc_1;
 
 --在代码块中对存储过程进行调用
 BEGIN
    proc_1;
 END;
 
 --2.有参存储过程
   --带有输入参数的存储过程
   --1.使用标量变量作为输入参数
   CREATE OR REPLACE PROCEDURE add_dept1(deptno dept.deptno%TYPE,
         dname dept.dname%TYPE,loc dept.loc%TYPE ) IS   
   BEGIN
     INSERT INTO dept VALUES(deptno,dname,loc);  
     COMMIT;
     EXCEPTION 
       WHEN dup_val_on_index THEN
           dbms_output.put_line('主键冲突，请重新设置'); 
   END add_dept1;
   
   --测试
   BEGIN
     --add_dept1(70,'山科大','青岛开发区');
     --add_dept1(deptno=>80,dname=>'青岛大学',loc=>'崂山区');
     add_dept1(&deptno,'&dname','&loc');
   END;
   SELECT * FROM dept;
   
   
   --2.使用记录类型作为输入参数
   CREATE OR REPLACE PROCEDURE add_dept2(dept_record dept%ROWTYPE) IS   
   BEGIN
     INSERT INTO dept VALUES dept_record;
     EXCEPTION 
       WHEN dup_val_on_index THEN
           dbms_output.put_line('主键冲突，请重新设置');
   END add_dept2;
   
   --测试
   DECLARE 
       dept_record dept%ROWTYPE;
   BEGIN
     dept_record.deptno:=&deptno;
     dept_record.dname:='&dname';
     dept_record.loc:='&loc';
     
     add_dept2(dept_record);
   END;  
   
   
   --3.使用集合类型作为输入参数
   
   CREATE TYPE deptno_table_type IS TABLE OF NUMBER(2);
   CREATE TYPE dname_table_type IS TABLE OF VARCHAR2(14);
   CREATE TYPE loc_table_type IS TABLE OF VARCHAR2(13);
   CREATE TYPE  p_emphiredate IS TABLE OF VARCHAR2(13);
  -- CREATE TABLE department AS
          SELECT * FROM dept;
   
   --三个数据集合作为参数
   CREATE OR REPLACE PROCEDURE add_dept3(deptno_table deptno_table_type,dname_table dname_table_type,
           loc_table loc_table_type) IS   
   BEGIN
     FOR i IN 1..deptno_table.count LOOP
        INSERT INTO department VALUES(deptno_table(i),dname_table(i),loc_table(i));
     END LOOP; 
     COMMIT; 
     EXCEPTION 
       WHEN dup_val_on_index THEN
           dbms_output.put_line('主键冲突，请重新设置');
       WHEN SUBSCRIPT_BEYOND_COUNT THEN
           dbms_output.put_line('部分集合的元素数值不够');
   END add_dept3;
   
   --调用测试
   DECLARE
       deptno_table deptno_table_type;
       dname_table dname_table_type;
       loc_table loc_table_type;
   BEGIN
     --BULK COLLECT INTO可以批量查询，提高查询效率，into后面跟的必须是集合
     SELECT * BULK COLLECT INTO deptno_table,dname_table,loc_table FROM dept;
     FOR i IN 1..deptno_table.count LOOP
       deptno_table(i):=deptno_table(i)+2;
     END LOOP;
     add_dept3(deptno_table,dname_table,loc_table);     
   END;
   
   SELECT * FROM department ORDER BY deptno ASC;
   
   
   --带有输出参数的存储过程
   --1.标量变量
   CREATE OR REPLACE PROCEDURE get_dept1(p_deptno dept.deptno%TYPE,
          dname OUT dept.dname%TYPE,loc OUT dept.loc%TYPE) IS
   
   BEGIN
          SELECT dname,loc INTO dname,loc  FROM dept WHERE deptno=p_deptno;
          EXCEPTION 
            WHEN NO_DATA_FOUND THEN
              dbms_output.put_line('该部门不存在');
   END get_dept1;
   
   --调用测试
   DECLARE
       p_deptno dept.deptno%TYPE;
       dname  dept.dname%TYPE;
       loc  dept.loc%TYPE;
   BEGIN
       p_deptno:=&deptno;
       get_dept1(p_deptno,dname,loc);
       dbms_output.put_line('部门号：'||p_deptno);
       dbms_output.put_line('部门名称：'||dname);
       dbms_output.put_line('部门地址：'||loc);
   END;  
   
   
   --2.记录类型
   CREATE OR REPLACE PROCEDURE get_dept2(p_deptno dept.deptno%TYPE, dept_record OUT dept%ROWTYPE) IS
   
   BEGIN 
     SELECT * INTO dept_record  FROM dept WHERE deptno=p_deptno;
     EXCEPTION 
            WHEN NO_DATA_FOUND THEN
              dbms_output.put_line('该部门不存在');
   END get_dept2;
   --调用测试
   DECLARE
       p_deptno dept.deptno%TYPE;
       dept_record dept%ROWTYPE;
   BEGIN
       p_deptno:=&deptno;
       get_dept2(p_deptno,dept_record);
       dbms_output.put_line('部门号：'||dept_record.deptno);
       dbms_output.put_line('部门名称：'||dept_record.dname);
       dbms_output.put_line('部门地址：'||dept_record.loc);
       
   END;
   
   --3.集合类型
   CREATE OR REPLACE PROCEDURE get_dept3(p_loc dept.loc%TYPE,deptno_table OUT deptno_table_type,
          dname_table OUT dname_table_type) IS
   BEGIN
          SELECT deptno,dname BULK COLLECT INTO deptno_table,dname_table FROM dept WHERE lower(loc)=p_loc;
          EXCEPTION 
            WHEN NO_DATA_FOUND THEN
              dbms_output.put_line('该部门不存在');
   END get_dept3;
   
   --调用测试
   DECLARE
       p_loc dept.loc%TYPE;
       deptno_table  deptno_table_type;
       dname_table  dname_table_type;
   BEGIN
      p_loc:=LOWER('&loc');
      get_dept3(p_loc,deptno_table,dname_table);
      
      --使用循环对数据进行处理
      FOR i IN 1..deptno_table.count LOOP
        dbms_output.put_line('部门号：'||deptno_table(i));
        dbms_output.put_line('部门名称：'||dname_table(i));
        dbms_output.put_line('部门地址：'||p_loc);
        dbms_output.put_line('=====================');
      END LOOP;
      
   END;
   
   --带有输入输出参数的存储过程
   --计算2个数的和与差
   CREATE OR REPLACE PROCEDURE add_sub(n1 IN OUT NUMBER,n2 IN OUT NUMBER) IS
   BEGIN
     n1:=n1+n2;
     n2:=n1-2*n2;
   END add_sub;
   
   --调用测试
   DECLARE
       num1 NUMBER:=&num1;
       num2 NUMBER:=&num2;
   BEGIN
       add_sub(num1,num2);
       dbms_output.put_line('和：'||num1);
       dbms_output.put_line('差：'||num2);
   END;
   
   

  ----- 练习部分
  --1. 特殊员工加薪10%后，如果该员工参加工作时间超过5年，额外加薪3000.
  CREATE OR REPLACE PROCEDURE salary_test(p_empno emp.empno%TYPE, p_emphiredate OUT NUMBER) IS   
   BEGIN
    SELECT ceil(months_between(SYSDATE,hiredate)/12) YEAR INTO p_emphiredate  FROM emp WHERE empno=p_empno;
     IF p_emphiredate>5 THEN 
       
       UPDATE emp SET sal=sal*1.1+3000 WHERE empno=p_empno;
       ELSE
         UPDATE emp SET sal=sal*1.1 WHERE empno=p_empno;
       END IF;
     COMMIT;
   END salary_test;
   --测试
  DECLARE
        p_empno emp.empno%TYPE:=&empno;
        p_emphiredate NUMBER;
   BEGIN 
    salary_test(p_empno,p_emphiredate);
   END;
   
  SELECT * FROM emp;
  
  --2. 检查员工工资是否在有效的范围内，不同工作的工资范围为：
  --CLERK      1500-2500
  --SALES      2501-3500
  --ANALYST    3501-4500
  --MANAGER    4500以上
  --如果工资在此范围内，输出工资正常，否则输出工资太低，并且更新工资为该范围内的最低工资 


   CREATE OR REPLACE PROCEDURE test_2(p_empno emp.empno%TYPE, p_empjob OUT emp.job%TYPE,p_empsalary OUT  NUMBER) IS   
   BEGIN
    SELECT sal INTO p_empsalary  FROM emp WHERE empno=p_empno;
    SELECT job INTO p_empjob FROM emp WHERE empno=p_empno;
     IF p_empjob='CLERK' THEN 
       IF p_empsalary>1500 AND p_empsalary<2500 THEN
         dbms_output.put_line('工资正好');
       ELSE
         dbms_output.put_line('工资太低');
         UPDATE  emp SET sal=1500; 
      END IF;
      ELSIF p_empjob='SALESMAN' THEN 
      IF p_empsalary>2501 AND p_empsalary<3500 THEN 
        dbms_output.put_line('工资正好');
        ELSE 
        dbms_output.put_line('工资太低');
        END IF;
       END IF;
     COMMIT;
   END test_2;
    -----------测试
  DECLARE
      
        p_empno emp.empno%TYPE:=&empno;
        p_empjob emp.job%TYPE;
        p_empsalary NUMBER;
   BEGIN 
    test_2(p_empno,p_empjob,p_empsalary);
   END;
   
   
   /*
   4.汇总每个部门每个职位员工的最高及平均工资，包括显示部门名称
和职位名称，将数据存储到Test表中，并将工资最高的部门和职位，以及最高工资用OUT参数输出。要求使用存储过程

   */
   
   
   
   
   
   
   
   
   
   
   
    SELECT * FROM copy_emp