标签：sql 10g 查询 玩转 Linux oracle 权限 pl

玩转oracle 10g实战教程

oracle 第 1 讲

①淘宝网，天涯网,校友网..都有各自的功能,那么当我们关闭系统的时候，下次再访问这些网站时，为什么他们各自的信息还存在。

②再比如c/s的软件，比如网游、qq、他们又是怎样保存数据的 -  a.游戏积分 b.qq聊天记录。

• 解决之道-文件、数据库

我们刚学习过文件,大家可能回答用文件就可以保存数据嘛!
没有错，可以如果用文件保存数据存在几个缺点:
(1)文件的安全性问题
(2)文件不利于查询和对数据的管理
(3)文件不利于存放海量数据
(4)文件在程序中控制不方便

• 解决之道-文件、数据库

为了解决上述问题,专家们设计出更加利于管理数据的东东-数据库(本质就是一个 软件 )，它能更有效的管理数据。

数据库是衡量一个程序员水平的重要指标。

举一个生活化的案例说明如果说 图书馆是保存书籍的,那么数据库就是保存数据的。

• 概述：目前主流数据库包括
  ● 微软： sql server 和access
  ● 瑞典MySQL： AB公司mysql
  ● ibm公司： db
  ● 美国Sybase公司： Sybase
  ● ibm公司： informix
  ● 美国oracle公司： oracle
  

简单对上面六种主流数据库做一个对比。[作图]

为什么选择oracle –性能优越


概述：从目前软件公司对数据库的需求看，oralce程序员的需求量是最大的，这里有一些数据大家可以看看。

为什么选择oracle –需求量大

概述：通过oracle ocm认证参加工作的人，一般月薪或年薪是多少？

我们看看别人怎么说 - oracle的认证考试简单介绍。

为什么选择oracle –待遇好

oracle的安装

• 系统要求
• 操作系统最好为windows server
• 内存最好在256M以上
• 硬盘空间需要2G以上

oracle的具体安装、启动我这里给大家演示一下。

数据库服务器、数据库和表的关系

Oracle

DB实例

DB实例

数据库对象

Client 数据库对象

⚫ 所谓安装数据库服务器，只是在机器上装了一个数据库管理程序，
这个管理程序可以管理多个数据库，一般开发人员会针对每一个
应用创建一个数据库。
⚫ 为保存应用中实体的数据，一般会在数据库创建多个表，以保存
程序中实体的数据。
⚫ 数据库服务器、数据库和表的关系如图所示：

oracle的卸载

• 1.停止所有与ORACLE相关的服务。

• 2. 使用OUI（Oracle Universal Installer）卸载Oracle软件。

    “开始”->“程序”->“Oracle-OraDb110g_home1|Oracle installation product|Universal installer.

• 3.删除注册表内容。运行regedit命令，删除下面内容：

    HKEY_LOCAL_MACHINE|SOFTWARE|ORACLE注册表键，删除此键。
    HKEY_LOCAL_MACHINE|SYSTEM|CurrentControlSet|Services，删除Services键下所有以oracle
为首的键。
HKEY_LOCAL_MACHINE|SYSTEM|CurrentControlSet|Services|Eventlog|Application，删除此键下所
有以oracle为首的键。
    HKEY_CLASSES_ROOT，删除此键下所有以Ora，Oracle，Orcl，EnumOra 为前缀的键。
HKEY_CURRENT_USER|Software|Microsoft|Windows|CurrentVersion|Explorer|MenuOrder|StartMenu|Programs，删除此键下所有以oracle为首的键。
    HKEY_LOCAL_MACHINE|SOFTWARE|ODBC|ODBCINST.INI注册表键，删除了Microsoft ODBC FOR ORACLE注册表键以外的所有有Oracle字样的键值。
    HKEY_LOCAL_MACHINE|SYSTEM|CurrentControlSet|Services，删除以Oracle或OraWeb为前缀的键。

• 4. 删除环境变量。

    删除环境变量CLASSPATH，PATH中含有Oracle字样的值。

• 5.最后在文件系统内删除ORACLE相关的文件及目录：删除系统盘符：\Progrm Files\Oracle目录；删除ORACLE_BASE目录。

    我无法删除D：\oracle目录，重新启动机器之后才删除。

• 神喻 代神说话的人 甲骨文

• 暗示 公司预作霸主的决心

• 地位

• 全球第一大数据库厂商

• 全球第二大独立软件供应商

• oracle公司介绍 –oracle的含义

• oracle公司介绍 –公司发展与现状

• 1970 年 数据库起家

• 1980 年 ORACLE6版本

• 1990 年 ORACLE7版本,多元化产品，以数据库为优先发展方向

• 1995 年 ORACLE8版本

• 1999 年 ORACLE8i版本

• 2001 年 ORACLE9i release

• 2002 年 ORACLE9i release

• 2003 年 ORACLE10g版本

• 2007 年 ORACLE11g版本

• 2009 年,甲骨文以每股9.5美元的价格收购Sun，交易总价值约为 74 亿美元。

• oracle 10g 比oracle 9i增加了什么 -

简要说：
10g支持网格(Grid)，支持自动管理(Automatic Management)。
详细说：
①10g的g是“Grid”缩写，支持网格计算，即，多台结点服务器利用高速网络组成一个虚拟的高性能服务器，负载在整个网格中均衡(Load Balance)，按需增点，避免单点故障(Single Point of Faliure)。
②安装容易，安装工作量比9i减少了一半。
③新增基于浏览器的企业管理器(Enterprise Manager)。

• oracle公司介绍 –公司发展与现状
• oracle公司介绍 –Larry Ellisin
• 财富榜前 30 名之内
• 与盖茨可以相比
• ORACLE的建立者和发展者
• IT风云人物

oracle公司介绍 –产品线

* 数据库服务器： 2007 年最新版本11G
* 应用服务器：Oracle Application Server
* 开发工具：Oracle JDeveloper，Oracle Designer，Oracle
Developer，等等
* 应用软件（主要竞争对手：德国SAP公司。）
* 企业资源计划(ERP)软件。
* 客户关系管理(CRM)软件。
* 人力资源管理软件(HCM)。

oracle管理工具的介绍(1)

sqlplus是oracle自带的工具软件，主要用于执行sql语句,pl\sql块.
如何使用:
1)在开始->程序->oracle oradb_home10g->application development->sqlplus
2)在运行栏中输入: sqlplusw即可

oracle管理工具的介绍( 2 )

概述:
sqlplus 是 dos下操作oracle的工具，其功能和sqlplus相似.
1)在运行栏中输入sqlplus
2)找到该可执行文件sqlplus.exe，
在oracle主目录\ora10g\bin\sqlplus.exe ，鼠标双击即可

oracle管理工具的介绍( 3 )

概述:
pl/sql developer 属于第三方软件,主要用于开发，测试，优化oracle pl/sql 的存储过程比如: 触发器,此软件oracle不带，需要单独安装。

oracle管理工具的介绍( 4 )

• Enterprise manager console(企业管理器)

oracle 10g是通过web管理的一般默认端口是 5500 ，也有 1158 的。
访问url(请一定保证oracle服务启动了):
http://ip:1158 (也可能是5500)/em
http://机器名:端口/em

sql*plus常用命令

• 连接命令

(1)conn[ect]
用法: conn 用户名/密码@网络服务名[as sysdba/sysoper]
当用特权用户身份连接时，必须带上as sysdba 或是 as sysoper
(2)disc[onnect]
说明:该命令用来断开与当前数据库的连接
(3)passw[ord]
说明:该命令用于修改用户的密码.如果要想修改其它用户的密码，需要
用sys/system登陆.
(4)show user
说明：显示当前用户名
(5)exit
说明：该命令会断开与数据库的连接，同时会退出sql*plus

sqlplus常用命令

• 交互式命令

(1)&
说明:可以替代变量,而该变量在执行时，需要用户输入。
sql>select * from emp where job='&job'
(2)edit
说明:该命令可以编辑指定的sql脚本
案例:sql>edit d:\a.sql
(3)spool
说明：该命令可以将sql*plus屏幕上的内容输出到指定文件中去.
案例:sql>spool d:\b.sql 并输入 sql>spool off

sqlplus常用命令

• 显示和设置环境变量

概述：可以用来控制输出的各种格式。
(1)linesize
说明:设置显示行的宽度,默认是 80 个字符
sql>show linesize
sql>set linesize 90
(2)pagesize
说明:设置每页显示的行数目,默认是14,用法和linesize一样。至于其它
环境参数的使用也是大同小异

oracle用户管理

• 创建用户(简单版)

概述：在oracle中要创建一个新的用户使用create user 语句,一般是具
有dba(数据库管理员)的权限才能使用。
基本语法：create user 用户名 identified by 密码

• 给用户修改密码

概述:如果给自己修改密码可以直接使用
sql>password 用户名
如果给别人修改密码则需要具有dba的权限,或是拥有alter user的系统
权限
sql>alter user 用户名 identified by 新密码

oracle用户管理

• 创建用户(细节)

例子: sql> create user shunping identified by m123
default tablespace users
temporary tablespace temp
quota 3m on users;
identified by表明该用户shunping 将用数据库方式验证default tablespace users //用户的表空间在users上
temporary tablespace temp//用户shunping的临时表健在temp 空间
quota 3m on users//表明用户shunping 建立的数据对象(表,索引,视图,pl/sql块..)最大只能是3m
刚刚创建的用户是没有任何权限的,因此,需要dba给该用户授权.
sql>grant connect to shunping
如果你希望该用户建表没有空间的限制
sql>grand resource to shunping
如果你希望该用户成为dba
sql>grant dba to shunping

oracle用户管理

• 删除用户

概述：一般以dba的身份去删除某个用户,如果用其它用户去删除用户
则需要具有 drop user的权限。
比如 drop user 用户名【cascade】

oracle用户管理

• 用户管理的综合案例

概述：创建的新用户是没有任何权限的，甚至连登录的数据库的权限都没有，需要为其指定相应的权限。给一个用户赋权限使用命令grant，回收权限使用命令revoke。
基本语法: grant 权限/角色 to 用户为了给讲清楚用户的管理，这里我给大家举一个案例。
①创建 xiaoming,并赋予 connect 和 resource
②回收权限

oracle用户管理

• 使用profile管理用户口令

概述：profile是口令限制,资源限制的命令集合,当建立数据时,oracle会自动建立名称为default的profile,当建立用户没有指定profile选项,那oracle就会将default分配给用户。
(1)帐户锁定
概述: 指定该帐户(用户)登陆时最多可以输入密码的次数,也可以指定用户锁定的时间(天)一般用dba的身份去执行该命令
例子:指定scott这个用户最多只能尝试 3 次登陆,锁定时间为 2 天，让我们看看怎么实现。
创建profile文件
sql> create profilelock_account limit failed_login_attempts 3
password_lock_time2;
sql>alter usertea profilelock_account;

oracle用户管理

2)给帐户(用户)解锁
sql> alter user tea account unlock;
(3)终止口令
为了让用户定期修改密码可以使用终止口令的指令来完成,同样这个命令也需要dba身份来操作.
例子:给前面创建的用户tea创建一个profile文件,要求该用户每隔 10 天要修改自家的登陆密码,宽限期为 2 天。看看怎么做.
sql> create profilemyprofile limit password_life_time 10
password_grace_time2;
sql>alter user tea profile myprofile

oracle用户管理

• 口令历史

概述:如果希望用户在修改密码时,不能使用以前使用过的密码,可使用口令历史,这样oracle就会将口令修改的信息存放到数据字典中,这样当用户修改密码时,oracle就会对新旧密码进行比较,当发现新旧密码一样时,就提示用户重新输入密码。
例子:
1)建立profile
sql> create profile password_history limit password_life_time 10
password_grace_time 2 password_reuse_time 10
password_reuse_time //指定口令可重用时间即 10 天后就需要修改
2)分配给某个用户.
sql>alter user tea profile myprofile

oracle用户管理

• 删除profile

概述:当不需要某个profile文件时，可以删除该文件.
sql> drop profile profile文件名

oracle数据库启动流程

• oracle也可以通过命令行的方式启动，我们看看具体是怎样操作。
• oracle启动流程-windows下

1) lsnrctl start (启动监听)
2) oradim – startup – sid 数据库实例名

• oracle启动流程-linux下

1) lsnctl start (启动监听)
2) sqlplus sys/change_on_install as sysdba (以sysdba身份登录,在oracle10g后可以这样写)
sqlplus /nolog
conn sys/change_on_install as sysdba
3) startup

oracle登录认证方式

• oracle登录认证方式-windows下

概述: oracle登录认证在windows下和linux下是不完全相同的,这里我们先说说windows下oracle的登录认证方式.

①操作系统认证

如果当前用户属于本地操作系统的ora_dba组（对于Windows操作系统而言),即可通过操作系统认证。

②oracle数据库验证(密码文件验证)对于普通用户,oracle默认使用数据库验证。

对于特权用户(比如sys用户),oracle默认使用操作系统认证,如果验证不通过,再到数据库验证(密码文件验证)。

通过配置sqlnet.ora文件，可以修改oracle登录认证方式

SQLNET.AUTHENTICATION_SERVICES= (NTS)是基于操作系统验证；

SQLNET.AUTHENTICATION_SERVICES= (NONE)是基于Oracle验证；

SQLNET.AUTHENTICATION_SERVICES= (NONE，NTS)是二者共存。

oracle登录认证方式

• oracle登录认证方式-linux下

这里大家了解即可:
默认情况下linux下的oracle数据库sqlnet.ora文件没有SQLNET.AUTHENTICATION_SERVICES参数，此时是基于操作系统认证和oracle密码验证共存的，

加上SQLNET.AUTHENTICATION_SERVICES参数后，不管SQLNET.AUTHENTICATION_SERVICES设置为NONE或者NTS，都是基于oracle密码验证的。

• 丢失管理员密码怎么办

恢复办法：把原有密码文件删除,生成一个新的密码文件。

恢复步骤如下：

① 搜索名为PWD数据库实例名.ora 文件

② 删除该文件,为预防万一,建议大家备份

③ 生成新的密码文件,在dos控制台下输入命令:

orapwd file=原来密码文件的全路径\密码文件名.ora password=新密码

entries=10;//entries:允许几个特权用户密码文件名 一定要和原来的密码文件名一样。

Linux公社（LinuxIDC.com）

 • Linux公社（LinuxIDC.com）于 2006 年 9 月 25 日注册并开通网站，Linux现在已经成为一种广受关注和支持的一种操作系统，IDC是互联网数据中心，LinuxIDC就是关于Linux的数据中心。

 • Linux公社是专业的Linux系统门户网站，实时发布最新Linux资讯，包括Linux、Ubuntu、Fedora、RedHat、红旗Linux、Linux教程、Linux认证、SUSE Linux、Android、Oracle、Hadoop等技术。

oracle第 2 讲

0. 数据库的一些基本概念
1. 表的管理
2. 基本查询
3. 复杂查询
4. 创建数据库实例

• 基本概念--数据库服务器、数据库和表的关系

• 所谓安装数据库服务器，只是在机器上装了一个数据库管理程序，这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。

• 为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。

• 数据库服务器、数据库和表的关系如图所示：

• 基本概念--数据在数据库中的存储方式
  

• 表的管理--创建表(基本语句)

CREATE TABLE table_name
(
field1 datatype,
field2 datatype,
field3 datatype,
)
field：指定列名 datatype：指定列类型

• 注意：创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。例：user对象
  

• 表的管理 — oracle常用数据类型
  

• 数值类型

number(p,s) p为整数位,s为小数位.范围：1 <= p <=38, - 84
<= s <= 127
保存数据范围：-1.0e-130 <= number value <
1.0e+126
保存在机器内部的范围：1 ~ 22 bytes
时间日期 date
TIMESTAMP(n)
包含年月日,时分秒。默认格式：DD-MON-YYYY。

从公元前 4712 年 1 月 1 日到公元 4712 年12 月 31 日的所有合法日期n的取值为0~9.表示指定TIMESTAMP中秒的小
数位数。

N为可选。

如果n为 0 ，timestamp与date等价[不推荐]

◇number可以理解成是一个可变的数值类型,比如number(12) ,你放一个小整数，它占用的字节数就少,你放一个大整数，它占用的字节数就多，很好!

oracle表的管理--oracle支持的数据类型

• 数值型

NUMBER[(precision [, scale])] NUMBER(p,s)
范围： 1 <= p <=38, -84 <= s <= 127
保存数据范围：-1.0e-130 <= number value < 1.0e+126
保存在机器内部的范围：1 ~ 22 bytes
有效位：从左边第一个不为 0 的数算起的位数。
s > 0
精确到小数点右边s位，并四舍五入。然后检验有效位是否<= p。
s < 0
精确到小数点左边s位，并四舍五入。然后检验有效位是否<= p + |s|。
s = 0 等价于NUMBER(p)
此时NUMBER表示整数。

oracle表的管理--oracle支持的数据类型

• 数值型

看几个案例:
number(5,2)
表示一个小数有 5 位有效数， 2 位小数。范围 -999.99~999.99
如果数值超出了位数限制就会被截取多余的位数。但在一行数据中的这
个字段输入575.316，则真正保存到字段中的数值是575.32。
number(5) <=> number(5,0)
表示一个五位整数，范围-99999~99999。
输入57523.316，真正保存的数据是 57523

oracle表的管理--oracle支持的数据类型

• 数值型(小练习)

Actual Data Specified As Stored As
----------------------------------------
123.89 NUMBER 123.89
123.89 NUMBER(3) 124
123.89 NUMBER(6,2) 123.89
123.89 NUMBER(6,1) 123.9
123.89 NUMBER(4,2) exceeds precision (有效位为5, 5 > 4)
123.89 NUMBER(6,-2) 100
.01234 NUMBER(4,5) .01234 (有效位为4)
.00012 NUMBER(4,5) .00012
.000127 NUMBER(4,5) .00013
.0000012 NUMBER(2,7) .0000012
.00000123 NUMBER(2,7) .0000012
1.2e-4 NUMBER(2,5) 0.00012
1.2e-5 NUMBER(2,5) 0.00001


Actual Data Specified As Stored As
----------------------------------------
123.2564 NUMBER 123.2564
1234.9876 NUMBER(6,2) 1234.99
12345.12345 NUMBER(6,2) Error (有效位为5+2 > 6)
1234.9876 NUMBER(6) 1235 (s没有表示s=0)
12345.345 NUMBER(5,-2) 12300
1234567 NUMBER(5,-2) 1234600
12345678 NUMBER(5,-2) Error (有效位为8 > 7)
123456789 NUMBER(5,-4) 123460000
1234567890 NUMBER(5,-4) Error (有效位为10 > 9)
12345.58 NUMBER(*, 1) 12345.6
0.1 NUMBER(4,5) Error (0.10000, 有效位为5 > 4)
0.01234567 NUMBER(4,5) 0.01235
0.09999 NUMBER(4,5) 0.09999

oracle表的管理 — 创建表

• 建表--学生表

--班级表
包含: 班级编号，班级名称

字段	字段类型
Id	整形
name	字符型
sex	字符型
brithday	日期型
fellowship	小数型
resume	大文本型

oracle表的管理 — 修改表

• 使用ALTER TABLE 语句添加, 修改, 或删除列的语法.

ALTER TABLE tablename
ADD ( columnname datatype );

ALTER TABLE table
MODIFY ( columnname datatype );

ALTER TABLE table
DROP column ( column );

• 修改表的名称：rename 表名to 新表名

oracle表的管理 — 修改表练习

1.给学生表添加班级编号
2.学生姓名 变成 varchar2(30)
3.学生姓名 变成 char(30)
4.删除学生表的 fellowship 字段
5.把学生表名 student修改成 stu
6.删除学生表

oracle表的管理 — crud

• Insert语句 (增加数据)

• Update语句 (更新数据)

• Delete语句 (删除数据)

• Select语句 (查找数据)

oracle表的管理 — 添加数据

INSERT INTO table [( column [ , column... ])]
VALUES (value [ , value... ]);

• 使用 INSERT 语句向表中插入数据。

• 插入的数据应与字段的数据类型相同。

• 数据的大小应在列的规定范围内，例如：不能将一个长度为80 的字符串加入到长度为 40 的列中。

• 在values中列出的数据位置必须与被加入的列的排列位置相对应。

• 字符和日期型数据应包含在单引号中。

• 插入空值，不指定或insert into table value(null)

  oracle表的管理 — 添加数据

• 练习：使用insert语句向表中插入三个学生的信息。

• 注意：字符和日期要包含在单引号中。

字段	字段类型
Id	整形
name	字符型
sex	字符型
brithday	日期型
fellowship	小数型
resume	大文本型

oracle表的管理 — 添加数据

• 插入部分字段
• 插入空值

INSERT INTO table [( column [ , column... ])]
VALUES (value [ , value... ]);

oracle表的管理 — 修改数据

UPDATE tbl_name
SET col_name1 = expr1 [, col_name2 = expr2 ...]
[WHERE where_definition ]

• 使用 update语句修改表中数据。
• UPDATE语法可以用新值更新原有表行中的各列。
• SET子句指示要修改哪些列和要给予哪些值。
• WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。

oracle表的管理—修改数据练习

• 改一个字段 : 根据学号修改性别.
• 修改多个字段 : 根据学号修改性别和生日
• 修改含有null值的数据 :
• 要求

-  将所有学生薪水修改为 5000 元。
-  将姓名为’zs’的学生薪水修改为 3000 元。
-  将’lisi’的薪水在原有基础上增加 1000 元。
-  将没有奖学金同学的奖学金改成 10 元

oracle表的管理 — 删除数据

delete from tbl_name
[WHERE where_definition ]

• 使用 delete语句删除表中数据。
• 如果不使用where子句，将删除表中所有数据。
• Delete语句不能删除某一列的值（可使用update）
• 使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。
• 同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。
• 删除表中数据也可使用TRUNCATE TABLE 语句，它和delete有所不同，参看mysql文档。

oracle表的管理 — 删除数据

• 删除数据

delete from 表名; --删除所有记录,表结构还在,写日志,可以恢复的,速度慢
drop table 表名;  --删除表的结构和数据
delete from student where xh='A001';  --删除一条记录
truncate table 表名;  --删除表中的所有记录,表结构还在,不写日志,无法找回删除的记录,速度快

oracle表基本查询 — 介绍

• 介绍

在我们讲解的过程中我们利用scott用户存在的几张表(emp,dept)为大家演示如何使用select语句，

select语句在软件编程中非常的有用，希望大家好好的掌握

oracle表基本查询 — 介绍

SELECT [DISTINCT] *|{ column 1 , column 2. column 3 ..}
FROM table;

• Select 指定查询哪些列的数据。

• column指定列名。

• *号代表查询所有列。

• From指定查询哪张表。

• DISTINCT可选，指显示结果时，是否剔除重复数据

• 基本select语句

oracle表基本查询 — 简单的查询语句

• 查看表结构
  sql>desc 表名;

• 查询所有列
  select * from 表名;

• 查询指定列
  select 列 1 ，列2... from 表名;

• 如何取消重复行
  select distinct deptno ,job from emp;

• 查询SMITH 的薪水,工作,所在部门

oracle表基本查询 — 简单的查询语句

• 使用算数表达式
• 显示每个雇员的年工资
• 使用列的别名

select ename "姓名",sal*12 as "年收入" from emp;

• 如何处理null值 使用nvl函数来处理
• 如何连接字符串(||)

oracle表基本查询 — 简单的查询语句

• 使用where子句
• 如何显示工资高于 3000 的员工
• 如何查找1982.1.1后入职的员工
• 如何显示工资在 2000 到 2500 的员工情况
• 如何使用like操作符
  %: 表示任意 0 到多个字符 _: 表示任意单个字符
• 如何显示首字符为S的员工姓名和工资
• 如何显示第三个字符为大写O的所有员工的姓名和工资
• 在where条件中使用in
• 如何显示empno为123,345,800...的雇员情况
• 使用is null的操作符
• 如何显示没有上级的雇员的情况

oracle表基本查询 — 简单的查询语句

• 使用逻辑操作符号
• 查询工资高于 500 或是岗位为MANAGER的雇员，同时还要满足他们的姓名首写字母为大写的J
• 使用order by子句
• 如何按照工资的从低到高的顺序显示雇员的信息
• 按照部门号升序而雇员的入职时间降序排列
• 使用列的别名排序 别名需要使用“ 号圈中

select ename,sal*12 "年薪" from emp order by "年薪" asc;

oracle表基本查询 — 简单的查询语句

• 分页查询

按雇员的id号升序取出。

oracle表复杂查询

• 说明

在实际应用中经常需要执行复杂的数据统计，经常需要显示多张表的数据，现在我们给大家介绍较为复杂的select语句

• 数据分组-max,min,avg,sum,count
• 如何显示所有员工中最高工资和最低工资
• 显示所有员工的平均工资和工资总和
• 计算共有多少员工
• 扩展要求：
• 请显示工资最高的员工的名字，工作岗位
• 请显示工资高于平均工资的员工信息

oracle表复杂查询

• group by 和having子句
• group by用于对查询的结果分组统计,
• having子句用于限制分组显示结果.
• 如何显示每个部门的平均工资和最高工资
• 显示每个部门的每种岗位的平均工资和最低工资
• 显示平均工资低于 2000 的部门号和它的平均工资扩展要求：

oracle表复杂查询

• 对数据分组的总结

1 分组函数只能出现在选择列表、having、order by子句种
2 如果在select 语句种同时包含有group by ,having ,order by 那么他们的顺序是group by , having , order by
3 在选择列种如果有列、表达式、和分组函数,那么这些列和表达式必须有一个出现在group by 子句中，否则就会出错

如select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)<2000;
这里deptno就一定要出现在group by 中

oracle表复杂查询 — 多表查询

• 说明

多表查询是指基于两个和两个以上的表或是视图的查询.在实际应用中,查询单个表可能不能满足你的需求,（如显示sales部门位置和其员工的姓名),这种情况下需要使用到(dept表和emp表)

• 显示雇员名,雇员工资及所在部门的名字【笛卡尔集】

规定:多表查询的条件是至少不能少于 表的个数- 1

• 如何显示部门号为 10 的部门名、员工名和工资
• 显示各个员工的姓名，工资，及其工资的级别扩展要求:
• 显示雇员名,雇员工资及所在部门的名字,并按部门排序.

oracle表复杂查询 — 多表查询

• 自连接

自连接是指在同一张表的连接查询。

• 显示员工的上级领导的姓名

比如显示’FORD’的上级.

• 扩展要求:
• 显示各员工的姓名和他的上级领导姓名.

oracle表复杂查询 — 子查询

• 什么是子查询

子查询是指嵌入在其它sql语句中的select语句,也叫嵌套查询

• 单行子查询

单行子查询是指只返回一行数据的子查询语句请思考：如何显示与SMITH同一部门的所有员工 - 

• 多行子查询

多行子查询指返回多行数据的子查询

• 请思考:如何查询和部门 10 的工作相同的雇员的名字、岗位、工资、部门号

oracle表复杂查询 — 子查询

• 在多行子查询中使用all操作符
• 请思考:如何显示工资比部门 30 的所有员工的工资高的员工的姓名、工资和部门号

select ename,sal,deptno from emp where sal>all (select sal from emp
where deptno=30);

• 扩展要求：大家想想还有没有别的查询方法.

Select ename,sal,deptno from emp where sal>(select max(sal) from
emp where deptno=30);

oracle表复杂查询 — 子查询

• 在多在多行子查询中使用any操作符

请思考:如何显示工资比部门 30 的任意一个员工的工资高的员工的姓名、工资和部门号

• 扩展要求：大家想想还有没有别的查询方法.

oracle表复杂查询 — 子查询

• 多列子查询

单行子查询是指子查询只返回单列、单行数据,多行子查询是指返回单列多行数据,都是针对单列而言的，而多列子查序则是指查询返回多个列数据的子查询语句

• 请思考如何查询与smith的部门和岗位完全相同的所有雇员

oracle表复杂查询 — 子查询

• 在from子句中使用子查询
• 请思考：如何显示高于自己部门平均工资的员工的信息
• 这里要用到数据查询的小技巧，把一个子查询当作一个临时表使用

解法①
select e1.*,e2.myavg from emp e1,(select avg(sal) myavg,deptno
from emp group by deptno) e2 where e1.deptno=e2.deptno and
e1.sal>e2.myavg
解法②
select e1.* from emp e1 where e1.sal>(select avg(sal) from emp
where deptno=e1.deptno)

oracle表复杂查询 — 子查询

• 在from子句中使用子查询
• 请思考：查找每个部门工资最高的人的详细资料

解法①
解法②
思路:得到所有的员工，进行筛选，每拿到一个员工，判断该员工的工资是否是他们部门的最高工资。
select * from emp e where sal=(select max(sal) from emp where
deptno=e.deptno);

oracle表复杂查询 — 子查询

• 在from子句中使用子查询
• 请思考：显示每个部门的信息和人员数量

解法①
自己完成...
解法②
查询获得所有的部门信息，每获得一个部门，我们就查询该部门的人数，保存为一个新的列。
select d.*,(select count(*) from emp where deptno=d.deptno) allnum from dept d;

oracle表复杂查询 — 子查询

• 在from子句中使用子查询

这里需要说明的当在from子句中使用子查询时,该子查询会被作为一个临时表来对待,当在from子句中使用子查询时，必须给子查询指定别名.

oracle表复杂查询 — 子查询

• 分页查询

按雇员的id号升序取出。

oracle表复杂查询 — 子查询

• 用查询结果创建新表
• 这个命令是一种快捷的建表方法.

create table mytable (id,name,sal,job,deptno)
as select empno,ename,sal,job,deptno from emp;

• 自我复制数据(蠕虫复制)
• 有时，为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据。

insert into mytable (id,name,sal,job,deptno)
select empno,ename,sal,job,deptno from emp;

oracle表复杂查询 — 合并查询

• 合并查询

有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union , union all,intersect , minus
1)union
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。
select ename,sal,job from emp where sal>2500 union
select ename,sal,job from emp where job=‘MANAGER';

oracle表复杂查询 — 合并查询

2)union all
该操作赋与union相似，但是它不会取消重复行,而且不会排序。
select ename,sal,job from emp where sal>2500
union all select ename,sal,job from emp where
job='manager';
3)intersect
使用该操作符用于取得两个结果集的交集。
select ename,sal,job from emp where sal>2500
intersect select ename,sal,job from emp where
job='manager';

oracle表复杂查询 — 合并查询

4) minus
使用该操作符用于取得两个结果集的差集，它只会显示存在第一个集合中，而不存在第二个集合中的数据。
select ename,sal,job from emp where sal>2500 minus
select ename,sal,job from emp where job='manager';

oracle表内连接和外连接

• 概述

表连接分为内连接和外连接。

• 内连接

内连接实际上就是利用 where 子句对两张表形成的笛卡尔积进行筛选,我们前面学习的查询都是内连接,也是在开发过程中用的最多的连接查询。

oracle表内连接和外连接

• 外连接

①左外连接 (如果左侧的表完全显示我们就说是左外连接）
②右外连接 (如果右侧的表完全显示我们就说是右外连接)
③完全外连接 (完全显示两个表，没有匹配的记录置为空)为了讲清楚，我们举例说明。

--表stu
id name
1, Jack
2, Tom
3, Kity
4, nono

--表exam
id grade
1, 56
2, 76
11, 8

oracle表内连接和外连接

• 外连接

1)内连接案例（显示两表id匹配的）
2)左连接（显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号,成绩显示为空）
select stu.id,stu.name, exam.grade from stu left join exam on
stu.id=exam.id 有的程序员喜欢这样写左外连接
select stu.id,stu.name, exam.grade from stu , exam where
stu.id=exam.id(+)

oracle表内连接和外连接

• 外连接

3)右连接（显示所有成绩，如果没有名字匹配，显示空）
select exam.id,stu.name, exam.grade from stu right join exam on
stu.id=exam.id 有的程序员喜欢这样写左外连接
select exam.id,stu.name, exam.grade from stu ,exam where
stu.id(+)=exam.id
4)完全外连接（显示所有成绩和所有人的名字，如果相应的匹配值，则显示空）

oracle表内连接和外连接

• 一个小练习

为加深大家对外连接的理解，我们做一个小练习
列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

oracle表复杂查询 — 创建新数据库实例

• 创建数据库有两种方法:

1) 通过oracle提供的向导工具 √
2) 我们可以用手工步骤直接创建.
 • Linux公社（LinuxIDC.com）
 • Linux公社（LinuxIDC.com）于 2006 年 9 月 25 日注册并开通网站，Linux现在已经成为一种广受关注和支持的一种操作系统，IDC是互联网数据中心，LinuxIDC就是关于Linux的数据中心。

 • Linux公社是专业的Linux系统门户网站，实时发布最新Linux资讯，包括Linux、Ubuntu、Fedora、RedHat、红旗Linux、Linux教程、Linux认证、SUSE Linux、Android、Oracle、Hadoop等技术。

oracle第 3 讲

1. java程序如何操作oracle
2. 如何在oracle中操作数据
3. oracle事务处理
4. sql函数的使用

java连接oracle

• 介绍
  前面我们一直在plsql中操作oracle,那么如何在java程序中操作数据库
  呢 -

下面我们举例说明,写一个ShowEmp.java(jsp),分页显示emp表的用户信息。

java连接oracle odbc桥连接:
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection ct=DriverManager.getConnection("jdbc:odbc:testsp","scott","m123");

java连接oracle jdbc连接
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myora1","scott","m123");

在oracle中操作数据 — 使用特定的格式插入日期

• 使用 to_date函数
  请大家思考：如何插入列带有日期的表,并按照年-月-日的格式插入 -

oracle中操作数据 — 使用子查询插入数据

• 介绍
  当使用values子句时,一次只能插入一行数据,当使用子查询插入数据时,
  一条insert语句可以插入大量的数据.当处理行迁移或者装载外部表的数
  据到数据库时,可以使用子查询来插入数据.

oracle中操作数据 — 使用子查询更新数据

• 介绍
  使用update语句更新数据时,既可以使用表达式或者数值直接修改数据,
  也可以使用子查询修改数据。

• 希望员工scott的岗位、工资、补助与smith员工一样

oracle中事务处理

• 什么是事务
  事务用于保证数据的一致性,它由一组相关的dml语句组成,该组的dml语
  句要么全部成功，要么全部失败。
  如:网上转账就是典型的要用事务来处理，用以保证数据的一致性。
• 事务和锁
  当执行事务操作时(dml语句）,oracle会在被作用的表上加锁,防止其它
  用户改表表的结构.这里对我们用户来讲是非常重要的。
• 提交事务
  当执使用commit语句可以提交事务.当执行了commit语句子后,会确认
  事务的变化、结束事务、删除保存点、释放锁，当使用commit语句结
  束事务子后，其它会话将可以查看到事务变化后的新数据

oracle中事务处理

• 回退事务
  在介绍回退事务前，我们先介绍一下保存点(savepoint)的概念和作用.
  保存点是事务中的一点.用于取消部分事务，当结束事务时，会自动的
  删除该事务所定义的所有保存点.
  当执行rollback时，通过指定保存点可以回退到指定的点,这里我们作图
  说明
• 事务的几个重要操作

1. 设置保存点
   savepoint 保存点名
2. 取消部分事务
   rollback to 保存点名
3. 取消全部事务
   rollback

oracle中事务处理

• java程序中如何使用事务
  在java操作数据库时，为了保证数据的一致性，比如转帐操作(图):
  (1)从一个帐户减掉10$(2)在另一个帐户上加入10$,我们看看如何使用
  事务 -

oracle中事务处理 – 事务隔离级别

• 事务隔离级别
  概念:隔离级别定义了事务与事务之间的隔离程度。
  ANSI/ISO SQL92标准定义了一些数据库操作的隔离级别(这是国际标
  准化组织定义的一个标准而已，不同的数据库在实现时有所不同)：

隔离级别 脏读 不可重复读 幻读
读未提交（Read uncommitted） V V V
读已提交（Read committed） x V V
可重复读（Repeatable read） x x V
可串行化（Serializable ） x x x

V 可能出现 x 不会出现

oracle中事务处理 – 事务隔离级别

• 事务隔离级别
  脏读（dirty read)：当一个事务读取另一个事务尚未提交的修改时，产
  生脏读。
  不可重复读（nonrepeatable read)：同一查询在同一事务中多次进行，
  由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时
  发生非重复读。
  幻读（phantom read)：同一查询在同一事务中多次进行，由于其他
  提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读。

oracle中事务处理 – 事务隔离级别

• oracle的事务隔离级别
  ORACLE提供了SQL92标准中的read committed和serializable，同时
  提供了非SQL92标准的read-only
  ◆ oracle的 read committed 说明：
  ①这是ORACLE缺省的事务隔离级别。
  ②保证不会脏读；但可能出现非重复读和幻像。
  ◆ oracle的 serializable 说明：
  ①serializable就是使事务看起来象是一个接着一个地顺序地执行(从
  效果上可以这样理解)
  ②仅仅能看见在本事务开始前由其它事务提交的更改和在本事务中所
  做的更改
  ③保证不会出现脏读、不可重复读和幻读
  ④Serializable隔离级别提供了read-only事务所提供的读一致性（事
  务级的读一致性），同时又允许DML操作

oracle中事务处理 – 事务隔离级别

• oracle的事务隔离级别
  ◆ oracle的 read only 说明：
  ①遵从事务级的读一致性，仅仅能看见在本事务开始前由其它事务提
  交的更改。
  ②不允许在本事务中进行DML操作。
  ③read only是serializable的子集。它们都避免了不可重复读和幻读。
  区别是在read only中是只读；而在serializable中可以进行DML操作

oracle中事务处理 – 事务隔离级别

• oracle的事务隔离级设置
  ◆ 设置一个事务的隔离级别

◆设置整个会话的隔离级别

oracle中事务处理 – 事务隔离级别

• oracle的事务隔离级--案例

我们举例一个案例来说明oracle的事务隔离级别. 以对emp表进行
操作为例。

sqlplus控制台 sqlplus控制台

oracle中事务处理 – 事务隔离级别

• oracle的事务隔离级在java程序中如何使用

connection.setTransactionIsolation(Connection.TRANSACTION_REA
D_COMMITTED);

特别说明:
①java程序中Connection.XXXXXX有五个，但不是所有的数据库对有
对应的五个事务隔离级别实现。
②在实际工作中，我们极少去修改各个数据库默认的隔离级别。

oracle中事务处理 — 只读事务

• 只读事务实际运用案例
  只读事务是指只允许执行查询的操作，而不允许执行任何其它dml操作
  的事务,使用只读事务可以确保用户只能取得某时间点的数据。假定机
  票代售点每天 18 点开始统计今天的销售情况，这时可以使用只读事务.
  在设置了只读事务后，尽管其它会话可能会提交新的事务,但是只读事
  务将不会取得最新数据的变化,从而可以保证取得特定时间点的数据信
  息。(图示)
• 设置只读事务
  set transaction read only

sql函数的使用 — 字符函数

• 介绍
  字符函数是oracle中最常用的函数，我们来看看有哪些字符函数:
  ◆ replace(char1,search_string,replace_string)
  ◆ instr(char1,char2,[,n[,m]])取子串在字符串的位置
• 显示所有员工的姓名,用”我是A”替换所有"A“

sql函数的使用 — 数学函数

• 介绍
  数学函数的输入参数和返回值的数据类型都是数字类型的.数学函数包
  括cos,cosh,exp,ln,log,sin,sinh,sqrt,
  tan, tanh,acos,asin,atan,round,我们讲最常用的：
  ◆ round(n,[m])
  ◆ trunc(n,[m])
  ◆ mod(m,n)
  ◆ floor(n)
  ◆ ceil(n)
  对数字的处理，在财务系统或银行系统中用的最多，不同的处理方法，
  对财务报表有不同的结果。

sql函数的使用 — 数学函数

• 介绍
  ◆ round(n,[m]) 该函数用于执行四舍五入,如果省掉m，则四舍五入到整
  数;如果m是正数,则四舍五入到小数点的m位后.如果m是负数,则四舍五
  入到小数点的m位前
  ◆ trunc(n,[m]) 该函数用于截取数字.如果省掉m，就截去小数部分,如果
  m是正数就截取到小数点的m位后,如果m是负数,则截取到小数点的前m
  位
  ◆ mod(m,n)
  ◆ floor(n) 返回小于或是等于n的最大整数
  ◆ ceil(n) 返回大于或是等于n的最小整数
  案例数据: 2345.56 45.94
• 显示在一个月为 30 天的情况所有员工的日薪金,忽略余数.

sql函数的使用 — 数学函数

• 介绍
  其它的数学函数,有兴趣的同学可以自己去看看:
  abs(n) 返回数字n的绝对值
  select abs(-13) from dual;
  acos(n) :返回数字的反余旋值
  asin(n): 返回数字的反正旋值
  atan(n): 返回数字的反正切
  cos(n)
  exp(n): 返回e的n次幂
  log(m,n)返回对数值
  power(m,n):返回m的n次幂

sql函数的使用 — 日期函数

• 介绍
  日期函数用于处理date类型的数据.
  默认情况下日期格式是dd-mon-yy 即 12 - 7 月- 78
  (1)sysdate: 该函数返回系统时间
  (2)add_months(d,n)
  (3)last_day(d):返回指定日期所在月份的最后一天
• 查找已经入职 8 个月多的员工
• 显示满 10 年服务年限的员工的姓名和受雇日期.
• 对于每个员工,显示其加入公司的天数.
• 找出各月倒数第 3 天受雇的所有员工.

sql函数的使用 — 转换函数

• 介绍
  转换函数用于将数据类型从一种转为另外一种.在某些情况下，oracle
  server允许值的数据类型和实际的不一样，这时oracle server会隐含的
  转化数据类型，比如:
  create table t1(id number);
  insert into t1 values(’ 10 ’) -->这样oracle会自动的将'10'-->10
  create table t2 (id varchar2(10));
  insert into t2 values(1); -->这样oracle 就会自动的将 1 --->'1';
  我们要说的是尽管oracle可以进行隐含的数据类型的转换,但是它
  并不适应所有的情况，为了提高程序的可靠性,我们应该使用转换函
  数进行转换

sql函数的使用 — 转换函数

• to_char
  你可以使用 select ename,hiredate,sal from emp where deptno=10;
  显示信息，可是，在某些情况下，这个并不能满足你的需求。
• 日期是否可以显示时/分/秒
• 薪水是否可以显示指定的货币符号

yy: 两位数字的年份 2004 -->04
yyyy: 四位数字的年份 2004 年
mm :两位数字的月份 8 月-->08
dd: 2位数字的天 30 号-->30
hh24: 8点－－》 20
hh12: 8点－－》 08
mi、ss -->显示分钟\秒

9:显示数字,并忽略前面 0
0:显示数字,如位数不足,则用 0 补齐
.：在指定位置显示小数点
,: 在指定位置显示逗号
$: 在数字前加美元
L: 在数字前加本地货币符号
C: 在数字前加国际货币符号
G:在指定位置显示组分隔符、
D:在指定位置显示小数点符号(.)
select ename,to_char(sal,'L99G999D99') from emp ;

sql函数的使用 — 转换函数

• 小练习(to_char)

• 显示 1980 年入职的所有员工

• 显示所有 12 月份入职的员工

• to_date
  函数to_date用于将字符串转换成date类型的数据.

• 能否按照中国人习惯的方式年-月-日添加日期

sql函数的使用 — 系统函数

• sys_context

1. terminal ：当前会话客户所对应的终端的标识符
2. lanuage: 语言
3. db_name: 当前数据库名称
4. nls_date_format:当前会话客户所对应的日期格式
5. session_user: 当前会话客户所对应的数据库用户名
6. current_schema: 当前会话客户所对应的默认方案名？
7. host: 返回数据库所在主机的名称
   通过该函数，可以查询一些重要信息，比如你怎在使用哪个数据库 -

select sys_context('userenv','db_name') from dual;

Linux公社（LinuxIDC.com）

• Linux公社（LinuxIDC.com）于 2006 年 9 月 25 日注

册并开通网站，Linux现在已经成为一种广受关注

和支持的一种操作系统，IDC是互联网数据中心，

LinuxIDC就是关于Linux的数据中心。

• Linux公社是专业的Linux系统门户网站，实时发

布最新Linux资讯，包括Linux、Ubuntu、Fedora、

RedHat、红旗Linux、Linux教程、Linux认证、

SUSE Linux、Android、Oracle、Hadoop等技术。

oracle第 4 讲

1. 维护数据的完整性
2. 序列(sequence)
3. 管理索引
4. 管理权限和角色

维护数据的完整性

• 介绍
  数据的完整性用于确保数据库数据遵从一定的商业的逻辑规则。
  在oracle中，数据完整性可以使用约束、触发器、应用程序(过程、
  函数)三种方法来实现，在这三种方法中，因为约束易于维护，并
  且具有最好的性能，所以作为维护数据完整性的首选.

维护数据的完整性

• 约束
  约束用于确保数据库数据满足特定的商业规则。在oracle中，约
  束包括: not null、unique,primary key,foreign key,和check 五种.

维护数据的完整性

• not null(非空)
  如果在列上定义了not null,那么当插入数据时，必须为列提供数据。
• unique(唯一)
  当定义了唯一约束后，该列值是不能重复的.但是可以为null。
• primary key(主键)
  用于唯一的标示表行的数据,当定义主键约束后，该列不但不能重复
  而且不能为null。
  需要说明的是:一张表最多只能有一个主键,但是可以有多个unqiue约
  束。
• foreign key(外键)
  用于定义主表和从表之间的关系.外键约束要定义在从表上，主表则
  必须具有主键约束或是unique约束.,当定义外键约束后，要求外键列
  数据必须在主表的主键列存在或是为null

维护数据的完整性

• check
  用于强制行数据必须满足的条件,假定在sal列上定义了check约束,并要求
  sal列值在 1000 ～ 2000 之间如果不再 1000 ～ 2000 之间就会提示出错。

维护数据的完整性

• 商店售货系统表设计案例(1)
  现有一个商店的数据库，记录客户及其购物情况，由下面三个表组成：
  商品goods（商品号goodsId，商品名goodsName，单价unitprice，商
  品类别category，供应商provider);
  客户customer（客户号customerId,姓名name,住址address,电邮email
  性别sex,身份证cardId);
  购买purchase（客户号customerId,商品号goodsId,购买数量nums);
  请用SQL语言完成下列功能：
  1 建表，在定义中要求声明：
  (1)每个表的主外键；
  (2)客户的姓名不能为空值；
  (3)单价必须大于 0 ，购买数量必须在 1 到 30 之间；
  (4)电邮不能够重复;
  (5)客户的性别必须是 男或者 女,默认是男

维护数据的完整性 — 维护

• 商店售货系统表设计案例(2)
  如果在建表时忘记建立必要的约束，则可以在建表后使用
  alter table命令为表增加约束.但是要注意: 增加not null约束
  时,需要使用modify选项,而增加其它四种约束使用add选项。
  (1)每个表的主外码；
  (2)客户的姓名不能为空值；--增加商品名也不能为空
  (3)单价必须大于 0 ，购买数量必须在 1 到 30 之间；
  (4)电邮不能够重复;--增加身份证也不重复
  (5)客户的性别必须是 男 或者 女,默认是男
  (6)增加客户的住址只能是‘海淀’、‘朝阳’、‘东城’、
  ‘西城’、‘通州’、‘崇文’

维护数据的完整性 — 维护

• 删除约束
  当不再需要某个约束时，可以删除.
  alter table 表名 drop constraint 约束名称;

在删除主键约束的时候，可能有错误,比如：
alter table 表名 drop primary key ;
这是因为如果在两张表存在主从关系，那么在删除主表的主键
约束时，必须带上 cascade 选项 如象
alter table 表名 drop primary key cascade;

维护数据的完整性 — 维护

• 列级定义
  列级定义是在定义列的同时定义约束。
• 表级定义
  表级定义是指在定义了所有列后，再定义约束.这里需要注意:
  not null约束只能在列级上定义。

序列(sequence)

• 一个问题
  在某张表中，存在一个 id 列 ( 整数 ), 我们希望在添加记录的时候，
  该列从 1 开始，自动的增长，怎么处理 -

序列(sequence)

• 介绍
  oracle中，是通过使用序列(sequence)来处理自动增长列。
  (1) 可以为表中的列自动产生值.
  (2) 由用户创建数据库对象,并可由多个用户共享.
  (3) 一般用于主键或唯一列.
• 案例说明

序列(sequence)

• 细节说明
  一旦定义了某个序列，你就可以用CURRVAL，NEXTVAL
  CURRVAL：返回 sequence的当前值
  NEXTVAL：增加sequence的值，然后返回 sequence 值
  比如：
  序列名.CURRVAL
  序列名.NEXTVAL

什么时候使用sequence：

• 不包含子查询、snapshot、VIEW的 SELECT 语句
• INSERT语句的子查询中
• INSERT语句的VALUES中
• UPDATE 的 SET中

序列(sequence)

• 细节说明
  可以看如下例子：
  INSERT INTO emp VALUES
  (my_seq.nextval, 'TOMCAT', 'CLERK', 7566 , SYSDATE, 1200,
  NULL,20);
  SELECT my_seq.currval FROM DUAL;
  但是要注意的是：
  第一次NEXTVAL返回的是初始值；随后的NEXTVAL会自动增加你定义的
  INCREMENT BY值，然后返回增加后的值。CURRVAL 总是返回当前
  SEQUENCE的值，但是在第一次NEXTVAL初始化之后才能使用CURRVAL，
  否则会出错。一次NEXTVAL会增加一次SEQUENCE的值，所以如果你在
  同一个语句里面使用多个NEXTVAL，其值就是不一样的。如果指定
  CACHE值，ORACLE就可以预先在内存里面放置一些sequence，这样存
  取的快些。cache里面的取完后，oracle自动再取一组到cache。 使用
  cache或许会跳号， 比如数据库突然不正常down掉（shutdown
  abort),cache中的sequence就会丢失. 所以可以在create sequence的时
  候用nocache防止这种情况。

管理索引 — 原理介绍

• 介绍
  索引是用于加速数据存取的数据对象.合理的使用索引可以大大降
  低i/o次数,从而提高数据访问性能。索引有很多种我们主要介绍常
  用的几种:

为什么添加了索引后，会加快查询速度呢 -

管理索引 — 创建索引

• 单列索引
  单列索引是基于单个列所建立的索引,语法:

• 复合索引
  复合索引是基于两列或是多列的索引。在同一张表上可
  以有多个索引，但是要求列的组合必须不同,语法:

create index index_name
on table( columnname,columnname... );

create index index_name
on table( columnname );

管理索引 — 使用原则

• 使用原则

①在大表上建立索引才有意义
②在where子句或是连接条件上经常引用的列上建立索引
③索引的层次不要超过 4 层

这里能不能给学生演示这个效果呢 - 如何构建一个大表呢 -

管理索引 — 索引的缺点

• 索引缺点分析
  索引有一些先天不足：
  1 ：建立索引，系统要占用大约为表的1.2倍的硬盘和内存空间来
  保存索引。
  2 ：更新数据的时候，系统必须要有额外的时间来同时对索引进行
  更新，以维持数据和索引的一致性。实践表明，不恰当的索引不
  但于事无补，反而会降低系统性能。因为大量的索引在进行插入、
  修改和删除操作时比没有索引花费更多的系统时间。
  比如在如下字段建立索引应该是不恰当的：
  1 、很少或从不引用的字段；
  2 、逻辑型的字段，如男或女(是或否)等。综上所述，提高查询效
  率是以消耗一定的系统资源为代价的，索引不能盲目的建立，这
  是考验一个DBA是否优秀的很重要的指标

管理索引 — 其它索引

• 介绍
  按照数据存储方式，可以分为B树、反向索引、位图索引;
  按照索引列的个数分类，可以分为单列索引、复合索引；
  按照索引列值的唯一性，可以分为唯一索引和非唯一索引.
  此外还有函数索引，全局索引，分区索引...
  对于索引我还要说:
  在不同的情况我们会在不同的列上建立索引，甚至建立不同种类
  的索引,请记住，技术是死的，人是活的。比如：
  B-树索引建立在重复值很少的列上，而位图索引则建立在重复值
  很多、不同值相对固定的列上。

管理权限和角色

• 介绍
  这一部分我们主要看看oracle种如何管理权限和角色，权限和角色
  的区别在那里。
  当刚刚建立用户时，用户没有任何权限，也不能执行任何操作。
  如果要执行某种特定的数据库操作，则必须为其授予系统的权限；
  如果用户要访问其它方案的对象，则必须为其授予对象的权限.为
  了简化权限的管理，可以使用角色。这里我们会详细的介绍

管理权限和角色

• 权限

权限是指执行特定类型sql命令或是访问其它方案对象的权利，包
括系统权限和对象权限两种:

管理权限和角色 — 系统权限

• 系统权限介绍
  系统权限是指执行特定类型sql命令的权利.它用于控制用户可以执行的
  一个或是一组数据库操作.比如当用户具有create table权限时，可以在
  其方案中建表,当用户具有create any table权限时,可以在任何方案中建
  表.oracle提供了 100 多中系统权限。常用的有：
  create session 连接数据库 create table 建表
  create view 建视图 create public synonym 键同义词
  create procedure 建过程、函数、包create trigger 建触发器
  create cluster 建簇
• 显示系统权限
  oracel提供了 100 多系统权限，而且oracle的版本越高，提供的系统权限
  就越多,我们可以查询数据字典视图system_privilege_map，可以显示
  所有系统权限
  select * from system_privilege_map order by name;

管理权限和角色 — 系统权限

• 授予系统权限
  一般情况，授予系统权限是有dba完成的，如果用其它用户来授予系
  统权限，则要求该用户必须具有grant any privilege的系统权限在授
  予系统权限时，可以带有with admin option选项，这样，被授予权限
  的用户或是角色还可以将该系统权限授予其它的用户或是角色。为
  了让大家快速入门，我们举例说明:
  1.创建两个用户ken , tom.初始阶段他们没有任何权限，如果登陆就
  会给出错误的信息
  1.1创建两个用户，并指定密码.
  2.给用户ken授权:
  2.1:授予create session 和create table权限时 带with admin option
  2.2 授予create view 时不带with admin option

管理权限和角色 — 系统权限

3.给用户tom授权
我们可以通过ken 给tom授权，因为with admin option是加上的。
当然也可以通过dba给tom授权,我们就用ken给tom授权:
① grant create session,create table to tom;
② grandt create view to tom; ok吗 - [不ok]

• 回收系统权限
  一般情况下，回收系统权限是dba来完成的，如果其它的用户来回收系
  统权限，要求该用户必须具有相应系统权限及转授系统权限的选项(with
  admin option）。回收系统权限使用revoke来完成,当回收了系统权限后，
  用户就不能执行相应的操作了，但是请注意，系统权限级联收回问题？
  [不是级联回收!]
  sys--------------->ken------------->tom
  (create session) (create session)(create session)
  用system执行如下操作:
  revoke create session from ken; 请思考tom还能登录？

管理权限和角色 — 对象权限

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

管理权限和角色 — 对象权限

• 授予对象权限
  在oracle9i前，授予对象权限是由对象的所有者来完成的，如果用
  其它的用户来操作，则需要用户具有相应的(with grant option )权
  限,从oracle9i开始,dba,sys,system 可以将任何对象上的对象权限
  授予其它用户.授予对象权限是用grant命令来完成的.

我们看几个案例:
1.monkey用户要操作scott.emp表,则必须授予相应的对象权限
①希望monkey可以查询scott.emp的表数据，怎样操作 -
②希望monkey可以修改scott.emp的表数据，怎样操作 -
③希望monkey可以删除scott.emp的表数据，怎样操作 -
④有没有更加简单的方法,一次把所有权限赋给monkey -

grant 对象权限 on 数据库对象
to 用户名 [, 角色名 ][,public] [ with grant option ]

管理权限和角色 — 对象权限

2.能否对monkey访问权限更加精细控制.(授予列权限)
①希望monkey只可以修改scott.emp的表的sal字段,怎样操作 -
②希望monkey只可查询scott.emp的表的ename,sal数据,怎样
操作 -
3.授予alter权限
如果black用户要修改scott.emp表的结构,则必须授予alter对象权
限
4.授予execute权限
如果用户想要执行其它方案的包/过程/函数,则须有execute
权限.比如为了让ken可以执行包dbms_transaction,可以授
execute权限

管理权限和角色 — 对象权限

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

revoke 对象权限 on 数据库对象
from 用户名 [, 角色名 ][,public]

管理权限和角色 — 角色

• 介绍
  角色就是相关权限的命令集合,使用角色的主要目的就是为了简化权限
  的管理.假定有用户1,2,3为了让他们都拥有权限
  ①连接数据库
  ②在scott.emp表上select,insert,update,,,,
  如果采用直接授权操作，则需要进行 12 次授权。

因为要进行 12 次授
权操作,所以比较
麻烦喔! 怎么办 -

管理权限和角色 — 角色

• 介绍
  我们如果采用角色就可以简化：
  首先将create session , select on scott.emp, insert on
  scott.emp,update on scott.emp授予角色，然后将该角色授予
  a,b,c用户，这样就可以三次授权搞定.

角色分为预定义和自定义角色两类：

可以考虑使用自
定义角色来解决
问题的。

管理权限和角色 — 角色

• 预定义角色
  预定义角色是指oracle所提供的角色,每种角色都用于执行一些特定的
  管理任务,下面我们介绍常用的预定义角色connect,resource,dba
  (一)connect角色
  connect角色具有一般应用开发人员需要的大部分权限,当建立了一个
  用户后,多数情况下,只要给用户授予connect和resource角色就够了,
  那么，connect角色具有哪些系统权限呢 -
  alter session create cluster create database link
  create sesssion create table create view create sequence

管理权限和角色 — 角色

• 预定义角色
  (二)resource角色
  resource角色具有应用开发人员所需要的其它权限,比如建立存储过程、
  触发器等。这里需要注意的是resource角色隐含了unlimited
  tablespace系统权限。
  resource角色包含以下系统权限:
  create cluster
  create indextype
  create table
  create sequence
  create type
  create procedure
  create trigger

管理权限和角色 — 角色

• 预定义角色
  (三)dba角色
  dba角色具有所有的系统权限，及with admin option选项,默认的
  dba用户为sys和system他们可以将任何系统权限授予其它用户.但是要
  注意的是dba角色不具备sysdba和sysoper的特权(启动和关闭数据库)

管理权限和角色 — 角色

• 自定义角色
  顾名思义就是自己定义的角色，根据自己的需要来定义.一般是dba来
  建立，如果用的别的用户来建立，则需要具有create role的系统权限.
  在建立角色时可以指定验证方式(不验证，数据库验证等)
  (一)建立角色(不验证)
  如果角色是公用的角色，可以采用不验证的方式建立角色.
  create role 角色名not identified;
  (二)建立角色(数据库验证)
  采用这样的方式时,角色名、口令存放在数据库中。当激活该角色时，
  必须提供口令.在建立这种角色时，需要为其提供口令
  create role 角色名identified by shunping

管理权限和角色 — 角色

• 角色授权
  当建立角色时，角色没有任何权限,为了使得角色完成特定任务，必须
  为其授予相应的系统权限和对象权限。
  (一)给角色授权
  给角色授予权限和给用户授权没有太多区别，但是要注意，系统权限
  的unlimited tablespace 和对象权限的with grant option 选项是不
  能授予角色的。

grant 对象权限 on 数据库对象
to 自定义角色名 [with admin option]

练习:

1. 用system 给某个自定义角色授予create session权限,要求该权限可以转授
   2.用scott用于给某个自定义角色授予查询权限

管理权限和角色 — 角色

(二)分配角色给某个用户
一般分配角色是由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 角色名

管理权限和角色 — 角色

• 显示角色信息
  ①显示所有角色
  sql>select * from dba_roles;
  ②显示角色具有的系统权限
  sql>select privilege,admin_option from role_sys_privs where
  role=‘角色名';
  ③显示角色具有的对象权限
  通过查询数据字典视图dba_tab_privs可以查看角色具有的对象权限或
  是列的权限。
  ④显示用户具有的角色，及默认角色
  当以用户的身份连接到数据库时，oracle会自动的激活默认的角色,通过查询
  数据字典视图dba_role_privs可以显示某个用户具有的所有角色及当前默认的
  角色 sql>select granted_role,default_role from dba_role_privs where
  grantee=‘角色名';

管理权限和角色 — 角色

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

简单了解,不详细介绍了

Linux公社（LinuxIDC.com）

• Linux公社（LinuxIDC.com）于 2006 年 9 月 25 日注

册并开通网站，Linux现在已经成为一种广受关注

和支持的一种操作系统，IDC是互联网数据中心，

LinuxIDC就是关于Linux的数据中心。

• Linux公社是专业的Linux系统门户网站，实时发

布最新Linux资讯，包括Linux、Ubuntu、Fedora、

RedHat、红旗Linux、Linux教程、Linux认证、

SUSE Linux、Android、Oracle、Hadoop等技术。

oracle第 5 讲

1. pl/sql的介绍
2. pl/sql的基础

pl/sql的介绍

• pl/sql是什么
  pl/sql(procedural language/sql) 是oracle在标准的sql语言上的扩展.
  pl/sql不仅允许嵌入sql语言，还可以定义变量和常量，允许使用条件
  语句和循环语句，允许使用例外处理各种错误,这样使得它的功能变
  得更加强大。[图]
• 学习必要性
  ①提高应用程序的运行性能
  ②模块化的设计思想[分页的过程，订单的过程，转账的过程..]
  ③减少网络传输量
  ④提高安全性
• 缺点
  移植性不好.

pl/sql的介绍 — 用什么开发pl/sql

• sqlplus开发工具
  sqlplus是oracle公司提供的一个工具，这个因为我们在以前介绍过的:
  举一个简单案例:
  编写一个存储过程，该过程可以向某表中添加记录.

• pl/sql developer开发工具
  pl/sql developer 是用于开发pl/sql块的集成开发环境(ide), 他是一个
  独立的产品，而不是oracle的一个附带品。
  举一个简单案例:
  编写一个存储过程，该过程可以删除某表记录.

pl/sql基础知识 — 介绍

• 介绍
  开发人员使用pl/sql编写应用模块时，不仅需要掌握sql语句的编写方
  法，还要掌握pl/sql语句及语法规则。pl/sql编程可以使用变量和逻辑
  控制语句，从而可以编写非常有用的功能模块。

比如:分页存储过程模块、订单处理存储过程模块、转账存储过程模
块.. 而且如果使用pl/sql编程，我们可以轻松的完成非常复杂的查询
要求.

pl/sql基础知识 — pl/sql可以做什么

• 简单分类

|-----过程(存储过程)
|
|-----函数
块(编程)-------|
|-----触发器
|
|-----包

pl/sql基础知识 — 编写规范

• 编写规范
  ①注释
  单行注释 --
  多行注释
  /..../来划分
  ②标识符号的命名规范
  1)当定义变量时，建议用v_作为前缀v_sal
  2)当定义常量时,建议用c_作为前缀c_rate
  3)当定义游标时,建议用_cursor作为后缀emp_cursor;
  4)当定义例外时，建议用e_作为前缀e_error

pl/sql基础知识 — pl/sql块介绍

• 介绍
  块(block)是pl/sql的基本程序单元，编写pl/sql程序实际上就是编写
  pl/sql块。要完成相对简单的应用功能，可能只需要编写一个pl/sql块；
  但是如果要想实现复杂的功能，可能需要在一个pl/sql块中嵌套其它
  的pl/sql块.

pl/sql基础知识 — pl/sql块结构

• 块结构示意图
  pl/sql块由三个部分构成:定义部分、执行部分、例外处理部分.如下所示:
  declear
  /定义部分-----定义常量、变量、游标、例外、复杂数据类型/
  begin
  /执行部分-----要执行的pl/sql语句和sql语句/
  exception
  /例外处理部分----处理运行的各种错误/
  end;

★可以和java编程结构做一个简单的比较

定义部分是从declare开始的，该部分是
可选的。执行部分是从begin开始的,该部
分是必须的。例外处理部分是从
exception开始的，该部分是可选的

pl/sql基础知识 — pl/sql实例

• 实例 1 - 只包括执行部分的pl/sql块

☞相关说明:
dbms_output是oracle所提供的包（类似java的开发包），该包包含
一些过程，put_line就是dbms_output包的一个过程。

案例：输出hello,world

pl/sql基础知识 — pl/sql实例

• 实例 2 - 包含定义部分和执行部分的pl/sql块

☞相关说明:
& 表示要接收从控制台输入的变量
|| 表示把两个串拼接

案例：根据用户输入的雇员编号，显示该雇员的名字

pl/sql基础知识 — pl/sql实例

• 实例 3 - 包含定义部分、执行部分和例外处理部分
  为了避免pl/sql程序的运行错误，提高pl/sql的健壮性，应该对可能
  的错误进行处理,这个很有必要：

①比如在实例 2 中，如果输入了不存在的雇员号，应当做例外处理.
②有时出现异常，希望用另外的逻辑处理，[网示]

我们看看如何完成①的要求
相关说明:oralce事先预定义了一些例外，no_data_found 就是找不
到数据的例外.

pl/sql基础知识 — 过程快速入门

• 过程
  过程用于执行特定的操作.当建立过程时，既可以指定输入参数(in),也可
  以指定输出参数(out).通过在过程中使用输入参数，可以将数据传递到
  执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境.
  在sqlplus中可以使用create procedure 命令来建立过程。
  实例如下:
  ①请考虑编写一个过程，可以输入雇员名,新工资可修改雇员的工资
  ②如何调用过程有两种方法:
  exec .. call

pl/sql基础知识 — 过程快速入门

• 过程
  ③如何在java程序中调用一个存储过程
• 如何使用过程返回值。
  特别说明: 对于过程我们会在以后给大家详细具体的介绍，现在请大家
  先有一个概念.

pl/sql基础知识 — 函数快速入门

• 函数
  函数用于返回特定的数据,当建立函数时，在函数头部必须包含return
  子句，而在函数体内必须包含return语句返回的数据。我们可以使用
  create function 来建立函数 ,实际案例:
  create function annual_incomec(name varchar2)
  return number is
  annual_salary number(7,2);
  begin
  select sal*12+nvl(comm,0) into annual_salary from emp where ename=name;
  return annual_salary;
  end;
  /
  在sqlplus 中调用函数
  sql>var income number—定义一个变量income,类型number
  sql>call annual_incomec('SCOTT') into:income;
  sql>print income
  同样我们可以在java 程序中调用该函数
  select annual_income('SCOTT') from dual;//这样
  可以通过rs.getInt(1)得到返回的结果

pl/sql基础知识 — 包

• 包
  包用于在逻辑上组合过程和函数,它由包规范和包体两部分组成。
  ①我们可以使用create package 命令来创建包:
  实例：
  create or replace package sp_packageis
  procedure update_sal(name varchar2,newsal number);
  function annual_income(name varchar2) return number;
  end;
  包的规范只包含了过程和函数的说明，但是没有过程和函数的实现代
  码。包体用于实现包规范中的过程和函数，
  ②建立包体可以使用create package body 命令

pl/sql基础知识 — 包

②建立包体可以使用create package body 命令
create or replace package body sp_packageis
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;

pl/sql基础知识 — 包

③如何调用包的过程或是函数
当调用包的过程或是函数时，在过程和函数前需要带有包名，如果要
访问其它方案的包，还需要在包名前加方案名.
如:
SQL>call sp_package.update_sal('SCOTT',1500);

特别说明:
包是pl/sql中非常重要的部分，我们在使用过程分页时，将会再次体验
它的威力。

pl/sql基础知识 — 触发器

• 触发器简单介绍
  触发器是指隐含的执行的存储过程。当定义触发器时，必须要指定触发
  的事件和触发的操作，常用的触发事件包括insert,update,delete语句，
  而触发操作实际就是一个pl/sql块。可以使用create trigger来建立触发器。

特别说明:
我们会在后面详细为大家介绍触发器的使用，因为触发器是
非常有用的,可维护数据库的安全和一致性。

pl/sql基础知识 — 定义并使用变量

• 介绍
  在编写pl/sql程序时，可以定义变量和常量；在pl/sql程序中
  包括有:
  ①标量类型(scalar)
  ②复合类型(composite)
  ③参照类型(reference)
  ④lob(large object)

pl/sql基础知识 — 定义并使用变量

• 标量(scalar)-常用类型
  在编写pl/sql块时,如果要使用变量,需在定义部分定义变量。
  pl/sql中定义变量和常量的语法如下:
  identifier [constant] datatype [not null] [:=| default expr]
  identifier:名称
  constant:指定常量.需要指定它的初始植,且其值是不能改变的
  datatype:数据类型
  not null:指定变量值不能为null
  := 给变量或是常量指定初始植
  default 用于指定初始植
  expr :指定初始植的pl/sql表达式,可是文本值、其它变量、函数等

举例说明吧

pl/sql基础知识 — 定义并使用变量

• 标量定义的案例
  ①定义一个变长字符串
  v_ename varchar2(10);
  ②定义一个小数 范围-9999.99～9999.99
  v_sal number(6,2);
  ③定义一个小数并给一个初始植为5.4 :=是pl/sql的赋值号
  v_sal2 number(6,2):=5.4
  ④定义一个日期类型的数据
  v_hiredate date;
  ⑥定义一个布尔变量，不能为空，初始植为false
  v_valid boolean not null default false;

pl/sql基础知识 — 定义并使用变量

• 标量(scalar)-使用标量
  在定义好变量后，就可以使用这些变量。这里需要说明的是pl/sql块为
  变量赋值不同于其它的编程语言，需要在等号前加冒号(:=)

案例：以输入员工号，显示雇员姓名、工资、个人所得税(税率
为0.03)为例。说明变量的使用,看看如何编写.

pl/sql基础知识 — 定义并使用变量

• 标量(scalar)-使用%type类型
  对于上面的pl/sql块有一个问题：
  就是如果员工的姓名超过了 5 字符的话，就会有错误，为了降低pl/sql程
  序的维护工作量，可以使用%type属性定义变量，这样它会按照数据库
  列来确定你定义的变量的类型和长度,

我们看看这个怎么使用：
标识符名 表名.列名%type;

pl/sql基础知识 — 定义并使用变量

• 复合变量(composite)-介绍
  用于存放多个值的变量。常用的包括这:
  ①pl/sql记录
  ②pl/sql表

pl/sql基础知识 — 定义并使用变量

• 复合类型-pl/sql记录
  类似与高级语言中的结构体,需要注意的是,当引用pl/sql记录成员时,必须
  要加记录变量作为前缀(记录变量.记录成员)如下：
  declare
  type emp_record_typeis record(
  name emp.ename%type,
  salary emp.sal%type,
  title emp.job%type);
  sp_record emp_record_type;
  begin
  select ename,sal,job into sp_recordfrom emp where empno=7788;
  dbms_output.put_line('员工名:'||sp_record.name);
  end;

pl/sql基础知识 — 定义并使用变量

• 复合类型-pl/sql表
  相当于高级语言中的数组.但是需要注意的是在高级语言中数组的下标不
  能为负数,而pl/sql是可以为负数的,并且表元素的下标没有限制.实例如下
  declare
  type sp_table_typeis table of emp.ename%type
  index by binary_integer;
  sp_table sp_table_type;--定义一个变量:sp_table :类型
  begin
  select ename into sp_table(- 1 )from emp where empno=7788;
  dbms_output.put_line('员工名:'|| sp_table(- 1 ));
  end;
  说明： sp_table_type 是pl/sql表类型
  emp.ename%type 指定了表的元素的类型和长度
  sp_table 为pl/sql表变量
  sp_table(0)则表示下标为 0 的元素

pl/sql基础知识 — 定义并使用变量

• 参照变量-介绍

参照变量是指用于存放数值指针的变量.通过使用参照变量，可以使得应
用程序共享相同对象，从而降低占用的空间。在编写pl/sql程序时，可
以使用游标变量(ref cursor)和对象类型变量,(ref obj_type)两种参照变
量类型

pl/sql基础知识 — 定义并使用变量

• 参照变量-ref cursor游标变量
  使用游标时，当定义游标时不需要指定相应的select语句，但是当使用
  游标时（open时)需要指定select语句，这样一个游标就与一个select语
  句结合了。实例如下:

①请使用pl/sql编写一个块，可以输入部门号，并显示该部门所有员工姓
名和他的工资.

②在①基础上，如果某个员工的工资低于 200 元，就增加 100 元.

Linux公社（LinuxIDC.com）

• Linux公社（LinuxIDC.com）于 2006 年 9 月 25 日注

册并开通网站，Linux现在已经成为一种广受关注

和支持的一种操作系统，IDC是互联网数据中心，

LinuxIDC就是关于Linux的数据中心。

• Linux公社是专业的Linux系统门户网站，实时发

布最新Linux资讯，包括Linux、Ubuntu、Fedora、

RedHat、红旗Linux、Linux教程、Linux认证、

SUSE Linux、Android、Oracle、Hadoop等技术。

oracle第 6 讲

1. pl/sql的进阶

2. oracle的视图

pl/sql进阶 — 控制结构

• 介绍
  在任何计算机语言(c,java,c,c++)都有各种控制语句(条件语句，循环
  结构，顺序控制结构..)在pl/sql中也存在这样的控制结构.

在本部分学习完毕后，希望大家达到:

1. 使用各种if语句
   2 ）使用循环语句
   3 ）使用控制语句---goto 和null;

pl/sql进阶 — 控制结构

• 条件分支语句
  pl/sql中提供了三种条件分支语句if -- then, if --then---else, if---then--

• elsif---else
  ★这里我们可以和java语句进行一个比较

• 简单的条件判断if – then

• 编写一个过程，可以输入一个雇员名，如果该雇员的工资低于
  2000,就给该雇员工资增加10%

pl/sql进阶 — 控制结构

• 二重条件分支 if — then--else

• 编写一个过程，可以输入一个雇员名,如果该雇员的补助不是 0 就在原来
  的基础上增加100;如果补助为 0 就把补助设为200;

• 多重条件分支 if--then--elsif--else

• 编写一个过程，可以输入一个雇员编号,如果该雇员的职位是
  PRESIDENT 就给他的工资增加1000,如果该雇员的职位是MANAGER
  就给他的工资增加500,其它职位的雇员工资增加200.

pl/sql进阶 — 控制结构

• 循环语句 -loop
  是pl/sql中最简单的循环语句,这种循环语句以loop开头，以end loop
  结尾,这种循环至少会被执行一次.
  案例:现有一张表users，表结构如下

请，编写一个过程，可输入用户名，并循环添加 10 个用户到users表
中，用户编号从 1 开始增加.

pl/sql进阶 — 控制结构

• 循环语句 – while循环
  基本循环至少要执行循环体一次，而对于while循环来说，只有条
  件为true时,才会执行循环体语句,while循环以while..loop 开始，以
  end loop结束
  案例:现有一张表users，表结构如下

请，编写一个过程，可输入用户名，并循环添加 10 个用户到users
表中，用户编号从 11 开始增加.

pl/sql进阶 — 控制结构

• 循环语句–for循环
  基本for循环的基本结构如下
  begin
  for iin reverse 1..10 loop
  insert into users values(i,’顺平’);
  end loop;
  end;
  /
  我们可以看到控制变量i，在隐含中就在不停的增加

推荐使用loop 循环结构，不推荐使用for循环

pl/sql进阶 — 控制结构

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

pl/sql进阶 — 控制结构

• 顺序控制语句-goto ,null
  ②null
  null 语句不会执行任何操作，并且会直接将控制传递到下一条
  语句。使用null语句的主要好处是可以提高pl/sql的可读性。

举一个简单的案例

pl/sql进阶 — 编写分页过程

• 介绍
  分页是任何一个网站(bbs、网上商城、blog)都会使用到的技术，因
  此学习pl/sql编程开发就一定要掌握该技术.

pl/sql进阶 — 编写分页过程

• 无返回值的存储过程
  古人云：欲速则不达，为了让大家伙比较容易接受分页过程编写，我还
  是从简单到复杂，循序渐进的给大家讲解。首先是掌握最简单的存储过
  程，无返回值的存储过程:
  案例：现有一张表book ,表结构如下：

请编写一个过程，可以向book表添加书,要求通过java程序调用该过程.

字段名 字段类型
id number(5)
name varchar2(100)
pubHouse varchar2(100)

pl/sql进阶 — 编写分页过程

• 有返回值的存储过程（非列表）
  再看如何处理有返回值的存储过程:
  案例：编写一个过程，可以输入雇员的编号，返回该雇员的姓名。

案例扩展:编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工
资、和岗位。

pl/sql进阶 — 编写分页过程

• 有返回值的存储过程（列表[结果集]）
  案例：编写一个过程，输入部门号，返回该部门所有雇员信息。对该题
  分析如下:
  由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替
  代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必
  须要用pagkage了,步骤如下：
  ①建一个包。
  ②建立存储过程。
  ③下面看看如何在java程序中调用

pl/sql进阶 — 编写分页过程

• 编写分页过程
  有了上面的基础，相信大家可以完成分页存储过程了。
  要求，请大家编写一个存储过程，要求可以输入表名、每页显示记录数、
  当前页,排序字段(dept降序)。返回总记录数，总页数，和返回的结果集.

如果大家忘了oracle中如何分页，请参考第三天的内容.
先自己完成，老师在后面给出答案，并讲解.

温馨提示

pl/sql进阶 — 例外处理

• 例外的基本概念
  在PL/SQL 的执行过程中发生异常时系统所作的处理称为一个例外情况
  (exception)。通常例外情况的种类有三种：

• 预定义的ORACLE 例外情况ORACLE 预定义的例外情况大约有 24 个，对于这种
  例外情况无须在程序中定义，由ORACLE 自动地触发。(重点)

• 非预定义的ORACLE 例外情况由使用者增加定义例外情况，然后ORACLE 自动
  将其触发执行。

• 自定义例外，这个用的较少。
  Exception
  When ＜异常情况名＞ｔｈｅｎ
  ＜异常处理代码＞
  When ＜异常情况名＞ｔｈｅｎ
  ＜异常处理代码＞
  ......
  ｗｈｅｎ ｏｔｈｅｒｓ ｔｈｅｎ
  ＜异常处理代码＞

pl/sql进阶 — 例外处理

• 例外传递
  如果不处理例外我们看看会出现什么情况:
  案例，编写一个过程，可接收雇员的编号，并显示该雇员的姓名.

问题是，如果输入的雇员编号不存在，怎样去处理呢 -

pl/sql进阶 — 例外处理

• 常用的预定义例外

ORACLE 预定之例外情况的处理,下列出常见几个

例外情况名 错误代码 描述
NO_DATA_FOUND ORA- 01403 对于SELECT 叙述没有传回任何值。
TOO_MANY_ROWS ORA- 01427 只允许传回一笔记录的SELECT 叙述结果却多于一笔。
INVALID_CURSOR ORA- 01001 使用非法的的光标操作。
VALUE_ERROR ORA- 06502 出现数值、数据形态转换、撷取字符串或强制性的错误。
INVALID_NUMBER ORA- 01722 字符串到数值的转换失败。
ZERO_DIVIDE ORA- 01476 被零除。
DUP_VAL_ON_INDEX ORA- 00001 试图向具有唯一键值的索引中插入一个重复键值。
CASE_NOT_FOUND ORA-xxxxx 没有case条件匹配
CURSOR_NOT_OPEN ORA-xxxxxx 游标没有打开

pl/sql进阶 — 例外处理

• 预定义例外 case_not_found
  在开发pl/sql块中编写case语句时，如果在when子句中没有包含必须
  的条件分支，就会触发case_not_found的例外
• 预定义例外 zero_divide
  当执行2/0 语句时,则会触发该例外。
• 预定义例外 no_data_found
  下面是一个pl/sql块,当执行select into 没有返回行，就会触发该例外
• 预定义例外 too_many_rows
  当执行select into 语句时，如果返回超过了一行,则会触发该例外。

对上面的每个例外，我们都给大家举一个案例说明:

pl/sql进阶 — 例外处理

• 如何处理多个的例外
  set serveroutput on;
  declare
  var_name varchar(60);
  begin
  select ename into var_name from emp
  where deptno='10';
  exception
  when no_data_found then
  dbms_output.put_line(' 没有匹配数据！');
  when too_many_rows then
  dbms_output.put_line('返回多行数据!');
  when others then
  dbms_output.put_line('提示 错误不明！');
  end;

oracle视图

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

oracle视图

• 视图与表的区别

①表需要占用磁盘空间，视图不需要
②视图不能添加索引
③使用视图可以简化复杂查询
比如:学生选课系统
④视图用利于提高安全性
比如:不同用户查看不同视图

oracle视图

• 创建视图
  create view视图名as select语句[with read only]

• 创建或修改视图
  create or replace view视图名as select语句[with read only]

• 删除视图
  drop view 视图名

当表结构过于
复杂，请使用
视图吧!

Linux公社（LinuxIDC.com）

• Linux公社（LinuxIDC.com）于 2006 年 9 月 25 日注

册并开通网站，Linux现在已经成为一种广受关注

和支持的一种操作系统，IDC是互联网数据中心，

LinuxIDC就是关于Linux的数据中心。

• Linux公社是专业的Linux系统门户网站，实时发

布最新Linux资讯，包括Linux、Ubuntu、Fedora、

RedHat、红旗Linux、Linux教程、Linux认证、

SUSE Linux、Android、Oracle、Hadoop等技术。

oracle第 7 讲-触发器

触发器-引入

• 几个问题

请大家考虑一个需求：当一个用户登录到oracle时，在一张
表中记录登录到oracle的用户名和登录时间等信息，怎么办 -
再一个需求: 禁止用户在星期天对某一张表进行删除操作，
怎么办 -
再一个需求: 当用户在删除一张表的时候,自动把删除的记录
备份到另外一张表中....

触发器 — 解决之道

• 解决之道

很多关系数据库中都提供一种技术，可以在用户进行某种操作
的时候，自动的进行另外一个操作，我们把这种技术称为触发
器技术.

触发器是指存放在数据库中，被隐含执行的存储过程,可以支持
dml触发器，还支持基于系统事件(启动数据库,关闭数据库,登陆)
和ddl操作建立触发器。

触发器 — 介绍

• 介绍

当发生特定事件时,（如修改表，建立对象，登陆到数据
库),oracle就会自动的去执行相应的代码.

触发器分类：

dml触发器、系统事件触发器、ddl触发器

触发器由触发事件，触发条件，触发操作三个部分构成.

触发器 — 创建语法

• 语法介绍

CREATE [OR REPLACE]TRIGGERtrigger_name
{BEFORE| AFTER}
{INSERT | DELETE| UPDATE [OFcolumn[, column...]]}
ON [schema.]table_name
[FOR EACHROW]
[WHENcondition]
BEGIN
trigger_body;
END;

触发器 — dml触发器

• 快速入门 1

在某张表(my_emp) 添加一条数据的时候，提示 ‘添加了一条数据’

在某张表(my_emp) 修改多条数据的时候，提示 多次‘修改了数据’

• 行级触发器和语句级触发器的区别

在创建触发器的时候，带不带for each row

触发器 — dml触发器

• 快速入门 2

为了禁止工作人员在休息日改变员工信息，开发人员可以建立before
语句触发器,从而实现数据的安全

触发器 — dml触发器

• 使用条件谓词

当触发器中同时包含多个触发事件(insert ,update ,delete)时，为了在触发器代码中
区分具体的触发事件,可以使用三个条件
inserting
updating
deleting

为了禁止工作人员在休息日改变员工信息，开发人员可以建立before
语句触发器,从而实现数据的安全,在给出提示时，明确提示用户是进
行的insert，update还是delete操作

触发器 — dml触发器

• 使用:old和:new

问题：当触发器被触发时，要使用被插入、更新或删除的记录中的
列值，有时要使用操作前、后列的值.
:new 修饰符访问操作完成后列的值
:old 修饰符访问操作完成前列的值

特性 INSERT UPDATE DELETE
OLD NULL 有效 有效
NEW 有效 有效 NULL

案例: ①在修改my_emp表 雇员的薪水时，显示雇员工资修改前和修
改后的值 ②如何确保在修改员工工资不能低于原有工资。

触发器 — dml触发器

• 课堂小练习

编写一个触发器,保证当用户在删除一张表(my_emp2)记录的
时候,自动把删除的记录备份到另外一张表(my_emp2_bak)
中....

id name

• 实现精细化控制

编写一个新的触发器，如何控制员工的新工资不能低于原来的工资，同时也不能
高出原来工资的20%,使用约束显然无法实现该规则，我们看看

触发器 — 系统触发器

• 系统触发器

系统事件是指基于oracle事件(例如logon和startup)所建立的触发器.
通过使用系统事件触发器,提供了跟踪系统或是数据库变化的机制.
下面介绍一些常用的系统事件属性函数,和建立各种事件触发器的方
法在建立系统事件触发器时，我们需要使用事件属性函数,常用的事
件属性函数如下:
ora_client_ip_address //返回客户端的ip
ora_database_name //返回数据库名
ora_login_user //返回登陆用户名
ora_sysevent //返回触发触发器的系统事件名
ora_des_encrypted_password //返回用户des加密后的密码

触发器 — 系统触发器

• 建立登陆和退出触发器

为了记录用户的登陆和退出事件，我们可以建立登陆和退出触发器
为了记录用户名称，时间，ip地址.我们首先建立一张信息表

sql>conn system/manager as sysdba
create table log_table( username varchar2(20),logon_time date,
logoff_time date,address varchar2(20));

我们一起来完成登陆和退出触发器，看看如何编写 -

触发器 — ddl触发器

• 介绍

什么是ddl (data definition language) ,说白了就是我们常用的create
、alter和drop这些数据定义语句.

触发器 — ddl触发器

• 创建ddl触发器

请编写一个触发器，可以记录某个用户进行的ddl操作
①为了记录系统所发生的ddl事件, 应该建立一张表(my_ddl_event)用来存放
相关信息 ,注意需要使用system用户登录conn system/manager as sysdba

字段名 字段类型
event varchar2(20)
username varchar2(20)
time date

my_del_event表

②创建该触发器tr_ddl
③测试触发器是否ok

触发器 — 管理触发器

• 禁止触发器
  是指让触发器临时失效
  alter trigger 触发器名disable;

• 激活触发器
  alter trigger 触发器名enable;

• 禁止或是激活表的所有触发器
  alter table emp disable all trigger;
  alter table emp enable all trigger;

• 删除触发器
  drop trigger 触发器名

☞管理触发器使用system登录

Linux公社（LinuxIDC.com）

• Linux公社（LinuxIDC.com）于 2006 年 9 月 25 日注

册并开通网站，Linux现在已经成为一种广受关注

和支持的一种操作系统，IDC是互联网数据中心，

LinuxIDC就是关于Linux的数据中心。

• Linux公社是专业的Linux系统门户网站，实时发

布最新Linux资讯，包括Linux、Ubuntu、Fedora、

RedHat、红旗Linux、Linux教程、Linux认证、

SUSE Linux、Android、Oracle、Hadoop等技术。

标签：sql,10g,查询,玩转,Linux,oracle,权限,pl
From： https://www.cnblogs.com/salixleaf/p/17351857.html