怎么样写函数

[So_CooL]

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;

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

[opps_zhou]

函数和存储过程中，是不能直接写 select 语句的，要么是 select into，要么就是用游标
调用函数，你写的方法是可以的

[hanks_gao]

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;

[hanks_gao]

SQL語句參考二樓的。

[yixilan]

[Quote=引用楼主 so_cool 的回复:]
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_ti……
[/Quote]
咦？Select之后，要INTO的，不能直接写；
另外，你在函数一开始已经写了return integer is result;就应该在结束的时候，返回一个integer类型的值；

[So_CooL]

还是不行

[luoyoumou]

-- 既然你要return integer，
-- 那么你在begin ... end 块里面的 end 前应该有 return 语句去返回一个整型值啊!

[luoyoumou]

-- 函数可以没有输入参数，但是函数必须有返回值!

[luoyoumou]

-- 给个列子给你：

-- 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 := i.empno;
    result(result.count).ename := i.ename;
    result(result.count).sal := i.sal;
  end loop;
  return(result);
end;
/
  
select * from table(myemp(30));