select substr('AAA-BBB',1, instr('AAA-BBB','-',-1)-1) 值 from dual;--AAAselect substr('AAA-BBB', instr('AAA-BBB','-',-1)+1) 值 from dual;--BBB
2. 帆软报表有参数SQL
select a.agency_code, a.agency_name, a.agency_type
from dw.dim_ta_subred_agency_info a
where1=1
${if(len(agency_code)==0,"","and a.agency_code in ('"+ agency_code +"')")}
orderby1
--步骤1：编写hrxml.sql 脚本文件
conn hr/hr
set timing off
set termout off
set heading off
set long 99999
spool ctl_db_info.xml replace
select dbms_xmlgen.getxml('select * from dc_ctl.ctl_db_info')from dual;exit--步骤2：执行命令
sqlplus -S /nolog @hrxml.sql 运行此脚本
--环境变量cat.bash_profile
export ORACLE_HOME=/home/app_adm/instantclient_11_2
export HPLSQL_HOME=/home/app_adm/hplsql-0.3.31
export PATH=$PATH:$ORACLE_HOME:$HPLSQL_HOME
export NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"
export LD_LIBRARY_PATH=$ORACLE_HOME
export JAVA_HOME=/usr/java/jdk1.8.0_181-cloudera/
5.Oracle中的服务名以及SID默认是实例名称
--1. Oracle的服务名(ServiceName)查询
SQL> show parameter service_name;--2. Oracle的SID查询命令：
SQL> select instance_name from v$instance;--3. 查看Oracle版本
SQL> select version from v$instance
SELECT T.table_name,
listagg(lower(T.COLUMN_NAME),',')WITHINGROUP(ORDERBY t.table_name, t.column_id) names
FROM all_tab_columns t
where table_name in('ITS_ASSET_UNIT','ITS_PROD_CODE','ITS_DIVIDEND_DETAILS','ITS_ACCOUNT_REQUEST','ITS_INIT_DATE','ITS_AGREEMENT','ITS_INVEST_ACCOUNT','ITS_EXT_SIGN_INFO')GROUPBY T.table_name;
8. 在Oracle中查看表在那个存储过程中使用过
--oracle 在Oracle中查看表在那个存储过程中使用过SELECTDISTINCT NAME
FROM all_source
WHERETYPE='PROCEDURE'AND upper(text)LIKE'%TAP_TREQUEST_PARAMETER%';
9. 查看Oracle版本信息
--方法1
select*from v$version;--方法2
SQL> col product format a35
SQL> col version format a15
SQL> col status format a15
SQL> select*from PRODUCT_COMPONENT_VERSION;
10. Oracle并行执行更新或者查询
UPDATE/*+ parallel(t 16) parallel(temp 16)*/ FUNDDC.DC_SHARE_HISTORY T
--由于资源占用，oracle报错01940，解决方案如下：
--1.首先将索要删除的用户锁定，这句必须执行，否则之后杀死进程无效！
alter user icontrol account lock;--2.从【v$Session】表查看当前用户占用资源，有使用资源的情况下，肯定不能删除用户
select saddr,sid,serial#,paddr,username,status from v$session where lower(username) = 'icontrol';select'alter system kill session '''||sid||','||serial#||''';' from v$session where lower(username) = 'icontrol';--3. 杀死status为【 INACTIVE】的进程，sid和seria#值为该列下的数值
alter system kill session 'sid,serial#';--4. 删除用户，如果不成功，即还是会报01940错，因为还有【 INACTIVE】进程没杀死
drop user icontrol cascade;
--方式一select t.date_id as exdate
from(select a.date_id, rank()over(orderby a.date_id desc)as rn
from dw.dim_date a
where a.date_id <= （selectvaluefrom icontrol.sch_variable
where name ='etf_rundate')and a.is_workday ='1') t
where t.rn =4--方式二select c.sk_date
from ctl_srcdwn_batch a
innerjoin comm_cldr_custom b
on a.busdate_int = b.sk_date
and b.sk_calendar=1innerjoin comm_cldr_custom c
on b.workday_no - c.workday_no =5and c.workday_flag=1and c.sk_calendar=1where a.srcsys ='${dk_system}'and a.dwnframe ='${dk_frame}';
19. 获取Oracle中的建表语句
SELECT t1.Table_Name AS"表名称",
t3.comments AS"表说明",
t1.Column_Name AS"字段名称",
t1.DATA_TYPE ||'('|| t1.DATA_LENGTH ||')'AS"数据类型",
t1.NullAble AS"是否为空",
t2.Comments AS"字段说明",
t1.Data_Default As"默认值"FROM cols t1
LEFTJOIN user_col_comments t2
ON t1.Table_name = t2.Table_name
AND t1.Column_Name = t2.Column_Name
LEFTJOIN user_tab_comments t3
ON t1.Table_name = t3.Table_name
LEFTJOIN user_objects t4
ON t1.table_name = t4.OBJECT_NAME
WHERENOTEXISTS(SELECT t4.Object_Name
FROM User_objects t4
WHERE t4.Object_Type ='TABLE'AND t4.Temporary='Y'AND t4.Object_Name = t1.Table_Name)ORDERBY t1.Table_Name, t1.Column_ID;
