Oracle杂记

2019-07-21

NVL 函数用法

一NVL函数是一个空值转换函数
NVL（表达式1，表达式2）
如果表达式1为空值，NVL返回值为表达式2的值，否则返回表达式1的值。该函数的目的是把一个空值（null）转换成一个实际的值。其表达式的值可以是数字型、字符型和日期型。但是表达式1和表达式2的数据类型必须为同一个类型。
对数字型： NVL（ comm,0);
对字符型 NVL( TO_CHAR(comm), ‘No Commission’)
对日期型 NVL（hiredate,’ 31-DEC-99’)
例子：
select   ename,NVL(TO_char(comm), ename||’ is not a salesperson!’) AS COMMISSION from emp
二 NVL2(表达式1，表达式2，表达式3）
如果表达式1为空，返回值为表达式3的值。如果表达式1不为空，返回值为表达式2的值。
例如 NVL2（comm，’sal+comm’,sal)
NVL2函数测试comm
如果comm为空，就返回sal 的值。如果 comm 不为空(null),就返回表达式 sal+comm的值。

在system用户下

--锁表 死锁 
select sess.sid,  
    sess.serial#, 
    lo.oracle_username,  
    lo.os_user_name,  
    ao.object_name,  
    lo.locked_mode, 
    SESS.machine, 
    'alter system kill session '''||sess.sid||','||sess.serial#||''';' 
    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 '738,1429';

临时表

--会话临时表模板 

CREATE GLOBAL TEMPORARY TABLE A_TMPA   
ON COMMIT PRESERVE ROWS    
AS   
( 
    SELECT * FROM DW_T_LAB 
)  ; 
SELECT * FROM A_TMPA;

零散

 --where后使用order by,需要先分组 再排序 

 select * from b_Merge where name='fuhuo' order by year desc;    

 --分组求和;  (与其他表不同) 

  SELECT class_id ,sum(score)  FROM A_stu  group by class_id ;  

 --开窗函数分组排序 

 SELECT row_number() over(partition by name order by year) b,id, aid,name,year,city FROM b_Merge   
 --开窗函数取第几次 

 SELECT * FROM (SELECT row_number() over(partition by name order by year) b,id, aid,name,year,city FROM b_Merge) where b=2;  
--查询出数据并创建新表 

create table student as ( 

SELECT  class_id,name,score FROM temp_class 

)

Oracle 中 call 和 exec的区别

exec是sqlplus的命令，只能在sqlplus中使用。
call是sql命令，任何工具都可以使用,call必须有括号，即例没有参数  

删除SQL表中的某一列

一行：
DELETE FROM 表名 WHERE 条件
一列：
属于修改表结构慎重考虑
ALTER TABLE 表名 DROP COLUMN 新列名

1	ALTER TABLE warehouse.DW_T_DIAGNOSIS2 DROP COLUMN IS_OTHER_ILDS;

Oracle中sys 和system的默认密码

system默认:manager

sys默认:change_on_install

使用SQL Plus登录数据库时，system使用密码manager可直接登录。

但如果是sys用户，密码必须加上as sysdba，即完整密码为：change_on_install as sysdba