pl_sql中函数的基本运用

1基本函数
create or replace function f_getworkdate (inputdate date)
return date is
  v_workdate date;
begin
select calendardate into v_workdate from tbl where tbl.startdate=date;
   return   v_workdate;
exception when others then
                   v_workdate:=null;
  return   v_workdate;
end;



2运用基本函数处理简单逻辑
CREATE OR REPLACE FUNCTION F_REPEAT(NumberStr in varchar2) return varchar2 is
  StrReportNo  varchar2(2000);
  StrTemp      varchar2(2000);
  TempReportNo varchar2(2000);
begin
  StrReportNo  := '';
  StrTemp      := '';
  TempReportNo := NumberStr;
  loop
    --判断参数有没有值
    if instr(TempReportNo || ',', ',', 1, 1) > 1 THEN
      --以','分隔截取字段
      StrTemp := SUBSTR(TempReportNo,
                        1,
                        INSTR(TempReportNo || ',', ',', 1, 1) - 1);
    end if;
    --判断是否已经存在相同值,这里前后一定要加分隔符
    if INSTR(',' || StrReportNo || ',', ',' || StrTemp || ',', 1, 1) = 0 THEN
      --累加不同结果
      StrReportNo := StrReportNo || ',' || StrTemp;
    end IF;
    --将判断过的字段删除掉
    TempReportNo := SUBSTR(TempReportNo,
                           INSTR(TempReportNo || ',', ',', 1, 1) + 1);
    --参数所有值完成过滤判断后跳出循环
    exit WHEN length(TempReportNo) = 0 or TempReportNo is null;
  end LOOP;
  return SUBSTR(StrReportNo, 2);
end F_REPEAT;

例;select  F_REPEAT('1,2,3,3,8') from dual