oracle知识点

关键字

join类型以及他们之间的差异

• JOIN:如果表中有至少一个匹配，则返回行 (返回满足条件的)
• LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行(返回包括左表中的所有记录和右表中联结字段相等的记录)
• RIGHT JOIN:即使左表中没有匹配，也从右表返回所有的行(返回包括右表中的所有记录和左表中联结字段相等的记录)
• FULL JOIN: 只要其中一个表中存在匹配，就返回行(返回满足条件的两个表中的全部)
• inner join(等值连接) 只返回两个表中联结字段相等的行

函数

1.nvl函数和nvl2函数

• nvl(value1,value2): 这个函数的意思是如果value1的值为null,那么函数返回value2的值 ,如果value1不为空,那么就返回value1的值(需要注意的是value1和value2要保持字段类型相同)。
• nvl2(value1,value2,value3): 这个函数的意思是如果value1的值为null 函数返回value3,否则函数返回value2 也就是说函数永远不会返回value1(注意的是参数value2value3可以是除了LONG类型之外的任意数据类型)。

2.拆分字符串函数(自己定义的Get_StrArrayStrOfIndex函数)

create or replace function Get_StrArrayStrOfIndex
(
  av_str varchar2,  --要分割的字符串
  av_split varchar2,  --分隔符号
  av_index number --取第几个元素
)
return varchar2
is
  lv_str varchar2(1024);
  lv_strOfIndex varchar2(1024);
  lv_length number;
begin
  lv_str:=ltrim(rtrim(av_str));
  lv_str:=concat(lv_str,av_split);
  lv_length:=av_index;
  if lv_length=0 then
 lv_strOfIndex:=substr(lv_str,1,instr(lv_str,av_split)-length(av_spli));
  else
    lv_length:=av_index+1;
    lv_strOfIndex:=substr(lv_str,instr(lv_str,av_split,1,av_index)+length(av_split),instr(lv_str,av_split,1,lv_length)-instr(lv_str,av_split,1,av_index)-length(av_split));
  end if;
  return  lv_strOfIndex;
end Get_StrArrayStrOfIndex;
复制代码

eg: select Get_StrArrayStrOfIndex('songguojun',0) from dual
运行结果为：songguojun

3.decode(expression, search_1, result_1, default)

• expression:条件
• search_1：值1
• result_1：结果1
• default：默认值

使用说明: 比较表达式和搜索字，如果匹配，返回结果；如果不匹配，返回default值；如果未定义default值，则返回空值。

4.Round(number[,decimals])

• number 待做截取处理的数值
• 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分，并四舍五入。如果为负数则表示从小数点开始左边的位数，相应整数数字用0填充，小数被去掉。
• [链接地址]：blog.csdn.net/tayanxunhua…

5.分析函数(OVER)

详细介绍见链接地址：blog.csdn.net/east_mrchiu…

命令

1.imp命令---导入dmp文件(用exp导出的)

imp prd_dc_lw/prd_dc_lw@prd_dc138 file=/data/penghong/STAT_STOMIDMER_MONTH.dmp full=yes
ignore=yesbuffer=10240000 indexes=n constraints=n statistics=none
复制代码

参数说明：

• prd_dc_lw/prd_dc_lw@prd_dc138---用户名/密码/数据库(在本地导入的话可以直接写为： $SRV )
• file---导入的dmp文件的全路径
  说明： 默认情况下直接执行该命令会自动创建表，如果在执行过程中报表空间不存在的话，则需要自己手动建表。

2.impdp命令---导入dmp文件(用expdp导出的),在同一个库下面的操作

impdp prd_dc_lw/prd_dc_lw@prd_dc138 
directory=ORACLE_DM_FILE_PATH dumpfile=STAT_STOMIDMER_MONTH.dmp 
logfile=ORACLE_LOG_DIR:STAT_STOMIDMER_MONTH_201710.log TABLE_EXISTS_ACTION=append
remap_tablespace=TS_PASAH_DB:TS_BATCH 
remap_schema=batch:prd_dc_lw parallel=4
复制代码

参数说明：

• prd_dc_lw/prd_dc_lw@prd_dc138---用户名/密码/数据库(在本地导入的话可以直接写为： $SRV )
• directory---dmp文件存放的目录(在etc文件夹下面的config.xml文件里配置的name的值。eg:< param name="ORACLE_DM_FILE_PATH" value="/dcfile/jsxt/" />)
• dumpfile---要导入的文件名
• logfile---日志文件路径
• TABLE_EXISTS_ACTION---table存在的情况下的操作
• remap_tablespace---导出表空间:导入的表空间
• remap_schema---导出的用户：导入的用户
• parallel---固定参数

后台运行该命令：

nohup impdp $SRV directory=DM_FILE_PATH dumpfile=YSSETT_STL_TXN_DTL_01_20170918.dmp 
logfile=liuwei.log TABLE_EXISTS_ACTION=append remap_tablespace=TS_YSSZGS:TS_BATCH 
remap_schema=ysszgs:batch parallel=4 &
复制代码

3.impdp命令---导入dmp文件，不在同一个库下的操作(远程导入到其他库中)

imp prd_dc_lw/prd_dc_lw@144.131.254.138:1521/dcdb file=/home/dbbatch01/liuwei/STL_TXN_DTL.dmp full=yes ignore=yes buffer=10240000 indexes=n constraints=n statistics=none
复制代码

4.sqlldr---导入txt文件

sqlldr userid=$SRV data=/dcfile/zjxt/20180108.txt
control=$HOME/UMSDC/ctl/mobile/ZJ_MER_FUNDING.ctl
log=/home/tmp/log/ZJ_MER_FUNDING.log 
bad=/home/tmp/log/ZJ_MER_FUNDING.bad 
rows=1000 readsize=20489599 ERRORS=99999 parallel=true
复制代码

参数说明：

• userid---要导入的数据库环境
• data---要导入的文件全路径
• control---控制文件的全路径(ctl是由tmp.ctl用sed命令转换而来的)
• log---日志文件全路径(没有的时候需要手动建)
• bad---同参数log
• rows、readsize、ERRORS、parallel这几个参数一般都是固定的

常见错误总结： [链接地址]:www.cnblogs.com/kerrycode/p…

零碎知识

1.同义词

• 创建同义词：(在webuser用户下建batch用户下sync_cpuser的同义词)

 create synonym SYNC_CPUSER  for batch.SYNC_CPUSER;
复制代码

• 查询同义词：(根据同义词名称查询同义词)

SELECT * FROM SYS.ALL_SYNONYMS WHERE SYNONYM_NAME='SYNC_CPUSER'
复制代码

• 删除同义词

drop synonym SYNC_CPUSER
复制代码

2.目录

• 创建目录：

 create or replace directory DM_FILE_PATH as '/home/dbbatch03/UMSDC/dm';
复制代码

• 查询所有目录:

select * from dba_directories;
复制代码

• 删除目录：

drop directory exp_dir;
复制代码

3.表空间

• 查询表空间的占用情况

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;
复制代码

• 查询每个表占用表空间的大小

select * from (select segment_name table_name,bytes/1024/1024/1024 GByte
from dba_segments where owner = USER order by bytes desc ) where rownum <= 30;
复制代码

• 查询表空间里有哪些表

select TABLE_NAME,TABLESPACE_NAME from dba_tables where TABLESPACE_NAME='TS_DC_BATCH';
复制代码

• 查询表所在的表空间

SELECT TABLE_NAME,TABLESPACE_NAME
FROM USER_TABLES where TABLE_NAME = 'UMP_USER_IDENTITY_INFO';
复制代码

4.表结构

• 查询表结构

select t.column_id as 序号,
       t.column_name as 字段名称,
       c.comments as 中文名称,
       t.data_type as 字段类型,
       t.data_length as 长度,
       (case when t.nullable = 'Y'
       then '√'
       else ''
       end)as 允许空 ,
       '' as 缺省值,
       c.comments as 字段描述
  FROM user_tab_cols t,
       user_col_comments c
 WHERE upper(t.table_name) = 'MID_PAYPROCESS_MON'
   and c.table_name = t.table_name
   and c.column_name = t.column_name
   and t.hidden_column = 'NO'
 order by t.column_id
复制代码

• 查询表索引

 select  t.column_name  as column_name ,t.index_name as index_name,
      (case when
       t.column_name = mid.column_name
       then 'PK、AK'
       else ''
        end) as constraint,
          (case when
       t.column_name = mid.column_name
       then '主键'
       else '加快查询速度'
        end) as purpose
       from user_ind_columns t 
       left join
       (select cu.column_name  as column_name from user_cons_columns cu,
       user_constraints au where cu.constraint_name = au.constraint_name
       and au.constraint_type = 'P' AND cu.table_name = 'STO_M00_CUSTOMER') mid
on mid.column_name = t.column_name
where t.table_name = 'STO_M00_CUSTOMER'
复制代码

5.数据库进程

• 查看进程

select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from v$locked_object lo, dba_objects ao, v$session sess
where ao.object_id = lo.object_id
and lo.session_id = sess.sid;
复制代码

• 杀死进程

alter system kill session 'sid,serial#' immediate;
复制代码

6.字段操作

• 修改字段类型

  alter table bm_mchnt_table modify (source varchar2(256));
复制代码

• 修改不能为空的字段可以为空

  alter table STL_TXN_DTL modify TRN_FWDFLAG null;
复制代码

• 修改字段的名称

alter table TBL_CNTRLZPAYCNVRT  rename column small_mchnt_no to small_mchnt_id;
复制代码

• 新增字段

  alter table CLEAR_01_T_DAY add (trans_num NUMBER(15));
复制代码

• 删除字段

alter table tablename drop (column);
复制代码

• 修改表名

ALTER TABLE old_table_name RENAME TO new_table_name;
复制代码

• 查询表的最后一次修改时间

select * from user_objects where object_name = 'HX_TRANS_MONTH'
复制代码

• 误删除表之后撤回

flashback table test_tmp to before drop;
复制代码

7.不常用到的

• 查询结果行转列

select country, sum(case when type='A' then money end) as A,  
    sum(case when type='B' then money end) as B
    from table1  group by country 
复制代码

• 去掉字段中的特殊字符

update ORGANIZATION set name  = replace(name,chr(10),'');
复制代码

说明： 制表符chr(9)、换行符 chr(10)、回车符 chr(13)

• 从远程同步表结构

create table MID_TRAN_TERM_MONTH_HIS as select * from
batch.MID_TRAN_TERM_MONTH_HIS@bat03b where 1 = 2;
复制代码

• 查询一个表在哪些存储过程里出现过

SELECT DISTINCT NAME FROM user_source WHERE TYPE = 'PROCEDURE'
 AND upper(text) LIKE '%MID_MER_PROJECT_APP%';
复制代码

• 在终端上远程访问数据库：

 sqlplus prd_dc_lw/prd_dc_lw@144.131.254.138:1521/dcdb
复制代码

• 相同的数据排序获取第一行

SELECT TRANS_CODE_NAME,
         TXN_AMT, 
         BAT_DATE,
         ROW_NUMBER() OVER(PARTITION BY MERNO ORDER BY BAT_DATE) AS NUM
    FROM MID_TN_SSDF_ALL_MCHNT_TMP) WHERE NUM = 1;
复制代码

7.执行效率

• union 和union all的区别 [链接网址]: zhidao.baidu.com/question/57… "请点击这里"

8.性能优化

[链接网址]：zhidao.baidu.com/question/45…

9.字符串截取

[链接网址]：www.jb51.net/article/526…

10.将查询结果横向拼接

[链接网址]：segmentfault.com/q/101000000…

11.查询结果以百分数形式显示

[链接网址]：blog.sina.com.cn/s/blog_614b…

 select 
m.t_merchnum,
 m.lt_merchnum, 
m.cu_merchnum,
  to_char(round((m.cu_merchnum/m.t_merchnum)*100,4),'fm9999990.0099')  || '%' ,
    s.lt_merchnum, 
   s.cu_merchnum, 
  to_char(round((s.cu_merchnum/m.t_merchnum)*100,4),'fm9999990.0099')  || '%' 
 from change_merch_total_mp m , 
 change_merch_total_sm s 
   where m.cityname = s.cityname 
and m.dealdate = s.dealdate 
and m.dealdate = '201805'
复制代码

转载于:https://juejin.im/post/5a7aa33b6fb9a0633426565f