oracle 是用于邏輯組合多個過程或函數.
一個oracle包包含兩部份:
1.包規范
相當于java里的接口.
-- 包規范
CREATE OR REPLACE PACKAGE pkg_edm
IS
age NUMBER := 30;
PROCEDURE add_test_hui(
in_id IN NUMBER,
in_value IN VARCHAR2);
FUNCTION getValueById(
in_id IN NUMBER) RETURN VARCHAR2;
END pkg_edm;
2.包體
相當于java里的接口實現類.
-- 包體
CREATE OR REPLACE PACKAGE BODY pkg_edm
IS
-- 查詢函數
FUNCTION getValueById(
in_id IN NUMBER) RETURN VARCHAR2
IS
rtn_value VARCHAR2(64) := '';
BEGIN
SELECT VALUE INTO rtn_value FROM test_hui WHERE ID=in_id;
RETURN rtn_value;
EXCEPTION
WHEN OTHERS THEN
RETURN rtn_value;
END getValueById;
-- 插入記錄過程
PROCEDURE add_test_hui(
in_id IN NUMBER,
in_value IN VARCHAR2)
IS
BEGIN
INSERT INTO test_hui (ID,VALUE) VALUES(in_id,in_value);
COMMIT;
END add_test_hui;
END pkg_edm;
包的調用:
1.測試調用包過程
--測試調用包過程
BEGIN
pkg_edm.add_test_hui(99999,'asdfasdf');
END;
2.測試調用包函數
--測試調用包函數
DECLARE
temp_value VARCHAR2(64);
BEGIN
temp_value := pkg_edm.getValueById(8713);
dbms_output.put_line(temp_value);
END;