創(chuàng)建包時(shí),包分為兩個(gè)部分,包頭和body部分,在包頭部分聲明包內(nèi)容,可以包括過程,函數(shù)啊等,body部分是包內(nèi)容的詳細(xì)部分,你的過程就寫在body部分,找找包定義看看就知道了。
創(chuàng)新互聯(lián)-云計(jì)算及IDC服務(wù)提供商,涵蓋公有云、IDC機(jī)房租用、重慶服務(wù)器托管、等保安全、私有云建設(shè)等企業(yè)級互聯(lián)網(wǎng)基礎(chǔ)服務(wù),溝通電話:18980820575
--------------------------------
這個(gè)簡單啊,再創(chuàng)建跟原來一樣的包名稱 用replace關(guān)鍵字啊!
create or replace package.....
數(shù)據(jù)庫在編譯時(shí),會自動查找替換,就可以將procedure加進(jìn)去了!
驗(yàn)證下不就知道了。
如果你的過程和函數(shù)都沒有語法錯(cuò)誤就是對的。
包:包含表頭和BODY信息。
將你的函數(shù)和過程頭部信息放到包中定義。調(diào)用就可以直接調(diào)用包里面的定義。
比如:
包:你的包名a.spc
CREATE OR REPLACE PACKAGE a
IS
PROCEDURE CREATE_AE_OUT_FILE (p_gr_hdr_id IN varchar) ;
END;
body:
CREATE OR REPLACE PACKAGE BODY a
IS
PROCEDURE CREATE_AE_OUT_FILE(p_gr_hdr_id IN varchar)
IS
v_gr_hdr_id cosgx.cos_gr_hdr.gr_hdr_id%type;
vfile varchar2(200);
BEGIN
--你的實(shí)現(xiàn)邏輯
END;
END;
函數(shù)也是一樣的。都需要在包里面定義
用SQLPLUS就可以自動生成的
--創(chuàng)建包頭
CREATE OR REPLACE PACKAGE ELS.pkg_proxy
AS
TYPE cursortype IS REF CURSOR;
-- 得到交接確認(rèn)代理人數(shù)據(jù)
PROCEDURE get_proxy_info (
v_emp_no IN VARCHAR2, --當(dāng)前登錄用戶
v_to_emp_no IN VARCHAR2, --代理人工號
v_to_emp_name IN VARCHAR2, --代理人姓名
v_proxy_date1 IN VARCHAR2, --代理開始時(shí)間: yyyy/mm/dd
v_proxy_date2 IN VARCHAR2, --代理結(jié)束時(shí)間: yyyy/mm/dd
v_from_emp_no IN VARCHAR2, --被代理人工號
v_from_emp_name IN VARCHAR2, --被代理人姓名
p_cursor OUT cursortype
);
END pkg_proxy;
--創(chuàng)建包頭
CREATE OR REPLACE PACKAGE BODY ELS.pkg_proxy
AS
-- 得到交接確認(rèn)代理人數(shù)據(jù)
PROCEDURE get_proxy_info (
v_emp_no IN VARCHAR2, --當(dāng)前登錄用戶
v_to_emp_no IN VARCHAR2, --代理人工號
v_to_emp_name IN VARCHAR2, --代理人姓名
v_proxy_date1 IN VARCHAR2, --代理開始時(shí)間: yyyy/mm/dd
v_proxy_date2 IN VARCHAR2, --代理結(jié)束時(shí)間: yyyy/mm/dd
v_from_emp_no IN VARCHAR2, --被代理人工號
v_from_emp_name IN VARCHAR2, --被代理人姓名
p_cursor OUT cursortype
)
AS
BEGIN
OPEN p_cursor FOR
WITH v AS
(
--查找當(dāng)前登錄人是否為管理員
SELECT *
FROM employee
WHERE dept_id IN (
SELECT ID
FROM department
START WITH ID =
(SELECT dept_id
FROM employee
WHERE emp_no = v_emp_no
AND is_admin IN (
SELECT r_value
FROM role_conditions
WHERE r_key =
'ProxySearch')
UNION
SELECT dept_id
FROM employee_add
WHERE emp_no = v_emp_no
AND is_admin IN (
SELECT r_value
FROM role_conditions
WHERE r_key =
'ProxySearch'))
CONNECT BY PRIOR ID = parent_id)
UNION
SELECT *
FROM employee
WHERE emp_no = v_emp_no)
SELECT DISTINCT *
FROM (SELECT a.currentuser, a.toagrentuser,
DECODE (a.agent_type,
'A', '當(dāng)前交接確認(rèn)代理',
'B', '將來離職簽核代理',
'C', '當(dāng)前離職簽核代理',
'D', '將來交接確認(rèn)代理',
''
) agent_type,
a.from_date, a.TO_DATE,
DECODE
(a.to_date1,
'', '無',
TO_CHAR (a.TO_DATE,
'yyyy/mm/dd am hh:mi:ss'
)
) to_date1,
a.flowinfo, b.emp_cname AS currentusername,
b1.emp_cname AS toagrentusername, workitem
FROM (
--當(dāng)前交接確認(rèn)代理
SELECT SUBSTR
(x.currentuser,
1,
INSTR (x.currentuser, '(') - 1
) AS currentuser,
SUBSTR
(x.toagrentuser,
1,
INSTR (x.toagrentuser, '(') - 1
) AS toagrentuser,
'A' AS agent_type,
x.create_date AS from_date,
x.create_date AS TO_DATE,
NULL AS to_date1, x.workid,
NULL AS formid, y.NAME workitem,
(SELECT e.emp_no
|| '('
|| e.emp_cname
|| ')'
FROM employee e
WHERE e.emp_no = y.emp_no)
AS flowinfo
FROM currentworkagrent x,
worktransferitem_m y
WHERE x.workid = y.ID
UNION ALL
--將來交接確認(rèn)代理
SELECT SUBSTR
(currentuser,
1,
INSTR (currentuser, '(') - 1
) AS currentuser,
SUBSTR
(toagrent,
1,
INSTR (toagrent, '(') - 1
) AS toagrent,
'D' AS agent_type, starttime,
endtime, endtime AS to_date1, NULL,
NULL, NULL, NULL
FROM futureagrent) a,
employee b,
employee b1
WHERE a.currentuser = b.emp_no(+)
AND a.toagrentuser = b1.emp_no(+)) a
WHERE currentuser IN (SELECT emp_no
FROM v)
AND (currentuser = v_from_emp_no OR v_from_emp_no IS NULL
)
AND ( currentusername = v_from_emp_name
OR v_from_emp_name IS NULL
)
AND (toagrentuser = v_to_emp_no OR v_to_emp_no IS NULL)
AND ( toagrentusername = v_to_emp_name
OR v_to_emp_name IS NULL
)
AND from_date =
TO_DATE (NVL (v_proxy_date2, '2099/12/31'),
'yyyy/mm/dd'
)
AND TO_DATE =
TO_DATE (NVL (v_proxy_date1, '1000/12/31'),
'yyyy/mm/dd'
)
ORDER BY a.agent_type, a.from_date DESC;
END;
END pkg_proxy;
25、包
(1)定義包頭
語法:
CREATE [OR REPLACE] PACKAGE [schame_name.]包名
IS | AS
包描述
包描述:可以是變量、常量及數(shù)據(jù)類型定義和游標(biāo)定義,也可以是存儲過程、函數(shù)定義和參數(shù)列表返回值類型。
例:
CREATE OR REPLACE PACKAGE employeePackage
AS
--類型定義
Type empRecType is record(
Empno number(4),
Salary number
)
--變量定義
p1 varchar2(20);
type t_departmentNo table is table of dept.deptno%type
index by binary_integer;
--游標(biāo)定義
Cursor order_sal retrurn EmpRecType;
--聘用員工過程
Procedure HireEmployee(p_EmpNo emp.empno%type);
End employeePackage;
例:
CREATE OR REPLACE PACKAGE scott.pkg_displayproduct
IS
procedure prc_getproductinfo
(
pid scott.product.prodid%type,
pname out scott.product.prodname%type,
ctname out scott.product.CATEGORYID%type,
dscp out scott.product.description%type
);
function fun_getstock
(
pid scott.product.prodid%type
)
return number;
END pkg_displayproduct;
(2)定義包體
語法:
CREATE OR REPLACE PACKAGE BODY [schame_name.]包名
IS | AS
包體描述;
END 包名;
包名:將要創(chuàng)建的包名稱,該名稱可以和包頭所在的包名相同,也可以不同。
包體描述:游標(biāo)、存儲過程或者函數(shù)的定義
包體是可選的,如果在包頭中沒有聲明任何存儲過程或者函數(shù),則包體就不存在,即使在包頭有變量、游標(biāo)或者類型的聲明
(3)引用包中對象
BEGIN
[schame_name.][包名.]對象名;
END;