Oracle学习笔记

一、 SQL
 基本的SQL SELECT语句
1. 查询：SELECT  数据操作语句：INSERT, DELETE, UPDATE, MERGE
2. 合并：把一个表的数据合并到另一个表中去，如若数据在原表在存在则UPDATE，否则INSERT
3. 事务控制语句：COMMIT提交，ROLLBACK回滚，SAVEPOINT存储点（与ROLLBACK搭配使用，可回滚到某个存储点，否则到最初点）
4. 数据定义语句：对对象操作。TRUNCATE清除表中所有数据， CREATE 创建，DROP删除，ALTER修改
5. 权限控制语句（DCL）：GRANT授予权限，REVOKE移除权限
 单行函数
1. LOWER()强制小写
2. UPPER()强制大写
3. INITCAP()每个单词首字母大写
4. CONCAT(‘’,’’)连接函数
5. SUBSTR(str, a[,b])返回str从位置a开始的b个字符
6. LENGTH(str)返回str的长度
7. INSTR(str1, str2[,a][,b])返回str2在str1中的位置，a表示扫描的起始位置，b表示第几次出的位置
8. LPAD(col, n, ‘c’)在col列的左边补n个c字符
9. RPAD(col, n, ‘c’)在col列的右边补n个c字符
10. LTRIM()左截取
11. RTRIM()右截取
12. ascii(x)返回字符x的十进制，即ASCII码值
13. chr(x),返回ASCII码为x的字符
14. replace(x,y[,z]),将x串中的y串替换为z串并返回
15. translate(x,y,z)将x串中所出现的字符在y串中的位置替换为z串中所对应的字符并返回
16. NLS(X)返回字符X对应的类型值
17. CONVERT(STR, DEST_CHARSET[,SOURCE_CHARSET])将STR串的字符集转换为DEST_CHARSET
18. NCHR(X)返回值为X的对应国家字符，等价于CHR(X USING NCHAR_CS)返回字符X对应的NCHAR_CS字符
19. UNSTR(X)返回X串对应的UNICODE字符集
 NUMBER 数字类型函数
1. ABS(X) 返回数字X的绝对值
2. CEIL(X) 返回>=X的最小整数
3. FLOOR(X) 返回<=X的最大整数
4. MOD(X,Y) 返回X/Y 的余数，如果Y为0，则返回X
5. ROUND(X[,Y]) 返回X小数点右边Y位的X值，默认返回最接近的整数，如：ROUND(5.2123322, 2)= 5.21
 时间类型函数
1. SYSDATE 系统时间
2. ADD_MONTHS(DATE,X) 返回日期DATE加上X个月后的值
3. CURRENT_DATE 返回当前会话时区日期， 与SYSDATE 相似。
4. DBTIMEZONE 返回数据库时区
5. LAST_DAY(DATE) 返回日期DATE在月份的最后一天日期，这个函数可以用来确定本月共有多少天
6. MONTHS_BETWEEN(DATE1, DATE2) 返回两日期相差的月数
7. NEW_TIME(DATE, ZONE1, ZONE2) 返回ZONE1时区DATE1对应在ZONE2时区的日期
8. NEXT_DAY(DATE, N) 指定日期后7天星期N的日期
 显示转换
1. TO_NUMBER(STR [,’FORMAT_MODEL’]) 字符串转换到数字类型
2. TO_DATE(STR [,’FORMAT_MODEL’] 字符串转换到日期类型
3. TO_CHAR(DATE [,’FORMAT_MODEL’ [, nlsparams]] ， 使用FM符号可以去掉年月日首位的空格或零， 如 select to_char(sysdate, ‘Fmyyyy-mm-dd’) from dual 输出为: 2012-3-2而非2012-03-02; 也可以通过双引号来格式化输出格式，如select to_char(sysdate, ‘Fmyyyy”年”mm”月”dd”日”’ from dual 输出为 2012年3月2日， 此函数不仅可以转换并输出日期串，也可以格式化输出数字格式串。
4. GREATEST(X,Y,…) 返回参数列表中最大值
5. LEAST(X,Y,…) 返回参数列表中最小值
6. user, uid 返回当前数据库用户和标识， select user, uid from dual
 条件表达式
1. Case 表达式 when 条件 1  then  返回值1
When 条件2 then  返回值2
When 条件n  then 返回值n
Else 返回值
End

2. Decode(条件， 比较值1， 返回值1， 比较值2， 返回值2，…, 返回值)
 统计函数
1. AVG(COL)
2. CORR(X1, X2) 返回表达式X1和X2组成的集合的相关系数
3. COUNT(COL) 统计所有非空字段的总行数
4. MAX(COL), MIN(COL) 返回所有行中最大或最小值
5. SUM(COL) 统计该列所有数字总和
 操作语句
1. MERGE INTO dest_table table_alias USING(table/view/sub_query) ON 条件
WHEN MATCHED THEN UPDATE SET AL.col1 = AL2.col1
WHEN NOT MATCHED THEN INSERT dest_table(col1, col2) VALUES(src_table.col1, src_table.col2)
2. ALTER TABLE table_name SET UNUSED(col list);
ALTER TABLE table_name DROP UNUSED COLUMNS;
DROP TABLE table_name CASCADE;
ALTER TABLE table_name ADD [CONSTRAINT]约束名 约束条件；
ALTER TABLE table_name DROP constraint约束名；
ALTER TABLE table_name PRIMARY KEY CASCADE;
ALTER TABLE table_name DISABLE constraint约束名；
ALTER TABLE table_name ENABLE constraint约束名；
ALTER TABLE table_name MODIFY (colName, colType NOT NULL);
SELECT * FROM ALL_CONSTRAINTS WHERE TABLE_NAME LIKE ‘%TEST%’;
 VIEW视图
1. 创建VIEW
CREATE [OR REPLACE] [FORCE/NOFORCE] VIEW view_name(colName colType, …)
As
子查询语句
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]]
2. 删除VIEW
DROP VIEW view_name;
 序列
CREATE SEQUENCE sequence_name
[increment by n]//步长
[start with n]//起始点
[maxvalue n/nomaxvalue]//递增
[minvalue n/nominvalue]//递减
[cycle/nocycle]//循环
[cache n/nocache];//n 为预先生成序列号的个数，默认为20。

SELECT CURRVAL, NEXTVAL FROM sequence_name;

ALTER SEQUENCE sequence_name
increment by
maxvalue
cycle
cache;
start with 不能修改。

删除序列：
drop SEQUENCE sequence_name;
 索引
被ORACLE 服务器用来加速对表的查询；
通过使用快速路径访问方法快速定位数据；
与表独立存放；
被ORACLE 服务器使用和维护。
一定是 WHERE 条件的才有可能使用索引。

手动创建索引:
CREATE INDEX index_name on table_name (col_name);
考虑创建索引一般需要满足的情况：
1. 该列通常含有大量不相同的值
2. 含有大量空值
3. 该列经常被一起出现在where条件中
4. 表的数据量很大，但每次查询只需要查询表中少量数据
不应该创建索引的情况：
1. 表很小，使用索引效果不明显
2. 列很少被用于查询条件
3. 表上大多查询确实需要得到大量数据，此时使用索引意义不大
4. 表中数据经常发生变动
5. 要被索引的列需要作为条件表达式的一部分，此时使用索引的意义也不大
删除索引：
DROP INDEX index_name;
 用户权限
创建新用户：
CREATE USER user_name IDENTIFIED BY password;
授予权限：
GRANT priv_list TO user/public/role(角色);
grant create session ,create table,create sequence to user_name;
分配配额：
ALTER USER user_name QUOTA nM ON space_name;
移除权限：
REVOKE priv_list/all ON object(包括表、视图、序列等) FROM user;
CREATE ROLE role_name;
GRANT priv_list TO role_name;
GRANT role_name TO user_list/role_list;
 集合操作
UNION 两表的并集，不含重复行
UNION ALL 两表的并集，但含重复行

二、 PL/SQL
 游标
一段私有的SQL 内存区域；分为：隐式游标和显式游标；
定义：CURSOR cursor_name is select_statement;
打开：OPEN cursor_name;
获取：FETCH cursor_name INTO [var1,var2,....] / [record_name];
个数，顺序和类型都必须匹配；
关闭：CLOSE cursor_name;关闭后若想再次利用须重新打开。
游标的属性：
1. sql%rowcount SQL 语句影响到的行数，在显式里是指FETCH 出的行数。
2. sql%found SQL 语句是否访问到数据的属性，在显式里是指FETCH 是否访问
到数据：返回TRUE/FALSE(while loop)
3. sql%notfound SQL 语句是否没访问到数据的属性， 在显式里是指FETCH 是
否没访问到数据：返回TRUE/FALSE(Basic loop)
3. sql%isopen 对显式有效，在隐式中始终为FALSE.
if not cursor_name %isopen then open cursor_name;
例：
declare
cursor empl_dep is
select last_name,department_name from employees,departments
where employees.department_id=departments.department_id;
empl_name employees.last_name %type;
depart_name departments.department_name %type;
begin
open empl_dep;
fetch empl_dep into empl_name,depart_name;
dbms_output.put_line(empl_name ||chr(32)||'work in'||chr(32)||depart_name);
while empl_dep%found loop
fetch empl_dep into empl_name,depart_name;
dbms_output.put_line(empl_name ||chr(32)||'work in'||chr(32)||depart_name);
end loop;
if empl_dep%isopen then
dbms_output.put_line(empl_dep%rowcount);
end if;
close empl_dep;
end;
 异常处理
异常：在执行过程中出现的错误。
错误触发：语句执行错误（规则）。
显式触发：人为认定的异常，人为规定出现异常的数据范围（逻辑）。
预定义异常：
有编号，有名称。使用异常名称扑捉；
1. NO_DATA_FOUND
2. TOO_MANY_ROWS
3. INVALID_CURSOR
4. ZERO_DIVIDE
5. DUP_VAL_ON_INDEX
非预定义异常：
有编号，没名称。需要先定义名称再扑捉；
(1)在DECLARE 部分定义一个异常的名称； exc_name EXCEPTION;
(2)把名称和异常编号连接起来； PARGMA EXCEPTION_INIT(exc_name,-exc_number);
应用错误（不会被捕获）：
RAISE_APPLICATION_ERROR
RAISE_APPLICATION_ERROR(自定义错误编号，自定义信息);
不会被 EXCEPTION 捕捉到，而是直接回显到界面上给用户看到。
在EXCEPTION ...WHEN ...THEN 之前或之中都可以使用。
 存储过程
创建：
create [or replace] procedure pro_name
[(parameter1 [mode] datatype,parameter2 [mode] datatype, ...)]
is/as
.....//声明部分
begin
....//函数主题
exception
....//异常处理
end;
MODE： 类型分为输入和输出
1)in 传入(形参)默认，可以有默认值
create or replace procedure empl_sal_pro(empl_id employees.employee_id %type)
is
begin
update employees set salary = 170000 where employee_id = empl_id;
end empl_sal_pro;
exec empl_sal_pro(empl_id=> 100)
2)out(实参)先将初始值传入经过处理后在传出,必须定义名称；
create or replace procedure empl_info(empl_id employees.employee_id%type, empl_name out employees.last_name%type, empl_sal out employees.salary%type) is
begin
select last_name,salary into empl_name,empl_sal from employees
where employee_id=empl_id;
end empl_info;

执行exec empl_info(100,:name,:sal)

 包(package)
一组相关类型的变量，常量，游标，存储过程，函数的集合。
1)组成：
包头：声明部分,只声明PUBLIC PROCEDURE/VARIABLE；
包体：程序实体，包含PRIVATE PROCEDURE/VARIABLE 、PUBLIC PROCEDURE/VARIABLE 和 LOCAL
VARIABLE；
包头没有包体是可以独立存在的。