在plsql中,编写一个函数,怎么编写？

直接sql语句：

select  wm_concat(decode(e.empno,7369,d.dname,null)),wm_concat(decode(e.empno,7369,e.ename,null)) from emp e, dept d

 where e.empno in ((select e1.mgr from emp e1 where e1.empno = 7369), 7369)

   and e.deptno = d.deptno

/

function ：

create or replace function print_(p_empno in number) return varchar2 is

  l_result varchar2(100)

begin

  select wm_concat(decode(e.empno, p_empno, d.dname, null)) || ',' ||wm_concat(decode(e.empno, p_empno, e.ename, null))

    into l_result

    from emp e, dept d

   where e.empno in

         ((select e1.mgr from emp e1 where e1.empno = p_empno), p_empno)

     and e.deptno = d.deptno

  return(l_result)

end

测试：

SQL> select print_(7369) from dual 

PRINT_(7369)

--------------------------------------------------------------------------------

RESEARCH,SMITH

SQL>

下面是代码:

CREATE OR REPLACE FUNCTION calcDates(START_DATE IN DATE, END_DATE IN DATE)

RETURN number IS

No_of_DAYS number

BEGIN

IF START_DATE <END_DATE THEN

SELECT count(1) days

INTO NO_OF_DAYS

FROM (SELECT DISTINCT trunc(START_DATE) + level - 1 dayList

FROM dual

connect BY trunc(START_DATE) + level - 1 <= trunc(END_DATE)) A

where not exists (select 1

from dp_tbl_public_holiday b

where to_date(b.ph_date, 'YYYYMMDD') = A.dayList)

and to_char(dayList, 'D') not in (1, 7)

ELSE

SELECT 0 - count(1) days

INTO NO_OF_DAYS

FROM (SELECT DISTINCT trunc(END_DATE) + level - 1 dayList

FROM dual

connect BY trunc(END_DATE) + level - 1 <= trunc(START_DATE)) A

where not exists (select 1

from dp_tbl_public_holiday b

where to_date(b.ph_date, 'YYYYMMDD') = A.dayList)

and to_char(dayList, 'D') not in (1, 7)

END IF

Return No_of_DAYS

END

注释:

1.dp_tbl_public_holiday

存储着 假期的信息, ph_date 假期的日期.

2.to_char(dayList, 'D') not in (1, 7)

这个强大的 to_char(dayList, 'D')返回的是日期是星期几,就好比是java里面的 Calendar.DAY_OF_WEEK,

not in (1, 7)就是不在周六或者周日, 1是周日, 7是周六 ,这个和java一样.

3.trunc(START_DATE) trunc 函数拿到的是日期的值,忽略小时数.

4.SELECT DISTINCT trunc(START_DATE) + level - 1 dayList

FROM dual

connect BY trunc(START_DATE) + level - 1 <= trunc(END_DATE)

这段代码就是列出两个日期之间所有日期. connect by 和 level 是个深奥的东东.需要好好消化`

---