from dual connect by rownum<31 就是connect byhttp://marcospring.iteye.com/blog/1721600ORACLE获取某个时间段之间的月份列表 获取某个时间段之间的月份列表(示例返回2009-03到2010-03之间的月份列表) SELECT TO_CHAR(ADD_MONTHS(TO_DATE(‘2009-03‘,‘YYYY-MM‘),ROWNUM-1 ),‘YYYY-MM‘) DAY_ID FROM DUAL CONNECT BY ROWNUM<=months_between (to_date(‘2010-03‘, ‘yyyy-mm‘),to_date(‘2009-03‘, ‘yyyy-mm‘))...
使用dbms_metadata.get_ddl()函数可以做到。 实验环境：Oracle 11.2.0.4以获取jingyu用户下的T1表为例： SQL> conn jingyu/jingyu Connected. SQL> select count(1) from t1;COUNT(1) ----------100SQL> select dbms_metadata.get_ddl(‘TABLE‘,‘T1‘,‘JINGYU‘) from dual;DBMS_METADATA.GET_DDL(‘TABLE‘,‘T1‘,‘JINGYU‘) --------------------------------------------------------------------------------CREATE TABLE...
set line 200set pagesize 0set long 99999set feedback offset echo off获得表、索引、视图、存储过程、函数的DDL：select dbms_metadata.get_ddl(‘TABLE‘,‘TABLE_NAME‘,‘TABLE_OWNER‘) from dual;select dbms_metadata.get_ddl(‘INDEX‘,‘INDEX_NAME‘,‘INDEX_OWNER‘) from dual;select dbms_metadata.get_ddl(‘VIEW‘,‘VIEW_NAME‘,‘VIEW_OWNER‘) from dual;select dbms_metadata.get_ddl(‘PROCEDURE‘,‘PROCED...
‘1970-01-01 08:00:00‘, ‘YYYY-MM-DD HH:MI:SS‘), ‘YYYY-MM-DD HH24:MI:SS‘) AS CDATE FROM DUAL;日期转换毫秒SELECT TO_NUMBER(TO_DATE(‘2014-07-28 17:12:45‘, ‘YYYY-MM-DD HH24:MI:SS‘) - TO_DATE(‘1970-01-01 8:0:0‘, ‘YYYY-MM-DD HH24:MI:SS‘)) * 24 * 60 * 60 * 1000 FROM DUAL;获取某天的信息select * from WORKSCANINFO where TO_CHAR(SCANDATE,‘DD-MM-YY‘) in(SELECT TO_CHAR(1511539200...
函数： CREATE OR REPLACE FUNCTION count_weekend(start_date IN DATE, end_date IN DATE)RETURN NUMBER IS V_COUNT INT;BEGINSELECT count(*) INTO V_COUNT FROM (SELECT start_date + num - 1, TO_CHAR(end_date + num - 1, ‘DAY‘) as t_date2 FROM (SELECT rownum as num FROM obj) WHERE start_date + num - 1 <= end_date) WHERE t_date2 in (‘星期六‘, ‘星期日‘);IF NVL(V_COUNT,0) ...
获取表的主键列名 SQL select * from user_cons_columns where constraint_name = (select constraint_name from user_constraints where table_name = ‘BST_FAVORITE‘ and constraint_type =‘P‘); 记住:表名要大写执行试试,该表所有主键都查出来了,主键列名就在第四列 获取表的所有列名 SQLselect * from table_name where rownum=1这条sql语句相信大家都知道什么意思. 用...
or replace procedure SQL_WX_SP(out_return out sys_refcursor) is beginopen out_return for ‘select * from wx_sp‘; end;C#代码：（这是调用的方法，out_return 这个是和存储过程中的游标参数对应的，类型是OracleDbType.RefCursor。SQL_WX_SP 是存储过程名称） public DataTable function(){try{OracleParameter[] par ={new OracleParameter("out_return", OracleDbType.RefCursor)};par[0].Direction = ParameterDirect...
* FROM (SELECT T.*,ROW_NUMBER() OVER (PARTITION BY T.SUBJECT, T.CHARCODE ORDER BY T.SCORE DESC) AS RNFROM T_SCORE T ) WHERE RN <= 3 oracle 获取科目前3名标签：desc sele har number code weight select over bsp 本文系统来源：https://www.cnblogs.com/lovedaodao/p/9986929.html
fu.user_ID,fu.user_name,fu.start_date,fu.END_DATE,fu.description,fe.last_name,fr.RESPONSIBILITY_NAME,fr.description, --职责描述fr.start_date,fr.END_DATE,frg.request_group_name, ---- 请求组名称frg.description requestdsc, ---- 请求组描述fr.menu_id, ---- 菜单 ID REQUEST_UNIT_TYPE, ---- 请求类型fcp.user_concurrent_program_name, ---请求并发程序名decode(fcp.EXECUTION_METHOD_CODE,‘H‘,‘主机‘,‘S‘,‘...
gls.currency_codeFROM hr_organization_information_v t,gl_sets_of_books glsWHERE t.org_information_context = ‘Operating Unit Information‘AND t.org_information5 = ‘10052‘AND gls.set_of_books_id = t.org_information3 Oracle EBS 获取公司段的本位币标签：_id from color book 公司 format class nbsp ebs 本文系统来源：https://www.cnblogs.com/jenrry/p/10006855.html
b.flex_value_set_id,t.application_column_name,t.form_left_promptFROM fnd_descriptive_flexs_tl fdfl,fnd_descriptive_flexs fdf,fnd_descr_flex_col_usage_tl t,fnd_descr_flex_column_usages b,fnd_descr_flex_contexts_vl fcWHERE t.application_id = b.application_idAND t.descriptive_flexfield_name = b.descriptive_flexfield_nameAND t.descriptive_flex_context_code = b.descriptive_flex_context_co...
fvs1.flex_value_set_name,fvs1.description,decode(upper(fvs1.longlist_flag),‘N‘,‘值列表‘,‘Y‘,‘长值列表‘,‘X‘,‘弹出式列表‘) 列表类型,decode(upper(fvs1.security_enabled_flag),‘N‘,‘无安全性‘,‘H‘,‘分层安全性‘,‘Y‘,‘不分层安全性‘) 安全性类型,decode(upper(fvs1.format_type),‘C‘,‘字符‘,‘D‘,‘日期‘,‘I‘,‘时间‘,‘N‘,‘数字‘,‘T‘,‘时间‘,‘X‘,‘标准日期‘,‘Y‘,‘标准日期时...
a.Owner 主键拥有者,a.table_name 主键表,b.Column_Name 主键列,b.Constraint_Name 主键名 From user_Constraints a,user_Cons_Columns b Where a.Constraint_Type = ‘P‘ --P-主键；R-外键；U-唯一约束and a.Constraint_Name = b.Constraint_Name And a.Owner = b.Owner And a.table_name = b.table_nameand a.owner=upper(‘ETL‘)And a.table_name=upper(‘TFUNDINFO‘); Oracle 获取表的主键、外键以及唯一约束条件...
a.* FROM (select * from (select t.*,row_number() over(partition by t.id order by t.createtime desc) rn from table t ) c where rn = 1) awhere a.id= ‘001‘;--加上where条件是查询一条，不加是查询多条 二：如果是获取时间最新一条数据SELECT * FROM table WHERE (id,createtime) IN (SELECT id,MAX(createtime) createtime FROM table GROUP BY id);--获取时间最新的多条数据SELECT * FROM table where ...
A.TABLE_NAME, B.COMMENTS FROM USER_TABLES A, USER_TAB_COMMENTS BWHERE A.TABLE_NAME = B.TABLE_NAME ORDER BY TABLE_NAME参考博客： 获取oracle数据库当前用户下所有表名和表名的注释 - 尹飞飞 - ITeye博客https://yinfeifei.iteye.com/blog/751858【Oracle】【18】获取数据库当前用户下所有表名和表名的注释标签：log style get title name span sele tables comm 本文系统来源：https://www.cnblogs.com/h...