Oracle 实例几个function



传入字符串,分割符号 分割后进行select 查询拼接操作

CREATE OR REPLACE
function concession_sign_dept(cfstr in VARCHAR2,cff in VARCHAR2)
 return VARCHAR2 as
 cf_index int:=0;
 cf_start int:=0;
 cffstr varchar(1000):=cfstr||',';
 tempval   VARCHAR2(1000);
  returnstr VARCHAR2(100);
BEGIN
 loop
  cf_index:=INSTR(cffstr,cff,cf_index+1);
  exit WHEN cf_index=0;
  tempval:=SUBSTR(cffstr, cf_start+1,cf_index-cf_start-1);
  select depart_name into tempval from SYS_DEPART where depart_code=tempval;
  returnstr:=returnstr||','||tempval;
  cf_start:=cf_index;
 end loop;
  returnstr:="SUBSTR"(returnstr, 2, length(returnstr));
 return returnstr;
end;

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

将查询出来的结果进行拼接 参数1查询的sql 参数2拼接的符号

CREATE OR REPLACE
function pjstr(tempsql in VARCHAR2, pjh in VARCHAR2)
  return VARCHAR2 as
  TYPE i_cursor_type IS REF CURSOR;
  mycursor  i_cursor_type;
  tempval   VARCHAR2(1000);
  returnstr VARCHAR2(100);
begin
  OPEN mycursor FOR tempsql;
  loop
    fetch mycursor
      into tempval;
    EXIT when mycursor%notfound;
    returnstr := returnstr || tempval || pjh;
  end loop;
  returnstr := substr(returnstr, 1, length(returnstr) - 1);
  close mycursor;
  return returnstr;
end;

-------------------------查找在同一表中删除的序列在主表中是否存在 如果不存在取最大值 存在判断当前年是否一样 否而当前年+序号

CREATE OR REPLACE
function concession_applicatio_getMaxNo
 return VARCHAR2 as
 returnval VARCHAR2(20);
 curyear varchar2(4):=to_char(sysdate,'yyyy');
BEGIN
 select concession_no into returnval from qis_concession_application where concession_no not IN(
 select a.concession_no from
  (select concession_no from qis_concession_application where flag='0') a,
  (select concession_no from qis_concession_application where flag='1') b
   where a.concession_no=b.concession_no
) and flag='0' and rownum=1 order by concession_no asc;
return returnval;
 EXCEPTION
  when NO_DATA_FOUND then
  select max(concession_no) into returnval from qis_concession_application;
  if instr(returnval,curyear)>0 THEN
   returnval:=to_char(to_number(returnval)+1);
  else
   returnval:=curyear||'0001';
  end if;
 return returnval;
end;