Oracle学习笔记

network\admin\目录下具有listener.ora(服务器监听器配置文件,默认端口1521)和tnsnames.ora(客户机连接配置文件)，可以通过Net Configuration Assistant工具配置。
默认用户模式有SYS，SYSTEM，SCOTT(口令默认为tiger)。
连接：
sqlplus sys/888888 @orcl(SID) as sysdba(或者sysoper)，切换使用connect scott/tiger @orcl。
创建角色：
create role newRole；
创建用户：
create user chenqian identified by 888888(default tablespace ? temporary tablespace ?没有指定则为system，temp表空间)
修改口令：
alter user chenqian identified by 888888
删除用户，包括模式对象：
drop user chenqian cascade;
创建表空间：
create tablespace ? datafile '?(,?)' size ?k/m autoextend off/on;

字符数据类型：char(1-2000byte),varchar2(1-4000byte，oracle建议因其varchar可能它用),long(2G,1个表只允许1列，不能建唯一和主键约束，不能建索引，过程不能接受该类型参数)。

数值数据类型：number(p,s)(p默认保留总长度为38位，范围1至38个十进制位；s用于确定出现四舍五入的位置，默认保留小数点后0位，范围-84至127)。

日期时间数据类型：date(7个字节，分别为世纪、年、月、日、小时、分、秒，sysdate函数是返回当前日期和时间)，timestamp(秒值精确到小数点后6位，还包括时区信息，systimestamp函数是返回当前日期、时间和时区)。

二进制数据类型：raw(1-2000byte,无默认大小，需指定)，long raw(2G,类似long)。

大对象(LOB)数据类型：4G,可以是外部或内部的，修改数据可以使用DML(数据操纵语言)也可以使用PL/SQL中提供的DBMS_LOB程序包，一个表中可以有多列LOB，包括CLOB(字符)、BLOB(二进制)、BFILE【二进制文件，使用bfilename函数(create directory dir as 'd:\';grant read on directory dir to SCOTT;bfilename('dir','bfileTest.xml');)】。

伪列实际并没有存储在表中，只可以查询；rowid伪列返回该行地址，可使用其定位；rownum伪列返回一个数值代表行的次序(第一行为1，取10条记录where rownum<11，不能用rownum>?)。

查看表结构：desc myTable;

scott解锁：alter user scott account unlock;

DDL(数据定义语言)=>
创建表：create table [schema.]myTable (myColumn varchar2(5),……);

修改表：alter table myTable modify/add (myColumn varchar(6),……);alter table myTable drop column myColumn;

删除表：truncate table myTable(只删除数据，不删除结构；与delete不同的是其快速删除记录并释放空间，不使用事务处理，因此无法回滚);drop myTable(删除表及其数据)。

DML(数据操纵语言)=>
查询无重复的记录：select distinct myColumn from myTable；(distinct接空格或()都可以,而且只能针对一个字段)
利用现有的表创建新表：create table myTable as select ……；(很少用，几乎无实际意义)
插入来自其他表的记录：insert into mytable select ……；(mytable后可接(columns))
更新表：update myTable set myColumn=? where ……；
删除表：delete myTable where ……；

TCL(事务控制语言)=>
……;
savepoint mark1;
……；
savepoint mark2;
rollback to savepoint mark1;(若无保存点，可直接rollback)
commit;

DCL(数据控制语言)=>
授权：
grant connect to chenqian;
grant resource to chenqian;(允许使用数据库中的空间)
grant dba to chenqian;
grant sysope to chenqian;
grant sysdba to chenqian;
grant create sequence(user,tablespace) to chenqian;
grant insert,delete,update,select on scott.emp to chenqian；
撤权：
revoke insert,delete,update,select on scott.emp to chenqian;(CRUD,增删改查);
查看当前用户/角色权限：
select * from session_privs;
select * from session_roles;
-- 确定角色的权限
select * from role_tab_privs ;  数据字典视图包含了授予角色的对象权限
select * from role_role_privs ; 包含了授予另一角色的角色
select * from role_sys_privs ;  包含了授予角色的系统权限



sql操作符=>
比较操作：
in:
……where myColumn in (1,2,……)；
like：
……where myColumn like '%s';(%表示0-n个字符匹配，_表示一个字符匹配)；

集合操作：
union(联合)、union all、intersect(交集，只返回两个查询都有的记录)、minus(减集，第一个查询结果除去第二个查询结果)，要求连接的各个查询具有相同的列数，且类型相同，不应含有long类型，列标题来自第一个select语句。
union
select column1 from table1 union select column2 from table2;【union返回不重复的记录,union all则包括重复的记录，还可以使用order by排序，但必须指定列索引(如order by 2)，而不是指定列名column2】


连接操作符(||)用于将多个字符串合并成一个字符串。

sql函数=>
单行函数
1、日期函数
to_date('2008-3-27",'yyyy-mm-dd')或者to_date('2008-3-27 08:08:08",'yyyy-mm-dd hh:mi:ss');
add_months(date0,2);
months_between(date0,date1);返回两个日期之间的月数
last_day(date0);返回当月最后一天的日期值
round(date0);默认为最靠近的那一天，也可以round(date0,'year/month/day')，day(周)为最靠近的星期日
next_day(date0,'星期一')，后面的第一个星期一,如"select next_day(sysdate,'星期一') from dual"==>"2008-8-25 14:14:29"
trunc(date0);默认截断为天，也可指定trunc(date0,'year/month/day'),如"select trunc(sysdate,'year') from dual"==>"2008-1-1"
extract(year/month/day/hour/minute/second from date0);提取日期时间类型中的特定部分，如"select extract(year from sysdate) from dual"==>"2008"

2、字符函数
initcap(char0);首字母大写
lower(char0); 转为小写
upper(char0);转为大写
ltrim/rtrim('abcd','ab');左/右/剪裁,如"ltrim('abcd','a')"==>"bcd"以及"rtrim('abcd','d')"==>"abc"
translate('chenqian','chen','1234');按字符翻译，返回"1234qia4"
replace('jack','j','bl');字符串替换，返回"black"
instr('chenqian','q');查找字符串位置，从1开始，所以返回5
substr('chenqian',2,3);取子字符串，2索引(从1开始)，3为长度，所以返回"hen"
concat('chenqian',' hello');连接字符串，返回"chenqian hello"
chr(67);根据ascii码返回对应的字符，这里为'C'，对应的函数就是"ascii('C')"==>67
lpad/rpad('chenqian',10,'%');左/右填充字符串,10为总长度，'%'为需要填充的字符串，所以"lpad('chenqian',10,'%')"==>"%%chenqian"
trim(' chenqian ');默认裁剪空格，也可以trim(leading/trailing/不写 'n' from 'chenqian')，这里'leading/trailing/不写'表示"开头/结尾/开头和结尾"，注意'n'只能是字符或数字不能是字符串
length('chenqian');
decode('a','a','true','false'),返回"true";decode('a','b','true','false'),返回"false";
decode(expr,'boy','男孩','girl','女孩'),expr为表达式，如果是'boy'则返回"男孩"，如果是'girl'则返回"女孩";

3、数字函数
abs(n);取绝对值
cell(n);向上取整
floor(n);向下取整
sign(n);取符号，如"select sign(-32) from dual"==>-1
power(m,n);m的n次幂
mod(m,n);m模n，取余数
round(m,n);n为小数点位数，四舍五入
trunc(m,n);n为小数点位数，截断
sqrt(n);平方根

4、转换函数
to_char(d|n [,fmt]);d为日期，n为数字，fmt指定日期或数字的格式，如果省略fmt，则默认转换为varchar2类型，
如select to_char(sysdate,'YYYY'' 年 ''fmMM'' 月 ''fmDD'' 日 '' HH24:MI:SS');填充模式"fm"格式掩码来避免空格填充和数字零填充。
to_date(char|varchar2 [,fmt])如to_date('2008-3-27",'yyyy-mm-dd')或者to_date('2008-3-27 08:08:08",'yyyy-mm-dd hh:mi:ss')。
to_number('100');返回number数据类型100，通常不需要，因为oracle可以对数字字符串进行隐式转换。

5、其他函数
nvl(expr0,expr1);结果类似于expr0==null?expr1:expr0,类似nvl2(expr0,expr0,expr1)
nvl2(expr0,expr1,expr2);结果类似于expr0==null?expr2:expr1。
nullif(expr0,expr1);等价于case when expr0=expr1 then null else expr0 end;。

分组函数(聚合函数)
avg(),min(),max(),sum(),count(*),count(myColumn)统计指定列非空值的个数,count(distinct myColumn)并且除去重复的值；
group by myColumn或者expr0语句配合分组函数使用，为每组返回一个结果；
having位于group by子句后面，用来指定group by子句的检索条件。

分析函数
与聚合函数不同的是它们为每组记录返回多个行。除了最后的order by子句，分析函数是查询中执行的最后一组操作。分析函数只能出现在select列表或order by子句中。例如select ename,job,deptno,sal,row_number()|rank()|dense_rank() over(partition by deptno order by sal desc) as sal_rank from scott.emp;
partition by deptno指定按deptno字段分隔开，再order by sal desc。
1、row_number为有序组中的每一行返回一个唯一的排序值，序号有order by子句指定，从1开始；
2、rank排位，具有相等值的行排位相同，序数随后跳跃相应的数值，如果前面两个为1，则第三个为3；
3、dense_rank排位，具有相等值的行排位相同，并且排位是连续的，如果前面两个为1，则第三个为2。

锁和表分区=>
行级锁
  行级锁是一种排他锁，防止其他事务修改此行，在使用insert、update、delete和select……for update等语句时，oracle会自动加上行级锁。只有在回滚或提交该事务后，锁定才会释放。
  select……for update [of cloumn_list] [wait n|nowait];
  of用于指定即将更新的列，即锁定行上的特定列，
  wait用于制定等待的秒数，防止无限期等待。


表级锁
  表级锁将保护表数据，限制对整个表的增删改，可以使用lock table语句显示地锁定表。
  lock table <table_name> in <lock_mode> mode [nowait];
  lock_mode是锁定的模式，包括：
      RS(行共享,row share)允许其他用户同时更新其他行，允许其他用户同时加共享锁，不允许有独占（排他性质）的锁；
      RX(行排他,row exclusive)允许其他用户同时更新其他行，只允许其他用户同时加行共享锁或者行排他锁；
      S(共享,share)不允许其他用户同时更新任何行，只允许其他用户同时加共享锁或者行共享锁；
      SRX(共享行排他,share row exclusive)允许其他用户同时更新其他行，只允许其他用户同时加行共享锁；
      X(排他,exclusive)其他用户禁止更新任何行，禁止其他用户同时加任何锁。
  oracle将自动检测死锁，并通过终止两个事务处理之一来解决问题。

总结：名称中带ROW的锁的共性是允许其他用户同时更新不同行，
根据是否允许其他用户同时加锁的种类不同而分不同类型           

如果不希望别的session lock/insert/update/delete表中任意一行，只允许查询，
可以用lock table table_name in exclusive mode。(X)这个锁定模式级别最高，并发度最小。

如果允许别的session查询或用select for update锁定记录，不允许insert/update/delete，
可以用lock table table_name in share row exclusive mode。(SRX)

如果允许别的session查询或select for update以及lock table table_name in share mode，
只是不允许insert/update/delete，可以用lock table table_name in share mode。
(share mode和share row exclusive mode的区别在于一个是非抢占式的而另一个是抢占式的。
进入share row exclusive mode后其他session不能阻止你insert/update/delete，
而进入share mode后其他session也同样可以进入share mode，进而阻止你对表的修改。(S)

还有两种锁定模式，row share(RS)和row exclusive(RX)。他们允许的并发操作更多，
一般直接用DML语句自动获得，而不用lock语句。




表分区
  表分区允许用户把一个表中的所有行分为几个部分，并将这些部分存储在不同的位置。被分区的表称为分区表，分成的每一部分称为分区。
  优点：1、改善表的查询性能(只访问分区)；2、表更容易管理(按分区加载和删除数据)；3、便于备份和恢复(可独立备份和恢复分区)；4、提高数据安全性(分区可分布在多个磁盘)。
  注意：要分区的表不能有long和long row数据类型的列。

  create table myTable partition by……；

  范围分区(column_name只能是数值或日期类型的列)：
           partition by range(column_name){
partition part1 values than (number1|date1) [tablespace ts1],
partition part2 values than (number2|date2) [tablespace ts2],
……
partition partN values than (maxvalue) [tablespace ts2]
     }
  散列分区(number_of_partitions分区数目,tablespace_list分区使用的表空间，如果tablespace_list<number_of_partitions则以循环方式分配)：
      patition by hash(column_name) partitions number_of_partitions [store in (tablespace_list)];
      或者
      partition by hash(column_name){
partition part1 [tablespace ts1],
partition part2 [tablespace ts2],
……
partition partN [tablespace ts2]
      }
  复合分区：
      partition by range(column_name1)
      subpartition by hash(column_name2){
partition part1 values than (number1|todate())(subpartiton part1_1,subpartiton part1_2),
partition part2 values than (number2|todate())(subpartiton part2_1,subpartiton part2_2),
……
partition partN values than (maxvalue)(subpartiton partN_1,subpartiton partN_2)
      }
  列表分区(value_list是该分区键值列表，即判断key：column_name的value是否在value_list中，default关键字允许存储前面的分区不能存储的记录)：
      partition by list(column_name){
partition part1 values (values_list1),
partition part2 values (values_list2),
……
partition partN values (default)
      }

  根据分区操作分区表：
select|delete * from table1 partition (part1);
  添加分区：
alter table table1 add partition part4 values less than (number4);
  删除分区：
alter table table1 drop partition part4；
  截断分区(保留该分区结构)：
alter table table1 truncate partition part4；
  合并分区：
alter table table1 merge partitions part4，part5 into partition_name;
  拆分分区：
alter table table1 split partition part4 at (value拆分值) into (partition part4_1,partition part4_2);
  重命名分区：
alter table table1 rename partition part4 to part4_new;



数据库对象=>
同义词：
1、私有同义词：只能被当前模式的用户访问，synonym_name不可与当前模式的对象名称相同。用户在自身模式下创建私有同义词必须拥有create synonym系统权限，在其他用户模式下创建则必须拥有create any synonym系统权限。
create [or replace] synonym [schema.]synonym_name for [schema.]object_name;
2、公有同义词：可被所有数据库用户访问，用户创建公有同义词必须拥有create public synonym系统权限。
create [or replace] public synonym [schema.]synonym_name for [schema.]object_name;
删除同义词：drop [public] synonym [schema.]synonym_name;

序列：
序列是用来生成唯一、连续的【整数】的数据库对象。序列通常用来自动生成主键或唯一键的值。序列可按升序、降序排列。
create sequence [schema.]sequence_name [start with integer] [increment by integer] [maxvalue integer|nomaxvalue]|[minvalue integer|nominvalue] [cycle|nocycle] [cache integer|nocache];
cycle--指定序列到达maxvalue/minvalue后，将继续从头开始生成值(循环);
cache--使用cache选项可以预先分配一组序列号，并将其保留在内存中，这样可以更快的访问序列号。
1、访问序列：用nextval和currval伪列，创建序列后第一次使用nextval时，将返回该序列的初始值，以后按increment值递增，currval返回序列的当前值，如 select sequence_name.currval from dual;
2、更改序列：注意不能更改序列的start with参数，
alter sequence [schema.]sequence_name [increment by integer] [maxvalue integer|nomaxvalue]|[minvalue integer|nominvalue] [cycle|nocycle] [cache integer|nocache];
3、删除序列：drop sequence [schema.]sequence_name;

视图：
create [or replace] [force|noforce] view view_name [(alias[,alias]...)] as select_statement [with check option [constraint]] [with read only];
force--无论基表是否存在，都将创建视图，如果表不存在oracle将产生警告，创建表后oracle自动重新编译该视图，也可手动alter view view_name compile；
alias--由视图的查询所选择的表达式或列的别名；
with check option--此选项指定只能插入或更新视图可以访问的行。
1、DML语句和联接视图：
在视图中使用DML语句只能修改一个底层的基表。
联接视图是在from子句中制定多个表或视图的视图，oracle提供了视图上的"instead of 触发器"，通过该触发器，可以通过视图同时对多个表执行DML操作。
create view myViwe as select [columns] from table1 t1，table2 t2 where t1.column1=t2.column1(+);此处使用"+"符号是oracle特有的外联接语法，
等价于create view myViwe as select [columns] from table1 t1 left outer join table2 t2 where t1.column1=t2.column1，t1为键保留表，t2则不是。

2、键保留表：
在联接视图中，如果视图包含了一个表的主键，并且也是这个视图的主键，则这个键被保留，这个表被称为键保留表，oracle可以通过此视图向表中插入行。包含外部联接的视图通常不包含键保留表，除非外部联接生成非空的值。oracle可以确定哪些表是键保留的，只有键保留表才能达到通过使用DML语句对视图操作等同于操作表的目的。
通过数据字典视图user_updatetable_columns,可以确定联接视图中可以更新的列，例如：select * from user_updatetable_columns where TABLE_NAME='view1';

3、删除视图：drop view view_name;

索引(索引值排序，rowid定位)：
索引只是一种快速访问数据的途径，它只影响执行的速度，索引是独立的结构，因此需要存储空间,也因此可独立于表空间。
创建：create [unique|bitmap] index indexName on tableName (column_list) [tablespace tablespaceName];unique指定为唯一索引，bitmap指定为位图索引。
位图索引，适用于低基数列，也就是不同值的数目远远小于表的行数,它不使用(rowid+索引列)的模式，而采用(二进制位图+索引列)的模式，位图中的每个位对应一个可能的rowid，如果设置了该位，则意味着拥有此rowid的行包含该键值，类似于先缩小搜索范围；不应用在频繁发生insert、update、delete操作的表上。
重建：alter index index_name rebuild;
删除：drop index index_name;
反向索引：create index index_name on table_name (column_list) reverse；使含有序数的列值更均匀
取消反向：alter index index_name rebuild noreverse;
索引组织表：该表的数据存储在与其关联的索引中，而不是将行的rowid作为索引条目的第二个元素，语法为：create table table_name(…primary key…)organization index;注意必须要有主键，且不允许使用unique和分区。
基于函数的索引：表达式不能包含任何聚合函数，不能在也不能包含LOB列、REF列的对象类型上创建基于函数的索引，create index index_name on table_name (expression)；
索引中的分区：与表类似，oracle也支持对索引进行分区。
局部分区索引--create index index_name on order_mast (orderno) local;如果查询名为"user_segments"的字典视图(segment_name="index_name")，可以发现该索引是作为单独的端为所有分区创建的。
全局分区索引--在分区表或非分区表上创建的索引，全局索引的键可以引用存储在多个分区中的行，create index index_name on order_mast (orderno) global partition by range(orderno)(……);
全局非分区索引--在分区表上创建全局索引；create [unique|bitmap] index index_name on table_name (column_list) [tablespace tablespace_name];。
获取索引的信息：数据字典视图user_indexes，user_ind_partitions，user_ind_columns。


PL/SQL(在PLSQL Developer中用"/"结束)=>
PL/SQL块中不允许直接使用create、drop、alter等DDL语言，但可以通过动态SQL来执行它们。
语法：
[declare
  declarations]
begin
  excutable statements
[exception
  exception handlers]
end;
declarations--指声明部分，包括变量、游标、自定义异常和局部子程序；
excutable statments--指可执行语句，可以嵌套子块；
exception handlers--指处理错误的代码块，也可以嵌套子块。
复合符号的含义：
":="      赋值操作符
"||"      连接操作符
"--"      单行注释
"/* */"   多行注释
"<< >>"   标签分隔符
".."      范围操作符
"**"      求幂操作符
声明变量：
variable_name data_type[(size)] [:=init_value]；
变量赋值：
variable_name:=variable_value;
select column_name…… into variable_name…… from ……；
常量声明：
variable_name constant data_type:=init_value；
初始化变量和常量时，可以使用保留字default替换赋值操作符“:=”，一条语句只能声明一个变量。

PL/SQL提供的4种内置数据类型：
1、标量数据类型：
标量数据类型包含单个值，没有内部组件，包括数字、字符、布尔值、和日期时间值。
数字数据类型：
binary_integer--用于存储带符号的整数，大小范围介于-2的31次方减1到2的31次方减1；
number--用于存储整数、定点数和浮点数，大小范围为1E-130至10E125；
pls_integer--用于存储带符号的整数，大小范围介于-2的31次方到2的31次方之间，它以机器算术运算为基础的执行运算的速度更快，上两种类型运算以库算术运算为基础，此外它需要的存储空间也比number类型更小；

字符数据类型：
char--char [(maximum_size[char|byte])]用于存储固定长度的字符数据，最大长度32767个字节，如果未指定长度，默认为1个字节，如果char(n)无法容纳n个多字节字符，则使用表示法char(n char)解决，char能插入long，但不能反向。
raw--raw(maximum_size)用于存储固定长度的二进制数据或字节串，最大长度32767个字节，它与char不同在于其不在字符集之间转换，raw能插入long raw，但不能反向。
long和long raw--与sql的long、long raw不同，PL/SQL的long类型是可变长度字符串，PL/SQL的long raw类型不在字符集之间转换，最大长度均为32760个字节，long raw列的最大长度为2GB。
varchar2--varchar(maximum_size),最大长度为32767个字节。

日期时间类型：
date--NLS_DATE_FORMAT用于设置默认日期格式。
timestamp--timestamp [(precision)],precision为精度，代表秒字段小数部分的位数，范围0-9，默认为6，NLS_TIMESTAMP_FORMAT用于设置默认的timestamp的格式。

布尔数据类型：
boolean--用于存储逻辑值true、false或null，不能将boolean数据插入到数据库列中，不能将列值提取或选择到boolean变量中，只允许进行逻辑操作。

2、LOB类型：
bfile、blob、clob、nclob
在表中插入图表代码:
declare
l_bfile bfile;
l_blob blob;
begin
insert into my_diagrams (diagram) values (EMPTY_BLOB()) return diagram into l_blob;
l_blob:=bfilename('images','\myimage.jpg');
DBMS_LOB.OPEN(l_bfile,DBMS_LOB.FILE_READONLY);
DBMS_LOB.LOADFROMFILE(l_blob,l_bfile,DBMS_LOB.GETLENGTH(l_bfile));
DBMS_LOB.CLOSE(l_bfile)；
commit；
end；
如何读取CLOB数据类型列中的值代码(注意先执行set serveroutput on)：
declare
clob_var clob;
amount integer;
offset integer;
output_var varchar2(100);
begin
select chapter_text into clob_var from my_book_text where chapter_id=5;
amount:=24;
offset:=1;
DBMS_LOB.READ(clob_var,amount,offset,output_var);
DBMS_OUTPUT.PUT_LINE(output_var);
end;
3、组合数据类型：
4、引用数据类型：
5、属性类型：
属性用于引用变量或数据库列的数据类型，以及表示表中一行的记录类型。
%TYPE--引用变量或数据库列的数据类型来声明变量，语法为icode table_name.column_name%TYPE;
%ROWTYPE--提供表示表中的一行的记录类型，记录类型可以存储从表中选择或由游标提取的整行数据，语法为emp_rec emp%ROWTYPE。

逻辑比较：
其它与java类似，但oracle使用“=”表示逻辑等于，不是“==”。

控制结构：
1、条件控制
if语句：
if condition1 then
sequence_of_statements1
elsif condition2 then
sequence_of_statements2
else
sequence_of_statements3
end if;
case语句：
case selector
when expression1 then sequence_of_statements1;
when expression2 then sequence_of_statements2;
……
when expressionN then sequence_of_statementsN;
[else sequence_of_statementsN+1;]
end case;
2、循环控制
loop语句(sequence_of_statements中必须使用exit或exit when语句)：
loop
sequence_of_statements;
end loop;

while语句：
while condition loop
sequence_of_statements
end loop;

for语句：
for counter in [reverse] value1..value2 loop
sequence_of_statements;
end loop;
3、顺序控制
goto语句：无条件地将控制权转到标签指定的语句。标签使用"<< >>"括起来的标识符，在PL/SQL块内必须具有唯一的名称。goto语句不能跳转到if、case、loop语句或子块中。
null语句：什么也不做，只是将控制权转到下一语句，用在if或其它语句语法要求至少需要一条可执行语句，但又不需要执行操作的情况下。

动态SQL(适合DDL)：
所谓动态SQL是指在PL/SQL块编译时SQL语句是不确定的，编译程序对动态语句部分不进行处理，只是在程序运行时动态地创建语句，对语句进行语法分析并执行该语句，oracle中的动态SQL可以通过本地动态SQL命令来执行，也可以通过DBMS_SQL程序包来执行。
execute immediate dynamic_sql_string [into define_variable_list] [using bind_argument_list];
into子句接受select语句选择的记录值，using子句用于绑定输入参数变量。
*******注意excute immediate语句只能用于处理返回单行或没有返回的sql语句，要处理返回多行的动态sql请使用REF游标的open…for语句。

错误处理：
1、预定义异常
它在STANDARD程序包中声明，每个预定义异常都有一个错误号，但是必须通过名字来捕获异常。
常用的有：
NO_DATA_FOUND(在表中不存在请求的行时出现)
TOO_MANY_ROWS(在select into语句后返回多行时出项)
ZERO_DIVIDE(以零做除数时出现)
语法为：
begin
sequence_of_statements;
exception
when <exception_name> then
sequence_of_statements;
when others then
sequence_of_statements;
end;
2、用户定义异常
声明异常：
declare
myexception_name exception；
引发异常：
raise myexception_name;

3、引发应用程序异常
RAISE_APPLICATION_ERROR(error_number,error_message);
error_number表示用户为异常制定的编号，该编号介于-20000到-20999之间的负整数，
error_message表示用户为异常指定的消息文本，长度可达2048字节。



游标管理=>
游标是构建PL/SQL中，用来查询数据，获取记录集合的指针。它可以让开发者一次访问结果集中的一行。
静态游标：
静态游标是在编译时知道其SELECT语句的游标。
1、隐式游标：
  PL/SQL为所有SQL数据操纵语句(包括返回一行的select)隐式声明游标，称为隐式游标的原因是用户不能直接命名和控制此类游标。
属性如下：
%FOUND    只有DML语句影响一行或多行时，%FOUND才返回true。
%NOTFOUND
%ROWCOUNT 返回DML语句影响的行数。
%ISOPEN   返回游标是否已打开的值。在执行SQL语句之后，oracle自动关闭SQL游标，所以它始终为false。
使用select into语句的结果必须有且只能有一行，若没有返回则NO_DATA_FOUND异常，若多行返回则TOO_MANY_ROWS异常。
2、显示游标：
  显示游标是由用户显示声明的游标，只有%isopen属性不同(true|false)，操作的4个步骤是：
声明
cursor cursor_name [parameter_list] [return return_rowtype] is select_statement;
打开(open)
open cursor_name[(parameters)];
从中获取记录(fetch)
fetch cursor_name into variables;
fetch命令常写在循环内，由于从结果集一次检索一行。结果集中的每一行的值存储一个变量中。在每次提取之后，结果集的指针就向前移动一行。
关闭(close)
close cursor_name；
  例如：
set serveroutput on
declare
emp_row scott.emp%rowtype;
cursor emp_cur is select * from scott.emp;
begin
open emp_cur;
loop
fetch emp_cur into emp_row;
exit when emp_cur%notfound;
dbms_output_line(emp_row.empno,emp_row.wname);
end loop;
close emp_cur;
end;
使用显示游标删除或更新
如果处理过程中需要删除或更新行，在定义游标时必须使用select...for update语句，而执行delete和update时使用where current of子句指定游标的当前行。
声明更新游标的语法：
cursor cursor_name is select_statement for update [of columns];
执行更新行的语法：
update table_name set column_name = column_value where current of cursor_name;
带参数的显示游标：
set serveroutput on
declare
dept_code emp.deptno%type;
emp_code emp.empno%type;
emp_name emp.empname%type;
cursor emp_cur(deptparam number) is select empno,ename from scott.emp where deptno=deptparam;
begin
dept_code := &部门编号;
open emp_cur(dept_code);
loop
fetch emp_cur into emp_code,emp_name;
exit when emp_cur%notfound;
dbms_output_line(emp_code||' '||emp_name);
end loop;
close emp_cur;
end;
循环游标
可以使用循环游标简化显示游标的处理代码。循环游标自动打开、提取、关闭游标。循环游标自动创建%rowtype类型的变量并将此变量用作记录索引。不能在同一个表内用游标更新。
for emp_row in emp_cur
loop
dbms_output_line(emp_row.empno);
end loop;
     record_index是PL/SQL声明的记录变量，它的属性声明为%rowtype类型。

REF游标：
运行的时候动态决定执行何种查询可以使用REF游标和游标变量。2个步骤：声明REF CURSOR类型和声明REF CURSOR类型的游标变量。
type ref_cursor_name is ref cursor [return record_type];
return语句用于指定游标提取结果集的返回类型。包括return语句表示是强类型REF游标，不包括则为弱类型REF游标(该方法能够用于获取任何结果集,但不能使用for循环)。
打开游标变量
open cursor_name for select_statement;
注意excute immediate语句只能用于处理返回单行或没有返回的sql语句，要处理返回多行的动态sql请使用REF游标的open…for语句。
打开执行返回多行动态sql的ref游标语法：
open cursor_name for dynamic_select_string [using bind_argument_list];
实例代码：
set serveroutput on
variable maxsal number
execute :maxsal :=2500
decalre
r_emp emp%rowtype;
type c_type is ref cursor;(无return则为弱类型，后面不能用for循环)
cur c_type;
p_salary number;
begin
p_salary := :maxsal;
open cur for 'select * from emp where sal>:1 order by sal desc' using p_salary;(指定:1绑定到p_salary)
dbms_output.put_line('薪水大于'||p_salary||'的员工有：');
loop fetch cur into r_emp;
exit when cur%notfound;
dbms_output.put_line('编号：'||r_emp.empno||'姓名：'||r_emp.ename||'薪水：'||r_emp.lsal);
end loop;
close cur;
end;

使用游标变量的限制：
1、for update子句不能与游标变量一起使用
2、不允许在程序包中声明游标变量
3、另一台服务器上的远程子过程不能接受游标变量参数的值
4、不能将null值赋给游标变量
5、游标变量不能使用比较运算符
6、数据库的列不能存储游标变量


子程序包和程序包=>
子程序包
子程序包是已命名的PL/SQL块，他们存储在数据库中，可以为他们指定参数，可以从任何数据库客户端和应用程序中调用它们。命名的PL/SQL程序包包括存储过程和函数，程序包是存储过程和函数的集合。子程序包有两种类型，即过程和函数。通常，使用过程执行操作，使用函数执行操作并返回值。
1、过程：
过程是执行某些操作的子程序，它是执行特定任务的模块。

创建过程语法：
create [or replace] procedure procdure_name [(parameter_list)] [is|as]
[local_declaraions]
begin
excutable_statements
[exception
exception_handlers]
end [procedure_name]
其中parameter_list的个体parameter不能有类型大小，例如find_emp(emp_name varchar2(20))中的varchar2不能有大小20的定义。

执行过程：
execute procedure_name (parameter_list);
如果parameter_list中带有out(包括in out)参数的值，就必须使用变量，例如：
declare
value1 number;
begin
test(value1);
dbms_output.put_line(to_char(value1));
end;

过程参数模式：
参数传递的模式有3种：in、out和in out；
parameter_name [in | out | in out] datatype [{:= | default} expression]
参数in模式是默认模式，如果未制定参数的模式，则认为该参数为in参数。
在返回到调用环境之前，必须先给out或in out参数赋值。
可以在参数列表中为in参数赋予一个默认值，不能为out、in out参数赋予默认值。

将过程的执行权限授予其他用户：
grant execute on procedure_name to (user_name | public);
删除过程：drop procedure procedure_name;

2、函数
函数的主要特征是它必须返回一个值。
定义函数语法：
create [or replace] function function_name [parameter_list]
return datatype {is|as} [local_declarations]
begin
executable_statements;
[exception
exception_handlers;]
end;
定义函数的限制：
函数只能带in参数，而不能带有in out或out参数；
形式参数必须只使用数据库类型，不得使用PL/SQL类型；
函数的返回类型也必须是数据库类型。

   与过程不同，函数不能单独执行，只能通过SQL语句或PL/SQL程序块来调用，调用的语法为：
(SQL语句)select function_name from dual;
(PL/SQL,variable的类型要与函数返回类型一致)variable_name := function_name(parameter_list);

函数的授权：
grant execute on function_name to (user_name | public);

删除函数：
drop function function_name;

使用函数的限制：
从select语句调用的任何函数均不能修改数据库表；
当远程执行或并行执行时，函数不得读取或写入程序包中变量的值；
从select、values或set子句调用的函数可以写入程序包中变量，其他子句中的函数不能写入程序包变量；
如果函数调用执行update的存储过程，则该函数不能在SQL语句内使用。

自主事务处理：
自主事务处理是由另一个事务处理(主事务处理)启动的独立事务处理。自主事务处理可以暂停主事务处理并处理过程内的SQL操作，提交或回退操作，然后恢复主事务处理。当一个过程调用另一个过程时，
在其中任一过程中进行的任何更改在这两个过程中都是可见的，任何提交或回退语句均将影响这两个过程中的事务处理。要定义自主事务处理，可以使用编译指示pragma autonomous_transaction包括在声明部分中，
该编译指示通知PL/SQL编译器将过程、函数或PL/SQL块标记为自主的。
自主事务的特征：
1、自主事务处理结果的变化不依赖于主事务处理的状态或最终配置；
2、自主事务处理提交或回退时，不影响主事务处理的结果；
3、自主事务处理一旦提交，该自主事务处理结果的变化对于其他事务处理就是可见的，这意味着用户可以访问已更新的信息，而无需等待主事务处理提交；
4、自主事务处理可以启动其他自主事务处理。

程序包
程序包是一种数据库对象，它是对相关PL/SQL类型、子程序、游标、异常、变量和常量的封装。它包含程序包规范和程序包主体两部分内容。在程序包规范中，可以声明类型、变量、常量、异常、游标和子程序。程序包主体用于实现在程序包规范中定义的游标、子程序。
创建程序包规范的语法：
create [or replace] package package_name
is | as
[public type and item declarations]
[subprogram specifications]
end [package_name];
其中public type and item declarations--声明类型、变量、异常和游标等；
    subprogram specifications--声明PL/SQL子程序包。
在程序包中声明的项也可以在程序包之外使用，称为“公共对象”。
创建程序包规范的实例：
create or replace package pack_me is
procedure order_proc (orno varchar2);
function order_fun(ornos varchar2) return varchar2;
end pack_me;
注意函数必须跟return结合使用。

程序包主体的初始化部分是可选的，它可用于初始化程序包中的变量。程序包的初始化部分即不能调用程序包，也不能将参数传递给程序包，而且，程序包的初始化部分仅运行一次。
创建程序包主体的语法：
create [or replace] package body package_name
is | as
[public type and item declarations]
[subprogram bodies]
[begin
initialization_statements]
end [package_name];
其中subprogram bodies--定义公共和私有PL/SQL子程序；
    begin initialization_statements--初始化语句。

如果创建包规范或包主体时出现编译错误，可以输入show errors命令查看详细的错误报告。
要引用程序包规范中声明类型、对象和子程序，可以使用：
package_name.type_name;
package_name.object_name;
package_name.subprogram_name。

私有项和公共项：
公共元素在规范中定义，私有元素在程序包主体中定义。私有元素不能在程序包之外引用。程序包中的任何其他元素均可以引用和使用私有元素。引用同一程序包中的元素不需要使用点分表示法。

程序包中的游标：
游标的定义分为游标规范和游标主体两部分，在更改游标主体时，无需改变游标规范，在包规范中声明游标规范时必须通过return子句指定游标的返回类型。返回类型可以使用%rowtype或自定义的记录类型。例如：
create or replace package cur_pack is
cursor ord_cur(vcode varchar2) return order_master%rowtype;//is... 主体
procedure ord_pro(vcode varchar2);//is... 主体
end cur_pack;

有关子程序和程序包的信息
通过查询user_objects数据字典视图，可以获得有关在会话中创建的子程序和程序包的信息。例如：
column object_name format A18
select object_name,object_type from user_objects
where object_type in ('procedure','function','package','package body');
通过查询user_source数据字典视图，可以获得存储子程序的文本，例如：
desc user_source
select line,text from user_source where name='test';


触发器和内置程序包=>
【触发器】
触发器是当特定事件出现时自动执行的代码块。触发器和过程的区别在于：过程是由用户或应用程序显示调用的，而触发器是不能被直接调用的。oracle会在事件请求触发器时，执行适当的触发器。
无论正在执行操作的用户身份如何，触发器都能够在他们执行操作时生效。
触发器的语法为：
create [or replace] trigger trigger_name
{before|after|instead of}
{insert | delete | update [of column[,column]...]}
[or {insert | delete | update [of column[,column]...]}]
on [schema.]table_or_view_name
[referencing [new as new_row_name] [old as old_row_name]]
[for each row]
[when (condition)]
[declare
variable_declation]
begin
statements;
[exception
exception_handlers]
end [trigger_name];
其中instead of--如果使用此子句，表示可以执行触发器代码来代替导致触发器调用的事件。
    referencing--指定新行(即将更新)和旧行(更新前)的其他名称，默认为new和old。
    for each row--指定是否对受影响的每行都执行触发器，即行级触发器，不使用则为语句级触发器。
    when--限制执行触发器的条件，可以包括新旧数据值的检查。
实例代码：
create or replace trigger biu_emp_deptno
befoe insert or update of deptno
on emp
for each row
when (New.deptno<>40)
begin
:New.comm := 0;
end;
触发器由触发器语句(create)、触发器限制(when)和触发器操作(begin...end)3部分组成。
触发器的类型：
类型包括：行级触发器(for each row)、语句级触发器、instead of触发器、模式触发器、数据库级触发器。
1、行级触发器：
在create trigger命令中指定for each row子句创建行级触发器，如果需要通过触发器在插入行中设置一个列值，就应该使用befor insert触发器访问新值。使用after insert触发器不允许设置插入值，因为该行已插入表中。
如果一个触发器有多种语句出发，可以用inserting、updating或deleting条件谓词来检查，如果为“真”，那么就是相应的语句类型激活了该触发器。条件谓词只能在触发器主体中使用(begin if inserting then         .... else ... end if;end;)。
2、语句级触发器：
不常用于与数据相关的活动，通常用于强制实施在表上执行操作的额外的安全措施。
3、instead of触发器：
是在视图上而不是在表上定义的触发器，它用来替换所使用实际语句的触发器。这样的触发器可以克服oracle在任何视图上设置的限制，允许用户修改不能直接使用DML语句修改的视图。
使用instead of触发器的限制：
只能在行级使用，而不能在语句级使用；
只能应用于视图，而不能应用于表。
如果有需要同时向两个表中插入值的情况，可以通过使用instead of触发器来实现。
4、模式触发器：
可以在模式级的操作上建立触发器，如create、alter、drop、grant、revoke和truncate等DDL语句。模式触发器提供的主要功能是阻止DDL操作以及在发生DDL操作时提供额外的安全监控。
创建模式触发器语法为：
create or replace trigger trigger_name
{before|after} trigger_event
on [schema.]schema
when (trigger_condition)
trigger_body;
其中trigger_event--包括create、alter、drop、grant、revoke和truncate等。
5、数据库级触发器：
可以创建在数据库时间上的触发器，包括启动、关闭、服务器错误、登录和注销等。可以使用这种类型的触发器自动进行数据库维护和审计活动。例如：
create or replace trigger system_startup
after startup on database
begin
...
end;

启用和禁用触发器：
语法为
alter trigger trigger_name {enable | disable};
启用和禁用特定表上的所有触发器
alter table table_name {enable | disable} all triggers;

删除触发器：
drop  trigger <trigger_name>;

查看触发器的信息：
使用user_triggers数据字典视图
desc user_triggers;
select trigger_name from user_triggers where table_name='emp';


【内置程序包】
1、dbms_output:
常用过程
dbms_output.enable(buffer_size);
dbms_output.disable;
dbms_output.put(varchar2|number|date);
dbms_output.put_line(varchar2|number|date);
dbms_output.new_line;//换行
2、dbms_lob：
过程
dbms_lob.append;
dbms_lob.copy;
   dbms_lob.erase;//用于删除lob_loc指定的blob或clob中从指定偏移量(offset)开始的指定量(amount)的部分。
函数
dbms_lob.getlength;
dbms_lob.instr;//查找子串
dbms_lob.read;//从lob数据中读取指定长度数据到缓冲区
dbms_lob.substr;//取子串
dbms_lob.write;//将指定数量的数据写入lob
专门操作bfile的过程和函数
dbms_lob.fileopen;
dbms_lob.getname;
dbms_lob.fileclose;
dbms_lob.filesopen;//此项为函数，用于判断bfile文件是否已打开
3、dbms_xmlquery：
用于将查询结果转换为xml格式，例如：
declare
result clob;
xmlstr varchar2(32767);
line varchar2(2000);
line_no integer := 1;
begin
result := dbms_xmlquery.getxml('select empno,ename from employee');
xmlstr := dbms_lob.substr(result,32767);
loop
exit when xmlstr is null;
line := dbms_lob.substr(xmlstr,1,instr(xmlstr,chr(10))-1);
dbms_output.put_line(line_no || ':' || line);
xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
line_no := line_no + 1;
end loop;
end;
4、dbms_random：
用来生成随机数，该包的random函数返回提个8位的整数，正负也是随机的。var_integer := dbms_random.random
5、utl_file:
用于从PL/SQL程序中读写操作系统文本文件。一般过程是打开、读或写、关闭。utl_file包要求文件所在位置是基于oracle的directory对象指定的目录，不能直接使用文件的绝对路径。例如：
create directory test_dir as 'c:\develop';
grant read,write on directory test_dir to scott;
******
declare
src clob;
xmlfile utl_file.file_type;
length integer;
buffer varchar2(16384);
begin
src := dbms_xmlquery.getxml('select * from emp');
length := dbms_lob.getlength(src);
dbms_lob.read(src,length,1,buffer);
xmlfile := utl_file.fopen('test_dir','employees.xml','w');
utl_file.put(xmlfile,buffer);
utl_file.fclose(xmlfile);
end;



数据备份和恢复=>
备份类型：
1、物理备份(对物理文件如数据文件、控制文件和日志文件的备份)
脱机备份(冷备份，关闭数据库时进行)
联机备份(热备份，运行在归档日志方式的数据库进行备份)
另外，用户还可以使用oracle提供的恢复管理器(RMAN,recovery manager)工具集进行增量文件的备份，可以只备份自上次备份以来变化了的数据块。
2、逻辑备份(对逻辑组件如表和存储过程等数据库对象的备份，使用export和import)
没有操作系统信息，所以可以在不同的平台之间传输。

数据库的恢复取决于故障类型，故障类型可分为：
1、语句故障
在执行SQL语句过程中发生的故障可导致语句故障，又可分为坏数据类型和权限不足，oracle可自动修复。
2、用户进程故障
当用户 程序出错而无法访问oracle数据库时，就会发生用户进程故障。导致的原因是异常断开连接或终止进程。PMON(Process Monitor,进程监控程序)会自动执行进程恢复。
3、实例故障
当oracle的数据库实例由于硬件或软件问题而无法继续运行时，就会发生实例故障，oracle会自动完成实例恢复，将数据库恢复到与故障之前的事务一致的状态，oracle会自动回滚未提交的数据。
4、介质故障
介质故障是当一个数据库文件、文件的部分或磁盘不能读或不能写时出现的故障。如何进行介质恢复取决于数据库的控制文件和联机日志文件是否通过复用保护起来。复用是指一个文件存储在其它几个磁盘上的过程，可以实施完全介质恢复或不完全介质恢复。
如果数据文件遭受永久性损坏，并且联机日志文件没有归档，则必须恢复整个数据库。

导出和导入实用程序：
导出实用程序将数据库中的对象和数据备份到一个操作系统二进制文件中，该文件称为导出转储文件(Export Dump Files)，其默认扩展名是.dmp。可以通过OEM(Oracle Enterprise Manager)或命令行交互运行导出和导入实用程序。exp help=y;imp help=y。

归档配置：
在数据库打开后，用SHOW parameter spfile命令查看当前是否是由SPFILE打开数据库。如果数据库是使用SPFILE则会显示具体的路径和SPFILE名，否则显示结果为空，表示使用了INIT参数文件。推荐最好使用SPFILE，因为我们在置归档的时候会动态修改参数，这样会更方便一些。


从ORACLE10G开始，改变日志模式已经变得很简单，很容易管理。在10G中，默认情况下，归档日志会存放到快速恢复区所对应的目录(对应初始化参数db_recovery_file_dest),并且会按照特定的格式生成归档日志文件名。当想要将归档日志放在默认的路径下是，只需要MOUNT状态执行Alter database archivelog即可。在10G中不需要对归档设置自动启动，因为这个参数已经10G摒弃掉，只要执行Alter database archivelog 就会自动置为自动归档。如果不想将归档日志放到默认路径，请执行下列步骤

1.备份初始化参数文件
create pfile from spfile
2.配置归档日志的名称格式
alter system set log_archvie_format='%S_%T_%R.log'  scope=spfile
在10G中设置日志格式时，一定要加%R参数，否则一定会出错
3.配置归档位置
sql>alter system set log_archive_dest_1='location=e:\oracle\oradata\archive' scope=spfile
在ORACLE 10G企业版里还可以配置多个归档路径，log_archvile_dest_n(其中n可以为1-10)
4. 置归档模式
将数据库正常关闭
shutdown immediate
启动到MOUNT模式
startup mount
置为归档模式
alter database archivelog
打开数据库
alter database open
查看数据库是否归档
SQL> archive log list;
如果显示为ENABLE则表示已成功归档或 SQL>select log_mode  from v$database;
如果为ARCHIVELOG则表示已成功归档