189 8069 5689
包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成
10年积累的成都网站设计、成都做网站经验，可以快速应对客户对网站的新想法和需求。提供各种问题对应的解决方案。让选择我们的客户得到更好、更有力的网络服务。我虽然不认识你，你也不认识我。但先做网站设计后付款的网站建设流程，更有普宁免费网站建设让你可以放心的选择与我们合作。
1.创建包
--创建一个包sp_package
create package sp_package is
--声明该包有一个过程和函数，（没有实现）
procedure update_sal(name varchar2,newsal number);
function annual_nicome(name varchar2) return number;
end;
--创建包体（用于实现已经声明的函数和过程）
create package body sp_package is
procedure update_sal(name varchar2,newsal number)
is
BEGIN
UPDATE emp
SET sal = newsal
WHERE ename = name;
END;
function annual_income(name varchar2)
return number is
annual_salary number;
BEGIN
SELECT sal * 12 + Nvl(comm,0)
INTO annual_salary
FROM emp
WHERE ename = name;
RETURN annual_salary;
END;
end;
--调用包中的内容
exec sp_package.update_sal('name',number);
--创建包头
CREATE OR REPLACE PACKAGE ELS.pkg_proxy
AS
TYPE cursortype IS REF CURSOR;
-- 得到交接确认代理人数据
PROCEDURE get_proxy_info (
v_emp_no IN VARCHAR2, --当前登录用户
v_to_emp_no IN VARCHAR2, --代理人工号
v_to_emp_name IN VARCHAR2, --代理人姓名
v_proxy_date1 IN VARCHAR2, --代理开始时间: yyyy/mm/dd
v_proxy_date2 IN VARCHAR2, --代理结束时间: yyyy/mm/dd
v_from_emp_no IN VARCHAR2, --被代理人工号
v_from_emp_name IN VARCHAR2, --被代理人姓名
p_cursor OUT cursortype
);
END pkg_proxy;
--创建包头
CREATE OR REPLACE PACKAGE BODY ELS.pkg_proxy
AS
-- 得到交接确认代理人数据
PROCEDURE get_proxy_info (
v_emp_no IN VARCHAR2, --当前登录用户
v_to_emp_no IN VARCHAR2, --代理人工号
v_to_emp_name IN VARCHAR2, --代理人姓名
v_proxy_date1 IN VARCHAR2, --代理开始时间: yyyy/mm/dd
v_proxy_date2 IN VARCHAR2, --代理结束时间: 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
(
--查找当前登录人是否为管理员
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', '当前交接确认代理',
'B', '将来离职签核代理',
'C', '当前离职签核代理',
'D', '将来交接确认代理',
''
) 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 (
--当前交接确认代理
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
--将来交接确认代理
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;
在存储过程或函数里调用oracle包的话，首先要有执行这个包的权限；如果包属于其他的用户（不是系统包），调用时：用户名.包名.存储过程(参数)名或者
变量：=用户名.包名.函数(参数)；因为函数有返回值，变量类型要跟函数返回值的类型一致。
在command
下调用的话：exec
用户名.包名.存储过程名(参数)；select
用户名.包名.函数名(参数)
from
dual；就可以了。包属于当期用户可以不写用户名。