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'))