oracle简单PKG(包)编写

PL/SQL Developer
1. Command Window
View nt_affix; 浏览nt_affix
Edit ob_affix; 编辑ob_affix
Drop type nt_affix2 /; 删除nt_affix
Create type nt_affix as table of ob_affix; 创建个nt_affix继承ob_affix
2. OB/NT/PKG都存于这个表名为USER_OBJECTS中
3. OB：
右键点击右边Types新建OB
create or replace type OB_SALECHECKON as object
(
-- Author : zyp
-- Created : 2012/4/20 12:25:17
-- Purpose : OB_SALECHECKON

-- Attributes
agentID VARCHAR2(32), -- N 被考勤ID
agentName VARCHAR2(64), -- N 被考勤名字
checkName VARCHAR2(64), -- N 考勤人
agentCode VARCHAR2(32), -- N 被考勤人编号
agentLevel VARCHAR2(32), -- N 被考勤人层级
checkItem VARCHAR2(64), -- Y 被考勤项目
checkStatus VARCHAR2(32), -- Y 考勤状态
checkStartTime DATE, -- Y 考勤开始时间
checkEndTime DATE, -- Y 考勤结束时间
onTime INT, -- Y 准时（次）
beLate INT, -- Y 迟到
leave INT, -- Y 请假
attendance VARCHAR2(64), --被考勤人出勤率
checkWork INT, --被考勤人被考勤次数
noCheckWork INT, --被考勤人未被考勤

-- Member functions and procedures
CONSTRUCTOR FUNCTION OB_SALECHECKON RETURN SELF AS RESULT
)
------------------------------------------------------------------
CREATE OR REPLACE TYPE BODY OB_SALECHECKON IS
CONSTRUCTOR FUNCTION OB_SALECHECKON RETURN SELF AS RESULT IS
BEGIN
RETURN;
END;
END;


4. NT:
点击SQL Window直接运行
CREATE OR REPLACE TYPE nt_salecheckon AS TABLE OF ob_salecheckon

5. PKG:
右键点击右边Packages新建pkg
create or replace package PKG_SALECHECKON is

-- Author : zyp
-- Created : 2012/4/20 14:39:24
-- Purpose : 当前插入考勤明细

function SALECHECKON(MAINID in varchar2,
CHECKSTATUS in varchar2,
AGENTLEVEL in varchar2) return nt_SALECHECKON;
end PKG_SALECHECKON;
---------------------------------------------------------------------------------------------------------------------------
create or replace package body PKG_SALECHECKON is

function SALECHECKON(MAINID in varchar2, --in传入的参数,out传出的参数
CHECKSTATUS in varchar2,
AGENTLEVEL in varchar2) return nt_SALECHECKON is
I smallint := 1; --遍历数声明
P_NT_SALECHECKON NT_SALECHECKON := NEW nt_salecheckon(); --实例化NT
begin

for C in (select it.checkedagentcode as agentCode,
it.checkedname as agentName,
it.agenglevel as agentLevel,
ma.startdatetime as checkStartTime,
ma.enddatetime as checkEndTime,
it.checkres as checkStatus
from SALECHECKONMAIN ma, SALECHECKONITEM it
where ma.salecheckonmainid = it.salecheckonmainid
and ma.salecheckonmainid = MAINID
and checkres = CHECKSTATUS
and it.agenglevel = AGENTLEVEL) loop

P_NT_SALECHECKON.EXTEND; --P_NT_SALECHECKON扩展
P_NT_SALECHECKON(I) := new OB_SALECHECKON(); --实例化OB
P_NT_SALECHECKON(I).agentCode := C.AGENTCODE; --获取对应的值
P_NT_SALECHECKON(I).agentName := C.AGENTNAME;
P_NT_SALECHECKON(I).agentLevel := C.AGENTLEVEL;
P_NT_SALECHECKON(I).checkStartTime := C.CHECKSTARTTIME;
P_NT_SALECHECKON(I).checkEndTime := C.CHECKENDTIME;
P_NT_SALECHECKON(I).checkStatus := C.CHECKSTATUS;
I := I + 1;
end loop;
RETURN P_NT_SALECHECKON;
end;

end PKG_SALECHECKON;

6.执行PKG:
SELECT * FROM table(PKG_SALECHECKON.SALECHECKON('LEC0000000099821','CHECKSTATUS_2','AM'))