一.包的组成介绍
- 包头(package):包头部分申明包内数据类型,常量,变量,游标,子程序和异常错误处理,这些元素为包的公有元素。
- 包主体(package body):包主体则是包定义部分的具体实现,它负责为包头中所声明子程序提供具体的实现,在包主体中还可以声明包的私有元素。
- 包头和包主体分开编译,并作为两个分开的对象分别存放在数据库字典中。
二.Oracle包与Java接口的比较
可以这样理解,把PACKAGE包头理解为JAVA中的接口,把PACKAGE BODY理解为JAVA中实现这个接口的类,而里面的存储过程和函数就相当于类中的方法了。调用执行直接在存储过程中以(包名.存储过程/函数)即可。也方便Java程序直接调用。
三.开发示例
3.1 封装单一存储过程的包
3.1.1 包
CREATE OR REPLACE PACKAGE MES_APP_PKG IS
TYPE MESA_CURSOR IS REF CURSOR;
-- Author : LINHAIY
-- Created : 2019-05-24 9:42:08
-- Purpose : Used for mes4 app kpis
-- Public type declarations
/*
type <TypeName> is <Datatype>;
-- Public constant declarations
<ConstantName> constant <Datatype> := <Value>;
-- Public variable declarations
<VariableName> <Datatype>;
-- Public function and procedure declarations
function <FunctionName>(<Parameter> <Datatype>) return <Datatype>;
*/
--user login
PROCEDURE MESA_UER_LOGIN(P_USER_NAME VARCHAR2, P_PASSWORD VARCHAR2, X_STATUS OUT VARCHAR2, X_CURSOR OUT MESA_CURSOR);
PROCEDURE MESA_GET_OLOFL_AMOUNT_P(P_LINE_ID NUMBER, X_ON_AMOUNT OUT NUMBER, X_OFF_AMOUNT OUT NUMBER);
--PROCEDURE MESA_GET_OLOFL_AMOUNT_P(P_LINE_ID NUMBER, X_CURSOR OUT MESA_CURSOR);
PROCEDURE MESA_PLAN_P(P_DATE VARCHAR2);
PROCEDURE MESA_KPI_1001_P(P_DATE VARCHAR2);
--驱动函数
PROCEDURE DRIVE_FUNC;
END MES_APP_PKG;
3.1.2 包的主体(实现)
CREATE OR REPLACE PACKAGE BODY MES_APP_PKG IS
-- Private type declarations
--type <TypeName> is <Datatype>;
-- Private constant declarations
/*
<ConstantName> constant <Datatype> := <Value>;
-- Private variable declarations
<VariableName> <Datatype>;
-- Function and procedure implementations
function <FunctionName>(<Parameter> <Datatype>) return <Datatype> is
<LocalVariable> <Datatype>;
begin
<Statement>;
return(<Result>);
end;
*/
--begin
-- Initialization
--<Statement>;
PROCEDURE MESA_JOB_LOG_P(P_DATE VARCHAR2, P_MODULE VARCHAR2, P_MSG VARCHAR2) IS
BEGIN
INSERT INTO MESA_JOB_LOG(EXEC_DATE, FUNC_NAME, ERR_MESSAGE)
VALUES(P_DATE, P_MODULE, P_MSG);
COMMIT;
END MESA_JOB_LOG_P;
--user login
/*MESA_UER_LOGIN: IF LOGIN FAILED: X_STATUS=0 X_CURSOR=NULL;
IF LOGIN SUCCESS: X_STATUS=1 X_XURSOR = RESULT_SET
*/
PROCEDURE MESA_UER_LOGIN(P_USER_NAME VARCHAR2, P_PASSWORD VARCHAR2, X_STATUS OUT VARCHAR2, X_CURSOR OUT MESA_CURSOR) IS
BEGIN
X_STATUS := 0 ;
BEGIN
SELECT COUNT(*) INTO X_STATUS FROM SYS_USER U
WHERE U.LOGIN_NAME = P_USER_NAME
AND U.PASSWORD = P_PASSWORD;
EXCEPTION
WHEN OTHERS THEN
X_STATUS :=0;
END ;
IF X_STATUS = 0 THEN
--LOGIN FIALED
OPEN X_CURSOR FOR SELECT NULL FROM DUAL;
RETURN;
ELSE
X_STATUS := 1;
OPEN X_CURSOR FOR
SELECT DISTINCT U.LOGIN_NAME, RR.RESOURCE_ID, RR.RESOURCE_NAME, RR.KEY_TYPE
FROM SYS_ROLE_RESOURCES RR, SYS_USER_ROLE R, SYS_USER U
WHERE U.ID = R.USER_ID
AND R.ROLE_ID = RR.ROLE_ID
AND U.LOGIN_NAME = P_USER_NAME;
END IF;
END MESA_UER_LOGIN;
PROCEDURE MESA_GET_OLOFL_AMOUNT_P(P_LINE_ID NUMBER, X_ON_AMOUNT OUT NUMBER, X_OFF_AMOUNT OUT NUMBER) IS
/*PROCEDURE MESA_GET_OLOFL_AMOUNT_P(P_LINE_ID NUMBER, X_CURSOR OUT MYCURSOR) IS
X_ON_AMOUNT NUMBER :=0;
X_OF_AMOUNT NUMBER :=0;*/
BEGIN
--当前上线数量
BEGIN
SELECT NVL(COUNT(DISTINCT PPS.PRODUCT_CODE),0) INTO X_ON_AMOUNT
FROM pln.PLN_PRODUCT_STATUS PPS
WHERE PPS.LINE_ID = P_LINE_ID --200000359 --(按照所选生产线查询)
AND PPS.STATUS_CODE = '1010'
AND PPS.STATUS_DATE >= TRUNC(SYSDATE)
AND PPS.STATUS_DATE < TRUNC(SYSDATE) + 1;
EXCEPTION
WHEN OTHERS THEN
X_ON_AMOUNT := 0 ;
END ;
--当前完工数量
BEGIN
SELECT NVL(COUNT(DISTINCT PPS.PRODUCT_CODE),0) INTO X_OFF_AMOUNT
FROM pln.PLN_PRODUCT_STATUS PPS
WHERE PPS.LINE_ID = P_LINE_ID --200000359 --(按照所选生产线查询)
AND PPS.STATUS_CODE = '1070'
AND PPS.STATUS_DATE >= TRUNC(SYSDATE)
AND PPS.STATUS_DATE < TRUNC(SYSDATE) + 1;
EXCEPTION
WHEN OTHERS THEN
X_OFF_AMOUNT := 0;
END ;
--OPEN X_CURSOR FOR
-- SELECT X_ON_AMOUNT CURRENT_ONLINE_AMOUNT, X_OFF_AMOUNT CURRENT_OFFLINE_AMOUNT FROM DUAL;
END MESA_GET_OLOFL_AMOUNT_P;
PROCEDURE MESA_PLAN_OFL_AMT_YM_P(P_DATE VARCHAR2, P_TYPE VARCHAR2) IS
V_DATE_START DATE;
V_DATE_END DATE;
BEGIN
V_DATE_END := TO_DATE(P_DATE, 'YYYY-MM-DD') - 1;
IF P_TYPE = 'M' THEN
V_DATE_START := TRUNC(TO_DATE(P_DATE, 'YYYY-MM-DD'), 'MM');
MERGE INTO MESA_KPI_PLANS MKP
USING (SELECT PPS.LINE_ID,
P_DATE S_DATE,
--TRUNC(PPS.STATUS_DATE) S_DATE,
COUNT(DISTINCT PPS.PRODUCT_CODE) S_AMT
FROM PLN.PLN_PRODUCT_STATUS PPS
WHERE PPS.STATUS_CODE = '1070'
AND PPS.STATUS_DATE >= V_DATE_START
AND PPS.STATUS_DATE < V_DATE_END --TRUNC(SYSDATE) + 1
GROUP BY PPS.LINE_ID/*, TRUNC(PPS.STATUS_DATE)*/) KV
ON (MKP.KPI_DATE = KV.S_DATE AND MKP.LINE_ID = KV.LINE_ID)
WHEN MATCHED THEN
UPDATE SE