Oracle的基本使用二

原创

woody2010 2012-05-09 14:09:01 博主文章分类：DBA ©著作权

©著作权归作者所有：来自51CTO博客作者woody2010的原创作品，请联系作者获取转载授权，否则将追究法律责任

显示系统权限.
oracle提供了100多种系统权限，而且oracle的版本越高，提供的系统权限就越多，我们可以询数据字典视图system_privilege_map，可以显示所有系统权限。
select * from system_privilege_map order by name;
授予系统权限.
一般情况，授予系统权限是由dba完成的，如果用其他用户来授予系统权限，则要求该用户必须具有grant any privilege的系统权限。在授予系统权限时，可以带有with admin option选项，这样，被授予权限的用户或是角色还可以将该系统权限授予其它的用户或是角色。为了让大家快速理解，我们举例说明：

1.创建两个用户ken，tom。初始阶段他们没有任何权限，如果登录就会给出错误的信息。
create user ken identfied by ken;
2 给用户ken授权
1). grant create session, create table to ken with admin option;
2). grant create view to ken;
3 给用户tom授权
我们可以通过ken给tom授权，因为with admin option是加上的。当然也可以通过dba给tom授权，我们就用ken给tom授权：
1. grant create session, create table to tom;
2. grant create view to ken; --ok吗？不ok
回收系统权限.
一般情况下，回收系统权限是dba来完成的，如果其它的用户来回收系统权限，要求该用户必须具有相应系统权限及转授系统权限的选项（with admin option）。
回收系统权限使用revoke来完成。
当回收了系统权限后，用户就不能执行相应的操作了，但是请注意，系统权限级联收回的问题?[不是级联回收！]
system --------->ken ---------->tom
(create session)(create session)( create session)
用system执行如下操作：
revoke create session from ken; --请思考tom还能登录吗？
答案：能，可以登录

对象权限
指访问其它方案对象的权利，用户可以直接访问自己方案的对象，但是如果要访问别的方案的对象，则必须具有对象的权限。
比如smith用户要访问scott.emp表（scott：方案，emp：表）
常用的有： reference引用
alter 修改 delete 删除 select 查询 insert 添加 update修改 index 索引 execut执行

. 显示对象权限

通过数据字段视图可以显示用户或是角色所具有的对象权限。视图为 dba_tab_privs
SQL> conn system/manager;
SQL> select distinct privilege from dba_tab_privs;
SQL> select grantor, owner, table_name, privilege from dba_tab_privs where grantee = 'BLAKE';

1.授予对象权限
在oracle9i前，授予对象权限是由对象的所有者来完成的，如果用其它的用户来操作，则需要用户具有相应的（with grant option）权限，从oracle9i开始，dba用户（sys，system）可以将任何对象上的对象权限授予其它用户。授予对象权限是用grant命令来完成的。
对象权限可以授予用户，角色，和public。在授予权限时，如果带有with grant option选项，则可以将该权限转授给其它用户。但是要注意with grant option选项不能被授予角色。
1.monkey用户要操作scott.emp表，则必须授予相应的对象权限
1). 希望monkey可以查询scott.emp表的数据，怎样操作？
grant select on emp to monkey;
2). 希望monkey可以修改scott.emp的表数据，怎样操作？
grant update on emp to monkey;
3). 希望monkey可以删除scott.emp的表数据，怎样操作？
grant delete on emp to monkey;
4). 有没有更加简单的方法，一次把所有权限赋给monkey？
grant all on emp to monkey;
2.能否对monkey访问权限更加精细控制。（授予列权限）
1). 希望monkey只可以修改scott.emp的表的sal字段，怎样操作？
grant update on emp(sal) to monkey
2).希望monkey只可以查询scott.emp的表的ename，sal数据，怎样操作？
grant select on emp(ename,sal) to monkey
3.授予alter权限
如果black用户要修改scott.emp表的结构，则必须授予alter对象权限
SQL> conn scott/tiger
SQL> grant alter on emp to blake;
当然也可以用system，sys来完成这件事。
4.授予execute权限
如果用户想要执行其它方案的包/过程/函数，则须有execute权限。
比如为了让ken可以执行包dbms_transaction，可以授予execute权限。
SQL> conn system/manager
SQL> grant execute on dbms_transaction to ken;
5.授予index权限
如果想在别的方案的表上建立索引，则必须具有index对象权限。
如果为了让black可以在scott.emp表上建立索引，就给其index的对象权限
SQL> conn scott/tiger
SQL> grant index on scott.emp to blake;
6.使用with grant option选项
该选项用于转授对象权限。但是该选项只能被授予用户，而不能授予角色
SQL> conn scott/tiger;
SQL> grant select on emp to blake with grant option;
SQL> conn black/shunping
SQL> grant select on scott.emp to jones;
回收对象权限.
在oracle9i中，收回对象的权限可以由对象的所有者来完成，也可以用dba用户（sys，system）来完成。
这里要说明的是：收回对象权限后，用户就不能执行相应的sql命令，但是要注意的是对象的权限是否会被级联收回？【级联回收】
如：scott------------->blake-------------->jones
select on emp select on emp select on emp
SQL> conn scott/tiger@accp
SQL> revoke select on emp from blake
请大家思考，jones能否查询scott.emp表数据。
答案：查不了了（和系统权限不一样，刚好相反）

15.角色
角色就是相关权限的命令集合，使用角色的主要目的就是为了简化权限的管理，假定有用户a，b，c为了让他们都拥有权限

1. 连接数据库
2. 在scott.emp表上select，insert，update。
如果采用直接授权操作，则需要进行12次授权。
因为要进行12次授权操作，所以比较麻烦喔！怎么办？如果我们采用角色就可以简化：

首先将creat session，select on scott.emp，insert on scott.emp， update on scott.emp授予角色，然后将该角色授予a，b，c用户，这样就可以三次授权搞定。
角色分为预定义和自定义角色两类：
预定义角色是指oracle所提供的角色，每种角色都用于执行一些特定的管理任务，下面我们介绍常用的预定义角色connect，resource，dba
1.connect角色
connect角色具有一般应用开发人员需要的大部分权限，当建立了一个用户后，多数情况下，只要给用户授予connect和 resource角色就够了，那么connect角色具有哪些系统权限呢？
alter session
create table
create view
create sequence create cluster
create database link
create session

2.resource角色
resource角色具有应用开发人员所需要的其它权限，比如建立存储过程，触
发器等。这里需要注意的是 resource角色隐含了unlimited tablespace系统权
限。
resource角色包含以下系统权限：
create cluster
create indextype
create table
create sequence create type
create procedure
create trigger

3.dba角色
dba角色具有所有的系统权限，及with admin option选项，默认的dba用户为sys和system，它们可以将任何系统权限授予其他用户。但是要注意的是dba角色不具备sysdba和sysoper的特权（启动和关闭数据库）。
自定义角色.
顾名思义就是自己定义的角色，根据自己的需要来定义。一般是dba来建立，如果用别的用户来建立，则需要具有create role的系统权限。在建立角色时可以指定验证方式（不验证，数据库验证等）。
1.建立角色（不验证）
如果角色是公用的角色，可以采用不验证的方式建立角色。 create role 角色名 not identified;
2.建立角色（数据库验证）
采用这样的方式时，角色名、口令存放在数据库中。当激活该角色时，必须提供口令。在建立这种角色时，需要为其提供口令。 create role 角色名 identified by 密码;
角色授权
当建立角色时，角色没有任何权限，为了使得角色完成特定任务，必须为其授予相应的系统权限和对象权限。
1.给角色授权
给角色授予权限和给用户授权没有太多区别，但是要注意，系统权限的unlimited tablespace和对象权限的with grant option选项是不能授予角色的。
SQL> conn system/manager;
SQL> grant create session to 角色名 with admin option
SQL> conn scott/tiger@myoral;
SQL> grant select on scott.emp to 角色名;
SQL> grant insert, update, delete on scott.emp to 角色名;
通过上面的步骤，就给角色授权了。

2.分配角色给某个用户
一般分配角色是由dba来完成的，如果要以其它用户身份分配角色，则要求用户必须具有grant any role的系统权限。
SQL> conn system/manager;
SQL> grant 角色名 to blake with admin option;
因为我给了with admin option选项，所以，blake可以把system分配给它的角色分配给别的用户。
删除角色.
使用drop role，一般是dba来执行，如果其它用户则要求该用户具有drop any role系统权限。
SQL> conn system/manager;
SQL> drop role 角色名;
问题：如果角色被删除，那么被授予角色的用户是否还具有之前角色里的权限？答案：不具了
显示角色信息.
1.显示所有角色
SQL> select * from dba_roles;
2.显示角色具有的系统权限
SQL> select privilege, admin_option from role_sys_privs where role='角色名';
3.显示角色具有的对象权限
通过查询数据字典视图dba_tab_privs可以查看角色具有的对象权限或是列的权限。
4.显示用户具有的角色，及默认角色
当以用户的身份连接到数据库时，oracle会自动的激活默认的角色，通过查询数据字典视图dba_role_privs可以显示某个用户具有的所有角色及当前默认的角色
SQL> select granted_role, default_role from dba_role_privs where grantee = ‘用户名’;

精细访问控制.
精细访问控制是指用户可以使用函数，策略实现更加细微的安全访问控制。如果使用精细访问控制，则当在客户端发出sql语句（select，insert，update，delete）时，oracle会自动在sql语句后追加谓词（where子句），并执行新的sql语句，通过这样的控制，可以使得不同的数据库用户在访问相同表时，返回不同的数据信息，如：用户 scott blake jones 策略 emp_access 数据库表 emp ，通过策略emp_access，用户scott，black，jones在执行相同的sql语句时，可以返回不同的结果。例如：当执行select ename fromemp; 时，根据实际情况可以返回不同的结果。

16.PL/SQL 块的结构和实例
pl/sql(procedural language/sql)是oracle在标准的sql语言上的扩展。
pl/sql不仅允许嵌入sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误，这样使得它的功能变得更加强大。
1.提高应用程序的运行性能
2.模块化的设计思想【分页的过程，订单的过程，转账的过程。。】
3.减少网络传输量
4.提高安全性（sql会包括表名，有时还可能有密码，传输的时候会泄露。PL/SQL就不会）
不好的地方：移植性不好（换数据库就用不了），
用什么编写pl/sql sqlplus开发工具 .
sqlplus是oracle公司提供的一个工具，这个因为我们在以前介绍过的：
举一个简单的案例：编写一个存储过程，该过程可以向某表中添加记录。
1.创建一个简单的表
Sql代码
1. create table mytest(name varchar2(30),passwd varchar2(30));
2.创建过程
Sql代码
1. create or replace procedure sp_pro1 is
2.begin--执行部分
3.insert into mytest values('韩顺平','m1234')；
4. end;
5. /
replace:表示如果有sp_pro1，就替换
如何查看错误信息：show error;
如何调用该过程：
1）exec 过程名（参数值1，参数值2...）；
2）call 过程名（参数值1，参数值2...）；
pl/sql developer是用于开发pl/sql块的集成开发环境(ide)，它是一个独立的产品，而不是oracle的一个附带品。
举一个简单案例：编写一个存储过程，该过程可以删除某表记录。
Sql代码
1. create or replace procedure sp_pro2 is
2.begin--执行部分
3.delete from mytest where name='韩顺平'；
4. end;
开发人员使用pl/sql编写应用模块时，不仅需要掌握sql语句的编写方法，还要掌握pl/sql语句及语法规则。pl/sql编程可以使用变量和逻辑控制语句，从而可以编写非常有用的功能模块。比如：分页存储过程模块、订单处理存储过程模块、转账存储过程模块。而且如果使用pl/sql编程，我们可以轻松地完成非常复杂的查询要求。
pl/sql可以做什么
编写规范.
1.注释单行注释 -- 多行注释 /*...*/来划分
2.标志符号的命名规范
1).当定义变量时，建议用v_作为前缀v_sal
2).当定义常量时，建议用c_作为前缀c_rate 3).当定义游标时，建议用_cursor作为后缀 emp_cursor
4).当定义例外时，建议用e_作为前缀e_error pl/sql块介绍

块(block)是pl/sql的基本程序单元，编写 pl/sql程序实际上就是编写pl/sql块，要完成相对简单的应用功能，可能只需要编写一个pl/sql块，但是如果想要实现复杂的功能，可能需要在一个pl/sql块中嵌套其它的pl/sql块。
块结构示意图.
pl/sql块由三个部分构成：定义部分，执行部分，例外处理部分。
如下所示：
declare /*定义部分——定义常量、变量、游标、例外、复杂数据类型*/
begin /*执行部分——要执行的pl/sql语句和sql语句*/
exception /*例外处理部分——处理运行的各种错误*/
end;
定义部分是从declare开始的，该部分是可选的；
执行部分是从begin开始的，该部分是必须的；
例外处理部分是从exception开始的，该部分是可选的。
可以和java编程结构做一个简单的比较。
1.set serveroutput on --打开输出选项
2. begin
3. dbms_output.put_line('hello');
4. end;
相关说明：
dbms_output是oracle所提供的包(类似java的开发包)，该包包含一些过程，put_line就是dbms_output包的一个过程。
pl/sql块的实例(2)
实例2-包含定义部分和执行部分的pl/sql块 .
Sql代码
1. declare
2. v_ename varchar2(5); --定义字符串变量
3. begin
4. select ename into v_ename from emp where empno=&aa;
5. dbms_output.put_line('雇员名：'||v_ename);
6. end;
7. /
如果要把薪水也显示出来，那么执行部分就应该这么写：
Sql代码
1. select ename,sal into v_ename,v_sal from emp where empno=&aa;
相关说明：
& 表示要接收从控制台输入的变量。看图：
pl/sql块的实例(3)
实例3-包含定义部分，执行部分和例外处理部分 .
为了避免pl/sql程序的运行错误，提高pl/sql的健壮性，应该对可能的错误进行处理，这个很有必要。
1.比如在实例2中，如果输入了不存在的雇员号，应当做例外处理。
2.有时出现异常，希望用另外的逻辑处理，[网示]
我们看看如何完成1的要求。
相关说明：
oracle事先预定义了一些例外，no_data_found就是找不到数据的例外。
Sql代码
1. declare
2. --定义变量
3. v_ename varchar2(5);
4. v_sal number(7,2);
5. begin
6. --执行部分
7. select ename,sal into v_ename,v_sal from emp where empno=&a a;
8. --在控制台显示用户名 9.dbms_output.put_line('用户名是：'||v_ename||' 工资：'||v_sal);
10. --异常处理
11.exception
12.when no_data_found then
13. dbms_output.put_line('朋友，你的编号输入有误！')；
14.end;
15./

17.pl/sql分类 -- 过程，函数，包，触发器
过程.
过程用于执行特定的操作，当建立过程时，既可以指定输入参数(in)，也可以指定输出参数(out)，通过在过程中使用输入参数，可以将数据传递到执行部分；
通过使用输出参数，可以将执行部分的数据传递到应用环境。在sqlplus中可以使用create procedure命令来建立过程。
实例如下：
1.请考虑编写一个过程，可以输入雇员名，新工资，可修改雇员的工资
2.如何调用过程有两种方法； exec call
3.如何在java程序中调用一个存储过程
问题：如何使用过程返回值？
特别说明：
对于过程我们会在以后给大家详细具体的介绍，现在请大家先有一个概念。
create procedure sp_pro3(spName varchar2, newSal number) is
--不要写成number(3,2),表明类型就可以了，不需要大小。就好像Java写方法时的参数一样
Sql代码
1. begin
2. --执行部分，根据用户名去修改工资
3. update emp set sal=newSal where ename=spName;
4. end;
5. /
java程序中调用一个存储过程
//演示java程序去调用oracle的存储过程案例
Java代码
1. import java.sql.*;
2. public class TestOraclePro{
3. public static void main(String[] args){
4.
5. try{
6. //1.加载驱动
7. Class.forName("oracle.jdbc.driver.OracleDriver");
8. //2.得到连接
9. Connection ct = DriverManager.getConnection("jdbc:o
racle:thin@127.0.0.1:1521:MYORA1","scott","m123");
10.
11. //3.创建CallableStatement 12. CallableStatement cs = ct.prepareCall("{call sp_pro 3(?,?)}");
13. //4.给?赋值
14. cs.setString(1,"SMITH");
15. cs.setInt(2,10);
16. //5.执行
17. cs.execute();
18. //关闭
19. cs.close();
20. ct.close();
21. } catch(Exception e){
22. e.printStackTrace();
23. }
24. }
25.}

函数.
函数用于返回特定的数据，当建立函数时，在函数头部必须包含return子句。
而在函数体内必须包含return语句返回的数据。我们可以使用create function
来建立函数，实际案例：
Sql代码
1.--输入雇员的姓名，返回该雇员的年薪
2. create function annual_incomec(name varchar2)
3. return number is
4. annual_salazy number(7,2);
5. begin 6. --执行部分
7. select sal*12+nvl(comm, 0) into annual_salazy from emp wher e ename=name;
8. return annual_salazy;
9. end;
10./
如果函数创建过程有编译错误，可以使用show error;命令显示错误
在sqlplus中调用函数
Sql代码
1. SQL> var income number
2. SQL> call annual_incomec('scott') into: income;
3. SQL> print income
同样我们可以在java程序中调用该函数
select annual_income('SCOTT') from dual;
这样可以通过rs.getInt(l)得到返回的结果。
包.
包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成。
1.我们可以使用create package命令来创建包。
实例：
--创建一个包sp_package
--声明该包有一个过程update_sal
--声明该包有一个函数annual_income
Sql代码
1. create package sp_package is
2. procedure update_sal(name varchar2, newsal number);
3. function annual_income(name varchar2) return number;
4. end;
包的规范只包含了过程和函数的说明，但是没有过程和函数的实现代码。包体用于实现包规范中的过程和函数。
2.建立包体可以使用create package body命令
--给包sp_package实现包体 Sql代码如下:
1. create or replace package body sp_package is
2. procedure update_sal(name varchar2, newsal number)
3. is
4. begin
5. update emp set sal = newsal where ename = name;
6. end;
7. function annual_income(name varchar2) return
number is
8. annual_salary number;
9. begin
10. select sal * 12 + nvl(comm, 0) into annual_salary from emp
11. where ename = name;
12. return annual_salary;
13. end;
14.end;
15./
3.如何调用包的过程或是函数
当调用包的过程或是函数时，在过程和函数前需要带有包名，如果要访问其它方案的包，还需要在包名前加方案名。如： SQL> call sp_package.update_sal('SCOTT', 1500);
特别说明：包是pl/sql中非常重要的部分，我们在使用过程分页时，将会再次体验它的威力。

触发器.
触发器是指隐含的执行的存储过程。当定义触发器时，必须要指定触发的事件和触发的操作，常用的触发事件包括insert,update,delete语句，而触发操作实际就是一个pl/sql块。可以使用create trigger来建立触发器。
特别说明：
我们会在后面详细为大家介绍触发器的使用，因为触发器是非常有用的，可维护数据库的安全和一致性

18.定义并使用变量,复合类型
在编写pl/sql程序时，可以定义变量和常量；在pl/sql程序中包括有：
1.标量类型(scalar)
2.复合类型(composite) 3.参照类型(reference)
4.lob(large object)
. 标量(scalar)——常用类型
在编写pl/sql块时，如果要使用变量，需在定义部分定义变量。pl/sql中定义变量和常量的语法如下：
identifier [constant] datatype [not null] [:=| default expr]
identifier : 名称
constant ：指定常量。需要指定它的初始值，且其值是不能改变的
datatype ：数据类型
not null ：指定变量值不能为null
:= 给变量或是常量指定初始值
default 用于指定初始值
expr ：指定初始值的pl/sql表达式，可以是文本值、其它变量、函数等。

标量定义的案例.
1.定义一个变长字符串
v_ename varchar2(10); 3.定义一个小数并给一个初始值为5.4 :=是pl/sql的赋值号
v_sal2 number(6,2):=5.4;
2.定义一个小数,范围 -9999.99~9999.99
v_sal number(6,2); 4.定义一个日期类型的数据
v_hiredate date;

5.定义一个布尔变量，不能为空，初始值为false
v_valid boolean not null default false;
标量(scalar)——使用标量.
在定义好变量后，就可以使用这些变量。这里需要说明的是pl/sql块为变量赋值不同于其它的编程语言，需要在等号前面加冒号(:=)
下面以输入员工号，显示雇员姓名、工资、个人所得税(税率为0.03)为例。说明变量的使用，看看如何编写。
Sql代码
1. declare
2. c_tax_rate number(3,2):=0.03;
3. --用户名
4. v_ename varchar2(5);
5. v_sal number(7,2);
6. v_tax_sal number(7,2);
7. begin
8.--执行 9.select ename,sal into v_ename,v_sal from emp where empno=&n o;
10.--计算所得税
11. v_tax_sal := v_sal*c_tax_rate;
12.--输出
13. dbms_output.put_line('姓名是：'||v_ename||'工资：
'||v_sal||' 交税：'||v_tax_sal);
14.end;
15./

标量(scalar)——使用%type类型 .
对于上面的pl/sql块有一个问题：
就是如果员工的姓名超过了5个字符的话，就会有错误，为了降低pl/sql程序的维护工作量，可以使用%type属性定义变量，这样它会按照数据库列来确定你定义的变量的类型和长度。
我们看看这个怎么使用：
标识符名表名.列名%type;
比如上例的v_ename，这样定义： v_ename emp.ename%type;

复合变量(composite)——介绍.
用于存放多个值的变量。主要包括这几种：
1.pl/sql记录
2.pl/sql表 3.嵌套表
4.varray
复合类型——pl/sql记录 .
类似于高级语言中的结构体，需要注意的是，当引用pl/sql记录成员时，必须要加记录变量作为前缀(记录变量.记录成员)如下：
Sql代码
1. declare
2. --定义一个pl/sql记录类型emp_record_type,类型包含3个数据name,salary,title。说白了，就是一个类型可以存放3个数据，主要是为了好管理
3. type emp_record_type is record(
4. name emp.ename%type,
5. salary emp.sal%type,
6. title emp.job%type);
7.--定义了一个sp_record变量，这个变量的类型是emp_record_type
8. sp_record emp_record_type;
9. begin
10. select ename, sal, job into sp_record from emp where empno = 7788;
11. dbms_output.put_line ('员工名:' || sp_record.name);
12.end;

复合类型-pl/sql表 .
相当于高级语言中的数组，但是需要注意的是在高级语言中数组的下标不能为负数，而pl/sql是可以为负数的，并且表元素的下标没有限制。实例如下：
Sql代码
1. declare
2.--定义了一个pl/sql表类型sp_table_type，该类型是用于存放
emp.ename%type
3.--index by binary_integer 表示下标是整数
4. type sp_table_type is table of emp.ename%type
5. index by binary_integer; 6.--定义了一个sp_table变量，这个变量的类型是sp_table_type
7. sp_table sp_table_type;
8. begin
9. select ename into sp_table(-1) from emp where empno = 7788;
10. dbms_output.put_line('员工名:' || sp_table(-1));
11.end;
说明：
sp_table_type 是pl/sql表类型
emp.ename%type 指定了表的元素的类型和长度 sp_table 为pl/sql表变量
sp_table(0) 则表示下标为0的元素
注意：如果把select ename into sp_table(-1) from emp where empno = 7788;
变成select ename into sp_table(-1) from emp;则运行时会出现错误，错误
如下： ORA-01422:实际返回的行数超出请求的行数
解决方法是：使用参照变量（这里不讲）
复合变量——嵌套表(nested table). 复合变量——变长数组(varray).
. 参照变量——介绍
参照变量是指用于存放数值指针的变量。通过使用参照变量，可以使得应用程
序共享相同对象，从而降低占用的空间。在编写pl/sql程序时，可以使用游标
变量(ref cursor)和对象类型变量(ref obj_type)两种参照变量类型。
参照变量——ref cursor游标变量 .
使用游标时，当定义游标时不需要指定相应的select语句，但是当使用游标时（open时）需要指定select语句，这样一个游标就与一个select语句结合了。实例如下：
1.请使用pl/sql编写一个块，可以输入部门号，并显示该部门所有员工姓名和他的工资。
2.在1的基础上，如果某个员工的工资低于200元，就添加100元。 Sql代码：
1. declare
2.--定义游标sp_emp_cursor
3. type sp_emp_cursor is ref cursor;
4.--定义一个游标变量
5. test_cursor sp_emp_cursor;
6.--定义变量
7. v_ename emp.ename%type;
8. v_sal emp.sal%type;
9. begin
10.--执行
11.--把test_cursor和一个select结合 12.open test_cursor for select ename,sal from emp where deptno=&no ;
13.--循环取出
14.loop
15. fetch test_cursor into v_ename,v_sal;
16. --判断是否test_cursor为空
17. exit when test_cursor%notfound;
18. dbms_output.put_line('名字：'||v_ename||' 工资：'||v_sal);
19.end loop;
20.end;
21./

19.pl/sql的进阶--控制结构(分支，循环，控制)
1.掌握pl/sql的高级用法（能缩写分页过程模块，下订单过程模块...）
2.会处理oracle常见的例外 3.会编写oracle各种触发器
4.理解视图的概念并能灵活使用视图
pl/sql的进阶--控制结构
在任何计算机语言(c，java，pascal)都有各种控制语句（条件语句，循环结构，顺序控制结构...）在pl/sql中也存在这样的控制结构。
在本部分学习完成后，希望大家达到：
1.使用各种if语句 2.使用循环语句 3.使用控制语句——goto和null；
条件分支语句. pl/sql中提供了三种条件分支语句if—then，if – then –else，if – then
– elsif – then
这里我们可以和java语句进行一个比较
简单的条件判断 if – then.
问题：编写一个过程，可以输入一个雇员名，如果该雇员的工资低于2000，就给该员工工资增加10%。
Sql代码
1. create or replace procedure sp_pro6(spName varchar2) is
2.--定义
3. v_sal emp.sal%type;
4. begin
5. --执行
6. select sal into v_sal from emp where ename=spName; 7. --判断
8. if v_sal<2000 then
9. update emp set sal=sal+sal*10% where ename=spName;
10. end if;
11.end;
12./

二重条件分支 if – then – else.
问题：编写一个过程，可以输入一个雇员名，如果该雇员的补助不是0就在原来的基础上增加100；如果补助为0就把补助设为200；
Sql代码
1. create or replace procedure sp_pro6(spName varchar2) is
2.--定义
3. v_comm emp.comm%type;
4. begin
5. --执行
6. select comm into v_comm from emp where ename=spName;
7. --判断 8. if v_comm<>0 then
9. update emp set comm=comm+100 where ename=spName;
10. else
11. update emp set comm=comm+200 where ename=spName;
12. end if;
13.end;
14./

多重条件分支 if – then – elsif – then.
问题：编写一个过程，可以输入一个雇员编号，如果该雇员的职位是PRESIDENT就给他的工资增加1000，如果该雇员的职位是MANAGER就给他的工资增加500，其它职位的雇员工资增加200。
Sql代码

1. create or replace procedure sp_pro6(spNo number) is
2. --定义
3. v_job emp.job%type;
4. begin
5. --执行
6. select job into v_job from emp where empno=spNo;
7. if v_job='PRESIDENT' then 8. update emp set sal=sal+1000 where empno=spNo;
9. elsif v_job='MANAGER' then
10. update emp set sal=sal+500 where empno=spNo;
11. else
12. update emp set sal=sal+200 where empno=spNo;
13. end if;
14.end;
15./

循环语句 –loop.
是pl/sql中最简单的循环语句，这种循环语句以loop开头，以end loop结尾，这种循环至少会被执行一次。
案例：现有一张表users，表结构如下：
用户id | 用户名 |
请编写一个过程，可以输入用户名，并循环添加10个用户到 users表中，用户编号从1开始增加。
Sql代码
1. create or replace procedure sp_pro6(spName varchar2) is
2.--定义 :=表示赋值
3. v_num number:=1;
4. begin
5. loop
6. insert into users values(v_num,spName); 7. --判断是否要退出循环
8. exit when v_num=10;
9. --自增
10. v_num:=v_num+1;
11. end loop;
12.end;
13./

循环语句 –while循环 .
基本循环至少要执行循环体一次，而对于while循环来说，只有条件为true时，才会执行循环体语句，while循环以while...loop开始，以end loop结束。
案例：现有一张表users，表结构如下：
用户id 用户名
问题：请编写一个过程，可以输入用户名，并循环添加10个用户到 users表中，用户编号从11开始增加。
Sql代码
1. create or replace procedure sp_pro6(spName varchar2) is
2.--定义 :=表示赋值
3. v_num number:=11;
4. begin
5. while v_num<=20 loop 6. --执行
7. insert into users values(v_num,spName);
8. v_num:=v_num+1;
9. end loop;
10.end;
11./

循环语句 –for循环 .
基本for循环的基本结构如下 Sql代码：
1. begin
2. for i in reverse 1..10 loop
3. insert into users values (i, 'shunping'); 4. end loop;
5. end;
我们可以看到控制变量i，在隐含中就在不停地增加。

顺序控制语句 –goto，null.
1.goto语句
goto语句用于跳转到特定符号去执行语句。注意由于使用goto语句会增加程序的复杂性，并使得应用程序可读性变差，所以在做一般应用开发时，建议大家不要使用goto语句。
基本语法如下 goto lable，其中lable是已经定义好的标号名，
Sql代码
1. declare
2. i int := 1;
3. begin
4. loop
5. dbms_output.put_line('输出i=' || i);
6. if i = 1{} then
7. goto end_loop; 8. end if;
9. i := i + 1;
10. end loop;
11. <<end_loop>>
12. dbms_output.put_line('循环结束');
13.end;

2.null
null语句不会执行任何操作，并且会直接将控制传递到下一条语句。使用null语句的主要好处是可以提高pl/sql的可读性。
Sql代码
1. declare
2. v_sal emp.sal%type;
3. v_ename emp.ename%type;
4. begin
5. select ename, sal into v_ename, v_sal from emp where empno = &no; 6. if v_sal < 3000 then
7. update emp set comm = sal * 0.1 where ename = v_ename;
8. else
9. null;
10. end if;
11.end;

20.PL/SQL分页
编写分页过程
分页是任何一个网站(bbs,网上商城，blog)都会使用到的技术，因此学习pl/sql编程开发就一定要掌握该技术。
无返回值的存储过程.
古人云：欲速则不达，为了让大家伙比较容易接受分页过程编写，我还是从简单到复杂，循序渐进的给大家讲解。首先是掌握最简单的存储过程，无返回值的存储过程：
案例：现有一张表book，表结构如下：书号书名出版社
请写一个过程，可以向book表添加书，要求通过java程序调用该过程。
--in:表示这是一个输入参数，默认为in
--out:表示一个输出参数
Sql代码
1. create or replace procedure sp_pro7(spBookId in number,spbookNa me in varchar2,sppublishHouse in varchar2) is
2. begin
3. insert into book values(spBookId,spbookName,sppublishHouse) ;
4. end;
5. / --在java中调用
Java代码
1.//调用一个无返回值的过程
2. import java.sql.*;
3. public class Test2{
4. public static void main(String[] args){
5.
6. try{
7. //1.加载驱动
8. Class.forName("oracle.jdbc.driver.OracleDriver");
9. //2.得到连接
10. Connection ct = DriverManager.getConnection("jdbc:o
racle:thin@127.0.0.1:1521:MYORA1","scott","m123");
11.
12. //3.创建CallableStatement
13.
CallableStatement cs = ct.prepareCall("{call sp_pro 7(?,?,?)}"); 14. //4.给?赋值
15. cs.setInt(1,10);
16. cs.setString(2,"笑傲江湖");
17. cs.setString(3,"人民出版社");
18. //5.执行
19. cs.execute();
20. } catch(Exception e){
21. e.printStackTrace();
22. } finally{
23. //6.关闭各个打开的资源
24. cs.close();
25. ct.close();
26. }
27. }
28.}
执行，记录被加进去了
有返回值的存储过程（非列表）.
再看如何处理有返回值的存储过程：
案例：编写一个过程，可以输入雇员的编号，返回该雇员的姓名。
案例扩张：编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗位。
Sql代码
1.--有输入和输出的存储过程
2. create or replace procedure sp_pro8
3. (spno in number, spName out varchar2) is
4. begin 5. select ename into spName from emp where empno=spno;
6. end;
7. /

Java代码
1. import java.sql.*;
2. public class Test2{
3. public static void main(String[] args){
4.
5. try{
6.
//1.加载驱动
7.
Class.forName("oracle.jdbc.driver.OracleDriver");
8.
//2.得到连接
9.
Connection ct = DriverManager.getConnection("jdbc:o
racle:thin@127.0.0.1:1521:MYORA1","scott","m123");
10.
11. //3.创建CallableStatement
12.
/*CallableStatement cs = ct.prepareCall("{call sp_p ro7(?,?,?)}");
13. //4.给?赋值
14. cs.setInt(1,10);
15. cs.setString(2,"笑傲江湖");
16. cs.setString(3,"人民出版社");*/
17. 18. //看看如何调用有返回值的过程
19. //创建CallableStatement
20. /*CallableStatement cs = ct.prepareCall("{call sp_p ro8(?,?)}");
21.
22. //给第一个？赋值
23. cs.setInt(1,7788);
24. //给第二个？赋值
25. cs.registerOutParameter(2,oracle.jdbc.OracleTypes.V ARCHAR);
26.
27. //5.执行
28. cs.execute();
29. //取出返回值,要注意？的顺序
30. String name=cs.getString(2);
31. System.out.println("7788的名字"+name);
32. } catch(Exception e){
33. e.printStackTrace();
34. } finally{
35. //6.关闭各个打开的资源
36. cs.close();
37. ct.close();
38. }
39. }
40.}
运行，成功得出结果。。
案例扩张：编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗位。
Sql代码
1.--有输入和输出的存储过程
2. create or replace procedure sp_pro8
3. (spno in number, spName out varchar2,spSal out number,spJob out
varchar2) is 4. begin
5. select ename,sal,job into spName,spSal,spJob from emp where empno=spno;
6. end;
7. /

Java代码
1. import java.sql.*;
2. public class Test2{
3. public static void main(String[] args){
4.
5. try{
6. //1.加载驱动
7. Class.forName("oracle.jdbc.driver.OracleDriver");
8. //2.得到连接
9. Connection ct= DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");
10.
11. //3.创建CallableStatement
12. /*CallableStatement cs = ct.prepareCall("{call sp_p ro7(?,?,?)}");
13. //4.给?赋值
14. cs.setInt(1,10);
15. cs.setString(2,"笑傲江湖");
16. cs.setString(3,"人民出版社");*/
17.
18. //看看如何调用有返回值的过程
19. //创建CallableStatement
20. /*CallableStatement cs = ct.prepareCall("{call sp_p ro8(?,?,?,?)}");
22. //给第一个？赋值
23. cs.setInt(1,7788); 24. //给第二个？赋值
25. cs.registerOutParameter(2,oracle.jdbc.OracleTypes.V ARCHAR);
26. //给第三个？赋值
27. cs.registerOutParameter(3,oracle.jdbc.OracleTypes.D OUBLE);
28. //给第四个？赋值
29.cs.registerOutParameter(4,oracle.jdbc.OracleTypes.V ARCHAR);
30.
31. //5.执行
32. cs.execute();
33. //取出返回值,要注意？的顺序
34. String name=cs.getString(2);
35. String job=cs.getString(4);
36. System.out.println("7788的名字"+name+" 工作： "+job);
37. } catch(Exception e){
38. e.printStackTrace();
39. } finally{
40. //6.关闭各个打开的资源
41. cs.close();
42. ct.close();
43. }
44. }
45.}
运行，成功找出记录
有返回值的存储过程（列表[结果集]）.
案例：编写一个过程，输入部门号，返回该部门所有雇员信息。
对该题分析如下：
由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代
的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用
pagkage了。所以要分两部分：
返回结果集的过程
1.建立一个包，在该包中，我定义类型test_cursor,是个游标。如下：
Sql代码
1. create or replace package testpackage as
2. TYPE test_cursor is ref cursor;
3. end testpackage;
2.建立存储过程。如下：
Sql代码
1. create or replace procedure sp_pro9(spNo in number,p_cursor out
testpackage.test_cursor) is 2. begin
3. open p_cursor for
4. select * from emp where deptno = spNo;
5. end sp_pro9;
3.如何在java程序中调用该过程
Java代码
运行，成功得出部门号是10的所有用户
编写分页过程.
--oracle的分页
Sql代码

1. import java.sql.*;
2. public class Test2{
3. public static void main(String[] args){
4.
5. try{
6. //1.加载驱动
7. Class.forName("oracle.jdbc.driver.OracleDriver");
8. //2.得到连接
9.
Connection ct = DriverManager.getConnection("jdbc:o
racle:thin@127.0.0.1:1521:MYORA1","scott","m123");
10.
11. //看看如何调用有返回值的过程
12. //3.创建CallableStatement
13. /*CallableStatement cs = ct.prepareCall("{call sp_p ro9(?,?)}");
14.
15. //4.给第？赋值
16. cs.setInt(1,10); 17. //给第二个？赋值
18.
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.C
URSOR);
19.
20. //5.执行
21. cs.execute();
22. //得到结果集
23. ResultSet rs=(ResultSet)cs.getObject(2);
24. while(rs.next()){
25.
System.out.println(rs.getInt(1)+" "+rs.getStrin
g(2));
26. }
27. } catch(Exception e){
28. e.printStackTrace();
29. } finally{
30. //6.关闭各个打开的资源
31. cs.close();
32. ct.close();
33. }
34. }
35.}

1. select t1.*, rownum rn from (select * from emp) t1 where rownum <=10;
2.--在分页时，大家可以把下面的sql语句当做一个模板使用
3. select * from
4. (select t1.*, rownum rn from (select * from emp) t1 where row num<=10)
5. where rn>=6;
--开发一个包
--建立一个包，在该包中，我定义类型test_cursor,是个游标。如下：
Sql代码
1. create or replace package testpackage as
2. TYPE test_cursor is ref cursor;
3. end testpackage;
4.--开始编写分页的过程
5. create or replace procedure fenye
6. (tableName in varchar2,
7. Pagesize in number,--一页显示记录数
8. pageNow in number,
9. myrows out number,--总记录数
10. myPageCount out number,--总页数
11. p_cursor out testpackage.test_cursor--返回的记录集
12. ) is
13.--定义部分 14.--定义sql语句字符串
15.v_sql varchar2(1000);
16.--定义两个整数
17.v_begin number:=(pageNow-1)*Pagesize+1;
18.v_end number:=pageNow*Pagesize;
19.begin
20.--执行部分
21.v_sql:='select * from (select t1.*, rownum rn from (select * fr om '||tableName||') t1 where rownum<='||v_end||') where rn>='|| v_begin;
22.--把游标和sql关联
23.open p_cursor for v_sql;
24.--计算myrows和myPageCount
25.--组织一个sql语句
26.v_sql:='select count(*) from '||tableName;
27.--执行sql,并把返回的值，赋给myrows；
28.execute inmediate v_sql into myrows;
29.--计算myPageCount
30.--if myrows%Pagesize=0 then这样写是错的
31.if mod(myrows,Pagesize)=0 then
32. myPageCount:=myrows/Pagesize;
33.else
34. myPageCount:=myrows/Pagesize+1
35.end if;
36.--关闭游标
37.close p_cursor;
38.end;
39./

--使用java测试
//测试分页
Java代码
1. import java.sql.*;
2. public class FenYe{
3. public static void main(String[] args){
4.
5. try{
6. //1.加载驱动
7. Class.forName("oracle.jdbc.driver.OracleDriver");
8. //2.得到连接
9. Connection ct = DriverManager.getConnection("jdbc:o
racle:thin@127.0.0.1:1521:MYORA1","scott","m123");
10.
11. //3.创建CallableStatement
12. CallableStatement cs = ct.prepareCall("{call fenye( ?,?,?,?,?,?)}");
13.
14. //4.给第？赋值
15. cs.seString(1,"emp");
16. cs.setInt(2,5);
17. cs.setInt(3,2);
18.
19. //注册总记录数
20. cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);
21. //注册总页数
22. cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER); 23. //注册返回的结果集
24. cs.registerOutParameter(6,oracle.jdbc.OracleTypes.C URSOR);
25.
26. //5.执行
27. cs.execute();
28.
29. //取出总记录数 /这里要注意，getInt(4)中4，是由该参数的位置决定的
30. int rowNum=cs.getInt(4);
31.
32. int pageCount = cs.getInt(5);
33. ResultSet rs=(ResultSet)cs.getObject(6);
34.
35. //显示一下，看看对不对
36. System.out.println("rowNum="+rowNum);
37. System.out.println("总页数="+pageCount);
38.
39. while(rs.next()){
40. System.out.println("编号："+rs.getInt(1)+" 名字： "+rs.getString(2)+" 工资："+rs.getFloat(6));
41. }
42. } catch(Exception e){
43. e.printStackTrace();
44. } finally{
45. //6.关闭各个打开的资源
46. cs.close();
47. ct.close();
48. }
49. }
50.}
运行，控制台输出：
rowNum=19
总页数：4
编号：7369 名字：SMITH 工资：2850.0
编号：7499 名字：ALLEN 工资：2450.0
编号：7521 名字：WARD 工资：1562.0
编号：7566 名字：JONES 工资：7200.0
编号：7654 名字：MARTIN 工资：1500.0
--新的需要，要求按照薪水从低到高排序，然后取出6-10 过程的执行部分做下改动，如下：
Sql代码
1. begin
2.--执行部分
3. v_sql:='select * from (select t1.*, rownum rn from (select * from '||tableName||' order by sal) t1 where rownum<='||v_end||') where rn>='||v_begin;
重新执行一次procedure,java不用改变，运行，控制台输出：
rowNum=19
总页数：4
编号：7900 名字：JAMES 工资：950.0
编号：7876 名字：ADAMS 工资：1100.0
编号：7521 名字：WARD 工资：1250.0
编号：7654 名字：MARTIN 工资：1250.0
编号：7934 名字：MILLER 工资：1300.0

21.例外处理
例外的分类.
oracle将例外分为预定义例外，非预定义例外和自定义例外三种。
预定义例外用于处理常见的oracle错误
非预定义例外用于处理预定义例外不能处理的例外
自定义例外用于处理与oracle错误无关的其它情况
例外传递.
如果不处理例外我们看看会出现什么情况：
案例，编写一个过程，可接收雇员的编号，并显示该雇员的姓名。
问题是，如果输入的雇员编号不存在，怎样去处理呢？
Sql代码
1.--例外案例
2. declare
3.--定义
4. v_ename emp.ename%type;
5. begin 6. --
7. select ename into v_ename from emp where empno=&gno;
8.dbms_output.put_line('名字：'||v_ename)
9. /
执行，弹出框，看图：
随便输个不存在的编号，回车，会抛出异常，显示：
ORA-01403: 未找到数据
ORA-06512: 在line 6
Sql代码
1. declare
2.--定义
3. v_ename emp.ename%type;
4. begin
5. --
6. select ename into v_ename from emp where empno=&gno; 7.dbms_output.put_line('名字：'||v_ename)
8. exception
9. when no_data_found then
10. dbms_output.put_line('编号没有！');
11./
执行，输入一个不存在的编号，回车，显示：
编号没有！

处理预定义例外.
预定义例外是由pl/sql所提供的系统例外。当pl/sql应用程序违反了oracle 规定的限制时，则会隐含的触发一个内部例外。pl/sql为开发人员提供了二十多个预定义例外。我们给大家介绍常用的例外。

预定义例外. case_not_found
在开发pl/sql块中编写 case语句时，如果在 when子句中没有包含必须的条件分支，就会触发case_not_found的例外：
Sql代码
1. create or replace procedure sp_pro6(spno number) is
2. v_sal emp.sal%type;
3. begin
4. select sal into v_sal from emp where empno = spno;
5. case
6. when v_sal < 1000 then
7. update emp set sal = sal + 100 where empno = spno; 8. when v_sal < 2000 then
9. update emp set sal = sal + 200 where empno = spno;
10. end case;
11.exception
12. when case_not_found then
13. dbms_output.put_line('case语句没有与' || v_sal || '相匹配的条件');
14.end;

预定义例外 cursor_already_open.
当重新打开已经打开的游标时，会隐含的触发例外cursor_already_open
Sql代码
1. declare
2. cursor emp_cursor is select ename, sal from emp;
3. begin
4. open emp_cursor;
5. for emp_record1 in emp_cursor loop
6. dbms_output.put_line(emp_record1.ename); 7. end loop;
8. exception
9. when cursor_already_open then
10. dbms_output.put_line('游标已经打开');
11.end;
12./

预定义例外 dup_val_on_index.
在唯一索引所对应的列上插入重复的值时，会隐含的触发例外
dup_val_on_index例外
Sql代码
1. begin
2. insert into dept values (10, '公关部', '北京');
3. exception 4. when dup_val_on_index then
5. dbms_output.put_line('在deptno列上不能出现重复值');
6. end;

预定义例外 invalid_cursor.
当试图在不合法的游标上执行操作时，会触发该例外
例如：试图从没有打开的游标提取数据，或是关闭没有打开的游标。则会触发该例外
Sql代码
1. declare
2. cursor emp_cursor is select ename, sal from emp;
3. emp_record emp_cursor%rowtype;
4. begin
5. --open emp_cursor; --打开游标
6. fetch emp_cursor into emp_record; 7. dbms_output.put_line(emp_record.ename);
8. close emp_cursor;
9. exception
10. when invalid_cursor then
11. dbms_output.put_line('请检测游标是否打开');
12.end;

预定义例外 invalid_number.
当输入的数据有误时，会触发该例外
比如：数字100写成了loo就会触发该例外
Sql代码
1. begin
2. update emp set sal= sal + 'loo';
3. exception 4. when invalid_number then
5. dbms_output.put_line('输入的数字不正确');
6. end;

预定义例外 no_data_found
下面是一个pl/sql块，当执行select into 没有返回行，就会触发该例外
Sql代码
1. declare
2. v_sal emp.sal%type;
3. begin
4. select sal into v_sal from emp
5. when ename='&name'; 6. exception
7. when no_data_found then
8. dbms_output.put_line('不存在该员工')；
9. end;

预定义例外 too_many_rows.
当执行select into语句时，如果返回超过了一行，则会触发该例外。
Sql代码
1. declare
2. v_ename emp.ename%type;
3. begin
4. select ename into v_ename from emp; 5. exception
6. when too_many_rows then
7. dbms_output.put_line('返回了多行');
8. end;

预定义例外 zero_divide.
当执行2/0语句时，则会触发该例外。

预定义例外. value_error
当在执行赋值操作时，如果变量的长度不足以容纳实际数据，则会触发该例外
value_error，比如：
Sql代码
1. declare
2. v_ename varchar2(5);
3. begin
4. select ename into v_ename from emp where empno = &no1; 5. dbms_output.put_line(v_ename);
6. exception
7. when value_error then
8. dbms_output.put_line('变量尺寸不足');
9. end;

其它预定义例外(这些例外不是在pl/sql里触发的，而是在用oracle时触发的，
所以取名叫其它预定义例外).
1.login_denied 当用户非法登录时，会触发该例外
2.not_logged_on 如果用户没有登录就执行dml操作，就会触发该例外
3.storage_error 如果超过了内存空间或是内存被损坏，就触发该例外
4.timeout_on_resource 如果oracle在等待资源时，出现了超时就触发该例外

非预定义例外.

非预定义例外用于处理与预定义例外无关的oracle错误。使用预定义例外只能处理21个oracle错误，而当使用pl/sql开发应用程序时，可能会遇到其它的一些oracle错误。比如在pl/sql块中执行dml语句时，违反了约束规定等等。在这样的情况下，也可以处理oracle的各种例外，因为非预定义例外用的不多，这里我就不举例了。

处理自定义例外.
预定义例外和自定义例外都是与oracle错误相关的，并且出现的oracle错误会隐含的触发相应的例外；而自定义例外与oracle错误没有任何关联，它是由开发人员为特定情况所定义的例外.
问题：请编写一个pl/sql块，接收一个雇员的编号，并给该雇员工资增加1000元，如果该雇员不存在，请提示。
Sql代码
1.--自定义例外
2. create or replace procedure ex_test(spNo number)
3. is
4. begin 5.--更新用户sal
6. update emp set sal=sal+1000 where empno=spNo;
7. end;
8. /
运行，该过程被成功创建。
SQL> exec ex_test(56);
PL/SQL过程被成功完成
这里，编号为56是不存在的，刚才的报异常了，为什么现在不报异常呢？
因为刚才的是select语句
怎么解决这个问题呢？修改代码，如下：
Sql代码
1.--自定义例外
2. create or replace procedure ex_test(spNo number)
3. is
4.--定义一个例外
5. myex exception;
6. begin
7.--更新用户sal
8. update emp set sal=sal+1000 where empno=spNo;
9. --sql%notfound这是表示没有update
10.--raise myex;触发myex
11.if sql%notfound then
12.raise myex;
13.end if;
14.exception
15.when myex then
16.dbms_output.put_line('没有更新任何用户')；
17.end;
18./

现在再测试一次：
SQL> exec ex_test(56);
没有更新任何用户

22.oracle的视图
oracle的视图
介绍.
视图是一个虚拟表，其内容由查询定义，同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。（视图不是真实存在磁盘上的）

视图与表的区别.
1.表需要占用磁盘空间，视图不需要
2.视图不能添加索引（所以查询速度略微慢点）
3.使用视图可以简化，复杂查询比如：学生选课系统
4.视图的使用利于提高安全性比如：不同用户查看不同视图

创建/修改视图
创建视图.
create view 视图名 as select 语句 [with read only]
创建或修改视图.
create or replace view 视图名 as select 语句 [with read only]
删除视图.
drop view 视图名
当表结构国语复杂，请使用视图吧！
--创建视图，把emp表的sal<1000的雇员映射到该视图（view）
Sql代码
1. create view myview as select * from emp where sal<1000;
--为简化操作，用一个视图解决显示雇员编号，姓名和部门名称
Sql代码
1. create view myview2 as select emp.empno,emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno;

视图之间也可以做联合查询