plsql简单操作

626
1、pl/sql

--NULL表示不可用、未赋值、不知道、不适用， 它既不是0 也不是空格。
select null + 2 from dual;

--别名 as  字符串区分大小写 别名中有空格
select last_name as "LastName", salary Salary, salary + 300 from employees;

--连接符||
select last_name || ' salary is ' || salary as "雇员工资" from employees;

--去除重复行 distinct
SELECT DISTINCT department_id FROM employees;

--create table t_char(
--a varchar2(20)
--)
select * from t_char for update;

--模糊查询 使用% 或者_ 作为通配符：
--转义字符 使用ESCAPE 标识转义字符
select * from t_char where a like '%\%%' escape '\';
select * from t_char where a like '%k%%' escape 'k';

--大小写转换函数
select LOWER('SQL Course'), Upper('SQL Course'), initcap('SQL course')
  from dual;
--Oracle数据库中的数据是大小写敏感的
SELECT employee_id, last_name, department_id
  FROM employees
WHERE LOWER(last_name) = 'higgins';

--字符串操作函数
select concat('Hello', 'tanke'),
       substr('tanke', 1, 3),
       length('tanke'),
       instr('tanke', 'k'),
       lpad('tanke', 8, '+'),
       rpad('tanke', 9, '-')，
       trim(' tanke ')
  from dual;
SELECT employee_id,
       CONCAT(first_name, last_name) NAME,
       job_id,
       LENGTH(last_name),
       INSTR(last_name, 'a') "Contains 'a'?"
  FROM employees
WHERE SUBSTR(job_id, 4) = 'REP';

--数字操作函数
SELECT ROUND(45.923, 2), ROUND(45.923, 0), ROUND(45.923, -1) FROM DUAL;
SELECT TRUNC(45.923, 2), TRUNC(45.923), TRUNC(45.923, -2) FROM DUAL;
SELECT last_name, salary, MOD(salary, 5000) FROM employees WHERE job_id = 'SA_REP';

--日期操作函数
--返回下个星期的日期,day为1-7或星期日-星期六,1表示星期日
select NEXT_DAY (to_date('2012-06-26','yyyy-mm-dd'),1) from dual;
select ADD_MONTHS (sysdate,6) from dual;
-------------------------------------------------------------------------------
MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')                 19.6774194
ADD_MONTHS ('11-JAN-94',6)                               11-Jul-94
NEXT_DAY ('01-SEP-95','FRIDAY')                          8-Sep-95
NEXT_DAY ('01-SEP-95',1)                                 3-Sep-95
NEXT_DAY (to_date('1995-09-01','YYYY-MM-DD'),1)          2-Sep-95
LAST_DAY('01-FEB-95')                                    28-Feb-95
ROUND('25-JUL-95','MONTH')                               1-Aug-95
ROUND('25-JUL-95' ,'YEAR')                               1-Jan-96
TRUNC('25-JUL-95' ,'MONTH')                              1-Jul-95
TRUNC('25-JUL-95','YEAR')                                1-Jan-95
--------------------------------------------------------------------------------
--修改注册表 nls_lang AMERICAN_AMERICA.ZHS16GBK
select NEXT_DAY ('01-MAY-95',1) from dual;
SELECT last_name,
       (SYSDATE - hire_date) / 7 AS WEEKS,
       sysdate + 1 as tomorrow,
       hire_date + 8 / 24
  FROM employees
WHERE department_id = 90;

--转换测试(隐式转换)
select * from test1;
select * from test1 where column1=2;

--显示转换 to_char to_number to_date
--TO_CHAR(date, 'format_model') ;
--------------------------------------------------------------------------------------
日期格式化元素                                       意义
YYYY                                            4位数字表示的年份
YEAR                                            英文描述的年份
MM                                              2位数字表示的月份
MONTH                                           英文描述的月份
MON                                             三个字母的英文描述月份简称
DD                                              2位数字表示的日期
DAY                                             英文描述的星期几
DY                                              三个字母的英文描述的星期几简称
HH24:MI:SS AM                                   时分秒的格式化
DDspth                                          英文描述的月中第几天
fm                                              格式化关键字，可选
---------------------------------------------------------------------------------------
select to_char(sysdate,'DAY') from dual;
SELECT last_name, TO_CHAR(hire_date, 'fmDD "of" Month YYYY') AS HIREDATE
  FROM employees;
--TO_CHAR() 函数：数字到字符串的转换
----------------------------------------------------------------------------------
数字格式化元素                        意义
9                                   表示一个数字
0                                   强制显示0
$                                   放一个美元占位符
L                                   使用浮点本地币种符号
.                                   显示一个小数点占位符
,                                   显示一个千分位占位符
---------------------------------------------------------------------------------
alter session set NLS_CURRENCY = '￥';
SELECT TO_CHAR(salary, 'L99,999.00') SALARY FROM employees
WHERE last_name = 'Ernst';
--TO_DATE() 函数：字符串到日期的转换
SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-YYYY')
FROM employees
WHERE hire_date < TO_DATE('01-Jan-90', 'DD-Mon-RR');

--其他单行函数
------------------------------------------------------------------------------
函数                                      用途
NVL(expr1, expr2)                   如果expr1为空，这返回expr2
NVL2 (expr1, expr2, expr3)          如果expr1为空，这返回expr3（第2个结果）否则返回expr2
NULLIF (expr1, expr2)               如果expr1和expr2相等，则返回空
COALESCE (expr1, expr2, ..., exprn)
                                    如果expr1不为空，则返回expr1,结束；否则计算expr2,直到找到
                                    一个不为NULL的值或者如果全部为NULL，也只能返回NULL
-------------------------------------------------------------------------------------
--条件表达式
--case
SELECT last_name, job_id, salary,
           CASE job_id
                WHEN 'IT_PROG' THEN 1.10*salary
                WHEN 'ST_CLERK' THEN 1.15*salary
                WHEN 'SA_REP' THEN 1.20*salary
                ELSE salary
           END "REVISED_SALARY"
FROM employees;
--decode
SELECT last_name, job_id, salary,
      DECODE(job_id, 'IT_PROG', 1.10*salary,
                     'ST_CLERK', 1.15*salary,
                     'SA_REP', 1.20*salary,
                      salary) REVISED_SALARY
FROM employees;


--全外连接
SELECT e.last_name, e.department_id, d.department_name
FROM employees e,departments d
where e.department_id(+) = d.department_id
union
SELECT e.last_name, e.department_id, d.department_name
FROM employees e,departments d
where e.department_id = d.department_id(+);

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

--count函数
--insert into test1 values(null);
select * from test1;
select count(*) from test1;--包含空行
select count(1) from test1;--不包含空行
select count(column1) from test1;
select count(distinct column1) from test1;
select count(column1) from (select column1 from test1 group by column1);
select count(*) from (select column1 from test1 group by column1);

--当分组计算函数遇到NULL:avg 不考虑分组
SELECT AVG(commission_pct) FROM employees;
select (select sum(commission_pct) from employees)/(select count(*) from employees ) from dual;
select (select sum(commission_pct) from employees)/(select count(commission_pct) from employees )
from dual;
SELECT AVG(NVL(commission_pct, 0)) FROM employees;

--select * from test1 for update nowait;