存储过程———＞＞＞包(两个存储过程一个存储函数)

本文链接：https://blog.csdn.net/m0_64050534/article/details/122828190

/*包*/
SCOTT.EMP
DBMS_OUTPUT.PUT_LINE()

CREATE OR REPLACE PACKAGE PKG_BAO IS
--过程：加工部门信息--
PROCEDURE SP_DEPT_INFO (
I_DEPTNO IN NUMBER , --部门编号
O_RESULT OUT NUMBER --运行结果 0-运行成功 1-运行失败
);

--过程：恢复EMP表--
PROCEDURE SP_EMP_REBUILED;

--函数：判断某年为闰年还是平年--
FUNCTION F_YEAR (
I_YEAR NUMBER )
RETURN VARCHAR2;

END PKG_BAO;

CREATE OR REPLACE PACKAGE BODY PKG_BAO IS
--过程：加工部门信息--
PROCEDURE SP_DEPT_INFO (
I_DEPTNO IN NUMBER , --部门编号
O_RESULT OUT NUMBER --运行结果 0-运行成功 1-运行失败
)
IS
/*======================================
程序说明
程序名称：SP_DEPT_INFO
创建人：zhangjibin
创建时间：2021-12-23
功能介绍：统计部门信息
目标表：DEPT_INFO
来源表：DEPT --部门信息表
EMP --员工信息表
参数：I_DEPTNO 部门编号
O_RESULT 运行结果 0-运行成功 1-运行失败
修改记录：
修改时间修改人修改内容

======================================*/
V_CT NUMBER(1) ; --数量统计
V_SP_NAME VARCHAR2(30) := 'SP_DEPT_INFO' ; --程序名称
V_STEP_CODE NUMBER(3) := 0 ; --步骤编号
V_STEP_DESC VARCHAR2(300) ; --步骤描述
V_SQL CLOB ; --SQL代码（记录&运行）
V_RESULTT VARCHAR2(300) := 'SUCCESS' ; --执行结果
V_TS VARCHAR2(30) ; --时间戳
V_SQLL VARCHAR2(2000) ; --SQL代码（运行）
BEGIN
--STEP1--
BEGIN
V_STEP_CODE := V_STEP_CODE+1;
V_STEP_DESC := '清除历史数据';

--核心代码 START--
V_SQL := 'DELETE FROM DEPT_INFO WHERE DEPTNO = '||I_DEPTNO;
EXECUTE IMMEDIATE V_SQL;
COMMIT;
--核心代码 END--

V_TS := TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF3');
--记录日志--
INSERT INTO ETL_LOG
VALUES(V_SP_NAME,V_STEP_CODE,V_STEP_DESC,V_SQL,V_RESULTT,V_TS);
COMMIT;
END;

--STEP2--
BEGIN
V_STEP_CODE := V_STEP_CODE+1;
V_STEP_DESC := '统计指定部门的信息到目标表';

--核心代码 START--
V_SQL := '
INSERT INTO DEPT_INFO
SELECT D.DEPTNO ,
D.DNAME ,
COUNT(E.EMPNO) ,
SUM(NVL(E.SAL,0)) ,
AVG(NVL(E.SAL,0))
FROM DEPT D
LEFT JOIN EMP E
ON D.DEPTNO = E.DEPTNO
WHERE D.DEPTNO = '||I_DEPTNO||'
GROUP BY D.DEPTNO,D.DNAME';
EXECUTE IMMEDIATE V_SQL;
COMMIT;
--核心代码 END--

V_TS := TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF3');
--记录日志--
INSERT INTO ETL_LOG
VALUES(V_SP_NAME,V_STEP_CODE,V_STEP_DESC,V_SQL,V_RESULTT,V_TS);
COMMIT;
END;

O_RESULT := 0;

--异常处理--
EXCEPTION
WHEN OTHERS THEN
V_TS := TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF3');
V_RESULTT := 'FAILED'||SQLERRM;
O_RESULT := 1;
--记录日志--
INSERT INTO ETL_LOG VALUES(V_SP_NAME,V_STEP_CODE,V_STEP_DESC,V_SQL,V_RESULTT,V_TS);
COMMIT;
END SP_DEPT_INFO;

--过程：恢复EMP表--
PROCEDURE SP_EMP_REBUILED
IS
/*======================================
程序说明
程序名称：SP_EMP_REBUILED
创建人：zhangjibin
创建时间：2021-12-22
功能介绍：用于恢复EMP表
修改记录：
修改时间修改人修改内容
2021-12-23 peiqi 补充了日志
...
======================================*/
V_CT NUMBER(1) ; --数量统计
V_SP_NAME VARCHAR2(30) := 'SP_EMP_REBUILED' ; --程序名称
V_STEP_CODE NUMBER(3) := 0 ; --步骤编号
V_STEP_DESC VARCHAR2(300) ; --步骤描述
V_SQL CLOB ; --SQL代码（记录&运行）
V_RESULTT VARCHAR2(300) := 'SUCCESS' ; --执行结果
V_TS VARCHAR2(30) ; --时间戳
V_SQLL VARCHAR2(2000) ; --SQL代码（运行）
BEGIN
--STEP1--
BEGIN
V_STEP_CODE := V_STEP_CODE+1;
V_STEP_DESC := '删除EMP表';

--核心代码 START--
SELECT COUNT(*) INTO V_CT FROM USER_TABLES WHERE TABLE_NAME = 'EMP';
IF V_CT = 1 THEN
V_SQL := 'DROP TABLE EMP';
EXECUTE IMMEDIATE V_SQL;
END IF;
--核心代码 END--

V_TS := TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF3');
--记录日志--
INSERT INTO ETL_LOG
VALUES(V_SP_NAME,V_STEP_CODE,V_STEP_DESC,V_SQL,V_RESULTT,V_TS);
COMMIT;
END;

--STEP2--
BEGIN
V_STEP_CODE := V_STEP_CODE+1;
V_STEP_DESC := '创建EMP表';

--核心代码 START--
V_SQL :='
CREATE TABLE EMP (
EMPNO NUMBER(4) ,
ENAME VARCHAR2(10),
JOB VARCHAR2(9) ,
MGR NUMBER(4) , --modified by zjb at 20211223 精度调为4
HIREDATE DATE ,
SAL NUMBER(7,2) ,
COMM NUMBER(7,2) ,
DEPTNO NUMBER(2) ) ';
EXECUTE IMMEDIATE V_SQL;
--核心代码 END--

V_TS := TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF3');
--记录日志--
INSERT INTO ETL_LOG
VALUES(V_SP_NAME,V_STEP_CODE,V_STEP_DESC,V_SQL,V_RESULTT,V_TS);
COMMIT;
END;

--STEP3--
BEGIN
V_STEP_CODE := V_STEP_CODE+1;
V_STEP_DESC := '插入数据';

--核心代码 START--
V_SQLL := 'INSERT INTO EMP
VALUES (7369, ''SMITH'', ''CLERK'', 7902, TO_DATE(''17-12-1980'', ''dd-mm-yyyy''), 800.00, NULL, 20)';
V_SQL := V_SQLL;
EXECUTE IMMEDIATE V_SQLL;

V_SQLL := 'INSERT INTO EMP
VALUES (7499, ''ALLEN'', ''SALESMAN'', 7698, TO_DATE(''20-02-1981'', ''dd-mm-yyyy''), 1600.00, 300.00, 30)';
V_SQL := V_SQL||';'||CHR(13)||V_SQLL;
EXECUTE IMMEDIATE V_SQLL;

V_SQLL := 'INSERT INTO EMP
VALUES (7521, ''WARD'', ''SALESMAN'', 7698, TO_DATE(''22-02-1981'', ''dd-mm-yyyy''), 1250.00, 500.00, 30)';
V_SQL := V_SQL||';'||CHR(13)||V_SQLL;
EXECUTE IMMEDIATE V_SQLL;

V_SQLL := 'INSERT INTO EMP
VALUES (7566, ''JONES'', ''MANAGER'', 7839, TO_DATE(''02-04-1981'', ''dd-mm-yyyy''), 2975.00, NULL, 20)';
V_SQL := V_SQL||';'||CHR(13)||V_SQLL;
EXECUTE IMMEDIATE V_SQLL;

V_SQLL := 'INSERT INTO EMP
VALUES (7654, ''MARTIN'', ''SALESMAN'', 7698, TO_DATE(''28-09-1981'', ''dd-mm-yyyy''), 1250.00, 1400.00, 30)';
V_SQL := V_SQL||';'||CHR(13)||V_SQLL;
EXECUTE IMMEDIATE V_SQLL;

V_SQLL := 'INSERT INTO EMP
VALUES (7698, ''BLAKE'', ''MANAGER'', 7839, TO_DATE(''01-05-1981'', ''dd-mm-yyyy''), 2850.00, NULL, 30)';
V_SQL := V_SQL||';'||CHR(13)||V_SQLL;
EXECUTE IMMEDIATE V_SQLL;

V_SQLL := 'INSERT INTO EMP
VALUES (7782, ''CLARK'', ''MANAGER'', 7839, TO_DATE(''09-06-1981'', ''dd-mm-yyyy''), 2450.00, NULL, 10)';
V_SQL := V_SQL||';'||CHR(13)||V_SQLL;
EXECUTE IMMEDIATE V_SQLL;

V_SQLL := 'INSERT INTO EMP
VALUES (7788, ''SCOTT'', ''ANALYST'', 7566, TO_DATE(''19-04-1987'', ''dd-mm-yyyy''), 3000.00, NULL, 20)';
V_SQL := V_SQL||';'||CHR(13)||V_SQLL;
EXECUTE IMMEDIATE V_SQLL;

V_SQLL := 'INSERT INTO EMP
VALUES (7839, ''KING'', ''PRESIDENT'', NULL, TO_DATE(''17-11-1981'', ''dd-mm-yyyy''), 5000.00, NULL, 10)';
V_SQL := V_SQL||';'||CHR(13)||V_SQLL;
EXECUTE IMMEDIATE V_SQLL;

V_SQLL := 'INSERT INTO EMP
VALUES (7844, ''TURNER'', ''SALESMAN'', 7698, TO_DATE(''08-09-1981'', ''dd-mm-yyyy''), 1500.00, 0.00, 30)';
V_SQL := V_SQL||';'||CHR(13)||V_SQLL;
EXECUTE IMMEDIATE V_SQLL;

V_SQLL := 'INSERT INTO EMP
VALUES (7876, ''ADAMS'', ''CLERK'', 7788, TO_DATE(''23-05-1987'', ''dd-mm-yyyy''), 1100.00, NULL, 20)';
V_SQL := V_SQL||';'||CHR(13)||V_SQLL;
EXECUTE IMMEDIATE V_SQLL;

V_SQLL := 'INSERT INTO EMP
VALUES (7900, ''JAMES'', ''CLERK'', 7698, TO_DATE(''03-12-1981'', ''dd-mm-yyyy''), 950.00, NULL, 30)';
V_SQL := V_SQL||';'||CHR(13)||V_SQLL;
EXECUTE IMMEDIATE V_SQLL;

V_SQLL := 'INSERT INTO EMP
VALUES (7902, ''FORD'', ''ANALYST'', 7566, TO_DATE(''03-12-1981'', ''dd-mm-yyyy''), 3000.00, NULL, 20)';
V_SQL := V_SQL||';'||CHR(13)||V_SQLL;
EXECUTE IMMEDIATE V_SQLL;

V_SQLL := 'INSERT INTO EMP
VALUES (7934, ''MILLER'', ''CLERK'', 7782, TO_DATE(''23-01-1982'', ''dd-mm-yyyy''), 1300.00, NULL, 10)';
V_SQL := V_SQL||';'||CHR(13)||V_SQLL||';';
EXECUTE IMMEDIATE V_SQLL;

COMMIT;
--核心代码 END--

V_TS := TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF3');
--记录日志--
INSERT INTO ETL_LOG
VALUES(V_SP_NAME,V_STEP_CODE,V_STEP_DESC,V_SQL,V_RESULTT,V_TS);
COMMIT;
END;

--STEP4--
BEGIN
V_STEP_CODE := V_STEP_CODE+1;
V_STEP_DESC := '创建约束';

--核心代码 START--
V_SQLL := 'ALTER TABLE EMP ADD CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)';
V_SQL := V_SQLL;
EXECUTE IMMEDIATE V_SQLL;

V_SQLL := 'ALTER TABLE EMP ADD CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO)
REFERENCES DEPT(DEPTNO)';
V_SQL := V_SQL||';'||CHR(13)||V_SQLL||';';
EXECUTE IMMEDIATE V_SQLL;
--核心代码 END--

V_TS := TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF3');
--记录日志--
INSERT INTO ETL_LOG
VALUES(V_SP_NAME,V_STEP_CODE,V_STEP_DESC,V_SQL,V_RESULTT,V_TS);
COMMIT;
END;

--异常处理--
EXCEPTION
WHEN OTHERS THEN
V_TS := TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF3');
V_RESULTT := 'FAILED'||SQLERRM;
--记录日志--
INSERT INTO ETL_LOG VALUES(V_SP_NAME,V_STEP_CODE,V_STEP_DESC,V_SQL,V_RESULTT,V_TS);
COMMIT;
RAISE;
END SP_EMP_REBUILED;

--函数：判断某年为闰年还是平年--
FUNCTION F_YEAR (
I_YEAR NUMBER )
RETURN VARCHAR2
IS
BEGIN
IF MOD(I_YEAR,4) = 0 AND MOD(I_YEAR,100) <> 0 OR MOD(I_YEAR,400) = 0 THEN
RETURN '闰年';
ELSE
RETURN '平年';
END IF;
END F_YEAR;

END PKG_BAO;