日期:2014-5-17 浏览次数:21217次 点赞次数:20

怎么样写函数
SQL code
Create function f_get_knit_time(dept_id in varchar2,process_id in varchar2,beginDt in varchar2,endDt in varchar2)
return integer is result;
begin
       select nvl( sum(QUANTITY * knit_time)/60,0) as knit_time
                        from WORK_CKM_KNITTIME a,(SELECT
                                WSM.LOT_NO,
                                WBM.CARD_SIZE,
                                count(*) QUANTITY                
                        from    WORKSCAN_SH_LIST WSL,
                                WORKSCAN_SH_MAIN WSM,   
                                WORK_BAR_LIST WBL,
                                WORK_BAR_MAIN WBM,
                                work_staff ws,
                                work_process wp
                        WHERE WSM.PKEY = WSL.PKEY and  
                                WBM.CARD_ID=WBL.CARD_ID AND
                                WSL.BAR_CODE=WBL.BAR_CODE and
                                ws.station_name ='电脑织机' and
                                wsm.object_id = ws.staff_id and
                        wsm.main_process = wp.process_id  and ws.Dept_ID := dept_id and ws.process_id := process_id and WSM.scan_date >= to_date(''+beginDt+'','dd-MM-yyyy HH24:MI:SS') and WSM.scan_date <= to_date(''+endDt+''+ ' 23:59:59' ,'dd-MM-yyyy HH24:MI:SS') 
                        GROUP BY WSM.LOT_NO,WBM.CARD_SIZE ) b where a.LOT_NO = b.LOT_NO and a.size_name = b.CARD_SIZE
end

select f_get_knit_time('16','+3','18-08-2011','18-08-2011') from dual;

请帮我看看这个函数再使用参数和调用函数是不是这样写.


------解决方案--------------------
函数和存储过程中,是不能直接写 select 语句的,要么是 select into,要么就是用游标
调用函数,你写的方法是可以的
------解决方案--------------------
create or replace function f_get_knit_time(dept_id in varchar2,process_id in varchar2,beginDt in varchar2,endDt in varchar2)
return INTEGER AS
select nvl( sum(QUANTITY * knit_time)/60,0) as knit_time
from WORK_CKM_KNITTIME a,(SELECT
WSM.LOT_NO,
WBM.CARD_SIZE,
count(*) QUANTITY
from WORKSCAN_SH_LIST WSL,
WORKSCAN_SH_MAIN WSM,
WORK_BAR_LIST WBL,
WORK_BAR_MAIN WBM,
work_staff ws,
work_process wp
WHERE WSM.PKEY = WSL.PKEY and
WBM.CARD_ID=WBL.CARD_ID AND
WSL.BAR_CODE=WBL.BAR_CODE and
ws.station_name ='电脑织机' and
wsm.object_id = ws.staff_id and
wsm.main_process = wp.process_id and ws.Dept_ID := dept_id and ws.process_id := process_id and WSM.scan_date >= to_date(''+beginDt+'','dd-MM-yyyy HH24:MI:SS') and WSM.scan_date <= to_date(''+endDt+''+ ' 23:59:59' ,'dd-MM-yyyy HH24:MI:SS') 
GROUP BY WSM.LOT_NO,WBM.CARD_SIZE ) b where a.LOT_NO = b.LOT_NO and a.size_name = b.CARD_SIZE
end;
------解决方案--------------------
SQL code
-- 给个列子给你:

-- Oracle返回表值的函数

create or replace type empobj as object
( empno number(4),
  ename varchar2(10),
  sal   number(7,2)
)
/

create or replace type emptb is table of empobj
/

create or replace function myemp(i_deptno number)
 return emptb
is
  Result emptb := emptb();
begin
  result := emptb();
  for i in (select empno, ename, sal from emp where deptno=i_deptno )
  loop
    result.extend;
    result(result.count):=empobj(NULL,NULL,NULL);
    result(result.count).empno :=