Oracle学习笔记

在sqlplusw下编辑代码时，出现了错误，不允许我们使用向左方向键向右移动到相应的位置上进行修改，很不方便，所以通常我们使用记事本进行代码的编辑，直接在命令行中输入“ed 文件名名称即可”。如 ed test，输入之后会提示找不到test.sql文件，要创建新文件吗？我们选择“是”，那么就创建了一个test文件，我们就可以在test文本中写相应的代码。创建完成之后，可以通过@文件名称的方式执行命令。如@test，就会执行在test中写的代码。

除了在sqlplus中创建文件之外，也可以通过@符找到磁盘上的文件，如我们在D盘上建立一个demo.txt的文件，里边写上查询指令。执行的时候，要指定文件的路径：@路径，如@D:\demo.txt，也会执行demo中的指令。效果也完全一样的。

“\”可以省略。如@D:demo.txt.如果文件的后缀是.sql，则不写后缀名称也可以找到。如：@D:demo。所以默认找到的后缀是“*.sql”。

在sqlplusw中可以使用“/”表示重复执行上一句命令的操作。

第三章用户、权限

一、用SQLPLUS登录Oracle

1.Sqlplus 用户/密码 [as 身份]

如登录系统帐户：conn sys/system as sysdba;

conn:连接到数据库的关键字

sys:系统用户名

system:是验证密码

as sysdba:是身份验证

2.Oracle 内置帐户

sys具有最大的权限。

Oracle数据库服务器启动后，一般至少有以下几个用户：

Internal，它不是一个真实的用户名，而是具有SYSDBA优先级的Sys用户的别名，它由DBA用户使用来完成数据库的管理任务，包括启动和关闭数据库；

sys:它是一个 DBA用户名，具有最大的数据库操作权限；

system:它也是一个 DBA用户名，权限仅次于 Sys用户

scott:它是一个oracle示例/学习帐户

3.停止和启动Oracle

启动/停止windows服务

Sqlplus /nolog 利用这个命令可以在DOS下不利用任何身份进入到SQL的状态。之后再利用身份登录

Connect /as sysdba

Shutdown/startup

/是以操作系统认证进行登录

Nolog不创建初始联接

4.创建、删除用户

△创建帐户：

CREATE USER username IDENTIFIED BY password

[DEFAULT TABLESPACE tablespace1]------>默认表空间

[TEMPORARY TABLESPACE tablespace2]------>临时表空间

[QUOTA n K ON tablespace1];------>不足时自动增加nK或nM

UNLIMITED----------------------->没有限制

如创建一个hellen的帐户：create user hellen identified by abcd;

△删除帐户：

基本语法：DROP USER userName [cascade];

如果加上cascade关键字可删除该用户所创建的对象。

如：删除用户hellen: drop user hellen;

△用户修改密码：

基本语法：ALTER USER userName IDENTIFIED BY password;

如: alter user hellen identified by abc123;

△用户解锁：

基本语法：ALTER USER userName ACCOUNT UNLOCK;

如：alter user hellen account unlock;

△查看当前登录用户

SHOW USER;

△查看系统有哪些用户：

SELECT USERNAME FROM DBA_USERS;

如：

第一步：desc dba_users;

第二步：select username, USER_ID, ACCOUNT_STATUS FROM DBA_USERS;

△查看用户的默认表空间

select username,default_tablespace from dba_users;

△查看用户有哪些表空间

select distinct tablespace_name from dba_tables where owner=‘USER';

△确定用户帐户所授予的权限

select * from DBA_tab_privs ; 直接授予用户帐户的对象权限 select * from DBA_role_privs ; 授予用户帐户的角色 select * from DBA_sys_privs WHERE GRANTEE=?; 授予用户帐户的系统权限

5.授于用户连接Oracle数据库的权限

格式：grant 权限 to 帐户名。

如授予可以连接到数据库的权限：grant hellen to scott;

常用的权限有：

connect (8) 连上Oracle,做最基本操作

resource(5) 具有程序开发最基本的权限

dba (77)数据库管理员所有权限

exp-full-database 可把数据库整个备份输出的权限

imp-full-datsabase 可把数据库整个备份恢复输入的权限

6.回收权限

基本语法：

REVOKE 权限 FROM 用户名;

REVOKE 实体权限|ALL ON 表空间 FROM 用户名|角色名|PUBLIC;

如授回连接到数据库的权限：revoke connect from hellen;

常见的实体权限：见附录

第四章 Oracle数据类型

Oracle 提供了22 种不同的SQL数据类型供我们使用:

★ CHAR：这是一个定长字符串，会用空格填充来达到其最大长度。非null 的CHAR(12.)总是包含12字节信息。CHAR 字段最多可以存储2,000 字节的信息。

★ NCHAR：这是一个包含UNICODE 格式数据的定长字符串。最多可以存储2,000 字节的信息。

★ VARCHAR2：这是一个变长字符串，与CHAR 类型不同，它不会用空格填充至最大长度。VARCHAR2(12)可能包含0～12字节的信息。VARCHAR2 最多可以存储4,000 字节的信息。

★ NVARCHAR2：这是一个包含UNICODE 格式数据的变长字符串。NVARCHAR2(12)可以包含0～12字符的信息。NVARCHAR2 最多可以存储4,000 字节的信息。

★ NUMBER：这种数据类型能存储精度最多达38 位的数字。这些数介于12×12(-130)-1— —12×12(126)之间。

用法:number(p,s);p和s是可以选的,用于表示整数部分和小数部分的精度

★ BINARY_FLOAT：这是Oracle 10g Release 1 及以后版本中才有的一种新类型。它是一个32位单精度浮点数，可以支持至少6 位精度，占用磁盘上5 字节的存储空间。

★ BINARY_DOUBLE:这也是10g中新的一种类型

★ CLOB：在Oracle9i 及以前的版本中，这种数据类型允许存储最多4GB 的数据，在Oracle 10g及以后的版本中允许存储最多（4GB）×（数据库块大小）字节的数据。这种数据类型很适合存储纯文本信息。

★ BLOB：在Oracle9i 及以前的版本中，这种数据类型允许存储最多4GB 的数据，在Oracle 10g及以后的版本中允许存储最多（4GB）×（数据库块大小）字节的数据。适合于存储图片/文档

★ LONG：这种类型能存储最多2G 的字符数据----建议使用CLOB代替

★ DATE：这是一个7 字节的定宽日期/时间数据类型。其中总包含7 个属性，包括：世纪、世纪中哪一年、月份、月中的哪一天、小时、分钟和秒。

★ TIMESTAMP：这是一个7 字节或12.字节的定宽日期/时间数据类型。它与DATE 数据类型不同，因为TIMESTAMP 可以包含小数秒（fractional second）；带小数秒的TIMESTAMP 在小数点右边最多可以保留9 位。

★ TIMESTAMP WITH TIME ZONE：与前一种类型类似，这是一个12.字节的定宽TIMESTAMP，不过它还提供了时区(TIME ZONE)支持。数据中会随TIMESTAMP 存储有关时区的额外信息，所以原先插入的TIME ZONE 会与数据一同保留。

★ ROWID：ROWID 实际上是数据库中一行的12字节地址。ROWID 中编码有足够的信息，足以在磁盘上定位这一行，以及标识ROWID 指向的对象。

(1)Oracle 中伪列就像一个表列，但是它并没有存储在表中

(2)伪列可以从表中查询，但不能插入、更新和删除它们的值

(3)常用的伪列有ROWID和ROWNUM

ROWID 是表中行的存储地址，该地址可以唯一地标识数据库中的一行，可以使用 ROWID 伪列快速地定位表中的一行。

ROWNUM 是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数

例：

列的类型主要有如下几种：

NUMBER(4):表示是数字，长度为4

VARCAHR2(10):表示的是字符串，只能10的个的长度

DATE:表示日期

NUMBER(7,2): 表示是数字，其中小数位占2位，整数位占5位，总共是7位。

第五章 SQL语句概述

SQL语句概述

○ SQL结构化查询语言(Structured Query Language)

一般读作：[si:kju:] 或者是字母 S Q L 的发音。

○ 目前数据库厂商实现的都是SQL92标准,还没有任何一家厂商通过SQL99标准认证

○ Oracle对SQL92做了扩展,所以称自己为加强版SQL(SQLPLUS)。

对于不同的数据库来讲，重点都是掌握SQL语句，因为现在的数据库全部是以SQL操作的标准，在实际中，各个数据库就是提供的函数不同。

SQL语言主要用于与数据库的通讯。SQL语言功能强大，主要分为以下同种：DML DDL DCL 事物控制语言。

一、SQL语句分类

1.DDL(Data Definition Language)数据定义语言:定义数据库对象(表空间,表,列,索引等)

如：CREATE,DROP,ALTER，TRUNCATE 等

2.DML(Data Manipulation Language)数据操纵语言:完成对数据记录的操作

如：INSERT,DELETE,UPDATE,SELECT 等。

3.DCL(Data Control Language)数据控制语言:定义用户的访问权限和安全级别

如：GRANT,REVOKE

4.事物控制(Transaction Control)事物控制:如：COMMIT,ROLLBACK

(1)事务是最小的工作单元，作为一个整体进行工作

(2)保证事务的整体成功或失败，称为事务控制

用于事务控制的语句有：

(4)COMMIT - 提交并结束事务处理

当向表插入一个新值的时候，该事物并没有被永久的写到磁盘上去，重新打开窗口再次查询该表中的数据时，发现并没有刚才插入的记录，这是因为这个事物还没有结束，当遇到commit或rollback才认为是结束了。

如果要永久性的提交可以执行：commit命令，再次打开新的窗口时该记录已被写到表中了。

(5)ROLLBACK - 撤销事务中已完成的工作

当我们做了和系列的操作以后，都没有执行commit命令，也就是没有提交，我们执行了rollback就可要回到原点了，也就是刚才所做的都等于没做，所有rollback回退是将所有的回退。

(6)SAVEPOINT – 标记事务中可以回滚的点

因为rollback回退是将所有的都回退了，这明显不太好，那么我们可以设置几个回退点，使再次回退的时候，不让其回退到原点，而是回退到我们固定的位置上去。

如：

UPDATE 表名 set id=2 WHERE id=3----->将id=3改为id=2

SAVEPOINT mark1------>设置一个还原点mark1

DELETE FROM 表名 WHERE id=5---->删除id=5

SAVEPOINT mark2;----->再设置一个还原点mark2

二、Oracle支持的SQL操作符的分类：算术操作符比较操作符逻辑操作符集合操作符连接操作符

1.算术操作符：

算术操作符用于执行数值计算

可以在SQL语句中使用算术表达式，算术表达式由数值数据类型的列名、数值常量和连接它们的算术操作符组成

算术操作符包括加(+)、减(-)、乘(*)、除(/)。

2.连接操作符用于将多个字符串或数据值合并成一个字符串

例：要求查出雇员的编号、姓名、工作但是显示的格式是：

编号是：7369的雇员，姓名是：SMITH,工作是:CLERK

SELECT '编号是：'||empno||'的雇员，姓名是：'||ename||'工作是'||job;---->使用了连接操作符

在查询中也可以使用四则运算功能。如我们要查每个雇员的姓名及年薪。

SELECT ename,sal*12 FROM emp;----->月薪*12表示年薪------>使用了算术操作符

3.比较操作符用于比较两个表达式的值

比较操作符包括 =、!=、<、>、<=、>=、BETWEEN…AND、IN、LIKE 和 IS NULL等

例：

SQL> SELECT itemdesc, re_level

FROM itemfile

WHERE qty_hand < max_level/2;

SQL> SELECT orderno FROM order_master

WHERE del_date IN (‘06-1月-05’,‘05-2月-05');

SQL> SELECT vencode,venname,tel_no

FROM vendor_master

WHERE venname LIKE 'j___s';

4.逻辑操作符

逻辑操作符用于组合多个计较运算的结果以生成一个或真或假的结果。

逻辑操作符包括与(AND)、或(OR)和非(NOT)。

例：显示 2005-5月-10 至 2005-5月-26的订单信息

SQL> SELECT * FROM order_master

WHERE odate > ‘10-5月-05'

AND del_date < ‘26-5月-05’;

5.集合操作符将两个查询的结果组合成一个结果，集合操作符有：UNION、UNION ALL、INTERSECT、MINUS

(1)UNION:将两个表中的所有的记录合到一起，但重复的只合一遍

如：用法有两个表(id 和 name)

SELECT * FROM 表A

UNION

SELECT * FROM 表B;

结果：A与B重复的行只拿一次

(2)UNION ALL:重复的行复取，也就是取出所有的(A+B)

SELECT * FROM A

UNION ALL

SELECT * FROM B;

(3)INTERSECT:返回公有的(A与B的交集)

(4)MINUS 差积(A-B或B-A)

用法都与UNION相同。

注一：在求差积时，如果A放前，去掉公共的行时，剩余的都是A中的，而B中剩余了什么并不管。如果B放前去掉公共行时，剩余都是B中的。

注二、在使用集合操作符时，两个表的类型一定要相同。

三、SQL 操作符的优先级从高到低的顺序是：

算术操作符 --------最高优先级

连接操作符

比较操作符

NOT 逻辑操作符

AND 逻辑操作符

OR 逻辑操作符 --------最低优先级

第六章表空间

表空间

1.创建表空间

基本语法：

CREATE TABLESPACE spacename

[LOGGING] | NOLOGGING

DATAFILE ‘d:\javasky.dbf’

SIZE 200M

AUTOEXTEND ON NEXT 200M;

如：

create tablespace javasky ---->表空间名

datafile 'd:\javasky.dbf'----->目录地址

size 20M----------------------->大小为20M

autoextend on next 5M;-------->当空间不足时自动增加5M;

2.删除表空间

基本语法：

DROP TABLESPACE “TABLESPANCENAME”

注意表空间的名字需要使用双引号包围,并且表空间的名称需要大写。

如：drop tablespace "JAVASKY";

3.查看表空间的名称和状态

select tablespace_name,status from dba_tablespaces;

表空间的状态属性主要有在线（ONLINE）、离线（OFFLINE）、只读（READ ONLY）和读写（READ WRITE）4种

4.修改表空间的状态

alter tablespace 表空间名状态;可以修改表空间的状态

第七章表

查看该帐户下所有的表：

第一种、select * from cat;

第二种、select * from tab;

● 建表

CREATE TABLE 表名(列名列类型,…);

如：create table student(id int,name varchar2(5),address varchar2(10));

--创建一个具有ID NAME ADDRESS 的学生信息表

创建完表以后可以利用:desc 表名来查看

如 dest student; 如名称、类型、是否为空。

● 修改表

1.增加新列

ALTER TABLE 表名 ADD 列名列类型 [ADD 列类型];

如新一个电话的列：alter table student add tel number;

增加一个新的列后给其赋值：update student set tel=15114562383 where id=2;

2.删除旧列

ALTER TABLE 表名 DROP COLUMN 列名;

3.修改列类型(要求,列中无数据)

ALTER TABLE 表名 MODIFY 列名列类型

4.修改列名

ALTER TABLE 表名 RENAME COLUMN 列名 TO 新列名;

5.修改表名

RENAME 表名 TO 新表名;

● 插入(记录)数据，也就是给列赋值

1.给其相应的字段赋值：INSERT INTO表名[(列名1,,,,)] VALUES(值1,,,); 注：[]中的内容可写可不写

如：INSERT INTO student(id,name,addredd) VALUES(1,'李小龙','河北省');

2.赋全值

INSERT INTO student VALUES(1,'李小龙','河北省');

3.赋值以后查看该表的内容： select * from student;

4.插入日期格式的值：

INSERT INTO 列名(列名)

VALUES (TO_DATE('2005-10-18', 'YYYY-MM-DD'));

● 修改记录

UPDATE 表名 SET 列名=值,….

[WHERE 条件];

如 update 表名 set tname='李小龙'

where tname='李建龙'; ----->这是一个条件限制

● 删除(记录)数据

第一种方法： DELETE FROM 表名

[WHERE 条件];

第二种方法： TRUNCATE TABLE 表名;

● 利用现有的表创建表

语法：

CREATE TABLE <new_table_name> AS

SELECT column_names FROM <old_table_name>;

如：

SQL> CREATE TABLE newitemfile

AS SELECT * FROM itemfile;------>所有的列

SQL> CREATE TABLE newitemfile1

AS SELECT itemcode, itemdesc, qty_hand ----->选择特定的列

FROM itemfile;

SQL> CREATE TABLE newitemfile2

AS SELECT * FROM itemfile

WHERE 1 = 2;------>建表时的条件，这里是1=2，明显为假，但是可是以建表，但是空的内容为空。

● 不带条件的DELETE和TRUNCATE TABLE的区别：

*在功能上，TRUNCATE TABLE是清空一个表的内容，它相当于DELETE FROM table_name。

*DELETE是dml操作，truncate table是ddl操作；因此DELETE可以回滚,TRUNCATE TABLE不可回滚。

*TRUNCATE TABLE 调整high water mark 而DELETE不；TRUNCATE TABLE之后，TABLE的HWM退回到 INITIAL和

NEXT的位置（默认）delete 则不可以。

*TRUNCATE TABLE 只能对TABLE进行操作， DELETE可以是table,view,synonym。

*TRUNCATE TABLE不会触发 DELETE触发器

*日志记录方式不同, DELETE逐行记录删除日志,TRUNCATE TABLE只记录在磁盘上某一

第八章函数

函数

数据库系统中，每个数据库之中唯一最大的区别就是函数的支持上，使用函数可以完成一系列的操作能。Oracle 提供一系列用于执行特定操作的函数。

SQL 函数带有一个或多个参数并返回一个值

以下是SQL函数的分类：单行函数、分组函数、分析函数

一、单行函数

单行函数对于从表中查询的每一行只返回一个值

可以出现在 SELECT 子句中和 WHERE 子句中

单行函数可以大致划分为：

字符函数：接受字符输入并且返回字符或数值

数值函数：接受数值输入并返回数值

日期函数：对日期型数据进行操作

转换函数：从一种数据类型转换为另一种数据类型

通用函数：NVL函数、DECODE函数单行函数的的语法：

function_name(columnle expression,[arg1,arg2.....])

function_name:函数的名称

culumnle:数据库列名

expression:字符串或计算表达式

arg1,arg2:函数中使用的参数

时间类型函数

SYSDATE返回当前的系统时间

ADD_MONTHS(date,x)返回加上x月后的日期DATE的值，X可以是任意的整数，如果结果的月份中所包含的日份量不于DATE的月份的日份量，则返回结果月份的最后一天，如果不小于，则结果与DATE的日份量相同。

LAST_DAY(日期)指定日期所在月份的最后一天的日期，

TRUNC(日期，‘MONTH\YEAR’)返回指定月份的第一天。

日期运算：

日期函数对日期值进行运算，并生成日期数据类型或数值类型的结果

日期函数包括：ADD_MONTHS、MONTHS_BETWEEN、LAST_DAY、ROUND、NEXT_DAY、TRUNC

范例一：

返回date1与date2之间相差的天数。该值是一个数值，其小数部分代表一天的几分之几。

SQL> select to_date('2010-01-31','yy-mm-dd')-to_date('2010-01-01','yy-mm-dd') as 相差天数 from dual;（相差天数是一个别名）

相差天数

----------

范例二：

返回date1与date2之间的时间间隔

select to_date('2010-01-01 10:30','yy-mm-dd hh:mi')

-to_date('2010-01-31 09:31','yy-mm-dd hh:mi') from dual;

TO_DATE('2010-01-0110:30','YY-MM-DDHH:MI')-TO_DATE('2010-01-3109:31','YY-MM-DDHH:MI')

------------------------------------------------------------------------------- -29.959028

2、字符函数

Initcap(char)：将首字母转换为大写

eg：Select initcap('hello')from dual;------->Hello

Lower(char)：转化为小写

eg：Select lower(‘FUN’) from dual;------->fun

Upper(char)：转化为大这与

eg：Select upper(‘sun’) from dual;------->SUN

Ltrim(char,set)：从左边开始截取字符(一般用来截取空格)

eg：Select ltrim( ‘xyzadams’,’xyz’) from dual;------->adams

Trim():从两端截取

eg：SQL> SELECT TRIM('a' FROM 'abcda') FROM dual;---->abc

Rtrim(char,set)：从右边开始截取字符(右截空格)

eg：Select rtrim(‘xyzadams’,’ams’) from dual;-------> xyzad

Translate(char, from, to)：替换单个字符

eg：Select translate(‘jack’,’j’ ,’b’) from dual; -------> back

Replace(char, searchstring,[rep string])：替换多个字符

eg：Select replace(‘jack and jue’ ,’j’,’bl’) from dual;------->black and blue

Instr (char, m, n) ：返回一个数值型，标识截取的字符的位置，从1开始计，第一次出现的位置

eg：Select instr (‘worldwide’,’d’) from dual; ------->5

Substr (char, m, n)：从第m开始截取n个字符

eg：Select substr(‘abcdefg’,3,2) from dual;------->cd

Concat (expr1, expr2)：合并

eg：Select concat (‘Hello’,’ world’) from dual;------->Hello world

作用：以UPPER()为例

1.UPPER()强制大写

如：SELECT UPPER('hellen') FROM dual;

范例：一般用户在查询一个人的姓名的时候可能考虑到这个人的姓名是以大写字母保存的还是以小写字母保存的呢？

SELECT * FROM emp WHERE ename=UPPER('smith');

3、字符函数

以下是一些其它的字符函数：CHR和ASCII、LPAD和RPAD、TRIM、LENGTH、DECODE

1、CHR和ASCII：通过一个字符求ASCII码

SQL> select chr(67) from dual;

-----

2、LPAD和RPAD：左填充、右填充

SQL> SELECT LPAD('abc',10,'*') FROM dual;

LPAD('ABC'

----------

*******abc

'abc'不足10个字符，在其左边填加7个补齐10个，右填充同理

3.LENGTH():求给定的字符的长度如：

SQL> SELECT LENGTH('abcd') FROM dual;

LENGTH('ABCD')

-----------------------------

如果是“中国”结果也是2，因为这是按字符算的

LENGTH()与LENGTHC()是等同的。

还有一个LENGTHB():'中国'的结果是4，是按字节算的。

LENGTH2()和LENTH4()都是按字符算的，我们常用的是LENGTH()和LENGTHB()

4.DECODE:相当于if语句如：

SQL> SELECT DECODE(id,1,'tom',2,'jack',3,'hellen') FROM student;

DECODE

------

tom

jack

hellen

如果id是1 2 3 分别返回 tom jack helen 相当于做了一个多行的判断

5.数字函数

函数	范例	结果
Abs(n)	Select abs(-15) from dual;	15
Ceil(n)	Select ceil(44.778) from dual;	45
Cos(n)	Select cos(180) from dual;	-.5984601
Cosh(n)	Select cosh(0) from dual;	1
Floor(n)	Select floor(100.2) from dual;	100
Power(m,n)	Select power(4,2) from dual;	16
Mod(m,n)	Select mod(10,3) from dual;	1
Round(m,n)	Select round(100.256,2) from dual;	100.26
Trunc(m,n)	Select trunc(100.256,2) from dual;	100.25
Sqrt(n)	Select sqrt(4) from dual;	2
Mod(m,n)	Select mod(4,2) from dual;	0
Sign(n)	Select sign(-30) from dual;	-1

dbms_random.value(x,y) Select dbms_random.value(2,4) from dual; 3.980765

6.转换函数

转换函数将值从一种数据类型转换为另一种数据类型

常用的转换函数有：TO_CHAR、TO_DATE、TO_NUMBER

1、TO_DATE

SQL> SELECT TO_DATE('2005-12-06','yyyy-mm-dd') FROM dual;

TO_DATE('2005-

--------------

06-12月-05

2、TO_NUMBER

SQL> SELECT TO_NUMBER('100') FROM dual;

TO_NUMBER('100')

----------------

100

3、TO_CHAR

SQL> SELECT TO_CHAR(sysdate,'YYYY"年"fmMM"月"fmDD"日"HH24:MI:SS') FROM dual;

TO_CHAR(SYSDATE,'YYYY"

----------------------

2010年11月13日11:01:45

7.其它函数:NVL、NVL2、NULLIF

1、NVL

SELECT comm,NVL(comm,0) FROM 表

如果为空就用0来代替，不为空自己。

2、NVL2

有3个参数，如果第1个为空，就走第3个表达式，若不为空，走第2个表达式

SELECT comm,NVL(comm,comm,0) FROM 表

3.NULLIF

如果两个表达式的值相等返回空，不相等返回第一个表达式

SQL> SELECT NULLIF(22,23) FROM dual;

NULLIF(22,23)

-------------

SQL> SELECT NULLIF(22,23) FROM dual;

NULLIF(22,23)

-------------

二、组函数

详细见组函数及分组统计一章

第九章约束

CREATE TABLE深入

● 为表增加约束

NOT NULL 非空约束要求值不能为空

UNIQUE 唯一约束要求值不能重复

PRIMARY KEY主键约束对于整个表表中的记录不能重复

FOREIGN KEY外键约束当多个表建立关联时设置的一个引用约束

CHECK 检查检查某一个列的值要符合某一个规范，如年龄(age>18)

● 设置表所在的表空间

● 使用序列实现自动增长

一、什么是约束?

约束是在表上强制执行的一些数据校验规则,被插入/修改/删除的数据必须符合在相关字段上设置的这些约束条件

二、约束定义的语法

列级约束：在定义列的同时定义约束

语法：列定义约束类型

表级约束：在定义了所有的列之后定义的约束

语法：

列定义......

[CONSTRAINT 约束名] 约束类型(列名)

约束名的命名规则：

推荐采用：表名_列名_约束类型简写

约束可以在创建表时就定义，也可以在创建完表后再添加

语法：ALTER TABLE 表名

ADD CONSTRAINT 约束名约束类型(要约束的列名)

三、各约束介绍

1.NOT NULL

(1)该列的值不能为空

(2)列级约束

如名字不能为空

create table depts(dept_id int,name varchar(20) NOT NULL,description varchar(100));

如果创建了以后再加不能为空的约束可以以修改列的形式：如

alter table employee modify empname varchar2(6) not null;

2.UNIQUE

(1)要求该列的值唯一，允许为空。注：Oracle允许有多个空什和null

(2)列级约束、表级约束

(3)取名：表名_列名_uk

如：名字不能为空且唯一

create table depts(dept_id int,name varchar(20) NOT NULL UNIQUE,descriptionvarchar(100));

如果是先创建了表来要增加约束可以写成：

alter table depts add constraint depts_name_uk unique(name);--将名字加上唯一约束

3.PRIMARY KEY

(1)用来唯一标识这一行记录，一个表中只能有一个主键

(2)功能上相当于非空且唯一

(3)列给约束、表级约束

(4)取名：表名_列名_pk

例1：将id号设置主键(在列级上定义)

create table depts(

id int primary key,---->将id设置为主键

name varchar(10) unique not null,---->名字不为空且唯一

description varchar(100));

例2：在表级上定义

CREATE TABLE student(

firstname VARCHAR(20),

lastname VARCHAR(20),

description VARCHAR(100),

[CONSTRAINT student_name_pk] PRIMARY KEY(firstname,lastname));

这个例子是联合主键，所谓联合主键就是firstname和lastname他们的组合不重复，就认为是有效。如A B和A C这不算是重复。

(5)主键有一个最主要的作用是：当两个表关联时，主外键时，要求引用的主表中的字段一定要为主键。

4.FOREIGN KEY

(1)用于两表间建立关系,需要指定引用主表的那列

(2)列级约束表级约束

(3)命名:主表名_子表名_FK

(4)语法:

[CONSTRAINT 约束名] FOREIGN KEY(子表外键列名)

REFERENCES 主表名(主键列名)

如我们把depts表和employees表的通过dept_id关联起来。

--部门表注表

create table depts(

dept_id int primary key,

description varchar(20) );

--员工表子表

create table employees(

emp_id int primary key,

address varchar(20),

dept_id int,

constraint depts_employees_fk

foreign key(dept_id)

references depts (dept_id)--主键

);

表之间的关联关系，建议创建好表之后通过 ALTER TABLE 语句来添加：

语法：

ALTER TABLE 表名

ADD CONSTRAINT 外键约束名

FOREIGN KEY (本表外键列名)

REFERENCES 主表名(主表主键列名)

[ON DELETE [RESTRICT|CASCADE|SET NULL|NO ACTION]]

例：

alter table employees

add constraint depts_employees_fk foreign key (depts_id)

references depts(depts_id);

ON DELETE:用来指定在删除主表中的数据时，对关联表(从表)所做的相关操作。

RESTRICT(限制):跟NO ACTION(没有动作)，效果一样，不采取动作，即当主表中的主键在子表中被使用，则不允许修改此主键值。

CASCADE(级联)：级联更新子表

SET NULL(设置为空)：主表删除(更新)行，则设置子表中的外键列为NULL。

示例：图

如上表employees表和depts表本来没有关系的，但是通过dept_id可以将他们联系起来。

注：depts表中的dept_id必须为主键。才能通过dept_id与employees表联合起来。

录depts表与employees表进行联合的时候，他们之间便有了联系，如：在depts表中一共有四个部门部，部门号代号分别是1、2、3、4。

但是如果我们在employeess表中插记录时，就不能给某个人所在部门号设置为5，因为根本没有这个部门。

如果我们在employees表中加了两个人都是2号部门，那么两个表关联下来以后就不允许我们删除depts表的2号部门了。别的部门只要是在employees表中没有被引用的都可以被删除。

当子表中引用了主表中的某一个记录时，那么这个主记录不允许删除。也不允许更改。

我们可以在删除以后做一些操作如：

例：

SQL> alter table employees

2 add constraint depts_employees_id

3 foreign key (dept_id)

4 references depts (dept_id)

5 on delete set null;--->删除后设置为空

也就是主表的部门被删除后，子表的记录走向为空。也就是原来引用该部门的记录现在部门不存在了，该记录的引用就就变为空了。

5.CHECK

(1)对某列的值进行范围限制、格式限制等

(2)列级约束、表级约束

(3)取名：表名_列名_ck

例：

SQL> create table student(

2 id int primary key,

3 name varchar(10) unique not null,

4 age int check(age>18)--可以使用like等

5 );

6.删除约束

先找到表的约束名称，执行：

select TABLE_NAME,CONSTRAINT_NAME from user_constraints where owner=‘’

其中 CONSTRAINT_NAME 为表的约束名称

然后删除约束：

alter table TABLE_NAME drop constraint CONSTRAINT_NAME [cascade];

使用CASCADE关键字使相关约束也失效.

如：删除sal上的约束

alter table employee drop constraint employee_sal_ck;

例：

SQL> desc user_constraints;

名称是否为空? 类型

----------------------------------------- -------- ----------------------------

OWNER NOT NULL VARCHAR2(30)

CONSTRAINT_NAME NOT NULL VARCHAR2(30)

CONSTRAINT_TYPE VARCHAR2(1)

TABLE_NAME NOT NULL VARCHAR2(30)

SEARCH_CONDITION LONG

SQL> select CONSTRAINT_NAME,TABLE_NAME,CONSTRAINT_TYPE from user_constraints whe

re table_name='employees';

可以看到employees表的表名、约束名字和类型等。

7.指定表空间

CREATE TABLE() TABLESPACE spacename

如：

desc user_tablespace ;----->查看所有的表空间

create table student(id int) tablespace system;

8.创建序列

序列 :是Oracle的一个对象，表也是一个对象。所谓序列是指生成了一个有顺序的数字对象。

序号生成器

创建语法:

CREATE SEQUENCE 序列名 MINVALUE number1 START WITH number2 INCREMENT BY number3 nocache|cache number3;

MINVALUE：最小的值

INCREMENT BY：从那开始

START WITH：增量、步长

是否可被缓存

是否可以循环

从序列中取值: Select 序列名.nextval from dual;--nextval序列中的下一个值

读取下一个值：Select seq.nextval from dual;

读取当前值：Select seq.currval from dual;

如创建一个序列：

SQL> create sequence seq_dept

2 start with 1

3 increment by 2

4 maxvalue 10;

查看：

SQL> select seq_dept.nextval from dual;

NEXTVAL

----------

第执行一次就+2.

SQL> select seq_dept.currval from dual;

NEXTVAL

----------

currval不论执行多少次都是1.

往部门表中插：

insert into depts values(seq_dept.nextval,"财务部");

seq_dept.nextval是一个数字对象，与部门号对应。

修改序列：如将最大值改为100.

alter sequence seq_dept maxvalue 100;

删除序列：drop sequence seq_dept;

第十章单查询查询

SQL查询的基本语法结构:

SELECT selection_list	选择哪些列
FROM table_list	从何处选择
WHERE primary_constraint	行必须满足的条件
GROUP BY grouping_columns	结果怎样分组
OREDER BY sort_columns	怎样对结果排序
ROWNUM offset	结果限定

以emp表和部门表(dept)为例：

一、普通查询

1.查询所有记录的所有列

SELECT * FROM emp

2.查询特定行

SELECT * FROM empWHERE name='纪小岚';

3.查询特定列

SELECT name,sarlary FROM emp;

4.去掉重复的值

SELECT DISTINCT dept_id FROM emp;

5.给列取别名

SELECT name AS 姓名 FROM employees;

SELECT name n FROM employee;

二、条件查询

SELECT 列名....

FROM 表名....

WHERE 条件

WHERER 条件子句中的表达式：

可以包括运算符（算术、比较、逻辑等）

可以使用圆括号将一个表达式分成几个部分

可以使用常量、列和函数来完成运算

注：WHERE子句中不能有聚合运算。

条件查询示例：

1.除了张三以外的所有员工：

select * form employees

where name!='张三';--或可以写成name<>'张三'

2.工资在2000-5000的员工。

第一种写法：where sal>=2000 and sal<=5000;

第二种写法：where sal between 2000 and 5000; --注：包含两端

3.部门编号是1、3、5的员工名字

select name form employees

impno=1 or impno=3 or empno=5;

4.有奖金的

where comm is not null;

注：0也算是有

5.所有姓张的

where name like '张%' ;--这里%是一个通配符。like是一个正则表达式匹配的关键字

6.姓张的且名字是3个字的。

where name like '张_ _';

7.在2008.1.1入职的女员工

where data='01-01-08' and sex='女';

三、查询排序

SELECT 列名....

FROM 表名....

[WHERE 条件]

ORDER BY 排序列名1[ASC|DESC]，排序列名2[ASC|DESC]...

ASC:升序缺省

DESC:降序

例：

select * from employees

order by sal,comm

desc;

四、在Oracel用“||”表示字符串的连接。如实现如下查询：

要求查出雇员的编号、姓名、工作但是显示的格式是：

编号是：7369的雇员，姓名是：SMITH,工作是:CLERK

SELECT '编号是：'||empno||'的雇员，姓名是：'||ename||'工作是'||job;

在查询中也可以使用四则运算功能。如我们要查每个雇员的姓名及年薪。

SELECT ename,sal*12 FROM emp;----->月薪*12表示年薪

但是在查询的结果中是以sal*12的字段显示的，这样的看起来让人不太明白，不知道到底是表示什么意思。所以我们最好为这样运算结果起一个别名，但是在起别名的时候，一定要回避中文。如：SELECT ename,sal*12 income FROM emp;【income表示年薪】。

第十一章多表查询-内连接

SELECT 深入查询:多表联接查询、记录联合

● 多表连接查询

1.使用单个SELECT语句从多个表中取出多个相关的数据，通过多表之间的关系，构建相关数据的查询。

2.多表连接通常是建立在相互关系的父子表上的。

3.SQL1999标准连接的语法：

-------------------------------------

SELECT...FROM join_table

JOIN_TYPE join_table

ON join_condition

WHERE where_condition

-------------------------------------

join_table:参与连接的表

join_condition:连接条件

JOIN_TYPE:连接类型：内连接、外连接、交叉连接、自连接。

where_condition: where 过虑条件

● 内连接

1.语法

SELECT 要查询的列表1

[INNER] JOIN 表2

ON 连接条件

WHERE

例1，以emp表和dept表为例：这是一个等值连接

SELECT empno,dname,ename,dept.loc FROM dept--第一个表

INNER JOIN emp--另外一个要连接的表(内连接)

ON emp.deptno=dept.deptno ;--连接条件，部门号相同(去除笛卡尔乘积)

注意：如果两个表中有相同的字段，则可能通过他的的表名来区别，

如：dept.address 或 emp.address

范例2.

select * from dept,emp

where dept.deptno=emp.deptno;

如果有第三个表C（用where表示）如：

select * from dept,emp,c

where dept.deptno=emp.deptno,and c.deptno=dept.deptno;

用 JOIN IN(如有三个表分别是a b c )

select a.id,b.name,c.score from a

inner join b on a.id=b.id

inner join c on c.id=b.id

[where];

2.只列出这些连接表中与连接条件相匹配的数据行

3.内连接分类

等值连接：在连接条件中使用等号(=)运算符来比较被连接列的列值（常用）

如：dept.deptno=emp.deptno

非等值连接：在连接条件中使用除了等号运算符以外其它比较运算符来比较被连接的列值.使用不等值连接容易产生笛卡尔乘积（不常用）。

自连接：在连接条件中使用等号(=)运算符来比较被连接列的列值，但他使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。不用写ON条件，如果两个表中字段、类型完全相同的会自动连接（不常用）。

内连接示例一：

select * from emp

inner join dept

on emp.deptid=depts.deptid;

如果表的名字很长，可以给其起一个别名如：

SELECT * FROM emp AS e,dept d

WHERE a.id=d.id;

第十二章多表查询-外连接

不仅列出与连接条件相匹配的行，还列出左表（左外连接）、右表（右外连接）、或两个表

（全外连接）中所有符合WHERE 过虑条件的数据行。

分类：左外连接 (LEFT JOIN)

右外连接 (RIGHT JOIN)

全外连接 (FULL JOIN)

一、左外连接

示例：

SELECT * FROM emp

LEFT JOIN dept

ON emp.deptno=dept.deptno;-->这是通用标准

以上示例与以下示例是等效的如：

select * from emp join dept

on emp.deptno=dept.deptno(+);-->这是特殊符号

还可以写成：

select * from emp,dept

where emp.deptno=dept.deptno(+);

关键字之前的是左表关键字之后的是右表。

左处连接：取出左表中所有数据，右表中符合条件的

右外连接：取出右表中所有数据，左表中符合条件的】

当左处连接时，（+）在等号右边。

当右外连接时，（+）在等号左边。

右外连接和全外连接同理,将LEFT写成RIGHT、FULL。

全连接范例：

select ename,empno,dname from emp

full join dept

on dept.deptno=emp.deptno;

● 记录联合

UNION

UNION ALL

UNION指令的的是将两个SQL语句的结果合并起来

UNION 在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。

UNION ALL，它的用法和UNION一样，只不过UNION含有DESTINCT的功能，它会把两个张表重复的记录去掉，而UNION ALL不会，所以从效率上，UNION ALL 会高一点。

注：UNION 用法中，两个SELECT 语句的字段类型匹配，而且字段个数要相同。

● 子查询中的 in 和 exists

用in和exists查询出表B的name在表A中的记录。

表A 表B

1.select from B where name in(select name from A)

2.select from B where exists(select temp.* from B temp.a where a.name=b.name AND a.name=temp.name)

通过上例比较，如果只有一个条件name使用in很方便，同理not in和not exits也是如此。

exists需要把表进行关联，in 不一定需要。

如何选择in还是exsits?

主要是看你筛选条件是在主查询上还是在子查询上，如果条件作用在主查询上使用in，如果作用在子查询上使用exists。

在数据量较大时，exists的效率高于in.

第十三章多表查询-基本

查询语句：

SELECT {DISTINCT} *|查询列1 别名1，查询列2 别名2....---->distinct是不可重复意思

FROM 表名称别名

WHERE 条件(s)}----->条件可以是多个

{ORDER BY 排序字段 ASC|DESC,排序字段 ASC|DESC}

一、学习目标：

1.多表查询，SQL:1999语法对多表查询的支持

2.分组统计级统计函数的使用

3.子查询，并结合多表查询，分组统计做复杂查询

4.数据库的更新操作

5.数据库的更新操作

6.事务处理及数据库死锁

二、具体内容

多表查询

之前在查询的时候都是在一张表中查询，那么在一张表以上的查询就叫做多表查询。

基本语法：

SELECT {DISTINCT} *|查询列1 别名1，查询列2 别名2..---->distinct是不可重复意思

FROM 表名称1 别名1，表名称2 别名2，表名称3 别名3..--->写多少都可以只要电脑够快

WHERE 条件(s)}----->条件可以是多个

{ORDER BY 排序字段 ASC|DESC,排序字段 ASC|DESC}

范例：下面使用了多表查询，同时查询emp表和dept表

1.select *from emp,dept;--->一共出现了56行的记录

查询emp表中的记录数: select count(*) from emp;---->14

查询dept表中的记录数:select count(*) from dept;--->4

以上同量使用了两张表进行查询，共出现了56(4*14)行，这是一个笛卡尔乘积。所以在多表查询的时候会产生笛卡尔积，如果表的数据越多，那么产生的笛卡尔成绩越大。如现在假设有5张表每张表有100000记录，那么就会产生100000的5次方条记录。所以多表查询在开发中不介意过多的使用的。

要想去掉笛卡尔积，必须使用字段进行关联的操作。

在emp表中存在一个deptno的字段,在dept表中也存在一个deptno的字段。而且emp表的deptno的字段在dept表的deptno字段的范围之内。

emp表的deptno=dept表的deptno，这属于关联字段。

在多有查询中加入 WHERE 语句，就可以消除笛卡尔积。

范例：修改之前的查询操作：

select * from dept,emp

where emp.deptno=dept.deptno--->这样写比较明确，也可以写成deptno=deptno.

此时只有14行记录了，消除掉了笛卡尔积。此时又存在了一个新的问题，那么现在表名称过长的话，怎么办？

所以在使用的时候会为表起一个别名：如：

select * from emp e,dept d ---->e 和 d 都是别名

where e.deptno=d.deptno;

所以要使用多表查询的时候，最好指定别名。

范例：要求查询出雇员的编号、雇员的姓名、部门的编号、部门的名称及部门位置。

select e.empno,e.ename,d.deptno,d.dname,d.loc

from emp e,dept d

where e.deptno=d.deptno;

结果：

EMPNO ENAME DEPTNO DNAME LOC

---------- ---------- ---------- -------------- -------------

7369 SMITH 20 RESEARCH DALLAS

7499 ALLEN 30 SALES CHICAGO

7521 WARD 30 SALES CHICAGO

范例：要求查询每个雇员的姓名、工作、雇员的直接上司的姓名。

在emp表中的mgr字段始终没有使用过，其表示一个雇员的上级领导的编号。现在要查询一个雇员的上级领导，则肯定要将emp表和emp表自己进行关联。

select e.ename,e.job,m.ename

from emp e,emp m

where e.mgr=m.empno;

范例：进上步扩展之前的程序，将雇员的所有部门名称同时列出。

部门名称在demp表的定义

select e.ename,e.job,m.ename,d.dname

from emp e,emp m,dept d

where e.mgr=m.empno and e.deptno=d.deptno;

现在进上步深入查询

现在要求查询每个雇员的姓名、工资、部门名称，工资在公司的等级（salgrade）,及其领导的姓名及工资所在公司的等级。

先查询工资表的内容(salgrade)

SELECT * FROM salgrade;

GRADE LOSAL HISAL

------ ---------- ----------

1 700 1200

2 1201 1400

3 1401 2000

4 2001 3000

5 3001 9999

分解：

1.查每个雇员的姓名、工资、部门名称，工资在公司的等级（salgrade），需要三张表进行关联。

select e.ename,e.sal,d.dname,s.grade

from emp e,dept d,salgrade s

where e.deptno=d.deptno and e.sal between s.losal and s.hisal;

2.此时雇员的工资等级已经求出，现在再求其领导的姓名及工资所在公司的等级。

select e.ename,e.sal,d.dname,s.grade,m.ename,m.sal,ms.grade

from emp e,dept d,salgrade s,emp m,salgrade ms

where e.deptno=d.deptno and

e.sal between s.losal and s.hisal and e.mgr=m.empno

and m.sal between ms.losal and ms.hisal;

范例：现在要求按照以下的样式写出工资的等级

1.第五等级工资

2.第四等级工资

3.第三等级工资

4.第二等级工资

5.第一等级工资

此时只能使用DECODE()函数

select e.ename,e.sal,d.dname,

DECODE(s.grade,1,'第五等工资',2,'第四等工资',3,'第三等工资',4,'第二等工资',5,'第一等工资'),

m.ename,m.sal,

DECODE(ms.grade,1,'第五等工资',2,'第四等工资',3,'第三等工资',4,'第二等工资',5,'第一等工资')

from emp e,dept d,salgrade s,emp m,salgrade ms

where e.deptno=d.deptno and

e.sal between s.losal and s.hisal and e.mgr=m.empno

and m.sal between ms.losal and ms.hisal;

结果：

ENAME	SAL	DNAME	DECODE(S.G	ENAME	SAL	DECODE(MS.
JAMES	950	SALES	第五等工资	BLAKE	2850	第二等工资
ALLEN	1000	SALES	第五等工资	BLAKE	2850	第二等工资
ADAMS	1100	RESEARCH	第五等工资	SCOTT	3000	第二等工资
MILLER	1235	ACCOUNTING	第四等工资	CLARK	2450	第二等工资
WARD	1250	SALES	第四等工资	BLAKE	2850	第二等工资
MARTIN	1250	SALES	第四等工资	BLAKE	2850	第二等工资
TURNER	1500	SALES	第三等工资	BLAKE	2850	第二等工资
CLARK	2450	ACCOUNTING	第二等工资	KING	5000	第一等工资
BLAKE	2850	SALES	第二等工资	KING	5000	第一等工资
FORD	3000	RESEARCH	第二等工资	JONES	3421.25	第一等工资
SCOTT	3000	RESEARCH	第二等工资	JONES	3421.25	第一等工资
SMITH	3100	SALES	第一等工资	FORD	3000	第二等工资
JONES	3421.25	RESEARCH	第一等工资	KING	5000	第一等工资

第十四章多表查询-连接

多表查询连接问题

一、左、右连接

现在dept表中存在4条数据：

SELECT *FROM dept;

DEPTNO DNAME LOC

--------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

现在将emp表和dept表关联查询，查询一下指定的字段。

SELECT e.empno,e.ename,d.deptno,d.dname,d.loc

FROM emp e,dept d

WHERE e.deptno=d.deptno;

结果：

EMPNO	ENAME	DEPTNO	DNAME	LOC
7369	SMITH	20	RESEARCH	DALLAS
7499	ALLEN	30	SALES	CHICAGO
7566	JONES	20	RESEARCH	DALLAS
7698	BLAKE	30	SALES	CHICAGO
7782	CLARK	10	ACCOUNTING	NEWYORK
.............部分已省略
共有14行记录

此时发生了变化，部门中一共有四个部门的信息，但此时只列出了三个，因为在雇员表中没有指定40部门的雇员。现在如果我想让40部门显示出来的时候，就需要左右连接了。如我们添加一个(+)符号.

SELECT e.empno,e.ename,d.deptno,d.dname,d.loc

FROM emp e,dept d

WHERE e.deptno=d.deptno(+);---->以e表为准默认是左连接，所以这个(+)写与不写是一样的，肯定无法显示40部门的信息。

结果：

EMPNO	ENAME	DEPTNO	DNAME	LOC
7369	SMITH	20	RESEARCH	DALLAS
7499	ALLEN	30	SALES	CHICAGO
7566	JONES	20	RESEARCH	DALLAS
7698	BLAKE	30	SALES	CHICAGO
7782	CLARK	10	ACCOUNTING	NEW YORK
. .............部分已省略
共有14行记录

显然与不加是一个样的。那么现在把（+）放在等号的左边。

SELECT e.empno,e.ename,d.deptno,d.dname,d.loc

FROM emp e,dept d

WHERE e.deptno(+)=d.deptno;---->以d表为准

结果：

EMPNO ENAME DEPTNO DNAME LOC

---------- ---------- - --------- -------------- -------------

7782 CLARK 10 ACCOUNTING NEW YORK

7566 JONES 20 RESEARCH DALLAS

7499 ALLEN 30 SALES CHICAGO

40 OPERATIONS BOSTON--------->出现了40部门

..............部分已省略

共15行记录

可以发现40部门出现了，所以此时就使用到了右连接。

(+)在等号左边表示右连接

(+)在等号右边表示左连接

因为把(+)放在等号右边的时候没有变化，跟不加一样，所以默认是左连接。

左右连接在开发中使用较多，实际上之前在查找雇员姓名及每一位雇员领导的时候就该使用左右连接了。

范例：查询雇员的姓名、编号、及其领导的编号、姓名。

SELECT e.ename,e.empno,m.ename,m.empno

FROM emp e,emp m

WHERE e.mgr=m.empno;

结果：

ENAME EMPNO ENAME EMPNO

---------- ---------- ---------- ------------------------------------

SMITH 7369 FORD 7902

ALLEN 7499 BLAKE 7698

WARD 7521 BLAKE 7698

JONES 7566 KING 7839

..............部分已省略

已选择13行。

发现查询出来的结果缺少了一条记录，因为其是最高领导，所以mgr为空。那么怎么把这个空显示出来呢。

SELECT e.ename,e.empno,m.ename,m.empno

FROM emp e,emp m

WHERE e.mgr=m.empno(+);

结果：

ENAME EMPNO ENAME EMPNO

---------- ---------- ---------- --------------------------------------------

SMITH 7369 FORD 7902

CLARK 7782 KING 7839

SCOTT 7788 JONES 7566

KING 7839 --------------->已显示

TURNER 7844 BLAKE 7698

..............部分已省略

已选择14行。

加入了左连接之后可以发现KING出现了。

二、SQL:1999语法对SQL的支持。

1.交叉连接(CROSS JOIN)：作用是产生笛卡尔积【了解】

范例：SELECT * FROM emp CROSS JOIN dept;------>也是产生56行记录，说明产生了笛卡积

2.自然连接(NATURAL JOIN)：自动进行关联字段的匹配【了解】

范例：SELECT * FROM emp NATURAL JOIN dept;---->产生了14条记录，消除笛卡尔积(自动进行了字段匹配)

3.USINT子句：直接指定关联的操作列【了解】

范例：

SELECT * FROM emp e JOIN dept d USING(deptno)

WHERE deptno=30;-------->产生了6行记录打印两张表所有的30部门的信息

4.ON 子句：表示自己编写连接条件【了解】

范例：

SELECT * FROM emp e JOIN dept d

ON(e.deptno=d.deptno)

WHERE e.deptno=30;------>与上面产生的结果是一样的

5.左连接（左外连接）、右连接（右外连接）LEFT JOIN,RIGTH JOIN【重点】

范例：

SELECT * FROM

emp e RIGHT OUTER JOIN dept d

ON(e.deptno=d.deptno);

第十五章子查询

子查询：在一个查询内部还包括另外一个查询，则称为子查询。

子查询的格式：

SELECT {DISTINCT} *|查询列1 别名1|查询列2 别名2....

FROM 表1 别名1，表2 别名2....

(

SELECT {DISTINCT} *|查询列1 别名1|查询列2 别名2....

FROM 表1 别名1，表2 别名2....

{WHERE条件}

{GROUP BY 分组条件}

{ORDER BY 排序字段 DESC|ASC,排序字段 DESC|ASC....}

)别名

{WHERE条件

(

SELECT {DISTINCT} *|查询列1 别名1|查询列2 别名2....

FROM 表1 别名1，表2 别名2....

{WHERE条件}

{GROUP BY 分组条件}

{ORDER BY 排序字段 DESC|ASC,排序字段 DESC|ASC....}

)

}

{GROUP BY 分组条件{HAVING 分组条件}}

{ORDER BY 排序字段 DESC|ASC,排序字段 DESC|ASC....}

范例：要求查出比7654工资要高的全部雇员信息

首先要清楚知道7654雇员工资是多少？

SELECT sal FROM emp WHERE empno=7654;

结果：

SAL

----------------

1250

之后要以以上的结果作为后续查询的依据，只要有其它人的工资大于SAL，则表示符合条件

SELECT * FROM emp

WHERE sal>(SELECT sal FROM emp WHERE empno=7654);---->使用子查询

所有的子查询必须在“（）”中编写代码。

子查询在操作中又分为以下三类

1.单列子查询：返回的结果是一列的一个内容

2.单行子查询：返回多个列，有可能是一个完整的记录

3.多行子查询：反回多条记录

一般在程序开发中单列子查询是用的最多的。

范例：查询出工资比7654高，同时与7788从事相同工作的全部雇员信息

第1步：查询出工资比7654高的

SELECT salFROM emp

WHERE empno=7654;

第2步：7788从事的工作

SELECT job FROM emp WHERE empno=7788

第3步将两个条件进行综合查找

SELECT * FROM emp

WHERE sal>(SELECT sal FROM emp WHERE empno=7654)

AND job=(SELECT job FROM emp WHERE empno=7788);

范例：工资最低的雇员姓名、工资、工作

第1步：求最低的工资

SELECT MIN(sal) FROM emp;

第2步：以最低工资为条件进行下一步查询

SELECT ename,job,sal FROM emp

WHERE sal=(SELECT MIN(sal) FROM emp);

范例：要求查询出:部门的名称、部门的员工数、部门的平均工资、部门的最低收入雇员的姓名。

分析：需要两张表关联：dept emp

第1步：如果要想求出每个部门的员工数量及平均工资，则肯定要使用分组统计，按照dept进行分组。

SELECT deptno,COUNT(empno),AVG(sal)

FROM emp

GROUP BY deptno;

第2步：但是如果要想查出部门的名称，需要与dept表进行关联。

SELECT d.dname,ed.c,ed.a

FROM dept d,(

SELECT deptno,COUNT(empno) c,AVG(sal) a【a c都是另起的别名】

FROM emp

GROUP BY deptno) ed【ed是别名】

WHERE d.deptno=ed.deptno;

第3步;最低收入雇员的姓名

SELECT d.dname,ed.c,ed.a,e.ename

FROM dept d,(

SELECT deptno,COUNT(empno) c,AVG(sal) a,MIN(sal) min,MAX(sal) max

FROM emp

GROUP BY deptno) ed,emp e

WHERE d.deptno=ed.deptno AND e.sal=ed.min;

如果此时在一个部门之中同时出现两个雇员的工资是最低的，那么这个程序是错误的。

在子查询中存在以下3种操作符号(IN ANY ALL) ：

1.IN操作符指定一个查询的范围

范例：求出每个部门的最低工资的雇员的信息

每个部门的最低工资，返回的工资肯定是多个，所以此时可以使用IN指定一个操作的范围

SELECT * FROM emp

WHERE sal IN(SELECT MIN(sal) FROM emp GROUP BY deptno)

2.ANY操作，存在3种情况分别是 >ANY =ANY <ANY

(1)=ANY与IN的功能完全一样

如：SELECT * FROM emp

WHERE sal =ANY(SELECT MIN(sal) FROM emp GROUP BY deptno);

(2)>ANY 比最小的值要大

如：SELECT * FROM emp

WHERE sal>ANY(SELECT MIN(sal) FROM emp GROUP BY deptno);

(3)<ANY 比最大的值要小

如：SELECT * FROM emp

WHERE sal <ANY(SELECT MIN(sal) FROM emp GROUP BY deptno);

3.ALL 操作(分为：>ALL <ALL)

(1)>ALL 比最大的值要大

如：SELECT * FROM emp

WHERE sal >ALL(SELECT MIN(sal) FROM emp GROUP BY deptno);

(2)<ALL 比最小的值要小

如：SELECT * FROM emp

WHERE sal <ALL(SELECT MIN(sal) FROM emp GROUP BY deptno);

对于子查询来讲,还可以进行多列子查询，一个子查询中同时可以返回多个查询的列。

【了解】

SELECT *

FROM emp

WHERE (sal,NVL(comm,-1)) IN (

SELECT sal,NVL(comm,-1) FROM emp WHERE deptno=20);

第十七章组函数及分组统计

分组：如将男生分为一组女生分为一组。

如果想求出每一组的平均身高、平均年龄，这就是使用到分组函数。

组函数：

在SQL中常用的组函数有：

COUNT()：求出全部的记录数

MAX()：求出一组中的最大值

MIN()：求出一组值中的最小值

AVG()：求出平均值

SUM()：求和

组函数及分组统计

一、组函数

范例：COUNT()函数

select count(empno) from emp;

结果：

COUNT(EMPNO)

----------------------

范例：MAX() MIN()求最大最小值,一般针对于数字

求所有员工最低工资

select min(sal) from emp;

结果：

MIN(SAL)

-------------------

800

求所有员工的最高工资

select max(sal) from emp;

求和及平均值

范例：求20部门中的总工资

SELECT SUM(sal) FROM emp WHERE deptno=20;

求所有员工的平均工资

SELECT AVG(sal) FROM emp;

二、分组统计

要想使用分组统计，首先应该固定其语法，使用GROUP BY 进行分组，此时SQL语法格式如下：

SELECT {DISTINCT} *|查询列1 别名1|查询列2 别名2....

FROM 表1 别名1，表2 别名2....

{WHERE条件}

{GROUP BY 分组条件}

{ORDER BY 排序字段 DESC|ASC,排序字段 DESC|ASC....}

范例：求出每个部门的雇员数量，应该按照部门编号划分，按照deptno分组。

SELECT deptno,COUNT(empno) FROM emp

GROUP BY deptno;

结果：

DEPTNO COUNT(EMPNO)

--------- -----------------------

30 6

20 5

10 3

求每个部门的平均工资

SELECT deptno,AVG(sal)

FROM emp

GROUP BY deptno;

要分组时要注意：

SELECT COUNT(empno) FROM emp;---->可以

SELECT deptno,COUNT(empno) FROM emp;---->不可以，不是单组分组函数

在查询的时候，以上的代码不能正确的执行是因为：

1.如果程序中使用了分组函数，则有两种可以使用的情况：

(1)程序中存在了GROUP BY,并指定了分组条件，这样可以将分组条件一起查询出来。

(2)如果不使用分组的话，则只能单独的使用分组函数。

2.在使用分组函数时，不能出现分组函数和分组条件之外的字段。

SELECT deptno,empno,COUNT(empno)---->empno属于分组以外的字段，这样不可以

FROM emp

GROUP BY deptno;

此时提示empno不是GROUP BY的表达式所以无法使用。

范例：按部门分组，并显示部门的名称及部门的员工数。

SELECT d.dname,COUNT(e.empno) FROM emp e,dept d

WHERE e.deptno=d.deptno

GROUP BY d.dname;

范例：要求写出平均工资大于2000的部门编号和平均工资。

错误的代码：

SELECT deptno,AVG(sal)

FROM emp

WHERE AVG(sal)>2000

GROUP BY deptno;

----------------------->这是不行的，因为在WHERE句中不能使用分组函数（AVG()）。

分组函数只能在分组中使用不能在WHERE中出现。如果现在要指定分组的条件，只能通过第二种条件的指令：

HAVING 此时的语法结构为：

SELECT {DISTINCT} *|查询列1 别名1|查询列2 别名2....

FROM 表1 别名1，表2 别名2....

{WHERE条件}

{GROUP BY 分组条件{HAVING 分组条件}}

{ORDER BY 排序字段 DESC|ASC,排序字段 DESC|ASC....}

范例：使用HAVING完成以上的操作

SELECT deptno,AVG(sal) FROM emp

GROUP BY deptno HAVING AVG(sal)>2000;---->将以前WHERE中的条件句放在HAVING

范例：显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且在满足从事同一工作的雇员月工资合计大于$5000，输出结果按月工资的合计升序排列。

第1步：显示全部的非销售人员：job<>'SALESMAN'

SELECT *

FROM emp

WHERE job<>'SALESMAN';------>SALESMAN一定要大写，因为他在表中的字段值就是大写的

第2步：按工作分组，同时求出工资的总和

SELECT job,SUM(sal) FROM emp

WHERE job<>'SALESMAN'

GROUP BY job;

第3步：对分组的条件进行限制，工资总和大于5000

SELECT job,SUM(sal) FROM emp

WHERE job<>'SALESMAN'

GROUP BY job HAVING SUM(sal)>5000;

第4步：工资的合计升序排列

SELECT job,SUM(sal) sum<--别名

FROM emp

WHERE job<>'SALESMAN'

GROUP BY job HAVING SUM(sal)>5000

ORDER BY sum;

分组的简单原则：只要一列上存在重复的内容才有可能考虑到分组。

注意：分组函数可以嵌套使用，但是在组函数嵌套使用的时候不能再出现分组条件的查询语句。

范例：求出平均工资最高的部门

错误的代码：

SELECT deptno,MAX(AVG(sal)) FROM emp

GROUP BY deptno;--->出现不是单组分组函数错误

正确的代码：

SELECT MAX(AVG(sal))--->去掉deptno

FROM emp

GROUP BY deptno;

第十八章数据库对象-视图

数据库对象之视图

学习目标：

1.掌握视图的作用及定义

2.掌握序列的使用：SEQUENCE

3.掌握PowerDesigner设计工具的使用

4.了解同义词、用户管理、嵌套及可变数组

5.理解数据库的设计范式

一、视图(重点)

视图的功能：一个视图实际上就是封装了一个复杂的子查询。

创建视图的语法：

CREATE VIEW 视图名称 AS 子查询

实际上此时的子查询就表示一个非常复杂的语句。

范例：

建立一个视图，此视图包含了全部的20部门的雇员信息(雇员姓名、编号、工作、雇佣日期)。

在创建之们先保证Oracel的OracleServiceMLDN和监听服务开启。

CREATE VIEW empv20 AS SELECT empno,ename,job,hiredate FROM emp WHERE deptno=20;

结果：视图创建完成

视图在创建完成之后，我们就可以像查找表一样直接对视图进行查询操作。

查询视图：SELECT * FROM empv20;

结果：

EMPNO ENAME JOB HIREDATE

---------- ---------- --------- ---------------------

7369 SMITH CLERK 17-12月-80

7566 JONES MANAGER 02-4月 -81

7788 SCOTT ANALYST 19-4月 -87

7876 ADAMS CLERK 23-5月 -87

7902 FORD ANALYST 03-12月-81

此时是通过视图找到20部门的全部信息，也就是可以发现，可以使用视图包装需要的的查询语句，此时视图只包含了四个字段的信息，如果我们想多增加一个字段的信息。如我们增加一个sal字段。

CREATE VIEW empv20 AS SELECT empno,ename,job,sal,hiredate FROM emp WHERE deptno=20;

结果：名称已由现有对象使用说明视图是无法重名的。此时只能先删除后重新建立。

删除视图的语法：DROP VIEW 视图名称

范例：删除empv20视图

DROP VIEW empv20;

删除之后重新执行创建视图的语句：

CREATE VIEW empv20 AS SELECT empno,ename,job,sal,hiredate FROM emp WHERE deptno=20;

但是如果所有的操作都是这样操作很麻烦。因为如果要想修改视图必须先删除视图，在Oracel中，为了方便用户修改视图，提供了一个替换的命令，此时完整的视图创建语法：

CREATE OR REPLACE VIEW 视图名称 AS 子查询;

作用以上的语法，在更改视图之前就不用再删除重建了。

CREATE OR REPLACE VIEW empv20 AS SELECT empno,ename,job,sal,hiredate FROM emp WHERE deptno=20;

所以视图的作用：可以封装复杂的查询，那么下面封装一个已经存在的复杂查询。

如这是一个复杂的子查询：

SELECT d.dname,ed.c,ed.a

FROM dept d,(

SELECT deptno,COUNT(empno) c,AVG(sal) a

FROM emp

GROUP BY deptno) ed

WHERE d.deptno=ed.deptno;

如果在开发中每次都写如此之长的语句，肯定不方便，因此要将其建立为视图，为了以后方便使用。

如：

CREATE OR REPLACE VIEW myempv AS

SELECT d.dname,ed.c,ed.a

FROM dept d,(

SELECT deptno,COUNT(empno) c,AVG(sal) a

FROM emp

GROUP BY deptno) ed

WHERE d.deptno=ed.deptno;

以后直接查询视图就可以得到之前的查询结果：如 SELECT * FROM myempv;这跟之前用子查询的结果是一样的。

范例：创建一个只包含一个20部门的雇员的信息

CREATE OR REPLACE VIEW empv20

SELECT * FROM emp WHERE deptno=20;

SQL> select * from empv20;

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-12月-80	800		20
7566	JONES	MANAGER	7839	02-4月	-81	2975	20
7788	SCOTT	ANALYST	7566	19-4月	-87	3000	20
7876	ADAMS	CLERK	7788	23-5月	-87	1100	20
7902	FORD	ANALYST	7566	03-12月-81	3000		20

下面进行更新视图的操作，在视图中不应该包含真实数据的，而且在此程序中，创建的视图实际上是存在条件的，此条件是deptno=20,如果现在将视图中的7369的部门编号修改为30.理论上是不允许修改的。

范例：将视图中的7369的部门编号修改为30。

UPDATE empv20 SET deptno=30 WHERE empno=7369;---->结果：已更新一行

SQL> select * from empv20;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

------ ---------- --------- ---------- -------------- ---------- ---------- -------------------------------------------------------------

7566 JONES MANAGER 7839 02-4月 -81 3421.25 20

7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

7876 ADAMS CLERK 7788 23-5月 -87 1100 20

7902 FORD ANALYST 7566 03-12月-81 3000 20

已经提示更新，重新查询视图时，但是在重新查看该视图后，已经没有7369这个雇员了。那么emp表中呢?

SELECT * FROM emp查看后发现在emp中7369雇员的部门编号成为30了。

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- -------------- ---------- ---------- ---------------------------------------------------------

7369 SMITH CLERK 7902 17-12月-80 800 30

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7566 JONES MANAGER 7839 02-4月 -81 2975 20

................部分已省略

这样明显不合适，因为在创建视图的时候是有条件的。一但修改之后此条件就破坏了，所以在创建视图时，SQL中提供了两个重要的参数：

(1)WITH CHECK OPTION:不能更新视图的创建条件

范例：在视图创建中使用此参数

CREATE OR REPLACE VIEW empv20

SELECT * FROM emp WHERE deptno=20

WITH CHECK OPTION;---->参数

结果：视图已创建

然后再更新操作：将视图中的7369的部门编号修改为30

UPDATE empv20 SET deptno=30 WHERE empno=7369;----->视图 WITH CHECK OPTIDN where 子句违规

说明创建条件再更改，那么其它字段呢，现在将7369的雇员姓名修改为“hellen”

范例：修改7369雇员的姓名

UPDATE empv20 SET ename='hellen' WHERE empno=7566;----->已更新一行

SQL> select * from emp;

EMPNO ENAME JOB MGR IREDATE SAL COMM DEPTNO

------ ---------- --------- ---------- -------------- ---------- ---------- --------------------------------------------------------------------------

7369 SMITH CLERK 7902 17-12月-80 800 30

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7566 hellen MANAGER 7839 02-4月 -81 2975 20--已改

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

...............部分已省略

但是，视图的本身作用还是用来查询的，所以不应该允许更改，所以此时可以使用第二个参数

(2)WITH READ ONLY：创建视图只读

那现在创建视图：

CREATE OR REPLACE VIEW empv20

SELECT * FROM emp WHERE deptno=20

WITH READ ONLY;---->参数

然后进行同样的改动，更新雇员姓名：

UPDATE empv20 SET ename='hellen' WHERE empno=7566;---->此处不允许虚拟列

视图无法更改，是只读的操作。

第十九章数据库对象-序列

数据库对象之序列

序列（重点）

在很多数据库系统中存在一个自动增长的列，如果现在要想在Oracle中完成自动增长的功能，则只能依靠序列完成，所有的自动增长操作，需要用户手工完成处理。

序列的创建格式：

CREATE SEQUENCE sequence

[INCREMENT BY n][START WITH n]---->增长的步长从几开始

[{MAXVALUE n|NOMAXVALUE}]------>最大值是多少，没有最大值

[{MINVALUE n|NOMINVALUE}]------>最小值

[{CYCLE|NOCYCLE}]--------------->是否循环

[{CACHE n|NOCACHE}]----------->是否缓存

范例：创建一个mysep的序列，验证自动增长的操作。

CREATE SEQUENCE myseq;--->序列已创建

序列创建完成之后，所有的自动增长应该由用户自己处理，所以在序列中提供了以下两种操作，

(1)vextVal：取得序列的下一个内容

(2)currVal：取得序列的当前内容

范例：建立一张表以验证序列的操作

CREATE TABLE testseq(

next NUMBER,

curr NUMBER

)------------->表已创建

向表中添加数据，添加数据的时候，需要手工的使用序列。

范例：使用序列

INSERT INTO testseq(next,curr) VALUES (myseq.nextval,myseq.currval);--->已创建一行

将以上的语句执行5次：

INSERT INTO testseq(next,curr) VALUES (myseq.nextval,myseq.currval);

......

INSERT INTO testseq(next,curr) VALUES (myseq.nextval,myseq.currval);

......

范例：查询myseq表，观察序列的变化

NEXT CURR

---------- -------------------

1 1

2 2

3 3

4 4

5 5

从结果中可以发现，nextval始终在进行自动增长的操作，而currval使用取出当前操作的序列结果。

也就是说现在的这种序列，每次增长的幅度的是1，那么也可以修改序列的增长幅度。

可以用以下的一个参数：

第次增长的幅度：INCREMENT BY 长度

范例：重新建立序列

先删除序列：DROP SEQUENCE myseq;

重新创建序列：CREATE SEQUENCE myseq INCREMENT BY 2;--->序列已创建

创建之后来进行一个测试，为了能说明问题，将testseq表从新创建，创建完成之后，重新进行数据插入操作。

插入5次数据。

其结果为：

SQL> select * from testseq;

NEXT CURR

---------- ----------------

1 1

3 3

5 5

7 7

9 9------->每次增长的幅度是2

默认情况下序列是从1开始的，可以通过START WITH 来指定开始的位置。

DROP SEQUENCE myseq;

CREATE SEQUENCE myseq INCREMENT BY 2 START WITH 10;

序列开始的位置是10;再次进行插入操作（5次）

其结果为：

SQL> select * from testseq;

NEXT CURR

---------- ---------------

1 1

3 3

5 5

7 7

9 9

10 10

12 12

14 14

16 16

18 18

重新创建一个序列，让其序列固定在1 3 5 7 9 循环序列

CREATE SEQUENCE myseq

MAXVALUE 10

START WITH 1

INCREMENT BY 2

CACHE 2 CYCLE;

重新建立testseq表，插入数据。

其结果为：

SQL> select * from testseq;

NEXT CURR

---------- ----------

1 1

3 3

5 5

7 7

9 9

1 1

现在序列的内容是循环的，但是从实际来看序列使用最多的语法是：CREATE SEQUENCE 序列名;

第二十章数据库对象-同义词

数据库对象之同义词

同义词（了解）

什么是同义词

同义词是数据库的一个对象。

数据库的对象包括：同义词、序列、视图、索引。

之前一直存在这样的查询语句：

SELECT SYSDATE FROM dual;

SQL> SELECT SYSDATE FROM dual;

SYSDATE

--------------

12-11月-10

那么dual到底是什么东西？dual是一张虚拟表，他虽然是虚拟表，可是此表到底是在那里定义的呢？如果现在使用SYSTEM连接数据库，查询此张表是否属于SYSTEM用户，则找不到，但是在SYS用户下可以找到。

conn sys/system as sysdba;

SQL> SELECT * FROM tab WHERE TNAME='DUAL';

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

DUAL TABLE

很奇怪dual表是在SYS帐户下的，但是我们在SCOTT帐户下就可以访问，正常的访问应该是用户.表名。

如：

conn sys/system as sysdba;

select * from scott.emp;

此时实际上就是同义词的作用了。同义词可以让其它用户通过一个名称方便的访问“用户名.表名称”

如：select * from dual 代替了 select * from scott.emp;

1.创建同义词的语法：CREATE SYNONYM 同义词名称 FOR 用户名.表名称

范例：将scott.emp定义emp的同义词

CREATE SYNONYM emp FOR scott.emp;

原来在SYS帐户下不能看emp表的内容，因为emp表就不是SYS帐户下的，那么创建了同义词之后就可以了。

show user

select * from emp;

2.创建同义词

DROP SYNONYM 同义词名称;

如：drop synonym emp;

现在在SYS帐户下就不能查看emp表了。

注：这种特性只适合于Oracel数据库。

所以：

同义词是现有对象的一个别名。

一、他的作用是：

1.简化SQL语句

2.隐藏对象的名称和所有者

3.提供对对象的公共访问

二、同义词共有两种类型：私有同义词公有同义词

1.私有同义词只能在其模式内访问，且不能与当前模式的对象同名。（这里的模式可以理解为帐户）

2.创建一个私有的同义词：CREATE SYNONYM emp FOR SCOTT.emp;（所以默认私有）

3.创建一个公有的同义词：CREATE PUBLIC SYNONYM emp_syn FOR SCOTT.emp;

在创建公有同义词时，赋于其权限：grant all on 公有同义词 to 用户

三、创建或替换现有的同义词：

CREATE OR REPLACE SYNONYM emp_syn FOR SCOTT.emp;

四、删除同义词：

SQL> DROP SYNONYM emp;

SQL> DROP PUBLIC SYNONYM emp_syn;

第二十一章数据库对象-索引

数据库对象之索引

索引的作用：最主要的目的是提高查询速度和性能的。所有索引都基于了一种技术B-Tree(二叉树)。

索引对表说来相当于一个目录的作用，索引是占空间的。

● 索引是与表相关的一个可选结构

1.用以提高 SQL 语句执行的性能

2.减少磁盘I/O

3.使用 CREATE INDEX 语句创建索引

4.在逻辑上和物理上都独立于表的数据

Oracle 自动维护索引

● 索引有各种类型，除了标准索引外，还有一些特殊类型的索引：唯一索引、位图索引、组合索引、基于函数的索引、反向键索引。

● 创建标准索引：

SQL> CREATE INDEX item_index ON itemfile (itemcode)--表（列）

TABLESPACE index_tbs;

创建以后，如果我们在itemcode列上进行查询时，会自动启用。

范例：在emp表的empno列上创建一个索引

CREATE INDEX a ON emp(empno);

重建索引：

SQL> ALTER INDEX item_index REBUILD;

删除索引：

SQL> DROP INDEX item_index;

● 唯一索引

唯一索引确保在定义索引的列中没有重复值

Oracle 自动在表的主键列上创建唯一索引。如果在表中的某一列创建一个唯一索引，他里边的值是不能得复的，我们在创建主键时会自动创建一个唯一索引。所以表中如果有主键了，就不用去创建唯一索引了。

使用CREATE UNIQUE INDEX语句创建唯一索引：

SQL> CREATE UNIQUE INDEX item_index

ON itemfile (itemcode);

● 组合索引

组合索引是在表的多个列上创建的索引。就像我们在创建主键时，可以多列的进行创建。

索引中列的顺序是任意的。

如果 SQL 语句的 WHERE 子句中引用了组合索引的所有列或大多数列，则可以提高检索速度

SQL> CREATE INDEX comp_index

ON itemfile(p_category, itemrate);

● 反向键索引

反向键索引反转索引列键值的每个字节

通常建立在值是连续增长的列上，使数据均匀地分布在整个索引上

创建索引时使用REVERSE关键字

SQL> CREATE INDEX rev_index

ON itemfile (itemcode) REVERSE;

如：

1002--->2001

1003--->3001 主要减少叉的密度，将其打散

将个反射键索引恢复成正常索引：注：但是普通索引是不能变成反向键索引的

SQL> ALTER INDEX rev_index REBUID NOREVERSE;

● 位图索引：

位图索引适合创建在低基数列上

位图索引不直接存储ROWID，而是存储字节位到ROWID的映射

减少响应时间

节省空间占用

Oracle位图索引主要是应用在数据仓库方面,其目的是查询效率更高,占用更小的索引空间,使用存储更有效,位图索引使用压缩形式存储

SQL> CREATE BITMAP INDEX bit_index

ON order_master (orderno);

使用位图索引的注意事项：

如果要使用位图索引，初始化参数STAR_TRANSFORMATION_ENABLED应该设置为TRUE.(alter system set STAR_TRANSFORMATION_ENABLED= true;)

优化模式应该是CBO。对于数据仓库的环境中，总是应该考虑使用CBO(COST-BASEDOPTIMIZER)。

位图索引应该建立在每一个事实表的外键列上。(这只是一个一般的规则.)

在OLTP上有更新的表不建议使用位图索引

位图索引适用在低基数列上(大量重复数据)

● 索引的创建原则

在经常做为条件的字段上创建索引

一个表允许创建几百个索引,通常我们都不会这样做,因为索引提高了查询效率,同时也给INSERT/UPDATE/DELETE的效率带来了影响,因为需要重新组织索引。

如果某几个字段经常一起出来做为条件,则创建多列索引(组合索引)。

使用以上的索引都是为了提高查询速度的，大数据库量才使用索引，创建索引以后不要进行频繁更新。

● 聚簇索引

聚簇是根据码值找到数据的物理存储位置，从而达到快速检索数据的目的。Oracle聚簇索引的顺序就是数据的物理存储顺序，叶节点就是数据节点。非聚簇索引的顺序与数据物理排列顺序无关，叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

注意：一个表最多只能有一个聚簇索引。

BTREE索引–聚簇

聚簇索引一般用于表的联接字段,比如,部门表的部门编号与员工表的所属部门,

创建聚簇索引要先创建聚簇,然后把表的字段放置到簇中

创建聚簇

CREATE CLUSTER clusterName(column Type) [OTHER OPTIONS]

添加簇中的字段

CREATE TABLE(col type,,FOREIGNKEY (deptno),,,) CLUSTER clusterName(column);

向部门表插入数据之前,需要先创建聚簇索引

CREATE INDEX indexName ON CLUSTER clusterName

Oracle聚簇索引保证两个表的记录按照deptno值尽量存放到同一个物理块当中。

ORACLE中的聚簇表是指两个表有一个字段完全相同，并且在业务中经常会按这个字段为目标连接这两个表，这时建立聚簇表，两个表公用一个字段，能减少占用空间，并能明显提高连接查询速度。

范例：

创建一个簇：CREATE CLUSTER clsdept(deptno varchar2(10));

创建一个簇表：

create table departments(deptno varchar2(10),deptname varchar2(12))

CLUSTER clus(deptno);

再创建一个：

create table employees(deptno varchar2(10),empNo char(3),empName varchar(12))

CLUSTER clus(deptno);

当多个表总是基于一个字段一起查时，用到簇比较合适。

● 索引组织表

索引组织表的数据存储在与其关联的索引中

索引中存储的是行的实际数据，而不是ROWID

基于主键访问数据

CREATE TABLE 命令与 ORGANIZATION INDEX 子句一起用于创建索引组织表

SQL> CREATE TABLE ind_org_tab (

vencode NUMBER(4) PRIMARY KEY,

venname VARCHAR2(20)

)

ORGANIZATION INDEX;

普通表与索引组织表的比较：

----------------------------------------------------------------

普通表索引组织表

----------------------------------------------------------------

ROWID 唯一地标识行主键唯一地标识行

隐式的 ROWID 列没有隐式的 ROWID 列

基于 ROWID 的访问基于主键的访问

顺序扫描返回所有行完全索引扫描返回所有行，并按主键顺序排列

支持分区不支持分区

----------------------------------------------------------------

● 基于函数的索引

基于一个或多个列上的函数或表达式创建的索引

表达式中不能出现聚合函数

不能在LOB类型的列上创建

创建时必须具有 QUERY REWRITE 权限

SQL> CREATE INDEX lowercase_idx

ON toys (LOWER(toyname));

SQL> SELECT toyid FROM toys

WHERE LOWER(toyname)='doll';

● 获取索引的信息

与索引有关的数据字典视图有：

USER_INDEXES －用户创建的索引的信息

USER_IND_COLUMNS －与索引相关的表列的信息

SQL> SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME

FROM USER_IND_COLUMNS

ORDER BY INDEX_NAME, COLUMN_POSITION;

● 使用索引

当索引被创建之后Oracle的查询会自动使用索引,对于用户来讲是透明的

以下情况索引无效:

使用<>比较时,索引无效,建议使用< or >

使用前置模糊匹配%时无效

使用函数

使用不匹的数据类型

● 数据库对象总结：

同义词是现有数据库对象的别名

序列用于生成唯一、连续的序号

视图是基于一个或多个表的虚拟表

索引是与表相关的一个可选结构，用于提高 SQL 语句执行的性能

索引类型有标准索引、唯一索引、反向键索引、位图索引和基于函数的索引

索引组织表基于主键访问数据

第二十二章网络配置

Oracle网络配置

如果其它的服务齐全，我们可以后添加一个监听。

服务器端监听器配置信息包括监听协议、地址及其他相关信息。配置信息保存在名为listener.ora的文件中。在安装服务器软件时自动配置一个监听器

客户端的网络服务名配置信息包括服务器地址、监听端口号和数据库SID等，与服务器的监听器建立连接。配置信息保存在名为tnsnames.ora的文件中

Oracle中的 Net Configuration Assistant和Net Manager工具都能用来配置监听器和网络服务名

● Oracle查询工具

Oracle 提供的工具非常容易使用。Oracle 的查询工具包括：SQL*Plus、iSQL*Plus、PL/SQL

PL/SQL 是 SQL 的扩展。PL/SQL 结合了SQL语言的数据操纵能力和过程语言的流程控制能力

● Oracle 默认用户

只有用合法的用户帐号才能访问Oracle数据库

Oracle 有几个默认的数据库用户：SYS SYSTEM SCOTT

SCOTT用户是Oracle 数据库的一个示范帐户，在数据库安装时创建

● 更改和删除用户

ALTER USER 命令可用于更改口令

修改 MARTIN 用户的密码:ALTER USER MARTIN IDENTIFIED BY martinpass;

DROP USER 命令用于删除用户

删除 MARTIN 用户模式:DROP USER MARTIN CASCADE;

● Windows 中的 Oracle 服务

OracleHOME_NAMETNSListener

该服务启动数据库服务器的监听器，监听器接受来自客户端应用程序的连接请求

若监听器未启动，则客户端将无法连接到数据库服务器

OracleServiceSID

该服务启动系统标识符为SID的数据库实例，其中 SID 是在安装 Oracle 9i 时输入的数据库名称

第二十三章嵌套表、可变数组

一、嵌套表(了解)

嵌套表：在一个表中嵌套另外一个子表

例如：现在有如下一种情况，一个部门可能承接多个项目，如果此时按照原来的方法高设计，需定义两张表，

department表和project表。

CREATE TABLE department(

deptno NUMBER(2) PRIMARY KEY NOT NULL,

dname VARCHAR2(50) NOT NULL

)

CREATE TABLE project(

proid NUMBER(4) PRIMARY KEY NOT NULL,

proname VARCHAR2(50) NOT NULL,

prodate DATE NOT NULL,

deptno NUMBER(2)

CONTRAINT department_project_deptno_fk FOREIGN KEY(deptno) NO DELETE CASCADE

)

以上操作是最通用的操作，而且本身也是属于最正确的操作，但是是在Oracle引入了嵌套表的概念，可以直接将项目表的类型作为一个department表的字段类型，达到嵌套的功能。

但是如果想完成一个嵌套表的制作，则首先要保证一点：因为数据库在创建表的时候要指定字段的类型，所在嵌套表本身也需要同样指定类型，那么这种类型就需要单独定义。

CREATE TYPE project_ty AS OBJECT(--------->创建一个类型

proid NUMBER(4),

proname VARCHAR2(50),

prodate DATE

);

知识点：show errors.可以查看错误

类型创建成功以后并不意味着此类型可以直接使用，因为此类型是一个完整的类型，所以要为此类型指定一个名称。

CREATE TYPE project_nt AS TABLE OF project_ty;

以上操作表示以后直接使用project_nt表示project_ty类型，就类似于VARCHAR(2)表示字符串是一样的。此时可以利用此类型创建department表。

CREATE TABLE department(

deptno NUMBER(2) PRIMARY KEY,

dname VARCHAR2(50) NOT NULL,

projects project_nt

) NESTED TABLE projects STORE AS project_nt_tab_temp;

对于插入数据来讲，需要需要指定每一个project_ty的类型。

INSERT INTO department(deptno,dname,projects) VALUES(1,'技术部',

project_nt(

project_ty(0001,'CRM',SYSDATE),//CRM为项目

project_ty(0002,'ERP',SYSDATE),//ERP为项目

project_ty(0003,'OA',SYSDATE)//OA为项目

)

);

此时查询嵌套表，可以返回多个项目。

SQL> select * from department;

DEPTNO DNAME

---------- --------------------------------------------------

PROJECTS(PROID, PRONAME, PRODATE)

------------------------------------------------------------------------------------------------

1 技术部

PROJECT_NT(PROJECT_TY(1, 'CRM', '21-11月-10'), PROJECT_TY(2, 'ERP', '21-11月-10'), PROJECT_TY(3, 'OA

如果此时需要查询一个部门的全部项目的话，则需要查询嵌套表。

SQL> SELECT * FROM TABLE(SELECT projectS FROM department WHERE deptno=1);

PROID PRONAME PRODATE

---------- -------------------------------------------------- --------------

1 CRM 21-11月-10

2 ERP 21-11月-10

3 OA 21-11月-10

可见将一个部门中的统一的全部项目查询出来了。

范例：更新项目编号为1的项目名称，将此名称的项目更新为“JAVA”

SQL> UPDATE TABLE (SELECT projectS FROM department WHERE deptno=1) pro

2 SET VALUE(pro)=project_ty('1','JAVA',TO_DATE('1988_10_1','yyyy-mm-dd'))

3 WHERE pro.proid=1;

已更新 1 行。

再次查询：

SQL> SELECT * FROM TABLE(SELECT projectS FROM department WHERE deptno=1);

PROID PRONAME PRODATE

---------- -------------------------------------------------- --------------

1 JAVA 01-10月-88

2 ERP 21-11月-10

3 OA 21-11月-10

二、可变数组(了解)

可变数组就是嵌套表的升级版，在可变数组中，实际上就是将内部的嵌套表的内容的长度进行了限制。

例如：

1.在一个部门有多个工人，如果按照可变数组的做法，肯定首先要做出一个工人的类型。

SQL> CREATE TYPE worker_info AS OBJECT(

2 id NUMBER,

3 name VARCHAR2(50),

4 sex VARCHAR2(6)

5 )

6 /

类型已创建。

2.面再定义数组类型：worker_info_list

SQL> CREATE TYPE AS VARRAY(10) OF worker_info;

2 /

类型已创建。

3.定义部门表，一个部门可能存在多个工人

SQL> CREATE TABLE department(

2 deptno NUMBER(2) PRIMARY KEY,

3 dname VARCHAR2(50) NOT NULL,

4 workers worker_info_list

5 );

表已创建。

4.插入测试数据

SQL> INSERT INTO department(deptno,dname,workers) VALUES(10,'财务部',

2 worker_info_list(

3 worker_info(1,'李小龙','男'),

4 worker_info(2,'张三','男'),

5 worker_info(3,'李君','女')

6 )

7 );

查询：

SQL> select * from department;

DEPTNO DNAME

---------- --------------------------------------------------

WORKERS(ID, NAME, SEX)

----------------------------------------------------------

10 财务部

WORKER_INFO_LIST(WORKER_INFO(1, '李小龙', '男'), WORKER_INFO(2, '张三', '男'), WORKER_INFO(3, '李君'

第一章 PL/SQL 简介

PL/SQL课程内容：

PL/SQL程序设计简介

PL/SQL块结构和组成元素

PL/SQL流程控制语句

-------------------------------------以上是基础，以下是高级对象

PL/SQL游标的使用-----------子程序

PL/SQL存储过程和函数-------子程序

PL/SQL包的创建和应用

PL/SQL触发器

本章目标：

理解 PL/SQL 功能和特点、了解数据类型及其用法、理解逻辑比较、理解控制结构、掌握错误处理

1.PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言，所以说QL/SQL语言是由两种语言结合下来的。他俩合到一起为目的是为了对SQL的扩展来达到功能更复杂的一些操作。

2.PL/SQL 是对 SQL 的扩展。过程化语言比如说循环、条件都是属于过程化语言其中的一部分，如插值的时候有一万条，必须得一条一条的插，比较慢，如果我们借助循环可能就非常快的完成了操作。

3.PL/SQL支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构

4.可用于创建一些高级对象如存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑。比如什么时候插值，什么时候修改，可以有条件来限定了。如给一个班级的每个人都提10分，就可以用循环为完成操作。

5.PL/SQL与 Oracle 服务器和 Oracle 工具紧密集成，具备可移植性、灵活性和安全性。

二、PL/SQL 的优点

1.支持 SQL，在 PL/SQL 中可以使用：数据操纵命令、事务控制命令、游标控制、SQL 函数和 SQL 运算符。

2.支持面向对象编程 (OOP)

3.可移植性，可运行在任何操作系统和平台上的Oralce 数据库。也就是说我们所写的PL/SQL代码包括触发器、储存过程包等写完之后可以放到任何操作系统平台上运行，这就是可移植性。

4.更佳的性能，PL/SQL 经过编译执行。如我们在使用SQL的时候我们要把在客户端写的发送到服务器端编译执行，然后返回给客户端一个请求结果，语句得逐个一条一条的进行编译，如果写了100条得一条一条的发，服务器一条一条的响应，那么每一次再请求的时候，他都会占带宽，在C/S底层要有一个网络传输。而PL/SQL中经过编译执行的。如我们创建一个储存过程里边有100条语句，在创建它时，编译了一次，等到下次有100个人或者是1000个人只需调用一次就可以了。所以这一点就可以节约带宽，减少了网络流通量。

运行过程：

客户写的程序，如SQL语句、其它的一些储存过程、或者是自己写的一个过程语句，客户缩写完之后，它发送给

Oracle服务器，用户将整个语句，不管是用什么写的，一次都交给服务器。那么服务器做何处理呢？他是一个整合的。也就PL/SQL和SQL是合成的。合成这后，在Oracle数据库中专门有一个处理他俩的引擎，叫做PL/SQL引擎，当发到服务器的时候，服务器有两个引擎，一个是SQL引擎，一个是PL/SQL引擎，所以引擎就是一个处理代码的服务器，服务器会将不同的代码发给不同的引擎，当处理完之后，会给服务器一个集中，将结果返回给客户端。这是整个过程。

还有一个优点是在语法上：

1.与 SQL 紧密集成，简化数据处理。

2.支持所有 SQL 数据类型

3..支持 NULL 值

4.支持 %TYPE 和 %ROWTYPE 属性类型

5.安全性，可以通过存储过程限制用户对数据的访问。如我们写了一个存储过程，这里边可能存了N个表，只给一个权限，其别人访问这个存储过程，别人只操作其中赋于的几个值就可以，至于里边操作的是那一个表，对那个表执行了什么操作，不必知道。

6.PL/SQL 引擎驻留在 Oracle 服务器中。也就是在Oracle中有专门处理PL/SQL语句的引擎。该引擎接受PL/SQL块并对其进行编译执行。用户发出请求时候是一个合成，有SQL的有PL/SQL的。那么放到服务器接受他的时候会对其进行分类筛选。PL/SQL引擎的功能主要是完成过程语句的执行， SQL引擎主要是执行SQL语句，他俩都是驻留在Oracle服务器端的。他的执行完之后，Oracle服务器把结果合成之后，返回给客户端。

7.PL/SQL 块是构成 PL/SQL 程序的基本单元。将逻辑上相关的声明和语句组合在一起

PL/SQL 分为三个部分，声明部分、可执行部分和异常处理部分

[DECLARE------------声明（在Oracel中的所有变量必须先声明后作用）

declarations]

BEGIN

executable statements

----------------------------------------可执行部分

[EXCEPTION ---------异常处理部分

handlers]

END;

在整个PL/SQL块中声明部分和异常部分是可选的。可执行部分是必须的。

例：

DECLARE---声明

qty_on_hand NUMBER(5);----注：先是变量名，后是类型注：分号

--------------------------------------------------上：声明部分

--------------------------------------------------下：主体部分(包括可执行部分和异常部分)

BEGIN

SELECT quantity INTO qty_on_hand

FROM Products

WHERE product = '芭比娃娃'

FOR UPDATE OF quantity;

IF qty_on_hand > 0 THEN

UPDATE Products SET quantity = quantity + 1

WHERE product = '芭比娃娃';

INSERT INTO purchase_record

VALUES ('已购买芭比娃娃', SYSDATE);

END IF;

COMMIT;

----------------------------------------上：可执行部分

----------------------------------------下：异常部分

EXCEPTION /* 异常处理语句 */

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('出错:'|| SQLERRM);

END;

-----------------------------------------------------------

在声明部分定义变量、游标、自定义常量

可执行部分包含SQL语句和PL/SQL语句。

三、变量与常量

PL/SQL 块中可以使用变量和常量

在声明部分声明，使用前必须先声明

声明时必须指定数据类型，每行声明一个标识符

在可执行部分的 SQL 语句和过程语句中使用

声明变量和常量的语法：

identifier [CONSTANT] datatype [NOT NULL]

[:= | DEFAULT expr];

给变量赋值有两种方法：

使用赋值语句 :=

使用 SELECT INTO 语句（基于查询得到的值）

如：

DECLARE

icode VARCHAR2(6);---变量

p_catg VARCHAR2(20);

p_rate NUMBER;

c_rate CONSTANT NUMBER := 0.10;--常量

BEGIN

...

icode := 'i205';---第一种赋值

SELECT p_category, itemrate * c_rate---第二种赋值

INTO p_catg, p_rate

FROM itemfile WHERE itemcode = icode;

...

END;

范例：第1种赋值

SQL> declare

2 v_test number:=90;

3 begin

4 DBMS_OUTPUT.PUT_LINE(v_test);

5 end;

6 /

PL/SQL 过程已成功完成。

SQL> set serverout on;---打开会话命令

SQL> /

90----结果

PL/SQL 过程已成功完成。

第2种赋值：将表中某个员工的工资赋值给一个变量

SQL> declare

2 v_sal number;

3 begin

4 select sal into v_sal from emp where empno=7499;

5 DBMS_OUTPUT.PUT_LINE('员工工资为：'||v_sal);

6 end;

7 /

员工工资为：1600

PL/SQL 过程已成功完成。

第二章 PL/SQL数据类型

PL/SQL 支持的内置数据类型

数据类型：标量类型、 LOB类型、属性类型

标量类型：数字类型、字符类型、布尔类型、日期类型

一、数字类型

LOB类型：BFILE(从Oracle中向系统中存二进制文件) BLOB(二进制数据) CLOB(大的字符) NCLOB

属性类型：

%TYPE :提供某个变量或数据库表列的数据类型

%ROWTYPE:提供表示表中一行的记录类型

NUMBER类型：BINARY_INTEGER、NUMBER、PLS_INTEGER

1.BINARY_INTEGER(-231-231)

子类型:

1.Natural(0-231)（非负及null）

2.Positive(1-231)(正数和null)

3.NaturalN(非空及非负)

4.positiveN(正数和非null)

5.signtype (要将整型变量限制为值-1,0,1)

1,2,3,4用于防止将空值赋予整型变量。

2.Number(1E-130~~ 10E125)

Number子类型：

子类型：DEC 、DECIMAL 、NUMERIC 用于声明最高精度为３８位十进制数字的定点数。定点数的精确度要比浮点数的精确度高。因为定点数是以十进制算的。如果我们保存货币一类的尽量用定点数。

子类型：DOUBLE、 PRECISION 和FLOAT用于声明最高精度为126个二进制位（大约相当于38位十进制数字）浮点数.

子类型：real:用于声明最高精度为63个二进制位（大约相当于１８位十进制数字）浮点数。

子类型：INTEGER、ＩＮＴ、SMALLINT用于声明最高精度为３８位十进制数字的整数。

3.PLS_INTEGER:（-231~~231）

此数据类型用于存储带符号的整数。

特点：它比以上两种数据类型的执行速度更快。PLS_INTEGER运算以机机器运算为基础。而NUMBER和BINARY_INTEGER运算以库算术运算为基础。比ＮUMBER需要的存储空间小。

二、字符类型

字符数据类型包括：CHAR、VARCHAR2、LONG、RAW、LONG RAW

PL/SQL 的数据类型与 SQL数据类型的比较：

数据类型	SQL类型	PL/SQL类型
CHAR	1..2000	1..32767
LONG	1..2GB	1..32760
LONG RAW	1..2GB	1..32760
RAW	1..2000	1..32767
VARCHAR2	1..4000	1..32767

从上可以看出同一类型可以出现放不下的可能，所有用的时候有注意。

三、日期类型

日期时间类型

存储日期和时间数据

常用的两种日期时间类型

DATE

TIMESTAMP

四、布尔类型(在SQL中没有而在PL/SQL中存在)

此类别只有一种类型，即BOOLEAN类型，用于存储逻辑值(TRUE、FALSE和NULL)。

需要注意的是：1.不能向数据库中插入BOOLEAN数据 2.不能将列值保存到BOOLEAN变量中 3.只能对BOOLEAN变量执行逻辑操作

五、LOB数据类型(大对象数据类型)

大对象数据类型主要用于存储大文本、图像、视频剪辑和声音剪辑等非结构化数据。

LOB 数据类型可存储最大4GB的数据。

LOB 类型包括：

BLOB 将大型二进制对象存储在数据库中

CLOB 将大型字符数据存储在数据库中

NCLOB 存储大型UNICODE字符数据

BFILE 将大型二进制对象存储在操作系统文件中

六、属性类型

用于引用数据库列的数据类型，以及表示表中一行的记录类型

属性类型有两种：

%TYPE - 引用变量和数据库列的数据类型，数据库中是什么类型，它会自动匹配，不用指定类型

%ROWTYPE - 提供表示表中一行的记录类型

范例一：

SQL> declare

2 v_ename emp.ename%type;

3 v_empno emp.empno%type;

4 begin

5 select empno,ename into v_empno,v_ename from emp--赋值

6 where empno=7499;

7 DBMS_OUTPUT.PUT_LINE('员工号为:'||v_empno||'员工名为:'||v_ename);

8 end;

9 /

员工号为:7499员工名为:ALLEN

PL/SQL 过程已成功完成。

范例二

SQL> declare

2 v_rec_emp emp%rowtype;

3 begin

4 select empno,ename into v_rec_emp.empno,v_rec_emp.ename from emp

5 where empno=7499;

6 DBMS_OUTPUT.PUT_LINE('员工号为:'||v_rec_emp.empno||'员工名为:'||v_rec_emp.ename);

7 end;

8 /

员工号为:7499员工名为:ALLEN

PL/SQL 过程已成功完成。

好处：不光写方便，而且当数据表的结构发生变化时，不用改动PL/SQL代码。

使用属性类型的优点：

不需要知道被引用的表列的具体类型

如果被引用对象的数据类型发生改变，PL/SQL 变量的数据类型也随之改变

以上都是Oracle自带的记录类型，自己也可以创建记录类型。类似于JAVA中的结构可以自己定义。

记录类型是把逻辑相关的数据作为一个单元存储起来，它必须包括至少一个标量型或RECORD 数据类型的成员，称作PL/SQL RECORD 的域(FIELD)，其作用是存放互不相同但逻辑相关的信息。

定义记录类型语法如下:

TYPE record_type IS RECORD(

Field1 type1 [NOT NULL] [:= exp1 ],

Field2 type2 [NOT NULL] [:= exp2 ],

. . . . . .

Fieldn typen [NOT NULL] [:= expn ] ) ;

范例：

SQL> declare----声明一个记录类型rec_emp

2 type rec_emp is record

3 (

4 v_sal emp.sal%type,

5 v_empno emp.empno%type,

6 v_ename emp.ename%type);

7 v_emp rec_emp;---声明一个记录类型的变量v_emp

8 begin

9 select sal,empno,ename into v_emp from emp

10 where empno=7499;

11 DBMS_OUTPUT.PUT_LINE('员工信息为：'||v_emp.v_sal||v_emp.v_empno||v_emp.v_ename);

12 end;

13 /

员工信息为：16007499ALLEN

PL/SQL 过程已成功完成。

七、逻辑比较

逻辑比较用于比较变量和常量的值，这些表达式称为布尔表达式

布尔表达式由关系运算符与变量或常量组成

布尔表达式的结果为TRUE、FALSE或NULL，通常由逻辑运算符AND、OR和NOT连接

布尔表达式有三种类型：数字布尔型、字符布尔型、日期布尔型

-----------------------------------------------------------------------------------------

关系运算符S 说明

-----------------------------------------------------------------------------------------

= 比较两个变量是否相等，如果值相当，则返回 True

<>, != 比较两个变量，如果不相等，则返回 True

< 比较两个变量，检查值 1 是否小于值 2

> 比较两个变量，检查值 1 是否大于值 2

<= 比较两个变量，检查变量 1 是否小于等于变量 2

>= 比较两个变量，检查变量 1 是否大于等于变量 2

------------------------------------------------------------------------------------------

八、PL/SQL可用的SQL语句

PL/SQL是ORACLE系统的核心语言，现在ORACLE的许多部件都是由PL/SQL写成。在PL/SQL中可以使用的SQL语：

INSERT，UPDATE，DELETE，SELECT INTO，COMMIT，ROLLBACK，SAVEPOINT。

注意：在 PL/SQL中只能用 SQL语句中的 DML 部分，不能用 DDL 部分，如果要在PL/SQL中使用DDL(如CREATE table 等)的话，只能以动态的方式来使用。

九、标识符

PL/SQL程序设计中的标识符定义与SQL 的标识符定义的要求相同。要求和限制有：

标识符名不能超过30字符；

第一个字符必须为字母；

不分大小写；

不能用’-‘(减号);

不能是SQL保留字。

注意: 一般不要把变量名声明与表中字段名完全一样,如果这样可能得到不正确的结果.

十、PL/SQL程序变量命名

第三章 LP/SQL控制语句

控制结构

PL/SQL 支持的流程控制结构：

条件控制:

IF 语句

CASE 语句

循环控制:

LOOP 循环

WHILE 循环

FOR 循环

顺序控制:

GOTO 语句

NULL 语句

1、IF 语句根据条件执行一系列语句，有三种形式：IF-THEN、IF-THEN-ELSE 和 IF-THEN-ELSIF

范例一：

先插入一个表

create table toys(id int primary key,toyname varchar2(2),toyprice number);

insert into toys values(1,'a',200);

insert into toys values(2,'b',100);

insert into toys values(3,'c',90);

SQL> select * from toys;

ID TO TOYPRICE

---------- -- ----------

1 a 200

2 b 100

3 c 90

进行价格调整：

SQL> declare

2 v_toyprice toys.toyprice%type;

3 begin

4 select toyprice into v_toyprice from toys where

5 id=1;

6 if v_toyprice>=200 then

7 update toys set toyprice=toyprice-50 where id=1;

8 elsif v_toyprice<100 then

9 update toys set toyprice=toyprice+20 where

10 id=1;

11 end if;

12 end;

13 /

PL/SQL 过程已成功完成。

SQL> select * from toys;

ID TO TOYPRICE

---------- -- ----------

1 a 150

2 b 100

3 c 90

范例二：

SQL> declare

2 v_toyprice toys.toyprice%type;

3 v_id toys.id%type;

4 begin

5 v_id:=&id;

6 select toyprice into v_toyprice from toys where

7 id=v_id;

8 if v_toyprice>=200 then

9 update toys set toyprice=toyprice-50 where id=1;

10 elsif v_toyprice<100 then

11 update toys set toyprice=toyprice+20 where

12 id=v_id;

13 end if;

14 end;

15 /

输入 id 的值: 2

原值 5: v_id:=&id;

新值 5: v_id:=2;

PL/SQL 过程已成功完成。

SQL> /

输入 id 的值: 3

原值 5: v_id:=&id;

新值 5: v_id:=3;

PL/SQL 过程已成功完成。

SQL> select * from toys;

ID TO TOYPRICE

---------- -- ----------

1 a 150

2 b 100

3 c 110

2、CASE 语句用于根据单个变量或表达式与多个值进行比较

执行 CASE 语句前，先计算选择器的值

范例：

SQL> BEGIN

2 CASE '&grade'--之所以输入的时候不带引号，是因为这里带了，如果这里写成&grade，输入里必须

--带引号

3 WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('优异');

4 WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE ('优秀');

5 WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE ('良好');

6 WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE ('一般');

7 WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE ('较差');

8 ELSE DBMS_OUTPUT.PUT_LINE ('没有此成绩');---其它语句都不满足时执行

9 END CASE;

10 END;

11 /

输入 grade 的值: A

原值 2: CASE '&grade'

新值 2: CASE 'A'

PL/SQL 过程已成功完成。

SQL> /

输入 grade 的值: B

原值 2: CASE '&grade'

新值 2: CASE 'B'

PL/SQL 过程已成功完成。

也可以写成：

SQL> DECLARE

2 grade varchar2(2);

3 BEGIN

4 grade :='&grade';

5 CASE '&grade'

6 WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('优异');

7 WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE ('优秀');

8 WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE ('良好');

9 WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE ('一般');

10 WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE ('较差');

11 ELSE DBMS_OUTPUT.PUT_LINE ('没有此成绩');

12 END CASE;

13 END;

14 /

输入 grade 的值: A

原值 4: grade :='&grade';

新值 4: grade :='A';

输入 grade 的值:

3、循环控制

循环控制用于重复执行一系列语句

循环控制语句包括：

LOOP、EXIT 和 EXIT WHEN----有条件退出和无条件退出

循环控制的三种类型：

LOOP - 无条件循环

WHILE - 根据条件循环

FOR - 循环固定的次数

范例：

SQL> begin

2 loop

3 if &score>60 then

4 DBMS_OUTPUT.PUT_LINE('成绩通过');

5 exit;

6 DBMS_OUTPUT.PUT_LINE('继续');

7 end if;

8 end loop;

9 end;

10 /

输入 score 的值: 70

原值 3: if &score>60 then

新值 3: if 70>60 then

成绩通过

PL/SQL 过程已成功完成。

范例二：

SQL> declare

2 monthly_value number :=0;--月销量

3 daily_value number:=0;--日销量

4 begin

5 while monthly_value <=2000

6 loop

7 monthly_value:=daily_value*31;

8 daily_value:=daily_value+10;

9 dbms_output.put_line('每日销量：'||daily_value);

10 end loop;

11 dbms_output.put_line('每月销量：'||monthly_value);

12 end;

13 /

每日销量：10

每日销量：20

每日销量：30

每日销量：40

每日销量：50

每日销量：60

每日销量：70

每日销量：80

每月销量：2170

PL/SQL 过程已成功完成。

范例三：

SQL> declare

2 i int:=0;

3 begin

4 while i<10 loop

5 dbms_output.put_line(i);

6 i:=i+1;

7 end loop;

8 end;

10 /

结果：0 1 2 3 4 5 6 7 8 9

PL/SQL 过程已成功完成。

范例四：

begin

for i in 1.. 10 loop

dbms_output.put_line(i);

end loop;

end;

带有reverse的

begin

for i in reverse 1.. 100 loop

dbms_output.put_line(i);

end loop;

end;

4.顺序结构

顺序控制用于按顺序执行语句

顺序控制语句包括：

GOTO 语句 - 无条件地转到标签指定的语句，早期的语言使用标签，现在不介意使用。

NULL 语句 - 什么也不做的空语句

例：

DECLARE

qtyhand itemfile.qty_hand%type;

relevel itemfile.re_level%type;

BEGIN

SELECT qty_hand,re_level INTO qtyhand,relevel

FROM itemfile WHERE itemcode = 'i201';

IF qtyhand < relevel THEN

GOTO updation;--->goto到一个标签。满足的时候走这个标签

ELSE

GOTO quit;------>不满足走这个标签

END IF;

<<updation>>----->定义标签用双引号

UPDATE itemfile SET qty_hand = qty_hand + re_level

WHERE itemcode = 'i201';

<<quit>>----->标签

NULL;

END;

第四章动态SQL

动态 SQL 是指在PL/SQL程序执行时生成的 SQL 语句

编译程序对动态 SQL 不做处理，而是在程序运行时动态构造语句、对语句进行语法分析并执行。跳过了编译器

DDL 语句命令和会话控制语句不能在 PL/SQL 中直接使用，但是可以通过动态 SQL 来执行

执行动态 SQL 的语法：

EXECUTE IMMEDIATE dynamic_sql_string----->EXECUTE IMMEDIATE：立即执行的意思

[INTO define_variable_list]

[USING bind_argument_list];

范例：

SQL> begin

2 execute immediate 'create table test(id int)';--->语句放在单引号中，

3 end;

4 /

PL/SQL 过程已成功完成。

动态的查询实例：

SQL> declare

2 sql_stmt varchar2(200);

3 emp_id number(4):=7566;

4 emp_rec emp%rowtype;

5 begin

6 sql_stmt := 'select * from emp where empno = :id';

7 execute immediate sql_stmt into emp_rec using emp_id;--->using用于绑定变量

8 dbms_output.put_line(emp_rec.ename);

9 end;

10 /

PL/SQL 过程已成功完成。

所谓动态的SQL就是条件没有确定下来。

注：动态SQL编译的时候不被编译。

第五章错误处理

在运行程序时出现的错误叫做异常

发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分

异常有两种类型：

预定义异常 -- 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发.所谓预定义异常就是Oracle系统根据用户经常出现的错误，预先定义出来的一些异常。如返回多行时，就会触发一个预定义异常。

用户定义异常 -- 用户可以在 PL/SQL 块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发

范例：

SQL> declare

2 v_rec emp%rowtype;

3 begin

4 select sal into v_rec.sal from emp;

5 exception--自己定义一个异常

6 when too_many_rows then

7 dbms_output.put_line('返回太多行');

8 end;

9 /

返回太多行

PL/SQL 过程已成功完成。

如果不加异常处理部分：

SQL> declare

2 v_rec emp%rowtype;

3 begin

4 select sal into v_rec.sal from emp;--不定义异常，直接结束，返回系统的预定义异常

5 end;

6 /

declare

第 1 行出现错误:

ORA-01422: 实际返回的行数超出请求的行数--注：这里同时产生了一个号

ORA-06512: 在 line 4

一、处理预定义异常

在Oracle中的常见异常: 详情见附录

范例：

SQL> declare

2 v_rec emp%rowtype;

3 begin

4 select sal into v_rec.sal from emp where empno=90;--并没有90号员工

5 exception

6 when no_data_found then--no_data_found就是一个系统的预定义异常

7 dbms_output.put_line('无符合条件的数据');

8 end;

9 /

无符合条件的数据

PL/SQL 过程已成功完成。

二、处理用户定义异常

处理用户定义异常需要三步：

1.声明异常(EXCEPTION) 2.引发异常(RAISE) 3.处理异常

预定义异常只需第3步就可以了。

范例：

SQL> declare

2 e_test exception;--声明异常 e_test

3 v_name varchar2(10);

4 begin

5 v_name:='&name';

6 if v_name not in('a','b','c') then

7 raise e_test;--引发异常

8 end if;

9 --处理异常

10 exception

11 when e_test then

12 dbms_output.put_line('输入的信息不在规定的范围内');

13 end;

14 /

输入 name 的值: a

原值 5: v_name:='&name';--如果这此处不加单引号，输入的时候一定要带上单引号

新值 5: v_name:='a';

PL/SQL 过程已成功完成。

SQL> /

输入 name 的值: e

原值 5: v_name:='&name';

新值 5: v_name:='e';

输入的信息不在规定的范围骨

PL/SQL 过程已成功完成。

三、引发应用程序错误

用于创建用户定义的错误信息

可以在可执行部分和异常处理部分使用

错误编号必须介于 –20000 和 –20999 之间

错误消息的长度可长达 2048 个字节

引发应用程序错误的语法：

RAISE_APPLICATION_ERROR(error_number, error_message);

范例：

SQL> declare

2 e_test exception;--声明异常

3 v_name varchar2(10);

4 begin

5 v_name:='&name';

6 if v_name not in('a','b','c') then

7 raise e_test;--引发异常

8 end if;

9 --处理异常

10 exception

11 when e_test then

12 raise_application_error(-20001,'输入的信息不在规定的范围内');

--产生错误的时候，同时生成一个号，类似于预定义错误。

13 end;

14 /

输入 name 的值: /

原值 5: v_name:='&name';

新值 5: v_name:='/';

declare

第 1 行出现错误:

ORA-20001: 输入的信息不在规定的范围内--自己编的

ORA-06512: 在 line 12--ORA-06512: Oracle系统自动生成的

注：异常是有针对性的。

第六章游标-隐式游标

游标

●执行原理：

Oracle服务器---->执行PL/SQL程序---检索行--->保存到游标中--提取行--->一次处理一行

●作用：逐行处理查询结果，以编程的方式访问数据

●游标的类型：隐式游标、显式游标、REF游标(REF 游标用于处理运行时才能确定的动态 SQL 查询的结果)

当每次查询的时候，如EMP表的所有信息，他会将查询的所有信息按行排序排布像一个表一样。将结果存到内存区域里边，然后Oracle提供了一种机制，游标就像一个指针一样，可以指定他的记录，第一次指向第一条，一直往下，直到结束。

隐式游标

在PL/SQL中使用DML语句时自动创建隐式游标。

隐式游标自动声明、打开和关闭，其名为 SQL。声明、打开、关闭分部是自己完成的。

通过检查隐式游标的属性可以获得最近执行的DML 语句的信息

隐式游标的属性有：

%FOUND –SQL 语句影响了一行或多行时为 TRUE，可以利用这个特性判断语句是否执行成功了。

%NOTFOUND –SQL 语句没有影响任何行时为TRUE。

%ROWCOUNT –SQL 语句影响的行数。

%ISOPEN -游标是否打开，始终为FALSE。对于隐式游标始终是假的。

当我们执行insert update delete等语句时，就会自动创建一个隐式的游标

原toys表的内容为：

SQL> select * from toys;

ID TO TOYPRICE

---------- -- ------------------------

1 a 200

2 b 100

3 c 90

范例(%FOUND )：

SQL> declare

2 begin

3 update toys set toyprice=120 where id=1;

4 --这时就会有一个隐式游标SQL

5 if sql%found then

6 dbms_output.put_line('已更新');

7 end if;

8 end;

9 /

已更新----打印出来了，说明更新，是真

SQL> select * from toys;

ID TO TOYPRICE

---------- -- ----------

1 a 120

2 b 100

3 c 90

范例(%notfount)

SQL> declare

2 begin

3 update toys set toyprice=120 where id=11;--不存在id=11

4 --这时就会有一个隐式游标SQL

5 if sql%notfound then

6 dbms_output.put_line('没有更新');

7 else

8 dbms_output.put_line('已更新');

9 end if;

10 end;

11 /

没有更新.

因为没有id=11记录存在。

范例(%rowcount)：

SQL> declare

2 begin

3 update toys set toyprice=120 ;

4 --这时就会有一个隐式游标SQL

5 if sql%notfound then

6 dbms_output.put_line('没有相关更新');

7 else

8 dbms_output.put_line('已更新的数量为：'||sql%rowcount);

9 end if;

10 end;

11 /

已更新的数量为：3

范例(%isopen)：

SQL> declare

2 begin

3 update toys set toyprice=120 ;

4 --这时就会有一个隐式游标SQL

5 if sql%notfound then

6 dbms_output.put_line('没有相关更新');

7 else

8 dbms_output.put_line('已更新的数量为：'||sql%rowcount);

9 if sql%isopen then

10 dbms_output.put_line('游标已打开');

11 else

12 dbms_output.put_line('游标已关');--始终都是关着的

13 end if ;

14 end if;

15 end;

16 /

已更新的数量为：3

游标已关

第七章游标-显式游标

显式游标(重点)

显式游标在 PL/SQL 块的声明部分定义查询，该查询可以返回多行。也就是处理返回多行的查询，用的最多。

以前只能查一行，现在可以返回多行的查询结果。

基本步聚:声明游标打开游标提取关闭

toy表内容：

SQL> select * from toys;

ID TOPRICE TOYPRICE

---------- ------ -----------------------

1 a 200

2 b 100

3 c 90

4 d 250

5 e 300

6 f 600

已选择6行。

范例：查看emp表中的记录打印出员工的姓名、员工号、工资

DECLARE

CURSOR emp_cur IS select * from emp;--声明游标关键字为CURSOR

emp_rec emp%rowtype;--可以在放得下整行记录的变量

BEGIN

OPEN emp_cur ;--打开游标

loop

FETCH emp_cur INTO emp_rec;--提取游标放到变量中

exit when emp_cur%notfound;--找不到数据时退出

dbms_output.put_line(emp_rec.empno||emp_rec.ename||emp_rec.sal);

end loop;

END;

1.带参数的显式游标

声明显式游标时可以带参数以提高灵活性

声明带参数的显式游标的语法如下：

------------------------------------------------

CURSOR <cursor_name>(<param_name> <param_type>)

IS select_statement;

------------------------------------------------

范例：

SQL> select * from toys;

ID TO TOYPRICE

---------- -- ----------

1 a 200

2 b 100

3 c 90

SQL> declare

2 cursor toy_cur(x number) is select * from toys where toyprice>x;--声明游标

3 --注：定义参数时，只需要给定一个类型就可以了，不用给定长度

4 a toys%rowtype;--定义一个变量

5 begin

6 open toy_cur(&price);--需要输入的值

7 loop

8 fetch toy_cur into a;--提取

9 exit when toy_cur%notfound ;--有条件退出

10 dbms_output.put_line('玩具名:'||a.toyname||'价格为:'||a.toyprice);

11 end loop;

12 close toy_cur;--关闭

13 end;

14 /

输入 price 的值: 100

原值 6: open toy_cur(&price);--需要输入的值

新值 6: open toy_cur(100);--需要输入的值

玩具名:a价格为:200

玩具名:d价格为:250

玩具名:e价格为:300

玩具名:f价格为:600

PL/SQL 过程已成功完成。

-------------------------------->很明显带有参数的游标要给不带参的灵活。其>100元的结果是一样的。

还可以写多个参数如：

toy_cur(p_toyprice number,b varchar2))

where toyprice=p_toyprice and name=b;

2.使用显式游标更新行

允许使用游标删除或更新活动集中的行

声明游标时必须使用 SELECT … FOR UPDATE语句

----------------------------------------------

--创建游标

CURSOR <cursor_name> IS

SELECT statement FOR UPDATE;

UPDATE <table_name>

SET <set_clause>

WHERE CURRENT OF <cursor_name>

----------------------------------------------

删除语法：

DELETE FROM <table_name>

WHERE CURRENT OF <cursor_name>

-----------------------------------------------

更新范例：

首先查看原表中的记录:

select * from toys;

ID TO TOYPRICE

------ -- ----------

1 a 200

2 b 100

3 c 90

4 d 250

再进行更改

--将toys有中所有价格在100内加20元

declare

CURSOR toys_cur IS select * from toys where toyprice<=100 FOR UPDATE;

toys_rec toys%rowtype;

begin

open toys_cur;

loop

FETCH toys_cur INTO toys_rec;

exit when toys_cur%notfound;

update toys set toyprice=toyprice+20 where CURRENT OF toys_cur;//注：一定要是当前的

dbms_output.put_line('符合要求的有:'||'玩具名'||toys_rec.toyname||'价格为'||toys_rec.toyprice);

end loop;

end;

结果：

符合要求的有:玩具名b价格为100

符合要求的有:玩具名c价格为90

再重新查看toys表

select * from toys;

ID TO TOYPRICE

----- -- ----------

1 a 200

2 b 120

3 c 110

4 d 250

删除范例：将所有价格在100元以内的玩具删除

select * from toys;

ID TO TOYPRICE

----- -- ----------

1 a 200

2 b 100

3 c 90

因为在表中只在c玩具大小100元，所以预计结果是将c玩具删除

进行删除

declare

CURSOR toys_cur IS select * from toys where toyprice<100 FOR UPDATE;

toys_rec toys%rowtype;

begin

open toys_cur;

loop

FETCH toys_cur INTO toys_rec;

exit when toys_cur%notfound;

DELETE FROM toys where CURRENT OF toys_cur;

dbms_output.put_line('符合要求的有:'||toys_rec.toyname);

end loop;

end;

结果：符合要求的有:c

再次查看toys表的结果：

select * from toys;

ID TO TOYPRICE

------ -- ----------

1 a 200

2 b 100

3.循环游标

循环游标用于简化游标处理代码

当用户需要从游标中提取所有记录时使用

循环游标的语法如下：

-------------------------------------

FOR <record_index> IN <cursor_name>

LOOP

END LOOP;

--------------------------------------

范例：取表中的所有字段

declare

--toy_rec toys%rowtype;--toy_rec变量在此处定义可不定义，就像if语句中的i变量,不用另外声明

cursor toy_cur is select * from toys;

begin

--open toy_cur;注：这里千万不能使游标打开

for toy_rec in toy_cur

loop

dbms_output.put_line(toy_rec.toyname||toy_rec.id||toy_rec.toyprice);

end loop;

--close toy_cur;注：同理不用关闭

end;

结果：

a 1 200

b 2 100

c 3 90

第八章游标-REF游标

● REF游标和游标变量

REF 游标和游标变量用于处理运行时动态执行的 SQL 查询。也就是SQL语句没有确定，要在执行时给游标设定一个查询。使用的频率很少。主要是以游标变量的形式进行操作的。

创建游标变量需要两个步骤：

1.声明 REF 游标类型

2.声明 REF 游标类型的变量

用于声明 REF 游标类型的语法为：

--------------------------------------

TYPE <ref_cursor_name> IS REF CURSOR

[RETURN <return_type>];

--------------------------------------

REF游标分为两种类型：弱类型和强类型。它们的区别差一个return语句

弱类型：TYPE <ref_cursor_name> IS REF CURSOR;

强类型：TYPE <ref_cursor_name> IS REF CURSOR

[RETURN <return_type>];

弱类型游标不能放入包中。

打开游标变量的语法如下： OPEN cursor_name FOR select_statement;

范例：

SQL> declare

2 type ref_toy_cur is ref cursor--声明一个REF游标

3 return toys%rowtype;--强类型

4 v_rec toys%rowtype;

5 cur_toy ref_toy_cur;--声明一个REF类型的变量

6 begin

7 open cur_toy for select * from toys;--注：是操作REF类型的变量

8 loop

9 fetch cur_toy into v_rec;

10 exit when cur_toy%notfound;--退出条件

11 dbms_output.put_line(v_rec.toyname);

12 end loop;

13 close cur_toy;

14 end;

16 /

PL/SQL 过程已成功完成。

● 使用游标变量执行动态 SQL

范例：

SQL> DECLARE

2 r_emp emp%ROWTYPE;

3 TYPE c_type IS REF CURSOR;--弱类型的无RETURN

4 cur c_type;

5 p_salary NUMBER;

6 BEGIN

7 p_salary := 2500;

8 OPEN cur FOR 'select * from emp where sal>:1--这是一个占位符

9 order by sal desc'--排序

10 USING p_salary;

11 DBMS_OUTPUT.PUT_LINE('薪水大于'|| p_salary ||'的员工有：');

12 LOOP

13 FETCH cur INTO r_emp;

14 EXIT WHEN cur%NOTFOUND;

15 DBMS_OUTPUT.PUT_LINE('编号：'|| r_emp.empno

16 || ' 姓名：' || r_emp.ename|| ' 薪水：' || r_emp.sal );

17 END LOOP;

18 CLOSE cur;

19 END;

21 /

薪水大于2500的员工有：

编号：7839 姓名：KING 薪水：5000

编号：7788 姓名：SCOTT 薪水：3000

编号：7902 姓名：FORD 薪水：3000

编号：7566 姓名：JONES 薪水：2975

编号：7698 姓名：BLAKE 薪水：2850

PL/SQL 过程已成功完成。

点位符：需要一个值，这个值目前没有，先用1先占着这个位置。

● 游标变量的优点和限制

游标变量的功能强大，可以简化数据处理。

▲游标变量的优点有：

(1)可从不同的 SELECT 语句中提取结果集

(2)可以作为过程的参数进行传递

(3)可以引用游标的所有属性

(4)可以进行赋值运算

▲使用游标变量的限制：

(1)不能在程序包中声明游标变量

(2)FOR UPDATE子句不能与游标变量一起使用

(3)不能使用比较运算符

第九章子程序-过程

子程序之过程

以前的写的PL/SQL语句，从declare到end没有一个统一名字，用的时候都是将整个块拿来使用，我们叫这个为匿名块，没名字。

目标：创建和使用子程序

子程序：命名的 PL/SQL 块，编译并存储在数据库中。在数据库作为一个对象存在。

子程序的各个部分：声明部分、可执行部分、异常处理部分(可选)

子程序的分类：1.过程－执行某些操作 2.函数－执行操作并返回值

注：过程与函数的最本质上的区别就是函数有返回值。过程也可以有返回值，但是需要参数。

子程序的优点：

模块化：将程序分解为逻辑模块

可重用性：可以被任意数目的程序调用

可维护性：简化维护操作

安全性：通过设置权限，使数据更安全

● 子程序-过程

过程是用于完成特定任务的子程序。

创建过程的语法：

-----------------------------------------

CREATE [OR REPLACE] PROCEDURE

<procedure name> [(<parameter list>)]

IS|AS --选一个，两个意义是一样的

<local variable declaration>--本过程要使用的变量

BEGIN

[EXCEPTION

<exception handlers>]

END;

------------------------------------------

范例：

SQL> create or replace procedure p_emp(p_empno number)

2 as

3 v_ename varchar2(20);

4 begin

5 select ename into v_ename from emp where empno=p_empno;

6 dbms_output.put_line('员工名称'||v_ename);

7 exception

8 when no_data_found then

9 dbms_output.put_line('没有此人：');

10 end;

11 /

过程已创建。

SQL> execute p_emp(7499);--调用过程

员工名称：ALLEN

PL/SQL 过程已成功完成。

过程参数的三种模式：

IN：用于接受调用程序的值，默认的参数模式

OUT：用于向调用程序返回值

IN OUT：用于接受调用程序的值，并向调用程序返回更新的值

执行过程的语法：

EXECUTE procedure_name(parameters_list);

范例一(IN和OUT)：

SQL> create or replace procedure

--带有两个参数第1个默认有个IN，第2个为OUT

2 p_emp(p_empno number,p_sal out number)--创建过程p_emp 注：out

3 as

4 v_ename emp.ename%type;

5 v_sal number;

6 begin

7 select ename,sal into v_ename,v_sal from emp where empno=p_empno;--赋值

8 if v_sal<=2000 then--如果工资小于2000就改为2000

9 p_sal:=2000;

10 end if;

11 dbms_output.put_line('员工名为:'||v_ename);

12 end;

13 /

过程已创建。

--调用子程序过程

SQL> declare

2 v_sal number;

3 begin

4 p_emp(7499,v_sal);--将第2个参数输了出来

5 dbms_output.put_line('工资标准:'||v_sal);

6 end;

7 /

员工名为:ALLEN

工资标准:2000

范例二(IN OUT):数值的交换

SQL> create or replace procedure

2 swap(x in out number,y in out number)--声明

3 as

4 temp number;

5 begin

6 temp:=x;

7 x:=y;

8 y:=temp;

9 end;

10 /

过程已创建。

--调用参数

SQL> declare

2 a number:=50;

3 b number:=100;

4 begin

5 swap(a,b);

6 dbms_output.put_line(a);

7 dbms_output.put_line(b);

8 end;

9 /

100

PL/SQL 过程已成功完成。

将过程的执行权限授予其他用户：

SQL> GRANT EXECUTE ON find_emp TO MARTIN;

SQL> GRANT EXECUTE ON swap TO PUBLIC;

删除过程：

SQL> DROP PROCEDURE find_emp;

第十章子程序-函数

子程序之函数

● 函数是可以返回值的命名的 PL/SQL 子程序。

创建函数的语法：

------------------------------------

CREATE [OR REPLACE] FUNCTION

<function name> [(param1,param2)]

RETURN <datatype> IS|AS --注：一定要有一个返回类型，没有长度

[local declarations]

BEGIN

Executable Statements;

RETURN result;

EXCEPTION

Exception handlers;

END;

--------------------------------------

定义函数的限制：

函数常用 IN 参数，也可接受 IN OUT 或 OUT 参数

形参不能是 PL/SQL 类型。也就是使用的是SQL类型

函数的返回类型也必须是数据库类型

访问函数的两种方式：1.使用 PL/SQL 块 2.使用 SQL 语句

创建函数：

CREATE OR REPLACE FUNCTION fun_hello

RETURN VARCHAR2--注：返回SQL类型的

BEGIN

RETURN '朋友，您好';

END;

从 SQL 语句调用函数：

SQL> SELECT fun_hello FROM DUAL;--注：从dual表中调用

注：函数是不能单独存在的，要跟其它部分共同构成语句。

范例：

SQL> create or replace function fun_emp(p_sal number)

2 return varchar2

3 as

4 v_max number;

5 v_min number;

6 begin

7 select max(sal),min(sal) into v_max,v_min from emp where deptno=20;

8 if p_sal>v_min and p_sal<v_max then

9 return '工资在范围内';

10 else

11 return '不在范围内';

12 end if;

13 end;

14 /

函数已创建。

SQL> select fun_emp(900) from dual;--注：不能从原表emp表调用

FUN_EMP(900)

-----------------------------------------------

不在范围内

SQL> select fun_emp(1500) from dual;

FUN_EMP(1500)

-----------------------------------------------

工资在范围内

● 调用自定义函数

当有多个函数时，可以用使用三种方法为函数传递参数

位置表示法 functionName(arg1[,arg2,arg3…])

名称表示法 functionName(argName=>argvalue,[…..])

混合表示法：同时使用位置表示法和名称表示法为函数传递参数。

即在调用一个函数时，同时使用位置表示法和名称表示法为函数传递参数。采用这种参数传递方法时，使用位置表示法所传递的参数必须放在名称表示法传递的参数的前面，也就是说无论函数具有多少个参数，只要其中有一个使用了名称表示法，其后所有的参数都必须使用名称表示法。

调用get_salary函数

按顺序为参数赋值

按参数名称赋值

创建参数带有默认值的函数

调用带有默认值的函数

范例：获取某部门工资总和

SQL> create or replace function fun1(p_deptno number,v_count out number)

2 return number

3 as

4 v_sum number;

5 begin

6 select sum(sal),count(*) into v_sum,v_count from emp

7 where deptno=p_deptno;

8 return v_sum;

9 exception

10 when no_data_found then

11 dbms_output.put_line('没有选定的信息');

12 when others then

13 dbms_output.put_line('错误代号'||sqlcode);

14 end;

15 /

函数已创建。

--调用函数

SQL> declare

2 v_sum number;

3 v_count number;

4 begin

5 v_sum:=fun1(20,v_count);--按顺序调

6 dbms_output.put_line('工资总和为:'||v_sum||'人员个数为:'||v_count);

7 end;

8 /

工资总和为:10075人员个数为:4

PL/SQL 过程已成功完成。

--按名称调用

v_sum:=fun1(v_count=>v_count，p_deptno=>20);

注：v_sum:=fun1(v_count=>v_count，20);--不可以

--混合调用

v_sum:=fun1(20,v_count=>v_coun);

● 创建参数带有默认值的函数

范例：

SQL> create or replace function

2 fun_def(name varchar2,age int ,sex varchar2 default '男')

3 return varchar2

4 as

5 v_result varchar2(50);

6 begin

7 v_result:='姓名:'||name||'年龄:'||age||'性别:'||sex;

8 return v_result;

9 end;

10 /

函数已创建。

PL/SQL 过程已成功完成。

SQL> declare

2 v_result varchar2(50);

3 begin

4 --v_result:=fun_def('李龙',20);

5 v_result:=fun_def(age=>23,name=>'杰克');

6 dbms_output.put_line(v_result);

7 end;

8 /

姓名:杰克年龄:23性别:男--------默认为男

也可以加上性别：

SQL> declare

2 v_result varchar2(50);

3 begin

4 v_result:=fun_def(age=>23,name=>'多莉',sex=>'女');

5 dbms_output.put_line(v_result);

6 end;

7 /

姓名:多莉年龄:23 性别:女

● 过程和函数的比较：

过程	函数
作为 PL/SQL语句执行	作为表达式的一部分调用
在规格说明中不包RETURN 子句	必须在规格说明中包含 RETURN 子句
不返回任何值	必须返回单个值
可以包含 RETURN 语句，但是与函数不同，它不能用于返回值	必须包含至少一条 RETURN语句

第十一章自主事物处理

自主事物处理是与过程相关的。

实例验证：

范例一：

SQL> select * from dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> create or replace procedure first--创建第1个过程

2 is

3 v_loc varchar2(30);

4 begin

5 select loc into v_loc from dept where deptno=10;

6 dbms_output.put_line(v_loc);

7 rollback;--注：回滚

8 end;

9 /

过程已创建。

SQL> create or replace procedure second--创建第2个过程

2 is

3 v_loc varchar2(20);

4 begin

5 update dept set loc='haerbin' where deptno=10;--将10号部门的地址改为haerbing

6 first();--在第2个过程中调用第1个过程

7 select loc into v_loc from dept where deptno=10;--被回滚了

8 dbms_output.put_line(v_loc);

9 end;

10 /

过程已创建。

SQL> execute second;

haerbin

NEW YORK--回滚后

SQL> select * from dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK--没有变

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

之所以改动后的值没有被保留，是因为在first中有一个rollback;

如果想使first回滚自己的，不要回滚调用者：可以在first中加一句：PRAGMA AUTONOMOUS_TRANSACTION

范例二：

SQL> create or replace procedure first

2 is

3 v_loc varchar2(30);

4 PRAGMA AUTONOMOUS_TRANSACTION;

5 begin

6 select loc into v_loc from dept where deptno=10;

7 dbms_output.put_line(v_loc);

8 rollback;

9 end;

10 /

过程已创建。

SQL> create or replace procedure second

2 is

3 v_loc varchar2(20);

4 begin

5 update dept set loc='haerbin' where deptno=10;

6 first();

7 select loc into v_loc from dept where deptno=10;

8 dbms_output.put_line(v_loc);

9 end;

10 /

过程已创建。

SQL> execute second;

NEW YORK

haerbin

PL/SQL 过程已成功完成。

SQL> select * from dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING haerbin--已变化

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

所以自主事物处理特点为：

主事(first)务处理启动独立事务处理

然后主事务处理被暂停

自主事务处理子程序内(second)的 SQL 操作

然后终止自主事务处理

恢复主事务处理

PRAGMA AUTONOMOUS_TRANSACTION 用于标记子程序为自主事务处理

自主事务处理的特征为：

1.与主事务处理的状态无关

2.提交或回滚操作不影响主事务处理

3.自主事务处理的结果对其他事务是可见的

4.能够启动其他自主事务处理

第十二章程序包

目标：创建和使用程序包

程序包是对相关过程、函数、变量、游标和异常等对象的封装。

程序包由规范和主体两部分组成。

规范声明和主体两部分是要单独拿出来的：

在规范中可以声明：包括类型、变量、常量、异常、游标规范和子程序规范等

在主体中可以声明：对象和实现在包规范中声明的子程序和游标

● 创建程序包

1.程序包规范

CREATE [OR REPLACE]

PACKAGE package_name --包名

IS|AS

[Public item declarations]

[Subprogram specification]

END [package_name];

2.程序包主体

CREATE [OR REPLACE] PACKAGE BODY package_name

IS|AS

[Private item declarations]

[Subprogram bodies]

[BEGIN

Initialization]

END [package_name];

注：包规范声明的名和主体声明的名一定要相同。

规范声明范例：

SQL> create or replace package pack_emp

2 as

3 --公共项区

4 --过程及函数的规格声明

5 --实现创建一个过程

6 --实现创建一个函数

7 procedure pro_first(p_empno number);--过程

8 function fun_first return varchar2;--函数

9 end pack_emp;--结束包名，为了与其它的区别

10 /

程序包已创建。

主体声明范例：

SQL> SQL> create or replace package body pack_emp

SP2-0734: 未知的命令开头 "SQL> creat..." - 忽略了剩余的行。

SQL> 2 as

SQL> 3 procedure pro_first(p_empno number)

SQL> 4 is

SQL> 5 --本过程使用的变量

SQL> 6 v_ename emp.ename%type;

SQL> 7 begin

SQL> 8 --将员工的名字打印

SQL> 9 select ename into v_ename from emp where empno=p_empno;

SQL> 10 dbms_output.put_line('员工名:'||v_ename);

SQL> 11 end pro_first;

SQL> 12 --返回一个字符串

SQL> 13 function fun_first return varchar2

SQL> 14 is

SQL> 15 begin

SQL> 16 return '你好';

SQL> 17 end fun_first;

SQL> 18 end pack_emp;

SQL> 19 /

SQL>

SQL> 程序包体已创建。

调用程序包中的函数和过程范例：execute 包名.对象名

SQL> execute pack_emp.pro_first(7499);

员工名:ALLEN

PL/SQL 过程已成功完成。

● 程序包的优点：模块化、更轻松的应用程序设计、信息隐藏、新增功能、性能更佳

● 程序包中的游标：

注：游标的定义分为游标规范和游标主体两部分

在包中声明游标的要求：

在包规范中声明游标规范时必须使用 RETURN 子句指定游标的返回类型

RETURN子句指定的数据类型可以是：

1.用 %ROWTYPE 属性引用表定义的记录类型

2.程序员定义的记录类型

创建游标程序包范例：

SQL> --包规范声明

SQL> create or replace package pack_cur

2 as

3 cursor emp_cur(p_deptno number)--声明一个游标

4 return emp%rowtype;

5 procedure pro_emp_cur(p_dept number);--需要一个过程操作游标

6 end pack_cur;

7 /

程序包已创建。

SQL> --声明包主体

SQL> create or replace package body pack_cur

2 as

3 cursor emp_cur(p_deptno number)--声明一个游标

4 return emp%rowtype--注：一定要有RETURN

5 IS

6 select * from emp where deptno=p_deptno;

7 procedure pro_emp_cur(p_dept number)--需要一个过程操作游标

8 as

9 v_rec_emp emp%rowtype;

10 begin

11 open emp_cur(p_dept);

12 loop

13 fetch emp_cur into v_rec_emp;

14 exit when emp_cur%notfound;

15 dbms_output.put_line('名为:'||v_rec_emp.ename||'工资:'||v_rec_emp.sal);

16 end loop;

17 close emp_cur;

18 end pro_emp_cur;

19 end pack_cur;

20 /

程序包体已创建。

--调用包中的过程

SQL> execute pack_cur.pro_emp_cur(20);--查看20部门的全体员工

名为:JONES工资:2975

名为:SCOTT工资:3000

名为:ADAMS工资:1100

名为:FORD工资:3000

PL/SQL 过程已成功完成。

● 有关子程序和程序包的信息

USER_OBJECTS 视图包含用户创建的子程序和程序包的信息

USER_SOURCE 视图存储子程序和程序包的源代码

● 总结：

子程序是命名的 PL/SQL 块，可带参数并可在需要时随时调用

有两种类型的PL/SQL子程序，即过程和函数

过程用户执行特定的任务，函数用于执行任务并返回值

程序包是对相关类型、变量、常量、游标、异常、过程和函数等对象的封装

程序包由两部分组成，即包规范和包主体

使用程序包的优点是：模块化、更轻松的程序设计、信息隐藏、新增功能以及性能更佳

第十三章触发器讲解

触发器

定义：触发器是当特定事件出现时自动执行的存储过程

特定事件可以是执行更新的DML语句和DDL语句

特点：触发器不能被显式调用

触发器的功能：

1.自动生成数据（可以从多个表中合成由他来创建一组新的数据）

2.自定义复杂的安全权限（从多个表中限制，是跨多个表存在的）

3.提供审计和日志记录（在我的数据库执行了什么样的操作都可以记录下来）

4.启用复杂的业务逻辑（是与多个表相关的如：从一个员工辞职了，其中的一个表中删除了这个员工，其它表中关于此员工的信息都可以同时删除掉了）。

所以触发器是数据库的高级使用。

● 触发器的组成部分

触发器由三部分组成：

1.触发器语句（事件）：定义激活触发器的 DML 事件和 DDL 事件

2.触发器限制：执行触发器的条件，该条件必须为真才能激活触发器

3.触发器操作（主体）：

包含一些 SQL 语句和代码，它们在发出了触发器语句且触发限制的值为真时运行，也就是只有触发器的条件为真是，触发器的主体才会运行。

触发事件：即在何种情况下触发TRIGGER; 例如：INSERT, UPDATE, DELETE。

触发时间：即该TRIGGER 是在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和该TRIGGER 的操作顺序。如：当触发器在执行时，如果是之前，就先执行触发器，后操作表，如果是多后，先操作表，后执行触发器的主体。

触发器本身：即该TRIGGER 被触发之后的目的和意图，正是触发器本身要做的事情。例如：PL/SQL 块。

触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。语句级：如数据更改100条，触发器只执行一次，这样不适合于单条的改动。行级：每一个符合条件的行，他都会去执行，常用的是行级。

语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次；

行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。

如：SQL> …

FOR EACH ROW

WHEN (NEW.empsal>OLD.empsal)

DECLARE

Sal_diff NUMBER;

如果WHEN子句中的条件得到满足，将执行BEGIN 块中的代码

触发器最常用的对象是表。

● 创建触发器语法

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER }

{INSERT | DELETE | UPDATE [OF column [, column …]]}

ON [schema.] table_name

[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]-->引用条件

[FOR EACH ROW ]

[WHEN condition]

trigger_body;

语法分析：

1.BEFORE 和AFTER指出触发器的触发时序分别为前触发和后触发方式，前触发是在执行触发事件之前触发当前所创建的触发器，后触发是在执行触发事件之后触发当前所创建的触发器。

2.FOR EACH ROW选项说明触发器为行触发器。行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操作影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略FOR EACH ROW 选项时，BEFORE 和AFTER 触发器为语句触发器，而INSTEAD OF 触发器则为行触发器。

3.EFERENCING 子句说明相关名称，在行触发器的PL/SQL块和WHEN 子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号(:)，但在WHEN子句中则不能加冒号。

4.WHEN 子句说明触发约束条件。Condition 为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL 函数。WHEN 子句指定的触发约束条件只能用在BEFORE 和AFTER 行触发器中，不能用在INSTEAD OF 行触发器和其它类型的触发器中。

BEFORE触发器的工作原理：是先激活触发器后保存到Oracle数据库

AFTER触发器的工作原理：是行保存以数据库后后激活触发器

范例：以toys表为例AFTER

如果输入信息的ID为零，则不能输入

● 每张表最多可建立12 种类型的触发器，它们是：

BEFORE INSERT

BEFORE INSERT FOR EACH ROW

AFTER INSERT

AFTER INSERT FOR EACH ROW

BEFORE UPDATE

BEFORE UPDATE FOR EACH ROW

AFTER UPDATE

AFTER UPDATE FOR EACH ROW

BEFORE DELETE

BEFORE DELETE FOR EACH ROW

AFTER DELETE

AFTER DELETE FOR EACH ROW

如果12个都创建时容易落入一个灾难，因为创建以后，只要一符合条件就会自动触发，有可能产生递归，所以触发器要适量而止。

触发器的执行顺序：

--->执行 BEFORE语句级触发器;

--->执行 BEFORE行级触发器

--->执行 DML语句

--->执行 AFTER行级触发器?

--->执行 AFTER语句级触发器

● 触发器的限制：

CREATE TRIGGER语句文本的字符长度不能超过32KB；

触发器体内的SELECT 语句只能为SELECT … INTO …结构，或者为定义游标所使用的SELECT 语句。

触发器中不能使用数据库事务控制语句 COMMIT; ROLLBACK, SVAEPOINT 语句；

由触发器所调用的过程或函数也不能使用数据库事务控制语句；

触发器中不能使用LONG, LONG RAW 类型；

触发器内可以参照LOB 类型列的列值，但不能通过 :NEW 修改LOB列中的数据；

触发器被激活时的特殊对象：

:NEW 修饰符访问操作完成后列的值

:OLD 修饰符访问操作完成前列的值

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

注：语句级触发器不能引用:OLD和:NEW的值

触发器的发、创建：

SQL> CREATE OR REPLACE TRIGGER trig_sal

AFTER UPDATE OF empsal ON salary_records

为 salary_records 表创建

trig-sal 触发器

● 触发器的类型有：

模式(DDL)触发器、数据库级触发器、DML触发器

DML触发器包括：行级触发器、语句级触发器、INSTEAD OF 触发器

DDL 触发器：在模式中执行 DDL 语句时执行

数据库级触发器：在发生打开、关闭、登录和退出数据库等系统事件时执行

DML 触发器：在对表或视图执行DML语句时执行

语句级触发器：无论受影响的行数是多少，都只执行一次

行级触发器：对DML语句修改的每个行执行一次

INSTEAD OF 触发器：用于用户不能直接使用 DML 语句修改的视图

触发器被激活时的特殊对象： :NEW 和 :OLD

1. :NEW 修饰符访问操作完成后列的值

2. :OLD 修饰符访问操作完成前列的值

● 触发器的组成部分示例

例1：为 salary_records 表创建trig-sal 触发器

CREATE OR REPLACE TRIGGER trig_sal

AFTER UPDATE OF empsal ON salary_records

…

例2：在更新 emp_sal 列之后激活触发器

CREATE OR REPLACE TRIGGER trig_sal

AFTER UPDATE OF empsal ON salary_records

例3：只有在WHEN子句中的条件得到满足时，才激活trig_sal 触发器

FOR EACH ROW

WHEN (NEW.empsal>OLD.empsal)

DECLARE

Sal_diff NUMBER;

操作：

BEGIN

sal_diff:=:NEW.empsal-:OLD.empsal;

DBMS_OUTPUT.PUT_LINE(‘工资差额：’sal_diff);

END;

● 工作原理：

1.AFTER触发器的工作原理

先保存更新到数据库，然后激活触发器

2.BEFORE触发器的工作原理

先激活触发器，后保存到更新到数据库

● 总结：

触发器是当特定事件出现时自动执行的存储过程

触发器分为 DML 触发器、DDL 触发器和数据库级触发器三种类型

DML 触发器的三种类型包括行级触发器、语句级触发器和 INSTEAD OF 触发器

第十四章触发器实例

触发器之实例

toys表内容：

ID TONAME TOYPRICE

--- -- ------------------

1 a 200

2 b 100

3 c 90

有toys表上创建一个AFTER触发器：如果插入的数据的价格为0，则触动触发器，提示相关的信息

create or replace trigger trig

after insert

on toys

--频率

for each row

--主体

begin

if(:new.toyprice=0) then

dbms_output.put_line('有一个玩具的价格为0');

end if;

end;

--插入一条数据启动触发器

SQL> insert into toys values(4,'水枪',0);--触动了触发器

有一个玩具的价格为0

SQL> insert into toys values(5,'游戏机',10);--没有触动触发器

已创建 1 行。

二、触发器的分类：

模式(DDL)触发器、数据库级触发器、DML触发器

最常用的是DML触发器，DML触发器分为行级触发器、语句级触发器、INSTEAD OF触发器.

1.模式(DDL)触发器:在模式中执行DDL语句时执行

2.数据库级触发器：在发生打开、关闭、登录和退出数据库等系统事件时执行。

3.DML触发器：在对表或视图执行DML语句时执行

4.语句级触发器：无论受影响的行数是多少，都只执行一次

5.行级触发器：对DML语句修改的每个行执行一次

6.INSTEAD OF触发器：用于用户不能直接使用DML语句修改的视图。

范例一：行级触发器

创建一个表A 包含ID 和 NAME 两个字段

再创建一个序列 create sequence seq;

创建一个触发器：当给A表中的ID插入值，ID值要用序列中的值

create or replace TRIGGER trig

before insert or update of id on A

for each row

begin

--如果插入ID值就用序列值代替

--inserting 谓词，插入

if inserting then

select seq.nextval into :new.id from dual;

else

dbms_output.put_line('不能修改ID的值');

end if;

end;

给A表中插入值：insert into A values(10,'jack');

查看表：

select * from A;

ID NAME

------ ----------

1 jack------->产生的ID并不是10，而是1

进行修改：update A set id=5 where id=1; 不能修改ID的值

再次查看：

select * from A;

ID NAME

----- ----------

5 jack

表中的数据发生了变化，这是因为触发器的具体功能还没有实现，我们可以引发一个应用程序异常

create or replace TRIGGER trig

before insert or update of id on A

for each row-->行级触发器

begin

if inserting then

select seq.nextval into :new.id from dual;

else

dbms_output.put_line('不能修改ID的值');

raise_application_error(20003,'不能修改ID的值');

end if;

end;

范例二：语句级触发器

创建一个触发器：

create or replace trigger trig

after insert or delete or update on toys

begin

if inserting then

dbms_output.put_line('在表中执行了插入操作');

elsif updating then

dbms_output.put_line('在表中执行了更新操作');

else

dbms_output.put_line('在表中执行了删除操作');

end if;

end;

触发器创建完成之后，对表进行一系列操作如：

SQL> update toys set toyname='x ' where toyname='a';

在表中执行了更新操作

SQL> delete from toys;

在表中执行了删除操作

范例二：INSTEAD OF触发器

SQL> CREATE OR REPLACE TRIGGER upd_ord_view

INSTEAD OF UPDATE ON ord_view

FOR EACH ROW

BEGIN

UPDATE order_master

SET vencode=:NEW.vencode

WHERE orderno = :NEW.orderno;

DBMS_OUTPUT.PUT_LINE(‘已激活触发器');

END;

范例三：模式触发器

创建一个表用来记录：

create table dropTable(objName varchar2(20),objType varchar2(20),dropTime date);

创建一个触发器：

CREATE OR REPLACE TRIGGER trig

AFTER DROP ON SCHEMA

BEGIN

insert into dropTable values(ORA_DICT_OBJ_NAME,ORA_DICT_OBJ_TYPE,SYSDATE);

END;

然后对一个表进行创建删除操作：

SQL> create table B(id int);

表已创建。

SQL> drop table B;

表已删除。

在dropTable表中查看操作记录

SQL> select * from dropTable;

OBJNAME OBJTYPE DROPTIME

-------------------- -------------------- --------------

B TABLE 29-11月-10

触发器的禁用和启用

SQL> ALTER TRIGGER aiu_itemfile DISABLE;

SQL> ALTER TRIGGER aiu_itemfile ENABLE;

删除触发器:

SQL> DROP TRIGGER aiu_itemfile;

查看有关触发器的信息

USER_TRIGGERS 数据字典视图包含有关触发器的信息

SQL> SELECT TRIGGER_NAME FROM USER_TRIGGERS

WHERE TABLE_NAME='EMP';

SQL> SELECT TRIGGER_TYPE, TRIGGERING_EVENT, WHEN_CLAUSE

FROM USER_TRIGGERS

WHERE TRIGGER_NAME = 'BIU_EMP_DEPTNO';

总结：

重点是DML触发器

INSTEAD OF 触发器原理：当我们执行UPDATE INSERT 时，这条语句并不被执行，而是执行触发器里边的语句，相当于回滚，如别人要在自己的数据库中执行操作，我们就可以达到一个限制。他主要是对视图进行操作的，如果不对视图操作没什么意义。

第十五章数据库设计和三大范式

目标：

1.了解设计数据库的步骤

2.掌握如何绘制数据库的E-R图

3.理解数据库的规范化－三大范式

● 为什么需要设计数据库？

良好的数据库设计可以：

1.节省数据的存储空间

2.能够保证数据的完整性

3.方便进行数据库应用系统的开发

糟糕的数据库设计：

1.数据冗余、存储空间浪费

2.内存空间浪费

3.数据更新和插入的异常

● 软件项目开发周期

现实世界-->建模-->信息世界-->模型转换-->数据库世界-->规范化为数据库

1.需求分析阶段：分析客户的业务和数据处理需求;

2.概要设计阶段：设计数据库的E-R模型图，确认需求信息的正确和完整;

3.详细设计阶段：将E-R图转换为多张表，进行逻辑设计，并应用数据库设计的三大范式进行审核;

4.代码编写阶段：选择具体数据库进行物理实现，并编写代码实现前端应用;

5.软件测试阶段：……

6.安装部署：……

● 设计数据库的步骤

1.收集信息：

与该系统有关人员进行交流、坐谈，充分理解数据库需要完成的任务。

如：BBS论坛的基本功能：

用户注册和登录，后台数据库需要存放用户的注册信息和在线状态信息；

用户发贴，后台数据库需要存放贴子相关信息，如贴子内容、标题等；

论坛版块管理：后台数据库需要存放各个版块信息，如版主、版块名称、贴子数等；

2.标识对象（实体－Entity）

标识数据库要管理的关键对象或实体

如：实体一般是名词：

用户：论坛普通用户、各版块的版主。

用户发的主贴

用户发的跟贴（回贴）

版块：论坛的各个版块信息

3.标识每个实体的属性（Attribute）

如：论坛用户：呢称、密码、电子邮件、生日、性别、用户的等级、备注信息、注册信息、状态、积分等

主贴：发贴人、发贴表情、回复数量、标题、正文、点击数、状态、最后回复时间。

回贴：帖子编号、回帖人、回帖表情、标题、正文、回帖时间、点击数。

版块：版块名称、本版格言、点击率、发贴数

4.标识对象之间的关系（Relationship）

如：跟贴和主贴有主从关系：我们需要在跟贴对象中表明它是谁的跟贴；

版块和用户有关系：从用户对象中可以根据版块对象查出对应的版主用户的情况；

主贴和版块有主从关系：需要表明发贴是属于哪个版块的；

跟贴和版块有主从关系：需要表明跟贴是属于哪个版块的；

5.绘制E-R（Entity－Relationship）实体关系图

如用矩形表示实体，一般是名词

用椭圆表示属性，一般也是名词

用棱形表示实体间的关系，一般是动词

如：

映射基数：其中关系包括：一对一、一对多、多对一、多对多如图：

6.将E-R图转换为表

将各实体转换为对应的表，将各属性转换为各表对应的列

标识每个表的主键列，需要注意的是：没有主键的表添加ID编号列，它没有实际含义，用于做主键或外键，例如用户表中的“UID”列，版块表中添加“SID”列，发贴表和跟贴表中的“TID”列

在表之间建立主外键，体现实体之间的映射关系

● 数据规范化

仅有好的RDBMS并不足以避免数据冗余，必须在数据库的设计中创建好的表结构

Dr E.F.codd 最初定义了规范化的三个级别，范式是具有最小冗余的表结构。这些范式是：

第一范式(1st NF －First Normal Fromate)

第二范式(2nd NF－Second Normal Fromate)

第三范式(3rd NF－ Third Normal Fromate)

1.第一范式

第一范式的目标是确保每列的原子性

如果每列都是不可再分的最小数据单元（也称为最小的原子单元），则满足第一范式（1NF）

如在一个表中的一列的值为：中国北京，这就违反了第一范式，可以分为中国和北京

如图：

2.第二范式

如果一个关系满足1NF，并且除了主键以外的其他列，都依赖与该主键，则满足第二范式（2NF）

第二范式要求每个表只描述一件事情。每列都和主键相关

如有一个表其字段为：

订单编号、产品编号、订购日期、价格。这就违反了第二范式

这个表可以分为两个表其字段分别为：

第一个表字段为：订单编号、订购日期

第二个表字段为：产品编号、价格

3.第三范式

如果一个关系满足2NF，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式（3NF）

如在一个表中有3个字段分别为：A B C 本来A与C没有任何关系，但是他们可以通过B建立起来关系。

存在这样关系的字段是不能同时存在于一个表中的，也就是要直接关联的不要间接关联的。

● 规范化实例

假设某建筑公司要设计一个数据库。公司的业务规则概括说明如下：

公司承担多个工程项目，每一项工程有：工程号、工程名称、施工人员等

公司有多名职工，每一名职工有：职工号、姓名、性别、职务（工程师、技术员）等

公司按照工时和小时工资率支付工资，小时工资率由职工的职务决定（例如，技术员的小时工资率与工程师不同）

我们可以直接制作一张表所有的信息都包含在其中，但是如果这样表中包含大量的冗余，可能会导致数据异常

更新异常：

例如，修改职工号=1001的职务，则必须修改所有职工号=1001的行

添加异常：

若要增加一个新的职工时，首先必须给这名职工分配一个工程。或者为了添加一名新职工的数据，先给这名职工分配一个虚拟的工程。（因为主关键字不能为空）

删除异常：

例如，1001号职工要辞职，则必须删除所有职工号＝1001的数据行。这样的删除操作，很可能丢失了其它有用的数据

采用一个表的设计结构，虽然很容易产生工资报表，但是每当一名职工分配一个工程时，都要重复输入大量的数据。这种重复的输入操作，很可能导致数据的不一致性。

那么我们可以应用范式规范化设计

可以设计4张表来达到目的：

第1张表的字段包括：工程号、工程名称---->以工程号为主键

第2张表的字体包括：职工号、姓名、职务--->以职工号为主键

第3张表的字段包括：职务、小时工资率

第4张表的字段包括：工程号、职工号、工时---->以工程号和职工号同时为主键，这是一个联合主键

● 规范化和性能的关系

1.为满足某种商业目标，数据库性能比规范化数据库更重要

2.通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息所需的时间

3.通过在给定的表中插入计算列（如成绩总分），以方便查询

4.进行规范化的同时，还需要综合考虑数据库的性能。

● 总结

在需求分析阶段，设计数据库的一般步骤为：收集信息、标识对象、标识每个对象的属性、标识对象之间的关系。

在概要设计阶段和详细设计阶段，设计数据库的步骤为：

1.绘制E-R图

2.将E-R图转换为表格

3.应用三大范式规范化表格

为了设计结构良好的数据库，需要遵守一些专门的规则，称为数据库的设计范式。

第一范式（1NF）的目标：确保每列的原子性。

第二范式（2NF）的目标：确保表中的每列，都和主键相关。

第三范式（3NF）的目标：确保每列都和主键列直接相关，而不是间接相关。

在制作数据库时冗余是不可能是完全避免的，而且适当的冗余还可以提高性能。

第十六章数据库的备份与恢复简介

数据库的备份与恢复简介

备份是数据库中数据的副本，它可以保护数据在出现意外损失时最大限度的恢复

Oracle数据库的备份包括两种类型：物理备份和逻辑备份

物理备份是对数据库的操作系统物理文件（如数据文件、控制文件和日志文件等）的备份

逻辑备份是对数据库逻辑组件（如表、视图和存储过程等数据库对象）的备份

● 故障类型

导致数据库操作终止的故障包括四种类型：语句故障、用户进程故障、实例故障、介质故障

在执行 SQL 语句过程中发生的逻辑故障可导致语句故障。如果用户编写的 SQL 语句无效，就会发生逻辑故障

当用户程序出错而无法访问数据库时发生用户进程故障。导致用户进程故障的原因是异常断开连接或异常终止进程

当 Oracle 的数据库实例由于硬件或软件问题而无法继续运行时，就会发生实例故障

在数据库无法正确读取或写入某个数据库文件时，会发生介质故障

● 导出和导入实用程序

导出和导入实用程序用于实施数据库的逻辑备份和恢复

导出实用程序将数据库中的对象定义和数据备份到一个操作系统二进制文件中

导入实用程序读取二进制导出文件并将对象和数据载入数据库中

导出和导入实用程序的特点有：

可以按时间保存表结构和数据

允许导出指定的表，并重新导入到新的数据库中

可以把数据库迁移到另外一台异构服务器上

在两个不同版本的Oracle数据库之间传输数据

在联机状态下进行备份和恢复

可以重新组织表的存储结构，减少链接及磁盘碎片

使用以下三种方法调用导出和导入实用程序：

1.命令参数行、交互提示符、参数文件

命令参数行：在命令行指定执行程序的参数和参数值。

交互提示符：以交互的方式提示用户逐个输入参数的值。

参数文件：允许用户将运行参数和参数值存储在参数文件中，以便重复使用参数

● 导出和导入数据库对象的四种模式是：

1.完全数据库：导出和导入整个数据库中的所有对象

2.表：导出和导入一个或多个指定的表或表分区

3.用户：导出和导入一个用户模式中的所有对象

4.表空间：导出和导入一个或多个指定的表空间中的所有对象

导出实用程序有以下常用命令参数：

--------------------------------------------------------------------------------------------------------------

参数说明

--------------------------------------------------------------------------------------------------------------

USERID 确定执行导出实用程序的用户名和口令

BUFFER 确定导出数据时所使用的缓冲区大小，其大小用字节表示

FILE 指定导出的二进制文件名称，默认的扩展名是.dmp

FULL 指定是否以全部数据库方式导出，只有授权用户才可使用此参数

OWNER 要导出的数据库用户列表

HELP 指定是否显示帮助消息和参数说明

ROWS 确定是否要导出表中的数据

TABLES 按表方式导出时，指定需导出的表和分区的名称

PARFILE 指定传递给导出实用程序的参数文件名

TABLESPACES 按表空间方式导出时，指定要导出的表空间名

-----------------------------------------------------------------------------------------------------------------

格式:

按用户方式导出数据:exp scott/tiger@accp file=scott_back owner=scott

按表方式导出数据:exp scott/tiger@accp tables=(emp, dept) file=scott_back_tab

按表空间方式导出数据:exp scott/tiger@accp tables=(emp, dept) file=scott_back_tab

使用参数文件导出数据:exp system/aptech parfile='C:\parameters.txt'

● 导入实用程序有如下常用命令参数：

-----------------------------------------------------------------------

参数说明

-----------------------------------------------------------------------

USERID 指定执行导入的用户名和密码

BUFFER 指定用来读取数据的缓冲区大小，以字节为单位

COMMIT 指定是否在每个数组（其大小由BUFFER参数设置）插入后进行提交

FILE 指定要导入的二进制文件名

FROMUSER 指定要从导出转储文件中导入的用户模式

TOUSER 指定要将对象导入的用户名。FROMUSER与TOUSER可以不同

FULL 指定是否要导入整个导出转储文件

TABLES 指定要导入的表的列表

ROWS 指定是否要导入表中的行

PARFILE 指定传递给导入实用程序的参数文件名，此文件可以包含这里列出的所有参数

IGNORE 导入时是否忽略遇到的错误，默认为N

TABLESPACES 按表空间方式导入，列出要导入的表空间名

------------------------------------------------------------------------

格式：

将整个文件导入数据库：imp accp/accp@accp file=item_back.dmp ignore=y full=y

将scott用户的表导入到martin用户：

imp system/aptech@accp file=scott_back fromuser=scott touser=martin tables=(emp,dept)

使用参数文件导入数据：imp system/oracle parfile='C:\parameters.txt'

数据库在运行期间需要不断进行备份，万一假设每户崩溃了，可以从数据中恢复数据。Oralce在安装完成之后可以使用两个命令进行数据库的备份与恢复。

数据库的备份：exp

数据库的恢复：imp

如我们在D盘上新建一个data的文件夹保存所要备份的数据，如果要备份则需要使用命令行方式，进入到d:\data文件夹之中。进入到data文件夹之后，执行exp命令，会提示用户名和口令（scott/tiger）,进入到帐户以后，我们看一下在这个帐户下都有那些表（SELECT * FROM tab）,我们要把些所有的表都备份，都先默认状态，回车即可，一直等到所有的表都导出完成之后，备份就完成了。

检验备份的效果，我们把数据库中的表全部表删除，然后再进行恢复。

全部删除以后，使用imp命令将备份的数据全部恢复（过程中都先yes）。

第十七章导入导出工具

常用工具

EXPDP和IMPDP

orale10G引入了最新的数据泵（DATA dump)技术，它使得DBA或应用程序开发人员可以

将数据库的元数据（对象定义）和数据快速转移到另一个ORacle数据库中。

数据泵导出和导入的作用:

1.数据泵导出和导入可以实现逻辑备份和逻辑恢复。

2.数据泵导出和导入可以在数据库用户之间移动对象。

3.使用数据泵导入（impdp)可以在数据库之间移动对象。

4.使用数据泵导入可以实现表空间搬移

数据泵导入与传统的导出导入的区别：

在oracle10G之前，传统的导出和导入分别使用exp工具和IMP工具来完成。

10G保留了传统的EXP和IMP工具，而且还提供了数据泵EXPDP和IMPDP。

由于数据泵EXPDP和IMPDP 的速度要优于EXP和IMP工具，所以oracle建议使用EXPDP和IMPDP工具。

使用注意事项：

1.EXP和IMP是客户端工具程序，它们既可以在客户端使用，也可以在服务器端使用。

2.EXPDP和IMPDP是服务器端的工具程序，它们只能在oracle服务器端使用。而不能在oracle客户端使用。

3.两个工具的导出只能用各自对应的导入工具。

--1导出表

各参数说明

1.scott/tiger 指定由哪个一有户导出、

2.directory:指定导出文体的路径

3.dumpfile:指定导出的文件名

4.tables:指定导出的表的集合

导出操作，先由管理员创建目录并授于该目录的读写权限给某一个用户

如下：

sql>conn sys/system as sysdba

sql>create directory dump_dir as 'd:\dump';

sql>grant read,write on directory dump_dir to scott;

sql>grant read,write on directory dump_dir to sun;

sql>grant read,write on directory dump_dir to mytest;

进入DOS 命令行执行如下操作

1.expdp scott/tiger directory=dump_dir dumpfile=Tab.dmp

tables=dept,emp

2.导出方案

导出方案是指将一个或多个方案的所有对象结构及数据存储到转储文件中，导出方案是通过使用

schemas选项来完成的。

普通用户只能导出自身方案，如果要导出其他方案，由要求用户必须具有DBA角色或者EXP_FULL_DATABASE角色

，下面经导出system方案和scott方案的所有对象为例，

说明导出方案的方法

expdp system/system DIRECTORY=dump_DIR dumpFIle=schema.dmp

schemas=system,scott

3.导出表空间

导出表空间是指将一个或多个表空间的所有对象及数据存储到转储文件

中，导出表空间是通过使用tablespaces选项来完成的。需要注意导出

表空间要求用户必须具有DBA或EXP_FULL_DATABASE角色。示例如下

expdp system/system DIRECTORY=dump_dir dumpFIle=tablespace.dmp

tablespaces =users,system

4.导出数据库

导出数据库是指将数据库所有对象及数据存储到转储文件中，导出数据库是通过使用FULL选项来完成的。

要求用户具有DBA和Exp_FULL_DATABASE角色

需要注意：当导出数据库时，不会导出sys\ordsys\ordplugins\ctxsys\MDSYS\LBACSYS以及XDB等方案的对象。

expdp system/system DIRECTORY=DUMP_DIR DUMPFILE=FULL.DMP FULL=Y

参数文件的使用：

parm.txt

tables=dept,emp

Directory=dump

dumpfile=tab.dmp

expdb scott/tiger parfile=d:\dump\parm.txt;

使用IMPDP（35个参数)

数据泵导入包括：导入表、导入方案、导入表空间、导入数据库等四种模式。

需要注意的是：转储文件被存放在DIRECTORY对象所对应的OS目录中，而不能直接指定转储文件所在的OS目录。

1.导入表：

普通用户可以将导入其自身的方案。但如果以其他用户身份导入表，则要求该用户必须具有imp_full_Database或dba角色。

当导入表时，既可以将表导入到源方案中，也可以将表导入到其他方案中。如果要将表导入到其他方案中，则必须指定remap_schema选项。下面

以表dept,emp导入自身方案为例

impdp scott/tiger DIRECTORY=dump_dir dumpfile=TAB.DMP SCHEMAS=scott

导入到其他方案中

impdp system/system directory=dump_dir dumpFile=TAB.DMP

TABLES=scott.dept,scott.emp SCHEMAS=scott

REMAP_SCHEMA=scott:system

impdp system/system directory=dump_dir dumpFile=TABS.DMP

TABLES=scott.dept,scott.emp remap_schema=scott:sun

impdp sun/sun directory=dump_dir dumpfile=tab.dmp tables=emp remap_schema=scott:sun--成功

--使用系统帐户

impdp system/system directory=dump_dir dumpfile=tab.dmp leremap_schema=scott:sun--成功

2.导入方案：

impdp scott/tiger directory=dump_dir dumpfile=schema.dmp

schemas=scott

impdp system/system directory=dump_dir dumpfile=schema.dmp

schemas=scott REMAP_schema=scott:system

3.导入表空间

impdp system/system directory=dump_dir dumpfile=tablespace.dmp

TABLESPACES=USERS,SYSTEM

4.导入数据库

impdp system/system directory=dump_dir dumpFile=full.dmp full=y

使用EXP和IMP实现导出导入

调用：EXP

当在客户端使用该工具时，必须要带有连接字符串;当在服务器端使用时

可以不带连接字符串。导出包括：导出表、方案、数据库三种模式

1.导出表

当导出表时，默认情况下导出相应表的所有索引，触发器、约束。

下面以system用户和scott用户分别导出scott.dept和scott.emp为例

exp system/system tables=scott.dept,scott.emp file=tab1.dmp

exp scott/tiger tables=dept,emp file=tab2.dmp

2.导出方案：

exp system/system owner =scott file=schema1.dmp

exp scott/tiger file=schema2.dmp

3.导出数据库，使用FULL选项

exp system/system Full=y file=full.dmp

导入:imp

1.导入表

imp scott/tiger file=tab2.dmp tables=dept,emp

imp system/system file=tab2.dmp tables=dept,emp

fromuser=scott touser=system

2.导入方案:

imp scott/tiger file=schema2.dmp

imp system/system file=schema2.dmp fromuser=scott

touser=system

3.导入数据库

imp system/system file=full.dmp full=y

imp help=y--可以查看帮指令帮助信息

第十八章数据库归档方式

Oracle 数据库可以运行在两种归档方式：

非归档日志方式归档日志方式:

非归档日志方式可以避免实例故障，但无法避免介质故障。在此方式下，数据库只能实施冷备份

归档日志方式产生归档日志，用户可以使用归档日志完全恢复数据库

非归档日志方式下数据库的工作原理：

表空间脱机--->备份表空间---->恢复表空间---->表空间联机

归档日志方式下数据库的工作原理：

日志文件1填满 --->准备向日志文件2写入信息 --->备份日志文件2--->向日志文件2写入信息--->清空日志文件 2

配置数据库在归档日志方式下运行，包括以下三个步骤：

确保数据库当前不处于归档方式--->设置相关数据库初始化参数--->在归档日志方式下启动数据库

查看当前数据库的归档方式：

SQL> conn sys/system as sysdba

已连接。

SQL> archive log list

数据库日志模式非存档模式

自动存档禁用

存档终点 USE_DB_RECOVERY_FILE_DEST

最早的联机日志序列 37

当前日志序列 39

数据库的日志归档方式有两种：

1.自动归档：自动归档对非活动日志文件文件进行自动归档

2.手动归档：手动归档允许用户手动归档非活动日志文件文件的已填充组

获取归档日志信息

可以通过数据字典视图查看归档日志信息

V$ARCHIVE_DEST －显示当前所有归档日志存储位置及其状态

V$ARCHIVE_LOG －显示历史归档日志信息

SQL> SELECT DEST_ID,NAME,ARCHIVED

FROM V$ARCHIVED_LOG;

SQL> SELECT DEST_ID,DEST_NAME,STATUS,DESTINATION

FROM V$ARCHIVE_DEST WHERE STATUS='VALID';

总结：

数据库备份用于保护数据库中的数据，有两种类型的备份：物理备份和逻辑备份

导致数据库操作中止的故障类型有：语句故障、用户进程故障、实例故障和介质故障

Oracle 提供导出和导入实用程序实施数据库的逻辑备份

导出导入实用程序有四种工作模式：完全数据库、表、用户和表空间

数据库可在两种方式下运行：非归档日志方式和归档日志方式

JDBC部分

第一章 jdbc简介

JDBC(java database connectivity)是JAVA存取数据库系统的一个解决方案

以前使用同一个java程序连接数据库时，不同数据库厂商都有各自不同的API，也就是你写的程序语言要想与我们的数据库相连接，必须实现我们的这个API，所以各个数据库对要实现自己都有一个要求。因为主动权一直都是由数据库厂商把握，所以早期的java程序员要知道各个数据库的API程序。

随着JAVA的逐渐发展，开始掌握了主动权，开始自己来实现这个API。也就是你们数据库厂商想想和我的这个程序相连着，必须实现我们自己提供的这个API。

JDBC就是现在这个接口不用数据库厂商来做了，sun公司自己推出一个API接口，由数据库厂商来实现，那个数据库要和java相连，必须实现sun公司推出的这个接口。所以对于现在java程序员来说，只需要知道一个API接口就可以了。实现一个写一个java程序适应所有数据库的目的。

JDBC的本质就是一个与数据库操作无关的java操作接口。

JDBC数据库驱动程序依实现方式可以分为四个类型：

Type 1：JDBC-ODBC Bridge

Type 2：Native-API Bridge

Type 3：JDBC-middleware

Type 4：Pure Java Driver

前3种使用桥连接，第4种是纯的java连接。

怎么实现java与数据库的连接

java与Oracle相连时，需要的信息有:

1.Oracle数据库驱动

如：Oracle数据库的驱动是:(oracle.jdbc.driver.OracleDriver)

2.提供JDBC URL

JDBC URL定义了连接数据库时的协议、子协议、数据源识别。

协议：子协议：数据源识别

“协议”在JDBC中总是jdbc开始；“子协议”是桥接的驱动程序或是数据库管理系统名称，使用MySQL的话是“mysql”;“数据源识别”标出找出数据库来源的地址与连接端口。例如，MySQL的JDBC URL编写如下：

jdbc:mysql://主机名:端口/数据库名称?参数=值&参数=值

主机名称可以是本机 localhost或是其他连接主机，连接端口为3306,假如要连接

demo数据库，并指明用户名称与密码，如下：

jdbc:mysql://localhost:3306/demo?user=root&password=123

如果要使用中文存取的话，还必须给定参数useUnicode及characterEncoding，表明

是否使用Unicode，并指定字符编码方式，例如：

jdbc:mysql://localhost:3306/demo?user=root&password=123&useUnicode

=true&characterEncoding=UTF-8

数据源识别符是标出数据源来源的地址与连接连接端口

如：(jdbc:oracle:thin:@localhost:1521:orcl) 这是一个Oracle数据库的URL信息。

用户如:(scott)

密码如: (tiger)

连接步聚：

第1步：动态加载JDBC驱动程序(只需要执行一次)

首先必须通过java.lang.Class类的forName()动态加载驱动程序类，并向DriverManager注册JDBC驱动程序(驱动程序会自动通过DriverManager.registerDriver()方法注册)。

如：一个加载Oracle的驱动程序类的程序片段如下：

try{

Class.forName(“oracle.jdbc.driver.OracleDriver”);

}catch(ClassNotFoundException e){

System.out.println(“找不到驱动程序类”);

}

第2步：利用DriverManager类中的getConnection()方法获得URL、用户、密码等信息。

如：DriverManager.getConnection(url, user, pwd);

getConnection()一共有三种类型，我们常用的是带有三个参数的。他返回的是Connection类型

static Connection getConnection(String url)

试图建立到给定数据库 URL 的连接。

static Connection getConnection(String url, Properties info)

试图建立到给定数据库 URL 的连接。

static Connection getConnection(String url, String user, String password)

试图建立到给定数据库 URL 的连接。

所以我们可以定义一个Connection类型的变量来接受获得的信息，如：

Connecton conn=DriverManager.getConnection(url, user, pwd);

范例：

public class dataSourse {

//驱动程序名

public String driver="oracle.jdbc.driver.OracleDriver";

//URL信息

public String url="jdbc:oracle:thin:@localhost:1521:orcl";

//用户名

public String user="scott";

//密码

public String pwd="abcd";

public Connection conn=null;

//获取连接的方法注:Connection()是一个接口，所以这是一个接口类型的方法

public Connection getConnection() throws ClassNotFoundException, SQLException{

//动态加载驱动程序

Class.forName(driver);//加载驱动 Class类的forName()方法

conn=DriverManager.getConnection(url, user, pwd);

return conn;

}

public static void main(String[] args) throws ClassNotFoundException, SQLException {

//创建对象

dataSourse ds=new dataSourse();

Connection con=ds.getConnection();

//输出连接对象

System.out.println(con);

}

第二章连接数据库

我们可以把驱动、URL、帐户、密码等信息以一个字符串的形式定义到一个类中，可以我们平时不这么做，而是将这些信息放到一个属性文件中。属性文件是以properties为扩展名的，如我们可以新建一个oracl.properties文件，里边在放的信息内容为：

#jdbc的配置文件注：属性文件中是以#作为注释开始的

driver=oracle.jdbc.driver.OracleDriver

url=jdbc\:oracle\:thin\:@localhost\:1521\:orcl

pwd=abcd

user=scott

然后我们可以在类中加载这个文件，加载properties文件有两个方法，一是利用构造器，二是利用静态区块，利用构造器是因为利用构造器在一创建对象时他就可以扫行了。而利用静态区块也是在创建对象时执行，而且只执行一次，我们平时常用构造进行器加载。

范例：定义一个接口

//用户获得数据库连接和关闭数据库的API接口

public interface IdataSource {

//获得数据库连接

public Connection getConnection() throws SQLException;

//关闭数据库连接

public void closeConnection(Connection conn)throws SQLException;

}

写一个工具类：

public class dataSource implements IdataSource {

//获取连接所需要的信息

public String driver="";

public String url="";

public String user="";

public String pwd="";

//在构造器初始化时。加载属jdbc.propertied文件，获取相关信息

public dataSource(){

//创建properties属性文件对象

Properties pro=new Properties();

try {

//加载属性文件

pro.load(new FileInputStream("D:\\jdbc.properties"));//文件所在路径

//通过Properties对象的getProperty()方法获得相应的值

driver=pro.getProperty("driver");

url=pro.getProperty("url");

user=pro.getProperty("user");

pwd=pro.getProperty("pwd");

try {

//动态加载驱动

Class.forName(driver);

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

} catch (FileNotFoundException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

}

//获取数据库连接

public Connection getConnection() throws SQLException{

return DriverManager.getConnection(url, user, pwd);;

}

//关闭数据库连接

public void closeConnection(Connection conn)throws SQLException{

if(conn!=null&&!conn.isClosed()){

conn.close();

}

获得Connection对象后，可以使用isClosed()方法测试与数据库的连接是否关闭，在操作完数据库之后，如果确定不在需要连接，则必须用close()方法来关闭与数据库的连接，以释放连接时相关的必要资源。

第三章常用数据库的驱动程序和JDBC URL

● Oracle 数据库 :

驱动程序包名： ojdbc14.jar

驱动类的名字： oracle.jdbc.driver.OracleDriver

JDBC URL ： jdbc:oracle:thin:@ dbip:port:databasename

说明：驱动程序包名有可能会变

JDBC URL 中黑色字体部分必须原封不动的保留，为该驱动识别的 URL 格式。红色字体部分需要根据数据库的安装情况填写。其中各个部分含义如下：

dbip – 为数据库服务器的 IP 地址，如果是本地可写： localhost 或 127.0.0.1 。

port – 为数据库的监听端口，需要看安装时的配置，缺省为 1521 。

databasename – 为数据库的 SID ，通常为全局数据库的名字。

举例如果要访问本地的数据库 allandb ，端口 1521 ，那么 URL 写法如下：

jdbc:oracle:thin:@localhost:1521:allandb

● SQL Server 数据库

驱动程序包名： msbase.jar mssqlserver.jar msutil.jar

驱动类的名字： com.microsoft.jdbc.sqlserver.SQLServerDriver

JDBC URL ：

jdbc:microsoft:sqlserver:// dbip:port;DatabaseName=databasename

说明：驱动程序包名有可能会变

JDBC URL 中黑色字体部分必须原封不动的保留，为该驱动识别的 URL 格

式。红色字体部需要根据数据库的安装情况填写。其中各个部分含义如下：

dbip – 为数据库服务器的 IP 地址，如果是本地可写： localhost 或 127.0.0.1 。

port – 为数据库的监听端口，需要看安装时的配置，缺省为 1433 。

databasename – 数据库的名字。

举例如果要访问本地的数据库 allandb ，端口 1433 ，那么 URL 写法如下：

jdbc: microsoft: sqlserver:@localhost:1433; DatabaseName =allandb

● MySQL 数据库

驱动程序包名： mysql-connector-java-5.1.7-bin.jar

驱动类的名字： com.mysql.jdbc.Driver

JDBC URL ： jdbc:mysql:// dbip:port/databasename

说明：驱动程序包名有可能会变

JDBC URL 中黑色字体部分必须原封不动的保留，为该驱动识别的 URL 格式。红色字体部需要根据数据库的安装情况填写。其中各个部分含义如下：

dbip – 为数据库服务器的 IP 地址，如果是本地可写： localhost 或 127.0.0.1 。

port – 为数据库的监听端口，需要看安装时的配置，缺省为 3306 。

databasename – 数据库的名字。

举例如果要访问本地的数据库 allandb ，端口 1433 ，那么 URL 写法如下：

jdbc:mysql://localhost:3306/allandb

第四章连接池

数据库连接的取得是一个非常消耗时间和资源的动作。取得一个数据库的连接包括：

建立Socket connection、交换数据（用户密码验证和相关的参数）、数据库初始会话（Session）、日志（logging）、分配行程(Proess)......

如进行数据库连接动作很频繁，则要考虑到重复使用连接的需求，以节省在获得连接时的时间和资源，通常会实现一个连接池。如果有了连接池，那么我们有需要时可以从池中获得，不需要时就将连接放回到池中，而不是直接关闭连接。使用ArrayList来实现连接池。

范例：实现一个简单的数据库连接池

接口：

public interface IdataSource {

//获得连接

public Connection getConnection() throws SQLException;

//关闭连接需要关闭其对象

public void closeConnection(Connection connection, Statement stmt, ResultSet rs)throws SQLException;

}

为什么这里有三个参数？

当我们使用单一连接时，关闭连接的时候自动会把所有使用的对象全部关掉，现在使用连接池，有时关有时是存着不关的，可是如果不关，他里面的对象还存在，一直占着资源，所以我们要手动将他连带的对象关掉，如执行语句的对象和结果集，所以这里有三个参数

这里要注意一个问题，关闭他们时是有一个顺序的，关闭的先后顺序是：Result-->Statement-->Connection

现在创建一个连接池：

public class ConnectioPool implements IdataSource {

public String driver="";//驱动

public String url="";//jdbc URL

public String user="";//用户

public String pwd="";//密码

private int max;//连接池中允许存放的最大连接数

//用ArrayList实现连接池

List<Connection> pool=new ArrayList<Connection>();

//在构造器初始化时，加载到jdbc.propertied 属性文件，获取相关信息

public ConnectioPool (){

//创建Properties属性文件对象

Properties pro=new Properties();

//加属性文件

try {

pro.load(new FileInputStream("D:\\jdbc.properties"));

//通过Properties对象的getProperty()方法获得相应的值

driver=pro.getProperty("driver");

url=pro.getProperty("url");

user=pro.getProperty("user");

pwd=pro.getProperty("pwd");

max=Integer.parseInt(pro.getProperty("poolsize"));

//加载驱动程序类

Class.forName(driver);

}catch (ClassNotFoundException e) {

e.printStackTrace();

}

catch (FileNotFoundException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

}

//线程的安全关闭Connection

public synchronized void closeConnection(Connection connection, Statement stmt, ResultSet rs) {

// 注意：关闭和先后顺序 rs stmt connection

if(rs!=null){

try {

rs.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

if(stmt!=null){

try {

stmt.close();

} catch (SQLException e) {

e.printStackTrace();

}

//如果池中的Connection达到了所允许的最大数，

if(pool.size()==max){

//真正关闭Connection

try {

connection.close();

} catch (SQLException e) {

e.printStackTrace();

}

}else{

//连接池中的Connection没有达到所允许的最大值，将Connection放入池中

pool.add(connection);

}

//线程安全获得Connecton

public synchronized Connection getConnection() throws SQLException{

//如果池没有Connection

if(pool.size()==0){

return DriverManager.getConnection(url,user,pwd);

}else{

//连接池中有空闲的的Connection，则从池中取出Connection

return pool.remove(pool.size()-1);

}

第三方连接池

dbcp:

DBCP是apache下面的一个开源的数据库连接池 ,tomcat自带

c3p0(推荐):

C3P0是Hibernate3.0默认的自带数据库连接池

proxool(推荐) :

proxool连接池是sourceforge下的一个开源项目,这个项目提供一个健壮、易用的连接池，最为关键的是这个连接池提供监控的功能，方便易用，便于发现连接泄漏的情况。

第五章数据操作-创建表

数据操作之创建表

Connection对象是Java连接的代表对象，接下来要执行SQL语句的话，必须获java.sql.Statement对象，他是JAVA中的一个SQL 叙述的具体代表对象，可以使用Connection的createStatement()来建立Statement对象：

如:

Connection conn=new Connection();

Statement stmt=conn.createStatement();

获得他的对象后，可以使用executeUpdate()、executeQuery()等方法来执行SQL语句，executeUpdate()主要是用来执行CREATE table ,INSERT,DROP table,ALTER table等会改变数据库内容的SQL。如我们在数据库中建立一个表：

建表范例：

public class CreateTableDemo {

public static void main(String[] args) throws SQLException {

//声明数据库连接池对象

IdataSource ds = new dataSource();

//获得数据库连接

Connection con = ds.getconnection();//连接，加载URL USER PWD等信息

//获得Statement对象

Statement stmt = con.createStatement();

//创建SQL语句创建表

String sql="create table A(id int,name varchar2(10))";

stmt.executeUpdate(sql);//注：executeUpdate()返回的一个整型

//所以我们可以根据他的值是否大于0，来测试这个语句是否执行了

//关闭Statement对象

if(stmt!=null){

stmt.close();

}

//关闭连接

ds.closeconn(con);

}

如果要在表中插入数据，更新数据，删除数据都可以使用Statement的executeUpdate()方法

stmt.executeUpdate("inser into A(1001,'Jack')");

而Statement的executeQuery()方法主要是用来查询的。

第六章数据操作-查询

数据库操作之查询

Statement的executeQuery()方法主要是用来SELECT等查询的。executeUpdate()方法返回的是int 类型的，表示记录变动的数据。

而executeQurey()方法返回的是一个java.sql.ResultSet对象，代表查询的结果，查询的结果会一条一条的记录，可以使用ResultSet的next()方法来移至下一条记录，它会返回true或false表示是否有下一条记录，接着可以使用getXXX()方法来获得数据。例如：

getString() getInt() getFloat()等方法分别获得相应字段类型的记录，getXXX()方法提供了按字段名称获得记录，也可以按字段的顺序例如：

result.getInt("id");//建议使用这种方式

result.getInt(1);

public class SlectTableValueDemo {

public static void main(String[] args) throws SQLException {

//创建数据库连接池

IdataSource ds = new dataSource();

//获得数据库连接

Connection con = ds.getconnection();//连接，加载URL USER PWD等信息

try {

//获得Statement对象

Statement stmt = con.createStatement();

//声明用于查询的SQL语句

String sql="select ename,empno,deptno from emp where deptno=20";

//执行查询等到一个结果集

ResultSet rs = stmt.executeQuery(sql);

//循环读取结果集中的数据

while(rs.next()){

System.out.print(rs.getString("ename")+"\t");

System.out.print(rs.getInt("empno")+"\t");

System.out.println(rs.getInt("deptno"));

}

} catch (SQLException e) {

e.printStackTrace();

}finally{

//关闭Result对象

//ds.closeconn(rs);

//关闭Statement对象

//ds.closeconn(stmt);

//关闭连接对象

ds.closeconn(con);

}

Connection()方法：

Statement createStatement() :创建一个 Statement 对象来将 SQL 语句发送到数据库。返回的是一个Statement类型。

Statement接口中有一方法：

ResultSet executeQuery(String sql)

执行给定的 SQL 语句，该语句返回单个 ResultSet 对象。返回ResultSet类型

ResultSet 是一个接口他中有一个方法：

boolean next()

将指针从当前位置下移一行。

第七章预处理

Statement的execute()方法可以用来执行SQL语句，并测试所执行的SQL是执行查询或是更新，返回TURE的话表示执行SQL将返回ResultSet的查询结果，此时可以使用Statement对象的getResultSet()获得ResultSet对象。如果execute()方法返回false，表示执行SQL会返回更新数目或没有结果，此时可以使用Statement对象的getUpdateCount()方法获取更新数目。

如果事先无法得知进行查询或是更新，就可以使用execute()方法。

Statement主要用于静态的SQL语句，也就是在执行executeUpdate() executeQuery() execute()等方法时，指定内容固定不变的SQL语句字符串，第一句SQL语句只能用于当时的执行。如果有些操作只是SQL语句某些参数会有些不同，其余的SQL子句皆相同，则可以使用java.sql.prepareStatement.

可以使用Connection的prepareStatement()方法建好一个预先编译好的SQL语句，其中参数会变动的部分，先制定“？”做为占位符，如在jdbc表中插入数据

PrepareStatement stmt=conn.prepareStatement("insert into jdbc(id,name)(?,?)");

等到真正在需要参数执行时，再使用相对应的

setInt() setString() setXXX()方法，制定?处真正应该有的参数，如：

stmt.setInt(1001);

stmt.setString("lixiaolong");

范例：动态的给jdbc表中插入值

public class prepareStatementDemo {

public static void main(String[] args) throws SQLException{

//创建数据库连接池

IdataSource ds=new dataSource();

//获得数据库连接

Connection conn=ds.getconnection();

// 使用占位符声明SQL语句

String sql="insert into jdbc values(?,?)";

Scanner scan=new Scanner(System.in);

conn.setAutoCommit(false);//ture为自动提交 false为手动提交不写默认为自动提交

//获得PrepareStatement对象

PreparedStatement pstt = conn.prepareStatement(sql);//预处理

System.out.println("请输入ID");

//给参数赋值 ID 和姓名

int id = scan.nextInt();

pstt.setInt(1, id);//有两个参数第一个表示索引值，第二个是列名

System.out.println("请输入姓名");

String name=scan.next();

pstt.setString(2, name);

pstt.executeUpdate();

conn.commit();//提交事物注：事物有四个特性

System.out.println("赋值成功");

}

第八章批处理

Statement中的execute()方法一次只能执行一个SQL语句，如果有多个SQL语句要的执行的话，可以使用executeBatch()方法，在一次方法调用中执行多个SQL语句，以增加执行的效率，可以使用addBatch()方法将要执行的SQL语句加入，然后执行executeBatch()方法即可。

一般步骤：

conn.setAutoCommit(false);

Statement stmt=conn.createStatement();

stmt.addBatch("......");//SQL语句

stmt.executeBatch();

stmt.commit():

范例：

public class BatchDemo {

public static void main(String[] args) throws SQLException{

IdataSource ds=new dataSource();

Connection conn=ds.getconnection();

String sql="insert into jdbc values(?,?)";

conn.setAutoCommit(false);//ture为自动提交 false为手动提交不写默认为自动提交

PreparedStatement pstt = conn.prepareStatement(sql);//预处理

//插入一万条记录作批量处理

for(int i=1;i<5;i++){

pstt.setInt(1, i);

pstt.setString(2, "JACK"+i);

pstt.addBatch();

}

pstt.executeBatch();//批执行语句

conn.commit();//提交事物

System.out.println("赋值成功");

}

第九章数据的数据

Meta Data 即“数据的数据”（Data about data）,ResultSet用来表示查询到的数据，而ResultSet数据的数据，即描述所查到的数据的背后的数据描述，用来表示表的名称，字段名称，字段类型等，这些信息可以通过ResultSetMetaData来获得。

范例：

public class Demo{

public static void main(String[] args) throws SQLException{

//声明数据库连接池对象

IdataSource stSource=new dataSource();

//获得数据库连接

Connection conn = stSource.getconnection();

//获得Statement对象

Statement stmt = conn.createStatement();

//声明SQL语句

String sql="select * from emp";

//声明结果集对象

ResultSet rs=null;

try {

//执行查询

rs=stmt.executeQuery(sql);

//获得ResultSet的ResultSetMetaData(数据对象)

ResultSetMetaData metaData=rs.getMetaData();

//获取表的名称

String tableName=metaData.getTableName(1);

//获取数据中的字段数

int columnCount=metaData.getColumnCount();

//循环打印出每个字段的名称及类型

for(int i=1;i<columnCount;i++){//注：是从1开始的

//取字段名称

String columnaName=metaData.getColumnName(i);

//取字段的类型

String columnType=metaData.getColumnTypeName(i);

//打印

System.out.printf("%s\t%s\t%s\t",tableName,columnaName,columnType);

}

System.out.println();

//打印出数据

while(rs.next()){

System.out.printf("%d%S%d",rs.getInt("empno"),rs.getString("ename"),rs.getInt("sal"));

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

//关闭ResultSet对象

//关闭Statement对象

//关闭Connection对象

stSource.closeconn(conn);

}

第十章调用函数

在JDBC中调用函数需要涉及到一个新的接口 CallableStatement ，他是继承 PreparedStatement 的一个接口，这个接口主要是做储存过程和函数调用的。调用储存过程一般要用到事物。开始先设为setAutoCommit(false);然后再进行手动的提交（commit( )）。

在java要调用一个函数，大致的步聚可分为以下几步：

1. 创建连接池对象

2. 获得连接

3. 定义一个String类型的SQL语句，要注意的是函数的SQL语句写法比较特殊，调用函数的语法格式是 {？= call 函数名（？，？）}。

4. 用Connecton接口中prepareCall( )的调用函数，他返回的是一个CallableStatement类型的值，CallableStatement他也是一个接口，是一个用于执行 SQL 存储过程的接口。JDBC API 提供了一个存储过程 SQL 转义语法，该语法允许对所有 RDBMS 使用标准方式调用存储过程。此转义语法有一个包含结果参数的形式和一个不包含结果参数的形式。如果使用结果参数，则必须将其注册为 OUT 型参数。其他参数可用于输入、输出或同时用于二者。参数是根据编号按顺序引用的，第一个参数的编号是 1。

5. 分别对函数中各个参数的值进行赋值，如果第一个是要输出的值，需要CallableStatement中的一个方法：registerOutParameter(int parameterIndex, int sqlType) 按顺序位置 parameterIndex 将 OUT 参数注册为 JDBC 类型 sqlType。这里第二个参数比较特殊，我们这里用 java 下特有的如：java.sql.Types.BIGINT 。

6. 执行SQL语句

7. 进行手动的提交 commit()。

范例：在JDBC中调用这两个函数

public class InvokeFunction {

//输入参数的函数

private static void InFunction(){

//创建连接池对象

PoolDataSource ds=new PoolDataSource();

//获得连接

Connection conn=ds.getconnection();

//调用函数的语法格式为：{?=call 函数名（？，？）}

String sql="{?=call fun1(?,?)}";//注意写调用函数时的语句格式

CallableStatement cs=null;//CallableStatement是个接口

try {

conn.setAutoCommit(false);//进行手动的事物处理

cs=conn.prepareCall(sql);//prepareCall(String sql)方法

//创建一个 CallableStatement 对象来调用数据库存储过程。

cs.registerOutParameter(1, java.sql.Types.BIGINT);

/*registerOutParameter(int parameterIndex, int sqlType)

按顺序位置 parameterIndex 将 OUT 参数注册为 JDBC 类型 sqlType。*/

cs.setInt(2, 30);//给第2个参数赋值

cs.setInt(3, 70);//给第3个参数赋值

cs.execute();

System.out.println("结果为:"+cs.getInt(1));//打印的是第3个参数

conn.commit();//提交事物

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

ds.closeconn(conn, cs, null);

}

//输出参数的函数

private static void OutFunction(){

PoolDataSource ds=new PoolDataSource();

Connection conn=ds.getconnection();

String sql="{?=call fun2(?,?)}";

CallableStatement cs=null;

try {

conn.setAutoCommit(false);

cs=conn.prepareCall(sql);

cs.registerOutParameter(1, Types.INTEGER);

cs.setInt(2,30);

cs.registerOutParameter(3, Types.INTEGER);

cs.execute();

//获取结果

int avg=cs.getInt(1);

int count=cs.getInt(3);

System.out.println("平均工资为:"+avg+"该部门的人数为:"+count);

conn.commit();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

ds.closeconn(conn, cs, null);

}

public static void main(String[] args) {

// 1.调用输入参数的函数

InFunction();

//2.调用输出参数的函数

OutFunction();

}

程序执行过程中所到的访方法

prepareCall(String sql)

创建一个 CallableStatement 对象来调用数据库存储过程。返回的CallableStatement类型。

registerOutParameter(String parameterName, int sqlType)

将名为 parameterName 的 OUT 参数注册为 JDBC 类型 sqlType。空的返回类型

函数的具体内容

首先写两个函数fun1()和fun2()

//第一个函数求两个数的和

create or replace function fun1(n1 in int,n2 in int ) return integer is

Result integer;

begin

Result :=n1+n2;

return(Result);

end;

//调用第一个函数

declare

i integer;

begin

i:=fun1(10,30);

dbms_output.put_line('结果为'||i);

end;

//第二函数返回一个部门的平均工资

create or replace function fun2(v_dept in int,v_count out int)

return number

Result number;

begin

select avg(sal),count(*) into Result,v_count from emp where deptno=v_dept;

return(Result);

end ;

//调用第二个函数

declare

i integer;

v_count int;

begin

i:=fun2(20,v_count=>v_count);

dbms_output.put_line('平均工资'||i);

dbms_output.put_line('人数为：'||v_count);

end;

第十一章调用过程

在java中调用过程与调用函数相似大致可以分为以下几步：

1、创建连接池对象

2、获得连接

3、定义一个过程的SQL语句，语法为：{call 过程名（？，？）}

4、然后执行 prepareCall()

范例：调用储存过程

public class InvokeProcedure {

//第1个储存过程给jdbc表中插值

public static void InProduce(){

PoolDataSource ds=new PoolDataSource(); //创建连接池对象

Connection conn=ds.getconnection(); //获得连接

String sql="{call In_proc(?,?)}"; //定义一个关于过程的SQL语句

CallableStatement cs=null; //定义一个CallableStatement类型的对象

try {

conn.setAutoCommit(false); //设置为手动提交事物

cs=conn.prepareCall(sql); //调用SQL语句执行过程

cs.setInt(1, 1002); //给第1个参数赋值

cs.setString(2, "Tom"); //给第2个参数赋值

cs.execute(); //执行SQL语句

conn.commit(); //提交事物

} catch (SQLException e) {

e.printStackTrace();

}finally{

ds.closeconn(conn, cs, null); //关闭连接

}

//第二个过程，给定一个部门号，求出这个部门的工资总

public static void OutProduce(){

PoolDataSource ds=new PoolDataSource();

Connection conn=ds.getconnection();

String sql="{call out_proc(?,?)}";

CallableStatement cs=null;

try {

conn.setAutoCommit(false);

cs=conn.prepareCall(sql);

cs.setInt(1, 20);

cs.registerOutParameter(2, Types.INTEGER);

cs.execute();

conn.commit();

System.out.println("部门的工资总数为:"+cs.getInt(2));

} catch (SQLException e) {

e.printStackTrace();

}finally{

ds.closeconn(conn, cs, null);

}

//第三个过程：一个带有游标的过程，打印出某个部门的全体员工的信息

public static void CursorProduce(){

PoolDataSource ds=new PoolDataSource();

Connection conn=ds.getconnection();

String sql="{call cur_proc(?,?)}";

CallableStatement cs=null;

try {

conn.setAutoCommit(false);

cs=conn.prepareCall(sql);

cs.setInt(1, 10);

cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);

cs.execute();

conn.commit();

//获取结果

ResultSet rs = (ResultSet)cs.getObject(2);

while(rs.next()){

int number = rs.getInt("empno");

String ename = rs.getString("ename");

int sal=rs.getInt("sal");

System.out.printf("员工号为%d姓名为%s工资为%d\n",number,ename,sal);

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

ds.closeconn(conn, cs, null);

}

//主函数

public static void main(String[] args) {

InProduce();

OutProduce();

CursorProduce()

}

三个过程：

//创建一个存储过程给jdbc表插入数据

create or replace procedure In_proc(id in int,name in varchar2) is

begin

insert into jdbc values(id , name);

end;

//过程二求某个部门的工资总数

create or replace procedure out_proc(v_dept in int,v_sum out int)

begin

select sum(sal) into v_sum from emp where deptno=v_dept;

end;

//带有游标的过程打印某个部门的全体员工的信息

create or replace procedure cur_proc(v_deptno in int,emp_cur out sys_refcursor)

begin

open emp_cur for select * from emp where deptno=v_deptno;

end;

第十二章 DAO封装

数据操作对象(data access object)

一、描述EMP表中信息的类

public class Emp {

private int empno;

private String ename;

private int sal;

private int dept;

public int getEmpno() {

return empno;

}

public void setEmpno(int empno) {

this.empno = empno;

}

public String getEname() {

return ename;

}

public void setEname(String ename) {

this.ename = ename;

}

public int getSal() {

return sal;

}

public void setSal(int sal) {

this.sal = sal;

}

public int getDept() {

return dept;

}

public void setDept(int dept) {

this.dept = dept;

}

二、进行操作的类

public class EmpDao {

//增

public int add(Emp emp){

ConectionPool ds= new ConectionPool();

Connection conn=ds.getConnection();

PreparedStatement ppt=null;

String sqlAdd="insert into emp(empno,ename,sal,deptno) values(?,?,?,?)";

try {

ppt= conn.prepareStatement(sqlAdd);

ppt.setInt(1, emp.getEmpno());

ppt.setString(2, emp.getEname());

ppt.setInt(3, emp.getSal());

ppt.setInt(4, emp.getDept());

return ppt.executeUpdate();//执行

} catch (SQLException e) {

e.printStackTrace();

return 0;

}finally{

ds.closeConnection(conn, ppt, null);

}

//删

public int delete(int Empno){

ConectionPool ds= new ConectionPool();

Connection conn=ds.getConnection();

String sqlDelete="delete from emp where empno=?";

PreparedStatement ppt=null;

try {

ppt= conn.prepareStatement(sqlDelete);

ppt.setInt(1, Empno);

return ppt.executeUpdate();//执行

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

return 0;

}finally{

ds.closeConnection(conn, ppt, null);

}

//改

public int update(Emp emp) throws SQLException{

ConectionPool ds= new ConectionPool();

Connection conn=ds.getConnection();

String sqlUpdate="update emp set sal=? where empno=?";

try {

PreparedStatement ppt= conn.prepareStatement(sqlUpdate);

ppt.setInt(1, emp.getEmpno());

ppt.setInt(2, emp.getSal());

return ppt.executeUpdate();//执行

} catch (SQLException e) {

e.printStackTrace();

return 0;

}

//单行查

public Emp getSingleRecord(int empno){

Emp emp=new Emp();

ConectionPool ds= new ConectionPool();

Connection conn=ds.getConnection();

String sqlSingleRecord="select * from emp where empno=?";

PreparedStatement ppt=null;

ResultSet rs=null;

try {

ppt=conn.prepareStatement(sqlSingleRecord);

ppt.setInt(1, empno);

rs= ppt.executeQuery();

while(rs.next()){

int empnumber=rs.getInt("empno");

String ename=rs.getString("ename");

int sal = rs.getInt("sal");

int deptno=rs.getInt("deptno");

emp.setEmpno(empnumber);

emp.setEname(ename);

emp.setSal(sal);

emp.setDept(deptno);

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

ds.closeConnection(conn, ppt, rs);

}

return emp;

}

//多行查询查所有的员工信息

public List<Emp> getAllEmp(){

List<Emp> empList=new ArrayList<Emp>();//定义一个Emp类型的容器

ConectionPool ds= new ConectionPool();

Connection conn=ds.getConnection();

String sqlAllEmp="select * from emp";

PreparedStatement ppt=null;

ResultSet rs=null;

try {

ppt=conn.prepareStatement(sqlAllEmp);

rs=ppt.executeQuery();

while(rs.next()){

Emp emp=new Emp();

int empnumber=rs.getInt("empno");

String ename=rs.getString("ename");

int sal = rs.getInt("sal");

int deptno=rs.getInt("deptno");

emp.setEmpno(empnumber);

emp.setEname(ename);

emp.setSal(sal);

emp.setDept(deptno);

//放入集合

empList.add(emp);

}

} catch (SQLException e) {

e.printStackTrace();

return empList;

}finally{

ds.closeConnection(conn, ppt, rs);

}

return empList;

}

三、演示类

增：

public class AddDemo {

public static void main(String[] args) {

Emp emp=new Emp();

emp.setEmpno(1001);

emp.setEname("lixiao");

emp.setSal(5000);

emp.setDept(10);

EmpDao dao=new EmpDao();

dao.add(emp);

System.out.println("添加员工成功");

}

删除：

public class DeleteDemo {

public static void main(String[] args) {

Emp emp=new Emp();

EmpDao dao=new EmpDao();

dao.delete(1001);

System.out.println("删除成功");

}

改：

public class UpdateDemo {

public static void main(String[] args) {

Emp emp=new Emp();

emp.setEmpno(7499);

emp.setSal(4000);

EmpDao dao = new EmpDao();

try {

dao.update(emp);

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

System.out.println("修改成功");

}

单行查：

public class SingleRecordDemo {

public static void main(String[] args) {

EmpDao dao=new EmpDao();

Emp emp=new Emp();

emp=dao.getSingleRecord(7499);

System.out.println("查询成功");

System.out.println("员工号为："+emp.getEmpno());

System.out.println("姓名为："+emp.getEname());

System.out.println("工资为："+emp.getSal());

System.out.println("所在部门为:"+emp.getDept()+"部门");

}

多行查：

public class getAllEmpDemo {

public static void main(String[] args) {

EmpDao dao=new EmpDao();

List<Emp> lists=dao.getAllEmp();

//读取集合

System.out.println("员工号"+" "+"员工名"+" "+"工资"+" "+"部门号");

for(int i=0 ;i<lists.size();i++){

Emp emp=lists.get(i);

System.out.println(emp.getEmpno()+" "+emp.getEname()+" "+emp.getSal()+" "+emp.getDept());

}

System.out.println("表中一共有"+lists.size()+"条记录");

}

附录

实体权限

-----------------------------------------------------

alter any cluster 修改任意簇的权限

alter any index 修改任意索引的权限

alter any role 修改任意角色的权限 alter any cluster

alter any sequence 修改任意序列的权限

alter any snapshot 修改任意快照的权限

alter any table 修改任意表的权限

alter any trigger 修改任意触发器的权限

alter cluster 修改拥有簇的权限

alter database 修改数据库的权限

analyze 使用analyze命令分析数据库中任意的表、索引和簇

audit any 为任意的数据库对象设置审计选项

audit system 允许系统操作审计

backup any table 备份任意表的权限

become user 切换用户状态的权限

commit any table 提交表的权限

create any cluster 为任意用户创建簇的权限

create any index 为任意用户创建索引的权限

create any procedure 为任意用户创建存储过程的权限

create any sequence 为任意用户创建序列的权限

create any snapshot 为任意用户创建快照的权限

create any synonym 为任意用户创建同义名的权限

create any table 为任意用户创建表的权限

create any trigger 为任意用户创建触发器的权限

create any view 为任意用户创建视图的权限

create cluster 为用户创建簇的权限

create database link 为用户创建的权限

create procedure 为用户创建存储过程的权限

create profile 创建资源限制简表的权限

create public database link 创建公共数据库链路的权限

create public synonym 创建公共同义名的权限

create role 创建角色的权限

create rollback segment 创建回滚段的权限

create session 创建会话的权限

create sequence 为用户创建序列的权限

create snapshot 为用户创建快照的权限

create synonym 为用户创建同义名的权限

create table 为用户创建表的权限

create tablespace 创建表空间的权限

create user 创建用户的权限

create view 为用户创建视图的权限

delete any table 删除任意表行的权限

delete any view 删除任意视图行的权限

delete snapshot 删除快照中行的权限

delete table 为用户删除表行的权限

delete view 为用户删除视图行的权限

drop any cluster 删除任意簇的权限

drop any index 删除任意索引的权限

drop any procedure 删除任意存储过程的权限

drop any role 删除任意角色的权限

drop any sequence 删除任意序列的权限

drop any snapshot 删除任意快照的权限

drop any synonym 删除任意同义名的权限

drop any table 删除任意表的权限

drop any trigger 删除任意触发器的权限

drop any view 删除任意视图的权限

drop profile 删除资源限制简表的权限

drop public cluster 删除公共簇的权限

drop public database link 删除公共数据链路的权限

drop public synonym 删除公共同义名的权限

drop rollback segment 删除回滚段的权限

drop tablespace 删除表空间的权限

drop user 删除用户的权限

execute any procedure 执行任意存储过程的权限

execute function 执行存储函数的权限

execute package 执行存储包的权限

execute procedure 执行用户存储过程的权限

force any transaction 管理未提交的任意事务的输出权限

force transaction 管理未提交的用户事务的输出权限

grant any privilege 授予任意系统特权的权限

grant any role 授予任意角色的权限

index table 给表加索引的权限

insert any table 向任意表中插入行的权限

insert snapshot 向快照中插入行的权限

insert table 向用户表中插入行的权限

insert view 向用户视图中插行的权限

lock any table 给任意表加锁的权限

manager tablespace 管理（备份可用性）表空间的权限

references table 参考表的权限

restricted session 创建有限制的数据库会话的权限

select any sequence 使用任意序列的权限

select any table 使用任意表的权限

select snapshot 使用快照的权限

select sequence 使用用户序列的权限

select table 使用用户表的权限

select view 使用视图的权限

unlimited tablespace 对表空间大小不加限制的权限

update any table 修改任意表中行的权限

update snapshot 修改快照中行的权限

update table 修改用户表中的行的权限

update view 修改视图中行的权限

在Oracle常见的异常：

--------------------------------------------------------------------------------------------------------

命名的系统异常产生原因

--------------------------------------------------------------------------------------------------------

ACCESS_INTO_NULL 未定义对象

CASE_NOT_FOUND CASE 中若未包含相应的 WHEN ，并且没有设置 ELSE 时

COLLECTION_IS_NULL 集合元素未初始化

CURSER_ALREADY_OPEN 游标已经打开

DUP_VAL_ON_INDEX 唯一索引对应的列上有重复的值

INVALID_CURSOR 在不合法的游标上进行操作

INVALID_NUMBER 内嵌的 SQL 语句不能将字符转换为数字

NO_DATA_FOUND 使用 select into 未返回行，或应用索引表未初始化的元素时

TOO_MANY_ROWS 执行 select into 时，结果集超过一行

ZERO_DIVIDE 除数为 0

SUBSCRIPT_BEYOND_COUNT 元素下标超过嵌套表或 VARRAY 的最大值

SUBSCRIPT_OUTSIDE_LIMIT 使用嵌套表或 VARRAY 时，将下标指定为负数

VALUE_ERROR 赋值时，变量长度不足以容纳实际数据

LOGIN_DENIED PL/SQL 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码

NOT_LOGGED_ON PL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据

PROGRAM_ERROR PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL 系统包

ROWTYPE_MISMATCH 宿主游标变量与 PL/SQL 游标变量的返回类型不兼容

SELF_IS_NULL 使用对象类型时，在 null 对象上调用对象方法

STORAGE_ERROR 运行 PL/SQL 时，超出内存空间

SYS_INVALID_ID 无效的 ROWID 字符串

TIMEOUT_ON_RESOURCE Oracle 在等待资源时超时

----------------------------------------------------------------------------------------------------------------------

送书的B博士

关注

1
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
Oracle学习笔记

Oracle 学习笔记目录❀Oracle部分第一章数据库介绍 3第二章 Oracle简介 4第三章用户、权限 6第四章 Oracle数据类型 8第五章 SQL语句概述 9第六章表空间 11第七章表 12第八章函数 14第九章约束 17第十章单查询查询 22第十一章多表查询-内连接 23第十二章多表查询-外连接 25第十三章
复制链接

扫一扫