SQL
Distinct
NULL
||连接符
Oracle数据库中大小写敏感
要求找出含有%的记录
这需要使用ESCAPE标识转义字符
select * from t_charwhere a like ‘%\%%‘ escape ‘\‘;
select * fromt_char where a like ‘%K%%‘ escape ‘K‘;
PLSQL中日期比较
1.格式需要一致，可以通过字符串比较。
2.TO_CHAR(CREATE_DATE,‘yyyy-mm-dd‘)>=NVL(P_CREATE_FROMDATE,TO_CHAR(CREATE_DATE,‘yyyy-mm-dd‘))
2. START_DATE <= TO_date(p_trans_date,‘YYYY-MM-DD‘)
--不易发现的错误
select * from test1where column1 = ‘1234‘;--将所有行转换为字符串
select * from test1where column1 = 1234;--将所有行转换为数字，包含字符的行出错
--连接
-oracle全外外连接
select e.last_name,e.department_id,d.department_namefrom employees e,departments dwhere e.department_id=d.department_id(+)union
select e.last_name,e.department_id,d.department_namefrom employees e,departments dwhere e.department_id(+)=d.department_id;
--sql1999标准
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
FULLOUTERJOIN departments d
ON (e.department_id =d.department_id) ;
分组计算函数和group by子句
备注：MIN, MAX 可用于任何数据类型，但AVG， SUM，STDDEV,VARIANCE仅适用于数值型字段。
使用GROUP BY 子句进行分组:
l 可以按照某一个字段分组，也可以按照多个字段的组合进行分组
l SELECT查询语句中同时选择分组计算函数表达式和其他独立字段时，其他字段必须出现在Group By子句中，否则不合法。
l 不能在Where条件中使用分组计算函数表达式，当出现这样的需求的时候，使用Having 子句。
l 分组计算函数也可嵌套使用。
事务控制
隐式的事务提交或回滚动作：
Commit,rollback 是显式的提交和回滚语句，还有一些隐式的提交和回滚是大家需要知道并引起注意的：
当如下事件发生是，会隐式的执行Commit动作：
1、数据定义语句被执行的时候，比如新建一张表：Create Table …
2、数据控制语句被执行的时候，比如赋权GRANT …( 或者DENY)
3、正常退出iSQL*Plus 或者PLSQLDEVELOPER, 而没有显式的执行COMMIT 或者ROLLBACK 语句。
当如下事件发生时，会隐式执行Rollback 动作：
1、非正常退出iSQL*Plus, PLSQL DEVELOPER, 或者发生系统错误。
在Commit 或者Rollback前后数据的状态：
1、在数据已经被更改，但没有Commit前，被更改记录处于被锁定状态，其他用户无法进行更改；
2、在数据已经被更改，但没有Commit前，只有当前Session的用户可以看到这种变更，其他Session的用户
看不到数据的变化。
3、在数据已经被更改，并且被Commit后，被更改记录自动解锁，其他用户可以进行更改；
4、在数据已经被更改，并且被Commit后，其他Session的用户再次访问这些数据时，看到的是变化后的数据。
那么同理可知Rollback前后数据的状态及锁的变化。
锁
表的命名要求和表中列的命名要求：
1、必须以字母开头
2、长度不能超过30个字符
3、只能包含A–Z,a–z, 0–9,_, $, and #
4、不能与数据库中的已有对象重名
5、不能使用Oracle 数据库的保留字
注意TRUNCATE 与DELETE FROM table 的区别： 1）没有Rollback机会2）HWM标记复位（HWM高水位线）都不会删除表结构
约束
更改表的语法：
添加列：
ALTERTABLE table
ADD(column datatype [DEFAULT expr]
[, columndatatype]...);
更改列：
ALTERTABLE table
MODIFY(column datatype [DEFAULT expr]
[, columndatatype]...);
删除列：
ALTERTABLE table
DROP(column);
ALTER TABLE tablenameADD CONSTRAINT constraintname
constrainttype(column1,…);
CONSTRAINT emp_email_ukUNIQUE(email))
CONSTRAINT dept_id_pk PRIMARYKEY(department_id))
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCESdepartments(department_id)
CONSTRAINT emp_salary_min CHECK (salary > 0)
索引、序列、同义词
函数索引
CREATE INDEX upper_dept_name_idx
ON departments(UPPER(department_name));
控制用户权限
GRANT object_priv [(columns)]
ON object
TO {user|role|PUBLIC}
[WITHGRANT OPTION];
如果你想让其他用户也有权把你赋给他的权限进一步赋予给别人，那么需要带WITH GRANT OPTION;
GRANT select, insert
ON departments
TO scott
WITH GRANT OPTION;
撤销权限
REVOKE {privilege [, privilege...]|ALL}
ON object
FROM{user[, user...]|role|PUBLIC}
创建DB-LINK，通过DB-LINK访问另一数据库中的表
CREATE PUBLIC DATABASE LINK hq.acme.com
USING‘sales‘;
SELECT*
FROMemp@HQ.ACME.COM;
递归查询
SELECTlast_name||‘ reports to ‘||
PRIORlast_name "Walk Top Down"
FROMemployees
STARTWITH last_name = ‘King‘
CONNECTBY PRIOR employee_id = manager_id ;
使用LEVEL关键字和LPAD函数，在OUTPUT中显示树形层次。
SELECT LPAD(last_name,LENGTH(last_name)+(LEVEL*2)-2,‘_‘)
AS org_chart
FROM employees
START WITH last_name=‘King‘
CONNECT BYPRIOR employee_id=manager_id
Group By 子句的增强
在Group By 中使用Rollup 产生常规分组汇总行以及分组小计
在Group By 中使用Cube 产生Rollup结果集+ 多维度的交叉表数据源
GROUPING函数：Rollup 和Cube有点抽象，他分别相当于n+1 和2的n次方常规Group by 运
算；那么在Rollup 和Cube的结果集中如何很明确的看出哪些行是针对那些列或者列的组合进行
分组运算的结果的？ 答案是可以使用Grouping 函数； 没有被Grouping到返回1，否则返回0
使用Grouping Set 来代替多次UNION
Exercise
--14. Showall employees who have managers with a salary higher than $15,000.
--Show the following data: employeename, manager name, manager salary, and salary grade of the manager.
createtable job_grades(grade_levelvarchar(20),lowest_salnumber,highest_salnumber);
insertinto job_gradesvalues(‘E‘,10000,1000000);
insertinto job_gradesvalues(‘E‘,9000,900000);
SELECT e.last_name, m.last_name manager,m.salary, j.grade_level
FROM employees e,employees m, job_grades j
WHERE e.manager_id = m.employee_id
AND m.salaryBETWEEN j.lowest_salAND j.highest_sal
AND m.salary >15000;
--15. Showthe department number, name, number of employees, and average salary of alldepartments,
--together with the names, salaries,and jobs of the employees working in each department.
SELECT d.department_id,
d.department_name,
count(e1.employee_id)employees,
NVL(TO_CHAR(AVG(e1.salary),‘99999.99‘), ‘No average‘) avg_sal,
e2.last_name,
e2.salary,
e2.job_id
FROM departments d,employees e1, employees e2
WHERE d.department_id = e1.department_id(+)
AND d.department_id =e2.department_id(+)
GROUPBY d.department_id,
d.department_name,
e2.last_name,
e2.salary,
e2.job_id
ORDERBY d.department_id, employees;
--19. Showthe employee number, last name, salary, department number, and the averagesalary in their department for all employees.
select e.employee_id, e.last_name,e.department_id,avg(d.salary)
from employees d,employees e
where d.department_id = e.department_id
groupby e.employee_id, e.last_name, e.department_id;
--31. Write a query to delete the oldestJOB_HISTORY row of an employee bylooking up the
--JOB_HISTORY table for theMIN(START_DATE) for the employee.
--Delete the records of only those employees who have changed at leasttwo jobs.
--If your query executes correctly, you will get the feedback:
DELETEFROM job_history JH
WHERE employee_id = (SELECT employee_id
FROM employees E
WHERE JH.employee_id = E.employee_id
AND START_DATE =
(SELECTMIN(start_date)
FROM job_history JH
WHERE JH.employee_id =E.employee_id)
AND3 > (SELECTCOUNT(*)
FROM job_history JH
WHERE JH.employee_id =E.employee_id
GROUPBY EMPLOYEE_ID
HAVINGCOUNT(*) >=2));
--41. Write a SQL script file to dropall objects (tables, views, indexes, sequences, synonyms, and so on) that youown.
SET HEADINGOFF ECHO OFF FEEDBACKOFF
SET PAGESIZE0
SELECT‘DROP‘ || object_type ||‘ ‘ || object_name ||‘;‘
FROM user_objects
ORDERBY object_type
SET HEADINGON ECHO ON FEEDBACKON
SET PAGESIZE24
PL/SQL
用到的数据库Student1/student ora1/ora2/ora3 oracle system/manager
DECLARE
v_variable VARCHAR2(5);
BEGIN
SELECT column_name
INTO v_variable
FROM table_name;
EXCEPTION
WHEN exception_name THEN
...
END;
SQL不能select into PL/SQL可以。
PLSQL的块包括三种：匿名块、存储过程、函数；
PLSQL特有的%TYPE属性来声明与XX类型一致的变量类型：
identifier Table.column_name%TYPE;
记录类型的定义语法：
TYPE type_name IS RECORD
(field_declaration[, field_declaration]…);
identifier type_name;
这是一个简单数组
TYPE ename_table_type IS TABLE OF
employees.last_name%TYPE
INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
隐式游标的几个有用属性：
SQL%ROWCOUNT 受最近的SQL语句影响的行数
SQL%FOUND 最近的SQL语句是否影响了一行以上的数据
SQL%NOTFOUND 最近的SQL语句是否未影响任何数据
SQL%ISOPEN 对于隐式游标而言永远为FALSE
显式游标的相关函数可以做到：
1、一行一行的处理返回的数据。
2、保持当前处理行的一个跟踪，像一个指针一样指示当前的处理的记录。
3、允许程序员在PLSQL块中人为的控制游标的开启、关闭、上下移动；
游标推荐写法
DECLARE
CURSOR emp_cursor IS
SELECT last_name, department_id
FROM employees;
BEGIN
FOR emp_record IN emp_cursor LOOP
-- implicit open and implicit fetch occur
IF emp_record.department_id = 80 THEN
...
END LOOP; -- implicit close occurs
END;
异常处理
BEGIN
. . .
EXCEPTION
WHEN NO_DATA_FOUND THEN
statement1;
WHEN TOO_MANY_ROWS THEN
statement1;
WHEN OTHERS THEN
statement1;
statement2;
END;
OTHERS
DECLARE
v_error_code NUMBER;
v_error_message VARCHAR2(255);
BEGIN
...
EXCEPTION
...
WHEN OTHERS THEN
ROLLBACK;
v_error_code := SQLCODE ;
v_error_message := SQLERRM ;
INSERT INTO errors
VALUES(v_error_code, v_error_message);
END;
想要在SQL语句中可以使用用户自定义的函数，那么这样的用户定义函数有哪些限制？
答: 有如下限制：
必须是个函数（不能是过程-Procedure）
只能用IN 模式的参数（不能有OUT, IN OUT 模式的参数）
只能接收SQL数据类型的参数，不能接收PLSQL 中特有的参数（比如记录、PLSQL内
存表）
函数返回的数据类型也必须是有效的数据类型，而不能是PLSQL特有的数据类型
在SQL中使用的函数，其函数体内部不能有DML语句。
在UPDATE/DELETE语句中调用的函数，其函数体内部不能有针对同一张表的查询语句
在SQL中调用的函数，其函数体内部不能有事务结束语句（比如Commit，Rollback)
定义者权限：函数执行时，对表的访问默认使用定义者权限。
那么什么情况会使用调用者权限呢？这需要在写函数的时候有特殊语句标识：AUTHID CURRENT_USER
Package好处：
1、模块化：一般把有相关性的函数和过程放到一个Package中；
2、易设计：可以把包说明和包体分别编写和编译，先编写和编译包说明部分，在编写和说明包体部分；这有利
于分工合作；
3、信息隐藏：包体中函数可以部分出现在包说明中，只有出现在包说明中的函数和过程才是该Package的公有
函数和过程，可以被其他包中的函数调用，否则对其他包中的函数是不可见的，未在包说明部分出现的函数
和过程相当于私有的。
4、加载性能提高：当Package中有一个函数或过程被调用时，整个Packege就被加载到内存中，这样当该
Package中其他函数被调用时，就直接从内存读取了，可以减少磁盘IO,从而提高性能。这个特性也提醒
我们不要去搞巨无霸的Package,把你用到的任何函数都写到一个Package中，这会导致严重的内存浪费。
5、重载：一个package中可以定义同名、不同参数的函数或过程。
存储过程
FOR UPDATE NOWAIT语句：有的时候我们打开一个游标是为了更新或者删除一些记录，这种情况下我们希望
在打开游标的时候即锁定相关记录，应该使用for update nowait语句，倘若锁定失败我们就停止不再继续，以免出现长时间等待资源的死锁情况。
DECLARE
CURSOR sal_cursor IS
SELECT e.department_id, employee_id, last_name, salary
FROM employees e, departments d
WHERE d.department_id = e.department_id
and d.department_id = 60
FOR UPDATE OF salary NOWAIT;
BEGIN
FOR emp_record IN sal_cursor
LOOP
IF emp_record.salary < 5000 THEN
UPDATE employees
SET salary = emp_record.salary * 1.10
WHERE CURRENT OF sal_cursor;
END IF;
END LOOP;
END;
动态SQL：不是在Designer Time写的SQL,而是可以在运行时临时拼接起来的SQL语句；
动态SQL可以使用Oracle内置包DBMS_SQL来执行，也可以使用EXECUTE IMMEDIATE语句来执行：
v_filehandle := UTL_FILE.FOPEN (p_filedir, p_filename,‘w‘);
UTL_FILE.PUTF (v_filehandle,‘SALARY REPORT: GENERATED ON
%s\n‘, SYSDATE);
UTL_FILE.NEW_LINE(v_filehandle);
Oracle数据库里面的LOB有四种类型：
1、CLOB：字符大对象，存储在数据库内部；
2、NCLOB：多字节字符大对象，存储在数据库内部；
3、BLOB：二进制大对象，存储在数据库内部；
4、BFILE：二进制文件，存储在数据库外部；
创建Trigger：Trigger的定义语句里面涉及到如下关键因素：
时机：Before或者After或Instead of
事件：Insert或Update或Delete
对象：表名（或视图名）
类型：Row或者Statement级；
条件：满足特定Where条件才执行；
内容：通常是一段PLSQL块代码；
重点注意：
Instead of : 用Trigger的内容替换事件本身的动作
Row级：SQL语句影响到的每一行都会引发Trigger
Statement级：一句SQL语句引发一次，不管它影响多少行（甚至0行）
PLSQL
1. Oracle 同义词
相当于alias(别名),比如把user1.table1在user2中建一个同义词table1
create synonym table1 for user1.table1;
这样当你在user2中查select * from table1时就相当于查select * from user1.table1;
优点自己总结吧。
2. Oracle 受权 ORA-00990: 权限缺失或无效
创建一个用户：
create user xxx identified by yyy
当密码里面有特殊字符时 windows平台下面应该用 "yyy" 其它平台未测试
刚创建好的用户是没有任何权限的 连登录数据库的权限都没有
这时，就要给用户授予权限：
ORACLE里面的权限有系统权限和对象权限两种
可以通过
select distinct p.privilege
from user_sys_privs p
order by p.privilege asc；
来查询ORACLE里面的所有系统权限；
常用的系统权限有 ：
CREATE SESSION 创建会话 登录数据库
CREATE SEQUENCE 创建序列
CREATE SYNONYM 创建同名对象
CREATE TABLE 在用户模式中创建表
CREATE ANY TABLE 在任何模式中创建表
DROP ANY TABLE 在任何模式中删除表
CREATE PROCEDURE 创建存储过程
EXECUTE ANY PROCEDURE 执行任何模式的存储过程
CREATE USER 创建用户
DROP USER 删除用户
CREATE VIEW 创建视图
通过下面的SQL可以查询所有的对象权限：
select distinct t.privilege fromuser_tab_privs t;
结果如下：
FLASHBACK
EXECUTE
ONCOMMIT REFRESH
ALTER
DEQUEUE
UPDATE
DELETE
DEBUG
UNDER
QUERY REWRITE
SELECT
READ
INSERT
INDEX
WRITE
REFERENCES
MERGE VIEW
另外还有角色授权 可以通过下面的SQL来查询ORALCE里面内置有哪些角色：
select distinct r.granted_role role_name
from user_role_privs r
where r.default_role = ‘YES‘
order by role_name asc;
授权语句：grant 权限
系统授权直接写：grant 权限名称 to 用户名；
对象权限则要写成：grant 权限名称 on schema. 对象名 to 用户名
还要注意一点是：授予权的时候系统权限和对象权限不能同时写，也就是说不能写成下面这个样子：
GRANT CREATE TABLE, SELECT ON scott.testTO scott1
这样写会报 ORA-00990: 权限缺失或无效
但是系统权限和角色是可以同时写在一起授予给某一个用户的
3. oracle之报错:ORA-00054: 资源正忙，要求指定NOWAIT
解决方案：
select session_id from v$locked_object; --首先得到被锁对象的session_id
SELECT sid, serial#, username, osuser FROM v$sessionwhere sid = session_id; --通过上面得到的session_id去取得v$session的sid和serial#，然后对该进程进行终止。
ALTER SYSTEM KILL SESSION ‘sid,serial‘;
example:
ALTER SYSTEM KILL SESSION ‘13, 8‘;
--ora0054
--查找锁定的会话
select session_id fromv$locked_object;
SELECTsid, serial#,username, osuser
FROM v$session
wheresidin (4908, 4913, 4955);
--杀死会话
ALTERSYSTEMKILLSESSION‘4908,2223‘;
ALTERSYSTEMKILLSESSION‘4913,937‘;
ALTERSYSTEMKILLSESSION‘4955,3079‘;
--查找锁
select * from v$lock whereblock=1;
select * from v$locked_object;
--查看当前用户的一些权限
selectdistinct p.privilegefromuser_sys_privs p;
4. 层次查询实例也可以利用两个嵌套的游标实现
PLSQL\PACKAGE_GET_AUDIT_DESC.pck
selectdistinct x.form_kind, x.form_no, auditm
from (selectt.FORM_KIND,
t.FORM_NO,
substr(sys_connect_by_path(t.AUDITM,‘,‘), 2) auditm,
level lv,
max(level) over(partitionbyt.FORM_KIND, t.FORM_NO) max_level
fromdw_tes_flow_audit_fs_v t
where1 = 1
/*andt.FORM_KIND = ‘SYS.FORM.009‘
andt.FORM_NO = ‘277721‘*/
andconnect_by_isleaf = 1
connectbyprior t.FORM_KIND= t.FORM_KIND
andprior t.FORM_NO =t.FORM_NO
andprior t.AUDITM< t.AUDITM) x
where lv =max_level;