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 IsNULLThen Return''; EndIf;
If AdmID_In IsNULLThen 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; EndIf;
if(Result isnull) Then Return''; EndIf;
if length(Result)>0then Result:=substr(Result,1,length(Result)-1); endif; return(Result);
Exception When OTHERS Then Return''; end Fuc_Get_AuthorName;
--计算日期比出生日期小,返回NULL if (CallDay_In - BirthDay_In)<0then returnnull; endif;
--计算日期 = 出生日期,返回0 if (BirthDay_In - CallDay_In)=0then return0; endif;
age := extract(yearfrom CallDay_In)-extract(yearfrom BirthDay_In)-1;
----如果月份相同,计算日期>出生日期,加1 if (extract(monthfrom BirthDay_In) = extract(monthfrom CallDay_In)) and (extract(dayfrom BirthDay_In) < extract(dayfrom CallDay_In)) then age := age +1; endif;
----如果计算月份比出生月份大,加1 if (extract(monthfrom BirthDay_In) < extract(monthfrom CallDay_In)) then age := age +1; endif;
----如果出生日期是2月29日,而计算日期是3月1日,那么计算日期是闰年,要减1 if (extract(monthfrom BirthDay_In) =2and extract(dayfrom BirthDay_In) =29and extract(monthfrom CallDay_In) =3and extract(dayfrom CallDay_In) =1and ((extract(yearfrom CallDay_In) mod 4=0and extract(yearfrom CallDay_In) mod 100!=0 ) or extract(yearfrom CallDay_In) mod 400=0)) then age := age -1; endif;
return age;
Exception When OTHERS Then Return0;
end;
5.获取数据表架构信息
SQL --请问如何用SQL语句获取指定表的表结构 --该表应包含以下字段:字段名、字段类型、字段大小、是否为空 SELECT column_name AS FIELDNAME,data_type AS TYPE,data_length ASSIZE, nullable ASISNULLFROM 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 ASISNULLFROM 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是分钟 selectuserfrom dual
Code --输入order by 的sqeuence是,应该为“ desc”或者“ asc” --若输入两个order by则,v_order_field=" a[sequence] ,order by b " CREATEORREPLACEPROCEDURE 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 :=''; endif; ----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; endif; ----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;