一、内容介绍
1、基础部分
oracle基本使用
oracle用户管理
oracle表管理
2、高级部分
oracle表查询
oracle权限角色
oracle函数
pl/sql编程
oracle数据库管理
索引、约束和事务
二、数据库简介
1、目前主流数据库
微软 sql server和access
瑞典AB公司 mysql
ibm公司 db2
美国sybase公司 sybase
ibm公司 informix
美国oracle公司 oracle
2、如何选择使用何种数据库：
1、项目的规模
a、负载量多大，用户多少
b、成本
c、安全性
3、数据库划分：
小型数据库：access，foxbase（负载最小，100人以内，成本在千元内，对安全性要求不高）
中型数据库：mysql，sql server，informix （负载日访问量5000-10000，成本在万元内，具有一定安全性，比如商务网站）
大型数据库：sybase，oracle，db2 （负载可以处理海量数据，sybase<oracle<db2，安全性能很高，成本高，相对昂贵）
4、oracle认证
dba认证:数据库管理员
java开发认证
oracle网络开发认证
oca(oracle初级认证)---ocp(oralce专家认证)---ocm(oracle大师认证)
三、oracle安装
1、系统要求
操作系统最好windows2000
内存256M以上
硬盘2G以上
2、oracle用户
oracle安装会自动生成sys用户和system用户
sys用户是超级用户，具有最高权限，具有sysdba角色，具有create database权限，该用户默认的密码是manager。
system用户是管理操作员的用户，权限也很大，具有sysoper角色，没有create database权限，该用户默认的密码是change_on_install，未设置时为空。
scott用户密码是tiger。
一般来说，对数据库维护，使用system用户登录就可以。
3、oracle卸载
1>运行regedit，进入注册表，删除时一定要小心别删错了；
2>删除HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE；
3>删除HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services下以Oracle开头的键值；
4>重启计算机，然后删除硬盘上的oracle目录，如果该目录不能删除，那么把这个目录改成其他名字，重启机器后再删除。
4、创建数据库的两种方法
1>通过oracle提供的向导工具；
2>手工步骤直接创建。
四、sql常用命令
1、连接命令
1>conn 用户名/密码@网络服务名 [as sysdba/sysoper]
当用特权用户身份连接时，必须带上as sysdba或是as sysoper。
2>disc[connect]
用于断开与当前数据库的连接
3>passw[ord]
用于修改用户密码，如果要修改其他用户密码，需要用sys/system登录。
4>show user
用于显示当前登录用户
5>exit
用于断开与数据库的连接，同时会退出sql*plus。
2、文件操作命令
1>start和@
说明：运行指定sql脚本
案例：start d:\xx.sql;或者@ d:\xx.sql;
2>edit
说明：编辑指定sql脚本
案例：edit d:\xx.sql;
3>spool
说明：将sql*plus屏幕上的内容输出到指定文件中
案例：spool d:\xx.sql;并输入spool off;
3、显示和设置环境变量
用于控制输出的各种格式，如果希望永久的保存相关设置，可以修改glogin.sql脚本。
1>linesize
说明：设置显示行的宽度，默认是80个字符
案例：set linesize 90;
2>pagesize
说明：设置每页显示的行数，默认是14
案例：set pagesize 20;
五、oracle用户管理
1、创建用户
create user xiaoming identified by 123;
2、修改密码
如果给自己修改密码，可以直接使用。
password 用户名
如果为别人修改密码，则需要具有dba权限，或者拥有alter user的系统权限。
alter user 用户名 identified by 新密码;
3、删除用户
一般以dba身份删除某个用户，如果其他用户删除，则需要具有drop user权限。
drop user 用户名 【cascade】;
删除用户时，如果要删除的用户，如果已经创建了表，那么就需要在删除时带上参数cascade，用于级联删除用户及创建的表。
4、oracle权限
1>系统权限
用户对数据库的相关权限，大概一百四十多种。
2>对象权限
用户对其他用户的数据对象（表、视图、存储过程等）操作权限，大概25个，例如insert，update，select，delete，all，create index。
5、用户角色：dba，resource
1>自定义角色
2>预定义角色
创建的新用户是没有任何权限的，甚至连登陆数据库的权限都没有，需要为其指定相应的权限，给用户授权使用命令grant，回收权限使用命令revoke。
6、授权
1>为用户授权：
grant 权限 on 表名 to 用户名;
2>回收权限：
revoke 权限 on 授权用户名.表名 from 被授权用户名;
7、权限维护
授权并且允许传递授权，如果是对象权限，添则加with grant option。
grant 权限 on 表名 to 用户名 with grant option;
如果是系统权限，则添加with admin option。
grant 权限 on 表名 to 用户名 with admin option;
注意：如果用户A把表table1的select权限赋给用户B，用户B再将其赋给用户C，那么当用户A把用户B的权限删除时，用户C将不具有此权限。
8、使用profile管理用户口令
profile是口令限制，资源限制的命令集合，当建立数据库时 ，oracle会自动建立名称为default的profile。当建立用户没有指定profile选项时，oracle就会将default分配给用户。
1>账户锁定：
指定该用户登录时最多可以输入密码的次数，也可以指定用户锁定的时间（天），一般用dba身份指定该命令。
例如：
create profile lock_account limit failed_login_attempts 3 password_lock_time 2;
alter user tea profile lock_account;
2>为用户解锁
alter user tea account unlock;
3>终止口令
为了让用户定期修改密码，可以使用终止口令的指令来完成，同样需要dba身份来操作。
例如：指定用户每隔10天修改密码，宽限其为2天。
create profile myprofile limit password_life_time 30 password_grace_time 2;
alter user tea profile myprofile;
4>口令历史
如果希望用户在修改密码时，不能使用以前使用过的密码，可以使用口令历史，oracle会将口令修改的信息存放到数据字典中，当用户修改密码时，oracle会对新旧密码进行比较，发现新旧密码一致时，就提示用户重新输入密码。
例如：指定口令10天后可重用
create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10;
alter user tea profile password_history;
5>删除profile
当不需要某个profile文件时，可删除该文件。
drop profile password_history【cascade】;
六、oracle表管理
1、表明和列名的命名规则
必须以字母开头；
长度不能超过30个字符；
不能使用oracle保留字；
只能使用如下字符：A-Z，a-z，0-9，$,#等。
2、数据类型
1>字符类型
char 定长 最大2000字符 占用空间，查询速度快（各位数一起比较）
例如：char(10) '学生' 前四个字符存放'学生'，后添加6个空格补全。
varchar2(20) 变长 最大4000字符 节省空间，查询速度慢（一个一个比较）
例子：varchar2(10) '学生' oracle分配四个字符，可节省空间。
clob(character large object) 字符型大对象 最大4G
2>数据类型
number 范围-10的38次方~10的38次方，可以表示证书，也可以表示小数。
例如：
number(5,2)表示一个小数有5位有效数，2位小数，范围 -999.99~999.99。
number(5)表示一个五位整数，范围-99999~99999。
3>日期类型
date 包含年月日时分秒
timestamp 对date数据类型的扩展，精度更高
4>图片类型
blob 二进制数据 可存放图片、声音 最大4G
出于安全考虑时，可以直接将音视频文件存放在数据库中。
3、建表
create table student(
xh number(4),
xm varchar2(20),
sex char(2),
birthday date,
sal number(7,2)
);
4、修改表
1>添加一个字段
alter table student add (classid number(2));
2>修改字段的长度
alter table student modify (xm varchar2(30));
3>修改字段的类型/名字（不能有数据）
alter table student modify (xm char(30));
4>删除一个字段
alter table student drop column sal;
5>修改表名
rename studnet to stu;
6>删除表
drop table student;
5、添加数据
1>插入所有字段
insert into student values ('A001','张三'，'男','01-5月-05',10);
oracle中默认的日期格式'DD-MON-YY'，月份必须是数字+“月”字
修改日期的格式
alter session set nls_date_format='yyyy-mm-dd';
修改后我们可以用熟悉的格式添加日期类型。
2>插入部分字段
insert into student(xh,xm,sex) values('A003','lisi','女')；
3>插入空值
insert into student(xh,xm,sex,birthday) values('A004','wangwu','男',null);
查询某字段为空值：
select * from student where birthday is null;
6、修改数据
1>修改一个字段
update student set set='女' where xh='A001';
2>修改多个字段
update student set set='女',birthday='1999-01-1' where xh='A001';
3>修改含有null值的数据
7、删除数据
1>delete from student;
删除所有记录，表结构还在，写日志可以恢复，速度慢。（savepoint rollback，默认只有一个保存点，可设置多个保存点）
2>drop table student;
删除表结构和数据。
3>truncate table student;
删除表中所有记录，表结构还在，不写日志，无法找回删除记录，速度快。
六、oracle表查询
1、基本查询
1>查看表结构
desc emp；
2>查询所有列
select * from emp;
3>查询指定列
select ename from emp;
4>去除重复行
select distinct mgr from emp;
5>使用算数表达式
select ename,sal*13+comm*13 from emp;
6>使用列的别名
select ename,sal*13+comm*13 [as] 年薪 from emp;
7>处理null值
使用nvl函数处理null值:
select ename,sal*13+nvl(comm*13,0) 年薪 from emp;
8>连接字符串
select ename || ' is a ' || job from emp;
9>使用where子句
select * from emp where sal>3000;
select * from emp where hiredate>'01-1月-1982';
10>使用like操作符
select * from emp where ename like '__S%';
11>在where条件中使用in
select * from emp where empno in (123,234,345);
注意：in是批量处理查询方式查询，速度快于or连接查询条件。
12>使用is null和is not null操作符
select * from emp where mgr is null;
13>使用逻辑操作符
select * from emp where （sal>500 or job='MANAGER'） and ename like 'J%';
14>使用order by语句
select * from emp order by deptno [asc],sal desc;
15>使用列的别名排序
select ename,sal*13+nvl(comm*13,0) as 年薪 from emp order by "年薪" desc;
注意：按别名查询时，别名必须使用""，不写和写成''都会出错。
2、复杂查询
1>数据分组：max,min,avg,sum,count
查询拥有最高工资的员工：
select ename,sal from emp where sal=(select max(sal) from emp);
查询工资高于平均工资的员工：
select * from emp where sal>(select avg(sal) from emp);
2>group by和having子句
group by用于对查询的结果分组统计；
having用于限制分组显示结果；
查询每个部门每个岗位的平均工资和最高工资：
select deptno,job,avg(sal),max(sal) from emp group by deptno,job order by deptno,job;
查询平均工资>2000的部门岗位的平均工资，最低工资，最高工资：
select deptno,job,min(sal),avg(sal),max(sal) from emp group by deptno,job having avg(sal)>2000 order by deptno,job;
注意：
分组函数只能出现在选择列表、having、order by子句中；
如果在select语句中同时包含group by、having、order by，则三者顺序必须为group by、having、order by；
在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现在group by子句中，否则会报错。
3、多表查询
1>多表查询指基于两个或两个以上的表或是视图的查询。
多表查询的条件：查询条件个数至少不能少于表的个数-1，以此排除笛卡尔积；
select dname,ename,sal from emp a1,dept a2 where a1.deptno=a2.deptno;
select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal;
select a2.dname,a1.ename,a1.sal from emp a1,dept a2 where a1.deptno=a2.deptno order by a1.deptno;
2>自连接
自连接是指同一张表的连接查询。
显示某个员工上级领导的姓名：
select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno;
4、子查询
子查询是指嵌入在其他查询条件中的查询，也叫嵌套查询。
1>单行子查询
单行子查询是指只返回一行数据的查询。
select * from emp where deptno=(select deptno from emp where ename='SMITH');
注意：数据库查询是从后往前，所以为提高性能，最好把最能筛选记录的条件放到最后面。
2>多行子查询
多行子查询是指只返回多行数据的查询。
select * from emp where job in (select distinct(job) from emp where emp.deptno=10);
3>多行子查询中使用all操作符
查询工资比30号部门最高工资还高的员工信息：
select * from emp where sal > all(select sal from emp where deptno=30);
相当于：
select * from emp where sal>(select max(sal) from emp where deptno=30);
第二条语句比第一条语句查询效率高。
4>多行子查询中使用any操作符
select * from emp where sal > any(select sal from emp where deptno=30);
5>多列子查询
多列子查询是指只返回多列数据的查询。
查询部门、岗位和smith相同的员工：
select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');
6>在from子句中使用子查询
当在from子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫做内嵌试图，而且此时必须给子查询指定别名。
查询工资高于自己部门平均工资的员工：
select a1.deptno,a1.ename,a1.sal from emp a1,(select deptno,avg(sal) avgsal from emp group by deptno)a2 where a1.deptno=a2.deptno and a1.sal>a2.avgsal order by a1.deptno;
注意：为表起别名时不能加as，为列起别名时可以加as。
7>分页查询
oracle分页共有三种方式
a)根据rowid分页
b)按分析函数分页
a)按rownum分页
select a1.*,rownum rm from (select * from emp) a1 where rownum<=10;
select * from (select a1.*,rownum rm from (select ename,sal from emp order by sal) a1 where rownum<=10) where rm>=6;
注意：
指定查询列，排序等只需修改最里层的子查询。
8>用查询结果创建新表
一种快捷的建表方法。
create table myemp (id,ename,sal) as (select empno,ename,sal from emp);
9>合并查询
在实际应用中，为了合并多个select语句，可以使用集合操作符union，union all，intersect，minus。
a)union
该操作符用于取得两个结果集的并集，当使用该操作符时，会自动去掉结果集中重复行。
select ename,sal,job from emp where sal>2500 union select ename,sal,job from emp where job='MANAGER';//显示6条记录
b)union all
该操作符和union相似，但不会取消重复行，而且不会排序。
select ename,sal,job from emp where sal>2500 union all select ename,sal,job from emp where job='MANAGER';//显示8条记录
c)intersect
用于取得两个结果集的交集。
select ename,sal,job from emp where sal>2500 intersect select ename,sal,job from emp where job='MANAGER';//显示2条记录
d)minus
用于取得两个结果集的差集。
select ename,sal,job from emp where sal>2500 minus select ename,sal,job from emp where job='MANAGER';
注意：合并查询效率高于其他方式查询。
七、java操作oracle数据库
1、java连接oracle
oracle分页示例：
配置oracle的jdbc驱动；
编写EmpList.jap代码如下：
<table>
<tr>
<td>用户名</td>
<td>薪水</td>
<%
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");
Statement sm = conn.createStatement();
int pageCount=0;//总页数
int rowCount=0;//总记录数
int pageSize=3;//页面大小
String s_pageNow = (String)request.getParameter("pageNow");
int pageNow = 0;
if(s_pageNow!=null)
{
pageNow = Integer.parseInt(s_pageNow);
}
ResultSet rs = sm.executeQuery("select count(*) from emp");
while(rs.next())
{
rowCount = rs.getInt(1);
if(rowCount%pageSize==0)
{
pageCount = rowCount/pageSize;
}
else
{
pageCount = rowCount/pageSize+1;
}
}
rs = sm.executeQuery("select * from (select a1.*,rownum rm from (select * from emp) a1 where rownum<="+pageNow*pageSize+") where rm>="+((pageNow-1)*pageSize+1));
while(rs.next())
{
out.println("<tr>");
out.println("<td>"+rs.getString(2)+"</td>");
out.println("<td>"+rs.getString(6)+"</td>");
out.println("</tr>");
}
for(int i=1; i<=pageCount; i++)
{
out.println("<a href=EmpList.jsp?pageNow="+i+">["+i+"]</a>");
}
rs.close();
sm.close();
conn.close();
%>
</tr>
</table>
2、在oracle插入数据
1>使用特定格式插入日期值
a)使用to_date函数
insert into emp values('1111','小红','manager','7839',to_date('1988-01-01','yyyy-mm-dd'),4000,0,10);
b)使用子查询插入数据
insert into myEmpTest (select empno,ename,deptno from emp where deptno=10);
c)使用update语句更新数据时，既可以使用表达式或者数值直接修改数据，也可使用子查询修改数据
修改员工scott的岗位、工资、补助和smith的一样；
update emp set (job,sal,comm)=(select job,sal,comm from emp where ename='SMITH') where ename='SCOTT';
八、oracle事务
1、事务
事务用于保证数据的一致性，它由一组相关的dml语句组成，该组dml语句要么全部执行成功，要么全部失败，如网上转账。
2、事务和锁
当执行事务操作时，oracle会在被作用的表上加锁，防止其他用户修改表结构。
3、提交事务
执行commit语句可以提交事务，当执行了commit语句后，会确认事务的变化，结束事务，删除保存点，释放锁，执行后，其他会话可以查看到事务变化后的新数据。
4、回退事务
保存点是事务中的一点，用于取消部分事务，当结束事务时，会自动删除该事务设定的所有保存点，当执行rollback时，可以回退到指定保存点。
注意：一旦commit，保存点将全部删除。
5、事务的几个重要操作
1>设置保存点
savepoint a;
2>取消部分事务：
rollback to a;
3>取消全部事务：
rollback;
4>提交事务：
commit;
6、在java使用事务
Connection conn = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");
//事务处理，设置自动提交为false
conn.setAutoCommit(false);
Statement sm = conn.createStatement();
sm.executeUpdate("update emp set sal=sal-100 where ename='SCOTT'");
sm.executeUpdate("update emp set sal=sal+100 where ename='SMITH'");
//全部执行后提交
conn.commit();
sm.close();
conn.close();
} catch (Exception e) {
try {
//遇到异常时回滚
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
7、只读事务
只读事务是指只允许执行查询的操作，而不允许执行任何其它dml操作的事务。使用只读事务可以确保用户只能取得某时间点的数据。在设置了只读事务后，尽管其它会话可能会提交新的事务，但只读事务不会取得最新数据的变化，从而保证取得特定时间点的数据信息。
设置只读事务：
set transaction read only;
九、oracle函数
1、字符函数
1>lower(char)
将字符串转换为小写格式。
2>upper(char)
将字符串转换为大写格式。
3>length(char)
统计字符串长度。
4>substr(char,m,n)
截取字符串，从m位开始，取n位。
将用户名首字母大写，其他字母小写显示：
select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)) from emp;
5>replace(char1,search_string,replace_string)
将字符串char1中search_string替换成replace_string。
6>instr(char1,char2,[n,m])
获取子串在字符串中位置。
2、数学函数
1>round(n,[m])
四舍五入，如果省略m，则四舍五入到整数。
select round(12.34,1) from dual;
2>trunc(n,[m])
用于截取数字，截取小数点后m位。
3>mod(m,n)
用于取模。
4>floor(n)
返回小于等于n的最大整数
5>ceil(n)
返回大于等于n的最小整数。
6>abs(n)
返回数字的绝对值。
7>exp(n)
返回e的n次幂。
8>power(m,n)
返回m的n次幂。
3、日期函数
默认日期格式是dd-mon-yy，即1-1月-1980。
1>sysdate
返回系统时间。
2>add_months(d,n)
查询入职时间大于8个月的员工：
select * from emp where sysdate>add_months(hiredate,8);
3>last_day(d)
返回指定日期所在月份的最后一天。
查询在当月倒数第三天入职的员工：
select * from emp where hiredate=last_day(hiredate)-2;
4、转换函数
1>to_char
将某信息准换为指定格式显示。
select to_char(sal,'L99999,99') from emp;
按默认字符集显示工资，默认是$，中文字符集下显示RMB。
select * from emp where to_char(hiredate,'yyyy-MM')='1980-12';
查询1980年12月入职的员工。
2>to_date
3>sys_context
terminal:当前会话客户所对应的终端标示符，即客户所在计算机名。
language：语言。
db_name:当前数据库名称。
nls_date_format：当前会话客户所对应的日期格式。
session_user：当前会话客户所对应的数据库用户名。
host：数据库所在主机的名称，如组名/计算机名。
十、数据库管理
1、数据库管理员
每个oracle数据库应该至少有一名数据库管理员。
职责：
1>安装和升级oracle数据库；
2>建库，表空间，表，视图，索引...
3>指定并实施备份与恢复计划；
4>数据库权限管理，调休，故障排除；
5>对于高级dba，要求参加项目开发，会编写sql语句，存储过程，触发器，规则，约束，包等。
2、管理数据库的用户主要是sys和system，两者区别是：
1>最重要的区别是，存储的数据的重要性不同
sys：所有oracle的数据字典的基表和视图都存放在sys用户中，这些oracle的基表和视图对于oracle的运行时至关重要的。由数据库自己维护，任何用户都不能手动更改。sys用户拥有sysdba，sysoper，dba角色或权限，是oracle权限最高的而用户。
system：用于存放次一级的内部数据，如oracle的一些特性或工具的管理信息，system用户拥有dba，sysdba角色或系统权限。
2>其次的区别，权限的不同
sys用户必须以as sysdba或as sysoper方式登陆，不能以normal方式登陆。
system如果正常登陆，其实就是一个普通的dba用户，如果以as sysdba登陆，实际上是作为sys用户登录的，从登录信息里面我们可以看出来。
3、sysdba、sysoper、dba权限区别:
三者权限排序：sysdba>sysoper>dba。
1>sysdba可以改变字符集，sysoper不能；
2>sysdbd可以创建删除数据库，sysoper不能；
3>sysdba可以部分恢复数据，sysoper只能完恢复，不能部分恢复。
4>sysdba可以让用户作为sys用户连接，sysoper可以进行一些基本操作，但不能查看用户数据。
5>sysdba登录之后用户是sys，sysoper登录之后用户是public。
6>dba用户是指具有dba角色的数据库用户，特权用户可以执行启动关闭实例等特殊操作，而dba用
4、管理初始化参数
初始化参数用于设置实例或是数据库的特征，oracle提供了200多个初始化参数，并且都有其默认值。
1>显示初始化参数
show parameter;
2>修改初始化参数
D:\oracle\admin\myorcl\pfile\init.ora文件中修改，比如要修改实例的名字等。
5、数据库的逻辑备份与恢复
1>逻辑备份是指使用工具export将数据对象的结构和数据导出到文件的过程，逻辑恢复是指当数据库对象被误操作而损坏后使用工具import利用备份文件把数据对象导入到数据库的过程。
物理备份既可在数据库打开的状态下进行，也可在关闭状态下进行，而逻辑备份只能在数据库打开状态下进行。
6、导出具体分为：导出表，导出方案，导出数据库三种方式，导出使用exp命令完成。
1>导出表
a)导出自己的表
exp userid=scott/tiger@orcl tables=(emp) file=D:\emp.dmp;
b)导出其他方案的表
如果用户需要导出其他方案的表，则需要dba权限或是exp_fulldatabase权限。
exp userid=system/manager@orcl tables=(scott.emp) file=D:\emp2.dmp;
c)导出表结构
exp userid=scott/tiger@orcl tables=(emp) file=D:\emp3.dmp rows=n;
d)使用直接导出方式导出数据
exp userid=scott/tiger@orcl tables=(emp) file=D:\emp.dmp direct=y;
这种方式比默认的常规方式要快，当数据量大时，可以考虑使用此方式。
但使用此方式导出时需要客户端字符集和数据库端字符集保持完全一致，否则会报错。
注意：在导入和导出的时候，要到oracle的bin目录下，执行exp.exe。
2>导出方案
a)导出自己的方案
exp userid=scott/tiger@orcl owner=scott file=D:\scott.dmp;
b)导出其他方案
如果用户需要导出其他方案的表，则需要dba权限或是exp_fulldatabase权限。
exp userid=system/manager@orcl owner=(system,scott) file=D:\system.dmp;
3>导出数据库
如果用户需要导出数据库，则需要dba权限或是exp_fulldatabase权限。
exp userid=system/manager@orcl full=y inctype=complete file=D:\system.dmp;
7、导入
1>导入表
a)导入自己的表
imp userid=scott/tiger@orcl tables=(emp) file=D:\emp.dmp;
b)导入其他方案的表
如果用户需要导入其他方案的表，则需要dba权限或是exp_fulldatabase权限。
imp userid=system/manager@orcl tables=(scott.emp) file=D:\emp2.dmp;
c)导入表结构
imp userid=scott/tiger@orcl tables=(emp) file=D:\emp3.dmp rows=n;
d)使用直接导入方式导入数据
imp userid=scott/tiger@orcl tables=(emp) file=D:\emp.dmp ignore=y;
这种方式比默认的常规方式要快，当数据量大时，可以考虑使用此方式。
但使用此方式导入时需要客户端字符集和数据库端字符集保持完全一致，否则会报错。
注意：在导入和导入的时候，要到oracle的bin目录下，执行imp.exe。
2>导入方案
a)导入自己的方案
imp userid=scott/tiger@orcl owner=scott file=D:\scott.dmp;
b)导入其他方案
如果用户需要导入其他方案的表，则需要dba权限或是exp_fulldatabase权限。
imp userid=system/manager@orcl owner=(system,scott) file=D:\system.dmp;
3>导入数据库
如果用户需要导入数据库，则需要dba权限或是exp_fulldatabase权限。
imp userid=system/manager@orcl full=y inctype=complete file=D:\system.dmp;
8、数据字典
数据字典是oracle数据库中最重要的组成部分，提供了数据库的一些系统信息，它是只读表和视图的集合，它的所有者为sys用户。
用户只能数据字典上执行查询操作，而其维护和修改是由系统自动完成。
通过查询数据字典可以取得以下系统信息：
对象定义情况；
对象占用空间大小；
列信息；
约束信息；
......
数据字典包括数据字典基表和数据字典视图，其中基表存储数据库的基本信息，普通用户不能直接访问数据字典基表。数据字典视图是基于数据字典基表建立的视图，普通用户可以通过查询视图取得系统信息，数据字典视图主要包括：user_xxx，all_xxx，dba_xxx三种类型。
1>user_tables
用于显示当前用户所拥有的所有表，返回用户所对应方案的所有表。
select table_name from user_tables;
2>all_tables
用于显示当前用户可以访问的所有表。
select table_name from all_tables;
3>dba_tables
用于显示所有方案拥有的数据库表，查询此数据库字典视图，要求用户必须是dba或者有select any table系统权限。
9、用户名、权限、角色
当建立用户时，oracle会把用户的信息存放到数据字典中，当给用户授予权限或角色时，oracle会把权限或角色的信息存放到数据字典中。
查询oracle中所有系统权限：
select * from system_privilege_map order by name;
查询角色具有的系统权限：
select * from dba_sys_privs where grantee='CONNECT';
select * from role_sys_privs where role='CONNECT';
查询oracle中所有对象权限：
select distinct privilege from dba_tab_privs;
查询角色具有的对象权限：
select * from dba_tab_privs where grantee='CONNECT';
查询角色具有的列权限：
select * from dba_col_privs where grantee='CONNECT';
查询oracle中的所有角色：
select * from dba_roles;
查询用户具有的角色：
select * from dba_role_privs where grantee='SCOTT';
查询所有数据库用户：
select * from dba_users;
查询数据库的表空间：
select tablespace_name from dba_tablespaces;
查询当前用户可以访问的所有数据字典视图：
select * from dict where comments like '%grant%';
查询当前数据库全称：
select * from global_name;
10、动态性能视图：
动态性能视图记载了例程启动后的相关信息。
动态视图用于记录当前例程的活动信息，当启动oracle server时，系统会建立动态性能视图，当停止oracle server时，系统会删除动态性能视图。oracle所有动态性能视图都是以v_$开始的，并且oracle为每个动态性能视图都提供了相应的同义词，并且其同义词都是以V$开始的。
动态性能视图的所有者为sys，一般情况下，有dba或是特权用户来查询动态性能视图。
11、管理表空间和数据文件
1>数据库的逻辑结构
oracle中逻辑结构包括表空间、段、区和块。
数据库由表空间构成，表空间由段构成，段由区构成，区由块构成，以提高数据库的效率。
2>表空间
表空间是数据库的逻辑组成部分，从物理上讲，数据库存放在数据文件中，从逻辑上讲，则是存放在表空间中，表空间由一个多多个数据文件组成。
表空间用于从逻辑上组织数据库的数据，数据库逻辑上是由一个或多个表空间构成，通过表空间可以达到以下作用：
a)控制数据库占用的磁盘空间；
b)dba可以把不同数据类型（索引、触发器等）部署到不同位置，这样有利于提高io性能，同时利于备份和恢复等管理操作。
3>建立表空间
create tablespace命令用于建立表空间，一般情况下，需特权用户或是dba来执行。如果用其他用户来创建表空间，则用户必须具有create tablespace的系统权限。
在建立数据库后，为便于管理表，最好建立自己的表空间。
create tablespace space001 datafile 'D:\space001.dbf' size 20m uniform size 128k;
4>使用表空间
create table mypart(deptno number(12),dname varchar2(14)，loc varchar2(13)) tablespace space001;
5>改变表空间状态
当建立表空间时，表空间处于联机状态，此时该表空间是可以访问的，并且该表空间是可以读写的，既可以查询数据，也可以执行各种语句。但在进行系统维护和数据维护时，可能需要改变表空间的状态，一般情况下，由特权用户或dba来操作。
a)使表空间脱机：
alter tablespace 表空间名 offline;
b)使表空间联机：
alter tablespace 表空间名 online;
c)只读表空间
alter tablespace 表空间名 read only;
d)改变表空间的状态
查询表空间包括色所有表：
select * from all_tables where tablespace_name='表空间名'；
查询某个表属于哪个表空间：
select tablespace_name,table_name from user_tables where table_name='emp';
6>删除表空间
drop tablespace 表空间名 including contents and datafiles;
including contents表示删除表空间时，删除该空间所有数据库对象，而datafile表示将数据库文件也删除。
7>扩展表空间
当表空间大小不足时，可以扩展表空间为其增加更多存储空间，扩展表空间有如下三种方式：
a)增加数据文件
alter tablespace 表空间名 add datafile 'd:\sp001.dbf' size 20m;
b)增加数据文件的大小
alter tablespace 表空间名 'd:\sp001.dbf' resize 20m;
注意：数据文件的大小不要超过500m。
c)设置文件的自动增长
alter tablespace 表空间名 'd:\sp001.dbf' autoextend on next 10m maxsize 500m;
8>移动数据文件
如果数据文件所在磁盘损坏时，该数据文件将不能再使用，为了能够重新使用，需要将这些文件的副本移动到其他磁盘并恢复，步骤如下：
a)确定数据文件所在的表空间
select tablespace_name from dba_data_files where file_name='d:\sp001.dbf';
b)使表空间脱机：
确保数据文件的一致性，将表空间转变为offline状态；
alter tablespace sp001 offline;
c)使用命令移动数据文件到指定目标位置
host move d:\sp001.dbf c:\sp001.dbf;
d)执行alter tablespace命令
alter tablespace sp001 rename datafile 'd:\sp001.fbf' to 'c:\sp001.dbf';
e)使表空间联机
alter tablespace sp001 online;
9>显示表空间信息
select tablespace_name from dba_tablespaces;
10>显示表空间所包含的数据文件
select file_name,bytes from dba_data_files where tablespace_name='表空间名';
十一、约束
1、维护数据完整性
数据的完整性用于确保数据库数据遵从一定的商业和逻辑规则。在oracle中，数据完整性可以使用约束、触发器、应用程序（过程、函数）三种方法来实现，在三种方法中，因为约束易于维护，并且具有最好的性能，所以作为维护数据完整性的首选。
2、约束
约束用于确保数据库数据满足一定的商业规则，在oracle中，约束包括：
1>not null：非空；
2>unique：唯一，不能重复但可以为空；
3>primary key：主键，不能重复且不能为空，一张表最多只能有一个主键，但可以有多个unique。
4>foreign key：用于定义主表和从表的关系，外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null；
5>check：用于强制数据必须满足的条件。
3、实例
1>建表及约束
create table goods(goodsId char(8) primary key,
goodsname varchar2(30),
unitprice number(10,2) check(unitprice>0),
category varchar2(8),
provider varchar2(30));
create table customer(customerId char(8) primary key,
name varchar2(50) not null,
address varchar2(50),
email varchar2(50) unique,sex char(2) default '男' check(sex in ('男','女')),
cardId char(10));
create table purchase(customerId char(8) references customer(customerId),
goodsId char(8) references goods(goodId),
nums number(10) check(nums between 1 and 30));
2>修改约束
alter table goods modify goodsname not null;
alter table customer add constraint cardunique unique(cardId);
alter table customer add constraint addressCheck check(address in('北京','上海','广州','深圳'));
3>删除约束
当不再需要某个约束时，可以删除。
alter table 表名 drop constraint 约束名称;
如果在两张表存在主从关系，那么在删除主键约束时，必须带上cascade选项。
alter table 表名 drop primary key cascade;
4、显示约束信息
1>显示约束信息
select constraint_name,constraint_type,status,validated from user_constraints where table_name='表名';
2>显示约束列
select column_name,position from user_cons_columns where constraint_name='约束名';
5、列级定义
列级定义是在定义列的同时定义约束。
create table department(dept_id number(2) constraint pk_department primary key,
name varchar2(12),
loc varchar2(12));
6、表级定义
表级定义是指在定义了所有列后，再定义约束，但not null约束只能在列级上定义。
create table employee(emp_id number(4),
name varchar2(15),
dept_id number(2),
constraint pk_employee primary key (emp_id),
constraint fk_department foreign key (dept_id) references department(dept_id));
十二、索引
1、索引的概念
索引是用于加速数据存取的数据对象，合理使用索引可以大大降低i/o次数，从而提高数据访问性能。
2、创建索引
1>单列索引
单列索引指基于单个列所建立的索引。
create index 索引名 on 表名(列名);
2>复合索引
复合索引是基于两列或者多列的索引，在同一张表上可以有多个索引，但是要求列的组合必须不同。
create index emp_idx1 on emp(ename,job);
create index emp_idx2 on emp(job,ename);
3、索引使用原则
1>在大表上建立索引才有意义；
2>在where子句或是连接条件经常使用的列上建立索引；
3>索引的层次不要超过4层。
4、索引的缺点
1>建立索引，系统要占用大约表的1.2倍硬盘和内存空间来保存索引。
2>更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引的一致性。
实践表明，不恰当的索引不但于事无补，反而会降低系统的性能，因为大量的索引在进行插入、修改和删除操作时比没有索引花费更多的时间。
比如在如下字段上建立索引是不恰当的：
1>很少或从不引用的字段；
2>逻辑型的字段，比如男或女等。
综上所述，提高查询效率是以消耗一定系统资源为代价的，索引不能盲目建立。
5、索引分类
1>按照数据存储方式，可以分为B*树索引、反向索引、位图索引；
2>按照索引列的个数分类，可以分为单列索引、符合索引；
3>按照索引列值的唯一性，分为唯一索引、非唯一索引；
4>此外还有函数索引、全局索引、分区索引......
在不同的情况下我们会在不同的列上建立索引，甚至建立不同种类的索引，比如：B*树索引是建立在重复值很少的列上，而位图索引则建立在重复值很多、不同值相对固定的列上。
6、显示索引信息
1>显示表的所有索引
通过查询数据字典视图可以显示索引信息，dba_indexs用于显示数据库所有的索引信息，user_indexs用于显示当前用户的索引信息。
select index_name,index_type from user_indexs where table_name='表名';
2>显示索引列
通过查询数据字典视图user_ind_columns，可以显示索引对应的列的信息。
select table_name,column_name from user_ind_columns where index_name='索引名';
十三、权限
当刚刚建立oracle用户时，用户没有任何权限，也不能执行任何操作，如果需要执行某种特定的数据库操作，则必须为其授予系统权限，如果用户要访问其他方案的对象，则必须为其授予对象权限，为了简化权限的管理，我们可以使用角色。
1、系统权限
权限是指执行特定类型sql命令或是访问其他方案对象的权利，包括系统权限和对象权限两种。
1>系统权限介绍
系统权限指执行特定sql命令的权限，它用于控制用户可指定的一个或是一组数据库操作，比如当用户具有create table权限时，可以在其方案中建表，当用户具有create any table权限时，可以在任何方案中建表，oracle提供了140多种系统权限。
2>显示系统权限
通过查询数据字典视图system_privilege_map，可以显示所有系统权限。
select * fromsystem_privilege_map order by name;
3>授予系统权限
一般情况下，授予系统权限是由dba完成的，如果其他用户来授予系统权限，则要求该用户具有grant any privilege的系统权限，在授予系统权限时，可以带有with admin option选项，表示被授予权限的用户或角色还可以将该系统权限授予其他用户或角色。
示例：
a)创建用户ken,tom，初始阶段他们没有任何权限，如果登录就会提示错误信息。
Create user ken identified by ken;
b)给ken用户授权
grant create session,create table to ken with admin option;
grant create view to ken;
c)给用户tom授权
用ken用户给tom授权
grant create session,create table to tom;
grant create view to tom;//无法授予
4>回收系统权限
一般情况下，回收系统权限是dba来完成的，如果其他用户来回收系统权限，要求该用户具有相应系统权限及转授系统权限的选项（with admin option），回收系统权限使用revoke来完成。
回收系统权限不是级联的，当system用户执行revoke create session from ken;回收ken的登录权限后，tom用户依然可以登录。
2、对象权限
1>对象权限介绍
对象权限指访问其他方案对象的权利，用户可以直接访问自己访问的对象，如果要访问别的方案的对象，则必须具有对象权限。
常用的有：
alter 修改
delete 删除
select 查询
insert 增加
update 修改
index 索引
references 引用
execute 执行
2>显示对象权限
通过查询数据字典视图dba_tab_privs，可以显示用户或是角色所具有的对象权限。
select distinct privilege from dba_tab_privs;
3>授予对象权限
在oracle9i之前，授予对象权限是由对象的所有者来完成的，从oracle9i开始，dba用户可以将任何对象上的对象权限授予其他用户，授予对象权限是用grant命令来完成的。
对象权限可以授予用户、角色、public，在授予权限时，如果带有with grant option选项，则可以将该权限转授给其他用户，但需要注意，with grant option选项不能被授予角色。
示例：
grant 对象权限 on 表名[(列名,列名2)] to 用户;
4>回收对象权限
回收对象权限可以由对象所有者完成，也可以由dba用户完成。
revoke 对象权限 on 表名 from 用户。
回收对象权限是级联的，这点和回收系统权限有所不同。
十四、角色
1、角色介绍
角色就是相关权限的命令集合，使用角色的目的就是简化权限的管理。
角色分为预定义和自定义角色。
2、预定义角色
预定义角色指oracle所提供的角色，每种角色都用于执行一些特定的管理任务，常见的预定义角色有：connect，resource，dba。
3、自定义角色
根据自己的需要来定义角色，一般是dba用户建立，如果别的用户来建立，需要具有create role的系统权限，建立角色时可以指定验证方式。
1>建立角色（不验证）
如果角色是公用的，可以采用不验证的方式建立角色。
create role 角色名 not identified;
2>建立角色（数据库验证）
采用这样的方式时，角色名、口令存放在数据库中，当激活该角色时，必须提供口令。
create role 角色名 identified by 口令;
4、角色授权
当建立角色时，角色没有任何权限，为了使角色完成特定任务，必须为其授予相应系统权限和对象权限。
1>为角色授权
系统权限的unlimited tablespace和对象权限的with grant option选项是不能授予角色的。
grant 系统权限 to角色名;
grant 对象权限 on 表名 to角色名;
2>分配角色给某个用户
一般分配角色是由dba来完成的，如果要以其他用户身份分配角色，要求用户必须具有grant any role的系统权限。
grant 角色名 to 用户名 [with admin option];
5、删除角色
一般删除角色是由dba来完成的，如果要以其他用户身份删除角色，要求用户必须具有drop any role的系统权限。
drop role 角色名;
注意：将某角色授予某用户时，删除角色后，用户将不具有此角色对应的系统权限和对象权限。
6、显示角色信息
1>显示所有角色
select * from dba_roles;
2>显示角色具有的系统权限
select privilege,admin_option from role_sys_privs where role='角色名';
3>显示角色具有的对象权限
select * from dba_tab_privs;
4>显示用户具有的角色及默认角色
select granted_role,default_role from dba_role_privs where grantee='用户名';
7、精细访问控制
指用户可以使用函数、策略实现更加细微的访问控制。如果使用精细访问控制，当客户端发出sql语句时，oracle会自动在sql语句后追加where子句，并执行新的sql语句，这样可以使不同的数据库用户在访问相同表时返回不同的数据信息。
十五、pl/sql编程
1、pl/sql概念
pl/sql(procedural language/sql)是oracle在标准sql语言上的扩展，pl/sql不仅允许嵌入sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误，这样使得它的功能变得更加强大。
pl/sql是非常强大的数据库过程语言，过程、函数可以在java程序中调用。
2、优点
1>提高应用程序的性能，如果操作需要执行大量sql语句或重复执行，存储过程比sql语句执行要快；
2>模块化的设计思想，只需创建一次，以后在程序中可以重复调用；
3>减少网络传输量，一个需要数百行的sql代码的操作由一条执行语句完成，不需要连接数百次数据库；
4>提高安全性，对于没有权限执行存储过程的用户，也可授权他们执行存储过程。
3、缺点
1>移植性不好。
4、示例
1>创建一个简单的表
create table mytest(name varchar2(30),passwd varchar2(30));
2>创建存储过程
create or replace procedure sp_pro1 is
begin
insert into mytest values('zhangsan','123');
end;
/
3>查看错误信息：
show error;
4>调用过程：
exec 过程名(参数值1，参数值2);
call 过程名(参数值1，参数值2);
5、pl/sql作用
实现块编程，包括过程、函数、触发器、包。
6、编写规范
1>注释
单行注释--
多行注释/*...*/
2>表示符号的命名规范
定义变量时，建议用v_作为前缀，如v_sal；
定义常量时，建立用c_作为前缀，如c_rate；
定义游标时，建议用_cursor作为后缀，如emp_cursor；
定义例外时，建议用e_作为前缀，如e_error；
7、块介绍
块是pl/sql的基本程序单元，编写pl/sql程序实际上就是编写块。
块由三个部分组成：定义部分、执行部分、例外部分。
declare
/*定义部分---定义常量、变量、游标、例外、符合数据类型，该部分可选*/
begin
/*执行部分，要执行的pl/sql语句和sql语句，该部分必须*/
exception
/*例外处理部分，处理运行的各种错误，该部分可选*/
8、只包含执行部分的过程
set serveroutput on;
begin
dbms_output.putline('hello');
end;
9、包含声明、执行、例外部分的过程
为了避免pl/sql程序的运行错误，提高pl/sql的健壮性，应该对可能的错误进行处理。
oracle实现预定义了一些例外，no_data_found表示找不到数据的例外。
declare
v_ename varchar2(5);
v_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp where empno=&aa;
dbms_output.put_line('用户名是：'||v_ename);
exception
when no_data_found then
dbms_output.put_line('编号输入错误');
end;
说明：&表示需要从控制台接受变量值。
10、过程
过程用于执行特定的操作，当建立过程时，可以指定输入、输出参数，通过在过程中输入参数，可以将数据传递到执行部分，通过使用输出参数，可以将执行部分的传递到应用环境。
1>创建存储过程
create or replace procedure sp_pro3 (spName varchar2,newSal number) is
begin
update emp set sal=newSal where ename=spName;
end;
2>调用存储过程
exec 存储过程名;
call 存储过程名;
3>在java中调用存储过程
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");
CallableStatement cs = conn.prepareCall("{call sp_pro3(?,?)}");
cs.setString(1, "SMITH");
cs.setInt(2, 10);
cs.execute();
cs.close();
conn.close();
11、函数
函数用于返回特定的数据，当建立函数时，在函数头部必须包含return子句，而在函数体内必须包含return语句返回的数据。
1>创建函数
create or replace function sp_fun2(spName varchar2) return
number is yearSal number(7,2);
begin
select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=spName;
return yearSal;
end;
2>调用函数
var nianxin number;
call sp_fun2('SCOTT') into:nianxin;
3>在java中调用函数
select sp_fun2('SCOTT') from dual;
通过rs.getInt(1)得到返回的结果。
12、包
包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成。
1>创建包规范
create or replace package sp_package is
procedure update_sal(name varchar2,newsal number);
function annual_income(name varchar2) return number;
end;
包规范只包含了过程和函数的声明，但没有过程和函数的实现代码，包体用于实现包规范中的过程和函数。
2>创建包体
create or replace package body sp_package is
procedure update_sal(name varchar2,newsal number) is
begin
update emp set sal=newsal where ename=name;
end;
function annual_income(name varchar2) return
number is annual_salary number;
begin
select sal*12+nvl(comm,0) into annual_salary from emp where ename=name;
return annual_salary;
end;
end;
13、触发器
触发器是指隐含的执行的存储过程，当定义触发器时，必须制定出发的事件和出发的动作，常用的触发事件包括insert、update、delete语句，而出发操作实际就是一个pl/sql块，可以使用create trigger来建立触发器。
14、定义并使用变量
1>标量类型（scalar）
indentifier [constant] datatype [notnull] [:= default expr]
indentifier：名称
constant：指定常量，需要指定它的初始值，且其值是不能改变的
datatype：数据类型
not null：指定变量值不能为null
:= 给变量或常量指定初始值
default：用于指定初始值
expr：指定初始值的pl/sql表达式，可以是文本值、其它变量、函数等。
使用标量示例：
declare
c_tax_rate number(3,2):=0.03;
v_ename varchar2(5);--按照数据库列来确定定义的变量的类型和长度
v_sal emp.sal%type;
v_tax_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
v_tax_sal:=v_sal*c_tax_rate;
dbms_output.put_line('用户名：'||v_ename||',工资：'||v_sal||',个人所得税：'||v_tax_sal);
end;
2>复合类型（composite）
用于存放多个值的变量，主要包括四种：
a)pl/sql记录
示例：
declare
--定义一个pl/sql记录类型emp_record_type，类型包括三个数据
type emp_record_type is record(name emp.ename%type,salary emp.sal%type,job emp.job%type);
--定义一个变量，变量类型为emp_record_type
sp_record emp_record_type;
begin
select ename,sal,job into sp_record from emp where empno='7788';
dbms_output.put_line('用户名：'||sp_record.name||',工资：'||sp_record.salary||'，岗位：'||sp_record.job);
end;
b)pl/sql表
相当于高级语言中的数组，但要注意的是，高级语言的下标不能为负数，pl/sql的下标可以为负数，并且表元素的下标没有限制。
declare
--定义了一个sp_table_type表类型，用于存放emp.ename%type,index by binary_integer表示下标是整数。
type sp_table_type is table of emp.ename%type index by binary_integer;
sp_table sp_table_type;
begin
--此处为0或-1，输出是下标也应为0或-1，当查询出的数据大于一条时，将报错：实际返回的行数大于请求的行数。
select ename into sp_table(0) from emp where empno='7788';
dbms_output.put_line('员工名：'||sp_table(0));
end;
c)嵌套表
d)变长数组varray
3>参照类型（reference）
参照变量是指用于存放数值指针的变量，通过使用参照变量可以使得应用程序共享相同对象，从而降低占用空间。在编写pl/sql程序时，可以使用游标变量（ref cursor）和对象类型变量（ref obj_type）两种参照变量类型。
示例：
declare
--定义游标类型
type sp_emp_cursor is ref cursor;
--定义一个游标变量
test_cursor sp_emp_cursor;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
--把游标和一个select结合
open test_cursor for select ename,sal from emp;
--循环取出
loop
fetch test_cursor into v_ename,v_sal;
--循环终止条件，判断test_cursor是否为空
exit when test_cursor%notfound;
dbms_output.put_line('员工名：'||v_ename||',工资：'||v_sal);
end loop;
end;
4>lob（large object）
十六、控制结构
1、条件分支语句
pl/sql提供了三种条件分支语句，if---then，if---then---else，if---then---elsif---else。
1>简单条件判断if---then
create or replace procedure sp_pro6(spName varchar2) is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where ename=spName;
if v_sal<2000 then
update emp set sal=sal*1.1 where ename=spName;
end if;
end;
2>二重条件分支if---then---else
create or replace procedure sp_pro6(spName varchar2) is
v_comm comm.sal%type;
begin
select comm into v_comm from emp where ename=spName;
if v_comm<>0 then
update emp set comm=comm+100 where ename=spName;
else
update emp set comm=comm+200 where ename=spName;
end if;
end;
3>多重条件分支if---then---elsif---else
create or replace procedure sp_pro7(spNo number) is
v_job emp.job%type;
begin
select job into v_job from emp where empno=spNo;
if v_job='PRESIDENT' then
update emp set sal=sal+1000 where empno=spNo;
elsif v_job='MANAGER' then
update emp set sal=sal+500 where empno=spNo;
else
update emp set sal=sal+200 where empno=spNo;
end if;
end;
注意：关键字为elsif，不要误写为else if。
2、循环语句-loop,while,for
1>loop
是pl/sql中最简单的循环语句，以loop开头，以end loop结尾，这种循环至少被执行一次。
示例：编写一个存储过程，可输入用户名，并循环添加10个用户到users1表中，用户编号从1开始。
create or replace procedure sp_pro7(spName varchar2) is
v_num number:=1;
begin
loop
insert into users1 values(v_num,spName);
exit when v_num=10;
v_num:=v_num+1;
end loop;
end;
2>while
while循环只有条件为true时才会执行循环语句，while循环以while开始，以end loop结束。
示例：编写一个存储过程，可输入用户名，并循环添加10个用户到users1表中，用户编号从11开始。
create or replace procedure sp_pro7(spName varchar2) is
v_num number:=11;
begin
while v_num<=20 loop
insert into users1 values(v_num,spName);
v_num:=v_num+1;
end loop;
end;
3>for循环
for循环循环变量隐含增长，变长不好控制，不建议使用。
示例：编写一个存储过程，可输入用户名，并循环添加10个用户到users1表中，用户编号从21开始。
create or replace procedure sp_pro8(spName varchar2) is
v_num number;
begin
for v_num in reverse 21..30 loop
insert into users1 values(v_num,spName);
end loop;
end;
3、顺序控制语句-goto,null
1>goto语句
goto语句用于跳转到特定标号去执行语句，由于使用goto语句会增加程序复杂性，并使得应用程序可读性变差，所以建议不要使用。
示例：循环输出从1到10
declare
i int:=1;
begin
loop
dbms_output.put_line('编号：'||i);
if i=10 then
goto end_loop;
end if;
i:=i+1;
end loop;
<<end_loop>>
dbms_output.put_line('循环结束');
end;
2>null
null语句不会执行任何操作，并且会将控制传递到下一条语句，使用null语句的主要好处是可以提高pl/sql的可读性。
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where ename=spName;
if v_sal<2000 then
update emp set sal=sal*1.1 where ename=spName;
else
null;
end if;
end;
十七、编写分页过程
1、无返回值的存储过程
1>创建表
create table book(bookId number,bookName varchar2(50),publishMouse varchar2(50));
2>编写存储过程
create or replace procedure sp_pro10(spBookId in number,spBookName in varchar2,spPublishMouse in varchar2) is
begin
insert into book values(spBookId,spBookName,spPublishMouse);
end;
3>java中调用存储过程
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");
CallableStatement cs = conn.prepareCall("{call sp_pro10(?,?,?)}");
cs.setInt(1, 10);
cs.setString(2, "java");
cs.setString(3, "人民出版社");
cs.execute();
cs.close();
conn.close();
2、有返回值的存储过程（返回一条记录）
1>创建存储过程
create or replace procedure sp_pro11(spNo in varchar2,spName out varchar2,spSal out number,spJob out varchar2) is
begin
select ename,sal,job into spName,spSal,spJob from emp where empno=spNo;
end;
2>java中调用存储过程
CallableStatement cs = conn.prepareCall("{call sp_pro11(?,?,?,?)}");
cs.setInt(1, 7788);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.registerOutParameter(3, oracle.jdbc.OracleTypes.DOUBLE);
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.VARCHAR);
cs.execute();
String name = cs.getString(2);
double sal = cs.getDouble(3);
String job = cs.getString(4);
3、有返回值的存储过程（返回列表）
由于oracle存储过程没有返回值，他的所有返回值都是通过out参数来替代的，列表是一个集合，不能用一般的参数，必须用到package。
1>创建一个包
--定义一个包，在该包中定义一个游标
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;
2>创建存储过程
create or replace procedure sp_pro12(spNo in number,p_cursor out testpackage.test_cursor) is
begin
open p_cursor for select * from emp where deptno=spNo;
end;
3>在java中调用存储过程
CallableStatement cs = conn.prepareCall("{call sp_pro12(?,?)}");
cs.setInt(1, 10);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
ResultSet rs = (ResultSet) cs.getObject(2);
while(rs.next())
{
System.out.println("用户编号："+rs.getInt(1)+"，用户名： "+rs.getString(2));
}
4、分页存储过程
1>创建存储过程
create or replace procedure fenye
(tableName in varchar2,--表名
myPageSize in number,--每页大小
myPageNow in number,--当前页数
myRows out number,--总记录数
myPageCount out number,--总页数
p_cursor out testpackage.test_cursor--返回的记录集
) is
--定义sql语句
v_sql varchar2(2000);
--定义当前页第一条记录数和最后一条记录数
v_begin number:=(myPageNow-1)*myPageSize+1;
v_end number:=myPageNow*myPageSize;
begin
--执行sql，返回记录集
v_sql:='select * from (select t1.*,rownum rn from （select * from '||tableName||'）t1
where rownum<='||v_end||') where rn>='||v_begin;
--打开游标
open p_cursor for v_sql;
--计算myRows
v_sql:='select count(*) from '||tableName;
execute immediate v_sql into myRows;
--计算myPageCount
if mod(myRows,myPageSize)=0 then
myPageCount:=myRows/myPageSize;
else
myPageCount:=myRows/myPageSize+1;
end if;
--关闭游标
--close p_cursor;
end;
2>java中调用存储过程
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");
CallableStatement cs = conn.prepareCall("{call fenye(?,?,?,?,?,?)}");
cs.setString(1, "emp");
cs.setInt(2, 5);
cs.setInt(3, 1);
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
int rowNum = cs.getInt(4);
int pageCount = cs.getInt(5);
ResultSet rs = (ResultSet) cs.getObject(6);
System.out.println("总记录数：" + rowNum + "，总页数：" + pageCount);
while(rs.next())
{
System.out.println("用户编号："+rs.getInt(1)+"，用户名： "+rs.getString(2));
}
注意：如果存储过程中关闭游标，java中调用时将报错：游标已关闭。
十八、例外处理
1、例外的分类
oracle将例外分为预定义例外、非预定义例外和自定义例外三种。
预定义例外用于处理常见的oracle错误。
非预定义例外用于处理预定义例外不能处理的例外。
自定义例外用于处理与oracle错误无关的其它情况。
示例：
declare
v_ename emp.ename%type;
begin
select ename into v_ename from emp where empno=&empno;
exception
when no_data_found then
dbms_output.put_line('此编号不存在！');
end;
2、处理预定义例外
预定义例外是由pl/sql所提供的系统例外，当pl/sql应用违反了oracle规定的限制时，则会隐含的触发一个内部例外。pl/sql为开发人员提供了二十多个预定义例外。
1>no_data_found例外
如果没有指定记录，就会出发no_data_found例外。
示例：
declare
v_ename emp.ename%type;
begin
select ename into v_ename from emp where empno=&empno;
exception
when no_data_found then
dbms_output.put_line('此编号不存在！');
end;
2>case_not_found例外
在开发pl/sql块中编写case语句时，如果在when子句中没有包含必须的条件分支，就会出发case_not_found例外。
示例：
create or replace procedure sp_exception1(spno number) is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=spno;
case
when v_sal<1000 then
update emp set sal=sal+100 where empno=spno;
when v_sal<2000 then
update emp set sal=sal+200 where empno=spno;
end case;
exception
when case_not_found then
dbms_output.put_line('没有匹配的条件');
end;
3>cursor_already_open例外
当重新打开已经打开的游标时，会隐含的触发cursor_already_open例外。
declare
cursor emp_cursor is select ename,sal from emp;
begin
open emp_cursor;
for emp_record1 in emp_cursor loop
dbms_output.put_line(emp_record1.ename);
end loop;
exception
when cursor_already_open then
dbms_output.put_line('游标已打开');
end;
4>dup_val_on_index例外
在唯一索引所对应的列上插入重复的值时，会隐含的出发dup_val_on_index例外。
5>invalid_cursor例外
当试图在不合法的游标上执行操作时，例如从没有打开的游标提取数据，或是关闭没有打开的游标，会触发该例外。
6>invalid_number例外
当输入的数据有误时，会触发该例外。
7>too_many_rows例外
当执行select into语句时，如果返回超过了一行，则会触发该例外。
8>zoro_divide例外
当除数为0时，会触发此例外。
9>value_error
当在执行赋值操作时，如果变量长度不足以容纳实际数据，会触发该例外。
10>login_denide
当用户非法登录时，会触发该例外。
11>not_logged_on
如果用户没有登录就执行dml操作，就会触发该例外。
12>storage_error
如果超出了内存空间或是内存损坏，就会触发该例外。
13>timeout_on_resource
如果oracle在等待资源时，出现了超时就会触发该例外。
3、非预定义例外
非预定义例外用于处理与预定义例外无关的oracle错误。比如在pl/sql块中执行dml语句时，违反了约束规定等。非预定义例外实际应用不多。
4、自定义例外
预定义例外和非预定义例外都是与oracle错误相关的，并且出现的oracle错误会隐含的出发相应的例外，而自定义例外与oracle错误没有任何关联，它是由开发人员为特定情况所定义的例外。
示例：
create or replace procedure sp_exception2(spNo number) is
--定义一个例外
myex exception;
begin
update emp set sal=sal+1000 where empno=spNo;
--没有update就触发myex例外
if sql%notfound then
raise myex;
end if;
--例外处理
exception
when myex then
dbms_output.put_line('没有更新任何用户');
end;
注意：执行select时，如果输入编号没有找到相应记录会出发例外，但执行update时，不会触发例外。
十九、视图
1、视图介绍
视图是一个虚拟表，其内容由查询定义，同真实表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集存在。行和列数据来自由由定义视图的查询所引用的表，并且在引用视图时动态生成。
2、视图和表的区别
1>表需要占用磁盘空间，视图不需要；
2>视图不能添加索引；
3>使用视图可以简化复杂查询；
4>视图可以提高安全性，例如不同用户查看不同视图。
3、操作视图
1>创建修改视图
create [or replace] view 视图名 as select语句 [with read olny];
2>删除视图
drop view 视图名;
注意：如果提示ORA-01031: insufficient privileges，可以使用system登录后，授权创建视图权限：grant create view to 用户名;
二十、触发器
1、触发器概念
触发器是特定事件执行时，自动执行的代码块。类似于存储过程，区别在于：存储过程由应用程序或用户显示调用，而触发器不能被直接调用。
2、功能
1>允许/限制对表的修改；
2>自动生成派生列，比如自增字段
3>强制数据一致性；
4>提供审计和日志记录
5>防止无效的事务处理
6>启用复杂的业务逻辑
3、触发器种类
after和before
4、操作触发器
1>创建触发器
create [or replace] trigger 触发器名 触发时间 触发事件
on 表名
[for each row]
begin
pl/sql语句
end;
触发器名：触发器对象的名称。
由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途。
触发时间：指明触发器何时执行，该值可取：
before-表示在数据库动作之前触发器执行;
after-表示在数据库动作之后出发器执行。
触发事件：指明哪些数据库动作会触发此触发器：
insert:数据库插入会触发此触发器;
update:数据库修改会触发此触发器;
delete:数据库删除会触发此触发器。
表 名：数据库触发器所在的表。
for each row:对表的每一行触发器执行一次。如果没有这一选项，则只对整个表执行一次。
2>禁用/启用触发器
禁用触发器：alter trigger 触发器名 disable;
启用触发器：alter trigger 触发器名 disable;
3>删除触发器
drop trigger 触发器名;
扫一扫
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
