oracle数据库应用技术

Index

1.Oracle12C简介与安装

1.发展历程

​ Oracle是第一个支持关系型数据库理论的产品。至今，Oracle已经成为关系型数据库产品一款最优秀的产品。

​ Oracle先后经历Oracle 1.0， Oracle 2.0，Oracle 3.0、 Oracle 5.0、 Oracle 6.0、 Oracle7.0、 Oracle8i、 Oracle9i、 Oracle10g、 Oracle11g、 Oracle12c版本的变迁。

2.Oracle12c的新特性

• 云端数据库整合的全新多租户架构

• 数据自动优化

• 深度安全防护

• 简化大数据分析

• 高效的数据库管理

3.安装包

链接：https://pan.baidu.com/s/1jWQLlFvcMUUmMppDldaxwA?pwd=orcl
提取码：orcl

• 虽然但是，不建议把oracle安装在自己的电脑上，因为这个数据库的服务占用内存很大，一般建议在服务器或者虚拟机上安装

4.安装步骤

（1）运行Setup.exe启动安装程序

（2）配置安全更新

（3）安装选项

（4）系统类

（5）Oracle主目录用户

（6）典型配置

• 这里的口令就是你登录oracle管理员用户的密码

（7）检查系统条件

（8）概要

（9）安装

• 问题1：

5.卸载步骤

（1）停用Oracle的所有系统服务

（2）通过“开始”菜单运行“Universal Installer”

---

---

---

---

---

---

(3)删除自动存储管理（ASM），在DOS命令行中执行命令：oracle-delete-asnsid+asm。

(4)在“开始”|“运行”框中输入regedit命令，进入注册表菜单窗口删除注册表中与Oracle相关的内容。

(5) 删除相应的环境变量：打开“环境变量”窗口，在“系统变量”列表中找到ORACEL_HOME选项，删除即可。如果系统变量中的CLASSPATH和PATH变量中也存在Oracle设置，那么也一并删除。

(6) 删除“开始”|“程序”中所有Oracle的组和图标。

(7) 删除所有与Oracle相关的目录。

(8) 删除相应目录后重启计算机。

6.oracle默认用户

SYS：该用户被默认创建并授予DBA角色，它是Oracle数据库中权限最大的管理员账号。

SYSTEM：被默认创建并授予DBA角色权限仅次于SYS。该用户创建和管理数据库中可显示管理信息的表或视图，以及被Oracle数据库应用和工具使用的各种数据库对象。

SYSMAN：该用户是企业管理的超级管理员账号，该账号能够创建和修改其他管理员账号，同时也能管理数据库实例。

DBSNMP：是Oracle数据库中用于智能代理（Intelligent Agent）的用户，用来监控和管理数据库相关性能。如果停止该用户，则无法提取相关的数据库信息。

SYSDBA是Oracle中具有最高级别的特殊权限，可以执行启动数据库、关闭数据库、建立数据库备份和恢复数据库，以及其他的数据库管理操作。

SYSOPER是Oracle数据库的另一个特权，可以执行启动数据库和关闭数据库，不能建立数据库，也不能执行不完全恢复，可以进行一些基本的操作而不能查看用户数据，不具备DBA角色的任何特权。

2.方案

Oracle通过方案来组织和维护表、视图、索引等数据库对象。

方案：是一系列逻辑数据结构或对象的集合。一个方案只能够被一个数据库用户所拥有，并且方案的名称与这个用户的名称相同。

Oracle数据库中的每一个用户都拥有一个唯一的方案，他所创建的所有方案对象都保存在自己的方案中

方案对象

}表 Tables

}视图 Views

}索引 Indexes

}触发器 Triggers

}同义词 Synonyms

}序列 Sequences

}函数、过程和包 Functions, Procedures , Packages

}簇或聚集 Clusters

}数据库链 Database links

}扩展程序库 External procedure libraries

---

•操作符 Operators

•维 Dimensions

•索引组织表 Index-organized tables

•索引类型 Indextypes

•Java classes, Java resources, Java sources

•实体化视图 Materialized views

•实体化视图日志 Materialized view logs

•对象表 Object tables

•对象类型 Object types

•对象视图 Object views

非方案对象

}表空间 Tablespaces

}用户 Users

}角色 Roles

}回退段 Rollback segments

}目录 Directories

对象、表空间和数据文件之间的关系

3.SQL种类

•数据定义语言 Data Definition Language Statements（DDL）

•数据操纵语言 Data Manipulation Language（DML） Statements

•事务控制 Transaction Control Statements

•会话控制 Session Control Statements

•系统控制 System Control Statements

•**嵌入SQL **Embedded SQL Statements

DDL(数据定义语言)

• 当用户执行DDL语句的时候，在每一条DDL语句执行前后，Oracle都将提交当前的事务。

4.表

1.表名的约定

1.必须是以字母开头

2.1–30个字符长度

3.仅能包含A–Z, a–z, 0–9, _，$和 #

4.在同一个用户下表名不能重复。

5.不能是Oracle保留字

2.数据类型

3.创建表

CREATE TABLE TABLENAME(
	NAME CHAR(20) NOT NULL,
    SEX INT 
)
1
2
3
4

• 创建者必须有创建表的权限

• 使用子查询创建表–创建表的同时插入行

CREATE TABLE DEPT 
AS
	SELECT EMPNO,ENAME,SAL*12 ANNSAL,HIREDATE
	FROM EMP
	WHERE DEPTNO = 30;
1
2
3
4
5

4.更改表

ALTER TABLE DEPT
	ADD (JOB VARCHAR2(9));
1
2

…

• 更改表名称

RENAME DEPT TO DEPARTMENT;
1

• 截断表
  • 删除表的所有数据
  • 释放被表使用的存储空间
  • 截断操作不能回退

TRUNCATE YABLE DEPARTMENT;
1

5.创建和管理约束

• 定义约束

CREATE TABLE EMP(
	EMPNO NUMBER(4),
    ENAME VARCHAR2(10),
    ...
    DEPTNO NUMBER(7,2) NOT NULL,
    CONSTRAINT EMP_EMPNO_PK PRIMARY KEY(EMPNO)
);
1
2
3
4
5
6
7

• 列约束与表约束

-- 列约束
CREATE TABLE EMP(
	EMPNO NUMBER(4),
    ENAME VARCHAR2(10) NOT NULL,
    JOB VARCHAR2(9),
    MMGR NUMBER(4),
    ......
    DEPTNO NUMBER(7,2) NOT NULL
);
--表约束
CREATE TABLE DEPT(
	DEPTNO NUMBER(2),
    DNAME VARCHAE2(14),
    LOC VARCHAR2(13),
    CONSTRAINT DEPT_DNAME_UK UNIQUE(DNAME)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

• 主键约束既可以定义在列或者表上

 CREATE TABLE   dept(
    	deptno 	  NUMBER(2),
  	dname	  VARCHAR2(14),
  	loc	  VARCHAR2(13),
  	CONSTRAINT dept_dname_uk UNIQUE (dname),
     --定义主键约束
  	CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno) );

1
2
3
4
5
6
7
8

• 外键约束

SQL> CREATE TABLE emp(
  2  	empno 	NUMBER(4),
  3	ename	VARCHAR2(10) NOT NULL,
  4	job	VARCHAR2(9),
  5	mgr	NUMBER(4),
  6	hiredate	DATE,
  7	sal	NUMBER(7,2),
  8 	comm	NUMBER(7,2),
  9	deptno	NUMBER(7,2) NOT NULL,
    -- 定义外键约束
 10	CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)
 11			REFERENCES dept (deptno));

1
2
3
4
5
6
7
8
9
10
11
12
13

• CHECK约束：定义表的每一行必须满足的条件

..., deptno	NUMBER(2),
      CONSTRAINT emp_deptno_ck  
            CHECK (DEPTNO BETWEEN 10 AND 99),...

1
2
3
4

5.视图

• 视图：视图可看作一个或多个表的子集，是特殊的数据库对象，用于限制对表中指定的表列或数据行的访问。

• 简单视图与复杂视图

6.序列

• 创建序列

CREATE SEQUENCE dept_sequence
  	INCREMENT BY 1
  	START WITH 91
  	MAXVALUE 100
  	NOCACHE
 	NOCYCLE;

1
2
3
4
5
6
7

• 查看序列

 SELECT	sequence_name, min_value, max_value, 
    		increment_by, last_number
    FROM	user_sequences 
    where sequence_name='DEPT_SEQUENCE';

1
2
3
4
5

• 序列的使用

1.NEXTVAL返回下一个有效的序列值。

2.CURRVAL得到当前的序列值。

3.第一次使用序列时必须首先调用NEXTVAL，否则CURRVAL不会包含任何值。

---

 INSERT INTO	dept(deptno, dname, loc)
    VALUES		(dept_sequence.NEXTVAL, 
  	 	       'MARKETING', 'SAN DIEGO');

1
2
3
4

• 查看当前的序列值

SELECT	 dept_sequence.CURRVAL
    FROM	dual;
     -- dual是一个虚拟表
1
2
3

• 更改序列

 ALTER SEQUENCE dept_sequence
  	  INCREMENT BY 1
  	  MAXVALUE 999999
      NOCACHE
  	  NOCYCLE;
1
2
3
4
5

7.DML

• 数据操纵语言，缩写为DML，用于查询和操作方案对象中的数据，它不隐式地提交当前事务！
  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • CALL
  • EXPLAIN PLAN
  • LOCK TABLE
  • MERGE

1.INSERT

-- 第一种插入方式
INSERT INTO DEPT(DEPTNO,DNAME,LOC)
VALUES (50,'DEVELOPMENT','DETROIT');
-- 第二种插入方式
INSERT INTO EMP
VALUES (70,'FINANCE',NULL);
-- 插入多行数据,注意不能使用VALUE子句ORACLEVALUE子句不支持插入多行
INSERT INTO MANAGERS(ID,NAME,SALARY,HIREDATE)
	SELECT EMPNO,ENAME,SAL,HIREDATE
	FROM EMP
	WHERE JOB = 'MANAGER';
1
2
3
4
5
6
7
8
9
10
11

• SYSDATE函数值，返回当前的日期和时间，后面会再次提到时间函数

2.UPDATE

UPDATE EMP
	SET DEPTNO = 20
	-- 如果不添加限制条件就是更新整张表的DEPTNO
	WHERE DEPNO = 7782;
1
2
3
4

3.DELETE

DELETE FROM DEPT WHERE DEPTNO = 50;
1

4.SELECT

• 查询所有的列和行

SELECT * FROM DEPT;
1

• 查询指定的列和所有行

SELECT DEPTNO,LOC
	FROM DEPT;
1
2

• 算术操作符（在数值型数据上使用+ - * /）

SELECT ENAME,SAL,SAL+300
FROM EMP;
1
2

• 定义列的别名,如果别名包含空格或者特定的字符或需要区分大小写时，需要使用双引号

SELECT ENAME AS NAME,SAL SALARY
FROM EMP;
1
2

• 连接符(||)

SELECT ENAME ||' '||'is a'||' '||JOB
AS "Employee Details"
FROM EMP;
1
2
3

• DISTINCT关键字

使用DISTINCT关键字消除所选择的重复行，只返回一行

SELECT DISTNCT DEPTNO
FROM EMP;
1
2

• 条件（WHERE）比较符

SELECT ENAME,SAL
FROM EMP
WHERE SAL BETWEEN 1000 AND 1500;
1
2
3

• IN比较符

SELECT EMPNO,ENAME,SAL,MGR
FROM EMP
WHERE MGR IN(7902,7566,7788);
1
2
3

• LIKE比较符
  • Oracle支持两种匹配符号[ % ]和[ __ ]，符号[ % ]可以匹配0个或者多个字符，符号[ __ ]可匹配任何单一字符

SELECT ENAME
FROM EMP
WHERE ENAME LIKE '_A%';
-- 使用ESCAPE定义查找[ % ]与[ _ ]字符
SELECT ENAME FROM EMP WHERE ENAME LIKE '%A\_B%' ESCAPE '\'
1
2
3
4
5

• IS NULL

SELECT ENAME,MGR
FROM EMP
WHERE MGR IS NULL;
1
2
3

• 逻辑操作符

-- AND
SELECT EMPNO,ENAME,JOB,SAL
FROM EMP
WHERE SAL >= 1000
AND JON = 'CLERK';
-- OR
SELECT EMPNO,ENAME,JOB,SAL
FROM EMP
WHERE SAL >= 1100
OR JOB = 'CLERK';
-- NOT
SELECT ENAME,JOB
FROM EMP
WHERE JOB NOT IN ('CLERK','MANAGER','ANALYST');
1
2
3
4
5
6
7
8
9
10
11
12
13
14

5.优先原则

SELECT *
FROM EMP
WHERE JOB='SALESMAN'
--优先描述OR
OR JOB='PRESIDENT'
AND SAL>1500;

SELECT *
FROM EMP
WHERE (JOB='SALESMAN'
      OR JOB='PRESIDENT')
      --文字描述为：查询工作岗位为销售员并且工资大于1500的员工信息，或者工作岗位是负责人并且工资大于1500的员工信息
      AND SAL>1500;
1
2
3
4
5
6
7
8
9
10
11
12
13

6.排序语句

SELECT ENAME,JOB,DEPTNO,HIREDATE
FROM EMP
ORDER BY HIREDATE DESC;
-- ASC从小到大排序（default）
-- DESC 从大到小排序
-- ORDER BY 子句在SELECT语句的最后面
1
2
3
4
5
6

8.SELECT进阶单行函数

• 单行函数：针对具体的数据类型

1.字符

• 常用函数列表

SELECT ENAME,CONCAT(ENAME,JOB),LENGTH(ENAME),INSTR(ENAME,'a')
FROM EMP
WHERE SUBSTR(JOB,1,5) = 'SALES';
1
2
3

2.数值

--ROUND
SELECT ROUND(45.923,2),ROUND(45.923,0),ROUND(45,923,-1)
FROM DUAL;
--TRUNC
SELECT TRUNC(45.923,2),TRUNC(45.923),TRUNC(45.923,-1)
FROM DUAL;
--MOD
SELECT ENAME,SAL,COMM,MOD(SAL,COMM)
FROM EMP
WHERE JOB='SALESMAN';
1
2
3
4
5
6
7
8
9
10

3.日期

• 日期类型oracle使用数字来存储日期信息，数字的整数部分表示与Julian calendar（罗马侵略历，公历）相距的天数，由公园前4712年1月1日开始，而小数部分代表时、分和秒
• 默认格式为DD-MON-YY,即中文的05-10月-22，英文的05-Oct-22
• SYSDATE函数返回当前日期和时间
• DUAL是Oracl内置的虚拟表，只有一行一列，可以用来查询时间之类的

SELECT SYSDATE FROM DUAL;
SELECT TO_CHAR(SYSDATE,YYYY-MM-DD HH24:MI:SS) FROM DUAL;
1
2

4.日期运算

• 不允许日期加上日期

SELECT ENAME,(SYSDATE-HIREDATE)/7 WEEKS
FROM EMP
WHERE DEPTNO = 10;
1
2
3

5.转换函数

1.隐式转换

• 隐式转换–赋值

• 隐式转换–表达式运算

2.显示转换

3.函数TO_CHAR（日期）

• 函数TO_CHAR的第二个参数的转换格式（日期）：

• 对于中文缩写来说和缩写是一致的

4.函数TO_CHAR(数值)

• 函数TO_CHAR的第二个参数的转换格式（数值）

5.函数TO_NUMBER

6.函数TO_DATE

7.空值置换函数:NVL函数

1.NVL(expr1,expr2)

2.NVL2(expr1,expr2,expr3)

• 函数可以使用date，character和number数据类型率
• 表达式的值和替换值类型必须匹配

9.条件表达式

1.CASE表达式

• 这里的else相当于是default语句

2.DECODE函数

• 注意里面的值只能带单引号，包括中文也是只能带上单引号，而不是双引号
• 最后一个字段相当于是default语句,这里的开始就是判断条件，相当于switch语句包裹的，后面的才是case语句

---

tips:单行函数能够在任何一个层次嵌套

10.连接查询

• 在进行连接表时，应该避免笛卡尔积

• 种类：相等连接、不相等连接、外连接、自连接

• 语法：

1.相等连接

select emp.empno,emp.ename,emp.deptno
from emp,dept
where emp.deptno=dept.deptno;
1
2
3

2.不相等连接

select e.ename,e.sal,s.grade
from emp e,salgrade s
where e.sal
between s.losal and s.hisal;
1
2
3
4

3.外连接

• 相当于是一个表对另外一个表的补充

-- （+）符号表示添加右边自己所没有的数据，左边没有就会使用null代替
select table.column,table.column
from table1,table2
where table.column(+)=table2.column;
-- （+）在右边与上面的含义相反
select table.column,table.column
from table1,table2
where table.column=table2.column(+);
1
2
3
4
5
6
7
8

例子：

select e.ename,d.depyno,d.dname
from emp e,dept d
where e.deptno(+)=d.deptno
order by e.deptno;
1
2
3
4

---

• 完全外部连接查询：

select ename,dname
from emp full outer join dept
on dept.deptno = emp.deptno;
1
2
3

4.自连接

SELECT worker.ename||' works for '||manager.ename
    FROM 	emp worker, emp manager
    WHERE 	worker.mgr = manager.empno;
-- 两根竖线表示连接符号
1
2
3
4

5.递归查询（树查询）

SELECT lpad(ename,length(ename)+(level-1)*3,'-') employee,level
FROM emp
START WITH ename = 'KING'
CONNECT BY PRIOR empno = mgr;
--lpad函数表示往左填充
1
2
3
4
5

SELECT empno, ename,level
FROM emp
START WITH ename = 'JONES'
CONNECT BY PRIOR empno = mgr;

1
2
3
4
5

SELECT empno, ename,level
FROM emp
START WITH ename = 'JONES'
CONNECT BY empno = PRIOR  mgr;

1
2
3
4
5

11.集合操作符

12.分组函数

1.语法

2.常见的分组函数

3.案例

---

4.数据分组

select deptno ,avg(sal)
from emp
group by deptno;
1
2
3

select deptno,job,sum(sal)
from emp
froup by deptno,job;
1
2
3

限制选择组

select deptno ,max(sal)
from emp
group by deptno
having max(sal)>2900;
1
2
3
4

组函数嵌套

select max(avg(sal))
from emp
group by deptno;
1
2
3

错误使用分组函数案例

• 使用分组函数必须使用group by子句

-- 这是错误代码示范
select deptno ,count(ename)
from emp;
1
2
3

• 在where子句中不能直接使用组函数

• 在having子句可以直接使用组函数

select deptno,avg(sal)
from emp
where avg(sal)>2000
group by deptno;
1
2
3
4

13.子查询

select ename
from emp
where sal>(select sal from emo where empno=7566);
1
2
3

1.单行子查询

select ename,job
from emp
where job = (select job from emp where empno=7369)
and sal >(select sal  from emp where empno = 7876);
1
2
3
4

2.多行子查询

-- any
select empno,ename,job
from emp
where sal<any (select sal from emp where job = 'clerk') and job<>'clerk';

-- all
select empno,ename,job
from emp
where sal>all(select avg(sal) from emp group by deptno);
1
2
3
4
5
6
7
8
9

3.多行子查询exists

SELECT  e.empno, e.ename
FROM    emp e
WHERE   EXISTS (SELECT ‘X’
		FROM  dept d
		WHERE e.deptno=d.deptno
		and d.loc= 'NEW YORK');
----------------------
SELECT  e.empno, e.ename
FROM    emp e
WHERE   e.deptno in (SELECT d.deptno
		FROM  dept d
		WHERE  d.loc= 'NEW YORK');

1
2
3
4
5
6
7
8
9
10
11
12
13

4.多行子查询in

SELECT  e.empno, e.ename
FROM    emp e
WHERE   e.deptno in (SELECT d.deptno
		FROM  dept d
		WHERE  d.loc= 'NEW YORK' 
				or d.loc= 'CHICAGO');

---------------
SELECT  e.empno, e.ename,d.loc
FROM    emp e,
   (SELECT deptno,loc FROM  dept 
    WHERE  loc= ‘NEW YORK’ or loc= 'CHICAGO') d
WHERE   e.deptno =d.deptno;

1
2
3
4
5
6
7
8
9
10
11
12
13
14

5.嵌套查询

SELECT	select_list
FROM	(SELECT	select_list
		FROM		table);
WHERE	expr
		 	
------------------
SELECT  e.empno, e.ename,d.loc
FROM    emp e,
   (SELECT deptno,loc FROM  dept 
    WHERE  loc= ‘NEW YORK’ or loc= 'CHICAGO') d
WHERE   e.deptno =d.deptno;
--------------
SELECT  e.empno, e.ename,d.loc
FROM    emp e, dept d
WHERE   e.deptno =d.deptno 
	and (d.loc= ‘NEW YORK’ or d.loc= 'CHICAGO') ;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

14.事务控制

1.事务控制命令：

• commit
• rollback
• savepoint
• set transaction
• set constraint

2.数据库事务控制

• 结束事务前数据状态

• 提交事务后数据状态

• 撤销事务后数据状态

3.savepoint标识

---

update emp
set deptno = 10
where empno = 7782;
------1 row updated.
commit;
------Commit complete.
delete from employee;
------14 rows deleted.
rollback;
------Rollback complete.
update emp
set deptno = 10
where deptno = 7782;
savepoint update_done;
------Savepoint created.
insert into emp
select * from emp_temp;
rollback to update_done;
------Rollback complete.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

15.PL/SQL编程基础

1.概述

2.PL/SQL字符集

• 合法字符

• 算术运算符

• 关系运算符

• 其他符号

3.PL/SQL块结构

declare
	v_n number(20);
begin
	v_n := 65/0;
exception
	when zero_divide then
	dbms_output.put_line('divided by zero');
end;

1
2
3
4
5
6
7
8
9

4.PL/SQL块块种类

5.PL/SQL变量

6.PL/SQL中的数据类型

7.声明PL/SQL变量

declare
	v_ename varchar2(10);
	v_sal number(6,2);
	c_tax_rate constant number(3,2) :=5.5;
	v_hirdate date;
	v_valid boolean not null default false;
1
2
3
4
5
6

8.命名规则

9.变量赋值

v_hiredate := '31-DEC-98';
v_ename := '张三';
1
2

10.变量的初始化和关键字

11.变量作用范围

12.基本标量数据类型

• varchar2

• number

• date

• boolean

13.标量变量声明

v_job				VARCHAR2(9) DEFAULT ‘CLERK';
v_count			BINARY_INTEGER := 0;
v_total_sal		NUMBER(9,2) := 0;
v_orderdate		DATE := SYSDATE + 7;
c_tax_rate		CONSTANT NUMBER(3,2) := 8.25;
v_valid			BOOLEAN NOT NULL := TRUE;

1
2
3
4
5
6
7

14.引用类型

---

• 用法：

...
  v_ename					emp.ename%TYPE;
  v_balance					NUMBER(7,2);
	  v_min_balance			v_balance%TYPE := 10;
	  v_emp 				emp%rowtype;
...		

1
2
3
4
5
6
7

15.记录类型&记录表类型&大对象数据类型(LOB)

• 略

---

• 记录表类型的语法格式如下：

---

16.在PL/SQL中的SQL函数

• 转换函数

• 操作符（逻辑操作符、算术操作符、连接、括号指数算符（**））与SQL相同

---

17.控制语句

1.分支结构

1.if逻辑结构

if v_ename='zhangsan' then
	v_mgr:=22;
end if;
1
2
3

2.case表达式

CASE grade
WHEN 'A' THEN  dbms_output.put_line('Excellent');
WHEN 'B' THEN  dbms_output.put_line('Very Good');
WHEN 'C' THEN  dbms_output.put_line('Good');
WHEN 'D' THEN  dbms_output.put_line('Fair');
WHEN 'E' THEN  dbms_output.put_line('Poor');
ELSE dbms_output.put_line('No such grade');
END CASE;
-- if 块：--------------------------------
IF grade = 'A' THEN dbms_output.put_line('Excellent');
ELSIF grade = 'B' THEN dbms_output.put_line('Very Good');
ELSIF grade = 'C' THEN dbms_output.put_line('Good');
ELSIF grade = 'D' THEN dbms_output. put_line('Fair');
ELSIF grade = 'E' THEN dbms_output.put_line('Poor');
ELSE
  dbms_output.put_line('No such grade');
END IF;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2.循环控制结构

1.Basic Loop

• 语法

DECLARE
  v_num NUMBER(2):=#  --用户任意给定的一个整数
  v_pro NUMBER(20):=1;  
  i NUMBER(2):=1;  --控制循环结束的循环变量
BEGIN
  IF v_num=0 THEN
    v_pro:=1;
  ELSE
    LOOP
      v_pro:=v_pro*i;  --计算给定整数的阶乘
      i:=i+1;
      EXIT WHEN i>v_num;
    END LOOP;
  END IF;
  dbms_output.put_line('num:'||v_num||'  factorial:'||v_pro);
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

2.for loop

• 语法

DECLARE
  v_num NUMBER(2):=#
  v_pro NUMBER(20):=1;
BEGIN
  IF v_num=0 THEN
    v_pro:=1;
  ELSE
    FOR i IN 1..v_num LOOP
      v_pro:=v_pro*i;      
    END LOOP;
  END IF;
  dbms_output.put_line('num:'||v_num||'  factorial:'||v_pro);
  --dbms_output.put('num:'||v_num);
  --dbms_output.put_line('  factorial:'||v_pro);
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

3.while loop

• 语法

DECLARE
  v_num NUMBER(2):=#
  v_pro NUMBER(20):=1;
  i NUMBER(2):=1;
BEGIN
  IF v_num=0 THEN
    v_pro:=1;
  ELSE
    WHILE i<=v_num LOOP
      v_pro:=v_pro*i; 
      i:=i+1;
    END LOOP;
  END IF;
  dbms_output.put_line('num:'||v_num||'  factorial:'||v_pro);
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

4.Loops和便签Labels嵌套

...
BEGIN
  <<Outer_loop>> 
  LOOP
    v_counter := v_counter+1;
  EXIT WHEN v_counter>10;
    <<Inner_loop>> 
    LOOP
      ...
      EXIT Outer_loop WHEN total_done = 'YES';
      -- Leave both loops
      EXIT WHEN inner_done = 'YES';
      -- Leave inner loop only
      ...
    END LOOP Inner_loop;
    ...
  END LOOP Outer_loop;
END;      

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

18.游标(Cursors)

1.隐式游标

 
set serveroutput on;
DECLARE
  v_empno  NUMBER := 7788;
  rows_deleted VARCHAR2(100);
BEGIN
  DELETE   FROM emp
  WHERE    empno = v_empno;
  rows_deleted := SQL%ROWCOUNT||' rows deleted.';
  dbms_output.put_line(rows_deleted);
END;

1
2
3
4
5
6
7
8
9
10
11
12

2.显式游标

• 处理流程

1.声明游标

• 语法

DECLARE
  CURSOR c1 IS 
    SELECT empno, ename
	    FROM   emp;

  CURSOR c2 IS
    SELECT *
    FROM   dept
    WHERE  deptno = 10;
BEGIN
  ...

1
2
3
4
5
6
7
8
9
10
11
12

2.打开游标

• 语法

3.从游标中提取数据

• 语法

DECLARE
CURSOR c1 IS SELECT * FROM emp;
emp_rec emp%ROWTYPE;  --定义一个和表结构完全一致的记录变量
BEGIN
   OPEN c1;
   FETCH c1 INTO emp_rec;
   dbms_output.put_line('姓名是:'||emp_rec.ename|| '工作是:'||emp_rec.job|| '工资是:'||emp_rec.sal);
   FETCH c1 INTO emp_rec;
   dbms_output.put_line('姓名是:'||emp_rec.ename||'工作是:'||emp_rec.job|| '工资是:'||emp_rec.sal);
   CLOSE c1;
END; 

1
2
3
4
5
6
7
8
9
10
11
12

4.关闭游标

• 语法

• 在一次循环遍历之后会默认自动关闭游标

3.显式游标的属性

1.ISOPEN属性

IF NOT c1%ISOPEN THEN
	OPEN c1;
END IF;
LOOP
  FETCH c1...

1
2
3
4
5
6

2.%NOTFOUND和%ROWCOUNT属性

4.游标提取控制

1.basic loop

DECLARE
   CURSOR emp_cursor IS 
	SELECT ename,sal FROM emp WHERE deptno=10;
   emp_record emp%ROWTYPE;  
BEGIN
   OPEN emp_cursor ;
   LOOP
      FETCH emp_cursor INTO emp_record.ename,emp_record.sal;
      EXIT WHEN emp_cursor%NOTFOUND;
      dbms_output.put_line('ename: '||emp_record.ename||'  sal:'||emp_record.sal);
   END LOOP;
   dbms_output.put_line('row count:'||emp_cursor%rowcount);
   CLOSE emp_cursor;
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2.while loop

DECLARE
   CURSOR emp_cursor IS 
	SELECT ename,sal FROM emp WHERE deptno=10;
   emp_record emp%ROWTYPE;  
BEGIN
   OPEN emp_cursor ;
   FETCH emp_cursor INTO emp_record.ename,emp_record.sal;
   while emp_cursor%FOUND LOOP
      dbms_output.put_line('ename: '||emp_record.ename||'  sal:'||emp_record.sal);
      FETCH emp_cursor INTO emp_record.ename,emp_record.sal;
   END LOOP;
   dbms_output.put_line('row count:'||emp_cursor%rowcount);
   CLOSE emp_cursor;
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

3.for loop

• 语法

DECLARE
   CURSOR emp_cursor IS 
	SELECT ename,sal FROM scott.emp WHERE deptno=10;
BEGIN
   FOR emp_record IN emp_cursor LOOP

      dbms_output.put_line('ename: '||emp_record.ename||'  sal:'||emp_record.sal);

   END LOOP;

/* 该命令无效，因为FOR循环结束后游标自动关闭
dbms_output.put_line('row count:'||emp_cursor%rowcount); */

END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

DECLARE
   CURSOR cur IS SELECT * FROM scott.emp ORDER BY sal DESC;
BEGIN
   FOR rec IN cur  LOOP
      IF cur%ROWCOUNT<=5 THEN
                dbms_output.put_line('ename:'||rec.ename||’sal:'||rec.sal);
      ELSE 
        EXIT;
      END IF;
   END LOOP;
END;

1
2
3
4
5
6
7
8
9
10
11
12

5.带参数的游标

19.异常处理

1.异常处理方法

EXCEPTION
  WHEN exception1 [OR exception2 . . .] THEN
    statement1;
    statement2;
    . . .
  [WHEN exception3 [OR exception4 . . .] THEN
    statement1;
    statement2;
    . . .]
  [WHEN OTHERS THEN
    statement1;
    statement2;
    . . .]

1
2
3
4
5
6
7
8
9
10
11
12
13
14

2.捕获异常规则

3.异常的类型

• 系统预定义的异常
• 系统非预定义的异常
• 用户自定义异常（显示触发，其余为隐式触发）

---

1.系统预定义异常

BEGIN  SELECT ... COMMIT;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    statement1; 
    statement2; 						         
  WHEN TOO_MANY_ROWS THEN
    statement1;
  WHEN OTHERS THEN
    statement1; 
    statement2; 
    statement3;
END;	
---------------------
DECLARE
  v_empRecord  emp%ROWTYPE; 
  v_empNo      emp.empno%TYPE; 
BEGIN  
  SELECT *  INTO v_empRecord  FROM emp;
  --SELECT *  INTO v_empRecord  FROM emp  WHERE empno = 12345789 ;
  --SELECT ename  INTO v_empNo  FROM emp  WHERE empno = 7369;
  EXCEPTION
    WHEN TOO_MANY_ROWS THEN
      dbms_output.put_line('TOO_MANY_ROWS EXCEPTION'); 
    WHEN NO_DATA_FOUND THEN
      dbms_output.put_line('NO_DATA_FOUND EXCEPTION'); 
    WHEN OTHERS THEN
      dbms_output.put_line('OTHERS EXCEPTION');
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

2.非预定义异常

CREATE OR REPLACE PROCEDURE insert_emp 
(no IN emp.empno%TYPE,   name IN emp.ename%TYPE DEFAULT NULL,
job IN emp.job%TYPE DEFAULT 'SALESMAN',
mgr IN emp.mgr%TYPE DEFAULT 7369,
hiredate emp.hiredate%TYPE DEFAULT SYSDATE,
salary emp.sal%TYPE DEFAULT 800,
comm emp.comm%TYPE DEFAULT NULL,
deptno emp.deptno%TYPE DEFAULT 10
)IS
e_integrity EXCEPTION;
PRAGMA EXCEPTION_INIT (e_integrity,-2291);
BEGIN
     INSERT INTO emp VALUES(no,name,job,mgr,hiredate,salary,comm,deptno);
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        dbms_output.put_line('该员工已经存在！');
    WHEN e_integrity THEN
        dbms_output.put_line('部门编号填写错误！');
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

3.用户自定义异常

[DECLARE]
  e_amount_remaining EXCEPTION;
. . .
BEGIN
. . .
  RAISE e_amount_remaining;
. . .
EXCEPTION
  WHEN e_amount_remaining  THEN
    :g_message := 'There is still an amount
	             in stock.';
. . .
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14

DECLARE  
  ex_null EXCEPTION; --系统非预定义异常的定义和关联
  PRAGMA EXCEPTION_INIT(ex_null,-01400);  
  ex_insert EXCEPTION; --用户自定义异常的定义  
  eno scott.emp.empno%TYPE:=&no; --定义程序块变量
  e_sal scott.emp.sal%TYPE:=&salary;
BEGIN
  IF e_sal>10000 THEN
    RAISE ex_insert;  --用户自定义异常的触发
  END IF;
  INSERT INTO scott.emp(empno,sal) VALUES(eno,e_sal);
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX  THEN   --系统预定义异常的捕获和处理
      dbms_output.put_line('该员工已经存在！');
    WHEN ex_null THEN   --系统非预定义异常的捕获和处理
      dbms_output.put_line('职工编号不能为空！');
    WHEN ex_insert THEN   --用户自定义异常的捕获和处理
      dbms_output.put_line('员工的工资不能超过10000！');
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

4.捕获异常中的函数

DECLARE
  v_error_code      NUMBER;
  v_error_message   VARCHAR2(255);
BEGIN
...
EXCEPTION
...
  WHEN OTHERS THEN
    ROLLBACK;
    v_error_code := SQLCODE ;
    v_error_message := SQLERRM ;
    INSERT INTO errors VALUES(v_error_code,
				   v_error_message);
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

5.传播异常

20.触发器

• 匿名PL/SQL块回顾

1.概念

2.创建语法

3.触发器的激发顺序

CREATE OR REPLACE TRIGGER TR_STMBEFORE
BEFORE DELETE ON EMP
BEGIN
  DBMS_OUTPUT.PUT_LINE('STM BEFORE');
END TR_STMBEFORE;
/
CREATE OR REPLACE TRIGGER TR_STMAFTER
AFTER DELETE ON EMP
BEGIN
  DBMS_OUTPUT.PUT_LINE('STM AFTER');
END;
/
-------------------------
CREATE OR REPLACE TRIGGER TR_ROWBEFORE
BEFORE DELETE ON EMP
FOR EACH ROW
BEGIN  
  DBMS_OUTPUT.PUT_LINE('ROW BEFORE DELETE EMPNO:'||:OLD.EMPNO);
END TR_STMBEFORE;
/
CREATE OR REPLACE TRIGGER TR_ROWAFTER
AFTER DELETE ON EMP
FOR EACH ROW
BEGIN  
  DBMS_OUTPUT.PUT_LINE('ROW AFTER DELETE EMPNO:'||:OLD.EMPNO);
END TR_STMBEFORE;
/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

• 例子：

CREATE OR REPLACE TRIGGER secure_emp
   BEFORE INSERT ON emp
   BEGIN
     IF (TO_CHAR(SYSDATE, 'DY') IN ('星期六', '星期天')) 
           OR (TO_CHAR(SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00')
     THEN 
         RAISE_APPLICATION_ERROR
			 (-20500, 'You may insert into EMP table only during business hours.');
      END IF;
END;

1
2
3
4
5
6
7
8
9
10
11

4.语句级触发器–谓动词

CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT OR UPDATE OR DELETE  ON EMP
  BEGIN
    IF (TO_CHAR(SYSDATE, 'DY') IN ('星期六', '星期天')) 
           OR (TO_CHAR(SYSDATE, 'HH24') NOT BETWEEN '08' AND '18')
    THEN 
      IF DELETING THEN
          RAISE_APPLICATION_ERROR (-20502, 'You may delete from  EMP table only during business hours.');
       ELSIF INSERTING THEN
         RAISE_APPLICATION_ERROR (-20500,'You may insert into EMP table only during business hours.');
       ELSIF UPDATING ('SAL') THEN
         RAISE_APPLICATION_ERROR (-20503,'You may update SAL only during business hours.');
       ELSE
         RAISE_APPLICATION_ERROR (-20504,'You may update EMP table only during normal hours.');
       END IF;
    END IF;
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

5.行级触发器

CREATE OR REPLACE TRIGGER restrict_salary
  BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW
    BEGIN
      IF NOT (:NEW.job IN ('PRESIDENT', 'MANAGER', 'ANALYST')) AND :NEW.sal > 2500
      THEN
        RAISE_APPLICATION_ERROR (-20202, 'Employee cannot earn this amount');
      END IF;
END;

1
2
3
4
5
6
7
8
9
10

6.:old和:new

• 在行级触发器中使用：old和：new

CREATE OR REPLACE TRIGGER audit_emp_values
   AFTER DELETE OR INSERT OR UPDATE ON emp
   FOR EACH ROW
   BEGIN
      INSERT INTO audit_emp_table (user_name, timestamp,empno,
          old_ename, new_ename, old_job,new_job, old_sal, new_sal)
          VALUES (USER, SYSDATE, :OLD.empno,:OLD.ename, :NEW.ename,
              :OLD.job,:NEW.job, :OLD.sal, :NEW.sal );
END;
 --  t_oplog(user,optime,opcontent)

1
2
3
4
5
6
7
8
9
10
11

7.限制条件

• 在行级触发器中使用限制条件

 CREATE OR REPLACE TRIGGER derive_commission_pct
    BEFORE INSERT OR UPDATE OF sal ON emp
    FOR EACH ROW
    WHEN (NEW.job = 'SALESMAN')
 BEGIN
    IF INSERTING  THEN :NEW.comm := 0;
    ELSIF :OLD.comm IS NULL THEN :NEW.comm := 0;
    ELSE  :NEW.comm := :NEW.sal*0.10;
    END IF;
 END;

1
2
3
4
5
6
7
8
9
10
11

---

• 练习

create or replace TRIGGER tri_insert_emp
  BEFORE INSERT  ON emp
  FOR EACH ROW
BEGIN
  if :new.deptno is null then
    :new.deptno:=10;
  ELSIF :new.deptno=30 then
    :new.job:='SALSEMAN';
  end if;
  SELECT seq_empno.NEXTVAL
      INTO :new.empno
      FROM dual;
END tri_insert_emp;

1
2
3
4
5
6
7
8
9
10
11
12
13
14

---

CREATE SEQUENCE SEQ_EMPNO 
START WITH 9000 
MAXVALUE 9999;

1
2
3
4

---

insert into emp (ename,sal) values (‘LL',2300);

1
2

---

insert into emp (empno,ename,sal) values (9100,‘HH',2500);

1
2

8.管理触发器

9.删除触发器

21.过程

1.创建过程

2.调用过程

2.1过程中的形式参数

2.2过程中的实际参数

2.3形参的约束

2.4参数的模式

2.5IN参数

CREATE OR REPLACE PROCEDURE raise_salary
(p_id IN emp.empno%TYPE)       IS
BEGIN
     UPDATE emp          SET sal = sal * 1.10          WHERE empno = p_id;
END raise_salary;
---------------------------
--执行
DECLARE
  v_empno emp.empno%TYPE:=&no;
BEGIN
  raise_salary(v_empno);
END;

1
2
3
4
5
6
7
8
9
10
11
12
13

2.6out参数

CREATE OR REPLACE PROCEDURE query_emp
(p_id IN emp.empno%TYPE, p_name OUT emp.ename%TYPE,
 p_salary OUT emp.sal%TYPE,p_comm OUT emp.comm%TYPE)
IS
BEGIN
  SELECT ename, sal, comm
      INTO p_name, p_salary, p_comm
      FROM emp  WHERE empno = p_id;
END query_emp;
-------------------
--执行
DECLARE
  v_empno emp.empno%TYPE:=&no;
  v_name  emp.ename%TYPE;
  v_salary  emp.sal%TYPE;
  v_comm  emp.comm%TYPE;
BEGIN
  query_emp(v_empno,v_name,v_salary,v_comm);
  dbms_output.put_line(v_empno||' '||v_name||' '||v_salary||' '||v_comm);
END;


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

2.7IN OUT参数

CREATE OR REPLACE PROCEDURE swap
(x IN OUT NUMBER ,y IN OUT NUMBER)
IS
z NUMBER;
BEGIN
	z:=x;
	x:=y;
	y:=z;
END swap;

1
2
3
4
5
6
7
8
9
10

DECLARE
  a NUMBER:=10;
  b NUMBER:=20;
BEGIN
  dbms_output.put_line('交换前a和b的值是：'||a||'  '||b);
  swap(a,b);
  dbms_output.put_line('交换后a和b的值是：'||a||'  '||b);
END;

1
2
3
4
5
6
7
8
9

2.8参数的缺省值

• 例子：

CREATE OR REPLACE PROCEDURE add_dept
  (p_name IN dept.dname%TYPE DEFAULT 'unknown',
   p_loc IN dept.loc%TYPE  DEFAULT 'NEW YORK')
IS
BEGIN
   INSERT INTO dept (deptno,dname, loc)
   VALUES (dept_seq.NEXTVAL, p_name, p_loc);
END add_dept;

1
2
3
4
5
6
7
8
9

• 执行：

BEGIN
               add_dept;
               add_dept ('TRAINING');
               add_dept ( p_loc =>'BOSTON ', p_name => 'EDUCATION');
               add_dept ( p_loc => 'CHICAGO') ;
              END;     /
--------------------
select * from dept;
1
2
3
4
5
6
7
8

---

--例8.4  为scott.emp表创建一个能完成插入功能的存储过程insert_emp。
CREATE OR REPLACE PROCEDURE insert_emp 
   (no IN scott.emp.empno%TYPE,
    name IN scott.emp.ename%TYPE DEFAULT NULL,
    job IN scott.emp.job%TYPE DEFAULT 'SALESMAN',
    mgr IN scott.emp.mgr%TYPE DEFAULT 7369,
    hiredate scott.emp.hiredate%TYPE DEFAULT SYSDATE,
    salary scott.emp.sal%TYPE DEFAULT 800,
    comm scott.emp.comm%TYPE DEFAULT NULL,
    deptno scott.emp.deptno%TYPE DEFAULT 10)   IS
    e_integrity EXCEPTION;
    PRAGMA EXCEPTION_INIT (e_integrity,-2291);
BEGIN
    INSERT INTO scott.emp VALUES(no,name,job,mgr,hiredate,salary,comm,deptno);
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN  dbms_output.put_line('该员工已经存在！');
        WHEN e_integrity THEN  dbms_output.put_line('部门编号填写错误！');
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

3.删除过程

22.函数

1.创建函数

• 语法

CREATE [OR REPLACE] FUNCTION function_name 
[(argument1 [IN | OUT | IN OUT] data_type , argument2 [IN | OUT | IN OUT] data­_type,…)]
RETURN data_type
IS |AS
   [declaration_section;]
BEGIN
   executable_section;
   RETURN expression;
[EXCEPTION
   exception_handlers;
RETURN expression;
END [function_name]; 

1
2
3
4
5
6
7
8
9
10
11
12
13

2.调用函数

CREATE OR REPLACE FUNCTION get_sal
      (p_id IN emp.empno%TYPE)
RETURN NUMBER
IS
      v_salary emp.sal%TYPE :=0;
BEGIN
     SELECT sal     INTO v_salary     FROM emp     WHERE empno = p_id;
     RETURN v_salary;
END get_sal;

1
2
3
4
5
6
7
8
9
10

---

3.删除函数

4.部分例题

• 例 创建函数，从scott.emp表中查询指定编号职工的工资。

--例  创建函数，从scott.emp表中查询指定编号职工的工资。
CREATE OR REPLACE FUNCTION select_sal
(no scott.emp.empno%TYPE)
RETURN scott.emp.sal%TYPE
IS
salary scott.emp.sal%TYPE;
BEGIN
SELECT sal INTO salary FROM scott.emp WHERE empno=no;
RETURN salary;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14

• 例 创建函数，从scott.emp表中查询指定编号员工的工资和姓名。

--例   创建函数，从scott.emp表中查询指定员编号工的工资和姓名。
CREATE OR REPLACE FUNCTION select_name_sal
(p_empno in number, p_name out varchar2)
RETURN number
IS
v_result number;
BEGIN
SELECT sal ,ename INTO v_result ,p_name FROM emp WHERE empno= p_empno;
RETURN v_result;
EXCEPTION
WHEN NO_DATA_FOUND THEN
	dbms_output.put_line('无符合要求的记录');
  	v_result:=0;
  	p_name:='';
	RETURN v_result;
END;
----------------------
--例   创建函数，从scott.emp表中查询指定员编号工的工资和姓名。
CREATE OR REPLACE FUNCTION SELECT_NAME_SAL
(P_EMPNO IN NUMBER,P_SAL OUT NUMBER, P_NAME OUT VARCHAR2)
RETURN NUMBER  --0 表示成功 1表示异常 员工编号不存在
IS
V_RESULT NUMBER:=0;
BEGIN
SELECT SAL ,ENAME INTO P_SAL ,P_NAME FROM EMP WHERE EMPNO= P_EMPNO;
RETURN V_RESULT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
	DBMS_OUTPUT.PUT_LINE('无符合要求的记录');
  	V_RESULT:=1;
	RETURN V_RESULT;
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33