oracle 自定义函数实例

创建函数

create or replace function get_sp(sp in varchar2) return varchar2 is
  v_sum varchar2(40);
begin
  select count(s.id) into v_sum from t_run_address_book s;
  return v_sum;
exception
  when NO_DATA_FOUND then
    dbms_output.put_line('没有数据!');
end;

调用：

DECLARE
  sp varchar2(40);
BEGIN
  sp :=get_sp('10');
  DBMS_OUTPUT.PUT_LINE(sp);
END;

或者在sql ---

select get_sp('10') form dual;

 

 

下班到家写注释。

---

 

create or replace function GetMeetingVideoItem(convid in varchar2) return varchar2 as
VideoItemVlause t_mt_meeting_video_item.prep_video_id%type;
retresult varchar2(3000);
type mycursor is ref cursor;
resultCursor mycursor;
 begin
    open resultCursor for select a.prep_video_id from  t_mt_meeting_video_item a
     where a.conv_id=convid;
     loop
         fetch resultCursor into VideoItemVlause;
               exit when resultCursor%notfound;
               retresult:=retresult||VideoItemVlause||',';
     end loop;
     close resultCursor;
     if length(retresult)>0 then  ---剔除最后一个逗号
         retresult:=substr(retresult,0,length(retresult)-1);
     end if;
     return retresult;
 end GetMeetingVideoItem;