Oracle开发常用函数与存储过程

1.输出用"、"符合分隔多个作者列表

create or replace function Fuc_Get_AuthorName(RecID_In in varchar2, AdmID_In in varchar2) return varchar2 is
  Result varchar2(8000);
  names varchar2(4000);

  Cursor Cur_Get_AuthorNameS Is
      SELECT PEOPLENAME From TB_NEWS_CONTRI_AUTHOR Where REC_ID=RecID_In;

  Cursor Cur_Get_AuthorName Is
      SELECT PEOPLENAME From TB_NEWS_CONTRI_AUTHOR Where REC_ID=RecID_In And UNITID=AdmID_In;
begin

  If RecID_In Is NULL Then
    Return '';
  End If;

  If AdmID_In Is NULL Then
    open Cur_Get_AuthorNameS;
    fetch Cur_Get_AuthorNameS into names;
    while Cur_Get_AuthorNameS%FOUND
    loop
        Result:=Result || names || '、';
        fetch Cur_Get_AuthorNameS into names;
    end loop;
    close Cur_Get_AuthorNameS;
  ELSE
    open Cur_Get_AuthorName;
    fetch Cur_Get_AuthorName into names;
    while Cur_Get_AuthorName%FOUND
    loop
        Result:=Result || names || '、';
        fetch Cur_Get_AuthorName into names;
    end loop;
    close Cur_Get_AuthorName;
  End If;


  if(Result is  null) Then
    Return '';
  End If;

  if length(Result)>0 then
     Result:=substr(Result,1,length(Result)-1);
  end if;
  return(Result);

  Exception
    When OTHERS Then
      Return '';
end Fuc_Get_AuthorName;

 

2.获取数据库表的主键最大值

CREATE OR REPLACE FUNCTION Fuc_Get_MaxId( TableName_In IN Varchar2,FieldName_In IN Varchar2,CallMode_In IN Varchar2 ) Return  Varchar2 IS
/*---------------------------------------------------------------------------*\
 功能：
     获取数据库表的主键最大值。
    输入参数：
     TableName_In --输入需要查询数据库表名称
     FieldName_In --输入数据库表对应主键的字段名
     CallMode_In   --输入调用方式，缺省为'0'
  输出参数：
     新的12位主键值
\*---------------------------------------------------------------------------*/
    
  SqlStrTmp Varchar2(500);
  MaxFieldTmp Varchar2(12);
  MaxFieldVar Varchar2(12);
  TableNameTmp Varchar2(50);

  FieldNameTmp Varchar2(50);
 
  SysFieldTmp Varchar2(4);

  Cursor Cur_Sys_Var Is 
         Select SubStrb(VarValue,1,4) VarValue From Tb_Sys_Var Where VarName='CurrCode';
  
BEGIN

  
  
  If TableName_In Is NULL Or FieldName_In Is NULL Then
    Return '000000000000';
  End If;

  TableNameTmp := Upper( TableName_In );
  FieldNameTmp := Upper( FieldName_In );

  Open Cur_Sys_Var;
  Fetch Cur_Sys_Var Into SysFieldTmp;
  If Cur_Sys_Var%NOTFOUND Then
    Close Cur_Sys_Var;
    Return '000000000000';
  End if;
  Close Cur_Sys_Var;
  
  SqlStrTmp := 'SELECT MAX(To_Number('||FieldNameTmp||')) FROM '||TableNameTmp
            ||' WHERE '||FieldNameTmp||' LIKE '''||SysFieldTmp||'%''';
  
  Execute Immediate SqlStrTmp INTO MaxFieldTmp;
  
  MaxFieldVar := SysFieldTmp||Lpad( To_Char(To_Number(Substrb(Nvl(MaxFieldTmp,SysFieldTmp||'00000000'),5))+1),8,'0');

  Return MaxFieldVar;
  
  Exception
    When OTHERS Then
      Return SysFieldTmp||'00000001';  
   
END;

 

3.根据分割类来分割字符串

CREATE OR REPLACE FUNCTION sf_split_string (strings VARCHAR2, substring VARCHAR2) RETURN Varchar2Varray IS
/*---------------------------------------------------------------------------*\
 功能：
     根据分割类来分割字符串
    输入参数：
     strings --输入字符串（如：aaaa|bbbb|cccc）
     substring --用于分割的标志(如:'  |  ' )
  输出参数：
     数组
\*---------------------------------------------------------------------------*/

len integer := LENGTH(substring);
lastpos integer := 1 - len;
pos integer;
num integer;
i integer := 1;
ret Varchar2Varray := Varchar2Varray(NULL);
BEGIN
LOOP
pos := instr(strings, substring, lastpos + len);
IF pos > 0 THEN --found
num := pos - (lastpos + len);
ELSE --not found
num := LENGTH(strings) + 1 - (lastpos + len);
END IF;

IF i > ret.LAST THEN
ret.EXTEND;
END IF;

ret(i) := SUBSTR(strings, lastpos + len, num);

EXIT WHEN pos = 0;
lastpos := pos;
i := i + 1;
END LOOP;

RETURN ret;
END;

 

4.计算周岁的函数

CREATE OR REPLACE FUNCTION Fuc_Get_FullAge(BirthDay_In in date, CallDay_In in date) RETURN integer IS
  age integer;
  
  BEGIN

  /*
  **给出出生日期，计算日期，返回周岁
  **1、如果计算日期和出生日期的月份相同，就要判断计算日期是否过了出生日期
  **2、如果是2月29日出生的，那如果是闰年，就是3月1号算增加1岁，如果不是闰年，3月2日增加1岁
  **
  */
  if BirthDay_In is null or CallDay_In is null then
      return  null;
  end if;
   
  --计算日期比出生日期小，返回NULL
  if (CallDay_In - BirthDay_In)<0 then
     return  null;
  end if;
  
  --计算日期   =   出生日期，返回0
  if (BirthDay_In - CallDay_In)=0 then
     return  0;
  end if;

  age := extract(year from CallDay_In)-extract(year from BirthDay_In)-1;

  ----如果月份相同，计算日期>出生日期,加1
  if   (extract(month from BirthDay_In) = extract(month from CallDay_In)) and (extract(day from BirthDay_In) < extract(day from CallDay_In)) then
      age := age + 1;
  end if;

  ----如果计算月份比出生月份大，加1
  if   (extract(month from BirthDay_In) < extract(month from CallDay_In)) then
   age := age + 1;
  end if;

  ----如果出生日期是2月29日，而计算日期是3月1日，那么计算日期是闰年，要减1
  if   (extract(month from BirthDay_In) = 2 and extract(day from BirthDay_In) = 29 and extract(month from CallDay_In) = 3 and extract(day from CallDay_In) = 1 and ((extract(year from CallDay_In) mod 4=0 and extract(year from CallDay_In) mod 100!=0 ) or extract(year from CallDay_In) mod 400=0)) then
   age := age - 1;
  end if;

  return age;

  Exception
    When OTHERS Then
      Return 0;

end;

 

5.获取数据表架构信息

--请问如何用SQL语句获取指定表的表结构
--该表应包含以下字段：字段名、字段类型、字段大小、是否为空  

SELECT   column_name   AS   FIELDNAME,data_type   AS   TYPE,data_length   ASSIZE,
nullable   AS   ISNULL   FROM   ALL_tab_columns  
 where  table_name =  'TB_SYS_ORG_MEMBER'    

SELECT * FROM ALL_TAB_COLUMNS 
where owner='ZSZGW' and table_name='TB_SYS_ORG_MEMBER';   

--根据字段注释查询字段

SELECT A.TABLE_NAME,A.COMMENTS,B.COLUMN_NAME,B.COMMENTS
FROM USER_TAB_COMMENTS A,USER_COL_COMMENTS B 
WHERE A.TABLE_NAME=B.TABLE_NAME and b.COMMENTS like '%发放%' --介绍
and a.table_name='TB_SYS_ORG_MEMBER';

--1、SELECT * FROM ALL_TAB_COLUMNS where owner='USE1' and table_name ='B';  
--2、desc 表名 
--3、
SELECT column_name AS FIELDNAME,data_type,data_length,
nullable AS ISNULL FROM user_tab_columns where table_name = 'TB_SYS_ORG_MEMBER' 


SELECT * FROM user_tab_columns where table_name = 'TB_SYS_ORG_MEMBER' 

select userenv ('terminal') from dual
 
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;--mi是分钟 
 
select user from dual
 
SELECT S.SID SESSION_ID, S.USERNAME, DECODE(LMODE, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', 
 TO_CHAR(LMODE)) MODE_HELD, DECODE(REQUEST, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', 
 TO_CHAR(REQUEST)) MODE_REQUESTED, O.OWNER||'.'||O.OBJECT_NAME||' ('||O.OBJECT_TYPE||')', 
 S.TYPE LOCK_TYPE, L.ID1 LOCK_ID1, L.ID2 LOCK_ID2 FROM V$LOCK L, SYS.DBA_OBJECTS O,
  V$SESSION S WHERE L.SID = S.SID AND L.ID1 = O.OBJECT_ID ;

 

6.带排序的oracle分页存储过程(来自网络)

--输入order by 的sqeuence是，应该为“ desc”或者“ asc”
--若输入两个order by则，v_order_field=" a[sequence] ,order by b "

CREATE OR REPLACE PROCEDURE TABLEPAGE_SELECT(v_page_size　　int, --the size of a page of list
　　　　　　　　　　　　　　　　　　　　　　 v_current_page int, --the current page of list
　　　　　　　　　　　　　　　　　　　　　　 v_table_name varchar2, --the talbe name
　　　　　　　　　　　　　　　　　　　　　　 v_order_field　varchar2,--the order field
　　　　　　　　　　　　　　　　　　　　　　 v_order_sequence varchar2,--the order sequence should by "_desc"or "_asc",_is blank.
　　　　　　　　　　　　　　　　　　　　　　 --v_sql_select　 varchar2, --the select sql for procedure
　　　　　　　　　　　　　　　　　　　　　　 --v_sql_count　　varchar2, --the count sql for procedure
　　　　　　　　　　　　　　　　　　　　　　 --v_out_recordcount OUT int, --the num of return rows
　　　　　　　　　　　　　　　　　　　　　　 p_cursor OUT refcursor_pkg.return_cursor) as
　v_sql　　　　 varchar2(3000); --the sql for select all rows of list
　v_sql_count　 varchar2(3000); --the count sql for procedure
　v_sql_order　 varchar2(2000); --the order of list
　v_count　　　 int; -- the amount rows fo original list
　v_endrownum　 int; --the end row num of the current page
　v_startrownum int; --the start row num of the current page
BEGIN
　----set the order of list
　if　v_order_field!='NO' then
　　v_sql_order :=' ORDER BY '|| v_order_field ||' '||v_order_sequence;
　else
　　 v_sql_order :='';
　end if;
　----catch the amount rows of list
　v_sql_count:='SELECT COUNT(ROWNUM) FROM '||v_table_name;
　execute immediate v_sql_count into v_count;
　-- v_out_recordcount := v_count;
　----set the value of start and end row
　if v_order_sequence='desc' then
　　v_endrownum:=v_count-(v_current_page-1)*v_page_size;
　　v_startrownum:=v_endrownum - v_page_size + 1;
　else
　　v_endrownum:= v_current_page * v_page_size;
　　v_startrownum := v_endrownum - v_page_size + 1;
　end if;
　----the sql for page slide
　v_sql := 'SELECT * FROM (SELECT '||v_table_name||'.*, rownum rn FROM '||v_table_name||' WHERE rownum <= ' ||
　　　　　 to_char(v_endrownum) ||' '|| v_sql_order||')　WHERE rn >= ' ||
　　　　　 to_char(v_startrownum)||' '||v_sql_order;
　open p_cursor for v_sql;
END TABLEPAGE_SELECT;