plsql简单操作

626[size=medium]
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;
[/size]