数仓建设之Oracle常见语法学习

1. 字符串截取

select substr('AAA-BBB', 1, instr('AAA-BBB', '-', -1) - 1) 值 from dual; --AAA
select substr('AAA-BBB', instr('AAA-BBB', '-', -1) + 1) 值 from dual; --BBB

2. 帆软报表有参数SQL

select a.agency_code, a.agency_name, a.agency_typefrom dw.dim_ta_subred_agency_info awhere 1 = 1${if(len(agency_code)==0,"","and a.agency_code in ('"+ agency_code +"')")}order by 1

3. oracle导出dmp文件

exp funddc/Jpmam_240416@ETL51New file="D:\dmp\temp_cube_trade_info_20240702.dmp" tables=(temp_cube_trade_info_20240702)
--oracle导入dmp文件 导入的时候会自己创建表
--full=y：代表将dmp文件中的所有数据都进行导入；
--ignore=y:默认为n,当不加这个参数时，导入的表或视图如果在原有表中本来就存在就无法导入这些数据，加上以后就会直接覆盖这些数据。
imp dc_ctl/dc_ctl@etltdb file="D:\dmp\temp_cube_trade_info_20240702.dmp" full=y ignore=y 

4.oracle中表的数据转化成xml文件导出

--步骤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

6. base64加解密

--base64加密
select utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw('DC_CTL')))from dual;
--base64解密
select utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw('RENfQ1RM')))from dual;

7.oracle行转列

SELECT T.table_name,listagg(lower(T.COLUMN_NAME), ',') WITHIN GROUP(ORDER BY t.table_name, t.column_id) namesFROM all_tab_columns twhere 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')GROUP BY T.table_name;

8. 在Oracle中查看表在那个存储过程中使用过

 --oracle 在Oracle中查看表在那个存储过程中使用过SELECT DISTINCT NAMEFROM all_sourceWHERE TYPE = '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

11. 存储过程异常捕获

exceptionwhen others thenrollback;runCode := '1';logMsg  := DBMS_UTILITY.format_error_stack ||DBMS_UTILITY.format_error_backtrace ||DBMS_UTILITY.format_call_stack;RAISE_APPLICATION_ERROR(-20040,'Oracle SQL错误码：' || SQLCODE || ',logMsg: ' || logMsg || ',错误消息：' ||SUBSTR(SQLERRM, 1, 1000));

12. oracle报错ora-01940

--由于资源占用，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;

13. oracle 新增字段

alter table sch_logs add level_ varchar2(8);
comment on column sch_logs.level_ is 'info,debug,error';

14.赋权

--将表Table_A的查询权限赋权给用户USER_A
GRANT SELECT ON Table_A to User_A;
--增删改查都开启权限的语句：
grant select,update,delete,insert on Table_A to USER_A;

15. Oracle中查看表空间位置

select * from dba_data_files;
create tablespace tbs_finedb datafile '/oradb/etldb/finedb.dbf' size 50M autoextend on next 10M maxsize unlimited;
create user finedb identified by finedb default tablespace tbs_finedb;

16. 恢复update、delete之前的数据

--恢复update、delete之前的数据
--根据修改语句查出你需要恢复的时间点
select * from v$sql where sql_text like '%update kycinfo%'
--new_table :新建表的名； table ：误操作的表名；  2020-09-10 11:44:25：保存这个时间点的数据到新表。
create table new_kycinfo as select * from kycinfo as of timestamp to_timestamp('2023-08-03 17:00:06','yyyy-mm-dd hh24:mi:ss');
--将原表的数据全部删除
delete kycinfo ;
--把恢复的数据保存到原表。
insert into kycinfo select * from new_kycinfo ;

17.oracle中不同字符集占用字节

gkb  ->中文2个字节
utf8 ->中文3个字节lengthb(string)计算string所占的字节长度：返回字符串的长度，单位是字节
length(string)计算string所占的字符长度：返回字符串的长度，单位是字符
对于单字节字符,LENGTHB和LENGTH是一样的.
如可以用length(‘string’)=lengthb(‘string’)判断字符串是否含有中文。
注：
一个汉字在Oracle数据库里占多少字节跟数据库的字符集有关，UTF８时，长度为三。
select lengthb('飘') from dual   可查询汉字在Oracle数据库里占多少字节

18.获取前t-4个工作日的日期

--方式一
select t.date_id as exdatefrom (select a.date_id, rank() over(order by a.date_id desc) as rnfrom dw.dim_date awhere a.date_id <= （select value from icontrol.sch_variablewhere name = 'etf_rundate' )and a.is_workday = '1') twhere t.rn = 4
--方式二
select c.sk_date from ctl_srcdwn_batch a
inner join comm_cldr_custom bon a.busdate_int = b.sk_dateand b.sk_calendar=1
inner join comm_cldr_custom con b.workday_no - c.workday_no = 5and c.workday_flag=1and c.sk_calendar=1
where 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 t1LEFT JOIN user_col_comments t2ON t1.Table_name = t2.Table_nameAND t1.Column_Name = t2.Column_NameLEFT JOIN user_tab_comments t3ON t1.Table_name = t3.Table_nameLEFT JOIN user_objects t4ON t1.table_name = t4.OBJECT_NAMEWHERE NOT EXISTS (SELECT t4.Object_NameFROM User_objects t4WHERE t4.Object_Type = 'TABLE'AND t4.Temporary = 'Y'AND t4.Object_Name = t1.Table_Name)ORDER BY t1.Table_Name, t1.Column_ID;

20. MYSQL不同版本对应的jdbc驱动类

--mysql3
org.gjt.mm.mysql.Driver
--mysql5
com.mysql.jdbc.Driver
--mysql8
com.mysql.cj.jdbc.Driver
--url
jdbc:mysql://10.169.1.239:3306/amc_newton?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8&allowMultiQueries=true--kettle使用jdbc驱动版本
mysql-connector-java-5.1.47.jar   --该版本支持MySQL3、5、8

21. excel VLOOKUP()函数的使用

=VLOOKUP(A2,B:B,1,0) A2在B列表中寻找匹配，匹配到显示B列的数据，匹配不到显示NA
=VLOOKUP(B2,A:A,1,0) B2在A列表中寻找匹配，匹配到显示A列的数据，匹配不到显示NA

22. oracle 存储过程备注

  -- Author  : APP_ADM-- Created : 2016/11/30 8:29:33-- Purpose : 统计MIS中PEER GROUP

23. 字段拼接

--字段拼接
listagg(a.manager_name, ',') within group(order by a.sk_managerid)
--例如：
select a.fund_code,listagg(a.sk_managerid, ',') within group(order by a.sk_managerid) as manager_code,listagg(a.manager_name, ',') within group(order by a.sk_managerid) as manager_namefrom funddc.prod_assoc_fundmanager agroup by a.fund_code;
--000073	uo211,uo309,uo341,uo411,uo608	杜猛,乐琪,杨景喻,叶敏,刘辉

24. GPG加密

crontab -e 8,18,28,38,48,58 4-18 * * * /bin/sh /home/apple/apple_schedule.sh >> /home/apple/tmp/apple.log
--导入公钥 公钥加密文件，用私钥解密文件
gpg --import gpg/APPLERSA_public.asc
gpg --encrypt --recipient [e-mail] --trust-model always
gpg --recipient [e-mail] --trust-model always --output ./encrypted/$i.pgp --encrypt ./apple/$i