2009-12-08传智播客 数据库——Oracle触发器/存储过程/视图等（转载）

一、Merge语句

根据条件在表中执行修改或插入数据的功能，如果插入的数据行在目的表中存在就执行UPDATE，如果是不存在则执行INSERT:

-避免了单独的修改

-增强性能和应用性

                它的功能就是将，一个表中的数据加入到另一个表中。

                格式：

MERGE INTO 目标表

USING 源表

ON(目标表.FIELD = 源表.FIELD)

WHEN MATCHED THEN

  UPDATE SET

  目标表.FIELD1 = 源表.FIELD1,

  目标表.FIELD2 = 源表.FIELD2,

  ...

WHEN NOT MATCHED THEN

  INSERT (目标表.FIELD1,目标表.FIELD2,...)

  VALUES (FV1,FV2,...)




                如果不需要，insert() vale()。可以不写WHEN NOT MATCHED THEN这一部分。

例，在“EMP”表中添加一个“LOC”字段，将“DEPT”表中的“LOC”字段值根据员工的部门编号拷贝到“EMP”表中的“LOC”字段。如果有部门编号不存在的员工，则什么也不做。

ALTER TABLE EMP ADD LOC VARCHAR2(20)



MERGE INTO EMP E

USING DEPT D

ON(E.DEPTNO = D.DEPTNO)

WHEN MATCHED THEN

  UPDATE SET

  E.LOC = D.LOC




                数据库事务处理，在此就不复习了。



二、数据库的主要对象

                1.数据库的对象：

对象名称
描述

表
基本的数据存储对象，以行和列的形式存在，列是字段，行是记录

数据字典
也就是系统表，存放数据库相关信息的表

约束条件
执行数据校验，保证了数据完整性的

视图
一个或者多个表数据的逻辑显示

索引
用于提高查询的性能

同义词
对象的别名




                2.Oracle中常用字段类型：

数据类型
说明

char(size)
定长字符，≤2000个字节

varchar2(size)
变长字符，≤4000个字节

date
日期数据，默认的格式是dd-mm-yy：如11-6月-06

timestamp
日期数据，要比date数据更确切，包含时分秒。

integer
整型数据

number[(s,p)]
数字型，可存放实型和整型 ,精度(p)和范围(s)

long
可变字符，≤2GB个字符

float,real
是NUMBER的子类型

blob
存放图形、声音和影像，大二进制对象，≤4GB

clob
存放大文本文件，≤4GB




                3.使用子查询创建表：

                以前直接使用Create Tabel tab_name…创建表，如果想创建表的同时，将另一张表中的某些数据导入到新表怎么办？此时，子查询派上了用场：

                例，创建一个“EMPNEW”表，它与“EMP”表具有相同的字段和值：

CREATE TABLE EMPNEW

AS SELECT * FROM EMP WHERE DEPTNO IS NOT NULL AND DEPTNO>0




                例，创建一个“EMPNEW2”表，包含“员工编号”和“员工姓名”两个字段。它只需要“EMP”表中的“EMPNO”和“ENAME”字段和值：

CREATE TABLE EMPNEW2(

       "员工编号",

       "员工姓名"

) AS SELECT EMPNO,ENAME FROM EMP

--这两条语句实现的功能相同

CREATE TABLE EMPNEW2

AS SELECT EMPNO "员工编号",ENAME "员工姓名" FROM EMP




三、对表的字段和表的操作

1.在表中增加字段：

                例，在“EMP2”表中添加一个“EMAIL”字段：

ALTER TABLE EMPNEW2 ADD EMAIL VARCHAR2(30)




2.在表中修改字段：

                例，修改“EMPNEW2”表中字段“EMAIL”的默认值为[e-mail]：

ALTER TABLE EMPNEW2 MODIFY EMAIL VARCHAR2(30) DEFAULT '[e-mail]'


不是任何情况都可以修改的，当字段只包含空值时，

类型、大小都可以修改，否则修改可能不能成功。



3.在表中删除字段：

例，删除“EMPNEW2”表中的“EMAIL”字段：

ALTER TABLE EMPNEW2 DROP COLUMN EMAIL


删除大表中的字段将需要比较长的时间。



4.删除表的内容：

                例，删除“EMPNEW2”表中的内容：

TRUNCATE TABLE EMPNEW2


与delete语句相比：

Truncate语句清空表中所有的内容，而delete可以根据where条件选择性删除数据。

Truncate是DDL语句，如果在事件中使用它，它是不可以回滚的。

Truncate释放表空间，但delete不会。



5.删除表：

                例，删除表“EMPNEW2”：

DROP TABLE EMPNEW2


1、表中所有数据将被删除

2、没有完成的事务被提交

3、所有相关的索引被删除

4、这个删除操作不能回滚



6.修改对象名称：

                例，：将表“EMPNEW2”名称修改为“EMPTWO”：

RENAME EMPNEW2 TO EMPTWO


使用RENAME语句改变对象名称，可以修改表、视图、序列或者同义词的名称，必须是对象的所有者。



四、Oracle数据库中的表

1.查询数据字典：

                例，查看被用户拥有有的表（我使用的是Oracle自带的SCOTT用户）：

SELECT TABLE_NAME FROM USER_TABLES




                例，查看用户拥有的所有对象类型：

SELECT DISTINCT OBJECT_TYPE FROM USER_OBJECTS




                例，查看所有用户拥有的表（必须是DBA用户登录）：

SELECT * FROM DBA_TABLES




五、约束

1.什么是约束：

约束是在表上强制执行的数据校验规则.

当表中数据有相互依赖性时,可以保护相关的数据不被删除.

Oracle 支持下面五类完整性约束:

1、NOT NULL      非空

2、UNIQUE Key   唯一键

3、PRIMARY KEY              主键

4、FOREIGN KEY               外键

5、CHECK                            检察



2.非空约束：

约束字段值不能为空。

与其他约束相比，非空约束只能定义的字段级。

例，创建表“USERTABLE”并设置“NAME”字段非空：

--非空约束

CREATE TABLE USERTABLE(

       ID INT,

       NAME VARCHAR2(10) NOT NULL--字段级

)

--或：ALTER TABLE USERTABLE MODIFY NAME NOT NULL—表外级

--查看用户的约束

SELECT * FROM USER_CONSTRAINTS

--查看表的约束

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='EMP'



3.唯一性约束：

约束字段不准出现重复的记录，但可以出现一个或多个空值。

例，创建表“USERTABLE”并设置“EMAIL”字段唯一：

--唯一性约束

CREATE TABLE USERTABLE(

      ID INT,

      NAME VARCHAR(10) NOT NULL,

      EMAIL VARCHAR(30) UNIQUE--字段级

      --或：CONSTRAINT EMAIL_UN1 UNIQUE(EMAIL)--表级

)

--或：ALTER TABLE USERTABLE ADD CONSTRAINT EMAIL_UN1 UNIQUE(EMAIL)--表外级


注意：这种可以在表外定义的约束，一般都在表外定义。这样易于调整和管理。



4.主键盘约束：

主键从功能上看相当于非空且唯一

一个表中只允许一个主键

主键是表中能够唯一确定一个行数据的字段

主键字段可以是单字段或者是多字段的组合

例，创建表“USERTABLE”并设置“ID”字段为主键：

--主键约束

CREATE TABLE USERTABLE(

       ID INT PRIMARY KEY,--字段级

       NAME VARCHAR2(10) NOT NULL,

       EMAIL VARCHAR(30) UNIQUE

       --或：CONSTRAINT ID_PRI PRIMARY KEY(ID)--表级

)

--或：ALTER TABLE USERTABLE ADD CONSTRAINT ID_PRI PRIMARY KEY(ID)--表外级


注意：这种可以在表外定义的约束，一般都在表外定义。这样易于调整和管理。



5.外键约束：

外键是构建于一个表的两个字段或者两个表的两个字段之间的关系

外键确保了相关的两个字段的关系：

子表外键列的值必须在主表参照列值的范围内，或者为空

主表主键值被子表参照时，主表记录不允许被删除

外键约束条件参照的是主表的一个或者多个字段的值，通常被外键参照的是主表的主键或者唯一键

例，创建一个“ROLETABLE”角色表，在“USERTABLE”表中添加一个外键，指向角色ID：

--外键约束

CREATE TABLE ROLETABLE(

       ID INT PRIMARY KEY,

       MODIFYTAB NUMBER(4)

)

--

CREATE TABLE USERTABLE(

       ID NUMBER(4) PRIMARY KEY,

       NAME VARCHAR2(10) NOT NULL,

       EMAIL VARCHAR(30) UNIQUE,

       ROLE_ID NUMBER(4),

       CONSTRAINT FK_ROL FOREIGN KEY(ROLE_ID) REFERENCES ROLETABLE(ID)--表级

)

--或：ALTER TABLE USERTABLE ADD CONSTRAINT FK_ROL FOREIGN KEY(ROLE_ID) REFERENCES ROLETABLE(ID)--表外级




6.CHECK约束：

Check约束条件是一种比较特殊的约束条件，通过check定义，

强制定义在字段上的每一记录都要满足check中定义的条件。

在check中定义检查的条件表达式，进入表中的数据必须符合。

check中设置的条件

条件表达式不允许使用：

1、SYSDATE, USER等函数

2、参照其他记录的值

例，为 “EMP”表中的“SAL”字段添加约束：

ALTER TABLE EMP ADD CONSTRAINT CK_SAL CHECK(SAL>6000)




7.删除约束：

删除约束条件对于表和数据不会产生影响

--删除“EMP”表中的“SAL”字段的CHECK约束

ALTER TABLE EMP DROP CONSTRAINT CK_SAL

--删除“ROLETABLE”中的“ID”字段的主键约束，同时“USERTABLE”的外键约束也被删除

ALTER TABLE ROLETABLE DROP PRIMARY KEY CASCADE




六、索引

1.什么时索引：

方案（schema）中的一个数据库对象

在 Oracle数据库中用来加速对表的查询

通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O

与表独立存放

由 Oracle数据库自动维护



2.索引的创建方法：

自动：当在表上定义一个PRIMARY KEY或者UNIQUE约束条件时，Oracle数据库自动创建一个对应的唯一索引。

手动：用户可以创建索引以加速查询。



3.创建索引：

例，创建一个“USERTABLE”，为“ID”字段创建索引：

--创建索引

CREATE TABLE USERTABLE(

       ID NUMBER(4),

       NAME VARCHAR2(10)

)

CREATE INDEX USERTABLE_INDEX ON USERTABLE(ID)




4.删除索引：

例，删除“USERTABLE”中“ID”字段的索引：

--删除索引

DROP INDEX USERTABLE_INDEX


删除索引，必须拥有索引或者拥有 DROP ANY INDEX 权限。



七、视图

1.什么是视图：

视图也就是虚表，实际上视图就是一个命名的查询，用于改变基表数据的显示。

可以限制对数据的访问

可以使复杂的查询变的简单

提供了数据的独立性

提供了对相同数据的不同显示



2.创建视图：

例，查询“EMP”表中薪金大于8000的员工。

SELECT * FROM EMP WHERE SAL>8000


如果上面的查询语句是我们经常要使用到的呢？每次都要编写这个语句吗？视图在此发挥了作用：

--创建视图

CREATE OR REPLACE VIEW SAL8000

AS SELECT * FROM EMP WHERE SAL>8000




3.从视图中查询数据：

--从视图中查询数据

SELECT * FROM SAL8000


就像是定义了一个宏！

如果创建视图“SELECT * FROM…”中的“*”，是某几个字段。则查询视图中也仅能查询创建视图时指定的字段。

查询视图结构，在命令窗口中输入：DESC 视图名称



4.修改视图：

例，修改上面创建的视图“SAL8000”，为每个列指定列名：

--修改视图

CREATE OR REPLACE VIEW SAL8000

AS SELECT EMPNO 工号,ENAME 姓名 FROM EMP WHERE SAL>8000


这视图应该是可以被覆盖的，没发现这里有修改的关键字。



5.创建复杂视图：

例，创建一个“EMP”表与“DEPT”表能过“DEPTNO”字段关联的视图：（联表视图）

--创建复杂视图

CREATE OR REPLACE VIEW EMPANDDEPT

AS SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME,D.LOC

FROM EMP E, DEPT D

WHERE E.DEPTNO = D.DEPTNO




6.禁止视图进行DML操作：

例，修改上边的视图，使其只能查询，不能进行DML操作：

--使用WITH READ ONLY禁止视图进行DML操作

CREATE OR REPLACE VIEW EMPANDDEPT

AS SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME,D.LOC

FROM EMP E, DEPT D

WHERE E.DEPTNO = D.DEPTNO

WITH READ ONLY




7.删除视图：

例，删除上面创建的“DEMANDDEPT”视图：

--删除视图

DROP VIEW EMPANDDEPT




8.行内视图：

行内视图是在SQL语句中使用的一个带有别名的子查询.

在主查询FROM 子句中的子查询就是行内视图.

行内视图不是数据库的对象,所以不需要显式的创建.



八、TOP-N分析

1．什么是TOP-N：

使用Top-N分析法可以得到某列上最什么的前n个值. 例如:

薪水最高的前5个员工?

人员最多的3个部门?

最大的几个值和最小的几个值都可以通过Top-N 分析的方法得到.



2.使用TOP-N：

例，查询“EMP”表中工资最高的前3位员工：

--TON-N分析

SELECT EMPNO,ENAME,JOB,SAL

FROM (

       SELECT ROWNUM,EMPNO,ENAME,JOB,SAL FROM EMP ORDER BY SAL DESC

) WHERE ROWNUM < 4


1、使用了rownum这个伪列，这个伪列将会返回行号，可以作为返回记录的序列号显示。

2、在from后面使用了子查询，这是标准的行内视图的使用。

3、在主查询中通过where条件中的rownum伪列定义过滤条件，只返回最什么的前几行数据。



3.数据库中的记录分布技术：

正是使用TOP-N分析实现的。

例，查询工资排行的第4-7位的员工信息：

--TOP-N分页

SELECT EMPNO,ENAME,JOB,SAL

FROM(

       SELECT ROWNUM MR,EMPNO,ENAME,JOB,SAL FROM(

              SELECT ROWNUM,EMPNO,ENAME,JOB,SAL FROM EMP ORDER BY SAL DESC

       )WHERE ROWNUM < 7

)WHERE MR > 3


上面我们是取页面的中间部分，可以将7换为3，3换为0。这样取的就是页面首部（前三位）！



九、同义词

1.什么是同义词：

同义词是数据库中一个对象的别名,可以简化对对象的访问。

通过使用同义词，可以：

   1、简化了引用另一个用户对象的方法

   2、缩短了对象名称的长度



2.创建同义词：

例，为“EMP”表创建一个同义词“E”：

--同义词

CREATE SYNONYM E FOR EMP




3.使用同义词：

例，使用同义词查询“EMP”所有记录：

--使用同义词（默认为私有同义词）

SELECT * FROM E




4.公开与私有同义词：

--公开同义词

CREATE PUBLIC SYNONYM E FOR EMP


公开同义词任何用户都可以访问，私有同义词只有创建它的用户才可以访问。



5.删除同义词：

例，删除上面创建的同义词“E”：

--删除同义词

DROP SYNONYM E




十、序列

1.什么是序列：

是一数据库对象，利用它可生成唯一的整数。

一般使用序列自动地生成表主键值或唯一键值

不直接连接到数据库中的任何表



2.定义序列的语法：

CREATE SEQUENCE [user.]sequence_name
[increment by n]
[start with n]
[maxvalue n|]
[minvalue n ];
[cache]

[NOCYCLE|CYCLE]



INCREMENT BY： 指定序列号之间的间隔，该值可为正的或负的整数，但不可为0。序列为升序。忽略该子句时，缺省值为1。

START WITH：指定生成的第一个序列号。在升序时，序列可从比最小值大的值开始，缺省值为序列的最小值。对于降序，序列可由比最大值小的值开始，缺省值为序列的最大值。

MAXVALUE：指定序列可生成的最大值。

MINVALUE：指定序列的最小值

NOCYCLE：一直累加，不循环

CYCLE：累加到最大值循环

CACHE：默认值是20

序列里的缓冲是大量并发提取序列值时提升性能的一种手段。

由于序列操作是原子操作，如果没有缓冲，序列的访问将只能由一个事务独占，独占期间阻塞其它事务的访问，且一定会涉及硬盘操作。这样如果有对一个序列的大量并发操作的话，系统性能将会下降。

缓冲是指事先成批的提取序列值，缓冲在内存中，供多个事务同时访问（每个事务占一个缓冲的序列值），且访问期间如果没有用尽缓冲是不用写硬盘的，可大幅度提高并发访问序列的性能。但带来的问题是序列值可能不连续（但肯定保证唯一），如并发访问序列值后有些事务回滚等操作，都会带来缓冲序列值的不连续或丢失。



3.创建序列：

例，创建一个“SEQ_EMP”序列：

--创建序列

CREATE SEQUENCE SEQ_EMP

INCREMENT BY 1

START WITH 1

MAXVALUE 100

MINVALUE 1

CYCLE




4.查看序列：

例，查看“SEQ_EMP”序列：

--查看序列

SELECT * FROM USER_SEQUENCES

--查看初始化序列，NEXTVAL将引起序列值增加“INCREMENT BY”

SELECT SEQ_EMP.NEXTVAL FROM DUAL

--查看序列当前值

SELECT SEQ_EMP.CURRVAL FROM DUAL




5.使用序列：

例，向“EMP”表中插入新记录。 “EMPNO”字段使用上面创建的“SEQ_EMP”序列的值：

--使用序列

INSERT INTO EMP (EMPNO,ENAME) VALUES (SEQ_EMP.NEXTVAL, 'XLTEST')


如果某个字段使用“SEQ_EMP”序列的值，就应该一直使用这个序列，否则可能会造成字段值重复。我理解序列就是自定义的“auto_increment”，可能还有些其他用途。



6.修改序列：

例，修改上面创建的“SEQ_EMP”序列：

--修改序列

ALTER SEQUENCE SEQ_EMP

INCREMENT BY 2

MAXVALUE 200

MINVALUE 2


修改序列可以：

修改未来序列值的增量。

设置或撤消最小值或最大值。

改变缓冲序列的数目。

指定序列号是否是有序。（这应该就是循环或不循环）

注意：

1．第一次NEXTVAL返回的是初始值

2．可以修改除start以外的所有sequence的参数。如果想要改变start值，必须删除序列再重新创建。



7.删除序列：

例，删除序列“SEQ_EMP”：

--删除序列

DROP SEQUENCE SEQ_EMP




十一、匿名块

1.什么是匿名块：

匿名块是只使用一次的PL/SQL程序块

匿名块没有名称，也不被存储在数据库中

由PL/SQL的四个基本组成部分构成

不能被重复使用



2.匿名块语法：

DECLARE

        变量、常量声明;

BEGIN

        pl/sql_block;

EXCEPTIONS

        异常捕捉;

END;




十二、存储过程

1.什么是存储过程：

用于在数据库中完成特定的操作或者任务。其实，它就是数据库中的自定义函数！

语法：

Create PROCEDURE name

                parameter_name [IN | OUT | IN OUT] datatype

                [{:= | DEFAULT} expr]

IS

pl/sql_block;


在Sql*Plus 中使用CREATE OR REPLACE子句创建存储程序单元

在头部定义所有参数

IS表示PL/SQL块的开始。

在IS之后，声明本地变量



2.存储过程的参数模式：

IN
OUT
IN OUT

默认

值：

传递给子程序
手动指定



返回到调用环境
手动指定



传递给子程序，返回到调用环境

参数形式：

常量       
 

未初始化的变量
 

初始化的变量

实参：
可以是表达式，常量，或者是初始化的变量。
 

必须是一个变量
 

必须是一个变量


上面一年便知：

IN，就是传入参数。

OUT，就是接收返回值。

INOUT，就是传入参数后，也接收返回值。



3.创建存储过程：

例，创建一个根据“EMPNO”字段更新“EMP”表中“SAL”字段值，并返回“OK!”：

--创建存储过程

CREATE OR REPLACE PROCEDURE UPEMPFUN(

       ARG_EMPNO IN NUMBER,

       ARG_SAL IN NUMBER,

       ARG_RES OUT VARCHAR

)IS BEGIN 

    UPDATE EMP SET SAL = ARG_SAL WHERE EMPNO=ARG_EMPNO;

    COMMIT; 

    ARG_RES := 'OK!';

END;




4.调用存储过程：

例，调用上面创建的存储过程：

--调用存储过程

DECLARE

  ARG_RES VARCHAR(20);

BEGIN

  UPEMPFUN(7369,99999,ARG_RES);

  DBMS_OUTPUT.PUT_LINE(ARG_RES);

END;