创建函数
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;