oracle 是用于邏輯組合多個(gè)過(guò)程或函數(shù).
一個(gè)oracle包包含兩部份:
1.包規(guī)范
相當(dāng)于java里的接口.
-- 包規(guī)范
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.包體
相當(dāng)于java里的接口實(shí)現(xiàn)類.
-- 包體
CREATE OR REPLACE PACKAGE BODY pkg_edm
IS
-- 查詢函數(shù)
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;
-- 插入記錄過(guò)程
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;
包的調(diào)用:
1.測(cè)試調(diào)用包過(guò)程
--測(cè)試調(diào)用包過(guò)程
BEGIN
pkg_edm.add_test_hui(99999,'asdfasdf');
END;
2.測(cè)試調(diào)用包函數(shù)
--測(cè)試調(diào)用包函數(shù)
DECLARE
temp_value VARCHAR2(64);
BEGIN
temp_value := pkg_edm.getValueById(8713);
dbms_output.put_line(temp_value);
END;