Oracle DB 学习备注

1.如何查询出oracle的中文行

select * from user_tab_cols where  length(column_name)<>lengthb(column_name);

2.PLSQL查询结果中文列名显示为“???”或是中文查询条件无结果的状况：

2.1首先执行语句 select * from V$NLS_PARAMETERS  查看第一行中PARAMETER项中为NLS_LANGUAGE 对应的VALUE项中是否为SIMPLIFIED CHINESE。如果不是，需要设置环境变量。
2.2我的电脑-属性-“高级”-环境变量，在系统变量处点击“新建”，设置变量名：NLS_LANG ， 变量值：SIMPLIFIED CHINESE_CHINA.ZHS16GBK 。

3.对比日期

select * from aa where date1>to_date('2008-08-08','yyyy-mm-dd')

4.通过比较A,B两个日期型字段，如果A大于B，则返回1；否则，返回0

selectcase when A-B>0 then 1 else 0 endfrom 表

5.排序
  5.1 正序 order by
  5.2 倒序 order by desc
6.取最大日期

    select max(日期) from 表

7.去重复

    select  distinct(job) from emp    select distinct  job, name  from emp

8.合并

    union all

9.查找重复次数

    select chcod,count(1) from a    group by chcod    having count(1)> 1

10.oracle截取字符前面的值

    substr(b.PERIOD_ENTERED,1,instr(b.PERIOD_ENTERED,'-')-1)

11.使用delete语句删除表行数据时候，出现时间在增长，却没有其他反应，表明：表被锁了；

--首先查看有哪些锁

select /*+ rule */ s.username,      decode(l.type,'TM','TABLE LOCK','TX','ROW LOCK',null) lock_level,      o.owner,      o.object_name,      o.object_type,      s.sid,s.serial#,      s.terminal,      s.machine,      s.program,      s.osuserfrom v$session s,v$lock l,dba_objects owhere l.sid = s.sidand l.id1 = o.object_id(+)and s.username is not null

 

--如果发生了锁等待，看是谁锁了表而引起谁的等待

--以下的语句可以查询到谁锁了表，而谁在等待。 如果有子节点，则表示有等待发生

select /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username user_name,       o.owner,       o.object_name,       o.object_type,       s.sid,       s.serial#from v$locked_object l,dba_objects o,v$session swhere l.object_id=o.object_idand l.session_id=s.sidorder by o.object_id,xidusn desc

 

--找到引起等待的session,杀掉该session: 

alter system kill session 'sid,serial#';

12.如何在Oracle中复制表结构和表数据
12.1. 复制表结构及其数据：

create table table_name_new as select * from table_name_old

12.2. 只复制表结构：

create table table_name_new as select * from table_name_old where 1=2;

或者：

create table table_name_new like table_name_old

12.3. 只复制表数据：
12.3.1如果两个表结构一样：

insert into table_name_new select * from table_name_old

或者

insert into table_name_new values table_name_old

12.3.2如果两个表结构不一样：

insert into table_name_new(column1,column2...) select column1,column2... from table_name_oldpasting

或者

insert into table_name_new(column1,column2...) values table_name_old(column1,column2...)

13.MONTHS_BETWEEN(DATE1,DATE2)MONTHS_BETWEEN函数返回两个日期之间的月份数。

months_between(to_date(to_char(sysdate, 'yyyymm'),'yyyymm'),                                     to_date(to_char(invoice_date, 'yyyymm'),                                             'yyyymm')) <= 12

或者

select months_between(to_date('20090228', 'yyyymmdd'), to_date('20080228', 'yyyymmdd')) as months from dual;

14. 字符串截断SUBSTR（string, start_position, [ length ]） 
                     string：字符串值；
      start_position：截取字符串的初始位置， Number型，start_position为负数时，表示从字符串右边数起；
                    length：截取位数，Number型，length为可选，如果length为空（即不填）则返回start_position后面的所有字符。
      举例：

　　STORE'abcdefghijklm' To mystring　　SUBSTR(mystring ,1,5) 显示 "abcde"　　SUBSTR(mystring ,6) 显示 "fghijklm"　　SUBSTR(mystring,-2)显示“lm”　　SUBSTR(mystrng,-4)显示“jklm”

15.运行包中的主程序
       找到包内的主程序，右键-测试，弹出测试脚本，F8运行；（有时候PLSQL会有缓存，可以重进后运行主程序；PS，该方法应该不适用其他人）

16.查看oracle用户状态信息：

    使用SQL Plus来查看信息，使用sys登陆：

sys as sysdba;

    查看用户表信息：

select username, account_status from dba_users; 

    oracle 的用户信息都在dba_users表中， 默认只有sys、和sysdba 两个用户是open的。

alter user scott account unlock; 这个命令是给scott用户解锁；

alter user scott identified by tiger; 这个命令是给scott用户设置密码；

 通过这两个语句,scott 用户就可以使用了。

 另外：设置数据库默认密码为永久有效

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED ;

17.部分字段替代函数

例：需要将列“xxxx/yy/mm”修改为“xxxx-yy-mm”

update 表1 t set t.列1=replace((select 列1from 表1 a where a.主键列=t.主键列) , '/' , '-' )
 replace（'将要更改的字符串','被替换掉的字符串','替换字符串'）

select  replace ('111222333444','222','888') from dual;                输出为 '111888333444'

在查询的时候将调整期间ADJ替换成12

SELECT case when p like '%ADJ' then replace(p,'ADJ','12') else p end period,a FROM (select '2012-12' p,100 a from dualunion allselect '2012-ADJ' p,200 a from dual ) X

18.物化视图

新建语法：

create materialized view materialized_view_namebuild [immediate|deferred]  --1.创建方式refresh [complete|fast|force|never]     --2.物化视图刷新方式on [commit|demand]   --3.刷新触发方式start with (start_date)   --4.开始时间next (interval_date)   --5.间隔时间with [primary key|rowid]  --默认 primary keyENABLE QUERY REWRITE   --7.是否启用查询重写as     --8.关键字select statement;   --9.基表选取数据的select语句

1.创建方式默认是immediate，立即；deferred，延迟，至第一次refresh时，才生效。
2.物化视图刷新方式默认为force，意思是：如果可以快速刷新，就执行快速刷新；否则，执行完全刷新。
  complete，完全刷新，即，刷新时更新全部数据，包括视图中已经生成的原有数据。
  fast，快速刷新，只刷新增量部分。前提是，需要在基表上创建物化视图日志（见示例）。该日志记录基表数据变化情况，所以才能实现增量刷新。
  never，从不刷新。
3.on commit，基表有commit动作时，刷新视图，不能跨库执行(因为不知道别的库的提交动作)；on demand，在需要时刷新，即，根据后面设定的起始时间和时间间隔进行刷新，或者，手动调用dbms_mview包中的过程刷新时再执行刷新。
4和5.即开始刷新时间和下次刷新的时间间隔。如：start with sysdate next sysdate+1/1440 表示马上开始，刷新间隔为1分钟。（与 on commit选项冲突）
6.基于基表的主键或者rowid创建。如果是基于rowid，不能对基表执行分组函数、多表连结等需要把多个rowid合成一行的操作（理由很简单：到底以哪个rowid为准呢？）。
7.如果设置了初始化参数query_rewrite_enabled=true则默认就会启用查询重写。但是，数据库默认该参数为false。并且，不是什么时候都应该启用查询重写。所以，该参数应该设置为false，而在创建特定物化视图时，根据需要开启该功能。另外，如果选择使用了上面第4‘5选项，则不支持查询重写功能（原因很简单，所谓重写，就是将对基表的查询定位到了物化视图上，而4、5选项会造成物化视图上部分数据延迟，所以，不能重写）。
例：

CREATE MATERIALIZED VIEW CUX_FA_MAIN_RESERVE_MVREFRESH FORCE ON DEMANDSTART WITH TO_DATE('07-09-2016 11:58:10', 'DD-MM-YYYY HH24:MI:SS') NEXT SYSDATE+4/24 AS SELECT * from fa_books_v;

取消语法：

DROP MATERIALIZED VIEW CUX_FA_MAIN_RESERVE_MV;

注意：

DROP            语句将删除表的结构，被依赖的约束(constrain)、触发器(trigger)、索引(index)；依赖于该表的存储过程/函数将保留,但是变为 invalid 状态；（不能回滚）

DELETE        只删除数据不删除表的结构(定义)，且删除部分数据与事务有关，如：trigger；（可以回滚）

TRUNCATE  只删除数据不删除表的结构(定义)，且删除全部数据与事务无关；（不能回滚）

修改刷新方式：

对于已经创建好的物化视图，可以修改其刷新方式（只能修改刷新方式，不能修改代码）：

alter materialized view CUX_FA_MAIN_RESERVE_MV refresh force on demand START WITH TO_DATE('09-09-2016 15:58:37', 'DD-MM-YYYY HH24:MI:SS') NEXT SYSDATE+6/24;  

19.用户权限查看

19.1.查看所有用户：

  select * from dba_users;  select * from all_users;  select * from user_users;

19.2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：

  select * from dba_sys_privs;  select * from user_sys_privs;

19.3.查看角色(只能查看登陆用户拥有的角色)所包含的权限

sql>select * from role_sys_privs;

19.4.查看用户对象权限：

  select * from dba_tab_privs;  select * from all_tab_privs;  select * from user_tab_privs;

19.5.查看所有角色：

  select * from dba_roles;

19.6.查看用户或角色所拥有的角色：

  select * from dba_role_privs;  select * from user_role_privs;

19.7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)

select * from V$PWFILE_USERS

19.8.以下语句可以查看Oracle提供的系统权限：

select name from sys.system_privilege_map

19.9.查看一个用户的所有系统权限(包含角色的系统权限)

    select privilege from dba_sys_privs where grantee='DATAUSER'      union      select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='DATAUSER' );   

20.NVL函数

对于NVL函数，需要知道什么是oracle中的空值null

20.1 NVL函数

NVL函数的格式：NVL(expr1,expr2)

如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

select nvl(null,2) from dual;

20.2 NVL2函数

NVL2函数的格式：NVL2(expr1,expr2,expr3)

如果函数的第一个参数不为空，那么显示第二个参数的值，如果第一个参数的值为空，则显示第三个参数的值。

select nvl2(comm,-1,1) from dual;

20.3 NULLIF函数

NULLIF函数的格式：NULLIF(expr1,expr2)

如果expr1和expr2相等，则返回空值null，否则返回第一个值。

20.4 Coalesce函数

该函数与NVL函数相似，其优势就是有更多的选项。

Coalesce函数的格式：Coalesce(expr1,expr2,expr3......,exprn)

函数返回表达式中第一个非空的值。

select coalesce(null,null,3,4,5) from dual;

返回值为：3；

如果所有自变量均为null，则coalesce返回null值。

21.内连接，左连接，右连接

21.1 内连接

内连接，即最常见的等值连接：

select * from TESTA,TESTBWHERE TESTA.A=TESTB.A

21.2 外连接

外连接分为左外连接，右外连接和全外连接

21.2.1 左外连接left join

左外连接就是在等值连接的基础上加上主表中的未匹配数据：

select * from TESTAleft join TESTBon TESTA.A=TESTB.A

或者

select * from TESTA,TESTBwhere TESTA.A=TESTB.A(+)

21.2.2 右连接right join

右外连接是等值连接的基础上加上被连接表的不匹配数据：

select * from TESTAright join TESTBon TESTA.A=TESTB.A

或者

select * from TESTA,TESTBWHERE TESTA.A(+)=TESTB.A

21.2.3 全外连接 full join

全外连接是在等值连接的基础上将左表和右表的未匹配数据都加上：

select * from TESTAfull join TESTBon TESTA.A=TESTB.A

22.列转行函数Listagg()

Listagg函数的格式：Listagg(expr1,expr2) within group(order by expr1)

expr1为列转行值，expr2为隔离标点；

    with temp as(        select 'China' nation ,'Guangzhou' city from dual union all        select 'China' nation ,'Shanghai' city from dual union all        select 'China' nation ,'Beijing' city from dual union all        select 'USA' nation ,'New York' city from dual union all        select 'USA' nation ,'Bostom' city from dual union all        select 'Japan' nation ,'Tokyo' city from dual       )      select nation,listagg(city,',') within GROUP (order by city)      from temp      group by nation  

用法就像聚合函数一样，通过Group by语句，把每个Group的一个字段，拼接起来。
非常方便。

同样是聚合函数，还有一个高级用法：
就是over(partition by expr1)
也就是说，在你不实用Group by语句时候，也可以使用LISTAGG函数：

with temp as(    select 500 population, 'China' nation ,'Guangzhou' city from dual union all    select 1500 population, 'China' nation ,'Shanghai' city from dual union all    select 500 population, 'China' nation ,'Beijing' city from dual union all    select 1000 population, 'USA' nation ,'New York' city from dual union all    select 500 population, 'USA' nation ,'Bostom' city from dual union all    select 500 population, 'Japan' nation ,'Tokyo' city from dual   )  select population,         nation,         city,         listagg(city,',') within GROUP (order by city) over (partition by nation) rank  from temp 

总结：LISTAGG()把它当作SUM()函数来使用就可以了。