ORACLE相关语法
一、Oracle入门
理论知识：
Oracle的物理组件有三个：
(1)数据文件 数据文件是用于存储数据库数据的文件，如表、索引数据。每个Oracle数据库有一个或多个物理数据文件，
一个数据文件只能与一个数据库关联。
(2)日志文件 用于记录对数据库进行的修改信息，日志文件主要用于在数据库出现故障时实施数据库恢复。
(3)控制文件 控制文件是记录数据库物理结构的二进制文件，每个Oracle数据库都含有一个控制文件。
Oracle的逻辑组件：
表空间(TableSpace) 表空间是数据库最大的逻辑单位，一个数据库至少包含一个表空间，一个表空间包含一个或多个段等等。
段(Segment) 段存在于表空间中，分成4类，数据段、索引段、回退段、临时段。
区(Extent) 区是磁盘空间分配最小单位，由连续的数据块组成，一个或多个区构成段，区只能存在于一个数据文件中。
数据块(Data Block) 数据块是数据库中最小的数据组织单位与管理单位，Oracle数据库中的数据存储于数据块中，取值范围2K-64K之间。
模式(schema) 模式是对用户所创建的数据库对象的总称，又称为用户模式。
概念：
内存 Oracle内存结构包含以下两个内存区。
1、系统全局区(SGA) 实例启动时分配该内存区，是Oracle实例的一个基本组件。
又称为共享全局区，它用来存储数据库信息，并由多个数据库进程共享。可分为共享池、数据缓冲区及日志缓冲区。
(1)共享池 是对SQL、PL/SQL程序进行语法分析、编译、执行的内存区域。共享池由库缓存和数据字典缓存组成。其中，库缓存含有
最近执行的SQL、PL/SQL语句的分析码和执行计划；数据字典缓存含有从数据字典中得到的表、索引、列定义和权限等信息。
(2)数据缓冲区 数据缓冲区用于存储从磁盘数据文件中读入的数据，所有用户共享。
(3)日志缓冲区 日志记录数据库的所有修改信息，主要用于恢复数据。
2、程序全局区(PGA) 服务器进程启动时分配该内存区。PGA为非共享区，只能单个进程使用，当一个用户会话结束后，PGA释放。
用户进程(PGA)发送SQL语句到共享全局区(SGA)，先在共享池的库缓存中查询是否存在所需的数据块，如果存在就在数据字典中读取相应的数据块，如
果不存在就由服务器进程(DBWR)来IO数据库
语法知识：
创建表空间的语法如下：
CREATE TABLESPACE tablespacename DATAFILE 'd:/filename.DBF' [SIZE int [KB|MB]] [AUTOEXTEND [OFF|ON]];
tablespacename 是需创建的表空间名称。
DATAFILE 指定组成表空间的一个或多个数据文件，当有多个数据文件时使用逗号分隔。
filename 是表空间中数据文件的路径和名称。
SIZE 指定文件的大小，用K指定千字节大小，用M指定兆字节大小。
AUTOEXTEND子句用来启用或禁用数据文件的自动扩展。
Oracle默认用户：
用户名：sys 默认密码：chage_on_install 用来管理拥有Oracle数据字典文件
用户名：system 默认密码：manager 用来管理拥有数据字典视图对象
用户名：scott 默认密码：tiger 示例用户，包括emp、dept等表
连接Oracle: 在控制台下输入 sqlplus 用户名/密码 回车或 sqlplusw 回车
相关命令：
disconn //退出当前登录
conn 用户名/密码 //连接Oracle
alter user 用户名 identified by 密码 //修改用户口令
drop user 用户名 cascade; //删除用户
alter user 用户名 account lock; //给某个用户加锁
alter user 用户名 account unlock; //给某个用户解锁
ed 回车: //打开缓冲区
/ 回车: //执行缓冲区中的语句
create user 用户名 identified by 密码 [password expire]
[default tablespace 表空间名] [temporary tablespace 临时表空间名]; //创建用户
相关权限：
grant connect to scott; //connect角色将允许用户创建数据库并在数据库中创建表或其他对象
grant resource to scott; //resource角色将允许用户使用数据库中的空间
grant create sequence to scott; //create sequence权限将允许用户创建序列，此权限包含在connect连接角色中
grant select on emp to scott; //将emp表的查询权限授予用户scott
grant update(vencode,venname) on 表名 to scott; //将特定列的更新权限授予用户scott
grant 权限 on 表名 to 用户名 with grant option; //接受该权限的用户可以将此权限授予其他用户
revoke select,update on 表名 from 用户名; //收回相应的权限
二、SQL查询和SQL函数
SQL支持如下类别的命令：
数据定义语言： create(创建)、alter(更改)、drop(删除)和truncate(截断)命令。
数据操纵语言： insert(插入)、select(选择)、delete(删除)和update(更新)命令。
事务控制语言： commit(提交)、savepoint(保存点)和rollback(回滚)命令。
数据控制语言： grant(授予)和revoke(回收)命令。
数据类型：
char: 长度在1到2000个字节，声明多少字节在内存中就占用多少字节，输入的值小于指定的长度时用空格填充。
varchar2: 长度在1到4000个字节，输入的值是多少字节，就占用多少字节。
long: 长度在2GB，设置为此类型的列时，要注意：一个表中只有一列可以为long类型，long类型列不能定义为唯一约束或主键约束，
不能建立索引，过程或存储过程不能接受long类型的参数。
number(p,s): 其中p为精度，表示数字的总位数，在1至38之间。s为范围，表示小数点右边数字的位数，在-84至127之间。
date: 日期类型，sysdate为当前系统时间。格式为08-9月 -07。
timestamp: 用于存储日期的年、月、日以及时间的时、分和秒。其中秒精确到小数点后6位，
systimestamp返回当前日期、时间。格式为08-9月 -07 04.08.30.000000 下午。
raw: 此数据类型用于存储基于字节的数据，如二进制数据或字节串，该类型最多能存储2000个字节，可以建立索引。
long raw: 此数据类型用于可变长度的二进制数据，最多能存储2GB。long数据类型的所有限制对long raw数据类型也同样有效。
lob又称为"大对象"数据类型，最多能存储4GB的非结构化信息。包括：
clob: clob代表Character LOB(字符LOB)，它能存储大量字符数据。如XML文档。
blob: blob代表Binary LOB(二进制LOB)，它能存储较大的二进制对象，如图形、视频剪辑和声音剪辑。
bfile: bfile代表Binary File(二进制文件)，它能够将二进制文件存储在数据库外部的操作系统文件中。
伪列：
rowid: select rowid,ename, from scott.emp where empno='7900';
rownum: select * from scott.emp where rownum<11; //限制查询返回的行数
语法知识：
desc 表名; //查看表结构
alter table 表名 modify (列名 varchar2(25)); //修改列
alter table 表名 add (列名 varchar2(12),列名 number(12)); //添加列
alter table 表名 drop column 列名; //删除列
truncate table 表名; //中删除记录而不删除结构，不使用事务处理，因此无法回滚
drop table 表名; //删除表及其全部数据
create table 新表名 as select * from 表名 where 1=2; //用现有的表创建一个新表
select deptno*2 "New No",dname,loc from dept; //指定一个含有特殊字符(如空格)的列标题
commit; //提交事务
savepoint 标记名; //标记事务点
rollback; //回滚整个事务处理
rollback to [savepoint] 标记名; //回滚到事务中某个特定的保存点
集合操作符：
union(联合): 此操作符返回两个查询选定的所有不重复的行。
语法： select orderno from order_master UNION select orderno from order_detail;
union all(联合所有): 此操作符合并两个查询选定的所有行，包括重复的行。
语法： select orderno,ename from order_master UNION ALL select orderno,proname from order_detail order by 2;
注意：在两个select语句中指定的列名不必相同，但数据类型必须匹配。也可以对联合查询的结果进行排序，使用Order By子句时，它必须放在最后
一个select语句之后，而且必须指定列索引来排序，而不是指定列名，列索引是从1开始的整数。上述语法便是以proname的索引排序
intersect(交集): 此操作符只返回两个查询都有的行。
语法： select orderno from order_master INTERSECT select orderno from order_detail;
minus(减集): 此操作符中返回由第一个查询选定但是第二个查询中没有选定的行，也就是在第一个查询结果中排除第二个查询结果中出现的行。
语法： select orderno from order_master MINUS select orderno from order_detail; 查询尚未交付的订单
连接(||)操作符：
语法： select ('供应商'||venname||'的地址是'||venadd1||' '||venadd2||' '||venadd3) 地址 from vendor_master where vencode='V002';
将多个字符串合并为一个字符串
SQL函数：
1、日期函数：
add_months: 此函数返回给指定的日期加上指定的月数后的日期值。语法为add_months(d,n),其中d是日期，n表示月数。
示例：select add_months(sysdate,2) from dual; 将当前时间加上2个月后的日期值。
months_between: 此函数返回两个日期之间的月数。语法为months_between(d1,d2),其中d1和d2是日期，如果d1大于d2，则结果为正数；否则为负数。
last_day: 此函数返回指定日期当月的最后一天的日期值，语法为last_day(d),其中d表示日期。
示例：select last_day(sysdate) from dual; 返回当前日期的月的最后一天，如果是9月就返回30-09月-07
round: 此函数返回日期值，将日期四舍五入为格式模型指定的单位。语法为round(d,[fmt])。其中d是日期，fmt是格式模型。fmt是一个可
选项，日期默认舍入为最靠近的那一天。如果指定格式为年"Year"，则舍入到年的开始，即1月1日;如果格式为月"Month"，则舍入到
月的第一日;如果格式为周"Day"，则舍入到最靠近的星期日。
示例：select round(sysdate,'month') from dual; 返回最接近的一个月。
next_day: 此函数返回指定的下一个星期几的日期。语法为next_day(d,day)。其中d表示日期，而day指周内任何一天。
示例：select next_day(sysdate,'星期日') from dual; 返回下一个星期日的日期，也可以用1表示，以此类推，星期一以2表示。
trunc: 此函数将指定日期截断为由格式模型指定的单位日期，与Round函数不同的是它只舍不入，语法为trunc(d,[fmt])，与round格式相同。
示例：select trunc(sysdate,'year') from dual; 返回当前年的第一天，也就是1月1日。
示例：select trunc(sysdate,'day') from dual; 返回紧靠前面的星期日。如果为"2005年1月27日"就会返回"2005年1月23日"。
extract: 此函数提取日期时间类型中的特定部分。语法为extract(fmt from d)，其中d是日期时间表达式，fmt是要提取的部分的格式。格式
的取值可以是year,month,day,hour,minute,second,注意此处的格式不使用单引号。
示例：select extract(year from sysdate) from dual; 返回当前的年份。
2、字符函数：
initcap(char): 首字母大写，示例：select initcap('hello') from dual; 输出结果:Hello。
lower(char): 转换为小写，示例：select lower('FUN') from dual; 输出结果:fun。
upper(char): 转换为大写，示例：select upper('sun') from dual; 输出结果:SUN。
ltrim(char,set): 左剪裁，示例：select ltrim('xyzadams','xyz') from dual; 输出结果:adams。
rtrim(char,set): 右剪裁，示例：select rtrim('xyzadams','ams') from dual; 输出结果:xyzad。
translate(char,from,to): 按字符翻译，示例：select translate('jack','abcd','1234') from dual; 输出结果:j13k。
replace(char,search_str,replace_str): 字符串替换，示例：select replace('jack and jue','j','bl') from dual; 输出结果:black and blue。
instr(char,substr[,pos1,pos2]): 查找子字串位置。
示例：select instr('vorldwide','d') from dual; 输出结果:5。pos1为可选，表示从第几个位置查找。pos2为可选，表示从第几次出现的位置找。
substr(char,pos,len): 取子字符串，示例：select substr('abcdefg',3,2) from dual; 输出结果:cd。
concat(char1,char2): 连接字符串，示例：select concat('Hello','world') from dual; 输出结果:Helloworld。
chr: 此函数根据Ascii码返回对应的字符，示例：select chr(45788),chr(53671),chr(50167),chr(65) from dual; 输出结果:曹 学 明 A。
ascii: 此函数返回GBK编码值，示例：select ascii('曹') cao ,ascii('学') xue,ascii('明') Ming from dual; 输出结果:45788 53671 50167。
lpad和rpad： 示例：select lpad('function',15,'=') from dual; 输出结果:=======function。而rpad则相反，字符串填充在右边。
trim: 此函数从字符串的开头或结尾(或开头和结尾)剪裁特定的字符，默认剪裁空格。如果加上leading选项时与ltrim函数相似。指定trailing时和 rtrim函数相似。
示例： select trim(9 from 999992598899) from dual; 输出结果:25988。
示例： select trim(leading 9 from 999992598899) from dual; 输出结果:2598899。
示例： select trim(trailing 9 from 999992598899) from dual; 输出结果:9999925988。
length: 此函数返回字符串的长度，示例：select length('frances') from dual; 输出结果:7。
decode: 示例：select deptno,dname,decode(loc,'NEW YORK','纽约','BOSTON','波士顿') from scott.dept;
此示例将替换显示loc列的结果,结果为"NEW YORK"的替换为"纽约"，"BOSTON"的替换为"波士顿"。
GREATEST/least: 返回一组表达式中的最大值/最小值,即比较字符的编码大小.
示例：select greatest('AA','AB','AC') from dual; 输出结果:AC。
select least('AA','AB','AC') from dual; 输出结果:AA。
select greatest('啊','安','天') from dual; 输出结果:天。
select least('啊','安','天') from dual; 输出结果:啊。
3、数字函数：
abs(n): 取绝对值，示例：select abs(-15) from dual; 输出结果:15。
ceil(n): 向上取整，示例：select ceil(44.778) from dual; 输出结果:45。
sign(n): 取符号，示例：select sign(-2) from dual; 输出结果:-1。
floor(n): 向下取整，示例：select floor(200.88) from dual; 输出结果:200。
power(m,n): m的n次幂，示例：select power(5,3) from dual; 输出结果:125。
mod(m,n): 取余数，示例：select mod(10,3) from dual; 输出结果:1。
round(m,n): 四舍五入，示例：select round(100.256,2) from dual; 输出结果:100.26。
trunc(m,n): 截断，示例：select trunc(100.256,2) from dual; 输出结果:100.25。
sqrt(n): 平方根，示例：select sqrt(4) from dual; 输出结果:2。
4、转换函数：
to_char(d|n[,fmt]): 其中d是日期，n是数字，fmt指定日期或数字的格式。
示例：select to_char(sysdate,'yyyy"年"fmmm"月"fmdd"日" hh24:mi:ss') from dual; 输出结果:2007年9月09日 20:44:27。
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24"时"mi"分"ss"秒"') from dual;
示例：select to_char(sal,'$99999') from emp; 输出结果:$1600。
to_date(char[,fmt]): 此函数将char或varchar2数据类型转换为日期数据类型。
示例：select to_date('2005-12-06','yyyy-mm-dd') from dual; 输出结果:06-12月-05。
select to_date('2008/08/10 20:08:08','yyyy/mm/dd hh24:mi:ss') from dual;
select to_date('2008-08-10 20:08:08','yyyy/mm/dd hh24:mi:ss') from dual;
select to_date('2008年08月10日 20时08分08秒','yyyy"年"mm"月"dd"日" hh24"时"mi"分"ss"秒"') from dual;
to_number(char): 此函数将包含数字的字符串转换为number数据类型，通常不用这么做，因为Oracle可以对数字字符串进行隐式转换。
示例：select sqrt(to_number('100')) from dual; 输出结果:10。
5、其它函数：
nvl(expression1,expression2): 如果expression1为NULL，则nvl返回expression2。
nvl2(expression1,expression2,expression3): 如果expression1不是NULL，则nvl2返回expression2，如果expression1是NULL，则返回expression3。
nullif(expr1,expr2): 此函数比较两个表达式，如果它们相等，则返回空值，否则返回expr1。
nullif函数等价于以下的case表达式：
case when expr1=expr2 then null else expr1 end
6、分组函数：
avg: 此函数返回指定列值的平均值，示例：select avg(sal) from emp; 输出结果:2073.21429。
min: 此函数返回指定列值的最小值，示例：select min(sal) from emp; 输出结果:800。
max: 此函数返回指定列值的最大值，示例：select max(sal) from emp; 输出结果:5000。
sum: 此函数返回指定列值的总和，示例：select sum(sal) from emp; 输出结果:29025。
count: 此函数是为了计算行数，它可以接受3种不同的参数
示例：select count(*) from emp; 输出结果:14。
示例：select count(列名) from 表名;
示例：select count(distinct 列名) from 表名;
group by: 此子句用于将信息表划分为组，按组进行聚合运算。select后面跟的列名只能是分组函数、group by子句中出现的列或表达式。
示例：select deptno,max(sal) from emp group by(deptno); 查出每个部门的最高工资。
示例：select deptno,count(*),sum(sal) from scott.emp group by deptno; //group by主要用来对一组数进行统计
having: 此子句用来指定group by子句的检索条件。
示例：select deptno,count(*),sum(sal) from scott.emp group by deptno having count(*)>=5; //having对分组统计再加限制条件
7、分析函数：只能出现在select列表或order by子句中。
row_number: 下面对所有员工的工资进行排名，即使工资相同，其排名也不能相同。
示例：select ename,job,deptno,sal,row_number() over(order by sal desc) as 排名 from scott.emp;
下面对所有员工的工资按部门进行排名，即使工资相同，排名也不同。
示例：select ename,job,deptno,sal,row_number() over(partition by deptno order by sal desc) as 排名 from scott.emp;
rank: 此函数计算一个值在一组值中的排位，排位是以1开头的连续整数，如果两行的序数为1，则没有序数2，下行的序数为3。
下面根据员工的工资和佣金对员工在每个部门中进行排位。相同的工资排位相同，并且排位不连续。
示例：select ename,sal,comm,deptno,rank() over(partition by deptno order by sal desc,comm) 排名 from scott.emp;
dense_rank: 此函数计算一个行在一组有序行中的排位，排位是以1开头的连续整数，具有相同值的排位相同，并且排位是连续的。
下面首先选择所有在accounting或research部门中工作的员工的部门名称、员工姓名和工资，
然后分别计算每个员工的工资在部门中的排位，相等的工次排位相同
示例：select d.dname,e.ename,e.sal,dense_rank() over(partition by e.deptno order by e.sal desc) 排名
from emp e,dept d where e.deptno=d.deptno;
三、锁和表分区
锁定是数据库用来控制共享资源并发访问的机制。
Oracle提供以确保在多用户环境下数据的完整性和一致性。
锁的两种级别：(只有在提交或回滚后才能释放锁定)
(1)行级锁：是一种排他锁，防止其他事务修改此行，但是不会阻止读取此行的操作。在使用Insert、Update、Delete和Select...For Update
等语句时，Oracle会自动应用行级锁定。
Select...For Update语法为：
Select...For Update [OF column_list] [WAIT n | NOWAIT]
其中：OF子句用于指定即将更新的列，即锁定行上的特定列。
WAIT子句指定等待其他用户释放锁的秒数，防止无限期的等待。NOWAIT为不等待。
示例1：演示如何锁定deptno值为10的所有行。
select * from dept where deptno=10 for update of dname,loc;
(2)表级锁：将保护表数据，在事务处理过程中，表级锁会限制对整个表的访问。表级锁用来限制对表执行添加、更新和删除等修改操作。
语法：
LOCK TABLE <table_name> IN <lock_mode> MODE [NOWAIT];
其中：table_name是要被锁定的表的名称。
lock_mode是锁定的模式。
表级锁的模式：
1、行共享(ROW SHARE,RS)： 允许其他用户访问和锁定该表，但是禁止排他锁锁定整个表。
2、行排他(ROW EXCLUSIVE,RX)：与行共享模式相同，同时禁止其他用户在此表上使用共享锁。使用Select...For update语句会自动应用行排他。
3、共享(SHARE,S)：共享锁将锁定表，仅允许其他用户查询表中的行，但不允许插入、更新或删除行。多个用户可以同时在同一张表中放置共享锁
即允许资源共享。但是这样极容易造成死锁。
4、共享行排他(SHARE ROW EXCLUSIVE,SRX)： 执行比共享表锁更多的限制。防止其他事务在表上应用共享锁、共享行排他锁以及排他锁。
5、排他(EXCLUSIVE,X)： 对表执行最大限制。除了允许其他用户查询该表的记录，排他锁防止其他事务对表做任何更改或在表上应用任何类型的锁。
示例2：演示如何以共享模式锁定表。
lock table dept in share mode nowait;
表分区的优点：
改善表的查询性能;表更容易管理;便于备份和恢复;提高数据安全性。
注意：要分区的表不能具有Long和Long Raw数据类型的列。
四种分区方法：
1、范围分区：根据表的某个列或一组列的值范围，决定将该数据存储在哪个分区上。
语法如下：
在Create Table语句后增加
PARTITION BY RANGE(column_name)
(
PARTITION part1 VALUE LESS THAN (range1) [TABLESPACE tbs1],
PARTITION part2 VALUE LESS THAN (range2) [TABLESPACE tbs2],
....
PARTITION partN VALUE LESS THAN (MAXVALUE) [TABLESPACE tbsN]
);
其中：column_name是以其为基础创建范围分区的列，特定行的该列值称为分区键。
part1...partN是分区的名称。
range1...MAXVALUE是分区的边界值。
tbs1...tbsN是分区所在的表空间，TABLESPACE子句是可选项。
示例3：
create table t_emp
(
empno number(4),
ename varchar2(30),
sal number
)
partition by range(empno)
(
partition e1 values less than (1000) tablespace emp1, 也可以 than (to_date('2003-01-01','yyyy-mm-dd'))
partition e2 values less than (2000) tablespace emp2, 也可以 than (to_date('2004-01-01','yyyy-mm-dd'))
partition e3 values less than (maxvalue) tablespace emp3
);
2、散列分区：语法有两种如下
PARTITION BY HASH(column_name)
PARTITIONS number_of_partitions [STORE IN (tablespace_list)];
或
PARTITION BY HASH(column_name)
(
PARTITION part1 [TABLESPACE tbs1],
PARTITION part2 [TABLESPACE tbs2],
...
PARTITION partN [TABLESPACE tbsN]
);
其中：column_name是以其为基础创建散列分区的列。
number_of_partitions是散列分区的数目，使用这种方法系统会自动生成分区的名称。
tablespace_list指定分区使用的表空间，如果分区数目比表空间的数目多，分区将会以循环的方式分配到表空间中。
part1...partN是分区的名称。
tbs1...tbsN是分区所在的表空间，TABLESPACE子句是可选项。
示例4：自动分配4个散列分区，
可以使用select partition_name,HIGH_VALUE from user_tab_partitions where table_name=upper('t_emp')查询分区名
create table t_emp
(
empno number(4),
ename varchar2(30),
sal number
)
partition by hash (empno)
partitions 4;
3、复合分区：是范围分区和散列分区的结合。在创建复合分区时，先根据范围对数据进行分区，然后在这些分区内创建散列子分区。
语法如下：
PARTITION BY RANGE(column_name1)
SUBPARTITION BY HASH(column_name2)
SUBPARTITIONS number_of_partitions [STORE IN (tablespace_list)]
(
PARTITION part1 VALUE LESS THAN (range1) [TABLESPACE tbs1],
PARTITION part2 VALUE LESS THAN (range2) [TABLESPACE tbs2],
....
PARTITION partN VALUE LESS THAN (MAXVALUE) [TABLESPACE tbsN]
);
其中：column_name1是以其为基础创建范围分区的列。
column_name2是以其为基础创建散列分区的列。
number_of_partitions是要创建的子分区的数目。
part1...partN是分区的名称。
range1...MAXVALUE是范围分区的边界值
示例5：将雇员表先按照雇佣时间hiredate进行了范围分区，然后再把每个分区分为2个子hash分区，此表一共是6个分区。
create table t_emp
(
empno number(4),
ename varchar2(30),
hiredate date
)
partition by range (hiredate)
subpartition by hash (empno)
subpartitions 2
(
partition e1 values less than (to_date('20020501','YYYYMMDD')),
partition e2 values less than (to_date('20021001','YYYYMMDD')),
partition e3 values less than (maxvalue)
);
4、列表分区：此分区允许用户明确地控制行到分区的映射。
语法如下：
PARTITION BY LIST(column_name)
(
PARTITION part1 VALUES (values_list1),
PARTITION part2 VALUES (values_list2),
....
PARTITION partN VALUES (DEFAULT)
);
其中：column_name是以其为基础创建列表分区的列。
part1...partN是分区的名称。
values_list是对应分区的分区键值的列表。
DEFAULT关键字允许存储前面的分区不能存储的记录。
示例6：
create table t_emp1
(
empno number(4),
ename varchar2(30),
location varchar2(30)
)
partition by list (location)
(
partition e1 values ('北京'),
partition e2 values ('上海','天津','重庆'),
partition e3 values ('广东','福建'),
);
要查询表分区中的数据行：select * from table_name PARTITION(p1); 注：p1是分区名。
分区维护操作：
1、添加分区：ALTER...ADD PARTITION语句用于在现有的最后一个分区之后添加新的分区。
示例7：演示如何将名为E4的新分区添加到示例3中创建的t_emp表。
ALTER TABLE t_emp ADD PARTITION E4 VALUES LESS THAN (3000);
在此请注意：上例公适用于已使用特定的键值定义了最后一个分区的表。如果要在表的开始或中间位置添加分区，或者最高分区的分区
边界是MAXVALUE，则应使用SPLIT PARTITION语句。
2、删除分区：使用ALTER TABLE...DROP PARTITION语句。
示例8：演示了如何删除t_emp表的E4分区。删除分区时，分区中的数据也随之删除。
ALTER TABLE t_emp DROP PARTITION E4;
3、截断分区：使用ALTER TABLE...TRUNCATE PARTITION语句来截断分区，只删除表分区中的所有记录。
示例9：演示了如何删除t_emp表中e3的分区的所有记录。
ALTER TABLE t_emp TRUNCATE PARTITION e3;
4、合并分区：可以将范围分区或复合分区表的两个相邻分区连接起来。结果分区将继承被合并的两个分区的较高上界。
语法如下：
ALTER TABLE table_name MERGE PARTITIONS partitions_name,partitions_name INTO PARTITION partition_name;
示例10：演示了如何将e1和e2合并成一个e2分区。
ALTER TABLE t_emp MERGE PARTITIONS e1,e2 INTO PARTITION e2;
5、拆分分区：使用SPLIT PARTITION语句在表的开头或中间添加分区。拆分分区允许用户将一个分区拆分为两个分区。
语法如下：
ALTER TABLE table_name SPLIY PARTITION partition_name AT (value) INTO (PARTITION partition1,PARTITION partition2);
示例11：演示了如何将t_emp表中的e3分区拆分为e31和e32两个分区。
ALTER TABLE t_emp SPLIT PARTITION e3 AT (Date '2005-01-01') INTO (PARTITION e31,PARTITION e32);
6、重新命名拆分后的分区:
ALTER TABLE t_emp RENAME PARTITION e31 TO P3;
ALTER TABLE t_emp RENAME PARTITION e32 TO P4;
可以查询字典视图user_tab_partitions来查看用户所创建的分区的详细信息
示例12：select table_name,partition_name,high_value from user_tab_partitions;
可以查询字典视图dba_tab_subpartitions来查看用户所创建的子分区的详细信息
示例13：select table_name,partition_name,subpartition_name from dba_tab_subpartitions;
四、数据库对象
表、视图、序列、过程、函数、程序包，甚至其它同义词都可以创建同义词。
1、同义词：私有同义词、公有同义词。
私有同义词只能被当前模式的用户访问。私有同义词名称不可与当前模式的对象名称相同。要在自身的模式创建私有同义词，
用户必须拥有Create Synonym系统权限。要在其它用户模式创建私有同义词，用户必须拥有Create Any Synonym系统权限。
公有同义词可被所有的数据库用户访问。要创建公有同义词，用户必须拥有Create Public Synonym系统权限。
创建私有同义词语法：
Create [OR REPLACE] SYNONYM [schema.]synonym_name FOR [schema.]object_name;
其中：OR REPLACE表示在同义词存在的情况下替换该同义词。
synonym_name表示要创建的同义词的名称。
object_name指定要为之创建同义词的对象的名称。
示例1：create synonym s_emp for scott.emp;
创建公有同义词语法：
Create PUBLIC SYNONYM synonym_name FOR [schema.]object_name;
示例2：create public synonym emp_syn from scott.emp;
可以查询字典视图User_Synonyms来查看用户所创建的同义词的详细信息
删除同义词语法：Drop Synonyms synonym_name; 删除公有同义词加上一个Public
此命令只删除同义词，不会删除对应的表。
2、序列：是用来生成唯一、连续的整数的数据库对象。序列通常用来自动生成主键或唯一键的值。
创建序列语法如下：
Create SEQUENCE sequence_name
[START WITH integer]
[INCREMENT BY integer]
[MAXVALUE integer|NOMAXVALUE]
[MINVALUE integer|NOMINVALUE]
[CYCLE|NOCYCLE]
[CACHE interger|NOCACHE];
其中：START WITH是指定要生成的第一个序列号。对于升序序列，其默认值为序列的最小值。对于降序序列，其默认值为序列的最大值。
INCREMENT BY是用于指定序列号之间的间隔。其默认值为1。如果integer为正值，则生成的序列将按升序排列，否则按降序排列。
MAXVALUE指定序列可以生成的最大值。
NOMAXVALUE这是默认选项，将升序序列的最大值设为10的27次幂，将降序序列的最大值设为-1。
MINVALUE指定序列的最小值。MINVALUE必须小于或等于START WITH的值，并且必须小于MAXVALUE。
NOMINVALUE这是默认选项，将升序序列的最小值设为1，将降序序列的最小值设为-10的26次幂。
CYCLE指定序列在达到最大值或最小值后，将继续从头开始生成值。
NOCYCLE这是默认选项。指定序列在达到最大值或最小值后，将不能再继续生成值。
CACHE使用CACHE选项可以预先分配一组序列号，并将其保留在内存中，这样可以更快的访问序列号。
NOCACHE此项则不会为加快速度而预先分配序列号。如果在创建序列时忽略了CACHE和NOCACHE选项，Oracle将默认缓存20个序列号。
示例3：Create SEQUENCE toys_seq
START WITH 10
INCREMENT BY 2
MAXVALUE 2000
MINVALUE 10
NOCYCLE
CACHE 30;
访问序列：可以通过CURRVAL和NEXTVAL伪列来访问该序列的值。
示例4：演示从序列toys_seq中选择值插入toys表中的toyid列。执行成功将会在该表的toyid列插入值"P10"和"P12"。
INSERT INTO toys(toyid,toyname,toyprice) values('p'||toys_seq.NEXTVAL,'TWENTY',25);
INSERT INTO toys(toyid,toyname,toyprice) values('p'||toys_seq.NEXTVAL,'MAGIC PENCIL',75);
示例5：演示如何查看序列当前值
Select toys_seq.CURRVAL from dual;
更改序列：ALTER SEQUENCE命令用于设置或删除MINVALUE或MAXVALUE、修改增量值、修改缓存中的序列号的数目。
修改序列语法如下：注意，不能修改序列的START WITH参数。在修改序列时，应注意升序序列的最小值应小于最大值。
ALTER SEQUENCE [schema.]sequence_name
[INCREMENT BY integer]
[MAXVALUE integer|NOMAXVALUE]
[MINVALUE integer|NOMINVALUE]
[CYCLE|NOCYCLE]
[CACHE interger|NOCACHE];
示例6：演示如何设置一个新的MAXVALUE，并为toys_seq序列打开了CYCLE。
ALTER SEQUENCE toys_seq
MAXVALUE 5000
CYCLE;
可以查询字典视图User_Sequences来查看用户所创建的序列的详细信息
删除序列语法：Drop SEQUENCE toys_seq;
3、视图
视图是存储的查询定义。
创建视图的语法如下：
Create [OR REPLACE] [FORCE | NOFORCE] VIEW view_name[(alias,alias,...)] AS
select_statement [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY];
其中：OR REPLACE表示在该视图存在的话，将重新创建该视图。
FORCE使用此关键字，则无论基表是否存在，都将创建视图。
NOFORCE这是默认值。如果使用此关键字，则仅当基表存在时才创建视图。
view_name表示要创建视图的名称。
alias指定在视图里面列的名字，名字数目必须与视图所选择的表达式的数目相匹配。
select_statement表示Select语句。
WITH CHECK OPTION此选项指定只能插入或更新视图可以访问的行。术语constraint表示为CHECK OPTION约束指定的名称。
WITH READ ONLY此选项确保不能在此视图上执行任何修改操作。
示例7：演示创建一个名为ven_view的视图，该视图与vendor_master表具有相同的结构。
Create VIEW ven_view AS select * from vendor_master;
视图中的ORDER BY子句：以便在查询视图时即使不使用Order By子句，结果集也会按指定的顺序排列行。
示例8：Create OR REPLACE VIEW ven_view(编号,日期) AS select orderno,odate from vendor_master order by venname;
创建带有错误的视图：使用FORCE选项
在以下情况下，Oracle也会创建视图：
视力定义的查询引用了一个不存在的表;视图定义的查询引用了现有表中无效的列;视图的所有者没有所需的权限。
示例9：下面创建一个基于venmast的视图。但数据库中并不存在名为"venmast"的表。
Create FORCE VIEW ven AS select * from venmast;
如果稍后创建名为venmast的表，可以使用ALTER VIEW ven COMPILE;
联接视图：
示例：演示如何创建联接视图
Create OR REPLACE VIEW ven_ord_view AS select vm.vencode,venname,orderno,odate,ostatus
from vendor_master vm,order_master om where vm.vencode=om.vencode;
键保留表：在联接视图中，如果视图包含了一个表的主键，并且也是这个视图的主键，则这个键被保留，则这个表被称为键保留表。
ven_ord_view视图以vendor_master和order_master这两个表为基表。order_master表被视为键保留表，因为orderno既是order_master表
的主键，也是视图的主键。而vendor_master表不被视为键保留表，因为vendor_master表的主键vencode是联接字段，不是视图的主键。
示例10：此示例成功修改了记录，因为odate列属于键保留表order_master。对于非键保留表vendor_master的venname列的更新则是不允许的。
update wen_ord_view odate=odate+1 where vencode='V003'; 注意：运行此示例时，必须确保基表的主键已正确创建。
可以查询字典视图User_Updateble_Columns来查看联接视图中可更新的列。
可以查询字典视图User_Views来查看用户所创建的视图信息。
视图中的函数：视图可以使用单行函数(由数字、字符、日期组成)、分组函数和表达式。
示例11：此示例用UPPER函数创建视图，注意使用函数或表达式时，应赋予列一个别名。
Create VIEW vendor_master_view AS select vencode,UPPER(venname) vendor_name from vendor_master;
删除视图语法：Drop VIEW view_name ;
4、索引：
查询User_indexes可以获取有关用户已创建的索引的详细信息。
查询User_ind_partitions可以获取有关用户已创建的分区索引的详细信息。
查询User_ind_columns可以获取有关列(用户的索引是基于这些列创建的)的详细信息。
唯一索引、组合索引、反向键索引、位图索引和基于函数的索引。
索引在逻辑上物理上都独立于关联表中的数据，在任何时候都可以创建或删除索引，而不会影响基表或其它索引。
创建普通索引的语法：
Create INDEX index_name ON table_name(column_list) [TABLESPACE tablespace_name];
其中：index_name指所创建索引的名称。
table_name表示为之创建索引的表名。
column_list是在其上创建索引的列名列表，可以基于多列创建索引。
tablespace_name为索引指定表空间。
示例12：演示如何在itemfile表的itemcode列上创建索引
create index item_index on itemfile(itemcode);
ALTER INDEX语句的REBUILD选项可以用来重建现有的索引。该选项提供的性能要优于使用DROP INDEX和CREATE INDEX语句重新创建索引。
示例13：重建索引
ALTER INDEX item_index REBUILD;
删除索引语法：Drop INDEX item_index;
(1)唯一索引：此索引可以确保在定义索引的列中，表的任意两行的值都不相同。Oracle自动为表的主键列创建唯一索引。
可以使用Create UNIQUE INDEX命令明确地创建唯一索引。
示例14：在itemfile表的itemcode列上创建了一个名为item_index的唯一索引。
Create UNIQUE INDEX item_index ON itemfile(itemcode);
(2)组合索引：组合索引是在表中的多个列上创建的索引。组合索引中列的顺序是任意的，不必是表中相邻的列。
创建组合索引时，应注意定义中使用的列的顺序。通常，最频繁访问的列应放置在列表的最前面。
示例15：在itemfile表上创建了一个名为comp_index的组合索引，当查询该表的的WHERE子句同时包含这两个列或只包含
p_category列时，以下示例语句创建的索引将用于检索数据，但如果单独使用itemrate列，则索引不能用于检索数据。
Create INDEX comp_index ON itemfile(p_category,itemrate);
(3)反向键索引：通常建立在一些值连续增长的列上，例如列中的值是是由序列产生的情况。
示例16：在itemfile表上创建了一个名为rev_index的反向键索引。注意使用REVERSE关键字。
Create INDEX rev_index ON itemfile(itemcode) REVERSE;
示例17：使用关键字NOREVERSE可以将反向键索引重建为标准索引。
ALTER INDEX rev_index REBUILD NOREVERSE;
注意：不能将标准索引重建为反向键索引。
(4)位图索引：如果某个列的值重复超过一百次，则可以考虑在该列上创建位图索引。
示例18：itemcode是order_detail表中的低基数列，因为货物编码在大多数订单中都是重复的，因此适合在该列上创建位图索引。
Create BITMAP INDEX bit_ind1 ON order_detail(itemcode);
位图索引不应当用在频繁发生的INSERT,UPDATE,DELETE操作的表上。位图索引最适合于数据仓库和决策支持系统。
(5)索引组织表：索引组织表与在一个或多个列上建立索引的普通表相似，但它无需为表和索引维护两个单独的存储空间，
数据库系统仅维护一个索引，该索引包含相应的已编码键值和与其关联的列值。
示例19：使用ORGANIZATION INDEX子句来创建索引组织表。
Create table ind_org_tab
(
vencode NUMBER(4) primary key, 注意：primary key是创建索引组织表所必需的。不允许使用分区。
venname VARCHAR2(20)
)
organization index;
索引组织表适合于通过主键来访问数据。
(6)基于函数的索引：如果在WHERE子句的算术表达式或函数中已经包含了某个列，则不会使用该列上的索引。不能在表达式包含任何
聚合函数，LOB列、REF列或包含LOB或REF的对象类型上创建基于函数的索引。
示例20：venname是vendor_master表的一个列，用于存储供应商的姓名，假定所有供应商的姓名都以混合大小写的形式存储
(如：John Smith、Dave Jones、Tony Greig等等)，同时假定我们经常需要根据供应商的姓名来查询表的数据。由于
姓名是以混合大小写的形式存储的，因此可能很难给出姓名的正确大小写形式。可以创建如下索引：
Create INDEX vn_ind ON vendor_master(UPPER(venname));
示例21：演示如何使用前面创建的基于函数的索引检索数据。
select * from vendor_master where UPPER(venname)='SMALL';
要创建基于函数或表达式的索引，必须具有QUERY REWRITE系统权限。
(7)索引中的分区：与对表进行分区类似，Oracle也允许对索引分区。牵引分区可以存储在不同的表空间中。
局部分区索引：Oracle为表的每个分区建立一个独立的索引。
示例22：先创建分区表
Create table order_mast
(
orderno number(4),
venname varchar2(20)
)
partition by range(orderno)
(
partition oe1 values less than(1000),
partition oe2 values less than(2000),
partition oe3 values less than(maxvalue)
);
接着创建局部索引：
create INDEX myind ON order_mast(orderno) LOCAL;
全局分区索引：是指在分区表或非分区表上创建的索引。
示例23：在上面创建的分区表上创建全局索引
create INDEX glb_ind ON order_mast(orderno) GLOBAL
partition by range(orderno)
(
partition ip1 values less than(1500),
partition ip2 values less than(maxvalue)
);
在有3个分区的表上创建2个分区的索引。注意：不能在散列分区或子分区建立全局索引。
全局非分区索引：全局分区索引是在分区表上创建的全局索引，它类似于非分区表上的索引，索引的结构不会被分割
五、使用PL/SQL
构成PL/SQL程序的基本单元是逻辑块(如过程、函数或匿名块)。
PL/SQL共分为3个部分，具体如下：
声明部分：声明块中使用的变量、游标和自定义异常。作用域仅限于它们所在的块。局部子程序也可以在PL/SQL块的声明部分中声明。此部分为可选项。
可执行部分：执行命令并操作在声明部分声明的变量和游标。PL/SQL块的可执行部分可以嵌套子块，此部分为必选项。
异常处理部分：处理执行块时引发的异常。PL/SQL块的异常处理部分也可以嵌套子块。此部分为可选。
PL/SQL语法如下：
[DECLARE
declarations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END;
其中：declarations指声明部分。
executable statements指可执行语句。
exception handlers指处理错误的代码块。
PL/SQL中的一些复合符号的含义如下：
:= 赋值操作符
|| 连接操作符
-- 单行注释
/*,*/ 多行注释
<<,>> 标签分隔符
.. 范围操作符
** 求幂操作符
1、声明变量语法如下：
variable_name data_type[(size)] [:=init_value];
其中：variable_name表示变量的名称。
data_type表示变量的SQL或PL/SQL数据类型。
size指定变量的范围。
init_value指定变量的初始值。
示例1：给变量赋值(两种方法)
declare
p_catg varchar2(20);
rate number(7,2);
rate_incr number(7,2);
begin
...
rate_incr:=1.10; --第一种
select p_category,itemrate*rate_incr
into p_catg,rate --第二种
from itemfile where itemcode='i201';
...
end;
2、声明常量语法如下：
variable_name CONSTANT data_type(size) :=value;
示例2：声明常量
set serveroutput on;
declare
total constant number:=2000;
begin
dbms_output.put_line(total);
end;
3、属性类型
%type：引用某个变量或数据库列的数据类型来声明变量。
v_empno emp.empno%type; 此代码声明了变量v_empno，它的数据类型与表emp中的empno列数据类型相同。
赋值方法有2种见示例1。
%rowtype：提供表示表中一行的记录类型。记录类型可以存储从表中选择或由游标提取的整行数据。
emp_rec emp%rowtype;
示例3：给%rowtype类型的引用变量赋值(变量名.引用表的列名)
emp_rec.empno=1234;
emp_rec.ename='andy';
emp_rec.job='Manager';
...
4、条件控制语句
IF...THEN语句：
IF 条件 THEN
执行语句;
END IF;
IF...THEN...ELSE语句：
IF 条件 THEN
执行语句1;
ELSE
执行语句2;
END IF;
IF...THEN...ELSIF语句：
IF 条件1 THEN
执行语句1;
ELSIF 条件2 THEN
执行语句2;
ELSE
执行语句3;
END IF;
CASE语句：
示例4：grade就是选择器。系统先计算选择器的值，然后匹配when子句的值输出相应的值，如果没有一个匹配，则执行else语句输出的值。
CASE '&grade'
when 'A' then dbms_output.put_line('优异');
when 'B' then dbms_output.put_line('优秀');
when 'C' then dbms_output.put_line('良好');
when 'D' then dbms_output.put_line('一般');
when 'E' then dbms_output.put_line('较差');
else dbms_output.put_line('没有此成绩');
4、循环控制
循环控制包括Loop和Exit语句，使用Exit语句可以立即退出循环，使用Exit When语句可以根据条件结束循环。
Loop循环：无条件循环，此循环中必须使用Exit或Exit When语句以免陷入无限循环。
示例5：要求用户输入学生所得分数。如果分数大于60，代码将显示消息"该生已通过"。
set serveroutput on;
begin
LOOP
if &marks>60 then
dbms_output.put_line('该生已通过');
exit;
end if;
END LOOP;
end;
While循环：此循环根据条件执行语句
示例6：用户输入一个数，如果大于0，就循环打印信息，直到这个数小于或等于0。
set serveroutput on;
declare
num number:=#
i number:=1;
begin
WHILE num>0
LOOP
dbms_output.put_line(i||'、===============^__^===============');
num:=num-1;
i:=i+1;
END LOOP;
end;
For循环：在执行语句前，For循环中的循环次数是已知的。
示例7：循环打印1到100之间的偶数。
set serveroutput on;
begin
FOR anan IN 1..100
LOOP
if anan mod 2=0 then
dbms_output.put_line(anan);
end if;
END LOOP;
end;
5、顺序控制
Goto语句：无条件地将控制权转到标签指定的语句。
示例8：循环1到100，如果num大于20就转到PRO语句来打印相应消息。
begin
for num in 1..100
loop
if num>20 then
GOTO PRO;
end if;
end loop;
<<PRO>>
dbms_output.put_line('GOTO语句用法');
end;
6、动态SQL语法如下：
EXECUTE IMMEDIATE dynamic_sql_string [INTO define_variable_list] [USING bind_argument_list];
其中：dynamic_sql_string是动态SQL语句字符串。
INTO子句用于接受SELECT语句选择记录的记录值。
USING子句用于绑定输入参数变量。
示例9：执行一条带参数的Select语句。
set serveroutput on;
declare
sql_stmt varchar2(200);
emp_id number(4):=7566;
emp_rec emp%rowtype;
begin
sql_stmt:='select * from emp where empno=:id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
dbms_output.put_line('职员编号：'||emp_rec.empno||' 职员工资：'||emp_rec.sal);
end;
注意：EXECUTE IMMEDIATE语句只能用于处理返回单行或没有返回的SQL语句，要处理多行的动态SQL请使用REF游标的OPEN...FOR语句。
7、异常处理
预定义异常：
access_into_null 在未初始化对象时出现
case_not_found 在CASE语句中的选项与用户输入数据不匹配时出现
collection_is_null 在给尚未初始化的表或数组赋值时出现
cursor_already_open 在用户试图重新打开已经打开的游标时出现。在重新打开游标前必须先将其关闭
dup_val_on_index 在用户试图将重复的值存储在使用唯一索引的数据库列中时出现
invalid_cursor 在执行非法游标运算(如打开一个尚未打开的游标)时出现
invalid_number 在将字符串转换为数字时出现
login_denied 在输入的用户名或密码无效时出现
no_data_found 在表中不存在请求的行时出现。
storage_error 在内存损坏或PL/SQL耗尽内存时出现
too_many_rows 在执行Select into语句后返回多行时出现
value_error 在产生大小限制错误时出现。例如，变量中的列值超出变量的大小
zero_divide 以零作除数时出现
示例10：预定义异常处理
set serveroutput on;
declare
name varchar2(5);
begin
select ename into name from emp;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line('返回多行');
end;
用户定义异常：
示例11：接受用户输入的数字，如果数字在1到100之间，就打印消息，否则将引发my_exception异常。
set serveroutput on;
declare
num number:=#
my_exception EXCEPTION;
begin
if num not between 1 and 100 then
raise my_exception;
else
dbms_output.put_line('输入的值界于1－100之间');
end if;
EXCEPTION
WHEN my_exception THEN
dbms_output.put_line('输入的值超出界限');
end;
引发应用程序错误：
示例12：接受用户输入的数字，如果数字在1到100之间，就打印消息，否则将引发应用程序错误。
set serveroutput on;
declare
num number:=#
my_exception EXCEPTION;
begin
if num not between 1 and 100 then
raise my_exception;
else
dbms_output.put_line('输入的值界于1－100之间');
end if;
EXCEPTION
WHEN my_exception THEN
RAISE_APPLICATION_ERROR(-20001,'输入的值超出界限'); 注意：错误号必须介于-20000和-20999之间的负整数。
end;
六、游标管理
Oracle中提供了两种游标类型，它们是：
静态游标：静态游标是在编译时知道Select语句的游标。静态游标又分为两种类型，隐式游标和显式游标。
REF游标：很多时候用户需要为游标使用的查询直到运行的时候才能确定，可以使用REF游标(引用游标)和游标变量满足这个要求。为了使用引用
游标，必须声明游标变量。有两种类型的REF游标，强类型REF游标和弱类型REF游标。
1、隐式游标：PL/SQL为所有SQL数据操纵语句(包括返回一行的Select)隐式声明游标。用户不能直接命名和控制此类游标。隐式游标名为SQL。
隐式游标属性：
(1)、%FOUND 只有在DML语句影响一行或多行时，%FOUND属性才返回TRUE。
示例1：如果有行被更新，SQL%FOUND就返回真并打印相应信息。
begin
update emp set sal=sal+8 where empno=7900;
if sql%found then
dbms_output.put_line('表已更新');
else
dbms_output.put_line('编号未找到');
end if;
end;
(2)、%NOTFOUND 此属性与%FOUND属性的作用正好相反。如果DML语句没有影响任何行，则%NOTFOUND属性返回TRUE。
(3)、%ROWCOUNT 此属性返回DML语句影响的行数。如果DML语句没有影响任何行，则%ROWCOUNT属性将返回0。
示例2：使用%ROWCOUNT属性。
begin
update emp set sal=sal+8 where empno=7900;
if sql%found then
dbms_output.put_line('表已更新,更新了'||SQL%ROWCOUNT||'行');
else
dbms_output.put_line('编号未找到');
end if;
end;
(4)、%ISOPEN 此属性返回游标是否已打开的值。隐式游标的%ISOPEN 属性始终为FALSE。
2、显式游标
使用显式游标的4个步骤是：
(1)、声明游标。
(2)、打开游标。
(3)、从游标中获取记录
(4)、关闭游标
声明显式游标语法：
CURSOR cursor_name [(parameter [,parameter]...)]
[RETURN return_type] IS select_statement;
其中：cursor_name指游标的名称。
parameter用于为游标指定输入参数。
return_type定义游标提取的行的类型。
select_statement指游标定义的查询语句。
打开游标语法：
OPEN cursor_name [(parameters)];
从游标中获取记录语法：
FETCH cursor_name INTO variables;
其中：cursor_name指游标的名称。
variables是变量名。
关闭游标语法：
CLOSE cursor_name;
显式游标的属性如下：
(1)、%FOUND 如果执行最后一条FETCH语句成功返回行，则%FOUND的值为TRUE。
(2)、%NOTFOUND 如果执行最后一条FETCH语句未能提取行时，则%NOTFOUND的值为TRUE。
(3)、%ROWCOUNT 返回到目前为止游标提取的行数。在第一次获取之前，%ROWCOUNT为零。当FETCH语句返回一行时，则该数加1。
(4)、%ISOPEN 如果游标已经打开，则返回TRUE，否则返回FALSE。
示例3：在声明部分声明cur_emp游标，并带一个输入参数，然后在BEGIN部分打开此游标。Loop部分循环执行直到游标中的所有行提取完成，
显示所有员工工资大于输入参数值的员工信息。最后关闭游标。
declare
CURSOR cur_emp(c_sal in number) IS
select * from emp where sal>c_sal;
emp_rec emp%rowtype;
e_sal emp.sal%type:=&工资;
begin
OPEN cur_emp(e_sal);
dbms_output.put_line('工资高于'||e_sal||'的员工清单如下：');
loop
FETCH cur_emp into emp_rec;
exit when cur_emp%notfound;
dbms_output.put_line('员工编号：'||emp_rec.empno||
' 员工姓名：'||emp_rec.ename||
' 员工工资：'||emp_rec.sal);
end loop;
CLOSE cur_emp;
end;
使用显式游标删除或更新
使用游标时，如果处理过程中需要删除或更新行，在定义游标时必须使用Select...For Update语句，
而在执行Delete和Update时使用Where Current Of子句指定游标的当前行。
声明更新游标的语法：
CURSOR cursor_name IS select_statement FOR UPDATE [OF columns];
在使用FOR UPDATE子句声明游标之后，可以使用以下语法更新行。
UPDATE table_name SET column_name=column_value WHERE CURRENT OF cursor_name;
注意：在声明游标时，Update命令中使用的列也必须出现在FOR UPDATE OF子句中。
声明游标时，Select语句必须只包括一个表，而且Delete和Update语句只有在打开游标并提取特定行之后才能使用。
示例4：演示如何使用显式游标更新行
declare
new_sal number;
CURSOR cur_emp IS
select sal from emp where sal<1000 FOR UPDATE OF sal;
begin
open cur_emp;
loop
fetch cur_emp into new_sal;
exit when cur_emp%notfound;
update emp set sal=sal+(1000-sal) where current of cur_emp;
end loop;
close cur_emp;
commit;
end;
3、循环游标
可以使用循环游标简化显式游标的处理代码。循环游标隐式打开游标，自动从活动集获取行，然后在处理完所有行时关闭游标。
循环游标自动创建%ROWTYPE类型的变量并将此变量用作记录索引。
语法如下：
FOR record_index IN cursor_name[(parameters)]
LOOP
executable_statements
END LOOP;
其中：record_index是PL/SQL声明的记录变量，此变量的属性声明为%ROWTYPE类型，作用域在FOR循环之内。
parameters是用于为游标指定输入参数。
循环游标的特性有：
(1)、在从游标中提取了所有记录之后自动终止。
(2)、提取和处理游标中的每一条记录。
(3)、如果在提取记录之后%NOTFOUND属性返回TRUE，则终止循环。如果未返回行，则不进入循环。
示例5：此示例声明了cur_emp游标，emp_rec是记录索引。在处理完游标中的所有记录之后，循环游标将终止。
declare
CURSOR cur_emp IS
select empno,ename,sal from emp;
begin
FOR emp_rec IN cur_emp
loop
dbms_output.put_line('员工编号：'||emp_rec.empno||
' 员工姓名：'||emp_rec.ename||
' 员工工资：'||emp_rec.sal);
end loop;
end;
4、REF游标
如果用户需要在运行的时候动态决定执行何种查询，可以使用REF游标和游标变量。
用于声明REF CURSOR类型的语法为：
TYPE ref_cursor_name IS REF CURSOR
[RETURN record_type];
其中：RETURN语句为可选子句，用于指定游标提取结果集的返回类型。包括RETURN语句表示是强类型REF游标，
否则是弱类型REF游标，可以获取任何结果集。
用于打开REF游标的语法如下：
OPEN cursor_name FOR select_statement;
用于提取和关闭游标变量的语法与显式游标相似。
示例6：此示例ref_cur_emp是一个弱类型REF游标类型，rec是自定义记录类型，refcur是游标变量，程序根据用户的输入显示相应的信息。
declare
TYPE ref_cur_emp IS REF CURSOR;
refcur ref_cur_emp;
TYPE rec IS RECORD
(
id emp.empno%type,
name emp.ename%type,
salary emp.sal%type
);
rec_emp rec;
begin
if &工资>2000 then
OPEN refcur FOR select empno,ename,sal from emp where sal>2000;
else
OPEN refcur FOR select empno,ename,sal from emp where sal<=2000;
end if;
loop
fetch refcur into rec_emp;
exit when refcur%notfound;
dbms_output.put_line('员工编号：'||rec_emp.id||
' 员工姓名：'||rec_emp.name||
' 员工工资：'||rec_emp.salary);
end loop;
close refcur;
end;
实现动态SQL的REF游标声明方法与普通的REF游标相同，只是在OPEN时指定了动态SQL字符串。
打开动态SQL的REF游标的语法如下：
OPEN cursor_name FOR dynamic_select_string [USING bind_argument_list];
示例7：演示动态SQL的用法。(返回结果集的动态SQL)
declare
r_emp emp%rowtype;
TYPE cur_type IS REF CURSOR;
cur cur_type;
p_salary number:=2500;
begin
OPEN cur FOR 'select * from emp where sal>:1 order by sal desc'
USING 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.sal);
end loop;
close cur;
end;
七、子程序和程序包
总结：
子程序是命名的PL/SQL块，可带参数并可在需要时随时调用。
PL/SQL有两种类型的子程序，即过程和函数。
过程用于执行特定的任务，函数用于执行任务并返回值。
程序包是对相关类型、变量、常量、游标、异常、过程和函数的封装。
程序包由包规范和包主体两部分组成。
包规范是包的接口，包含公用对象及其类型。
包主体实现包规范中的游标和子程序，包主体中的声明仅限于在包内使用。
程序包中游标的定义分为游标规范和游标主体两部分。
语法及示例：
1、存储过程
创建存储过程的语法：
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_list)]
{IS|AS}
[local_declarations]
BEGIN
executable_statements
[EXCEPTION
exception_handlers]
END [procedure_name];
其中：procedure_name是过程的名称。
parameter_list是参数列表。
local_declarations是局部声明。
executable_statements是可执行语句。
exception_handlers是异常处理程序。
示例1：演示创建过程(参数列表中为IN参数赋予一个默认值，不能为OUT、IN OUT参数赋予默认值)
create or replace procedure find_emp(emp_no in number:=7900)
as
empname varchar2(20);
begin
select ename into empname from emp where empno=emp_no;
dbms_output.put_line('雇员姓名是 '||empname);
exception
when no_data_found then
dbms_output.put_line('雇员编号未找到');
end find_emp;
调用过程：EXECUTE procudure_name(parameters_list);
也可以在过程里面调用，直接写上procudure_name而不必写EXECUTE。
示例2：演示创建带OUT参数的过程
create or replace procedure test(value1 varchar2,value2 out number)
is
identity number;
begin
select sal into identity from emp where empno=value1;
if identity<2000 then
value2:=1000;
else
value2:=500;
end if;
end;
调用带OUT参数的过程：
declare
value2 number;
begin
test('7900',value2);
dbms_output.put_line(value2);
end;
示例3：演示创建带IN OUT参数的过程
create or replace procedure swap(p1 in out number,p2 in out number)
is
v_temp number;
begin
v_temp:=p1;
p1:=p2;
p2:=v_temp;
end;
调用带IN OUT参数的过程：
declare
num1 number:=100;
num2 number:=200;
begin
swap(num1,num2);
dbms_output.put_line('num1= '||num1);
dbms_output.put_line('num2= '||num2);
end;
示例4：将过程的执行权限授予其他用户
GRANT EXECUTE ON find_emp TO scott;
GRANT EXECUTE ON swap TO PUBLIC;
将find_emp过程的执行权限授予给用户scott，将执行swap过程的权限授予所有数据库用户。
删除过程语法：DROP PROCEDURE procudure_name;
2、函数
定义函数的语法如下：
CREATE [OR REPLACE] FUNCTION function_name
[(parameter_list)]
RETURN datatype
{IS|AS}
[local_declarations]
BEGIN
executable_statements
[EXCEPTION
exception_handlers]
END [function_name];
其中：function_name是函数的名称。
parameter_list是参数列表。
local_declarations是局部声明。
executable_statements是可执行语句。
exception_handlers是异常处理程序。
使用函数时注意：形式参数必须只使用数据库类型，不得使用PL/SQL类型。函数的返回类型也必须是数据库类型。
函数不能单独执行，只能通过SQL语句或PL/SQL程序块来调用。
示例5：演示如何创建函数
create or replace function fun_hello
return varchar2 is
begin
return '朋友，您好';
end;
调用函数：select fun_hello from dual;
函数的授权：同过和的授权一样具体请看示例4。
删除函数：DROP FUNCTION function_name
过程和函数的差异
过程 函数
作为PL/SQL语句执行 作为表达式的一部分调用
在规范中不包含RETURN子句 必须在规范中包含RETURN子句
不返回任何值 必须返回单个值
可以包含RETURN语句，但是与函数不同，它不能用于返回值 必须包含至少一条RETURN语句
3、程序包
创建包规范的语法：
CREATE [OR REPLACE] PACKAGE package_name
IS|AS
[Public type and item declarations]
[Subprogram specifications]
END [package_name];
其中：package_name是包的名称。
Public type and item declarations是声明类型、常量、变量、异常和游标等。
Subprogram specifications声明PL/SQL子程序。
示例6：演示创建程序包规范
create or replace package pack_op is
procedure pro_print_ename(id number);
procedure pro_print_sal(id number);
function fun_re_date(id number) return date;
end;
创建包主体的语法：
CREATE [OR REPLACE] PACKAGE BODY package_name
IS|AS
[Public type and item declarations]
[Subprogram bodies]
[BEGIN
Initialization_statements]
END [package_name];
其中：package_name是包的名称。
Public type and item declarations是声明类型、常量、变量、异常和游标等。
Subprogram bodies是定义公共和私有PL/SQL子程序。
示例7：演示创建程序包主体
create or replace package body pack_op is
procedure pro_print_ename(id number) is
name emp.ename%type;
begin
select ename into name from emp where empno=id;
dbms_output.put_line('职员姓名：'||name);
end pro_print_ename;
procedure pro_print_sal(id number) is
salary emp.sal%type;
begin
select sal into salary from emp where empno=id;
dbms_output.put_line('职员工资：'||salary);
end pro_print_sal;
function fun_re_date(id number) return date is
bedate emp.hiredate%type;
begin
select hiredate into bedate from emp where empno=id;
return bedate;
end fun_re_date;
end pack_op;
示例8：调用程序包中创建的过程和函数
exec pack_op.pro_print_ename(7900);
exec pack_op.pro_print_sal(7900);
select pack_op.fun_re_date(7900) from dual;
示例9：演示程序包中的游标
创建包规范
create or replace package pack_emp is
cursor cur_emp return emp%rowtype;
procedure pro_cur;
end pack_emp;
创建包主体
create or replace package body pack_emp is
cursor cur_emp return emp%rowtype is
select * from emp;
procedure pro_cur is
rec_emp emp%rowtype;
begin
open cur_emp;
loop
fetch cur_emp into rec_emp;
exit when cur_emp%notfound;
if rec_emp.sal<1000 then
dbms_output.put_line('员工工资：'||rec_emp.sal||'，需加倍努力争取提高工资');
elsif rec_emp.sal>=1000 and rec_emp.sal<2000 then
dbms_output.put_line('员工工资：'||rec_emp.sal||'，工资一般，争取搞个部门经理做做');
else
dbms_output.put_line('员工工资：'||rec_emp.sal||'，工资不错，争取搞个总经理做做');
end if;
end loop;
end pro_cur;
end pack_emp;
调用程序包中的过程以调用程序包中的游标
exec pack_emp.pro_cur;
示例10：存储过程返回游标的子程序包(此程序包返回r_cur游标)
CREATE OR REPLACE package SCOTT.pk_wt
is
type mytype is ref cursor;
procedure p_wt(mycs out mytype);
end;
CREATE OR REPLACE package BODY SCOTT.pk_wt
is
procedure p_wt(mycs out mytype)
is
r_cur mytype;
begin
open r_cur for select * from emp;
mycs:=r_cur;
end p_wt;
end pk_wt;
查询有关过程、函数和程序包的信息：USER_OBJECTS数据字典视图
column object_name format a18
select object_name,object_type from user_objects where object_type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY');
八、触发器和内置程序包
总结：
触发器是当用户执行特定操作时自动执行的存储过程。
触发器由触发器语句、触发器限制和触发器操作3个部分组成。
Oracle中的触发器分为行级触发器、语句级触发器、Instead of触发器、模式触发器和数据库级触发器5种类型。
Instead of触发器只能应用于视图。允许用户修改不能直接使用DML语句修改的视图。
内置程序包存储在数据库中，用于扩展数据库的功能。
DBMS_OUTPUT程序包允许用户从程序包、触发器和存储过程中输出信息。
DBMS_LOB程序包用于操作BLOB、CLOB和BFILE等数据类型。
模式触发器的触发事件语句有：CREATE、ALTER、DROP、GRANT、REVOKE和TRUNCATE等DDL语句。
语法及示例：
1、用于创建触发器的语法：
CREATE [or replace] TRIGGER trigger_name
{BEFORE|AFTER|INSTEAD OF}
[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];
其中：BEFORE和AFTER 指在事件发生之前或之后激活触发器。
INSTEAD OF 如果使用此子句，表示可以执行触发器代码来代替导致触发器调用的事件。
INSERT、DELETE和UPDATE 指定构成触发器事件的数据操纵类型，UPDATE还可以指定列的列表。
REFERENCING 指定新行(即将更新)和旧行(更新前)的其他名称，默认为NEW和OLD。
table_or_view_name 指要创建触发器的表或视图的名称。
FOR EACH ROW 指定是否对受影响的每行都执行触发器，即行级触发器，如里不使用此子句，则为语句级触发器。
WHEN 限制执行触发器的条件，该条件可以包括新旧数据值的检查。
DECLARE...END 是一个标准的PL/SQL块。
示例1：此救命演示在SCOTT模式下创建触发器，如果更新表EMP时，Comm的值不等于40就激活触发器，将插入或更新值设置为0。
CREATE OR REPLACE TRIGGER biu_emp_deptno
BEFORE INSERT OR UPDATE OF deptno 1
ON emp 2 此3行为触发器语句
FOR EACH ROW 3
WHEN (New.deptno<>40) 4 此行为触发器限制
BEGIN 6
:New.comm:=0; 7 此3行为触发器操作
END; 8
示例2：先创建一个测试表，接着创建一个序列，然后创建一个自动生成ID列的行级触发器，并禁止更新表的ID列。
create table test_trg(id number,name varchar2(20));
create sequence seq_test;
create or replace trigger bi_test_trg
before insert or update of id
on test_trg
for each row
begin
if inserting then
select seq_test.nextval into :new.id from dual;
else
raise_application_error(-20020,'不允许更新ID值!');
end if;
end;
示例3：为EMP表创建了语句级触发器。如果用户对表执行插入、删除或更新行的操作，将激活此触发器。
create or replace trigger aiud_emp
after insert or update or delete
on emp
begin
if updating then
DBMS_OUTPUT.PUT_LINE('EMP中的数据已更新');
elsif deleting then
DBMS_OUTPUT.PUT_LINE('EMP中的数据已删除');
elsif inserting then
DBMS_OUTPUT.PUT_LINE('数据已插入EMP');
end if;
end;
模式触发器语法：
create or replace trigger trigger_name
{before|after} trigger_event
on schema
when (trigger_condition)
trigger_body;
示例4：先创建一个存储信息的表，然后创建一个模式触发器，在删除当前模式下的对象时激活触发器。
create table table dropped_obj
(
obj_name varchar2(30),
obj_type varchar2(20),
drop_date date
);
create or replace trigger log_drop
after drop on schema
begin
insert into dropped_obj values(ora_dict_obj_name,ora_dict_obj_type,sysdate);
end log_drop;
2、启用和禁用触发器
alter trigger trigger_name {enable|disable};
3、删除触发器
drop trigger <trigger_name>;
4、使用user_triggers数据字典视图
select trigger_name,table_name,trigger_type,triggering_event from user_triggers;
5、内置程序包
dbms_output.put|put_line('msg');
dbms_random.random mod 100 返回一个8位的随机整数，该整数的正负也是随机的。
dbsm_random.string('x',10) 返回一个10位的随机ascii码字符。
chr(abs(dbms_random.value(40000,50000))); 返回一个gbk码对应的汉字，gbk码介于40000之50000之间。
示例5：演示dbms_xmlquery和dbms_lob包的用法
declare
result clob;
xmlstr varchar2(32767);
line varchar2(2000);
begin
result:=dbms_xmlquery.getxml('select empno,ename from emp');
xmlstr:=dbms_lob.substr(result,32767);
loop
exit when xmlstr is null;
line:=substr(xmlstr,1,instr(xmlstr,chr(10))-1);
dbms_output.put_line(line);
xmlstr:=substr(xmlstr,instr(xmlstr,chr(10))+1);
end loop;
end;
使用utl_file包必须先将创建目录对象与操作系统的目录关联起来。
create directory test_dir as 'C:/develop';
grant read,write on directory test_dir to scott;
九、备份与恢复简介
总结：
备份是对数据库中数据的复制，它有两种类型：物理备份和逻辑备份。
导致数据库操作中止的故障包括4种类型：语句故障、用户进程故障、实例故障、和介质故障。
Oracle提供了导入和导出实用程序来实现数据库的逻辑备份。
导出实用程序将数据库中的数据备份成一个二进制文件，即导出转储文件。导入实用程序将备份文件中的数据导入到Oracle数据库中。
导入和导出实用程序通过命令行参数、交互提示符和参数文件等方式来调用。
导入和导出实用程序均提供4种方式的操作：表方式、用户方式、表空间方式和全部数据库方式。
数据库可在两种方式下工作：非归档日志方式和归档日志方式。
1、导出实用程序将数据库中的对象定义和数据备份到一个操作系统二进制文件中，该文件称为导出转储文件(Export Dump Files)，其默认扩展名是.dmp。
导出实用程序语法：
exp username/password [PARMETER=value]...
可以使用以下命令显示导出实用程序的参数说明。
C:/> emp help=y
2、导入实用程序将导出的转储文件导入数据库。
导入实用程序语法：
imp username/password [PARMETER=value]...
可以使用以下命令显示导入实用程序的参数说明。
C:/> imp help=y
导入的方式取决于导出的方式，即如果是表方式导出，则必须以表方式导入。
示例1：查看所有有效的归档日志文件存储目录。
select dest_id,dest_name,status,destination from V$ARCHIVE_DEST where status='VALID';
后序部分：
如何删除表中的重复记录？
例句：
DELETE FROM table_name a WHERE rowid > ( SELECT min(rowid) FROM table_name b WHERE b.pk_column_1 = a.pk_column_1 and b.pk_column_2= a.pk_column_2 );
Rem =====================================================================================
Rem 1.查看所有用户：
Rem =====================================================================================
select * from dba_users;--sys和system用户有权限执行
select * from all_users;
select * from user_users;
Rem =====================================================================================
Rem 2.查看用户系统权限：
Rem =====================================================================================
select * from dba_sys_privs;--系统权限有643个
select * from all_sys_privs;
select * from user_sys_privs;
Rem =====================================================================================
Rem 3.查看用户对象权限：
Rem =====================================================================================
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
Rem =====================================================================================
Rem 4.查看所有角色：
Rem =====================================================================================
select * from dba_roles;
Rem =====================================================================================
Rem 5.查看用户所拥有的角色：
Rem =====================================================================================
select * from dba_role_privs;
select * from user_role_privs;
Rem =====================================================================================
Rem 6.查看用户有哪些权限
Rem =====================================================================================
select granted_role from user_role_privs
union
select privilege from user_sys_privs;
Rem =====================================================================================
Rem 7.查看当前用户每个表占用空间的大小：
Rem =====================================================================================
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name
Rem =====================================================================================
Rem 8.查看每个表空间占用空间的大小：
Rem =====================================================================================
Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name
一、Oracle入门
理论知识：
Oracle的物理组件有三个：
(1)数据文件 数据文件是用于存储数据库数据的文件，如表、索引数据。每个Oracle数据库有一个或多个物理数据文件，
一个数据文件只能与一个数据库关联。
(2)日志文件 用于记录对数据库进行的修改信息，日志文件主要用于在数据库出现故障时实施数据库恢复。
(3)控制文件 控制文件是记录数据库物理结构的二进制文件，每个Oracle数据库都含有一个控制文件。
Oracle的逻辑组件：
表空间(TableSpace) 表空间是数据库最大的逻辑单位，一个数据库至少包含一个表空间，一个表空间包含一个或多个段等等。
段(Segment) 段存在于表空间中，分成4类，数据段、索引段、回退段、临时段。
区(Extent) 区是磁盘空间分配最小单位，由连续的数据块组成，一个或多个区构成段，区只能存在于一个数据文件中。
数据块(Data Block) 数据块是数据库中最小的数据组织单位与管理单位，Oracle数据库中的数据存储于数据块中，取值范围2K-64K之间。
模式(schema) 模式是对用户所创建的数据库对象的总称，又称为用户模式。
概念：
内存 Oracle内存结构包含以下两个内存区。
1、系统全局区(SGA) 实例启动时分配该内存区，是Oracle实例的一个基本组件。
又称为共享全局区，它用来存储数据库信息，并由多个数据库进程共享。可分为共享池、数据缓冲区及日志缓冲区。
(1)共享池 是对SQL、PL/SQL程序进行语法分析、编译、执行的内存区域。共享池由库缓存和数据字典缓存组成。其中，库缓存含有
最近执行的SQL、PL/SQL语句的分析码和执行计划；数据字典缓存含有从数据字典中得到的表、索引、列定义和权限等信息。
(2)数据缓冲区 数据缓冲区用于存储从磁盘数据文件中读入的数据，所有用户共享。
(3)日志缓冲区 日志记录数据库的所有修改信息，主要用于恢复数据。
2、程序全局区(PGA) 服务器进程启动时分配该内存区。PGA为非共享区，只能单个进程使用，当一个用户会话结束后，PGA释放。
用户进程(PGA)发送SQL语句到共享全局区(SGA)，先在共享池的库缓存中查询是否存在所需的数据块，如果存在就在数据字典中读取相应的数据块，如
果不存在就由服务器进程(DBWR)来IO数据库
语法知识：
创建表空间的语法如下：
CREATE TABLESPACE tablespacename DATAFILE 'd:/filename.DBF' [SIZE int [KB|MB]] [AUTOEXTEND [OFF|ON]];
tablespacename 是需创建的表空间名称。
DATAFILE 指定组成表空间的一个或多个数据文件，当有多个数据文件时使用逗号分隔。
filename 是表空间中数据文件的路径和名称。
SIZE 指定文件的大小，用K指定千字节大小，用M指定兆字节大小。
AUTOEXTEND子句用来启用或禁用数据文件的自动扩展。
Oracle默认用户：
用户名：sys 默认密码：chage_on_install 用来管理拥有Oracle数据字典文件
用户名：system 默认密码：manager 用来管理拥有数据字典视图对象
用户名：scott 默认密码：tiger 示例用户，包括emp、dept等表
连接Oracle: 在控制台下输入 sqlplus 用户名/密码 回车或 sqlplusw 回车
相关命令：
disconn //退出当前登录
conn 用户名/密码 //连接Oracle
alter user 用户名 identified by 密码 //修改用户口令
drop user 用户名 cascade; //删除用户
alter user 用户名 account lock; //给某个用户加锁
alter user 用户名 account unlock; //给某个用户解锁
ed 回车: //打开缓冲区
/ 回车: //执行缓冲区中的语句
create user 用户名 identified by 密码 [password expire]
[default tablespace 表空间名] [temporary tablespace 临时表空间名]; //创建用户
相关权限：
grant connect to scott; //connect角色将允许用户创建数据库并在数据库中创建表或其他对象
grant resource to scott; //resource角色将允许用户使用数据库中的空间
grant create sequence to scott; //create sequence权限将允许用户创建序列，此权限包含在connect连接角色中
grant select on emp to scott; //将emp表的查询权限授予用户scott
grant update(vencode,venname) on 表名 to scott; //将特定列的更新权限授予用户scott
grant 权限 on 表名 to 用户名 with grant option; //接受该权限的用户可以将此权限授予其他用户
revoke select,update on 表名 from 用户名; //收回相应的权限
二、SQL查询和SQL函数
SQL支持如下类别的命令：
数据定义语言： create(创建)、alter(更改)、drop(删除)和truncate(截断)命令。
数据操纵语言： insert(插入)、select(选择)、delete(删除)和update(更新)命令。
事务控制语言： commit(提交)、savepoint(保存点)和rollback(回滚)命令。
数据控制语言： grant(授予)和revoke(回收)命令。
数据类型：
char: 长度在1到2000个字节，声明多少字节在内存中就占用多少字节，输入的值小于指定的长度时用空格填充。
varchar2: 长度在1到4000个字节，输入的值是多少字节，就占用多少字节。
long: 长度在2GB，设置为此类型的列时，要注意：一个表中只有一列可以为long类型，long类型列不能定义为唯一约束或主键约束，
不能建立索引，过程或存储过程不能接受long类型的参数。
number(p,s): 其中p为精度，表示数字的总位数，在1至38之间。s为范围，表示小数点右边数字的位数，在-84至127之间。
date: 日期类型，sysdate为当前系统时间。格式为08-9月 -07。
timestamp: 用于存储日期的年、月、日以及时间的时、分和秒。其中秒精确到小数点后6位，
systimestamp返回当前日期、时间。格式为08-9月 -07 04.08.30.000000 下午。
raw: 此数据类型用于存储基于字节的数据，如二进制数据或字节串，该类型最多能存储2000个字节，可以建立索引。
long raw: 此数据类型用于可变长度的二进制数据，最多能存储2GB。long数据类型的所有限制对long raw数据类型也同样有效。
lob又称为"大对象"数据类型，最多能存储4GB的非结构化信息。包括：
clob: clob代表Character LOB(字符LOB)，它能存储大量字符数据。如XML文档。
blob: blob代表Binary LOB(二进制LOB)，它能存储较大的二进制对象，如图形、视频剪辑和声音剪辑。
bfile: bfile代表Binary File(二进制文件)，它能够将二进制文件存储在数据库外部的操作系统文件中。
伪列：
rowid: select rowid,ename, from scott.emp where empno='7900';
rownum: select * from scott.emp where rownum<11; //限制查询返回的行数
语法知识：
desc 表名; //查看表结构
alter table 表名 modify (列名 varchar2(25)); //修改列
alter table 表名 add (列名 varchar2(12),列名 number(12)); //添加列
alter table 表名 drop column 列名; //删除列
truncate table 表名; //中删除记录而不删除结构，不使用事务处理，因此无法回滚
drop table 表名; //删除表及其全部数据
create table 新表名 as select * from 表名 where 1=2; //用现有的表创建一个新表
select deptno*2 "New No",dname,loc from dept; //指定一个含有特殊字符(如空格)的列标题
commit; //提交事务
savepoint 标记名; //标记事务点
rollback; //回滚整个事务处理
rollback to [savepoint] 标记名; //回滚到事务中某个特定的保存点
集合操作符：
union(联合): 此操作符返回两个查询选定的所有不重复的行。
语法： select orderno from order_master UNION select orderno from order_detail;
union all(联合所有): 此操作符合并两个查询选定的所有行，包括重复的行。
语法： select orderno,ename from order_master UNION ALL select orderno,proname from order_detail order by 2;
注意：在两个select语句中指定的列名不必相同，但数据类型必须匹配。也可以对联合查询的结果进行排序，使用Order By子句时，它必须放在最后
一个select语句之后，而且必须指定列索引来排序，而不是指定列名，列索引是从1开始的整数。上述语法便是以proname的索引排序
intersect(交集): 此操作符只返回两个查询都有的行。
语法： select orderno from order_master INTERSECT select orderno from order_detail;
minus(减集): 此操作符中返回由第一个查询选定但是第二个查询中没有选定的行，也就是在第一个查询结果中排除第二个查询结果中出现的行。
语法： select orderno from order_master MINUS select orderno from order_detail; 查询尚未交付的订单
连接(||)操作符：
语法： select ('供应商'||venname||'的地址是'||venadd1||' '||venadd2||' '||venadd3) 地址 from vendor_master where vencode='V002';
将多个字符串合并为一个字符串
SQL函数：
1、日期函数：
add_months: 此函数返回给指定的日期加上指定的月数后的日期值。语法为add_months(d,n),其中d是日期，n表示月数。
示例：select add_months(sysdate,2) from dual; 将当前时间加上2个月后的日期值。
months_between: 此函数返回两个日期之间的月数。语法为months_between(d1,d2),其中d1和d2是日期，如果d1大于d2，则结果为正数；否则为负数。
last_day: 此函数返回指定日期当月的最后一天的日期值，语法为last_day(d),其中d表示日期。
示例：select last_day(sysdate) from dual; 返回当前日期的月的最后一天，如果是9月就返回30-09月-07
round: 此函数返回日期值，将日期四舍五入为格式模型指定的单位。语法为round(d,[fmt])。其中d是日期，fmt是格式模型。fmt是一个可
选项，日期默认舍入为最靠近的那一天。如果指定格式为年"Year"，则舍入到年的开始，即1月1日;如果格式为月"Month"，则舍入到
月的第一日;如果格式为周"Day"，则舍入到最靠近的星期日。
示例：select round(sysdate,'month') from dual; 返回最接近的一个月。
next_day: 此函数返回指定的下一个星期几的日期。语法为next_day(d,day)。其中d表示日期，而day指周内任何一天。
示例：select next_day(sysdate,'星期日') from dual; 返回下一个星期日的日期，也可以用1表示，以此类推，星期一以2表示。
trunc: 此函数将指定日期截断为由格式模型指定的单位日期，与Round函数不同的是它只舍不入，语法为trunc(d,[fmt])，与round格式相同。
示例：select trunc(sysdate,'year') from dual; 返回当前年的第一天，也就是1月1日。
示例：select trunc(sysdate,'day') from dual; 返回紧靠前面的星期日。如果为"2005年1月27日"就会返回"2005年1月23日"。
extract: 此函数提取日期时间类型中的特定部分。语法为extract(fmt from d)，其中d是日期时间表达式，fmt是要提取的部分的格式。格式
的取值可以是year,month,day,hour,minute,second,注意此处的格式不使用单引号。
示例：select extract(year from sysdate) from dual; 返回当前的年份。
2、字符函数：
initcap(char): 首字母大写，示例：select initcap('hello') from dual; 输出结果:Hello。
lower(char): 转换为小写，示例：select lower('FUN') from dual; 输出结果:fun。
upper(char): 转换为大写，示例：select upper('sun') from dual; 输出结果:SUN。
ltrim(char,set): 左剪裁，示例：select ltrim('xyzadams','xyz') from dual; 输出结果:adams。
rtrim(char,set): 右剪裁，示例：select rtrim('xyzadams','ams') from dual; 输出结果:xyzad。
translate(char,from,to): 按字符翻译，示例：select translate('jack','abcd','1234') from dual; 输出结果:j13k。
replace(char,search_str,replace_str): 字符串替换，示例：select replace('jack and jue','j','bl') from dual; 输出结果:black and blue。
instr(char,substr[,pos1,pos2]): 查找子字串位置。
示例：select instr('vorldwide','d') from dual; 输出结果:5。pos1为可选，表示从第几个位置查找。pos2为可选，表示从第几次出现的位置找。
substr(char,pos,len): 取子字符串，示例：select substr('abcdefg',3,2) from dual; 输出结果:cd。
concat(char1,char2): 连接字符串，示例：select concat('Hello','world') from dual; 输出结果:Helloworld。
chr: 此函数根据Ascii码返回对应的字符，示例：select chr(45788),chr(53671),chr(50167),chr(65) from dual; 输出结果:曹 学 明 A。
ascii: 此函数返回GBK编码值，示例：select ascii('曹') cao ,ascii('学') xue,ascii('明') Ming from dual; 输出结果:45788 53671 50167。
lpad和rpad： 示例：select lpad('function',15,'=') from dual; 输出结果:=======function。而rpad则相反，字符串填充在右边。
trim: 此函数从字符串的开头或结尾(或开头和结尾)剪裁特定的字符，默认剪裁空格。如果加上leading选项时与ltrim函数相似。指定trailing时和 rtrim函数相似。
示例： select trim(9 from 999992598899) from dual; 输出结果:25988。
示例： select trim(leading 9 from 999992598899) from dual; 输出结果:2598899。
示例： select trim(trailing 9 from 999992598899) from dual; 输出结果:9999925988。
length: 此函数返回字符串的长度，示例：select length('frances') from dual; 输出结果:7。
decode: 示例：select deptno,dname,decode(loc,'NEW YORK','纽约','BOSTON','波士顿') from scott.dept;
此示例将替换显示loc列的结果,结果为"NEW YORK"的替换为"纽约"，"BOSTON"的替换为"波士顿"。
GREATEST/least: 返回一组表达式中的最大值/最小值,即比较字符的编码大小.
示例：select greatest('AA','AB','AC') from dual; 输出结果:AC。
select least('AA','AB','AC') from dual; 输出结果:AA。
select greatest('啊','安','天') from dual; 输出结果:天。
select least('啊','安','天') from dual; 输出结果:啊。
3、数字函数：
abs(n): 取绝对值，示例：select abs(-15) from dual; 输出结果:15。
ceil(n): 向上取整，示例：select ceil(44.778) from dual; 输出结果:45。
sign(n): 取符号，示例：select sign(-2) from dual; 输出结果:-1。
floor(n): 向下取整，示例：select floor(200.88) from dual; 输出结果:200。
power(m,n): m的n次幂，示例：select power(5,3) from dual; 输出结果:125。
mod(m,n): 取余数，示例：select mod(10,3) from dual; 输出结果:1。
round(m,n): 四舍五入，示例：select round(100.256,2) from dual; 输出结果:100.26。
trunc(m,n): 截断，示例：select trunc(100.256,2) from dual; 输出结果:100.25。
sqrt(n): 平方根，示例：select sqrt(4) from dual; 输出结果:2。
4、转换函数：
to_char(d|n[,fmt]): 其中d是日期，n是数字，fmt指定日期或数字的格式。
示例：select to_char(sysdate,'yyyy"年"fmmm"月"fmdd"日" hh24:mi:ss') from dual; 输出结果:2007年9月09日 20:44:27。
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24"时"mi"分"ss"秒"') from dual;
示例：select to_char(sal,'$99999') from emp; 输出结果:$1600。
to_date(char[,fmt]): 此函数将char或varchar2数据类型转换为日期数据类型。
示例：select to_date('2005-12-06','yyyy-mm-dd') from dual; 输出结果:06-12月-05。
select to_date('2008/08/10 20:08:08','yyyy/mm/dd hh24:mi:ss') from dual;
select to_date('2008-08-10 20:08:08','yyyy/mm/dd hh24:mi:ss') from dual;
select to_date('2008年08月10日 20时08分08秒','yyyy"年"mm"月"dd"日" hh24"时"mi"分"ss"秒"') from dual;
to_number(char): 此函数将包含数字的字符串转换为number数据类型，通常不用这么做，因为Oracle可以对数字字符串进行隐式转换。
示例：select sqrt(to_number('100')) from dual; 输出结果:10。
5、其它函数：
nvl(expression1,expression2): 如果expression1为NULL，则nvl返回expression2。
nvl2(expression1,expression2,expression3): 如果expression1不是NULL，则nvl2返回expression2，如果expression1是NULL，则返回expression3。
nullif(expr1,expr2): 此函数比较两个表达式，如果它们相等，则返回空值，否则返回expr1。
nullif函数等价于以下的case表达式：
case when expr1=expr2 then null else expr1 end
6、分组函数：
avg: 此函数返回指定列值的平均值，示例：select avg(sal) from emp; 输出结果:2073.21429。
min: 此函数返回指定列值的最小值，示例：select min(sal) from emp; 输出结果:800。
max: 此函数返回指定列值的最大值，示例：select max(sal) from emp; 输出结果:5000。
sum: 此函数返回指定列值的总和，示例：select sum(sal) from emp; 输出结果:29025。
count: 此函数是为了计算行数，它可以接受3种不同的参数
示例：select count(*) from emp; 输出结果:14。
示例：select count(列名) from 表名;
示例：select count(distinct 列名) from 表名;
group by: 此子句用于将信息表划分为组，按组进行聚合运算。select后面跟的列名只能是分组函数、group by子句中出现的列或表达式。
示例：select deptno,max(sal) from emp group by(deptno); 查出每个部门的最高工资。
示例：select deptno,count(*),sum(sal) from scott.emp group by deptno; //group by主要用来对一组数进行统计
having: 此子句用来指定group by子句的检索条件。
示例：select deptno,count(*),sum(sal) from scott.emp group by deptno having count(*)>=5; //having对分组统计再加限制条件
7、分析函数：只能出现在select列表或order by子句中。
row_number: 下面对所有员工的工资进行排名，即使工资相同，其排名也不能相同。
示例：select ename,job,deptno,sal,row_number() over(order by sal desc) as 排名 from scott.emp;
下面对所有员工的工资按部门进行排名，即使工资相同，排名也不同。
示例：select ename,job,deptno,sal,row_number() over(partition by deptno order by sal desc) as 排名 from scott.emp;
rank: 此函数计算一个值在一组值中的排位，排位是以1开头的连续整数，如果两行的序数为1，则没有序数2，下行的序数为3。
下面根据员工的工资和佣金对员工在每个部门中进行排位。相同的工资排位相同，并且排位不连续。
示例：select ename,sal,comm,deptno,rank() over(partition by deptno order by sal desc,comm) 排名 from scott.emp;
dense_rank: 此函数计算一个行在一组有序行中的排位，排位是以1开头的连续整数，具有相同值的排位相同，并且排位是连续的。
下面首先选择所有在accounting或research部门中工作的员工的部门名称、员工姓名和工资，
然后分别计算每个员工的工资在部门中的排位，相等的工次排位相同
示例：select d.dname,e.ename,e.sal,dense_rank() over(partition by e.deptno order by e.sal desc) 排名
from emp e,dept d where e.deptno=d.deptno;
三、锁和表分区
锁定是数据库用来控制共享资源并发访问的机制。
Oracle提供以确保在多用户环境下数据的完整性和一致性。
锁的两种级别：(只有在提交或回滚后才能释放锁定)
(1)行级锁：是一种排他锁，防止其他事务修改此行，但是不会阻止读取此行的操作。在使用Insert、Update、Delete和Select...For Update
等语句时，Oracle会自动应用行级锁定。
Select...For Update语法为：
Select...For Update [OF column_list] [WAIT n | NOWAIT]
其中：OF子句用于指定即将更新的列，即锁定行上的特定列。
WAIT子句指定等待其他用户释放锁的秒数，防止无限期的等待。NOWAIT为不等待。
示例1：演示如何锁定deptno值为10的所有行。
select * from dept where deptno=10 for update of dname,loc;
(2)表级锁：将保护表数据，在事务处理过程中，表级锁会限制对整个表的访问。表级锁用来限制对表执行添加、更新和删除等修改操作。
语法：
LOCK TABLE <table_name> IN <lock_mode> MODE [NOWAIT];
其中：table_name是要被锁定的表的名称。
lock_mode是锁定的模式。
表级锁的模式：
1、行共享(ROW SHARE,RS)： 允许其他用户访问和锁定该表，但是禁止排他锁锁定整个表。
2、行排他(ROW EXCLUSIVE,RX)：与行共享模式相同，同时禁止其他用户在此表上使用共享锁。使用Select...For update语句会自动应用行排他。
3、共享(SHARE,S)：共享锁将锁定表，仅允许其他用户查询表中的行，但不允许插入、更新或删除行。多个用户可以同时在同一张表中放置共享锁
即允许资源共享。但是这样极容易造成死锁。
4、共享行排他(SHARE ROW EXCLUSIVE,SRX)： 执行比共享表锁更多的限制。防止其他事务在表上应用共享锁、共享行排他锁以及排他锁。
5、排他(EXCLUSIVE,X)： 对表执行最大限制。除了允许其他用户查询该表的记录，排他锁防止其他事务对表做任何更改或在表上应用任何类型的锁。
示例2：演示如何以共享模式锁定表。
lock table dept in share mode nowait;
表分区的优点：
改善表的查询性能;表更容易管理;便于备份和恢复;提高数据安全性。
注意：要分区的表不能具有Long和Long Raw数据类型的列。
四种分区方法：
1、范围分区：根据表的某个列或一组列的值范围，决定将该数据存储在哪个分区上。
语法如下：
在Create Table语句后增加
PARTITION BY RANGE(column_name)
(
PARTITION part1 VALUE LESS THAN (range1) [TABLESPACE tbs1],
PARTITION part2 VALUE LESS THAN (range2) [TABLESPACE tbs2],
....
PARTITION partN VALUE LESS THAN (MAXVALUE) [TABLESPACE tbsN]
);
其中：column_name是以其为基础创建范围分区的列，特定行的该列值称为分区键。
part1...partN是分区的名称。
range1...MAXVALUE是分区的边界值。
tbs1...tbsN是分区所在的表空间，TABLESPACE子句是可选项。
示例3：
create table t_emp
(
empno number(4),
ename varchar2(30),
sal number
)
partition by range(empno)
(
partition e1 values less than (1000) tablespace emp1, 也可以 than (to_date('2003-01-01','yyyy-mm-dd'))
partition e2 values less than (2000) tablespace emp2, 也可以 than (to_date('2004-01-01','yyyy-mm-dd'))
partition e3 values less than (maxvalue) tablespace emp3
);
2、散列分区：语法有两种如下
PARTITION BY HASH(column_name)
PARTITIONS number_of_partitions [STORE IN (tablespace_list)];
或
PARTITION BY HASH(column_name)
(
PARTITION part1 [TABLESPACE tbs1],
PARTITION part2 [TABLESPACE tbs2],
...
PARTITION partN [TABLESPACE tbsN]
);
其中：column_name是以其为基础创建散列分区的列。
number_of_partitions是散列分区的数目，使用这种方法系统会自动生成分区的名称。
tablespace_list指定分区使用的表空间，如果分区数目比表空间的数目多，分区将会以循环的方式分配到表空间中。
part1...partN是分区的名称。
tbs1...tbsN是分区所在的表空间，TABLESPACE子句是可选项。
示例4：自动分配4个散列分区，
可以使用select partition_name,HIGH_VALUE from user_tab_partitions where table_name=upper('t_emp')查询分区名
create table t_emp
(
empno number(4),
ename varchar2(30),
sal number
)
partition by hash (empno)
partitions 4;
3、复合分区：是范围分区和散列分区的结合。在创建复合分区时，先根据范围对数据进行分区，然后在这些分区内创建散列子分区。
语法如下：
PARTITION BY RANGE(column_name1)
SUBPARTITION BY HASH(column_name2)
SUBPARTITIONS number_of_partitions [STORE IN (tablespace_list)]
(
PARTITION part1 VALUE LESS THAN (range1) [TABLESPACE tbs1],
PARTITION part2 VALUE LESS THAN (range2) [TABLESPACE tbs2],
....
PARTITION partN VALUE LESS THAN (MAXVALUE) [TABLESPACE tbsN]
);
其中：column_name1是以其为基础创建范围分区的列。
column_name2是以其为基础创建散列分区的列。
number_of_partitions是要创建的子分区的数目。
part1...partN是分区的名称。
range1...MAXVALUE是范围分区的边界值
示例5：将雇员表先按照雇佣时间hiredate进行了范围分区，然后再把每个分区分为2个子hash分区，此表一共是6个分区。
create table t_emp
(
empno number(4),
ename varchar2(30),
hiredate date
)
partition by range (hiredate)
subpartition by hash (empno)
subpartitions 2
(
partition e1 values less than (to_date('20020501','YYYYMMDD')),
partition e2 values less than (to_date('20021001','YYYYMMDD')),
partition e3 values less than (maxvalue)
);
4、列表分区：此分区允许用户明确地控制行到分区的映射。
语法如下：
PARTITION BY LIST(column_name)
(
PARTITION part1 VALUES (values_list1),
PARTITION part2 VALUES (values_list2),
....
PARTITION partN VALUES (DEFAULT)
);
其中：column_name是以其为基础创建列表分区的列。
part1...partN是分区的名称。
values_list是对应分区的分区键值的列表。
DEFAULT关键字允许存储前面的分区不能存储的记录。
示例6：
create table t_emp1
(
empno number(4),
ename varchar2(30),
location varchar2(30)
)
partition by list (location)
(
partition e1 values ('北京'),
partition e2 values ('上海','天津','重庆'),
partition e3 values ('广东','福建'),
);
要查询表分区中的数据行：select * from table_name PARTITION(p1); 注：p1是分区名。
分区维护操作：
1、添加分区：ALTER...ADD PARTITION语句用于在现有的最后一个分区之后添加新的分区。
示例7：演示如何将名为E4的新分区添加到示例3中创建的t_emp表。
ALTER TABLE t_emp ADD PARTITION E4 VALUES LESS THAN (3000);
在此请注意：上例公适用于已使用特定的键值定义了最后一个分区的表。如果要在表的开始或中间位置添加分区，或者最高分区的分区
边界是MAXVALUE，则应使用SPLIT PARTITION语句。
2、删除分区：使用ALTER TABLE...DROP PARTITION语句。
示例8：演示了如何删除t_emp表的E4分区。删除分区时，分区中的数据也随之删除。
ALTER TABLE t_emp DROP PARTITION E4;
3、截断分区：使用ALTER TABLE...TRUNCATE PARTITION语句来截断分区，只删除表分区中的所有记录。
示例9：演示了如何删除t_emp表中e3的分区的所有记录。
ALTER TABLE t_emp TRUNCATE PARTITION e3;
4、合并分区：可以将范围分区或复合分区表的两个相邻分区连接起来。结果分区将继承被合并的两个分区的较高上界。
语法如下：
ALTER TABLE table_name MERGE PARTITIONS partitions_name,partitions_name INTO PARTITION partition_name;
示例10：演示了如何将e1和e2合并成一个e2分区。
ALTER TABLE t_emp MERGE PARTITIONS e1,e2 INTO PARTITION e2;
5、拆分分区：使用SPLIT PARTITION语句在表的开头或中间添加分区。拆分分区允许用户将一个分区拆分为两个分区。
语法如下：
ALTER TABLE table_name SPLIY PARTITION partition_name AT (value) INTO (PARTITION partition1,PARTITION partition2);
示例11：演示了如何将t_emp表中的e3分区拆分为e31和e32两个分区。
ALTER TABLE t_emp SPLIT PARTITION e3 AT (Date '2005-01-01') INTO (PARTITION e31,PARTITION e32);
6、重新命名拆分后的分区:
ALTER TABLE t_emp RENAME PARTITION e31 TO P3;
ALTER TABLE t_emp RENAME PARTITION e32 TO P4;
可以查询字典视图user_tab_partitions来查看用户所创建的分区的详细信息
示例12：select table_name,partition_name,high_value from user_tab_partitions;
可以查询字典视图dba_tab_subpartitions来查看用户所创建的子分区的详细信息
示例13：select table_name,partition_name,subpartition_name from dba_tab_subpartitions;
四、数据库对象
表、视图、序列、过程、函数、程序包，甚至其它同义词都可以创建同义词。
1、同义词：私有同义词、公有同义词。
私有同义词只能被当前模式的用户访问。私有同义词名称不可与当前模式的对象名称相同。要在自身的模式创建私有同义词，
用户必须拥有Create Synonym系统权限。要在其它用户模式创建私有同义词，用户必须拥有Create Any Synonym系统权限。
公有同义词可被所有的数据库用户访问。要创建公有同义词，用户必须拥有Create Public Synonym系统权限。
创建私有同义词语法：
Create [OR REPLACE] SYNONYM [schema.]synonym_name FOR [schema.]object_name;
其中：OR REPLACE表示在同义词存在的情况下替换该同义词。
synonym_name表示要创建的同义词的名称。
object_name指定要为之创建同义词的对象的名称。
示例1：create synonym s_emp for scott.emp;
创建公有同义词语法：
Create PUBLIC SYNONYM synonym_name FOR [schema.]object_name;
示例2：create public synonym emp_syn from scott.emp;
可以查询字典视图User_Synonyms来查看用户所创建的同义词的详细信息
删除同义词语法：Drop Synonyms synonym_name; 删除公有同义词加上一个Public
此命令只删除同义词，不会删除对应的表。
2、序列：是用来生成唯一、连续的整数的数据库对象。序列通常用来自动生成主键或唯一键的值。
创建序列语法如下：
Create SEQUENCE sequence_name
[START WITH integer]
[INCREMENT BY integer]
[MAXVALUE integer|NOMAXVALUE]
[MINVALUE integer|NOMINVALUE]
[CYCLE|NOCYCLE]
[CACHE interger|NOCACHE];
其中：START WITH是指定要生成的第一个序列号。对于升序序列，其默认值为序列的最小值。对于降序序列，其默认值为序列的最大值。
INCREMENT BY是用于指定序列号之间的间隔。其默认值为1。如果integer为正值，则生成的序列将按升序排列，否则按降序排列。
MAXVALUE指定序列可以生成的最大值。
NOMAXVALUE这是默认选项，将升序序列的最大值设为10的27次幂，将降序序列的最大值设为-1。
MINVALUE指定序列的最小值。MINVALUE必须小于或等于START WITH的值，并且必须小于MAXVALUE。
NOMINVALUE这是默认选项，将升序序列的最小值设为1，将降序序列的最小值设为-10的26次幂。
CYCLE指定序列在达到最大值或最小值后，将继续从头开始生成值。
NOCYCLE这是默认选项。指定序列在达到最大值或最小值后，将不能再继续生成值。
CACHE使用CACHE选项可以预先分配一组序列号，并将其保留在内存中，这样可以更快的访问序列号。
NOCACHE此项则不会为加快速度而预先分配序列号。如果在创建序列时忽略了CACHE和NOCACHE选项，Oracle将默认缓存20个序列号。
示例3：Create SEQUENCE toys_seq
START WITH 10
INCREMENT BY 2
MAXVALUE 2000
MINVALUE 10
NOCYCLE
CACHE 30;
访问序列：可以通过CURRVAL和NEXTVAL伪列来访问该序列的值。
示例4：演示从序列toys_seq中选择值插入toys表中的toyid列。执行成功将会在该表的toyid列插入值"P10"和"P12"。
INSERT INTO toys(toyid,toyname,toyprice) values('p'||toys_seq.NEXTVAL,'TWENTY',25);
INSERT INTO toys(toyid,toyname,toyprice) values('p'||toys_seq.NEXTVAL,'MAGIC PENCIL',75);
示例5：演示如何查看序列当前值
Select toys_seq.CURRVAL from dual;
更改序列：ALTER SEQUENCE命令用于设置或删除MINVALUE或MAXVALUE、修改增量值、修改缓存中的序列号的数目。
修改序列语法如下：注意，不能修改序列的START WITH参数。在修改序列时，应注意升序序列的最小值应小于最大值。
ALTER SEQUENCE [schema.]sequence_name
[INCREMENT BY integer]
[MAXVALUE integer|NOMAXVALUE]
[MINVALUE integer|NOMINVALUE]
[CYCLE|NOCYCLE]
[CACHE interger|NOCACHE];
示例6：演示如何设置一个新的MAXVALUE，并为toys_seq序列打开了CYCLE。
ALTER SEQUENCE toys_seq
MAXVALUE 5000
CYCLE;
可以查询字典视图User_Sequences来查看用户所创建的序列的详细信息
删除序列语法：Drop SEQUENCE toys_seq;
3、视图
视图是存储的查询定义。
创建视图的语法如下：
Create [OR REPLACE] [FORCE | NOFORCE] VIEW view_name[(alias,alias,...)] AS
select_statement [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY];
其中：OR REPLACE表示在该视图存在的话，将重新创建该视图。
FORCE使用此关键字，则无论基表是否存在，都将创建视图。
NOFORCE这是默认值。如果使用此关键字，则仅当基表存在时才创建视图。
view_name表示要创建视图的名称。
alias指定在视图里面列的名字，名字数目必须与视图所选择的表达式的数目相匹配。
select_statement表示Select语句。
WITH CHECK OPTION此选项指定只能插入或更新视图可以访问的行。术语constraint表示为CHECK OPTION约束指定的名称。
WITH READ ONLY此选项确保不能在此视图上执行任何修改操作。
示例7：演示创建一个名为ven_view的视图，该视图与vendor_master表具有相同的结构。
Create VIEW ven_view AS select * from vendor_master;
视图中的ORDER BY子句：以便在查询视图时即使不使用Order By子句，结果集也会按指定的顺序排列行。
示例8：Create OR REPLACE VIEW ven_view(编号,日期) AS select orderno,odate from vendor_master order by venname;
创建带有错误的视图：使用FORCE选项
在以下情况下，Oracle也会创建视图：
视力定义的查询引用了一个不存在的表;视图定义的查询引用了现有表中无效的列;视图的所有者没有所需的权限。
示例9：下面创建一个基于venmast的视图。但数据库中并不存在名为"venmast"的表。
Create FORCE VIEW ven AS select * from venmast;
如果稍后创建名为venmast的表，可以使用ALTER VIEW ven COMPILE;
联接视图：
示例：演示如何创建联接视图
Create OR REPLACE VIEW ven_ord_view AS select vm.vencode,venname,orderno,odate,ostatus
from vendor_master vm,order_master om where vm.vencode=om.vencode;
键保留表：在联接视图中，如果视图包含了一个表的主键，并且也是这个视图的主键，则这个键被保留，则这个表被称为键保留表。
ven_ord_view视图以vendor_master和order_master这两个表为基表。order_master表被视为键保留表，因为orderno既是order_master表
的主键，也是视图的主键。而vendor_master表不被视为键保留表，因为vendor_master表的主键vencode是联接字段，不是视图的主键。
示例10：此示例成功修改了记录，因为odate列属于键保留表order_master。对于非键保留表vendor_master的venname列的更新则是不允许的。
update wen_ord_view odate=odate+1 where vencode='V003'; 注意：运行此示例时，必须确保基表的主键已正确创建。
可以查询字典视图User_Updateble_Columns来查看联接视图中可更新的列。
可以查询字典视图User_Views来查看用户所创建的视图信息。
视图中的函数：视图可以使用单行函数(由数字、字符、日期组成)、分组函数和表达式。
示例11：此示例用UPPER函数创建视图，注意使用函数或表达式时，应赋予列一个别名。
Create VIEW vendor_master_view AS select vencode,UPPER(venname) vendor_name from vendor_master;
删除视图语法：Drop VIEW view_name ;
4、索引：
查询User_indexes可以获取有关用户已创建的索引的详细信息。
查询User_ind_partitions可以获取有关用户已创建的分区索引的详细信息。
查询User_ind_columns可以获取有关列(用户的索引是基于这些列创建的)的详细信息。
唯一索引、组合索引、反向键索引、位图索引和基于函数的索引。
索引在逻辑上物理上都独立于关联表中的数据，在任何时候都可以创建或删除索引，而不会影响基表或其它索引。
创建普通索引的语法：
Create INDEX index_name ON table_name(column_list) [TABLESPACE tablespace_name];
其中：index_name指所创建索引的名称。
table_name表示为之创建索引的表名。
column_list是在其上创建索引的列名列表，可以基于多列创建索引。
tablespace_name为索引指定表空间。
示例12：演示如何在itemfile表的itemcode列上创建索引
create index item_index on itemfile(itemcode);
ALTER INDEX语句的REBUILD选项可以用来重建现有的索引。该选项提供的性能要优于使用DROP INDEX和CREATE INDEX语句重新创建索引。
示例13：重建索引
ALTER INDEX item_index REBUILD;
删除索引语法：Drop INDEX item_index;
(1)唯一索引：此索引可以确保在定义索引的列中，表的任意两行的值都不相同。Oracle自动为表的主键列创建唯一索引。
可以使用Create UNIQUE INDEX命令明确地创建唯一索引。
示例14：在itemfile表的itemcode列上创建了一个名为item_index的唯一索引。
Create UNIQUE INDEX item_index ON itemfile(itemcode);
(2)组合索引：组合索引是在表中的多个列上创建的索引。组合索引中列的顺序是任意的，不必是表中相邻的列。
创建组合索引时，应注意定义中使用的列的顺序。通常，最频繁访问的列应放置在列表的最前面。
示例15：在itemfile表上创建了一个名为comp_index的组合索引，当查询该表的的WHERE子句同时包含这两个列或只包含
p_category列时，以下示例语句创建的索引将用于检索数据，但如果单独使用itemrate列，则索引不能用于检索数据。
Create INDEX comp_index ON itemfile(p_category,itemrate);
(3)反向键索引：通常建立在一些值连续增长的列上，例如列中的值是是由序列产生的情况。
示例16：在itemfile表上创建了一个名为rev_index的反向键索引。注意使用REVERSE关键字。
Create INDEX rev_index ON itemfile(itemcode) REVERSE;
示例17：使用关键字NOREVERSE可以将反向键索引重建为标准索引。
ALTER INDEX rev_index REBUILD NOREVERSE;
注意：不能将标准索引重建为反向键索引。
(4)位图索引：如果某个列的值重复超过一百次，则可以考虑在该列上创建位图索引。
示例18：itemcode是order_detail表中的低基数列，因为货物编码在大多数订单中都是重复的，因此适合在该列上创建位图索引。
Create BITMAP INDEX bit_ind1 ON order_detail(itemcode);
位图索引不应当用在频繁发生的INSERT,UPDATE,DELETE操作的表上。位图索引最适合于数据仓库和决策支持系统。
(5)索引组织表：索引组织表与在一个或多个列上建立索引的普通表相似，但它无需为表和索引维护两个单独的存储空间，
数据库系统仅维护一个索引，该索引包含相应的已编码键值和与其关联的列值。
示例19：使用ORGANIZATION INDEX子句来创建索引组织表。
Create table ind_org_tab
(
vencode NUMBER(4) primary key, 注意：primary key是创建索引组织表所必需的。不允许使用分区。
venname VARCHAR2(20)
)
organization index;
索引组织表适合于通过主键来访问数据。
(6)基于函数的索引：如果在WHERE子句的算术表达式或函数中已经包含了某个列，则不会使用该列上的索引。不能在表达式包含任何
聚合函数，LOB列、REF列或包含LOB或REF的对象类型上创建基于函数的索引。
示例20：venname是vendor_master表的一个列，用于存储供应商的姓名，假定所有供应商的姓名都以混合大小写的形式存储
(如：John Smith、Dave Jones、Tony Greig等等)，同时假定我们经常需要根据供应商的姓名来查询表的数据。由于
姓名是以混合大小写的形式存储的，因此可能很难给出姓名的正确大小写形式。可以创建如下索引：
Create INDEX vn_ind ON vendor_master(UPPER(venname));
示例21：演示如何使用前面创建的基于函数的索引检索数据。
select * from vendor_master where UPPER(venname)='SMALL';
要创建基于函数或表达式的索引，必须具有QUERY REWRITE系统权限。
(7)索引中的分区：与对表进行分区类似，Oracle也允许对索引分区。牵引分区可以存储在不同的表空间中。
局部分区索引：Oracle为表的每个分区建立一个独立的索引。
示例22：先创建分区表
Create table order_mast
(
orderno number(4),
venname varchar2(20)
)
partition by range(orderno)
(
partition oe1 values less than(1000),
partition oe2 values less than(2000),
partition oe3 values less than(maxvalue)
);
接着创建局部索引：
create INDEX myind ON order_mast(orderno) LOCAL;
全局分区索引：是指在分区表或非分区表上创建的索引。
示例23：在上面创建的分区表上创建全局索引
create INDEX glb_ind ON order_mast(orderno) GLOBAL
partition by range(orderno)
(
partition ip1 values less than(1500),
partition ip2 values less than(maxvalue)
);
在有3个分区的表上创建2个分区的索引。注意：不能在散列分区或子分区建立全局索引。
全局非分区索引：全局分区索引是在分区表上创建的全局索引，它类似于非分区表上的索引，索引的结构不会被分割
五、使用PL/SQL
构成PL/SQL程序的基本单元是逻辑块(如过程、函数或匿名块)。
PL/SQL共分为3个部分，具体如下：
声明部分：声明块中使用的变量、游标和自定义异常。作用域仅限于它们所在的块。局部子程序也可以在PL/SQL块的声明部分中声明。此部分为可选项。
可执行部分：执行命令并操作在声明部分声明的变量和游标。PL/SQL块的可执行部分可以嵌套子块，此部分为必选项。
异常处理部分：处理执行块时引发的异常。PL/SQL块的异常处理部分也可以嵌套子块。此部分为可选。
PL/SQL语法如下：
[DECLARE
declarations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END;
其中：declarations指声明部分。
executable statements指可执行语句。
exception handlers指处理错误的代码块。
PL/SQL中的一些复合符号的含义如下：
:= 赋值操作符
|| 连接操作符
-- 单行注释
/*,*/ 多行注释
<<,>> 标签分隔符
.. 范围操作符
** 求幂操作符
1、声明变量语法如下：
variable_name data_type[(size)] [:=init_value];
其中：variable_name表示变量的名称。
data_type表示变量的SQL或PL/SQL数据类型。
size指定变量的范围。
init_value指定变量的初始值。
示例1：给变量赋值(两种方法)
declare
p_catg varchar2(20);
rate number(7,2);
rate_incr number(7,2);
begin
...
rate_incr:=1.10; --第一种
select p_category,itemrate*rate_incr
into p_catg,rate --第二种
from itemfile where itemcode='i201';
...
end;
2、声明常量语法如下：
variable_name CONSTANT data_type(size) :=value;
示例2：声明常量
set serveroutput on;
declare
total constant number:=2000;
begin
dbms_output.put_line(total);
end;
3、属性类型
%type：引用某个变量或数据库列的数据类型来声明变量。
v_empno emp.empno%type; 此代码声明了变量v_empno，它的数据类型与表emp中的empno列数据类型相同。
赋值方法有2种见示例1。
%rowtype：提供表示表中一行的记录类型。记录类型可以存储从表中选择或由游标提取的整行数据。
emp_rec emp%rowtype;
示例3：给%rowtype类型的引用变量赋值(变量名.引用表的列名)
emp_rec.empno=1234;
emp_rec.ename='andy';
emp_rec.job='Manager';
...
4、条件控制语句
IF...THEN语句：
IF 条件 THEN
执行语句;
END IF;
IF...THEN...ELSE语句：
IF 条件 THEN
执行语句1;
ELSE
执行语句2;
END IF;
IF...THEN...ELSIF语句：
IF 条件1 THEN
执行语句1;
ELSIF 条件2 THEN
执行语句2;
ELSE
执行语句3;
END IF;
CASE语句：
示例4：grade就是选择器。系统先计算选择器的值，然后匹配when子句的值输出相应的值，如果没有一个匹配，则执行else语句输出的值。
CASE '&grade'
when 'A' then dbms_output.put_line('优异');
when 'B' then dbms_output.put_line('优秀');
when 'C' then dbms_output.put_line('良好');
when 'D' then dbms_output.put_line('一般');
when 'E' then dbms_output.put_line('较差');
else dbms_output.put_line('没有此成绩');
4、循环控制
循环控制包括Loop和Exit语句，使用Exit语句可以立即退出循环，使用Exit When语句可以根据条件结束循环。
Loop循环：无条件循环，此循环中必须使用Exit或Exit When语句以免陷入无限循环。
示例5：要求用户输入学生所得分数。如果分数大于60，代码将显示消息"该生已通过"。
set serveroutput on;
begin
LOOP
if &marks>60 then
dbms_output.put_line('该生已通过');
exit;
end if;
END LOOP;
end;
While循环：此循环根据条件执行语句
示例6：用户输入一个数，如果大于0，就循环打印信息，直到这个数小于或等于0。
set serveroutput on;
declare
num number:=#
i number:=1;
begin
WHILE num>0
LOOP
dbms_output.put_line(i||'、===============^__^===============');
num:=num-1;
i:=i+1;
END LOOP;
end;
For循环：在执行语句前，For循环中的循环次数是已知的。
示例7：循环打印1到100之间的偶数。
set serveroutput on;
begin
FOR anan IN 1..100
LOOP
if anan mod 2=0 then
dbms_output.put_line(anan);
end if;
END LOOP;
end;
5、顺序控制
Goto语句：无条件地将控制权转到标签指定的语句。
示例8：循环1到100，如果num大于20就转到PRO语句来打印相应消息。
begin
for num in 1..100
loop
if num>20 then
GOTO PRO;
end if;
end loop;
<<PRO>>
dbms_output.put_line('GOTO语句用法');
end;
6、动态SQL语法如下：
EXECUTE IMMEDIATE dynamic_sql_string [INTO define_variable_list] [USING bind_argument_list];
其中：dynamic_sql_string是动态SQL语句字符串。
INTO子句用于接受SELECT语句选择记录的记录值。
USING子句用于绑定输入参数变量。
示例9：执行一条带参数的Select语句。
set serveroutput on;
declare
sql_stmt varchar2(200);
emp_id number(4):=7566;
emp_rec emp%rowtype;
begin
sql_stmt:='select * from emp where empno=:id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
dbms_output.put_line('职员编号：'||emp_rec.empno||' 职员工资：'||emp_rec.sal);
end;
注意：EXECUTE IMMEDIATE语句只能用于处理返回单行或没有返回的SQL语句，要处理多行的动态SQL请使用REF游标的OPEN...FOR语句。
7、异常处理
预定义异常：
access_into_null 在未初始化对象时出现
case_not_found 在CASE语句中的选项与用户输入数据不匹配时出现
collection_is_null 在给尚未初始化的表或数组赋值时出现
cursor_already_open 在用户试图重新打开已经打开的游标时出现。在重新打开游标前必须先将其关闭
dup_val_on_index 在用户试图将重复的值存储在使用唯一索引的数据库列中时出现
invalid_cursor 在执行非法游标运算(如打开一个尚未打开的游标)时出现
invalid_number 在将字符串转换为数字时出现
login_denied 在输入的用户名或密码无效时出现
no_data_found 在表中不存在请求的行时出现。
storage_error 在内存损坏或PL/SQL耗尽内存时出现
too_many_rows 在执行Select into语句后返回多行时出现
value_error 在产生大小限制错误时出现。例如，变量中的列值超出变量的大小
zero_divide 以零作除数时出现
示例10：预定义异常处理
set serveroutput on;
declare
name varchar2(5);
begin
select ename into name from emp;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line('返回多行');
end;
用户定义异常：
示例11：接受用户输入的数字，如果数字在1到100之间，就打印消息，否则将引发my_exception异常。
set serveroutput on;
declare
num number:=#
my_exception EXCEPTION;
begin
if num not between 1 and 100 then
raise my_exception;
else
dbms_output.put_line('输入的值界于1－100之间');
end if;
EXCEPTION
WHEN my_exception THEN
dbms_output.put_line('输入的值超出界限');
end;
引发应用程序错误：
示例12：接受用户输入的数字，如果数字在1到100之间，就打印消息，否则将引发应用程序错误。
set serveroutput on;
declare
num number:=#
my_exception EXCEPTION;
begin
if num not between 1 and 100 then
raise my_exception;
else
dbms_output.put_line('输入的值界于1－100之间');
end if;
EXCEPTION
WHEN my_exception THEN
RAISE_APPLICATION_ERROR(-20001,'输入的值超出界限'); 注意：错误号必须介于-20000和-20999之间的负整数。
end;
六、游标管理
Oracle中提供了两种游标类型，它们是：
静态游标：静态游标是在编译时知道Select语句的游标。静态游标又分为两种类型，隐式游标和显式游标。
REF游标：很多时候用户需要为游标使用的查询直到运行的时候才能确定，可以使用REF游标(引用游标)和游标变量满足这个要求。为了使用引用
游标，必须声明游标变量。有两种类型的REF游标，强类型REF游标和弱类型REF游标。
1、隐式游标：PL/SQL为所有SQL数据操纵语句(包括返回一行的Select)隐式声明游标。用户不能直接命名和控制此类游标。隐式游标名为SQL。
隐式游标属性：
(1)、%FOUND 只有在DML语句影响一行或多行时，%FOUND属性才返回TRUE。
示例1：如果有行被更新，SQL%FOUND就返回真并打印相应信息。
begin
update emp set sal=sal+8 where empno=7900;
if sql%found then
dbms_output.put_line('表已更新');
else
dbms_output.put_line('编号未找到');
end if;
end;
(2)、%NOTFOUND 此属性与%FOUND属性的作用正好相反。如果DML语句没有影响任何行，则%NOTFOUND属性返回TRUE。
(3)、%ROWCOUNT 此属性返回DML语句影响的行数。如果DML语句没有影响任何行，则%ROWCOUNT属性将返回0。
示例2：使用%ROWCOUNT属性。
begin
update emp set sal=sal+8 where empno=7900;
if sql%found then
dbms_output.put_line('表已更新,更新了'||SQL%ROWCOUNT||'行');
else
dbms_output.put_line('编号未找到');
end if;
end;
(4)、%ISOPEN 此属性返回游标是否已打开的值。隐式游标的%ISOPEN 属性始终为FALSE。
2、显式游标
使用显式游标的4个步骤是：
(1)、声明游标。
(2)、打开游标。
(3)、从游标中获取记录
(4)、关闭游标
声明显式游标语法：
CURSOR cursor_name [(parameter [,parameter]...)]
[RETURN return_type] IS select_statement;
其中：cursor_name指游标的名称。
parameter用于为游标指定输入参数。
return_type定义游标提取的行的类型。
select_statement指游标定义的查询语句。
打开游标语法：
OPEN cursor_name [(parameters)];
从游标中获取记录语法：
FETCH cursor_name INTO variables;
其中：cursor_name指游标的名称。
variables是变量名。
关闭游标语法：
CLOSE cursor_name;
显式游标的属性如下：
(1)、%FOUND 如果执行最后一条FETCH语句成功返回行，则%FOUND的值为TRUE。
(2)、%NOTFOUND 如果执行最后一条FETCH语句未能提取行时，则%NOTFOUND的值为TRUE。
(3)、%ROWCOUNT 返回到目前为止游标提取的行数。在第一次获取之前，%ROWCOUNT为零。当FETCH语句返回一行时，则该数加1。
(4)、%ISOPEN 如果游标已经打开，则返回TRUE，否则返回FALSE。
示例3：在声明部分声明cur_emp游标，并带一个输入参数，然后在BEGIN部分打开此游标。Loop部分循环执行直到游标中的所有行提取完成，
显示所有员工工资大于输入参数值的员工信息。最后关闭游标。
declare
CURSOR cur_emp(c_sal in number) IS
select * from emp where sal>c_sal;
emp_rec emp%rowtype;
e_sal emp.sal%type:=&工资;
begin
OPEN cur_emp(e_sal);
dbms_output.put_line('工资高于'||e_sal||'的员工清单如下：');
loop
FETCH cur_emp into emp_rec;
exit when cur_emp%notfound;
dbms_output.put_line('员工编号：'||emp_rec.empno||
' 员工姓名：'||emp_rec.ename||
' 员工工资：'||emp_rec.sal);
end loop;
CLOSE cur_emp;
end;
使用显式游标删除或更新
使用游标时，如果处理过程中需要删除或更新行，在定义游标时必须使用Select...For Update语句，
而在执行Delete和Update时使用Where Current Of子句指定游标的当前行。
声明更新游标的语法：
CURSOR cursor_name IS select_statement FOR UPDATE [OF columns];
在使用FOR UPDATE子句声明游标之后，可以使用以下语法更新行。
UPDATE table_name SET column_name=column_value WHERE CURRENT OF cursor_name;
注意：在声明游标时，Update命令中使用的列也必须出现在FOR UPDATE OF子句中。
声明游标时，Select语句必须只包括一个表，而且Delete和Update语句只有在打开游标并提取特定行之后才能使用。
示例4：演示如何使用显式游标更新行
declare
new_sal number;
CURSOR cur_emp IS
select sal from emp where sal<1000 FOR UPDATE OF sal;
begin
open cur_emp;
loop
fetch cur_emp into new_sal;
exit when cur_emp%notfound;
update emp set sal=sal+(1000-sal) where current of cur_emp;
end loop;
close cur_emp;
commit;
end;
3、循环游标
可以使用循环游标简化显式游标的处理代码。循环游标隐式打开游标，自动从活动集获取行，然后在处理完所有行时关闭游标。
循环游标自动创建%ROWTYPE类型的变量并将此变量用作记录索引。
语法如下：
FOR record_index IN cursor_name[(parameters)]
LOOP
executable_statements
END LOOP;
其中：record_index是PL/SQL声明的记录变量，此变量的属性声明为%ROWTYPE类型，作用域在FOR循环之内。
parameters是用于为游标指定输入参数。
循环游标的特性有：
(1)、在从游标中提取了所有记录之后自动终止。
(2)、提取和处理游标中的每一条记录。
(3)、如果在提取记录之后%NOTFOUND属性返回TRUE，则终止循环。如果未返回行，则不进入循环。
示例5：此示例声明了cur_emp游标，emp_rec是记录索引。在处理完游标中的所有记录之后，循环游标将终止。
declare
CURSOR cur_emp IS
select empno,ename,sal from emp;
begin
FOR emp_rec IN cur_emp
loop
dbms_output.put_line('员工编号：'||emp_rec.empno||
' 员工姓名：'||emp_rec.ename||
' 员工工资：'||emp_rec.sal);
end loop;
end;
4、REF游标
如果用户需要在运行的时候动态决定执行何种查询，可以使用REF游标和游标变量。
用于声明REF CURSOR类型的语法为：
TYPE ref_cursor_name IS REF CURSOR
[RETURN record_type];
其中：RETURN语句为可选子句，用于指定游标提取结果集的返回类型。包括RETURN语句表示是强类型REF游标，
否则是弱类型REF游标，可以获取任何结果集。
用于打开REF游标的语法如下：
OPEN cursor_name FOR select_statement;
用于提取和关闭游标变量的语法与显式游标相似。
示例6：此示例ref_cur_emp是一个弱类型REF游标类型，rec是自定义记录类型，refcur是游标变量，程序根据用户的输入显示相应的信息。
declare
TYPE ref_cur_emp IS REF CURSOR;
refcur ref_cur_emp;
TYPE rec IS RECORD
(
id emp.empno%type,
name emp.ename%type,
salary emp.sal%type
);
rec_emp rec;
begin
if &工资>2000 then
OPEN refcur FOR select empno,ename,sal from emp where sal>2000;
else
OPEN refcur FOR select empno,ename,sal from emp where sal<=2000;
end if;
loop
fetch refcur into rec_emp;
exit when refcur%notfound;
dbms_output.put_line('员工编号：'||rec_emp.id||
' 员工姓名：'||rec_emp.name||
' 员工工资：'||rec_emp.salary);
end loop;
close refcur;
end;
实现动态SQL的REF游标声明方法与普通的REF游标相同，只是在OPEN时指定了动态SQL字符串。
打开动态SQL的REF游标的语法如下：
OPEN cursor_name FOR dynamic_select_string [USING bind_argument_list];
示例7：演示动态SQL的用法。(返回结果集的动态SQL)
declare
r_emp emp%rowtype;
TYPE cur_type IS REF CURSOR;
cur cur_type;
p_salary number:=2500;
begin
OPEN cur FOR 'select * from emp where sal>:1 order by sal desc'
USING 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.sal);
end loop;
close cur;
end;
七、子程序和程序包
总结：
子程序是命名的PL/SQL块，可带参数并可在需要时随时调用。
PL/SQL有两种类型的子程序，即过程和函数。
过程用于执行特定的任务，函数用于执行任务并返回值。
程序包是对相关类型、变量、常量、游标、异常、过程和函数的封装。
程序包由包规范和包主体两部分组成。
包规范是包的接口，包含公用对象及其类型。
包主体实现包规范中的游标和子程序，包主体中的声明仅限于在包内使用。
程序包中游标的定义分为游标规范和游标主体两部分。
语法及示例：
1、存储过程
创建存储过程的语法：
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_list)]
{IS|AS}
[local_declarations]
BEGIN
executable_statements
[EXCEPTION
exception_handlers]
END [procedure_name];
其中：procedure_name是过程的名称。
parameter_list是参数列表。
local_declarations是局部声明。
executable_statements是可执行语句。
exception_handlers是异常处理程序。
示例1：演示创建过程(参数列表中为IN参数赋予一个默认值，不能为OUT、IN OUT参数赋予默认值)
create or replace procedure find_emp(emp_no in number:=7900)
as
empname varchar2(20);
begin
select ename into empname from emp where empno=emp_no;
dbms_output.put_line('雇员姓名是 '||empname);
exception
when no_data_found then
dbms_output.put_line('雇员编号未找到');
end find_emp;
调用过程：EXECUTE procudure_name(parameters_list);
也可以在过程里面调用，直接写上procudure_name而不必写EXECUTE。
示例2：演示创建带OUT参数的过程
create or replace procedure test(value1 varchar2,value2 out number)
is
identity number;
begin
select sal into identity from emp where empno=value1;
if identity<2000 then
value2:=1000;
else
value2:=500;
end if;
end;
调用带OUT参数的过程：
declare
value2 number;
begin
test('7900',value2);
dbms_output.put_line(value2);
end;
示例3：演示创建带IN OUT参数的过程
create or replace procedure swap(p1 in out number,p2 in out number)
is
v_temp number;
begin
v_temp:=p1;
p1:=p2;
p2:=v_temp;
end;
调用带IN OUT参数的过程：
declare
num1 number:=100;
num2 number:=200;
begin
swap(num1,num2);
dbms_output.put_line('num1= '||num1);
dbms_output.put_line('num2= '||num2);
end;
示例4：将过程的执行权限授予其他用户
GRANT EXECUTE ON find_emp TO scott;
GRANT EXECUTE ON swap TO PUBLIC;
将find_emp过程的执行权限授予给用户scott，将执行swap过程的权限授予所有数据库用户。
删除过程语法：DROP PROCEDURE procudure_name;
2、函数
定义函数的语法如下：
CREATE [OR REPLACE] FUNCTION function_name
[(parameter_list)]
RETURN datatype
{IS|AS}
[local_declarations]
BEGIN
executable_statements
[EXCEPTION
exception_handlers]
END [function_name];
其中：function_name是函数的名称。
parameter_list是参数列表。
local_declarations是局部声明。
executable_statements是可执行语句。
exception_handlers是异常处理程序。
使用函数时注意：形式参数必须只使用数据库类型，不得使用PL/SQL类型。函数的返回类型也必须是数据库类型。
函数不能单独执行，只能通过SQL语句或PL/SQL程序块来调用。
示例5：演示如何创建函数
create or replace function fun_hello
return varchar2 is
begin
return '朋友，您好';
end;
调用函数：select fun_hello from dual;
函数的授权：同过和的授权一样具体请看示例4。
删除函数：DROP FUNCTION function_name
过程和函数的差异
过程 函数
作为PL/SQL语句执行 作为表达式的一部分调用
在规范中不包含RETURN子句 必须在规范中包含RETURN子句
不返回任何值 必须返回单个值
可以包含RETURN语句，但是与函数不同，它不能用于返回值 必须包含至少一条RETURN语句
3、程序包
创建包规范的语法：
CREATE [OR REPLACE] PACKAGE package_name
IS|AS
[Public type and item declarations]
[Subprogram specifications]
END [package_name];
其中：package_name是包的名称。
Public type and item declarations是声明类型、常量、变量、异常和游标等。
Subprogram specifications声明PL/SQL子程序。
示例6：演示创建程序包规范
create or replace package pack_op is
procedure pro_print_ename(id number);
procedure pro_print_sal(id number);
function fun_re_date(id number) return date;
end;
创建包主体的语法：
CREATE [OR REPLACE] PACKAGE BODY package_name
IS|AS
[Public type and item declarations]
[Subprogram bodies]
[BEGIN
Initialization_statements]
END [package_name];
其中：package_name是包的名称。
Public type and item declarations是声明类型、常量、变量、异常和游标等。
Subprogram bodies是定义公共和私有PL/SQL子程序。
示例7：演示创建程序包主体
create or replace package body pack_op is
procedure pro_print_ename(id number) is
name emp.ename%type;
begin
select ename into name from emp where empno=id;
dbms_output.put_line('职员姓名：'||name);
end pro_print_ename;
procedure pro_print_sal(id number) is
salary emp.sal%type;
begin
select sal into salary from emp where empno=id;
dbms_output.put_line('职员工资：'||salary);
end pro_print_sal;
function fun_re_date(id number) return date is
bedate emp.hiredate%type;
begin
select hiredate into bedate from emp where empno=id;
return bedate;
end fun_re_date;
end pack_op;
示例8：调用程序包中创建的过程和函数
exec pack_op.pro_print_ename(7900);
exec pack_op.pro_print_sal(7900);
select pack_op.fun_re_date(7900) from dual;
示例9：演示程序包中的游标
创建包规范
create or replace package pack_emp is
cursor cur_emp return emp%rowtype;
procedure pro_cur;
end pack_emp;
创建包主体
create or replace package body pack_emp is
cursor cur_emp return emp%rowtype is
select * from emp;
procedure pro_cur is
rec_emp emp%rowtype;
begin
open cur_emp;
loop
fetch cur_emp into rec_emp;
exit when cur_emp%notfound;
if rec_emp.sal<1000 then
dbms_output.put_line('员工工资：'||rec_emp.sal||'，需加倍努力争取提高工资');
elsif rec_emp.sal>=1000 and rec_emp.sal<2000 then
dbms_output.put_line('员工工资：'||rec_emp.sal||'，工资一般，争取搞个部门经理做做');
else
dbms_output.put_line('员工工资：'||rec_emp.sal||'，工资不错，争取搞个总经理做做');
end if;
end loop;
end pro_cur;
end pack_emp;
调用程序包中的过程以调用程序包中的游标
exec pack_emp.pro_cur;
示例10：存储过程返回游标的子程序包(此程序包返回r_cur游标)
CREATE OR REPLACE package SCOTT.pk_wt
is
type mytype is ref cursor;
procedure p_wt(mycs out mytype);
end;
CREATE OR REPLACE package BODY SCOTT.pk_wt
is
procedure p_wt(mycs out mytype)
is
r_cur mytype;
begin
open r_cur for select * from emp;
mycs:=r_cur;
end p_wt;
end pk_wt;
查询有关过程、函数和程序包的信息：USER_OBJECTS数据字典视图
column object_name format a18
select object_name,object_type from user_objects where object_type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY');
八、触发器和内置程序包
总结：
触发器是当用户执行特定操作时自动执行的存储过程。
触发器由触发器语句、触发器限制和触发器操作3个部分组成。
Oracle中的触发器分为行级触发器、语句级触发器、Instead of触发器、模式触发器和数据库级触发器5种类型。
Instead of触发器只能应用于视图。允许用户修改不能直接使用DML语句修改的视图。
内置程序包存储在数据库中，用于扩展数据库的功能。
DBMS_OUTPUT程序包允许用户从程序包、触发器和存储过程中输出信息。
DBMS_LOB程序包用于操作BLOB、CLOB和BFILE等数据类型。
模式触发器的触发事件语句有：CREATE、ALTER、DROP、GRANT、REVOKE和TRUNCATE等DDL语句。
语法及示例：
1、用于创建触发器的语法：
CREATE [or replace] TRIGGER trigger_name
{BEFORE|AFTER|INSTEAD OF}
[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];
其中：BEFORE和AFTER 指在事件发生之前或之后激活触发器。
INSTEAD OF 如果使用此子句，表示可以执行触发器代码来代替导致触发器调用的事件。
INSERT、DELETE和UPDATE 指定构成触发器事件的数据操纵类型，UPDATE还可以指定列的列表。
REFERENCING 指定新行(即将更新)和旧行(更新前)的其他名称，默认为NEW和OLD。
table_or_view_name 指要创建触发器的表或视图的名称。
FOR EACH ROW 指定是否对受影响的每行都执行触发器，即行级触发器，如里不使用此子句，则为语句级触发器。
WHEN 限制执行触发器的条件，该条件可以包括新旧数据值的检查。
DECLARE...END 是一个标准的PL/SQL块。
示例1：此救命演示在SCOTT模式下创建触发器，如果更新表EMP时，Comm的值不等于40就激活触发器，将插入或更新值设置为0。
CREATE OR REPLACE TRIGGER biu_emp_deptno
BEFORE INSERT OR UPDATE OF deptno 1
ON emp 2 此3行为触发器语句
FOR EACH ROW 3
WHEN (New.deptno<>40) 4 此行为触发器限制
BEGIN 6
:New.comm:=0; 7 此3行为触发器操作
END; 8
示例2：先创建一个测试表，接着创建一个序列，然后创建一个自动生成ID列的行级触发器，并禁止更新表的ID列。
create table test_trg(id number,name varchar2(20));
create sequence seq_test;
create or replace trigger bi_test_trg
before insert or update of id
on test_trg
for each row
begin
if inserting then
select seq_test.nextval into :new.id from dual;
else
raise_application_error(-20020,'不允许更新ID值!');
end if;
end;
示例3：为EMP表创建了语句级触发器。如果用户对表执行插入、删除或更新行的操作，将激活此触发器。
create or replace trigger aiud_emp
after insert or update or delete
on emp
begin
if updating then
DBMS_OUTPUT.PUT_LINE('EMP中的数据已更新');
elsif deleting then
DBMS_OUTPUT.PUT_LINE('EMP中的数据已删除');
elsif inserting then
DBMS_OUTPUT.PUT_LINE('数据已插入EMP');
end if;
end;
模式触发器语法：
create or replace trigger trigger_name
{before|after} trigger_event
on schema
when (trigger_condition)
trigger_body;
示例4：先创建一个存储信息的表，然后创建一个模式触发器，在删除当前模式下的对象时激活触发器。
create table table dropped_obj
(
obj_name varchar2(30),
obj_type varchar2(20),
drop_date date
);
create or replace trigger log_drop
after drop on schema
begin
insert into dropped_obj values(ora_dict_obj_name,ora_dict_obj_type,sysdate);
end log_drop;
2、启用和禁用触发器
alter trigger trigger_name {enable|disable};
3、删除触发器
drop trigger <trigger_name>;
4、使用user_triggers数据字典视图
select trigger_name,table_name,trigger_type,triggering_event from user_triggers;
5、内置程序包
dbms_output.put|put_line('msg');
dbms_random.random mod 100 返回一个8位的随机整数，该整数的正负也是随机的。
dbsm_random.string('x',10) 返回一个10位的随机ascii码字符。
chr(abs(dbms_random.value(40000,50000))); 返回一个gbk码对应的汉字，gbk码介于40000之50000之间。
示例5：演示dbms_xmlquery和dbms_lob包的用法
declare
result clob;
xmlstr varchar2(32767);
line varchar2(2000);
begin
result:=dbms_xmlquery.getxml('select empno,ename from emp');
xmlstr:=dbms_lob.substr(result,32767);
loop
exit when xmlstr is null;
line:=substr(xmlstr,1,instr(xmlstr,chr(10))-1);
dbms_output.put_line(line);
xmlstr:=substr(xmlstr,instr(xmlstr,chr(10))+1);
end loop;
end;
使用utl_file包必须先将创建目录对象与操作系统的目录关联起来。
create directory test_dir as 'C:/develop';
grant read,write on directory test_dir to scott;
九、备份与恢复简介
总结：
备份是对数据库中数据的复制，它有两种类型：物理备份和逻辑备份。
导致数据库操作中止的故障包括4种类型：语句故障、用户进程故障、实例故障、和介质故障。
Oracle提供了导入和导出实用程序来实现数据库的逻辑备份。
导出实用程序将数据库中的数据备份成一个二进制文件，即导出转储文件。导入实用程序将备份文件中的数据导入到Oracle数据库中。
导入和导出实用程序通过命令行参数、交互提示符和参数文件等方式来调用。
导入和导出实用程序均提供4种方式的操作：表方式、用户方式、表空间方式和全部数据库方式。
数据库可在两种方式下工作：非归档日志方式和归档日志方式。
1、导出实用程序将数据库中的对象定义和数据备份到一个操作系统二进制文件中，该文件称为导出转储文件(Export Dump Files)，其默认扩展名是.dmp。
导出实用程序语法：
exp username/password [PARMETER=value]...
可以使用以下命令显示导出实用程序的参数说明。
C:/> emp help=y
2、导入实用程序将导出的转储文件导入数据库。
导入实用程序语法：
imp username/password [PARMETER=value]...
可以使用以下命令显示导入实用程序的参数说明。
C:/> imp help=y
导入的方式取决于导出的方式，即如果是表方式导出，则必须以表方式导入。
示例1：查看所有有效的归档日志文件存储目录。
select dest_id,dest_name,status,destination from V$ARCHIVE_DEST where status='VALID';
后序部分：
如何删除表中的重复记录？
例句：
DELETE FROM table_name a WHERE rowid > ( SELECT min(rowid) FROM table_name b WHERE b.pk_column_1 = a.pk_column_1 and b.pk_column_2= a.pk_column_2 );
Rem =====================================================================================
Rem 1.查看所有用户：
Rem =====================================================================================
select * from dba_users;--sys和system用户有权限执行
select * from all_users;
select * from user_users;
Rem =====================================================================================
Rem 2.查看用户系统权限：
Rem =====================================================================================
select * from dba_sys_privs;--系统权限有643个
select * from all_sys_privs;
select * from user_sys_privs;
Rem =====================================================================================
Rem 3.查看用户对象权限：
Rem =====================================================================================
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
Rem =====================================================================================
Rem 4.查看所有角色：
Rem =====================================================================================
select * from dba_roles;
Rem =====================================================================================
Rem 5.查看用户所拥有的角色：
Rem =====================================================================================
select * from dba_role_privs;
select * from user_role_privs;
Rem =====================================================================================
Rem 6.查看用户有哪些权限
Rem =====================================================================================
select granted_role from user_role_privs
union
select privilege from user_sys_privs;
Rem =====================================================================================
Rem 7.查看当前用户每个表占用空间的大小：
Rem =====================================================================================
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name
Rem =====================================================================================
Rem 8.查看每个表空间占用空间的大小：
Rem =====================================================================================
Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name
5669
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
