Oracle11g入门到精通摘要

最新推荐文章于 2025-06-21 12:28:56 发布

原创最新推荐文章于 2025-06-21 12:28:56 发布 · 2.3k 阅读

4 ·

CC 4.0 BY-SA版权

Oracle 专栏收录该内容

1 篇文章

订阅专栏

本文详细介绍SQL语言的基础知识，包括SQL的特点、关键字使用、数据表的创建与管理、数据查询、数据操纵等内容，适合初学者入门。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一.SQL语言基础：

1.SQL采用集合操作方式，对数据的处理是成组进行的，而不是一条一条处理的。通过哟使用集合操作方式，可以加快数据的处理速度。

2.基表（Base Table）是本身独立存在的表，在SQL中一个关系就对应一个基表。

3.视图（View）是从基表或其他视图中导出的表，它本身不独立存储在数据库中，即数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基表中，因此视图是一个虚表。

4.SQL关键字不区分大小写，既可以使用大写格式，也可以使用小写格式，或者混用大小写格式。

5.字符值和日期值区分大小写。如：

SELECT employee_name,salary,job,deptno FROM employee WHERE employee_name = 'SCOTT';
SELECT employee_name,salary,job,deptno FROM employee WHERE employee_name = 'scott';

以上两个SQL语句的执行结果是不一样的，因为在WHERE子句中'SCOTT'和'scott'是不一样的两个名称。

6.CREATE：在数据库中，对所有数据对象的创建均由CREATE语句来完成。

（1）.创建表，一般格式如下：（[]表示可选）

CREATE TABLE<表名>（

<列名><数据类型>[列级完整性约束条件]

[,<列名><数据类型>[列级完整性约束条件]]……

[,<表级完整性约束条件>];

）

如：

create table person(
	p_id NUMBER(6) not null unique,
	p_name VARCHAR2(20),
	p_salary NUMBER(8,2)
)

系统执行CREATE TABLE语句后，就在数据库中建立了一个新的空的person表，并将有关person表的定义及有关约束条件存放在数据字典中。

（2）.创建视图

视图是从基表或其他视图中导出的表，它本身不独立存储在数据库中，即数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基表中，因此视图是一个虚表。

一般格式如下：

CREATE VIEW <视图名>[(<列名>[,<列名>],,,)]

AS<子查询>

[WITH CHECK OPTION]

其中，子查询可以是不包含ORDER BY子句和DISTINCT短语的任意复杂的SELECT语句。WITH CHECK OPTION表示对视图进行UPDATE、INSERT和DELETE操作时，要保证更新、插入或删除的行满足视图中的谓词条件（即子查询中的条件表达式）。例如：

我创建一个视图：

create view IS_student
  as
select sno,sname,sage
from student
  where sname='IS'
with check option;
  加上了with check option;后，不能执行插入操作

insert into is_student values('95100','李娜',12);

原因是当创建了这个视图后，因为有WITH CHECK OPTION这条语句，表示接下来对这个视图的增删改都必须满足where sname='IS'这个条件，执行上面那句增加操作的时候相当于执行insert into is_student values('95100','李娜',12) where sname='IS'条件不满足所以不能成功执行。

（3）创建索引

在SQL语言中，建立索引使用CREATE INDEX语句，其一般格式为：

CREATE[UNIQUE][CLUSTER]INDEX<索引名>

ON<表名>(<列名>[<次序>][,<列名>[<次序>]]……);

其中，UNIQUE选项表示此索引的每一个索引值不能重复，对应唯一的数据记录。CLUSTER选项表示要建立的索引是聚簇索引。<表名>是所要创建索引的基表的名称。索引可以建立在对应表的一列或多列上，如果是多个列，各列名之间需用逗号分隔。<次序>选项用于指定索引值的排列次序，ASC表示升序，DESC表示降序，默认为ASC。

提示：聚簇索引指的是索引项的顺序与表中记录的物理顺序相一致的索引组织。

如：

CREATE INDEX IT_LASTNAME ON IT_EMPLOYEES(LAST_NAME);

上述语句执行后将回在IT_EMPLOYEES表的LAST_NAME列上建立一个索引，而且IT_EMPLOYEES表中的记录将按照LAST_NAME值升序存放。

用户可以在查询频率高的列上建立聚簇索引，从而提高查询效率。由于聚簇索引是将索引和表记录放在一起存储，所以在一个基表上最多只能建立一个聚簇索引。在建立聚簇索引后，由于更新索引列数据时会导致表中记录的物理顺序的变更，系统代价较高，因此对于经常更新的列不宜建立聚簇索引。

7.DROP：当某个数据对象不再被需要，可以将它删除，SQL语言用来删除数据对象的语句是DROP。

（1）.删除表一般格式为：

DROP TABLE<表名>;

例如，删除IT_EMPLOYEES表的语句为：

DROP TABLE IT_EMPLOYESS;

删除基表定以后，表中的数据、在该表上建立的索引都将自动被删除掉。因此执行删除基表的操作时一定要谨慎。

注意：在有的系统中，删除基表会导致在此表上建立的视图也一起被删除，但在Oracle中，删除基表后建立在此表上的视图定义仍然保留在数据字典中，而当用户引用该视图时会报错。

（2）删除视图一般格式为：

DROP VIEW<视图名>;

视图删除后视图的定义将从数据字典中删除。但是要注意，由该视图导出的其他视图定义仍在数据字典中，不会被删除，这将导致用户在使用相关视图时会发生错误，所以删除视图时要注意视图之间的关系，需要使用DROP VIEW语句将这些视图全部删除。同样删除基表后，由该基表导出的所有视图并没有被删除，需要继续使用DROP VIEW语句一一进行删除。

（3）删除索引一般格式为：

DROP INDEX<索引名>;

8.ALTER：随着应用环境和应用需求的变化，有时需要修改已建立好的基表，SQL语言用ALTER TABLE语句修改基表，其一般格式为：

ALTER TABLE<表名>

[ADD<新列名><数据类型>[完整性约束]]

[DROP<完整性约束名>]

[MODIFY<列名><数据类型>];

其中，<表名>表示所要修改的基表，ADD子句用于增加新列和新的完整性约束条件，DROP子句用于删除指定的完整性约束条件，MODIFY子句用于修改原有的列定义，如修改列名和数据类型。例如：

（1）.向IT_EMPLOYEES表中增加“雇员生日”列，其数据类型为日期型：

ALTER TABLE IT_EMPLOYEES ADD BIRTH_DATE DATE;

无论基表中原来是否有数据，增加的列一律为空值。

（2）.将IT_EMPLOYEES表的MANAGER_ID字段改为8为：

ALTER TABLE IT_EMPLOYEES MODIFY MANAGER_ID NUMBER(8);

（3）删除IT_EMPLOYEES表EMPLOYEE_ID字段的UNIQUE约束：

ALTER TABLE IT_EMPLOYEES DROP UNIQUE(EMPLOYEE_ID);

注意：在SQL语言中，并没有提供删除属性列的语句，用户只能通过间接的方法实现这一功能。首先将被删除表中要保留的列及其内容复制到一个新表中，然后删除原表，最后再将新表重命名为原表名即可。

9.数据查询

（1）.DISTINCT关键字：删除结果集中重复的行。例如：

select distinct department_id from employees;

那么在查询结果中department_id是不会有重复的。

（2）.WHERE子句：WHERE子句用于筛选从FROM子句中返回的值，完成的选择操作。只有满足WHERE子句中判断条件的行才会显示，反之不显示。一般格式如下：

SELECT column_list

FROM table_name

WHERE conditional_expression 这个为查询时返回记录满足的判断条件。

*条件表达式：可以用运算符来对值进行比较。可用的运算符介绍如下：

|-- A=B 表示若A与B的值相等，则为TRUE。

|-- A>B表示若A大于B的值，则为TRUE。

|-- A<B表示若A小于B的值，则为TRUE。

|-- A!=B或A<>B 表示若A不等于B的值，则为TRUE。

|-- A LIKE B其中，LIKE是匹配运算符。在这种判断条件中，若A的值匹配B的值，则该判断条件为TRUE。在LIKE表达式中可以使用通配符。Oracle支持的通配符为：“%”代表0个、1个或多个任意字符，使用“_”代表一个任意字符。

|-- NOT<条件表达式> NOT运算符用于对结果取反。

例如：编写一个查询，判断所有FIRST_NAME列以“B”开头的雇员。

select employee_id,first_name,last_name from employees 
where first_name like 'B%';

*连接运算符：在WHERE子句中可以使用连接运算符将各个表达式关联起来组成复合判断条件。常用的有AND和OR。

贼复合判断条件中，需要注意运算符的优先级。Oracle会先运算优先级高的运算符，然后再运算优先级低的运算符，同级别的优先级则从左到右进行运算。为了增加可读性，可以使用括号将各个表达式括起来。

例如：

select employee_id,first_name,last_name,department_id from employees 
where (department_id=60) or (department_id=30);

*NULL值：在数据库中，NULL值是一个特定的术语，用来描述记录中没有定义内容的字段值，通常我们称之为空。

例如：往EMPLOYEES表中插入一条manager_id列为null的值

insert into departments(department_id,department_name,manager_id)
values(300,'数据库',NULL);

NULL值是一个特殊的取值，使用“=”对NULL值进行查询是无法得到需要的结果的。

例如：查询manager_id为null的值，使用=。

select department_id,department_name,manager_id
from departments
where manager_id = NULL;

提示：从查询结果中可以看出，这样查询的结果集什么都没，所以不能使用manager_id=NULL这样的判断方式。

Oracle提供了两个SQL运算符，IS NULL和IS NOT NULL。使用这两个运算符，可以判断某列的值是否为NULL：

select department_id,department_name,manager_id
from departments
where manager_id is NULL;

（3）.ORDER BY子句：在前面介绍的数据检索技术中，只是把数据库中的数据从表中直接取出来。这时，结果集中数据的排列顺序是由数据的存储顺序决定的。但是显然这个不符合我们的需求。我们需要对结果集进行排序。在SELECT语句中，可以使用ORDER BY子句实现对查询的结果集进行排序。使用ORDER BY子句的语法形式如下：

SELECT column_list

FROM table_name

ORDER BY[(order_by_expression[ASC|DESC])…]order_by_expression表示将要排序的列名或由列名组成的表达式，关键字ASC升序，也是默认排列顺序，DESC表示降序排列。
例如：使用ORDER BY子句对检索到的数据进行排序，该排列顺序是按照薪资从低到高的升序进行的。

select employee_id,first_name,last_name,salary
from employees
where salary>2000
order by salary;

如果需要对多个列进行排序，只需要在ORDER BY子句后指定多个列名。这样当输出排序结果时，首先根据第一列进行排序，当第一列的值相同时，再对第二列进行比较排序。其他列以此类推。

例如：首先对job_id排序，然后再排序SALARY。

select last_name,job_id,salary
from employees
where salary>2000
order by job_id,salary desc;

（4）.GROUP BY子句：GROUP BY子句用于在查询结果集中对记录进行分组，以汇总数据或者为整个分组显示单行的汇总信息。说白了就是按group by后面条件分组，条件一样的就合并。

使用group by子句的注意事项：

*出现在SELECT列表中的字段，如果出现的位置不是在组函数中，那么必须出现在GROUP BY子句中。

*在GROUP BY子句中出现的字段，可以不出现在SELECT子句中。

*不能在WHERE子句中使用组函数！原因：

执行整个语句的过程是：最先执行的是WHERE子句--->对数据结果进行GROUP BY分组--->对分组后的数据进行Having子句的组函数过滤--->最终通过ORDER BY子句进行排序--->将排序的结果返回给用户。所以说聚组函数放在WHERE子句中肯定不行的，因为那时还没有数据结果！
（5）.HAVING子句：HAVING子句是对GROUP BY分组后的结果进行限制。

一般语法如下：

SELECT column,group_function

FROM table

[WHERE condition]

[GROUP BY group_by_expression]

[HAVING group_condition]

[ORDER BY column];

例如：按部门进行分组，输出部门id,部门的平均工资，要求平均工资>2000，并且部门不为NULL，并且不是10号部门。

select department_id,avg(salary) from employees
where department_id is not null and department_id !=10
group by department_id
having avg(salary)>2000

（6）.多表连接查询：

①等值连接：简单连接使用逗号将两个或多个表进行连接，这是最简单、也是最常用的多表查询形式。

基本形式：简单连接仅是通过SELECT子句和FROM子句来连接多个表，其查询的结果是一个通过笛卡尔积所生成的表。所谓笛卡尔积所生成的表，就是由一个基表中每一行与另一个基表的每一行连接在一起所生成的表，查询结果的行数是两个基表行数的积。

例如：以下的查询将EMPLOYEES表和DEPARTMENTS表相连接，从而生成一个笛卡尔积。

select employee_id,lase_name,department_name from employees,departments;

条件限定：笛卡尔积中包含大量数据冗余，所以我们需要在SELECT语句中加入WHERE条件语句。

例如：

select employee_id,lase_name,department_name from employees,departments
where employees.department_id = departments.department_id;

注意：在以上示例中，连接的两个表具有同名的列，则必须使用表名对列进行限定，以确认该列属于哪一个表。

表别名：在夺标查询时，如果多个表直接存在同名的列，则必须使用表名来限定。但是，随着查询变得越来越复杂，语句会因为每次限定列时输入表名而变得冗长。因此，SQL语言提供了另一种机制------表别名。表别名是在FROM子句中用于各个表的”简短名称“。

例如：

select emp.employee_id,emp.lase_name,dep.department_name from employees emp,departments dep
where emp.department_id = dep.department_id;

注意：如果为表指定了别名，那么语句中的所有子句都必须使用别名，而不允许再使用实际的表名。

不允许的原因：在SELECT语句的顺序中，FROM子句最先被执行，然后就是WHERE子句，最后才是SELECT子句。当在FROM子句中指定列表名后，表的真实名称将被替换。同时，其他的子句只能使用表别名来限定列。在上面的示例中（省略），由于FROM子句已经用表别名覆盖了表的真实名称，当执行SELECT子句选择显示的列时，将无法找到真实表名称EMPLOYEES所限定的列。

②不等值连接：不等值连接是指在连接条件中使用除相等比较符外的其他比较操作符的连接查询，并且不等连接主要用于在不同表之间显示特定范围的信息。

例如：

select e.employee_id,e.first_name,e.salary,j.grade_level,j.lowest_sal,j.highest_sal
from employees e,job_grade j
where e.salary between j.lowest_sal and j.highest_sal;     //用到了between and关键字  表示在什么范围里

③外连接：使用外连接可以看到不满足条件的记录，在Oracle9i之前，当执行外连接时，都是使用连接操作符(+)来完成的。分左外连接和右外连接。一般格式如下：

左外连接：

SELECT table.column,table.column

FROM table1,table2

WHERE table1.column=table2.column(+);

右外连接：

SELECT table.column,table.column

FROM table1,table2

WHERE table1.column(+)=table2.column;

当使用(+)操作符执行外连接时，应该将操作符放在显示较少行（完全满足连接条件行）的一端。当使用（+）操作符时，必须要注意以下事项：用(+)操作外连接时，(+)操作符位置正好相反，如左外连接时，(+)在右边。左边的显示多。冯威的视频有错误，他讲的Oracle9i之间的语法认为(+)在那边就是哪连接，其实是不对的，应该反一下。

Ⅰ.(+)操作符只能出现在WHERE子句中，并且不能与OUTER JOIN语法同时使用。

Ⅱ.当使用(+)操作符执行外连接时，如果在WHERE子句中包含多个条件，则必须在所有条件中都包含(+)操作符。

Ⅲ.(+)操作符只适用于列，而不能用在表达式上。

Ⅳ.(+)操作符不能与OR和IN操作符一起使用。

Ⅴ.(+)操作符只能用于实现左外连接和右外连接，而不能用于实现全外连接。

左外连接：

例如：显示部门为10的部门名、雇员名以及其他部门名为例，使用(+)操作符执行左外连接的方法

SELECT a.dname,b.ename FROM dept a, emp b

WHERE a.deptno=b.deptno(+) AND b.deptno(+)=10

DEPT表：

EMP表：

效果如下：（左边表）DEPT全部显示（右边表）EMP和DEPT相匹配的显示

因为在DEPT表中DEPTNO=10的在EMP表中DEPTNO=10的有3条记录这3条记录的显示实际上是等值连接。按照第一个条件等值连接的话，RESEARCH应该有5条记录，但是由于第二个条件是b.deptno(+)=10所以没有对应ENAME的值。下面的记录没有ENAME值的原因也一样。

右外连接：

例如：显示部门为10的部门名、雇员名以及其他部门名为例，使用(+)操作符执行右外连接的方法

SELECT a.dname,b.ename FROM dept a, emp b

WHERE a.deptno(+)=b.deptno AND a.deptno(+)=10

效果如下：（右边表）EMP全部显示（左边表）DEPT和EMP相匹配的显示

下面介绍的是Oracle9i之后的外连接语法

在Oracle9i之前，外连接语法都是在WHERE子句中指定的；从Oracle9i开始，还可以在FROM子句中指定连接语法。语法如下：

SELECT table1.column,table2.column

FROM table1[INNER| LEFT | RIGHT | FULL] JOIN table2 ON table1.column1 = table2.column2

INNER JOIN表示内连接；LEFT JOIN表示左外连接；RIGHT JOIN表示右外连接；FULL JOIN表示完全外连接；ON子句用于指定连接条件。注意，如果使用FROM子句指定内、外连接，则必须要使用ON子句指定连接条件；如果使用(+)操作符指定外连接，则必须使用WHERE子句指定连接条件。

1.内连接：内连接用于返回满足连接条件的所有记录。默认情况下，在执行连接查询时如果没有指定任何连接操作符，那么这些连接查询都属于内连接。

例如：显示部门为10的部门名称以及雇员名称，使用内连接的方法。

没有指定任何连接操作符两个表的等值连接（这是在Oracle9i之前的写法）：

SELECT a.dname,b.ename FROM dept a,emp b
WHERE a.deptno = b.deptno AND a.deptno=10;

指定了INNER JOIN连接操作符两个表的等值连接（这是在Oracle9i之后的写法这也是Hibernate中使用的语法）：

SELECT a.dname,b.ename FROM dept a INNER JOIN emp b ON a.deptno = b.deptno AND a.deptno=10;

上面两句SQL语句的执行结果是一样的。

三个表的等值连接，分别用Oracle9i之前的语法和Oracle9i之后的语法写：

Oracle9i之前的语法：

SELECT e.employee_id,e.first_name,d.department_id,d.department_name,l.location_id,l.city
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id

Oracle9i之后的语法：(下面这个例子中在JOIN前省略的INNER)

SELECT e.employee_id,e.first_name,d.department_id,d.department_name,l.location_id,l.city
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
JOIN locations l ON (d.location_id = l.location_id)

从Oracle9i开始，如果主表的主键列和从表的外部键列名称相同，那么还可以使用NATURAL JOIN关键字自动执行内连接操作。（也就是说等值连接的另一种方式是NATURAL JOIN）也称为自然连接。

当表EMP中有一个Address字段，表DEPT中也有一个Address字段，这时候使用自然连接是没有什么意思的，EMP的Address代表员工住址，而DEPT的Address代表单位地址。所以自然连接在实际的应用中使用次数很少。

USING子句：如果一些列有相同的名称但是类型不匹配，自然连接将出错，可以在自然连接的NATURAL JOIN子句上使用USING子句来设置用于等值连接的。

同时需要注意的是：不要在参照列上使用列名或者别名作为前缀。而且此时不能再写NATURAL。

例如：SELECT empno,dname FROM emp JOIN dept USING(deptno)

SELECT empno,dname FROM emp NATURAL JOIN dept 这句SQL语句和上面那句的执行效果是一样的，因为deptno类型一样。

例如：

SELECT a.dname,b.ename FROM dept a NATURAL JOIN emp b

SELECT a.dname,b.ename FROM dept a INNER JOIN emp b ON a.deptno = b.deptno

上面两句是一样的效果。

2.左外连接：左外连接是通过指定LEFT [OUTER] JOIN 选项来实现的。当使用左外连接时，不仅会返回满足连接条件的所有记录，而且还会返回不满足连接条件的连接操作符左边表的其他行。

例如：显示部门为10的部门名、雇员名，使用左外连接的方法。

SELECT a.dname,b.ename FROM dept a LEFT JOIN emp b
ON a.deptno = b.deptno AND a.deptno = 10;

效果如下：

3.右外连接：右外连接是通过RIGHT [OUTER] JOIN选项来实现的。当使用右外连接时，不仅会返回满足连接条件的所有行，而且还会返回不满足连接条件的连接操作符右边表的其他行。

例如：显示部门为10的部门名、雇员名，使用右外连接的方法。

SELECT a.dname,b.ename FROM dept a RIGHT JOIN emp b
ON a.deptno = b.deptno AND a.deptno = 10;

效果如下：

4.完全外连接：完全外连接是通过指定FULL [OUTER] JOIN选项来实现的。当使用完全外连接时，不仅会返回满足连接条件的所有行，而且还会返回不满足连接条件的所有其他行。

例如：显示部门为10的部门名、雇员名，使用完全外连接的方法。

SELECT a.dname,b.ename FROM dept a FULL OUTER JOIN emp b
ON a.deptno = b.deptno AND a.deptno = 10;

5.自连接：自连接是指在同一张表之间的连接查询，它主要用在自参照表上显示上下级关系或者层次关系。自参照表是指在不同列之间具有参照关系或主从关系的表。例如，EMP表包含有EMPNO（雇员号）和MGR（管理者号）两列，二者就具有参照关系。

例：将上面这个表按照下面这个图的格式查询

这时，可以将这一张表分成两个表来看待，一个是员工表，一个是管理者表。

SELECT e.employee_id,e.first_name,m.manager,m.first_name
FROM employees e,employees m
WHERE e.employee_id = m.employee_id;

6.交叉连接：在两个表联合查询的的时候，其中一张表的的一条记录会和另一张表的所有记录产生和另一张表所有记录条数的一样的结果。

例如：下图如果使用交叉连接，就会产生笛卡尔积，产生一个总条数为65条的记录。

在Oracle8i：

SELECT * FROM emp e,dept d    没有写WHERE条件

在Oracle9i：

SELECT * FROM emp CROSS JOIN dept

④子查询：子查询是指潜入在其他SQL语句中的SELECT语句，也称为嵌套查询。

使用子查询的注意事项：

1.子查询要用括号括起来

2.将子查询放在比较运算符的右边（增强可读性）

3.在Oracle8i之前的版本中，子查询不能包含Order By子句

4.对单行子查询使用单行运算符，对多行子查询使用多行运算符。

单行子查询：是指只返回一行数据的子查询语句。当在WHERE子句中引用单行子查询时，可以使用单行比较符（=，>，<，>=，<=，<>）。

例如：显示SCOTT同事的姓名、工资和部门号为例，用单行子查询写。

SELECT ename,sal,deptno
FROM emp 
WHERE deptno = 
(SELECT deptno FROM emp WHERE ename = 'SCOTT');

多行子查询：是指返回多行数据的子查询语句。当在WHERE子句中使用多行子查询时，必须要使用多行比较符（IN,ALL,ANY）。它们的作用如下：

注意，ALL和ANY操作符不能单独使用，而只能与单行比较符（=，>，<，>=，<=，<>）结合使用。

1.在多行子查询中使用IN操作符：可以将原表中特定列的值与子查询返回的结果集中的值进行比较，如果某行的特定列的值存在，则在SELECT语句的查询结果中就包含这一行。

例如：查询所有的员工信息，并且员工的job在10号部门存在的。

SELECT * FROM emp WHERE job IN
(SELECT DISTINCT job FROM emp WHERE deptno=10);

2.在多行子查询中使用ANY操作符：ANY操作符必须与当行操作符结合使用，并且返回行只需匹配于子查询的任一个结果就行。

ANY通常与大小于符号搭配使用，不单独使用。可以是<ANY和>ANY。

⑴<ANY 表示小于子查询数据中的最大值

⑵>ANY 表示大于子查询数据中的最小值

例如：查询工资小于各部门的平均工资最大值的员工信息

这句话可以这么理解：首先要获取各个部门的工资平均值然后查询所有员工信息的工资

SELECT * FROM emp WHERE sal<ANY
(SELECT AVG(sal) FROM emp GROUP BY deptno)

例如：查询工资大于各部门的平均工资最小值的员工信息

SELECT *
FROM emp WHERE sal>ANY
(SELECT AVG(sal) FROM emp GROUP BY deptno)

3.在多行子查询中使用ALL操作符：ALL操作符必须与单行操作符结合使用，并且返回行必须要匹配于所有子查询结果。

ALL通常与大小于符号搭配使用，不单独使用。可以是<ALL和>ALL。

⑴>ALL 表示大于子查询数据中的最大值

⑵<ALL 表示小于子查询数据中的最小值

例如：查询工资大于各部门的平均工资最大值的员工信息

SELECT *
FROM emp WHERE sal>ALL
(SELECT AVG(sal) FROM emp GROUP BY deptno)

例如：查询工资小于各部门的平均工资最小值的员工信息

SELECT *
FROM emp WHERE sal<ALL
(SELECT AVG(sal) FROM emp GROUP BY deptno)

EXISTS关键字：在一些情况下，只需要考虑是否满足判断条件，而数据本身并不重要，这时就可以使用EXISTS关键字来定义子查询。EXISTS关键字只注重子查询是否返回行，如果子查询返回一个或多个行，那么EXISTS便返回为TRUE,反之为FALSE。

例如：

SELECT employee_id,last_name FROM employees em
WHERE EXISTS(
      SELECT * FROM departments dep 
      WHERE em.department_id = dep.department_id
      AND location_id = 1700);

在上述SQL语句中，外层的SELECT语句返回的每一行数据都要由子查询来评估。如果EXISTS关键字中指定的条件为真，查询结果就包含这一行；反之改行被丢弃。因此，整个查询的结果取决于内层的子查询。

提示：由于EXISTS关键字的返回值取决于查询是否会返回行，而不取决于这些行的内容，因此对子查询来说，输出列表无关紧要，可以使用“*”代替。

⑤.合并查询：合并查询就是合并多个SELECT语句的结果。

（1）UNION:UNION运算符可以将多个查询结果集相加，形成一个结果集，其结果等同于集合运算中的并运算。即UNION运算符可以将第一个查询中的所有行与第二个查询中搞的所有行相加，并消除其中重复的行形成一个合集。

例如：第一个查询将选择所有LAST_NAME列以C或者S开头的雇员信息，第二个查询会选择所有LAST_NAME列以S或者T开头的雇员信息。其结果是所有LAST_NAME列以C或者S或者T开头的雇员信息均会被列出。

SELECT employee_id,last_name
FROM employees
WHERE last_name LIKE 'C%' OR last_name LIKE 'S%'
UNION
SELECT employee_id,last_name
FROM employees
WHERE last_name LIKE 'S%' OR last_name LIKE 'T%';

注意：UNION运算会将合集中的重复记录滤除，这是UNION运算和UNION ALL运算唯一不同的地方。

（2）UNION ALL：UNION ALL与UNION语句的工作方式基本相同，不同之处是UNION ALL操作符形成的结果集中包含有两个子结果集中重复的行。

SELECT employee_id,last_name
FROM employees
WHERE last_name LIKE 'C%' OR last_name LIKE 'S%'
UNION ALL
SELECT employee_id,last_name
FROM employees
WHERE last_name LIKE 'S%' OR last_name LIKE 'T%';

（3）MINUS：MINUS操作符用于获取两个结果集的差集。当使用该操作符时，只会显示在第一个结果集中存在的，并且在第二个结果集中不存在的数据，还会以第一列进行排序。

例如：显示工资高于2500但是岗位不是“MANAGER”的雇员信息。

SELECT ename,sal,job
FROM emp
WHERE sal>2500
MINUS
SELECT ename,sal,job
FROM emp
WHERE job ='MANAGER'

（4）INTERSECT：INTERSECT操作符用于获取两个结果集的交集。当使用该操作符时，只会显示同时存在于两个结果集中的数据，并且会以第一列进行排序。

例如：显示工资高于2500并且岗位为“MANAGER”的雇员信息。

SELECT ename,sal,job
FROM emp
WHERE sal>2500
INTERSECT
SELECT ename,sal,job
FROM emp
WHERE job ='MANAGER'

注意：在使用集合操作符编写复合查询时，其规则包括：

一.在构成复合查询的各个查询中，各SELECT语句指定的列必须在数量上和数据类型上相匹配；

二.不允许在构成复合查询的各个查询中规定ORDER BY子句；

三.不允许在BLOB、LONG这样的大数据类型对象上使用集合操作符。

10.数据操纵：SQL的数据操纵功能通过数据操纵语言（Data Manipulation Language,DML）实现，用于改变数据库中的数据，数据更新包括插入、删除和修改3种操作，对应INSERT、DELETE、UPDATE三条语句。

（1）INSERT语句：INSERT语句用于完成各种向数据表中插入数据的功能，既可根据对列赋值一次插入一条记录，也可根据SELECT查询子句获得的结果记录集批量插入指定数据表。

一般INSERT语句语法：

INSERT INTO [user.]table [@db_link] [(column1[,column2]...)]

VALUES(express1[,express2]...)

其中，table表示要插入的表名；db_link表示数据库链接名；column1，column2表示表的列名；VALUES表示给出要插入的值列表。

在向表的所有列添加数据时，也可以省略INSERT INTO子句后的列表清单，使用这种方法时，必须根据表中定义的列的顺序，为所有的列提供数据。可以使用DESC命令查看表中定义列的顺序。

批量INSERT：

SQL提供了一种成批添加数据的方法，即使用SELECT语句替换VALUES语句，由SELECT语句提供添加的数据，语法如下：

INSERT INTO [user.]table [@db_link] [(column1[,column2]...)] Subquery

其中，Subquery是子查询语句，可以是任何合法的SELECT语句，其所选列的个数和类型应该与前边的column相对应。

例如：从EMPLOYEES表提取department_id等于“IT”的雇员信息，并保存到IT_EMPLOYEES中。

INSERT INTO IT_EMPLOYEES
(employee_id,first_name,last_name,email,phone_number,job_id,salary,manager_id)
SELECT em.employee_id,em.first_name,em.last_name,em.email,em.phone_number,em.job_id,em.salary,em.manager_id
FROM employees em,departments dep
WHERE em.department_id = dep.department_id
AND dep.department_name = 'IT';

从上面的运行结果可以看出，使用INSERT和SELECT的组合语句一次性为新创建的表添加了5行记录。

注意：在使用INSERT和SELECT的组合语句成批添加数据时，INSERT INTO指定的列名可以与SELECT指定的列名不同，但是其数据类型必须相匹配，即SELECT返回的数据必须满足表中列的约束。

（2）.UPDATE语句

当需要修改表中一列或多列的值时，可以使用UPDATE语句。使用UPDATE语句可以指定要修改的列和修改后的新值，使用WHERE子句可以限定被修改的行。语法格式如下：

UPDATE table_name

SET{column1=express[,column2=express2]

(column1[,column2])=(select query)}

[WHERE condition]

其中，各选项含义如下：

*UPDATE子句用于指定要修改的表名称。需要后跟一个或多个要修改的表名称，这部分是必不可少的。

*SET子句用于设置要更新的列以及各列的新值。需要后跟一个或多个要修改的表列，这也是必不可少的。

*WHERE后跟更新限定条件，为可选项。

例如：使用UPDATE语句为所有程序员提高15%的薪水：如果未使用WHERE子句限定修改的行，则会更新整个表。

UPDATE employees
SET salary = salary * 1.15
WHERE job_id = 'IT_PROG';

同INSERT语句一样，可以使用SELECT语句的查询结果来实现更新数据。

例如：更新编号为104的雇员薪水，调整后的薪水为IT程序员薪水的平均值。

UPDATE employees
SET salary = 
(SELECT AVG(Ssalary) FROM employees WHERE job_id = 'IT_PROG')
WHERE employee_id=104;

注意：在使用SELECT语句提供新值时，必须保证SELECT语句返回单一的值，否则将会出现错误。

（3）.DELETE语句：

从数据库中删除记录可以使用DELETE语句来完成。如果UPDATE语句一样，用户也需要规定从中删除记录的表，以及限定表中哪些行将被删除。DELETE语法如下：

DELETE FROM table_name

[WHERE condition]
例如：从IT_EMPLOYEES表中删除一条记录。

DELETE FROM it_employees WHERE employee_id = 107

注意：建议使用DELETE语句一定要带上WHERE子句，否则将会把表中所有数据全部删除。

（4）.TRUNCATE语句：

如果用户确定要删除表中所有的记录，则建议使用TRUNCATE语句。使用TRUNCATE语句删除数据时，通常要比DELETE语句快很多。因为使用TRUNCATE语句删除数据时，它不会产生回滚信息，因为执行TRUNCATE操作也不能被撤销。

例如：

TRUNCATE TABLE it_employees;

在TRUNCATE语句中还可以使用关键字REUSE STORAGE，表示删除记录后仍然保存记录占用的空间；与此相反，也可以使用DROP STORAGE关键字，表示删除记录后立即回收记录占用的空间。TRUNCATE语句默认为使用DROP STORAGE关键字。

例如：使用REUSE STORAGE保留删除记录后的空间：

TRUNCATE TABLE it_employees REUSE STORAGE;

说明：若使用DELETE FROM TABLE_NAME语句，则整个表中的所有记录都将被删除，只剩下一个表格的定义，在这一点上，语句作用的效果和TRUNCATE TABLE TABLE_NAME的效果相同。但是DELETE语句可以用ROLLBACK来恢复数据，而TRUNCATE语句则不能。

11.数据控制：

（1）GRANT语句：SQL用GRANT语句向用户授予操作权限，GRANT语句的一般格式为：

GRANT<权限>[,<权限>]...

[ON<对象类型><对象名>] 对象类型有TABLE和DATABASE 对象名表示表名和数据库名的意思

TO<用户>[,<用户>]...

[WITH GRANT OPTION]

对于不同类型的操作对象有不同的操作权限，对属性列和视图的操作权限包括查询（SELECT）、插入（INSERT）、修改（UPDATE）、删除（DELETE）以及这4种权限的总和（ALLPRIVILEGES）。对基表的操作权限包括查询、插入、修改、删除、修改表（ALTER）和建立索引（INDEX）以及这六种权限的总和。对数据库可以有建立表（CREATETAB）的权限，该权限属于DBA，可由DBA授予普通用户，普通用户拥有此权限后可以建立基表，基表的所有者（Owner）拥有对该表的一切操作权限。

常见的操作权限如表所示：

接收权限的用户可以是一个或多个具体用户，也可以是PUBLIC，即全体用户。如果指定了WITH GRANT OPTION子句，则获得某种权限的用户还可以把这种权限再授予其他的用户。如果没有指定WITH GRANT OPTION子句，则获得某种权限的用户只能使用该权限，但不能传播该权限。

例如：把查询IT_EMPLOYEES表的权限授给用户User1。

GRANT SELECT 
ON TABLE IT_EMPLOYEES
TO User1;

例如：把对IT_EMPLOYEES表和JOBS表的全部操作权限授予用户User2和User3。

GRANT ALL PRIVILEGES
ON TABLE IT_EMPLOYEES,JOBS
TO User2,User3;

例如：把对表DEPARTMENT的查询权限授予所有用户。

GRANT SELECT
ON TABLE DEPARTMENT
TO PUBLIC;

例如：把查询IT_EMPLOYEES表和修改雇员编号的权限授给用户User4。

GRANT UPDATE(EMPLOYEE_ID),SELECT
ON TABLE IT_EMPLOYEES
TO User4;

这里对授予表中某一列的权限时，必须明确指出相应的列名。

例如：把对表DEPARTMENT的INSERT权限授予User5用户，并允许将此权限再授予其他用户。

GRANT INSERT
ON TABLE DEPARTMENT
TO User5
WITH GRANT OPTION;

例如：User5将此权限授予User6。

GRANT INSERT
ON TABLE DEPARTMENT
TO User6
WITH GRANT OPTION;  （也可以不写，不写就表示User6用户不能再授权给别的用户）

例如：DBA把在数据库DB_EMPLOYEES中建立表的权限授予用户User8。

GRANT CREATETAB8
ON DATABASE DB_EMPLOYEES  /*注意这里用的的是DATABASE 是数据库名*/
TO User8;

注意：授予关于DATABASE的权限必须与授予关于TABLE的权限分开，这是因为对象类型不同。

（2）.REVOKE语句：授予的权限可以由DBA或者其他授权者用REVOKE语句收回。一般格式为：

REVOKE <权限>[,<权限>]...

[ON <对象类型><对象名>]

FROM <用户>[,<用户>]...
例如：把用户User4修改雇员编号的权限收回。

REVOKE UPDATE(EMPLOYEE_ID)
ON TABLE IT_EMPLOYEES
FROM User4;

例如：收回所有用户对表DEPARTMENT的查询权限。

REVOKE SELECT
ON TABLE DEPARTMENT
FROM PUBLIC;

例如：把用户User5对DEPARTMENT表的INSERT权限收回。

REVOKE INSERT
ON TABLE DEPARTMENT
FROM User5;

如果User6将对DEPARTMENT表的INSERT权限授予了User6，而User6又将其授予了User7。执行上述SQL语句后，DBMS在回收User5对DEPARTMENT表的INSERT权限的同时，还会自动回收User6和User7对DEPARTMENT表的INSERT权限。也就是说，收回权限的操作会级联下去。但如果User6或User7还从其他用户处获得对DEPARTMENT表的INSERT权限，则他们仍具有此权限，系统只收回直接或间接从User5处获得的权限。

12.Oracle常用函数：

（1）字符类函数：字符类函数是专门用于字符处理的函数，处理的对象可以是字符窜常数，也可以是字符类型的列。常用的字符函数如下所示：

Ⅰ.ASCII(<c1>)：该函数用于返回c1第一个字母的ASCII码，其中c1是字符串。它的逆函数是CHR()。

例如：

SELECT ASCII ('A') BIG_A,ASCII('a') SMALL_A
FROM dual;

Ⅱ.CHR(<i>)：该函数用于求i对应的ASCII字符，其中i是一个数字。

例如：

SELECT CHR(65),CHR(97) FROM dual;

Ⅲ.CONCAT(c1,c2)：该函数将c2连接到c1的后面，如果c1为null，将返回c2；如果c2为null，则返回c1；如果c1、c2都为null，则返回null。其中，c1、c2均为字符串，它和操作符”||“返回的结果相同。

例如：

SELECT CONCAT('oracl ','11g') name FROM dual;

Ⅳ.INITCAP(c1)：该函数将c1中每个单词的第一个字母大写，其他字母小写返回。单词由空格、控制字符、标点符号限制。其中c1为字符串。

例如：

SELECT INITCAP('oracle universal installer') name FROM dual;

Ⅴ.LENGTH(c1)：该函数用于返回c1的长度，如果c1为null，那么将返回null值。其中c1为字符串。

例如：

SELECT LENGTH('Oracle 11g') FROM dual;

Ⅵ.LOWER(c1)：该函数用于返回c1的小写字符，经常出现在WHERE子串中。

例如：

SELECT LOWER(job_id) FROM JOBS
WHERE LOWER(job_id) LIKE 'it%';

SELECT LOWER('SS') FROM DUAL

Ⅶ.UPPER(c1)：该函数用于返回c1的大写字符。

例如：

SELECT UPPER('ss') FROM DUAL

Ⅷ.LTRIM(c1,c2)：该函数表示将c1中最左边的字符去掉，直至c1第一个字符不在c2中，如果没有c2，那么c1就不会改变。区分大小写。

例如：

SELECT LTRIM('pikaqiu','pi') FROM DUAL

Ⅸ.REPLACE(c1,c2[,c3])：该函数用c3代替出现在c1中的c2字符串，然后返回。

例如：

SELECT REPLACE('xxx2','2','haha') FROM dual

Ⅹ.SUBSTR(c1,<i>[,j])：该函数表示从c1的第i位开始返回长度为j的子字符串，如果j为空，则直到串的尾部。其中，c1为字符串，i、j为整数。

例如：

SELECT SUBSTR('Message',1,4) FROM dual

Ⅺ.TO_CHAR(number,c1)：该函数用来转化数字为字符。

例如：转换3000.45为3000.4500

SELECT TO_CHAR(3000.45,'9999.9999') FROM dual;

例如：转化3000.45为0003000.4500

SELECT TO_CHAR(3000.45,'0000000.0000') FROM dual;

注意：9代表一位数字，如果当前有数字则显示数字，否则不显示，但小数部分会强制显示。（显示0）

0强制显示该位，如果当前有数字，则显示数字，否则显示0。

Ⅻ.$增加美元符号显示

例如：将3000.45转换显示$0003000.4500

SELECT TO_CHAR(3000.45,'$0009999.9999') FROM dual;

L增加本地货币符号显示（RMB或￥）

SELECT TO_CHAR(3000.45,'L0009999.9999') FROM dual;

增加千分位符号1234567.77---->1,234,567.7700

SELECT TO_CHAR(1234567.77,'0,000,000.9999') FROM dual;

使用to_number('字符','格式')：将字符转换为数字，跟上面正好相反

例如：将0003000.4500字符转化为数字3000.45

SELECT TO_NUMBER('0003000.4500','0009999.0000') FROM dual;

例如：将$0003000.4500字符转化为数字3000.45

SELECT TO_NUMBER('$0003000.4500','$0009999.0000') FROM dual;

例如：将￥1,234,567.4500转化为数字：

SELECT TO_NUMBER('￥1,234,567.4500','L9,999,999.9999') FROM dual;

（2）.数字类函数：
Ⅰ.ROUND(number,decimal_places)：

number表示需要四舍五入的数值

decimal_places表示小数取几位，默认是0。

例如：

SELECT ROUND(45.926) FROM dual;  //返回46

SELECT ROUND(45.926,2) FROM dual;  //返回45.93

Ⅱ.TRUNC(n,[m])：该函数用于截取数字。m默认是0，即默认截去小数部分。如果m是正数，则将数字n截取至小数点后的第m位；如果m是负数，则将数字n截取至小数点的前m位。

例如：

SELECT TRUNC(45.926),TRUNC(45.926,2),TRUNC(450.926,-2) FROM dual;

Ⅲ.MOD(m,n)：该函数用于取得两个数字相除后的余数。如果数字n为0，则返回结果为m。

例如：

SELECT MOD(5,0) FROM dual;   //返回5

（3）.日期函数：

Ⅰ.MONTHS_BETWEEN(date2,date1) 日期靠后的在前

例如：计算2008年-4月-1日和2012年-12月-21日之间相差几个月

SELECT MONTHS_BETWEEN(TO_DATE('2012-12月-21','yyyy-month-dd'),TO_DATE('2008-4月-1','yyyy-month-dd')) FROM dual;

Ⅱ.ADD_MONTHS(date1,number)

例如：计算出2008-4月01 六个月后的日期

ECT ADD_MONTHS(TO_DATE('2008-4月-01','yyyy-month-dd'),2) FROM dual;

Ⅲ.NEXT_DAY(date,'day')：给出日期date和星期几day，计算下一个星期的日期。

例如：如果当前是2月28日，星期四，计算下礼拜一的日期。（注意：如果day写成星期五的话那只会就近返回明天的日期，而不是下个礼拜五的日期）

SELECT NEXT_DAY(TO_DATE('2013-2月-28','yyyy-month-dd'),'星期四') FROM dual;

Ⅳ.LAST_DAY(date1)：计算指定日期所在月份的最后一天的日期

例如：

SELECT LAST_DAY(TO_DATE('2013-1月-28','yyyy-month-dd')) FROM dual;

（4）.其他函数：

Ⅰ.NVL(expr1,expr2)：该函数用于将NULL转变为实际值。如果expr1是null，则返回expr2；如果expr1不是null，则返回expr1。参数expr1和expr2可以是任意数据类型，但二者数据类型必须匹配。

例如：

SELECT NVL(NULL,'XXC') FROM dual;

Ⅱ.NVL2(expr1,expr2,expr3)：该函数是Oracle9i新增加的函数，它也用于处理NULL。如果expr1不是null,则返回expr2；如果expr1是null，则返回expr3。参数expr1可以是任意数据类型，而expr2和expr3可以是除了LONG以外的任何数据类型。

例如：

SELECT NVL2(NULL,'XXC','PKQ') FROM dual;

Ⅲ.NULLIF(expr1,expr2)：该函数是Oracle9i新增加的函数，用于比较表达式expr1和expr2。如果两者相等，则返回NULL，否则返回expr1。

例如：

SELECT NULLIF(1,2) FROM dual;  //返回1

SELECT NULLIF(1,1) FROM dual;  //返回null

Ⅳ.COALESCE(expr1[,expr2][,expr3]...)：该函数是Oracle9i新增加的函数，用于返回表达式列表中第一个NOT NULL表达式的结果。

例如：

SELECT COALESCE('','','A') FROM dual; //返回A

SELECT COALESCE('S','','A') FROM dual; //返回S

13.PL/SQL语言及编程：SQL语言只是访问、操作数据库的语言，而并不是一种程序设计语言，因此不能用于程序开发。

（1）.PL/SQL的基本结构：

DECLARE

--声明一些变量、常量、用户定义的数据类型以及游标等（这一部分可选）

BEGIN

--主程序体，这里可以加入各种合法语句

EXCEPTION

--异常处理程序，当程序中出现错误时执行这一部分

END;--主程序体结束

从上面这个结构可以看出，它包含3个基本部分：声明部分（declarative section）、执行部分（executable section）和异常处理部分（exception section）。其中，只有执行部分是必须的，其他两个部分都是可选的。需要强调的是，该结构最后的分号是必须的。

（2）.PL/SQL的注释分为单行注释--和多行注释/**/。在PL/SQL中除了由引号引起来的字符串以外，PL/SQL不区分字母的大小写。

（3）.PL/SQL数据类型：数字类型变量存储整数或实数。它包含NUMBER、PLS_INTEGER和BINARY_INTEGER3种基本类型。其中，NUMBER类型的变量可以存储整数或浮点数，而BINARY_INTEGER或PLS_INTEGER类型的变量只存储整数。

NUMBER(P,S)是一种格式化的数字，其中P是精度，S是刻度范围。精度是数值中所有有效数字的个数，而刻度范围是小数点右边数字位的个数。精度和刻度范围都是可选的，但如果指定了刻度范围，就必须指定精度范围。

（4）.字符类型：字符类型变量用来存储字符串或者字符数据。其类型包括VARCHAR2、CHAR、LONG、NCHAR和NVARCHAR2（后两种类型在PL/SQL8.0以后才可以使用）①.VARCHAR2(MaxLength)：其中MaxLength是字符串的最大长度，必须在定义中给出，因为系统没有默认的最大长度。MaxLength最大可以是32767字节，这一点与数据库类型的VARCHAR2有所不同，数据库类型的VARCHAR2的最大长度是4000字节，所以一个长度大于4000字节的PL/SQL类型VARCHAR2变量不可以赋值给数据库中的一个VARCHAR2变量，而只能赋值给LONG类型的数据库变量。

说明：数据库变量和PL/SQL语言的变量是两个不同的概念。在创建表时的变量都是数据库变量。如CREATE TABLE a(name VARCHAR2(30));这里的name就是数据库变量，VARCHAR2就是数据库变量类型。

②.CHAR(MaxLength)：其中MaxLength也是最大长度，以字节为单位，最大为32767字节。与VARCHAR2不同，MaxLength可以不指定，默认为1.如果赋给CHAR类型的值不足MaxLength，则在其后面用空格补全，这也是不同于VARCHAR2的地方。注意，数据库类型中的CHAR只有2000字节，所以如果PL/SQL中CHAR类型的变量长度大于2000字节，则不能赋给数据库中的CHAR。

③.LONG类型变量是一个可变的字符串，最大长度是32760字节。LONG变量与VARCHAR2变量类似。数据库类型的LONG长度最大可达2GB，所以几乎任何字符串变量都可以赋值给它。

提示：NCHAR和NVARCHAR2类型是PL/SQL8.0以后才加入的类型，它们的长度指定根据各国字符集的不同而不同。

④.日起类型：日期类型中只有一种类型---DATE，用来存储日期和时间信息，包括世纪、年、月、天、小时、分钟和秒。DATE变量的存储空间是7个字节，每个部分占用一个字节。

⑤.布尔类型：布尔类型中的唯一类型是BOOLEAN，主要用于控制程序流程。一个布尔类型变量的值可以是TRUE、FALSE或NULL。

⑥.type定义的数据类型：自定义数据类型。语法格式如下：

type<数据类型名>is<数据类型>;

在Oracle中允许用户定义两种数据类型，它们是RECORD（记录类型）和TABLE（表类型）。

例如：使用type定义teacher_record记录变量。

TYPE teacher_record IS RECORD
(
     TID NUMBER(5) NOT NULL :=0,
     NAME VARCHAR2(50),
     TITLE VARCHAR2(50),
     SEX CHAR(1)
);

该RECORD定义后，在以后的使用中就可以定义基于teacher_record的记录变量。

例如：

ateacher teacher_record;

引用这个记录变量时要指明内部变量，如ateacher.tid或ateacher.name。

另外，PL./SQL还提供了%TYPE和%ROWTYPE两种特殊的变量，用于声明与表的列相匹配的变量和用户定义数据类型，%TYPE表示单属性的数据类型，%ROWTYPE表示整个属性列表的结构，即元祖的类型。

例如：

TYPE teacher_record IS RECORD
(
     TID TEACHERS.TID%TYPE NOT NULL :=0,   //表名.列名%TYPE
     NAME TEACHERS.NAME%TYPE,
     TITLE TEACHERS.TITLE%TYPE,
     SEX TEACHERS.SEX%TYPE
);

例如：也可以定义一个与表TEACHERS的结构类型一致的记录变量。

teacher_record TEACHERS%ROWTYPE

（5）.PL/SQL变量和常量：

①.定义常量：语法格式如下

<常量名>constant<数据类型>:=<值>;
其中，关键字constant表示在定义常量。常量一旦定义，在以后的使用中其值将不再改变。一些固定的大小为了防止有人改变，最好定义成常量。

例如：下面语句定义了一个及格线的常量Pass_Score，它的类型为整形，值为60。

Pass_Score CONSTANT INTEGER:=60;

②.定义变量：语法格式如下

<变量名><数据类型>[(宽度):=<初始值>];

可见，变量定义时没有关键字，但要指定数据类型，宽度和初始值可选。

例如：

address VARCHAR2(30);

③.变量初始化：PL/SQL定义了一个为初始化变量应该存放的内容，被赋值为NULL。换句话说，NULL可以被默认地赋值给任何未经过初始化的变量。这是PL/SQL的一个独到之处。许多其他程序设计语言没有定义未初始化变量的取值。

（6）.PL/SQL语句控制结构：主要控制语句如下表。

①.选择结构：

（1）.IF语句格式如下：

IF{条件表达式1}THEN

{语句序列1;}

[ELSIF(条件表达式2)THEN 注意这里是ELSIF

{语句序列2;}]

[ELSE

{语句序列3:}]

END IF;

可以把这个语法分为3种情况来理解：

（一）.IF...THEN语句：当IF后面的判断为真时执行THEN后面的语句，否则跳过这一控制语句。

例如：

IF NO=98020 THEN   --此处NO值通过游标得到，游标后面会讲
  INSERT INTO temp_table VALUES(NAME,BIRTHDAY);
  END IF;

（二）.IF...THEN..ELSE语句

例如：

IF NO=98020 THEN   --此处NO值通过游标得到，游标后面会讲
  INSERT INTO found_table VALUES(NAME,BIRTHDAY);
ELSE 
  INSERT INTO notfound_table VALUES(NAME,BIRTHDAY);
END IF;

（三）.IF...THEN...ELSIF语句

例如：

IF score>90 THEN
  Score := score -5 ;
ELSIF score<60 THEN
  Score := score +5 ;
END IF;

（2）.CASE语句：CASE结果是Oracle9i后新增加的结构。

CASE 检测表达式

WHEN 表达式1 THEN 语句序列1

WHEN 表达式2 THEN 语句序列2

...

WHEN 表达式n THEN 语句序列n

[ELSE 其他语句序列]

END;

其中，CASE语句中的ELSE子句是可选的。如果检测表达式的值与下面任何一个表达式的值都不匹配时，PL/SQL会产生预定义错误CASE_NOT_FOUND。

注意：CASE语句中表达式1到表达式n的类型必须同检测表达式的类型相符。

例如：

DECLARE 
  v_score NUMBER(2):= 90;
  v_grade VARCHAR2(10);
BEGIN
  v_grade := CASE  v_score
  WHEN 100 THEN '满分'
  WHEN 90 THEN '优秀'
  WHEN 60 THEN '及格'
  ELSE '输入有误'
  END;
  DBMS_OUTPUT.put_line(v_grade);
END;

（3）.NULL结构：在IF结构中，只有相关的条件为真时，相应的语句才执行，如果条件为FALSE或NULL时，语句都不会指定。特别是当条件为NULL时，常常会对程序的流程和输出有比较大的影响。

例一：

DECLARE
  V_NUMBER1 NUMBER;
  V_NUMBER2 NUMBER;
  V_Result VARCHAR2(7);
BEGIN
  IF V_NUMBER1<V_NUMBER2 THEN
    V_Result := 'Yes';
  ELSE
    V_Result := 'No';
  END IF;
END;

例二：

DECLARE
  V_NUMBER1 NUMBER;
  V_NUMBER2 NUMBER;
  V_Result VARCHAR2(7);
BEGIN
  IF V_NUMBER1>V_NUMBER2 THEN
    V_Result := 'No';
  ELSE
    V_Result := 'Yes';
  END IF;
END;

从直观上看，这两段代码的功能完全一样，只不过把判断的顺序颠倒了一下而已。但是如果V_NUMBER1的值是1，V_NUMBER2的值是NULL。那么对于例一来说(1<NULL)返回NULL，则不执行IF里的语句，执行ELSE的，结果返回NO，对于例二来说(1>NULL)返回NULL，则不执行IF里的语句，执行ELSE的，结果返回YES。

要想解决这个问题，需要在程序块中添加NULL检查。如下所示：

DECLARE
  V_NUMBER1 NUMBER;
  V_NUMBER2 NUMBER;
  V_Result VARCHAR2(7);
BEGIN
  IF V_NUMBER1 IS NULL OR V_NUMBER2 IS NULL THEN
    V_Result := 'Unknown';
  ELSIF V_NUMBER1<V_NUMBER2 THEN
    V_Result := 'Yes';
  ELSE
    V_Result := 'No';
  END IF;
END;

（4）.循环结构：

①.LOOP...EXIT...END语句

例如：

control_var:0;
LOOP
    IF control_var>5 THEN
      EXIT;
    END IF;
    control_var := control_var +1;
END LOOP;

②.LOOP...EXIT WHEN...END

例如：

control_var:0;
LOOP
    EXIT WHEN control_var>5
    control_var := control_var +1;
END LOOP;

③.WHILE...LOOP...END

例如：

control_var:0;
WHILE control_var<=5 LOOP
  control_var : = control_var+1;
END LOOP;

④.FOR...IN...LOOP...END语句：

例如：

for i in reverse 0..5 loop  --i从0到5进行循环 每次加1 所以这个for循环是不能规定步长的  reverse表示反转 
    insert into test values('pkx',16);   --如果这里写NULL  表示它什么也不做
end loop;

⑤.GOTO语句：

这是个无条件转向语句。当执行GOTO语句时，控制程序会立即转到由标签标示的语句。其中，label是在PL/SQL中定义的标号。标签是用双箭头括号《》括起来的。

例如：

...--程序其他部分
<<goto_mark>>  --定义了一个转向标签goto_mark
..--程序其他部分
IF no>999 THEN
  GOTO goto_mark;  --如果条件成立则转向goto_mark继续执行
...--程序其他部分

declare
   i int:=1;
begin
   loop
      dbms_output.put_line('输出i='||i);
   if i=10 then
      goto end_xxc;
   end if;
      i:=i+1;
   end loop;
      dbms_output.put_line('循环结束1');
   <<end_xxc>>
      dbms_output.put_line('循环结束2');   --这句话如果没有会出错
end;

游标：

PL/SQL包含隐式游标和显示游标等两种游标类型，其中隐式游标用于处理SELECT INTO和DML语句，而显示游标则专门用于处理SELECT语句返回的多行数据。

（1）.显示游标：为了处理SELECT语句返回的多行数据，开发人员可以使用显示游标，使用显示游标包括定义游标、打开游标、提取数据和关闭游标四个阶段。

①.定义游标：在使用显示游标之前，必须首先在定义部分定义游标。定义游标用于指定游标所对应的SELECT语句，语法如下：

CURSOR cursor_name IS select_statement;

cursor_name 表示用于指定的游标名称；select_statement表示用于指定游标所对应的SELECT语句。

②.打开游标：当打开游标时，Oracle会执行游标所对应的SELECT语句，并将SELECT语句的结果暂时存放到结果集中。语法如下：

OPEN cursor_name;

该游标名必须是在定义部分已经被定义的游标。

③.提取数据：在Oracle9i之前，使用FETCH语句每次只能提取一行数据；从Oracle9i开始，通过使用FETCH...BULK COLLECT INTO语句，每次可以提取多行数据。语法如下：

语法一：FETCH cursor_name INTO variable1,variable2,...;

语法二：FETCH cursor_name BULK COLLECT INTO collect1,collect2,...[LIMIT rows];

variable用于指定接收游标数据的变量；collect用于指定接收游标结果的集合变量。注意，当使用语法一时，必须要使用循环语句处理结果集的所有数据。

④.关闭游标：在提取并处理了结果集的所有数据之后，就可以关闭游标并释放其结果集了。语法如下：

CLOSE cursor_name;

（2）.显示游标属性：显示游标属性用于返回显示游标的执行信息，这些属性包括%ISOPEN,%FOUND,%NOTFOUND和%ROWCOUNT。当使用显示游标属性时，必须要在显示游标属性之前带有显示游标名作为前缀（游标名.属性名）。

①.%ISOPEN：该属性用于确定游标是否已经打开。如果游标已经打开，则返回值为TRUE；反之，则返回值为FALSE。示例如下：

IF c1%ISOPEN THEN --如果游标打开，则执行相应操作

...

ELSE--如果游标未打开，则打开游标

OPEN c1;

END IF;

②.%FOUND：该属性用于检查是否从结果集中提取到了数据。如果提取到数据，则返回值为TRUE；反之，则返回FALSE。示例如下：

LOOP

FETCH ci INTO var1,var2;--提取数据到变量中

IF c1%FOUND THEN --如果提取到数据，则进行处理

...

ELSE--如果未提取到数据，则循环退出

EXIT;

END IF;

END LOOP;

③.%NOTFOUND：该属性与%FOUND属性恰好相反。如果提取到数据，则值返回为FALSE；如果没有提取到数据，则返回值为TRUE。示例如下：

LOOP

FETCH c1 INTO var1,var2;--提取数据到变量中

EXIT WHEN c1%NOTFOUND;

...

END LOOP;
④.%ROWCOUNT：该属性用于返回到当前行（hang）为止已经提取到的实际行数。示例如下：

LOOP

FETCH c1 INTO my_ename,my_deptno;

IF c1%ROWCOUNT > 10 THEN

...

END IF;

...

END LOOP;

（3）.显示游标使用实例：

事例一：在显示游标中使用FETCH...INTO语句显示部门10的所有雇员名。

DECLARE
  CURSOR emp_cursor IS
      SELECT ename,sal FROM emp WHERE deptno = 10;
  v_ename emp.ename%TYPE;
  v_sal emp.sal%TYPE;
  BEGIN 
    OPEN emp_cursor;
    LOOP
      FETCH emp_cursor INTO v_ename,v_sal;
      EXIT WHEN emp_cursor%NOTFOUND;--这句话不跟紧LOOP的原因，游标在指向最后一行的时候，%NOTFOUND还并不满足，执行FETCH的时候指针才向下移动一行，直到指向最后一行的再下面才满足。
      DBMS_OUTPUT.PUT_LINE(v_ename||':  '||v_sal);
    END LOOP;  
    CLOSE emp_cursor;
END;

注意：FETCH语句每次执行一次，游标向后移动一行，直到结束（游标只能逐个向后移动，而不能跳跃移动或是向前移动）。

事例二：在显示游标中，使用FETCH...BULK COOLECT INTO语句提取所有数据。显示部门10的所有雇员名。

DECLARE
  CURSOR emp_cursor IS SELECT ename FROM emp WHERE deptno=10;
  TYPE ename_table_type IS TABLE OF VARCHAR2(10);  --自定义一个数组类型，每个元素类型是VARCHAR2(10)
  ename_table ename_table_type;
BEGIN
  OPEN emp_cursor;
  FETCH emp_cursor BULK COLLECT INTO ename_table;
  FOR i IN 1.. ename_table.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(ename_table(i));
  END LOOP;
  CLOSE emp_cursor;
END;

提示：（1）TABLE OF VARCHAR2(10)表示定义一个联合数组，每个元素的类型是VARCHAR2(10)

（2）Type xxc IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(20);

表示定义一个联合数组：

TABLE OF VARCHAR2(10)表示定义一个联合数组，每个元素的类型是VARCHAR2(10)

INDEX BY VARCHAR2(20)表示数组下标的类型。

（3）TYPE xxc_record IS RECORD

(

NEW_Code VARCHAR2(10);

NEW_Name VARCHAR2(10);

Old_Name VARCHAR2(10);

);

TYPE xxc_date IS TABLE OFxxc_record INDEX BY VARCHAR2(20);

TABLE OF xxc_record表示数组里元素的类型，

INDEX BY VARCHAR2(20);表示数组的下标类型。

事例三：在显示游标中使用FETCH..BULK COLLECT INTO..LIMIT语句提取部分数据

当使用FETCH..BULK COLLECT INTO语句提取数据时，默认情况下会提取结果集的所有数据。如果结果集含有大量数据，并且使用VARRAY集合变量接收数据，那么可能需要限制每次提取的行数。下面以每次提取5行数据为例，使用LIMIT子句限制提取行的方法。示例如下：

DECLARE
  TYPE name_array_type IS VARRAY(5) OF VARCHAR2(10);--声明长度为5的数组，每个元素类型为VARCHAR2(10)
  name_array name_array_type;
  CURSOR emp_cursor IS SELECT ename FROM emp;
  rows INT:=5;--声明一个变量，表示每次往游标里读取几条数据
  v_count INT:=0;--声明一个变量，表示当前第几行
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor BULK COLLECT INTO name_array LIMIT rows;
    DBMS_OUTPUT.PUT('雇员名：');
    FOR i IN 1..(emp_cursor%ROWCOUNT-v_count) LOOP--emp_cursor%ROWCOUNT-v_count 表示当前游标所指位置减去上一次游标所指为止  这样做是为了防止总条数不是5的倍数 1..5
      DBMS_OUTPUT.PUT_LINE(name_array(i)||'   ');
    END LOOP;
    DBMS_OUTPUT.NEW_LINE;
    v_count:=emp_cursor%ROWCOUNT;
    EXIT WHEN emp_cursor%NOTFOUND;
  END LOOP;
  CLOSE emp_cursor;
END;

示例四：使用游标属性

当使用显示游标时，为了取得显示游标的执行信息，需要使用显示游标属性。下面以使用显示游标属性%ISOPEN和%ROWCOUNT为例，在PL/SQL块中使用显示游标属性的方法。示例如下：

DECLARE
  CURSOR emp_cursor IS SELECT ename FROM emp WHERE deptno=10;
  TYPE ename_table_type IS TABLE OF VARCHAR2(10);
  ename_table ename_table_type;
BEGIN
  IF NOT emp_cursor %ISOPEN THEN --如果游标未打开，则打开游标
    OPEN emp_cursor;
  END IF;
  FETCH emp_cursor BULK COLLECT INTO ename_table;
  DBMS_OUTPUT.PUT_LINE('提取的总计行数：'||emp_cursor%ROWCOUNT);--打印总计行数  这里没有进行循环，因为只需要知道行数就行了
  CLOSE emp_cursor;
END;

事例五：基于游标定义记录变量

使用%ROWTYPE属性不仅可以基于表和视图定义记录变量，也可以基于游标定义记录变量。当基于游标定义记录变量时，记录成员名实际就是SELECT语句的列名或别名。为了简化显示游标的数据处理，建议开发人缘使用记录变量存放游标数据。下面以显示所有雇员名极其工资为例，说明在处理显示游标数据时使用记录变量的方法。示例如下：

DECLARE
  CURSOR emp_cursor IS SELECT ename e,sal s FROM emp;
  emp_record emp_cursor%ROWTYPE;
BEGIN
  OPEN emp_cursor;
  LOOP
       FETCH emp_cursor INTO emp_record;
       EXIT WHEN emp_cursor %NOTFOUND;
       DBMS_OUTPUT.PUT_LINE('雇员名：'||emp_record.e||',雇员工资：'||emp_record.s);--可以用别名，但是用了别名不能用全名
  END LOOP;
  CLOSE emp_cursor;
END;

（4）参数游标：参数游标是指带有参数的游标。在定义了参数游标之后，当使用不同参数值多次打开游标时，可以生成不同的结果集。定义参数游标的语法如下：

CURSOR cursor_name(parameter_name datatype) IS select_statement;

当定义参数游标时，需要指定参数名极其数据类型。下面以显示特定部门所有雇员名为例。示例如下：

DECLARE
  CURSOR emp_cursor(no NUMBER) IS SELECT ename FROM emp WHERE deptno=no;--参数里的类型后不要写限定的大小
  v_ename emp.ename%TYPE;
BEGIN
  OPEN emp_cursor(10);
  LOOP
    FETCH emp_cursor INTO v_ename;
    EXIT WHEN emp_cursor %NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_ename);
  END LOOP;
  CLOSE emp_cursor;
END;

注意：定义参数游标时，游标参数只能指定数据类型，而不能指定长度。另外，定义参数游标时，一定要在游标子查询的WHERE子句中引用该参数，否则失去了定义参数游标的意义。

（5）使用游标更新或删除数据：通过使用显示游标，不仅可以一行一行地处理SELECT语句的结果，而且也可以更新或删除当前游标行的数据。注意，如果要通过游标更新或删除数据，在定义游标时必须要带有FOR UPDATE子句，语法如下：

CURSOR cursor_name(parameter_name datatype) IS select_statement FOR UPDATE [OF column_reference] [NOWAIT];

如上所示，FOR UPDATE子句用于在游标结果集数据上加行共享锁，以防止其他用户在相应行上执行DML操作；当SELECT语句引用到多张表时，使用OF子句可以确定哪些表要枷锁，如果没有OF子句，则会在SELECT语句所引用的全部表上枷锁；NOWAIT子句用于指定不等待锁。在提取了游标数据之后，为了更新或删除当前游标行数据，必须在UPDATE或DELETE语句中引用WHERE CURRENT OF子句。语法如下：

UPDATE table_name SET column=..WHERE CURRENT OF cursor_name;

DELETE table_name WHERE CURRENT OF cursor_name;

1.使用游标更新数据：下面以给工资低于2000的雇员增加100元工资为例，说明使用显示游标更新数据的方法。示例如下：

DECLARE
  CURSOR emp_cursor IS SELECT ename,sal FROM emp FOR UPDATE;
  v_ename emp.ename%TYPE;
  v_oldsal emp.sal%TYPE;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO v_ename,v_oldsal;
    EXIT WHEN emp_cursor %NOTFOUND;
    IF v_oldsal<2000 THEN
      UPDATE emp SET sal = sal + 100 WHERE CURRENT OF emp_cursor;--表示更新当前行的工资
    END IF;
  END LOOP;
  CLOSE emp_cursor;
END;

2.使用游标删除数据：下面以解雇部门30的所有雇员为例，说明使用显示游标删除数据的方法。示例如下：

DECLARE
  CURSOR emp_cursor IS SELECT deptno FROM emp  FOR UPDATE;
  v_deptno emp.deptno%TYPE;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO v_deptno;
    EXIT WHEN emp_cursor %NOTFOUND;
    IF v_deptno=30 THEN
      DELETE FROM emp WHERE CURRENT OF emp_cursor;
    END IF;
  END LOOP;
  CLOSE emp_cursor;
END;

2.使用OF子句在特定表上加行共享锁：吐过游标子查询涉及到多张表，那么在默认情况下会在所有修改表行上加行共享锁。为了只在特定表上加行共享锁，需要在FOR UPDATE子句后带有OF子句。下面以显示所有雇员姓名、工资、部门名，并更新部门30的所有雇员工资为例，说明在显示游标中使用OF子句给特定表加锁的方法。示例如下：

DECLARE
  CURSOR emp_cursor IS SELECT ename,sal,dname,emp.deptno FROM emp,dept 
  WHERE emp.deptno=dept.deptno FOR UPDATE OF emp.deptno;
  emp_record emp_cursor%ROWTYPE;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO emp_record;
    EXIT WHEN emp_cursor %NOTFOUND;
    IF emp_record.deptno=30 THEN
      UPDATE emp SET sal = sal + 100 WHERE CURRENT OF emp_cursor;
    END IF;
    DBMS_OUTPUT.PUT_LINE('雇员名：'||emp_record.ename||'，工资：'||emp_record.sal||',部门名:'||emp_record.dname);
  END LOOP;
  CLOSE emp_cursor;
END;

存储过程：

过程用于执行特定操作。语法如下：

CREATE [OR REPLACE] PROCEDURE procedure_name

(

argument1 [mode1] datatype1,

argument2 [mode2] datatype2,...

)

IS [AS]

PL/SQL Block;

如上所示，procedure_name用于指定过程名称：argument1,argument2等则用于指定过程的参数；IS或AS用于开始一个PL/SQL块。注意，当指定参数数据类型时，不能指定其长度。另外，当建立过程时，既可以指定输入参数(IN)，也可以指定输出参数（OUT）及输入输出参数（IN OUT）。通过在过程中使用输入参数，可以将应用环境的数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境。定义子程序参数时，如果不指定参数模式，则默认为输入参数；如果要定义输出参数，那么需要指定OUT关键字；如果要定义输入输出参数，则需要指定IN OUT关键字。

1.建立过程：不带任何参数

建立过程时，过程既可以带有参数，也可以不带任何参数。下面以建立用于输出当前系统日期和时间的过程为例。

CREATE OR REPLACE PROCEDURE out_time
IS
BEGIN 
  DBMS_OUTPUT.PUT_LINE(systimestamp);
END;

建立了过程之后，调用过程有两种方法：（1）在SQL窗口执行CALL OUT_TIME();括号是必须的 （2）在命令行窗口执行EXECUTE OUT_TIME或EXEC OUT_TIME都可。在命令行里存储过程后面的括号可加可不加。

2.建立过程：带有IN参数

下面以建立为雇员插入数据的过程ADD_EMPLOYEE为例，说明建立带有输入参数的过程的方法。

CREATE OR REPLACE PROCEDURE add_employee
(
eno NUMBER,
name VARCHAR2,
sal NUMBER,
job VARCHAR2 DEFAULT 'CLERK',
dno NUMBER
)
IS 
  e_integrity EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_integrity,-2291);
BEGIN
  INSERT INTO emp(empno,ename,sal,job,deptno) VALUES (eno,name,sal,job,dno);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    RAISE_APPLICATION_ERROR(-20000,'雇员号不能重复');
  WHEN e_integrity THEN
    RAISE_APPLICATION_ERROR(-20001,'部门号不存在');
END;

输入重复的雇员号：

call add_employee(7369,'xxc',11111,'MANAGER',10);

输入不存在的部门号：

call add_employee(7169,'xxc',11111,'MANAGER',15);

数据满足约束规则：

call add_employee(1111,'xxc',11111,'MANAGER',10);

3.建立过程：带有OUT参数
当定义输出参数时，必须要提供OUT关键字。下面以建立用于输出雇员名及其工资的过程为例，说明建立带有OUT参数的过程的方法。

CREATE OR REPLACE PROCEDURE query_employee
(
       eno NUMBER,
       name OUT VARCHAR2,
       salary OUT NUMBER
)
IS
BEGIN
  SELECT ename,sal INTO name,salary FROM emp WHERE empno=eno;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20000,'该雇员不存在');
END;

在命令行中调用有输出参数的存储过程

var name VARCHAR2(10)
var salary NUMBER
exec query_employee(7788,:name,:salary)

4.建立过程：带有IN OUT参数

当使用这种参数时，在调用过程之前需要通过变量给这种参数传递数据，在调用结束之后，Oracle会通过该变量将过程结果传递给应用程序。下面以计算两个数值相除结果的过程compute为例，说明在过程中使用IN OUT参数的方法。

CREATE OR REPLACE PROCEDURE compute
(
num1 IN OUT NUMBER,
num2 IN OUT NUMBER 
)--这里声明的参数是指过程接收的参数
IS
  --这里设置的参数是指提供给过程用的参数
  v1 NUMBER;
  v2 NUMBER;
BEGIN
  v1:=num1/num2;
  v2:=MOD(num1,num2);
  num1 := v1;
  num2 := v2;
END;

如上所示，在过程compute中，num1，num2为输入输出参数。当在应用程序中调用该过程时，必须要提供两个变量临时存放数值，在运算结束之后将两数相处的商和余数分别存放到这两个变量中。

调用此过程：

var n1 NUMBER
var n2 NUMBER
exec :n1:=100
exec :n2:=30
exec compute(:n1,:n2)
PRINT n1 n2

5.删除过程：用户可以使用DROP PROCEDURE命令来删除该过程。

DROP PROCEDURE OUT_TIME

函数：

函数用于返回特定数据。语法如下所示：

CREATE [OR REPLACE] FUNCTION function_name

(

argument1 [mode1] datatype1,

argument2 [mode2] datatype2,

...

)RETURN datatype

IS|AS

PL/SQL Blockl;

如上所示，function_name用于指定函数名称；argument1,argument2等则用于指定函数的参数，注意，当指定参数数据时，不能指定其长度；RETURN子句用于指定函数返回值的数据类型；IS或AS用于开始一个PL/SQL块。注意，当建立函数时，在函数头部必须要带有RETURN子句，在函数体内至少要包含一条RETURN语句。另外，当建立函数时，既可以指定输入参数(IN)，也可以指定输出参数（OUT）及输入输出参数（IN OUT）。

1.建立函数：不带任何参数

下面以建立用于显示当前数据库用户名的函数为例。

CREATE OR REPLACE FUNCTION get_user
RETURN VARCHAR2
IS
  v_user VARCHAR2(100);
BEGIN
  SELECT username INTO v_user FROM user_users;
  RETURN v_user;
END;

调用该函数：因为该函数有返回值，所以它只能作为表达式的一部分来调用。

1.使用变量接收函数返回值

var v1 VARCHAR2(100)
exec :v1:=get_user
PRINT v1

2.在SQL语句中直接调用函数

SELECT get_user FROM dual;

3.使用包DBMS_OUTPUT调用函数

set serveroutput on
exec dbms_output.put_line('当前数据用户:'||get_user)

2.建立函数：带有IN参数
下面以建立用于返回雇员工资的函数为例。

CREATE OR REPLACE FUNCTION get_sal(name IN VARCHAR2)
RETURN NUMBER
AS
  v_sal emp.sal%TYPE;
BEGIN
  SELECT sal INTO v_sal FROM emp WHERE UPPER(ename)=UPPER(name);
  RETURN v_sal;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    raise_application_error(-20000,'该雇员不存在');
END;

调用函数

var sal NUMBER
exec :sal:=get_sal('scott')

3.建立函数：带有OUT参数

下面以建立用于返回雇员所在部门名和岗位的函数为例。

CREATE OR REPLACE FUNCTION get_info
(
name VARCHAR2,
title OUT VARCHAR2
)RETURN VARCHAR2
AS
  deptname dept.dname%TYPE;
BEGIN
  SELECT a.job,b.dname INTO title,deptname FROM emp a,dept b WHERE a.deptno=b.deptno 
  AND UPPER(a.ename)=UPPER(name);
  RETURN deptname;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20000,'该雇员不存在');
END;

调用函数：

var job varchar2(20)
var dname varchar2(20)
exec :dname:=get_info('scott',:job)

4.建立函数：带有IN OUT参数

使用这种参数时，在调用函数之前需要通过变量给该种参数传递数据，在调用结束之后Oracle会将函数的部分结果通过该变量传递给应用程序。

下面以计算两个数值相处的结果的函数result为例。

CREATE OR REPLACE FUNCTION result
(
  num1 IN NUMBER,
  num2 IN OUT NUMBER
)
RETURN NUMBER
AS
   v_result NUMBER(6);
   v_remainder NUMBER;
BEGIN
  v_result:=num1/num2;
  v_remainder:=MOD(num1,num2);
  num2:=v_remainder;
  RETURN v_result;
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    RAISE_APPLICATION_ERROR(-20000,'不能除0');
END;

调用函数：

var result1 NUMBER
var result2 NUMBER
exec :result2:=30
exec :result1:= result(100,:result2)

5.删除函数

DROP FUNCTION function_name;

触发器：

1.语句级触发器：语句级触发器对每个DML语句执行一次。不管这条SQL语句影响了几行数据。

CREATE [OR REPLACE] TRIGGER trigger_name

timing

event1[OR event2 OR event3]

ON table_name

WHEN condition

BEGIN

trigger_body

END;

timing:表示触发时机，可以使用after或before 分别表示DML语句执行之后或之前

even1:表示触发事件，例如insert/update/delete

when:表示执行触发器的条件

trigger_body:触发器的执行体

例如：

CREATE OR REPLACE TRIGGER depttrigger
BEFORE 
INSERT or UPDATE
ON dept
BEGIN
  DBMS_OUTPUT.PUT_LINE('语句级触发器被调用');
END;

测试：

insert into dept values(60,'xxc','HZ');

2.行级触发器：行级触发器对DML语句影响的每个行执行一次。

CREATE [OR REPLACE] TRIGGER trigger_name

timing

event1[OR event2 OR event3]

ON table_name

[REFERENCING OLD AS old | NEW AS new]

FOR EACH ROW

WHEN condition

BEGIN

trigger_body

END;

timing:表示触发时机，可以使用after或before 分别表示DML语句执行之后或之前

even1:表示触发事件，例如insert/update/delete

when:表示执行触发器的条件

trigger_body:触发器的执行体

FOR EACH ROW：对表中的每行操作都会触发这个触发器。影响几行触发几次。

REFERENCEING：用来声明触发器替换前后的引导前缀名的。默认替换前的前缀名为OLD，替换后的前缀名为NEW，也可以自己声明替换前后的变量前缀规则。

例如：

CREATE OR REPLACE TRIGGER depttriggerrow
BEFORE 
INSERT or UPDATE or DELETE
ON dept
FOR EACH ROW
BEGIN
  DBMS_OUTPUT.PUT_LINE('语句级触发器被调用');
END;

测试：

DELETE FROM dept WHERE DEPTNO=50 OR DEPTNO=60

例如：创建一个触发器，完成注册用户的自动设置登录信息，在登录的同时往登陆表中插入登录信息。

用户表

CREATE TABLE emp2
(
empid NUMBER(6),
empname VARCHAR2(20),
username VARCHAR2(20),
psw VARCHAR2(25),
tel VARCHAR2(25)
)

登陆表

CREATE TABLE login
(
username VARCHAR2(20),
psw VARCHAR2(25)
)

创建一个触发器，完成注册用户的自动设置登录信息。

CREATE OR REPLACE TRIGGER login_trigger
AFTER
INSERT
ON emp2
FOR EACH ROW
BEGIN
  INSERT INTO login(username,psw) values(:new.username,:new.psw);--new表示插入到emp2表中数据的当前行，用new.列名的方式取值
END;

测试：

INSERT INTO emp2 VALUES(0001,'XXC','XXC','111','188');

创建一个触发器，完成删除用户，并且删除登陆表中相应的信息。

CREATE OR REPLACE TRIGGER logout_trigger
AFTER
DELETE
ON emp2
FOR EACH ROW
BEGIN
  DELETE FROM login WHERE username = :old.username;
END;

测试：

DELETE FROM emp2 WHERE username = 'XXC';

替代触发器：只能被用于视图。

CREATE [OR REPLACE] TRIGGER trigger_name

INSTEAD OF

timing

event1[OR event2 OR event3]

ON VIEW_NAME

[REFERENCING OLD AS old | NEW AS new]

FOR EACH ROW

WHEN condition

BEGIN

trigger_body

END;

例如：

创建视图

CREATE OR REPLACE VIEW depeview
AS
SELECT deptno,dname,loc FROM dept

触发器：

CREATE OR REPLACE TRIGGER deptview_trigger
INSTEAD OF
INSERT
ON depeview
FOR EACH ROW
BEGIN
  DBMS_OUTPUT.PUT_LINE('视图触发器调用');
END;

测试：

INSERT INTO deptview VALUES(99,'XXC','BB');