oracle实用语句及函数记录

查询插入：insert into table_A(col1,col2) select col1,col2 from tab_B where condition;

 

补齐函数：lpad(col1,length,'a'),左边补齐函数，用于处理数据，将col1列的数据，格式化成固定length长度的字符，不足长度时用'a'补充，rpad是右边补齐函数

 

空值转换：nvl(col1,'a'),将col1列的NULL值或者空值转换成'a'

 

CASE查询：case   SELECT cust_last_name, CASE credit_limit WHEN 100 THEN 'Low'   WHEN 5000 THEN High'   ELSE 'Medium' END   FROM customers;

SELECT department_id
  FROM departments d
  WHERE EXISTS
  (SELECT * FROM employees e
    WHERE d.department_id 
    = e.department_id);