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;
1.输出用"、"符合分隔多个作者列表
2.获取数据库表的主键最大值
3.根据分割类来分割字符串
4.计算周岁的函数
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;
--请问如何用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 ;
5.获取数据表架构信息
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;
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;
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;