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