SQL包相关

-------------------包：把之前学的plsql的知识封装起来，便于管理

分为 包头(包定义) 和 包体

----创建包头的语法结构：
CREATE [OR REPLACE] PACKAGE 包名
IS / AS
变量、常量及数据类型定义;
游标定义头部;
函数、过程的定义和参数列表以及返回类型;
END [包名];

--比如：
    CREATE OR REPLACE PACKAGE PKG_TEST1
  IS
      --存储过程
    PROCEDURE 过程名[(参数名 [参数类型] 数据类型)];
    
    --自定义函数   
    FUNCTION 函数名[(参数名 [参数类型] 数据类型)] RETURN 返回的数据类型;
  
  END PKG_TEST1;
  

----创建包体的语法结构：
CREATE OR REPLACE PACKAGE BODY 包名
IS / AS

PROCEDURE 过程名(参数)
IS / AS
BEGIN
 --过程体;
END [过程名];

FUNCTION 函数名(参数) RETURN 类型
IS / AS
BEGIN
 --函数体;
END [函数名];

END;

--需要注意的是：
1、包头和包体连接起来的依据就是： 包名 
2、创建的时候需要先创建包头，再创建包体（反过来包体会报错，需要在包头创建后重新replace一下包体）
3、语法结构上：包头和包体 IS 到 END 之间没有 BEGIN
4、如果sp和fun带有参数，那么在包头里面也需要带上对应的参数
    意思就是：从 REPLACE 到 IS 这中间的任何一个单词都不要少

--比如：创建一个包：包含2个sp，2个自定义函数
sp_1 ：打印helloworld
sp_2 ：同步emp表数据到emp_tag

fun_1：返回2个值中较大的那个值
fun_2：返回社保缴纳月份

----创建包头
CREATE OR REPLACE PACKAGE PKG_TEST1
IS
    PROCEDURE SP_1;
  PROCEDURE SP_2;
  FUNCTION FUN_1(X NUMBER,Y NUMBER) RETURN NUMBER;
  FUNCTION FUN_2(P_DATE DATE) RETURN VARCHAR2;
END PKG_TEST1;
/
----创建包体
CREATE OR REPLACE PACKAGE BODY PKG_TEST1 IS
  PROCEDURE SP_1 IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('HELLO WORLD!');
  END SP_1;

  PROCEDURE SP_2 IS
    CURSOR C_EMP_TAG IS
      SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM EMP;
    SYS_DATE DATE := SYSDATE;
    CT       NUMBER;
  
  BEGIN
    FOR X IN C_EMP_TAG LOOP
      SELECT COUNT(*) INTO CT FROM EMP_TAG T WHERE T.EMPNO = X.EMPNO;
    
      IF CT = 1 THEN
        UPDATE EMP_TAG T
           SET T.ENAME      = X.ENAME,
               T.JOB        = X.JOB,
               T.           MGR = X.MGR,
               T.HIREDATE   = X.HIREDATE,
               T.SAL        = X.SAL,
               T.COMM       = X.COMM,
               T.DEPTNO     = X.DEPTNO,
               T.CHECK_DATE = SYS_DATE
         WHERE T.EMPNO = X.EMPNO;
      ELSIF CT = 0 THEN
        INSERT INTO EMP_TAG
          (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, CHECK_DATE)
        VALUES
          (X.EMPNO,
           X.ENAME,
           X.JOB,
           X.MGR,
           X.HIREDATE,
           X.SAL,
           X.COMM,
           X.DEPTNO,
           SYS_DATE);
      END IF;
    END LOOP;
    COMMIT;
  END SP_2;

  FUNCTION FUN_1(X NUMBER, Y NUMBER) RETURN NUMBER IS
  BEGIN
    IF X > Y THEN
      RETURN X;
    ELSE
      RETURN Y;
    END IF;
  END FUN_1;

  FUNCTION FUN_2(P_DATE DATE) RETURN VARCHAR2 IS
    V_SBJN VARCHAR2(20);
  BEGIN
    V_SBJN := TO_CHAR(ROUND(P_DATE, 'MM'), 'YYYY-MM');
  
    RETURN V_SBJN;
  END FUN_2;

END PKG_TEST1;
/

-----调用包里的sp
BEGIN
  PKG_TEST1.SP_1;
  PKG_TEST1.SP_2;
END;

-----调用包里的fun
SELECT E.*,
       PKG_TEST1.FUN_2(E.HIREDATE) 社保缴纳
FROM EMP E;

/* 当你看到调用sp或者fun前面有一个 XXX. 的时候，那个这个 XXX 就是一个包名。
     就可以根据 XXX 找到对应的包。以及里面的内容
   或者 右键这个 XXX，选择“查看说明与主体”
*/

---我们在项目组中提交包的格式：包头跟包体是分为两个sql文件提交的

--小练习一把：创建一个包，里面包含2个sp，2个fun
sp_1：打印hello world
SP_2：打印部门10的员工的工号和姓名
fun_1：比较2个值的大小，返回较大的值，如果一样大，返回1024
fun_2：返回任务是否超时，时间超过3天则显示 “超时”，否则显示 “正常”

--创建包头
CREATE OR REPLACE PACKAGE PKG_TEST2 IS
  PROCEDURE SP_1;
  PROCEDURE SP_2;
  FUNCTION FUN_1(X NUMBER, Y NUMBER) RETURN NUMBER;
  FUNCTION FUN_2(P_DATE1 DATE, P_DATE2 DATE) RETURN VARCHAR2;
END;

CREATE OR REPLACE PACKAGE BODY PKG_TEST2 IS
  PROCEDURE SP_1 IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('HELLO WORLD');
  END SP_1;
  PROCEDURE SP_2 IS
    CURSOR C_DEPT10 IS
      SELECT EMPNO, ENAME FROM EMP WHERE DEPTNO = 10;
  BEGIN
    FOR X IN C_DEPT10 LOOP
      DBMS_OUTPUT.PUT_LINE('工号:' || X.EMPNO || ' 姓名:' || X.ENAME);
    END LOOP;
  END SP_2;
  FUNCTION FUN_1(X NUMBER, Y NUMBER) RETURN NUMBER IS
  BEGIN
    IF X > Y THEN
      RETURN X;
    ELSIF X < Y THEN
      RETURN Y;
    ELSE
      RETURN 1024;
    END IF;
  END FUN_1;
  FUNCTION FUN_2(P_DATE1 DATE, P_DATE2 DATE) RETURN VARCHAR2 IS
    V_MINUS NUMBER;
    V_CHAR  VARCHAR2(6);
  BEGIN
    V_MINUS := ABS(P_DATE1 - P_DATE2);
  
    IF V_MINUS > 3 THEN
      V_CHAR := '超时';
    ELSE
      V_CHAR := '正常';
    END IF;
  
    RETURN V_CHAR;
  END FUN_2;
END;

--测试一下
BEGIN
  PKG_TEST2.SP_1;
  PKG_TEST2.SP_2;
END;

SELECT PKG_TEST2.FUN_1(1, 2),
       PKG_TEST2.FUN_1(1, 1),
       PKG_TEST2.FUN_1(10, 2),
       PKG_TEST2.FUN_2(SYSDATE, ADD_MONTHS(SYSDATE, 5)),
       PKG_TEST2.FUN_2(SYSDATE, SYSDATE + 3)
  FROM DUAL;