表：table
創(chuàng)新互聯(lián)是專業(yè)的通州網(wǎng)站建設(shè)公司，通州接單;提供網(wǎng)站設(shè)計(jì)制作、做網(wǎng)站,網(wǎng)頁設(shè)計(jì),網(wǎng)站設(shè)計(jì),建網(wǎng)站,PHP網(wǎng)站建設(shè)等專業(yè)做網(wǎng)站服務(wù);采用PHP框架,可快速的進(jìn)行通州網(wǎng)站開發(fā)網(wǎng)頁制作和功能擴(kuò)展;專業(yè)做搜索引擎喜愛的網(wǎng)站,專業(yè)的做網(wǎng)站團(tuán)隊(duì),希望更多企業(yè)前來合作!
存儲(chǔ)過程：procedure
觸發(fā)器：trigger
包：package
包體：packagebody
函數(shù)：function
視圖：view
--表create table my_toys( id varchar2(5), name varchar2(20), price number);--插入測(cè)試數(shù)據(jù)insert into my_toys values('G001','電熱毯',145);insert into my_toys values('G002','自行車',327);insert into my_toys values('G003','牙膏',14.5);insert into my_toys values('G004','棉被',105);insert into my_toys values('G005','熱水瓶',65);insert into my_toys values('G006','拖鞋',22.5); --包規(guī)范create or replace package toyspackas procedure UpdateToyPrice; function AvgToyPrice return number;end toyspack; --包主體create or replace package body toyspackas procedure UpdateToyPrice as avgPrice number := AvgToyPrice; begin while (avgPrice = 400) loop --循環(huán)更新 update my_toys set price= case when price*1.1500 then price*1.1 else price end; avgPrice := AvgToyPrice; commit; end loop; end UpdateToyPrice; function AvgToyPrice return number as v_avg number; begin select avg(price) into v_avg from my_toys; return v_avg; end AvgToyPrice;end toyspack; --調(diào)用execute toyspack.UpdateToyPrice;select toyspack.AvgToyPrice from dual;
驗(yàn)證下不就知道了。
如果你的過程和函數(shù)都沒有語法錯(cuò)誤就是對(duì)的。
包：包含表頭和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就可以自動(dòng)生成的
--創(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, --代理人工號(hào)
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, --被代理人工號(hào)
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, --代理人工號(hào)
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, --被代理人工號(hào)
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;
包用于在邏輯上組合過程和函數(shù)，它由包規(guī)范和包體兩部分組成
1.創(chuàng)建包
--創(chuàng)建一個(gè)包sp_package
create?package?sp_package?is
--聲明該包有一個(gè)過程和函數(shù)，（沒有實(shí)現(xiàn)）
procedure?update_sal(name?varchar2,newsal?number);
function?annual_nicome(name?varchar2)?return?number;
end;
--創(chuàng)建包體（用于實(shí)現(xiàn)已經(jīng)聲明的函數(shù)和過程）
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;
--調(diào)用包中的內(nèi)容
exec?sp_package.update_sal('name',number);
調(diào)用方法：包名.F_insert_user。
個(gè)人認(rèn)為這個(gè)包意義不大，不能提高數(shù)據(jù)庫性能，反而增加了編程時(shí)的代碼量。
重慶分公司
重慶分公司
