Oracle

发布日期: 2021-05-27

作者: 勇也很努力

文章字数: 22.3k

阅读时长: 91 分

学习视频链接：B站动力节点

SQL分类

简单的SQL语句

表的创建

sql

DROP TABLE EMP;
DROP TABLE DEPT;
DROP TABLE BONUS;
DROP TABLE SALGRADE;

CREATE TABLE DEPT
       (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
    DNAME VARCHAR2(14) ,
    LOC VARCHAR2(13) ) ;

CREATE TABLE EMP
       (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);

CREATE TABLE BONUS
    (
    ENAME VARCHAR2(10)  ,
    JOB VARCHAR2(9)  ,
    SAL NUMBER,
    COMM NUMBER
    ) ;

CREATE TABLE SALGRADE
      ( GRADE NUMBER,
    LOSAL NUMBER,
    HISAL NUMBER );

数据

sql

INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 
commit;

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7369, 'SMITH', 'CLERK', 7902,  TO_Date( '12/17/1980 12:00:00 上午', 'MM/DD/YYYY HH:MI:SS AM')
, 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7499, 'ALLEN', 'SALESMAN', 7698,  TO_Date( '02/20/1981 12:00:00 上午', 'MM/DD/YYYY HH:MI:SS AM')
, 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7521, 'WARD', 'SALESMAN', 7698,  TO_Date( '02/22/1981 12:00:00 上午', 'MM/DD/YYYY HH:MI:SS AM')
, 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7566, 'JONES', 'MANAGER', 7839,  TO_Date( '04/02/1981 12:00:00 上午', 'MM/DD/YYYY HH:MI:SS AM')
, 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7654, 'MARTIN', 'SALESMAN', 7698,  TO_Date( '09/28/1981 12:00:00 上午', 'MM/DD/YYYY HH:MI:SS AM')
, 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7698, 'BLAKE', 'MANAGER', 7839,  TO_Date( '05/01/1981 12:00:00 上午', 'MM/DD/YYYY HH:MI:SS AM')
, 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7782, 'CLARK', 'MANAGER', 7839,  TO_Date( '06/09/1981 12:00:00 上午', 'MM/DD/YYYY HH:MI:SS AM')
, 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7788, 'SCOTT', 'ANALYST', 7566,  TO_Date( '04/19/1987 12:00:00 上午', 'MM/DD/YYYY HH:MI:SS AM')
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7839, 'KING', 'PRESIDENT', NULL,  TO_Date( '11/17/1981 12:00:00 上午', 'MM/DD/YYYY HH:MI:SS AM')
, 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7844, 'TURNER', 'SALESMAN', 7698,  TO_Date( '09/08/1981 12:00:00 上午', 'MM/DD/YYYY HH:MI:SS AM')
, 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7876, 'ADAMS', 'CLERK', 7788,  TO_Date( '05/23/1987 12:00:00 上午', 'MM/DD/YYYY HH:MI:SS AM')
, 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7900, 'JAMES', 'CLERK', 7698,  TO_Date( '12/03/1981 12:00:00 上午', 'MM/DD/YYYY HH:MI:SS AM')
, 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7902, 'FORD', 'ANALYST', 7566,  TO_Date( '12/03/1981 12:00:00 上午', 'MM/DD/YYYY HH:MI:SS AM')
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7934, 'MILLER', 'CLERK', 7782,  TO_Date( '01/23/1982 12:00:00 上午', 'MM/DD/YYYY HH:MI:SS AM')
, 1300, NULL, 10); 
commit;

INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
5, 3001, 9999); 
commit;

数据类型

数据库中所有数据类型的默认值都是NULL

Oracle中的主要的数据类型：

字符型
- 固定长度的字符类型，经常被定义成CHAR(N)形式，N表示占用的字节数。最大长度是2000字节。
- 变长的字符类型，定义格式是VARCHAR2(N)，N表示最多可占用的字节数，最大长度是4000字节。
数值型，NUMBER
日期型，用于定义日期时间的数据，长度是7个字节，默认格式是DD-MON-RR，比如11-APR-17

字符型

VARCHAR(10)定长的字符型数据

CHAR(2)定长的字符型数据

VARCHAR2(20)变长的字符型数据

数值型

NUMBER(4)不带小数点的数值

NUMBER(8,2)数据的总长度是8位，小数点后占两位

SQL语句的书写规则

SQL被称为结构化查询语言（Structured Query Language），SQL是操作和检索关系型数据库的标准语言，使用SQL语句，程序员和数据库管理员可以完成如下任务：

改变数据库的结构
更改系统的安全设置
增加用户对数据库或表的许可权限
在数据库中检索需要的信息
对数据库的信息进行更新

Sql语句的书写规则

在Oracle数据库中，sql语句大小写不敏感
sql语句可单行或多行书写
在sql语句中，关键字不能跨多行或缩写
为了提高可读性，一般关键字大写，其它小写
子句通常位于独立行，便于编辑，提高可读性
空格和缩进是程序易读

SELECT

在查询的过程中，对于数值型的数据，可以执行+，-，*，/运算；也可以给查询的字段起别名，有不同的方式，可以不用””，可以用””，可以用as，可以不用as。

sql

SELECT EMPNO 员工编号,ENAME 员工姓名,SAL "月薪",SAL*12 "年薪",COMM "奖金",(SAL*12 + COMM) "年收入" FROM EMP;

select语句中的算数表达式

对NUMBER型数据可以使用算数操作符创建表达式（+ - * /）
对DATE型数据可以使用部分算数操作符创建表达式（+ -）

在算术表达式中出现NULL，得到的结果就是NULL，NULL不等于0.0

对于日期的数据可以使用+ -运算符
一个日期+-一个数值（就是+-一个天数），得到一个新的日期
sq1
SELECT EMPNO,ENAME,HIREDATE "雇佣日期",(HIREDATE + 90) "转正日期" FROM EMP;
两个日期型的数据相减，得到的是两者之间相差的天数
sql
--SYSDATE取得当前的日期
SELECT EMPNO,ENAME,(SYSDATE - HIREDATE)/365 "工作的年限" FROM EMP;
两个日期型的数据不能相加，日期型的数据不能进行乘除运算

字符串的连接操作符

将列或字符与其它列连接
用双竖线表示（||）
产生的结果列是另一个字符表达式

用||可以把两列或多列查询结果合并到一起
sql
SELECT EMPNO,ENAME,EMPNO||ENAME FROM EMP;
在连接表达式中出现字符数据，字符型的数据必须要使用，在连接表达式中出现null，就是原来的字符型数据
sql
SELECT EMPNO || '的经理是' || MGR "从属关系" FROM EMP;

空值的使用

空值是指不可用、未分配的值
空值不等于零或空格
任意类型都可以支持空值

空值的运算表达式

包括空值的任何算术表达式都等于空
包括空值的连接表达式等于与空字符串连接，也就是原来的字符串

定义字段的别名

改变列的标题头
用于表示计算结果的含义
作为列的别名
如果别名中使用特殊字符，或者是强制大小写敏感，或有空格时，都需要加双引号

文本字符串

文本字符串是一个包括在SELECT查询列表中的字符，表达式或数字
日期和字符型文本字符串必须用单引号括起来
没返回一条记录文本字符串被显示一次

去除单列重复的数据

sql

SELECT DISTINCT DEPTNO FROM EMP;

去除多列重复的数据

sql

SELECT DISTINCT JOB,DEPTNO FROM EMP;

DISTINCT的作用范围是后面所有字段的组合。

使用WHERE子句对数据进行过滤

sql

SELECT * FROM EMP WHERE DEPTNO = 10;

对于字符型的数据和日期型的数据必须要使用''，字符型的数据对大小写是敏感的。
对于日期型的数据，格式是敏感的，使用日期型数据的格式是DD-MM-YYYY（日-月-年）

Where中的字符串和日期值

字符串和日期要用单引号括起来

字符串是大小写敏感的，日期值是格式敏感的

缺省的日期格式是’DD-MON-YY’

改变当前会话中的日期格式

sql

alter session set nis_date_format="YYYY-MM-DD HH:MI:SS"

SELECT * FROM EMP WHERE HIREDATE = '20-2月-1981'；

SELECT * FROM EMP WHERE HIREDATE = '1980-12-17';

LIKE用于字符型数据的查询，可以执行模糊查询

sql

SELECT * FROM EMP WHERE ENAME LIKE '%A%';

%表示0个或多个任意的字符。

_下划线表示一个字符。

sql

SELECT * FROM EMP WHERE ENAMe LIKE '_A%';

若查询的字符型数据中含有特殊字符，例如%

sql

SELECT * FROM EMP WHERE ENAME LIKE '%\%%' ESCAPE '\';

ESCAPE '\'指定以\为转义符。

对于空值，要使用IS NULL进行比较

sql

SELECT * FROM EMP WHERE COMM IS NULL;

使用NOT运算符

sql

SELECT * FROM EMP WHERE SQL NOT BETWEEN 2000 AND 3000;

SELECT * FROM EMP WHERE JOB NOT IN('MANAGER','CLERK');

SELECT * FROM EMP WHERE COMM IS NOT NULL;

优先级规则（括号将跨越所有优先级规则）

次序	运算符
1	所有的比较运算
2	NOT
3	AND
4	OR

对查询后的数据进行排序，使用ORDER BY子句

sql

--默认按照指定的字段升序排列
SELECT * FROM EMP ORDER BY SAL;
--指定字段降序排序
SELECT * FROM EMP ORDER BY SAL ASC;
--可以按照多个字段进行排列
SELECT * FROM EMP ORDER BY DEPTNO,SAL;
--使用字段的别名来进行排序
SELECT EMPNO,ENAME,(SAL * 12) "年薪" FROM EMP ORDER BY "年薪";

函数

函数适用于执行数据处理和复杂运算的。通过对一组数据进行一系列的运算，得到一个最终的需要输出的结果。函数通常都会有一个或多个输入，被称作函数的参数。在函数的内部对输入的参数进行逻辑计算，最终会得到一个或多个值，但是只有一个值作为函数的计算结果返回给客户。

函数可以出现在sql语句的各个位置，最常见的是出现在查询列表中。

两种SQL函数

根据函数对多行输入数据的处理方式，函数分为单行函数和多行函数。
单行函数：每行作为一个输入参数，经过函数的计算得到每行的计算结果。单行函数执行的操作影响表中每一行的显示效果。每行都会得到相应的计算结果
多行函数：对多行输入值进行计算，得到多行对应的一个结果。

sql

--单行函数：每次取一条记录，作为函数的参数，得到这条记录对应的单个结果
SELECT ENAME,LENGTH(ENAME) FROM EMP;
--多行函数：一次性把多条记录当作参数输入给函数，得到多条记录对应的单个结果
SELECT MAX(SAL) FROM EMP;

单行函数分为五中类型：

字符函数
数字函数
日期函数
转换函数
其他函数

字符函数

sql

--大小写控制函数
SELECT * FROM EMP WHERE ENAME = UPPER('smith');
--lower('Hello World') 转小写
--upper('Hello World') 转大写
select lower('Hello World') 转小写, upper('Hello World') 转大写 from dual;
--initcap: 首字母大写  
select initcap('hello world') 首字符大写 from dual;

--字符控制函数  
-- concat: 字符连接函数, 等同于  ||  
select concat('Hello',' World') from dual;  
--substr:求母串中的某个子串  
select substr('Hello World',3) from dual;  
select substr('Hello World',3,4) from dual;  
--length和lengthb: 字符数和字节数  
select length('China') 字符数, lengthb('China') 字节数  from dual;  
--instr:在母串中，查找子串的位置  
select instr('Hello World','ll') from dual;  
--lpad,rpad: 左右填充,将abcd用*填充到10位  
select lpad('abcd',10,'*') 左填充, rpad('abcd',10,'*') 右填充 from dual;  
--trim: 去掉字符串前后指定的字符  
select trim('H' from 'Hello WorldH') from dual;  
--replace:字符串替换函数  
select replace('Hello Wordl','l','*') from dual;

数字函数

sql

--数字函数  
select round(45.926,2) 四舍五入, trunc(45.926,2)  截断 ,mod(1600,300) 求于 from dual;
--ROUND函数  
select round(45.923,0) 整数位, round(45.923,-1) 十位,round(45.923,-2) 百位 from dual;

日期函数

日期的运算

日期类型的数据可以使用算数运算符进行计算
从日期中加或减一个数值，以得到一个新的日期结果值
两个日期相减以便得到它们相差多少天
注意：日期不能加另一个日期，日期也不支持乘除运算

sql

--日期函数
--显示当前日期
select sysdate from dual;
--显示时间部分
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
--显示昨天，今天和明天，加减数字仍未日期
select sysdate-1 昨天, sysdate 今天, sysdate+1 明天 from dual;
--两个日期相减，结果为相差的天数，查询员工信息，显示员工工龄。两个日期不能相加
select empno,ename,sysdate-hiredate 天 from emp;
--查询员工信息，显示员工工龄,分别按照天，星期，月显示
select empno,ename,sysdate-hiredate 天,(sysdate-hiredate)/7 星期,(sysdate-hiredate)/30 月 from emp;
--months_between:两个日期相差的月数
select (sysdate-hiredate)/30 方式一,months_between(sysdate,hiredate) 方式二 from emp;
--add_months:在指定日期上加上若干个月
select add_months(sysdate,1) 下个月,add_months(sysdate,123) "123个月后" from dual;
--last_day: 某个日期当月的最后一天
select last_day(sysdate) from dual;
--next_day:下周六
select next_day(sysdate,'星期五') from dual;
--对日期进行四舍五入
select round(sysdate,'MONTH') 月,round(sysdate,'YEAR') from dual;
--对日期进行截断
select trunc(sysdate,'MONTH') 月,trunc(sysdate,'YEAR') from dual;
--日期格式 
select * from emp where hiredate=to_date('1982-01-23','yyyy-mm-dd');

日期函数

函数	描述
months_between	两日期相差多少月
add_months	在一个日期上加或减若干个月到新的日期
next_day	取得从当前日期开始遇到的第一个指定星期几的日期
last_day	计算指定日期所在月份的最后一天的日期
round	取得按年或月四舍五入得到的新日期
trunc	取得按年或月截取得到的新日期

转换函数

转换有两种方式，隐式转换和手动转换。

使用to_char函数将日期作为字符串显示

to_char(date,'fmt')

对于日期型的数据必须用单引号括起来，并且是大小写敏感
可包含任何有效的日期格式

在转换日期位字符串时，日期中年，月，日，时，分，秒都需要相应的格式控制符来控制转换的显示格式。

格式控制符	描述
YYYY，YYY，YY	分别代表4位，3位，2位的数字年份
YEAR	年的拼写
MM	数字月
MONTH	月份的全拼名称
MON	月份的缩写
DD	数字日
DAY	星期的全拼
DY	星期的缩写
AM	表示上午或下午
HH24，HH12	24小时制或12小时制
MI	分钟
SS	秒钟
SP	数字的拼写
TH	数字的序数词
”特殊字符“	在日期时间中加入特殊的字符

sql

--2021/5/18 1 2021-05-18 17:24:41 下午 星期二
select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss am day') from dual;
-- 查询当前日期：显示:  2021-05-18 17:26:56 今天是 星期二
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss "今天是"day') from dual;

使用to_char函数将数字作为字符显示

to_char(number,'fmt')

9	代表一个数字（有数字就显示，没有就不显示）
0	强制显示0
$	放置一个$符
L	放置一个本地货币符
.	显示小数点
,	显示千位指示符

sql

--查询员工信息，显示员工的编号，姓名，月薪,要求有货币代码(L)，千位符(,)，小数点(.), 
select empno,ename,to_char(sal,'L9,999.99') from emp;

to_number和to_date函数

使用to_number函数将字符转换为数字，to_number(char[,'fmt'])
使用to_date函数将字符转换为日期，to_date(char[,'fmt'])

sql

--to_number，把字符型的数据转换为数值，字符的格式和模板的模式必须要一致
select to_number('$800.00','$999,999.00') from dual;

--to_date，把字符型的数据转换为日期型的数据
select * from emp where hiredate = to_date('1981-2-20','yyyy-mm-dd');

其他函数

和空值相关的函数
nvl(expr1,expr2)
nvl2(expr1,expr2,expr3)
nullif(expr1,expr2)
coalesce(expr1,expr2,...,exprn)

sql

--在算术表达式中出现null，得到的结果就是null
select empno,ename,sal,comm,(sal*12+comm) "年收入" from emp;

nvl函数

将空值转换为实际的值。

nvl(comm,0)如果comm字段为null，就用0替换该null

数据格式可以是日期，字符，数字
数据类型必须匹配，否则出错
- nvl(comm,0)
- nvl(hiredate,'01-JAN-97')
- nvl(job,'No Job Yet')

sql

select empno,ename,sal,comm,(sal*12+nvl(comm,0)) "年收入" from emp;
--将7654的job和hiredate暂时改为null进行测试
select ename,job,nvl(job,'还没有工作') from emp where empno = 7654;
select ename,hiredate,nvl(hiredate,'1-1月-1987') from emp where empno = 7654;

nvl2函数

nvl2(expr,expr1,expr2)

如果expr不为null，返回expr1；若为null，返回expr2
expr可以为任何数据类型

sql

select ename,job,nvl2(job,'有工作','没工作') from emp where empno = 7654;

select ename,job,nvl2(job,job,'没工作') from emp;

nullif

nullif(expr1,expr2)比较两个表达式，如果相等返回空值，如果不等返回第一个表达式。

sql

select ename,job,nullif(length(ename),length(ename)),nullif(length(ename),length(job)) from emp;

case表达式

sql

--case实现if...else if...else的功能
select ename,job,sal,
        case job
            when 'CLERK' then 1.10 * sal
            when 'MANAGER' then 1.3 * sal
            when 'SALESMAN' then 1.4 * sal
            else sal
        end as "修订工资数"
from emp where ename = 'SMITH';

decode函数

decode函数类似于一系列case或if-then-else语句

decode(col/expression,search1,result1
       [,search2,result2,...,]
       [,default])

sql

select ename,job,esal,
    decode(job,'CLERK',sal * 1.10,
           'MANAGER',sal * 1.30,
           'SALESMAN',sal * 1.40,
           sal) as "修订工资数"
from emp where ename = 'SMITH';

嵌套函数

单行函数可被嵌入到任何层
嵌套函数从最深层到最低层求值

sql

select ename,job,nvl(to_char(job),'还没有工作') as "结果" from emp where job is null;

分组函数

分组函数就是多行函数。分组函数对多行输入值进行计算，得到多行对应的单行结果。在通常的情况下，会对一个表总的数据进行分组，得到多组数据，每个组中包括一行或多行数据。分组函数对每组记录分别进行计算，每组返回一个结果。最终产生多个组对应的数据集合。分组有多少，最终的结果集就有多少行。

sql

select max(sal) from emp;

常用分组函数

avg([distinct|all]n)
count({*|[distinct|all]expr})
max([distinct|all]expr)
min([distinct|all]expr)
sum([distinct|all]n)

使用avg函数可以求出一组数据的平均值，而sum可以求出一组数的总和。这两个函数都是针对数值类型的数据的

使用max函数可以求出一组数据中的最大值，min求出最小值这两个函数可以适用于任何数据类型。对于日期类型，最早的日期被认为是最小的值；对于字符型的数据，采用ASCII码值比较的方式。

count函数是用于计算符合条件的数据的条数。count有两种用法：count(*)和count(字段)，并且结果可能不同。count(*)求出所有符合条件的记录条数；而count(字符)这是求出所有符合条件并且字段值是非空的记录数。

sql

select max(sal),min(sal),avg(sal),sum(sal) from emp;

所有的组函数都是忽略空值的

sql

--按照人数计算平均奖金
select sum(comm)/count(*),avg(nvl(comm,0)) from emp;

group by子句

除了对整个表的数据进行统计计算外，更多的需要是根据用户的实际需求对数据进行分组，然后对每个分组进行组函数计算

select column,group_funtion
from table
[where condition]
[group by group_by_expression]
[order by column];

使用group by子句将表中的数据分成多个小组。分组后的数据执行组函数计算，结果返回给客户。最终的结果自动按照分组子分组进行升序排列
组函数忽略空值，可以使用nvl，nvl2，coalesce函数处理空值
结果集隐式按升序排列，如果需要改变排序方式可以使用order by子句

使用group by子句

出现在select列表中的字段，如果出现的位置不是在组函数中，那么必须出现在group by子句中
也可以只出现在group by子句中

sql

select max(sal) from emp group by deptno;
select deptno,max(sal) from emp group by deptno;

--按照多个字段进行分组
select deptno,job,max(sal) from emp group by deptno,job order by deptno;

--多分组后的数据进行过滤，过滤大于等于3000的记录，不能使用where子句，而是使用having子句
select deptno,max(sal) from emp group by deptno having max(sal) >= 3000 order by deptno;

--先使用where对数据过滤，过滤后用group by分组，分组后的数据用having在过滤
select deptno,max(sal) from emp where deptno is not null
group by deptno
having max(sal) >= 3000
order by deptno;

组函数也可以嵌套，在组函数嵌套时，必须要使用group by。组函数最多能嵌套两层。

多表查询

用单个的select语句从多个表中查询相关的数据。在多表查询时，如果不加入等值条件，会产生笛卡尔乘积。

多表查询分为不同的标准：

SQL1992标准

sql

--等值查询，在父子表的关系上，用=来连接两个表的两个字段或多个表的多个字段。
select e.empno,e.ename,e.deptno,d.deptno,d.dname
from emp e,dept d
where e.deptno = d.deptno;
--非等值查询，两个表之间没有父子关系，用!=来连接两个表
select e.empno,e.ename,e.sal,s.grade,s.losal,s.hisal
from emp e,salgrade s
where e.sal between s.losal and s.hisal and empno = 7369;
--自连接，通过别名，将一个表虚拟成两个表，然后在这两个表上做等值查询
select  e.empno,e.ename,m.empno,m.ename
from emp e,emp m
where e.mgr = m.empno and e.empno = 7369;
--外连接：在等值查询的基础之上，可以查询不满足等值条件的数据
--左外连接，可以把右边的表中不满足等值条件的数据查询出来
select  e.empno,e.ename,e.deptno,d.deptno,d.dname
from emp e,dept d
where e.deptno(+) = d.deptno;
select  e.empno,e.ename,e.deptno,d.deptno,d.dname
from emp e,dept d
where d.deptno(+) = e.deptno;
--右外连接，可以把左边表中不满足等值条件的数据查询出来
select  e.empno,e.ename,e.deptno,d.deptno,d.dname
from emp e,dept d
where e.deptno = d.deptno(+);
select  e.empno,e.ename,e.deptno,d.deptno,d.dname
from emp e,dept d
where d.deptno = e.deptno(+);
--+号不能出现在=号两边

SQL1999标准

sql

--交叉连接，相当于sql92标准等值查询时没有给出正确的等值条件，会产生笛卡尔乘积
select e.*,d.*
from emp e
cross join dept d;
--自然连接：在父子表关系上，自动的匹配两个表中列名完整相同的字段（参照列），在这些相同名称的字段上做等值查询
--参照列上不能使用前缀
--自然连接的缺陷：1、会把所有的参照列都作为等值条件；2、如果参照列的类型不同，查询会报错
--当两个表中没有参照列时，自然查询会产生笛卡尔乘积
select e.empno,e.ename,deptno,d.dname
from emp e
natural join dept d;
--join...using，在自然连接的基础上，加以改进，使用指定的参照列来作为等值条件
select e.empno,e.ename,deptno,d.dname
from emp e
join dept d using(deptno)
where e.empno = 7369;
--join...on，使用on里面指定的条件作为查询条件（on里面的条件可以是任意条件）
select e.*,d.*
from emp e
join dept d on (e.deptno = d.deptno);
--使用join...on做n个表的等值查询，需要n-1个join...on子句

--使用join...on做非等值查询
select e.empno,e.ename,s.grade,s.losal,s.hisal
from emp e
join salgrade s on (e.sal between s.losal and s.hisal)
where e.empno = 7369;

--outer join ...on 外连接
--left outer join ...on() 可以把左边表种不满足等值条件的数据查询出来
select e.empno,e.ename,e.deptno,d.deptno,d.dname
from emp e
left outer join dept d on (e.deptno = d.deptno);

select e.empno,e.ename,e.deptno,d.deptno,d.dname
from dept d
left outer join emp e on (e.deptno = d.deptno);

--right outer join ...on()可以把右边表中不满足等值条件的数据查询处理啊
select e.empno,e.ename,e.deptno,d.deptno,d.dname
from emp e
right outer join dept d on (e.deptno = d.deptno);

select e.empno,e.ename,e.deptno,d.deptno,d.dname
from dept d
right outer join emp e on (e.deptno = d.deptno);

--full outer join ...on()可以把左右两边表中不满足等值条件的数据查询出来
select e.empno,e.ename,e.deptno,d.deptno,d.dname
from emp e
full outer join dept d on (e.deptno = d.deptno);

使用union把两个结果集合并成一个结果集，两个查询语句的结果集必须要一致才能合并。

结果集一致指的是查询字段的个数、字段的类型，字段的顺序必须要一致。

sql

--使用查询语句来创建表（复制表及其数据，但是并没有复制主外键）
create table dept_bak
as
select * from dept;

--union，会去除重复的数据
select * from dept_bak
union
select * from dept;
--union all，不会去除重复的数据
select * from dept_bak
union all
select * from dept;

子查询

子查询的分类，就是根据子查询的返回结果来区分的

单行子查询：返回单行，使用单行比较运算符
多行子查询：返回多行，使用多行比较运算符

any和all一般不单独使用，通常是和”>“或”<”搭配使用

运算符含义

in 等于列表中的任何值

any 将值与子查询返回的任意一个值进行比较

all 比较子查询返回的每一个值

运算符	含义
in	等于列表中的任何值
any	将值与子查询返回的任意一个值进行比较
all	比较子查询返回的每一个值

sql

--查询比7566员工的工资高的人的信息
--为了给主查询提供条件，而首先执行的一个查询，被称为子查询（子查询是用来给主查询提供查询条件的）
--子查询通常出现在比较运算符的右边，并且用()包围起来，便于理解
select * from emp
where sal > (select sal from emp where empno = 7566);

--单行单列子查询，要使用单行比较运算符
--子查询中使用了组函数，但是子查询的结果依旧是单行单列，仍然可以使用单行比较操作符
select * from emp
where sal < (select avg(sal) from  emp);
--单行多列子查询
select mgr,job from emp where empno = 6379;

--子查询返回的结果是多行单列，就不能使用单行比较操作符
--多行单列子查询，要使用多行比较运算符，in\all\any
--使用in运算符
select e.empno,e.ename,e.job,e.sal
from emp e
where e.job in (select job from emp where sal > 2000);
--使用all运算符
--> all，大于子查询的最大值
select e.*
from emp e
where e.sal > all (select sal from emp where deptno = 30);
--< all，小于子查询的最小值
select e.*
from emp e
where e.sal < all (select sal from emp where deptno = 30);
--any
--> any，大于子查询的最小值
select e.*
from emp e
where e.sal > any (select sal from emp where deptno = 20);
--< any，小于子查询的最大值
select e.*
from emp e
where e.sal < any (select sal from emp where deptno = 30);
--多行多列子查询
select mgr,job from emp where empno = 7566 or empno = 7369;

--成对的比较
--查询和7566，7369同经理同职位的员工的信息
select empno,ename,mgr,job
from emp
where (mgr,job) in (select mgr,job from emp where empno = 7566 or empno = 7369)
and empno != 7566 and empno != 7369;
--非成对的比较，把多行多列的子查询拆分成两个多行单列的子查询，分别使用in运算
select empno,ename,mgr,job
from emp
where mgr in(select mgr from emp where empno = 7566 or empno = 7369)
and job in(select job from emp where empno = 7566 or empno = 7369)
and empno != 7566 and empno != 7369;

--子查询出现在having中
--having对分组后的数据进行过滤
select deptno,max(sal)
from emp
group by deptno
having max(sal) > (select max(sal) from emp where deptno = 20);

--子查询出现在form后面，用来提供数据源的，子查询被虚拟成一个表
select 部门编号,maxsal,avgsal
from (select deptno 部门编号,max(sal) maxsal,min(sal) minsal,avg(sal) avgsal,sum(sal) sumsal
      from emp
     where deptno is not null
     group by deptno)
where 部门编号 = 20;

事务

把多个相关的操作捆绑成一个逻辑单元，要么全都成功，要么全都失败。

事务开始于第一个操作
事务结束于
- 提交结束，所有的操作都成功；回滚结束，所有的操作都失败，回滚到事务开始之前的状态。
- DDL或DCL语句（隐式的提交事务）
- 用户连接异常，或者用户断开连接（隐式回滚）
- 系统崩溃（隐式的回滚）
事务会在多种情况下结束。通常应该由产生事务的用户回话通过命令显示的控制事务的结束（commit和rollback），这样可以保证事务中数据改变的可控性。
除了用户直接控制事务外，其它的情况会隐式的结束事务。由于隐式结束事务的不可预料性，应尽量避免隐式的结束事务。

commit和rollback的优点

oracle中控制事务的主要命令是commit和rollback命令，还有一个辅助的savepoint命令。

使用commit和rollback来控制事务，有如下的好处

保证数据一致性，修改过的数据（不一致的数据）在没有提交之前其它的用户是不能看到的
在数据永久生效前重新查看修改的数据
将相关的操作组织在一起。一个事务中的相关数据改变或者全部成功，或者全部失败

事务的自动处理

使用commit和rollback命令可以显示的控制事务的结束。另外在一些时候，事务也会隐式的结束。隐式结束事务有两种方式：隐式提交和隐式回滚
当下列情况发生时，事务自动隐式提交：
- 执行一个DDL语句
- 执行一个DCL语句
- 从SQL*Plus正常退出
当从SQL*Plus中强行退出，客户端到服务器端的连接异常中断，或系统失败时，事务自动隐式回滚

回退到指定的标记点上

通过存储点在当前的事务中创建标记点
可回退到存储点指定的标记点

sql

--在事务中使用标记点
insert into dept values(50,'人事部','NEW YORK');
savepoint a;
delete from dept where deptno = 50;
savepoint b;
update dept set dname = '销售部' where deptno = 40;
rollback to b;
commit;
rollback to a;

DML语句

DML语句，会引起数据库中发生一个事务。

插入语句

sql

--第一个执行的DML语句会引起事务
--在事务没有结束之前，只有当前用户可以看到对数据库的修改操作，其它用户是看不到的
--事务可以以回滚的方式结束，所有的操作被放弃，回滚到事务开始之前的状态
--事务也可以以提交的方式结束，对数据库的修改被永久的保存，其它用户可以看到被修改的数据

--插入数据时，不指定插入的字段，则表示要插入全部的字段
insert into dept(deptno,dname,loc) values(50,'销售部','NEW YORK');
--回滚
rollback;
--提交
commit;

--一次性插入多条数据（复制表中的数据，把查询结果当作数据插入到表中）
insert into dept_bak select * from dept;

更新语句

sql

--使用update语句时，在事务没有结束之前，该条数据会被锁住，其它的用户无法修改这条数据
--事务结束之后，该条数据的锁被放开，其它用户才可以操作这条数据
update dept set dname = '人事部',loc = 'DALLAS' where deptno = 50;

删除语句

sql

delete from dept where deptno = 50;

合并语句

sql

MERGE INTO table_name alias1 
USING (table|view|sub_query) alias2
ON (join condition) 
WHEN MATCHED THEN 
 UPDATE table_name 
 SET col1 = col_val1, 
   col2 = col_val2 
WHEN NOT MATCHED THEN 
 INSERT (column_list) VALUES (column_values);

根据条件在表中执行修改或插入数据的功能，如果插入的数据行在目的表中存在就执行update，如果不存在则执行insert。

避免了单独的修改
增强性能和应用性
在数据仓库应用程序中尤为有用

sql

merge into dept_back d
using dept s
on(d.deptno = s.deptno)
when matched then
    update set
        d.dname = s.sname,
        d.loc = d.loc
when not matched then
    insert values(s.deptno,s.dname,s.loc)

建表

创建表，必须具有

创建表的权限
足够的存储区域

数据类型

数据类型	说明
varchar2(size)	可变长字符串
char(size)	定长字符串
number(p.s)	数字值
date	日期值
long	变长度的字符串，最大字节数可达2GB
clob	单位字符串数据最大字节数4G
raw and long raw	存储二进制数据的可变长度字符串
blob	二进制数据，最大字节数4G
bfile	二进制数据外部存储，最大字节数4G

sql

--建表语句
create table student(
    sid number(4) primary key,
    sname varchar(10),
    grade varchar(20),
    sex char(2) default '男',
    birthday date
);
--插入数据
insert into student values(1,'test01','1班','女','12-8月-1987');
insert into student (sid,sname,grade,birthday) values(2,'test02','2班',to_date('1987-12-8','yyyy-mm-dd'));

select * from student;

在不影响数据的情况下，对表做出修改，对表的修改主要是对字段的修改。

主要的操作：

1、添加字段。添加字段，总是可以成功，新添加的字段出现在表的最后面。

sql

alter table student add tel varchar2(20);

2、修改字段（修改字段的类型，修改字段的长度）。在该字段没有数据的时候，字段的类型、字段的长度都是可以修改的。

sql

alter table student modify tel varchar(11);

对于缺省值的修改，不会影响已经存在的数据，只会对以后插入的数据产生影响。

sql

alter table student modify sex char(2) default '女';
insert into student (sid,sname,grade,birthday) values(3,'test03','1班',to_date('1987-12-8','yyyy-mm-dd'));

当该字段有数据时，字段的类型是不能修改的。字段的长度是可以修改的，增大总是可以的，减小时需要看数据的实际长度。

sql

alter table student modify grade varchar2(21);

3、删除字段，将该字段下的所有数据一并删除。

sql

alter table student drop column tel;

在建表时使用子查询

语法：

create table table_name 
    [column(,column...)]
as
subquery;

新建的字段列表必须与子查询中的字段列表匹配
字段列表可以省略

sql

create table student_bak
as
select sid,sname from student;

删除表的数据

在删除数据的时候，可以使用delete语句，但是效率不高。

truncate table比delete删除速度快，但是该命令需慎用

sql

truncate table table_name;

清除表中所有的记录
是DDL语句，不可以回滚，而delete语句可以回滚
释放表的存储空间，delete命令不释放空间

删除表

表中所有的数据将被删除
没有完成的事务被提交
所有相关的索引被删除
不能回退

sql

drop table table_name;

改变对象的名称

在Oracle中，对象的名称可以方便的进行修改。通过一个DDL语句的rename命令可以改变一个数据库对象的名称
使用rename语句改变对象名称，可以修改表、视图、序列或同义词的名称
必须是对象的所有者，即使是DBA也不可以

sql

rename old_name to new_name;

tename student to student_new;

约束

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

约束条件可以时构建在一个表的单个字段上，也可以构建在一个表的多个字段上。

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

Oracle支持下面5类完整性约束：

not null非空
unique key唯一键
primary key主键
foreign key外键
check 检察

约束必须要建立在表上，但是约束时一个独立的数据库对象，没有表，也就没有约束。

创建约束的操作，可以在建表时把约束创建处理，也可以在建好表之后，通过alter table来给表添加约束。

约束也是一个独立的数据库对象，也需要命名，有两种方式：

1、给约束取名。

2、让oracle给约束取名。

非空约束，是唯一个可以定义在列级的约束（只能看守一个字段），确保该字段的数据不能为空。

除了主键约束外，一个表中可以同时存在多种相同类型的约束。

sql

create table student(
    sid number(4) primary key,
    sname varchar(10) not null,
    grade varchar(20) not null,
    sex char(2) default '男',
    birthday date
);

唯一约束，保证该字段的数据不能重复，或字段组合不能重复，但是可以为null

一个表中也可以同时存在多个唯一约束。

唯一约束可以作用在单个字段上（称为列级约束）

sql

drop table student;
create table student(
    sid number(4) primary key,
    sname varchar2(20) not null,
    email varhcar2(20),
    constraints email_uni unique(email)
);
insert into student values(1,'test01','test@qq.com');
insert into student values(2,'test01','test2@qq.com');

唯一约束也可以同时作用在多个字段上（称为表级约束）

sql

drop table student;
create table student(
    sid number(4) primary key,
    first_name varchar2(10),
    last_name varchar2(20),
    constraints name_uni unique (first_name,last_name)
);

insert into student values(1,'test','01');
insert into student values(2,'test','02');
insert into student values(3,'01','test');

insert into student values(4,'test','01');

insert into student values(5,'test',null);

主键约束：从功能上说，相当于非空且唯一

通过主键可以唯一确定一条记录。

在一个表中只能有一个主键约束。

一个实体中什么样的数据可以被确定为主键？

不要使用实体的业务数据来当作主键，因为业务数据是可以编号的，应该用一个和实体无关的流水号来当作表的主键。

主键约束作用在单个字段上。

sql

drop table student;
create table student(
    sid number(4),
    sname varchar2(20),
    constraints pk_stu primary key (sid)
);
insert into student values (1,'test01');
insert into student values (2,'test01');
insert into student values (2,'test01');
insert into student values (null,'test01');

主键约束同时作用在多个字段上。

sql

drop table student;
create table student(
    first_name varchar2(10),
    last_name varchar2(20),
    grade varchar2(20),
    constraints pk_stu primary key (first_name,last_name)
);

insert into student values('test','01','java');
insert into student values('test','02','python');
insert into student values('01','test','java');
insert into student values('02','test','java');
insert into student values('test',null,'c++');
insert into student values(null,'test','javascript');
insert into student values(null,null,'java');

外键约束

一对多（多对一）用主外键来实现。

外键约束可以重复，可以为null。

外键约束关系到两个表的两个字段之间的关系。

在外键约束下，在建表时，须先建立父表，在建立.子表。

sql

create table dept1(
    deptno number(4) primary key,
    dname varchar2(10)
);

create table empl1(
    empno number(4) primary key,
    ename varchar2(10),
    job varchar2(10),
    dept_no number(4),
    constraints fk_emp foreign key (dept_no) references dept1 (deptno)
);

在外键约束下，在添加数据时，要先添加父表数据，后添加子表数据。

sql

insert into dept1 values(1,'人事部');
insert into dept1 values(2,'销售部');
insert into dept1 values(3,'研发部');

insert into empl1 values (1,'test01','销售员',2);
insert into empl1 values (2,'test02','工程师',3);
insert into empl1 values (3,'test03','人事经理',1);
insert into empl1 values (4,'test04','销售员',null);

在外键约束下，删除数据时，要先删除子表数据，后删除父表数据。

sql

delete from empl1 where dept_no = 3;
delete from dept1 where deptno = 3;

在外键约束下，删除表时，要先删除子表，后删除父表。

sql

drop table empl1;
drop table dept1;

父表中的什么样的字段可以被其它表引为外键？
父表中的主键或唯一键可以被其他表作为外键。

在建立好表之后，通过alter table给表添加约束。

sql

--一对一，用主外键来实现，在外键上同时设置唯一约束
create table card(
    cid number(4) primary key,
    cname varchar2(10)
);

create table person(
    pid number(4) primary key,
    pname varchar2(20),
    cid number(4),
    constraints fk_per_card foreign key (cid) references card(cid),
    constraint cid_uni unique (cid)
);

insert into card values (1,'身份证');
insert into card values (2,'身份证');

insert into card values(1,'test01',1);
insert into card values(2,'test02',1);


--多对多关系的实现，要引入关系表
create table student(
    sid number(4) primary key,
    sname varchar2(20)
);

create table course(
    cid number(4) primary key,
    cname varchar2(20)
);
--1、关系表中可以用上面两个表中的主键当作外键，同时又把外键当作联合主键
create table stu_cour(
    sid number(4),
    cid number(4),
    constraint fk1 foregin key (sid) references student (sid),
    constraint fk2 foregin key (cid) references course(cid),
    constraint pk_stu_cour primary key (sid,cid)
);

--2、关系表中可以用上面两个表中的主键当作外键，关系表中有自己独立的主键
create table stu_cour(
    scid number(4) primary key,
    sid number(4),
    cid number(4),
    constraint fk1 foregin key (sid) references student (sid),
    constraint fk2 foregin key (cid) references course(cid)
);

insert into student values(1,'test01');
insert into student values(2,'test02');
insert into student values(3,'test03');

insert into course values(1,'java');
insert into course values(2,'html');
insert into course values(3,'mysql');

insert into stu_cour values(1,1,1);
insert into stu_cour values(2,1,2);
insert into stu_cour values(3,1,3);

数据库对象

对象名称	描述
表	基本的数据存储对象，以行和列的形式存在
约束	执行数据校验，保证数据完整性的对象
视图	一个或多个表数据的显示
索引	用于提高查询的速度
同义词	对象的别名

数据库对象的命名规则

必须以字母开头
可包括数字（0-9），字母（a-z）和三个特殊字符（# _ $)
不要使用oracle的保留字
同一用户下的对象不能同名，即使是不同的对象类型

索引

什么是索引？

方案（schema）中的一个数据库对象
在Oracle数据库中用来加速对表的查询
通过使用快速路径访问方法快速定位数据，减少了磁盘的I/O
索引是独立的数据库对象，并不与表存储在一起，而是与表独立存放
索引记录了表的索引字段的值，也就是关键字，关键字始终与表的索引字段值相同，这种同步由Oracle数据库自动维护

创建索引有两种方式：

自动创建：Oracle会自动为主键和唯一键创建索引

自动创建的索引是无法手动删除的，但是在删除主键约束、唯一约束时，对应的索引会被自动的删除。
手动创建约束，表中什么样的字段应该添加索引

查询时，经常被用来作为查询条件的字段，应该添加索引

sql

--创建索引
create index ename_index on emp (ename);
--删除索引
drop index ename_index;

视图

视图也就是虚表，实际上是一个命名的查询，用于改变数据的显示形式，简化查询。访问视图与表的访问方式是一样的。

任何有效的查询语句，无论是多么复杂，都可以被创建成一个视图。

视图的好处：

可以限制对数据的访问，让用户通过视图可以看到表中的一部分数据
可以使复杂的查询变得简单
提供了数据的独立性，用户不用知道数据来源于哪
提供了对相同数据的不同显示

sql

create view emp_view
as
select * from emp;

--在子查询中使用别名创建视图
create or replace view emp_v
(empno_v,ename_v,job_v,年薪)
as
select  e.empno,e.ename,e.job,e.sal*12 as "年薪"
from emp e
where e.deptno = 10;
--在视图中的列名使用的是子查询中列的别名
--在create view语句中字段与子查询中的字段必须匹配
--在create view语句中字段与子查询中的字段必须匹配

删除视图，会影响原来的数据吗？

sql

drop view emp_view;

视图是一个（虚）表，对表插入数据，也可以对视图插入数据。

对视图插入数据，数据会被插入到源表中。

禁止对视图执行DML操作

通过设置with read only选项可以禁止对视图执行DML操作

当用户禁止对视图操作后，任何对视图执行的DML操作都将导致Oracle错误

sql

create or replace view v_empinfo
as
select * from emp where sal > 2000
with read only;

TON—P分析法

行内视图，就是出现在from后面的子查询，也就是一个视图，但是该视图没有命名，不会在数据库中保存

sql

--查询工资最高的前三个人的信息，这种方式被称为TOP-N分析法
select rownum,e.*
from (select * from emp order by sal desc) e
where rownum <= 3;

rownum只能适用于<=的情况，不能适用于>=的情况。

数据的插入顺序决定了查询数据时的顺序，也就是决定了rownum的顺序。

（1）rownum对于等于某值的查询条件

如果希望找到学生表中第一条学生的信息，可以使用rownum=1作为条件。但是想找到学生表中第二条学生的信息，使用rownum=2结果查不到数据。因为rownum都是从1开始，但是1以上的自然数在rownum做等于判断时认为都是false条件，所以无法查到rownum=n（n>1的自然数）。

sql

--可以查到数据
select rownum,t.* from student t where rownum = 1;
--查询不到数据
select rownum,t.* from student t where rownum = 2;

（2）rownum对于大于某值的查询条件

如果想找到从第二行记录以后的记录，当使用rownum>2是查询不出记录的，原因是由于rownum是一个总是从1开始的伪列，Oracle认为rownum > n（n>1的自然数）这种条件依旧不成立，所以查询不到记录。

（3）rownum对于小于某值的查询条件

如果想找到第三条记录以前的记录，当使用rownum<3是能得到2条记录的。显然rownum对于rownum < n（n>1的自然数）的条件是成立的，所以可以找到记录。

（4）可能有时需要查询rownum在某区间的数据，从上面可以看出rownum对小于某值的查询条件是认为true的，rownum对于大于某值的查询条件直接认为是false的，但是可以间接的让它转为认为是true的。那就必须使用子查询。例如要查询rownum在第二行到第三行之间的数据，包括第二行和第三行数据，那么先让它返回小于等于3的记录行，然后再主查询中判断新的rownum的别名列大于等于2的记录行。但是这样的操作会在大数据集中影响速度。

（5）rownum和排序

Oracle中的rownum是在取数据时产生的序号，所以想对指定排序的数据去指定rownum行数据就必须注意了。

sql

select rownum,id,name from (select * from student order by name);

rownum并不是按照name列来生成的序号，是系统按照记录插入时的顺序给记录排的号，rowid也是顺序分配的。为了解决这个问题，必须使用子查询。

sql

--可以使用rowid来获得数据的修改权限
select rowid,e.* from emp e;

对于rownum来说Oracle系统顺序分配为查询返回的行的编号，返回的第一行分配的是1，第二行是2，依次类推，这个伪字段可以用于限制查询返回的总行数，而且rownum不能以任何表的名称作为前缀。

rowid是物理地址，用于定位Oracle中具体数据的物理存储位置。

同义词

同义词，就是数据库对象的一个别名，可以简化访问其它用户的数据库对象。

通过使用同义词，可以：

简化了引用另一个用户对象的方法（用户名.对象名）
缩短了对象名称的长度
同时屏蔽了对象的名称，使用户不知道最终的数据来源于哪个对象

语法：

sql

create synonym synonym_name for object;

创建和删除同义词

sql

--创建表的别名
create synonym dual_1 for sys.dual;
select sysdate from dual_1;
--删除同义词
drop synonym dual_1;

序列

用来维护数据库的主键数据。

nextval，currval的使用
select *.currval from sys.dual;

sql

--
create sequence seq_stu
minvalue 1
maxvalue 9999
start with 1
increment by 1;
--使用序列
insert into student values(seq_stu.nextval,'test');

select * from student;

select seq_stu.currval from sys.dual;

数据库设计范式

第一范式：一个表中不能包含重复的数据列。

一个表中不能包含重复的数据列。一个实体不能包含重复的属性（消除重复数据组，没有重复的属性和属性组）。

第二范式：所有的非主键字段必须完全依赖于表（实体）的主键。

第三范式：非主键字段不能依赖于其它的非主键字段，即非主键字段之间不存在着传递依赖。

非主键属性不能依赖于其它的非主键属性。换句话说就是非主键属性之间不能存在传递依赖。

PL/SQL

PL/SQL也是一种程序语言，被称作支持SQL的程序语言（Program Language），是Oracle数据库对SQL语句的扩展，在普通的SQL语言中增加了编程语言的特点。

数据操作和查询语句被包含在PL/SQL代码的过程性单元中，经过逻辑判断、循环等操作完成复杂的功能或者计算。

使用PL/SQL可以编写具有很多高级功能的程序，虽然这些功能可以通过多个SQL语句来完成同样的功能，但是PL/SQL具有以下的优点：

使一组语句功能形成模块化程序开发
使用过程性语言控制程序结构
可以对程序中的错误进行处理
具有较好的可移植性
集成在数据库中，调用更快
减少了网络的交互，有助于提高程序性能

PL/SQL提供的新特性

PL/SQL提供了一些新的特性，可以进行复杂的信息处理

软件包
触发器
存储过程
函数
异常处理

PL/SQL可以使用所有的SQL数据操作，游标控制和事务控制命令，以及所有的SQL函数和运算符。PL/SQL完全支持SQL数据类型。

SQL，SQL*Plus，PL/SQL之间的关系

可以把Oracle数据库看作餐馆的厨房，而SQL*Plus是将菜单（脚本，命令或程序）送进厨房（即数据库）的服务员。在厨房中有两个厨师，SQL和PL/SQL。作为服务员的SQL*Plus知道它可以处理哪些命令，哪些命令要交给厨师处理。

在SQL*Plus提示符下输入的执行命令或程序就像顾客点的蔡，对于每个顾客点的蔡，厨师都知道如何及逆行处理，就像在厨师心中的菜谱一样，PL/SQL也存储常用命令的食谱（这些元素称为触发器，存储函数，存储过程，软件包）。

有些大菜需要多个厨师共同处理，大多数的程序都是结合了SQL和PL/SQL，并在它们之间来回的传递信息，从而处理脚本或程序。顾客点的菜做好之后，再由作为服务员的SQL*Plus将执行的结果显示给用户。

PL/SQL块的基本结构

PL/SQL中起作用的部分都是由基本块组成的。基本块由4个组成部分。

声明部分：declare，可选部分

变量、常量、游标、用户自定义异常声明
执行体开始部分：begin，必要部分
- sql语句
- PL/SQL语句
异常处理部分：exception，可选部分

程序出现异常时，捕捉异常并处理异常
执行体结束：end;，必要部分

sql

declare
    --在declare部分声明变量，常量等
    --声明变量的规范：变量名称 变量类型 [:=缺省值];
    v_deptno number;
begin
    --在begin部分可以写sql语句，pl/sql语句
    --在begin部分可以使用declare部分声明的变量，常量
    dbms_output.put_line('欢迎使用PL/SQL，执行查询语句之前，v_deptno=' || v_deptno);
    select deptno into v_deptno from emp where empno = 7369;
    dbms_output.put_line('执行查询语句后，v_deptno=' || v_deptno);
    delete from emp where deptno = v_deptno;
    delete from dept where deptno = v_deptno;
end;

PL/SQL环境

在数据库执行PL/SQL程序时，PL/SQL语句和SQL语句被分别解析和执行的。PL/SQL块被数据库内部的PL/SQL引擎提取，将SQL语句取出送给Oracle的SQL引擎。两种语句分别在两种引擎中分析处理，在数据库内部完成数据交互，处理的过程。

PL/SQL块语法规则

语句可以写在多行，就像SQL语句一样
各个关键字，字段名称等，通过空格分隔
每条语句必须通过分号结束，包括PL/SQL结束部分的end关键字后面也需要加分号
标识符的规定
- 最多可以包含30个字符
- 不能包含保留字，若有使用双引号括起来
- 必须以字母字符开始
- 不能与数据库的表或者列名称相同
在PL/SQL程序中出现的字符和日期必须用单引号括起来

数字可以是简单值或科学计数法表示

v_Sql number(4) := 2000;
v_year number(4) := v_sql *12;

在PL/SQL中也需养成添加注释的习惯，注释可以是
- /*和*/之间的多行注释
- 单行注释，以--开始

PL/SQL中处理变量

在声明部分声明和初始化变量
在执行部分为变量赋新值，或在表达式中使用变量
在异常处理部分也可以使用变量
通过参数把值传递到PL/SQL块中
通过输出变量或者参数将值传出PL/SQL块

声明变量和常量

语法：

identifier [constant] datatype [not null]
            [:= | default expr];

定义的标识符名称应遵循命名规则
在声明常量时可以为其设置初始化值，也可以设置not null
可以使用赋值运算符（:=）或者default保留字来初始化标识符
在声明时，每行只能声明一个标识符

sql

declare
    v_total_sal number(9,2) := 0; --PL/SQL中赋值语句
    c_tax_rate constant number(3,2) := 8.25; --常量只能被赋值一次
    v_gender char(1);
    v_valid boolean not null := true;
    v_b boolean;
    v_num1 number(2) := 11;
    v_num2 number(2) := 10;
begin
    dbms_output.put_line('v_total_sal=' || v_total_sql);
    -- = 相当于Java中的 == 的功能，执行逻辑比较操作
    v_b := (v_num1 = v_num2);
    /*
    if(v_b = true) then
        dbms_output.put_line('OK');
    else
        dbms_output.put_line('NOT OK');
    end if;
    */
    if(v_b) then
        dbms_output.put_line('OK');
    else
        dbms_output.put_line('NOT OK');
    end if;
end;

简单变量

简单变量不包括任何组件，只能保存一个值

基本类型包括三大类：字符，数字，日期

binary_integer，整形数字
number[(precision,scale)]，数字类型
char[(maximum_length)]，定长字符类型
varchar2(maximum_length)，变长字符类型
date，日期类型
long，长字符类型
long raw，长二进制类型
clob/blob/bfile，大对象类型（字符大对象，二进制大对象，操作系统文件大对象）
boolean，布尔类型，有效值为true，false，null

复合数据类型

复合变量也叫组合变量。在复合变量中包含多个内部的组件，每个组件都可以单独存放值。一个复合变量可以存放多个值。

与简单变量类型不同，复合变量不是数据库中已经存在的数据类型，所以复合变量在声明类型之前，首先要创建使用到的复合类型，然后将变量声明为复合变量。

复合数据类型：

PL/SQL tables，表类型
PL/SQL records，记录类型

复合类型被创建后，可以被使用多次定义多个变量。

table

type type_name = is table of scalar_datatype
    [not null] index by binary_integer;
identifier type_name;

表类型类似于其它编程语言中的数组类型

由两个组件组成：

数据类型为binary_integer（整形数字）的主键
数据类型为一个确定的简单类型的列

table类型没有长度限制，可以动态增长。表类型中的第二部分类似于数组中的值，这个部分必须是一个已经确定的简单类型，不能是其它的复合类型。

表类型的结构很像数组。第一部分是一个按1递增的整形数字，起到数字索引的作用，第二部分是一种确定的简单类型，用来存放每个索引号对应的具体的数值。

sql

declare
    --在声明部分声明表类型
    type nameed_table_type is table of varchar2(6) index by binary_integer;
    --使用声明的表类型来声明变量
    v_table1 nameed_table_type;
begin
    --给表类型的变量赋值，可以通过索引来访问表类型的变量
    --表类型的变量没有长度的限制
    v_table1(1) := 'hello1';
    v_table1(2) := 'hello2';
    v_table1(3) := 'hello3';
    v_table1(4) := '工程师';
    v_table1(5) := 'hello1';

    dbms_output.put_line('v_table1(1)=' || v_table1(1) || ',v_table1(2)=' || v_table1(2));
end;

records

复合类型中的records类型是由多个组件组成的一种类型。包含一个或几个组件，每个组件称为一个域（field），域的数据类型可以是简单变量类型、另一个record类型或PL/SQL的table类型。

在使用records变量时把多个域的集合作为一个逻辑单元使用，对记录类型变量赋值或引用，都需要使用”记录变量名.域名“的方式来实现。

主要用于从表中取出查询到的行数据。

记录类型可以包含一个或多个域，每个域相当于记录类型变量的一个属性。在使用记录变量类型时，实际上是对记录类型变量的属性进行操作。每个域都可以是不同的数据类型，存放不同类型的数据。

sql

declare
    --声明记录类型
    type named_recore_type is record(
        empno number(4),
        ename varchar2(20),
        job varchar2(20),
        mgr number(4),
        hiredate date,
        sal number(6,2),
        com number(8,2),
        deptno number(4)
    );

    --使用记录类型来声明变量
    v_rec named_recore_type;
begin
    --给记录类型的变量赋值，只有类型中声明好的字段，才可以进行访问
    v_rec.empno := 7963;
    v_rec.ename := 'test';
    v_rec.job := 'Java开发';
    v_rec.mgr := 7800;
    v_rec.hiredate := to_date('1987-12-5','yyyy-mm-dd');
    v_rec.sal := 2000;
    v_rec.comm := 1000;
    v_rec.deptno := 10;

    dbms_output.put_line('v_rec.empno=' || v_rec.empno || 'v_rec.ename=' || v_rec.ename);
end;

%type

除了可以使用已经确定的类型来声明变量之外，还可以使用%type，%rowtype来作为变量的类型。

%type的前缀可以是一个前面已经声明过的简单类型的变量（可以是简单类型的变量，也可以是复合类型的变量-表类型的变量），也可以是一个表的字段的名称。

通过%type类型声明新变量的类型，实际上就是将参照的变量或表中的字段类型作为新变量的类型，新变量的类型与它所参照的类型完全相同，并且保持同步。

sql

declare
    v_empno number(4);
    --用v_empno的类型作为v_deptno的类型
    v_deptno v_empno%type;
begin
    v_empno := 8000;
    v_deptno := 20;
    dbms_output.put_line('v_empno=' || v_empno || 'v_deptno=' || v_deptno);
end;


declare
--用emp表的deptno的类型作为v_deptno的类型，当emp表中的deptno的类型编号变化时，v_deptno的类型自动跟着变化
    v_deptno emp.deptno%type;
begin
    v_deptno := 30;
    dbms_output.put_line('v_deptno=' || v_deptno)
end;

使用%type属性的好处：

可能不知道数据库中字段的数据类型
数据库中字段的数据类型可以在运行时被改变
和前面声明过的变量的类型保持一致

%rowtype

%rowtype与%type作用类似，用于定义不确定的类型。

%rowtype的前缀可以是一个表名（或视图），也可以是前面声明的一个记录类型的变量（该变量必须要参照一个表，而不能是自定义的记录类型）。

record中的域，与表的字段名称、个数、数据类型，以及长度完全相同，可以用来存放从从表中取出的一条记录的所有的字段值（select * from table）。

sql

declare
    -- %rowtype的前缀是一个表名
    v_emp dept%rowtype;
    --使用%rowtype时，Oracle做了两件事：1、用dept表的字段及其类型来声明了一种记录类型；2、用这种记录来声明变量
begin
    v_emp.deptno := 30;
    v_emp.dname := '研发部';
    v_emp.loc := '北京';
    dbms_output.put_line('v_emp.deptno=' || v_emp.deptno || 'v_emp.dname=' || v_emp.dname);
end;


declare
    --v_dept是一种记录类型的变量
    v_dept dept%type;
    --用v_dept的类型（记录类型）来作为v_emp的类型，v_emp也是一种记录类型
    v_emp v_dept%rowtype;
begin
    v_emp.deptno := 20;
    v_emp.dname := '人事部';
    v_emp.loc := '上海';
    dbms_output.put_line('v_emp.deptno=' || v_emp.deptno || 'v_emp.dname=' || v_emp.dname);
end;

%rowtype声明的肯定是记录类型的变量。

嵌套块和变量作用域

PL/SQL程序也叫做PL/SQL程序块，在程序块中可以嵌套另一个程序块，外部的程序块叫做”父块“或”外部块“，嵌套的块叫”子块“或”嵌套块“。

只要是允许执行语句的地方，就可以使用嵌套PL/SQL块
嵌套块也被当作一个语句
异常部分也可以包含嵌套块，嵌套块也可以包括异常部分
应用对象的程序对象的作用域指的是可见范围
标识符可见的范围
- 子块可以向上查看父块的标识符
- 父块不能向下查看子块的标识符

PL/SQL嵌套块的使用：只要是能出现非声明性的SQL语句的地方（在begin和exception部分），都可以出现嵌套块。嵌套块本本身就被当作一个SQL语句来执行。

sql

declare
    v_weight number(3) := 100;
begin
    declare
        v_weight number(3) := 200;
    begin
        v_weight := v_weight + 1;
        dbms_output.put_line('在嵌套块中：v_weight=' || v_weight); --201
    end;

    v_weight := v_weight + 1;
    dbms_output.put_line('在嵌套块外：v_weight=' || v_weight); --101
end;

PL/SQL中的函数

可用
- 数字函数
- 字符函数
- 类型转换函数
- 日期函数
不可用
- 组函数

在PL/SQL中使用函数和直接在Oracle中使用函数没有区别。

sql

declare
    v_date datep := to_date('1987-12-8');
    v_name varchar2(10) := 'Hello test';
begin
    dbms_output.put_line('v_date=' || to_char(v_date,'yyyy-mm-dd') || ',字符长度是：' || length(v_name));
end;

数据类型转换

把数据转换为可操作的数据类型。

转换函数：

to_char
to_date
to_number

sql

declare
    v_1 varchar2(20);
begin
    --v_1 := user || ':' || to_char(sysdate);
    v_1 := user || ':' || sysdate;
    dbms_output.put_line(v_1);
end;

PL/SQL中的SQL命令

在PL/SQL程序块中可以使用各种SQL命令，但是使用的方法根据命令不同也各不相同。

使用select命令，可以从数据库中取出单行数据，语法略有变化
使用DML命令，修改数据库中的行，没有变化
使用commit或rollback命令控制事务，没有变化
通过execute immediate，执行DDL和DCL语句

查询数据

在PL/SQL中通过select从数据库中检索数据：

sql

select select_list
into ariable_name | record_name
from table
where condition;

必须使用into子句，用于将查询出的数据传递给变量
查询必须并且只能返回一行
可以使用完整的select语法

sql

declare
    v_empno emp.empno%type;
    v_ename emp.ename%type;
begin
    --查询列表和变量必须要匹配
    select empno,ename into v_empno,v_ename from emp where empno = 7369;
    dbms_output.put_line('v_empno=' || v_empno || ',v_ename=' || ename);
end;

--用记录类型的变量和select *搭配使用
declare
    v_emp emp%rowtype;
begin
    select * into v_emp from emp where empno = 7369;
    dbms_output.put_line('v_empno=' || v_empno || ',v_ename=' || ename);
end;

在PL/SQL中使用组函数时，可以使用，但是要保证查询语句只能返回一条记录。

sql

declare
    v_deptno number(4);
    v_maxsal number(8,2);
    v_minsal number(8,2);
begin
    select deptno,max(sal) maxsal,min(sal) minsal into v_deptno,v_maxsal,v_minsal
    from emp
    group by deptno
    having max(sal) > 3000;

    dbms_output.put_line('v_deptno=' || v_deptno || ',v_maxsal=' || v_maxsal);
end;

查询异常

PL/SQL中的select语句必须返回且只返回一行。如果检索到了0行或多于1行，将会引起异常。

select常见的异常：

too_many_rows
no_data_found

操作数据

通过使用DML命令，修改数据库中表的数据。

在PL/SQL中执行DML语句、事务控制语句和在Oracle中执行没有任何区别。

sql

begin
    insert into emp values(8000,'test','销售员',7902,'12-8月-1987',2000,1000,10);
    update emp set job = 'MANAGER',sal = 2600 where empno = 7369;
    delete from dept where deptno = 40;
    --commit;
    --rollback;
end;

在PL/SQL中执行DDL，DCL要使用execute immediate。

sql

begin
    execute immediate 'create table users (id number(4) primary key,name varchar2(20),sex char(2) )';
end;

begin
    execute immediate 'create table users (userid number(4) primary key,username varchar2(20),sex char(2) check (sex in (''男'',''女'')) )';
end;

条件判断

在PL/SQL中使用if elsif else。

sql

--根据职务，现有的工资，给7369涨工资
declare
    v_job emp.job%type;
    v_sal emp.sql%type;
    v_addsal v_sal%type;
begin
    select job,sal into v_job,v_sal from emp where empno = 7369;
    if(v_job = 'CLERK') then
        v_addsal := v_sal * 1,2;
    elsif (v_job = 'SALESMAN') then
        v_addsal := v_sal * 1.5;
    elsif (v_job = 'PRESIDENT') then
        v_addsal := v_sal * 1.7;
    elsif (v_job = 'MANAGER') then
        v_addsal := v_sal * 1,8;
    else
        v_addsal := v_sal * 2.0;
    end if;

    update emp set sal = v_addsal where empno = 7369;
    commit;
end;

循环控制

loop语句

循环用于多次执行某些语句。

主要有三种循环类型：

简单循环
for循环
while循环

循环的四个条件：1、初始条件；2、循环条件；3、循环体；4、迭代条件。

简单循环

sql

declare
    v_count number(3) := 1; --初始条件
begin
    loop
        dbms_output.put_line('v_count=' || v_count); --循环体
        v_count := v_count+1; --迭代条件
        exit when v_count > 10; --循环条件
    end loop;
end;

--利用loop循环，向users表插入10条数据，如果编号是奇数，性别为男；如果编号是偶数，性别为女
declare
    v_sex char(2);
    v_count number(3) := 1;
    v_name varchar2(10);
begin
    loop
        if (mod(v_count,2) = 0) then
            v_sex := '女';
        else
            v_sex := '男';
        end if;
        v_name := 'test' || v_count;
        insert into users values(v_count,v_name,v_sex);
        v_count := v_count + 1;
        exit when v_count > 10;
    end loop;
    commit;
end;

for循环

使用for循环，循环执行指定次数。

sql

for index in [reverse] lower_bound...upper_bound loop
    statement1;
    ...
end loop;

reverse是反转的意思，正常的循环计数器从小到大，使用reverse将计数器从大到小递减。

for循环使用的规则：

循环计数器不需要声明，Oracle自动为for循环声明计数器变量
只能在循环体内引用计数器
如果需要在循环体外引用计数器的值，可以使用一个变量记录计数器的值

sql

declare
    v_sex char(2);
    v_name varchar2(10);
begin
    for v_count in 1..100 loop
        if (mod(v_count,2) = 0) then
            v_sex := '女';
        else
            v_sex := '男';
        end if;
        v_name := 'test' || v_count;
        insert into users values(v_count,v_name,v_sex);
    end loop;
    commit;
end;

while循环

使用while循环，只要条件满足就执行循环。

sql

while condition loop
    statement1;
    statement2;
    ...
end loop;

sql

declare
    v_count number(3) := 1;
begin
    while (v_count <= 10) loop
        dbms_output.put_line('v_count=' || v_count);
        v_count := v_count + 1;
    end loop;
end;

异常处理

异常就是在Oracle数据库中运行时出现的错误，可能使用户设置的一些数据规则的违反（如约束），异常处理就是对这些异常的捕捉和处理。

在PL/SQL中出现的异常，如果没有处理，异常会被传递给调用环境，中断程序的执行。

PL/SQL程序会从发生异常的代码处中断，以后的代码是无法执行的。

捕获异常语法

exception
    when exception1 [or exception2...] then
        statement1;
        statement2;
        ...
    [when exception3 [or exception4...] then
        statement1;
        statement2;
        ...
    ]
    [when others then
        statement1;
        statement2;
        ...
    ]

异常出现在程序的执行部。在执行某些语句时，可能违反了某些数据库的规则或用户的规则，从而触发异常。异常在发生之后就不能继续执行程序，而是立刻进入到异常处理部分（exception）；在异常处理部分首先要捕获异常，如果异常被捕获到，就执行用户定义的异常处理程序，如果发生的异常没有被捕获到，就会被传递到程序的调用环境中，产生报错信息。

sql

declare
    v_job emp.job%type;
begin
    select job into v_job from emp where empno = 8001;
    dbms_output.put_line('v_job=' || v_job);
exception
    when no_data_found then
        dbms_output.put_line('执行的select语句没有查询到结果');
    when others then
        dbms_output.put_line('PL/SQL中发生了其它异常');
end;

当PL/SQL程序中，在begin部门的语句引起异常之后，就会进入到exception部分执行异常处理功能。

Oracle的异常类型分为三类

预定义异常，Oracle中已经预先为这种异常定义好了名称，在异常处理部分直接通过异常名称进行捕获。

预定义的一些异常名称：
- no_data_found，没有找到数据
- too_many_rows，数据太多
- invalid_cursor，失效的游标
- zero_divide，除数为0
- dup_val_on_index，唯一索引中插入重复值
- value_error，赋值异常

非预定义异常，也是因为违反了Oracle的规则，Oracle会产生报错信息（有错误编号和错误信息），但是Oracle并没有为这类错误定义好异常名称，那么可以自定义一种异常名称，并将这种异常名称和错误编号进行绑定。

sql

declare
    e_myexception exception; --自定义一种异常名称
    pragma exception_init(v_emp_remaining,-002292);
begin
    delete from dept where deptno = 10;
exception
    when v_emp_remaining then
        dbms_output.put_line('违反外键约束');
    when others then
        dbms_output.put_line('PL/SQL中发生了其它异常');
end;

用户定义异常，操作时，并没有违反Oracle的规则，而是违反了用户定义的规则。由于没有违反Oracle的规则，Oracle不会自己主动的产生报错信息，需要手动的提升一个异常。

sql

drop table sm_emp;
create table sm_emp(
    no char(4),
    name char(10),
    salary number(6,2),
    phone char(8)
);

insert into sm_emp values ('01','test',999.99,'3474907');
insert into sm_emp values ('02','test',999.99,'3474907');
insert into sm_emp values ('03','te',999.99,null);
commit;

declare
    e_phone_lost_exception exception;
    cursor cur_sm_emp is select * from sm_emp;
begin
    for v_rec_emp in cur_sm_emp 1oop
        if(v_rec_emp.phone is null) then
            v_name := v_rec_emp.name;
            raise e_phone_lost_exception;
        end id;
    end loop
exception
    when e_phone_lost_exception then
        dbms_output.put_line(v_name || '的电话为null');
    when others then
        dbms_output.put_line('PL/SQL中发生了其它异常');
end;

预定义异常和非预定义异常都是因为违反了Oracle的规则，Oracle会自动的产生异常；而用户自定义异常并没有违反Oracle的规则，Oracle不会自动的产生异常。

异常捕获相关的函数

在进行异常捕获时，在where子句中捕获大多数的预先可以预测到的异常。但是在数据库中存在太多的不可预期的异常。所以在程序中很可能异常发生时，没有捕获。这时需要借助where others then子句在处理这些的非预期的异常。

where others then可以捕获到预先没有定义的异常。为了有效的处理这些异常，以便在以后的程序中减少这些异常，通常会将这些异常写入错误日志表中。

为了能够记录发生的异常信息，Oracle提供了两个函数：

sqlcode，返回错误代码，number类型
sqlerrm，返回与错误代码关联的信息，varchar2类型

对于非预测的异常，如何处理？

用when others then捕获所有的非预期的异常，渠道错误编号和错误信息，记录到数据库中。

sql

drop table err_log;
create table err_log (id number(5) primary key,code number(10),message varchar2(255),datetime date);

select * from err_LOG;

declare
    v_code number(10);
    v_msg varchar2(255);
begin
    delete from dept where deptno = 10;
exception
    when others then
        --获取错误编号
        v_code := salcode;
        v_msg := sqlerrm;

        --把错误编号和错误信息插入到日志表中
        insert into err_log values(seq_errlog.nextval,v_code,v_msg,sysdate);
        commit;
        dbms_output.put_line('PL/SQL程序中遇到异常，请查询日志表获取详细信息');
end;

PL/SQL程序单元

PL/SQL程序，也叫PL/SQL程序单元，是数据库中命名的PL/SQL块。

主要有四类：

过程，执行特定操作
函数，进行复杂计算，返回计算的结果
包，将逻辑上相关的过程和函数组织在一起
触发器，事件触发，执行相应操作

块类型

匿名块

sql

[declare]
    变量，常量声明
begin
    语句;
[exception]
end;

过程

sql

procedure name is
    变量，常量声明
begin
    语句;
[exception]
end;

函数

sql

function name return datatype is
    变量，常量声明
begin
    语句;
    returen value;
[exception]
end;

触发器

sql

trigger name after|before is
    变量，常量声明
begin
    语句;
[exception]
end;

包

sql

package name
    函数声明;
    过程声明;
end;

package body name
begin
    函数定义;
    过程定义;
end;

匿名块

匿名块只使用一次的PL/SQL程序块
匿名块没有名称，也不被存储在数据库中，它不是PL/SQL存储程序单元
由PL/SQL的四个基本组成部分构成
在SQL*PLUS中通过“/”编译并且执行
不能被重复使用，当要执行时，需要重新编写并编译执行

sql

create or replace procedure cur_emp is
    cursor cur_emp is select * from emp;
    v_rec_emp emp%rowtype;
begin
    open cur_emp;
    fetch cur_emp into v_rec_emp;
    while (cur_emp%found) loop
        fetch cur_emp into v_rec_emp;
        dbms_output.put_line(v_rec_emp.empno || ',' || v_rec_emp.ename);
        fetch cur_emp into v_rec_emp;
    end loop;
    dbms_output.put_line('查询数据的总条数是：' || cur_emp%rowcount);
    close cur_emp;
end;

--调用过程
--在命令行中调用
exec cur_emp;
--在匿名块中调用
begin
    cur_emp;
end;

创建带有参数的存储过程。

sql

create or replace procedure add_dept(p_deptno number,p_dname varchar2,p_loc varchar2) is

begin
    insert into dept values(p_deptno,p_dname,p_loc);
    commit;
end;

--在匿名块中调用存储过程
--调用有参数的过程的不同方式
--1、按参数名称调用
declare
    v_deptno number(4) := 50;
    v_dname varchar2(20) := '销售部';
    v_loc varchar2(10) := '北京';
begin
    add_dept(p_loc => v_loc,p_dname => v_dname,p_deptno => v_deptno);
end;
--2、按参数的位置进行调用
declare
    v_deptno number(4) := 50;
    v_dname varchar2(20) := '销售部';
    v_loc varchar2(10) := '北京';
begin
    add_dept(v_deptno,v_dname,v_loc);
end;
--3、按混合方式调用
declare
    v_deptno number(4) := 50;
    v_dname varchar2(20) := '销售部';
    v_loc varchar2(10) := '北京';
begin
    add_dept(v_deptno,p_loc => v_loc,p_dname => v_dname);
end;

作为Oracle中过程的参数，除了有数据类型之外，还有一种特殊的类型，既输入输出类型（in，out，in out）。

sql

create or replace param_test(p_in in varchar2,p_out out varchar2,p_in_out in out varchar2) is

begin
    dbms_output.put_line('在过程中，p_in =' || p_in);
    dbms_output.put_line('在过程中，p_out = ' || p_out);
    dbms_output.put_line('在过程中，p_in_out = ' || p_in_out);

    --作为out类型的参数，可以在过程中被重新赋值，并且会被返回给调用者
    p_out := 'out类型的参数在过程中被重新赋值';
    p_in_out := 'in out类型的参数在过程中被重新赋值';
end;

declare
    v_in varchar2(100) := 'in类型参数的初始值';
    v_out varchar2(100) := 'out类型参数的初始值';
    v_in_out varchar2(100) := 'in out类型参数的初始值';
begin
    param_test(v_in,v_out,v_in_out);
    dbms_output.put_line('在过程中，p_in =' || p_in);
    dbms_output.put_line('在过程中，p_out = ' || p_out);
    dbms_output.put_line('在过程中，p_in_out = ' || p_in_out);
end;

存储过程完成的是一系列的操作，可以单独的调用一个过程，来完成某些操作。

函数：可以接收一个或多个参数，在函数中完成运算，最终返给用户一个结果（Oracle中的函数必须有返回结果）

sql

--根据职位和薪金，计算佣金的值
--函数的参数也有输入输出类型，但是out类型的参数使用不方便，所以在函数中只使用in类型的参数
create or replace function add_comm(p_job varchar2,v_sal emp.sal%type) return number is
    v_comm emp.comm%type;
begin
    if(p_job = 'CLERK') then
        v_comm := p_sal * 0.5;
    elsif(p_job = 'SALESMAN') then
        v_comm := p_sal * 0.6;
    elsif(p_job = 'PRESIDENT') then
        v_comm := p_sal * 0.7;
    elsif(p_job = 'ANALYST') then
        v_comm := p_sal * 0.8;
    else
        v_comm := p_sal * 1.0;
    end if;
    return v_comm;  --在函数中一定要有一个有效的return语句
end;

--调用函数
--1、无法在命令行中调用函数
--2、在匿名块中调用函数
declare
    v_res number(8);
    v_str varchar2(20) := 'test';
    v_length number(3);
begin
    v_res := add_comm('SALESMAN',2000);
    v_length := length(v_str);
    dbms_output.put_line('v_res=' || v_res);
    dbms_output.put_line('v_length=' || v_length);
end;
--3、在sql语句中使用函数
select ename,length(ename) 名字长度,job,sal,add_comm(job,sal) "佣金"
from emp;

参数模式

in	out	in out
默认	必须指定	必须指定
值被传递给子程序	返回到调用环境	传递给子程序，返回到调用环境
参数形式：常量	未初始化的变量	初始化的变量
可以是表达式，常量，或者是初始化的变量	必须是一个变量	必须是一个变量

函数与过程比较

过程	函数
完成某些特定的任务或工作	完成复杂的计算
作为一个独立的PL/SQL语句来执行	不饿能独立执行，必须作为表达式的一部分来调用
程序头部使用procedure声明	头部使用function声明
程序头不用描述返回类型	程序头部必须描述返回值的类型
不需要return语句	必须在程序执行主体中办好return语句，至少有一个有效的return语句
可以使用in/out/in out三种模式的参数	可以使用in/out/in out三种模式的参数（但是out/in out没有意义）
可以不返回值，也可以返回多个值，通过out/in out类型参数来返回	必须返回一个值，通过return语句返回，并且返回的值要与声明部分一致，也可以通过out类型的参数带出变量（但是通常情况下不会这么做）

函数和存储过程

函数的好处：

可以实现用简单的SQL语句不能实现的计算
提高查询的效率

函数和存储过程使用规则：

1、在SQL语句中只能使用函数，而不是过程
2、函数中不允许DML语句，在过程中可以使用DML语句
3、函数的形参可以为in(out/in out)
4、在函数中必须返回Oracle支持的数据类型，而不是PL/SQL支持的数据类型

在SQL语句中调用函数

任何有效的SQL子句中
select命令的选择列表
where和having条件子句
order by，和group by子句
insert命令的values子句
update命令的set子句

Oracle的存储程序单元

在Oracle中，除了单次执行的PL/SQL匿名块以外，更多的使用的是PL/SQL程序（也就是PL/SQL的子程序）。

PL/SQL子程序就是数据库中命名的PL/SQL块，可以接收参数，也可以传出计算结果。

PL/SQL子程序主要有两种类型：

存储过程（proceduer）：用来完成某些操作的任务
函数（function）：用来做复杂的计算

PL/SQL子程序，是基于PL/SQL块的结构的，只是比PL/SQL块多了子程序头部的定义。使用PL/SQL子程序，使得PL/SQL程序易于维护，而且可以重复的使用。

游标

游标是一段私有的SQL工作区，也就是一段内存区域，用于暂时存放受到SQL语句影响到的数据。

在执行增删改查语句时，Oracle都会开辟一块内存空间，用来暂时存放受到SQL语句影响的数据。这块内存空间就被称为游标区域，可以借助于游标来分析这些受到影响的数据。

游标的分类：

隐式游标：被用于描述执行的SQL命令。在PL/SQL中出现的SQL语句，Oracle都会为其分配一段私有的内存空间，也就是游标区域。所有的DML语句或PL/SQL select语句都有隐式游标。
显示游标：由开发人员通过程序显示控制，用于从表中取出多行数据，并将多行数据一行一行的单独进行处理。

两种游标具有相同的属性，可以使用游标属性取得SQL语句的相关信息。

游标属性包括四种：

%rowcount，受SQL影响的行数
%found，Boolean值，是否还有数据
%notfound，Boolean值，是否已无数据
%isopen，游标是否打开

显示游标和隐式游标都有以上四种属性。但是使用方法和含义却不相同。

在使用游标时，需要使用游标名称作为前缀。但是隐式游标没有名称，所以在使用隐式游标时采用统一的一个名称SQL。

sql%rowcount，受SQL影响的行数
sql%found，Boolean值，是否还有数据
sql%notfound，Boolean值，是否已无数据
sql%isopen，总是为false

sql

declare
    v_count number(3);
begin
    delete from emp where deptno = 10;
    v_count := sql%rowcount;
    dbms_output.put_line('被删除的数据的条数是：' || v_count);
end;

显示游标的使用

可以用于暂存查询取出的多行结果，然后一行一行的处理
显示游标就是专门用来查询多条数据的
按行处理查询返回的多行结果
显示游标首先将查询出的多行数据暂存在游标区域中，然后再PL/SQL中借助于循环语句手动的控制游标的多行操作，每次取出一条进行处理，直到取出游标中所有的数据为止。

sql

declare
    --1、声明游标，一个显示游标，就是和一个有效的select语句绑定的
    cursor cur_emp is select * from emp;
    v_rec_emp emp%rowtype;
begin
    --2、打开游标，就是执行了游标绑定的SQL语句，并且把受到影响的数据放入到了游标区域中
    open cur_emp;

    --3、取出游标中的一条数据装入记录类型的变量中
    fetch cur_emp into v_rec_emp;

    --从记录类型的变量中取出查询的数据
    dbms_output.put_line(v_rec_emp.empno || ',' || v_rec_emp.ename);

    --关闭游标，清空游标区域
    close cur_emp;
end;

使用loop循环遍历和%found搭配使用分析游标数据

sql

declare
    cursor cur_emp is select * from emp;
    v_rec_emp emp%rowtype;
begin
    --游标在没有之前，在关闭之后，是无法使用的，就会导致无效的游标错误
    --在关闭之后，如果需要重新使用游标，需要重新打开游标
    open cur_emp;
    fetch cur_emp into v_rec_emp;
    loop
        dbms_output.put_line(v_rec_emp.empno || ',' || v_rec_emp.ename);
        fetch cur_emp into v_rec_emp; --装入另一条数据
        exit when cur_emp%notfound;  --当游标中没有数据时，退出循环
    end loop;
    dbms_output.put_line('查询数据的总条数是：' || cur_emp%rowcount);
    close cur_emp;
end;

使用while循环和%found搭配使用分析游标数据

sql

declare
    cursor cur_emp is select * from emp;
    v_rec_emp emp%rowtype;
begin
    open cur_emp;
    --在没有执行fetch操作之前，游标中是没有数据的，也就是cur_emp%found为false。
    fetch cur_emp into v_rec_emp;
    while (cur_emp%found) loop
        fetch cur_emp into v_rec_emp;
        dbms_output.put_line(v_rec_emp.empno || ',' || v_rec_emp.ename);
        fetch cur_emp into v_rec_emp;
    end loop;
    dbms_output.put_line('查询数据的总条数是：' || cur_emp%rowcount);
    close cur_emp;
end;

使用for循环，可以简化游标的开发，Oracle会自动声明记录类型的变量，Oracle会自动的open,fetch,close游标

sql

declare
    cursor cur_emp is select * from emp;
begin
    --Oracle会自动的声明记录类型的变量v_rec_emp，类型是emp%rowtype
    for v_rec_emp in cur_emp loop
        dbms_output.put_line(v_rec_emp.empno || ',' || v_rec_emp.ename);
    end loop;
end;

触发器

触发器类似于函数和过程，同样是具有声明部分、执行部分和异常处理部分的命名PL/SQL块。但与过程、函数不同的是，触发器是在事件发生时隐式的运行的，并且触发器不能接收参数；而过程、函数是用户显示调用的，可以接收参数。

运行触发器的方式叫做触动（firing），指在特定的事件发生的时候（前或后）自动运行定义好的PL/SQL程序。

触发的事件可以是多数据库表的DML操作（insert、update或delete）或某个视图的操作。

触发的事件也可以是系统事件，例如数据库的启动和关闭，以及一些DDL操作。

触发器被作为触动触发器的事务的一部分，所以在触发器中不可以使用结束事务的事务控制语句。

触发器主要用于下列情况：

安全性方面，确定用户的操作是否可以继续执行
产生对数据值修改的审计，将修改的信息记录下来，产生数据改动记录
提供更灵活的完整性校验规则，能根据复杂的规则校验数据
提供表数据的同步复制，使多个表的数据同步
事件日志记录，记录数据库的重要操作

触发器类型

在Oracle数据库中主要有两种触发器类型

DML触发器
系统触发器

DML触发器

DML触发器由DML语句触发，并且语句类型决定了DML触发器的类型。

DML触发器类型主要包括insert、update、delete三种触发器。

操作对象：表或视图
触发的时机包括：对表来说由before或after触发，对视图来说由instead of
触发范围包括：行级触发或语句级触发。行级触发时在每行数据操作时都会触发执行
可以设置when子句，决定触发后是否执行触发器的执行部分；如果不设置when子句，那么只要事件触发，就执行程序体

语句级DML触发器语法格式

sql

create [or replace] trigger trigger_name
    timing
        event1 [or event2 or event3]
            on table_name
        when conditions
[declare]
    --同样可以声明变量等
begin

end;

timing：表示触发实际可以是after或before
event1：表示触发事件，例如：insert，delete，update
when：表示执行触发器的条件
tirgger_body：触发器的执行体

sql

--向emp表中插入一条数据，会自动激活触发器的工作
create or replace trigger secure_emp
    before insert on emp
begin
    --如果当前的时间是星期六或星期日，或时间不在8:00-18:00之间
    if(to_char(sysdate,'dy') in ('星期六','星期日') or
      (to_char(sysdate,'hh24:mi') not between '08:00' and '18:00')) then
      --提升一条用户自定义的错误信息，通过这个错误信息，中断用户的操作
      raise_application_error(-20500,'你只能在工作时间对表进行操作');
    end if;
end;

注意：

raise_application_error：这一语句升起一个用户定义错误，显示一条用户定义提示。
错误号必须在当在-20000..-20999之间

sql

create or replace trigger secure_emp_2
    before insert or update or delete on employees
begin
    --如果当前时间是周六或周日，或者时间不在8:00-18:00之间
    if(to_char(sysdate,'dy') in ('SAT','SUN')) or (to_char(sysdate,'hh24') not between '08' and '18') then
        if deleting then
            raise_application_error(-20502,'你只能在工作时间删除员工表的数据');
        elsif inserting then
            raise_application_error(-20500,'你只能在工作时间插入员工表的数据');
        elsif updating ('SALARY') then
            raise_application_error(-20503,'你只能在工作时间更新员工表的数据');
        else
            raise_application_error(-20504,'你只能在工作时间操作员工表的数据');
        end if;
    end if;
end;

行级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 conditions]
[declare]
    --同样可以声明变量等
begin

end;

for each row：表明对表中的每行数据操作时都会触发这个触发器。referencing子句是说明触发器替换值的前缀名，默认替换前的前缀名为old，替换后的前缀名为new。也可以自己声明替换前后变量的前缀规则。

sql

create or replace trigger restrict_salary
    before insert or update of sal on emp
    for each row
begin
    --如果新插入的工作不是'AD_PRES'，'AD_VP'，并且新的工资 > 15000
    if not (:new.job in ('AD_PRES','AD_VP')) and :new.sal > 15000 then
        raise_application_error(-20202,'员工不能有这么高的工资');
    end if;
end;

--不允许降低员工的工资
create or replace trigger restrict_salary
    before update of sal on emp
    for each row
begin
    --新的工资小于老的工资
    if not (:new.sal < :old.sal) then
        raise_application_error(-20202,'黑心的资本家，你不能降低员工的工资');
    end if;
end;

创建触发器，解决外键约束问题

sql

--在插入员共表记录之前，首先判断新插入的部门编号是否存在；
--如果新的部门编号不存在，则首先向部门表中插入该部门
create or replace trigger restrict_salary
    before insert on emp
    for each row
--在触发器中要想声明变量，必须要加入declare
declare
    v_count number(4);
    v_dname varchar(10);
begin
    select count(*) into v_count from dept where deptno = :new.deptno;
    if(v_count < 1) then
        v_dname := '部门-' || :new.deptno;
        insert into dept values (:new.deptno,v_dname,'北京');
    end if;
end;

通过触发器，来实现级联删除的操作。

sql

--在删除部门时，如果该部门有员工，把该部门的所有员工也删除掉
create or replace trigger restrict_salary
    before delete on dept
    for each row
begin
    --删除部门之前，首先删除员工表中的该部门的所有员工
    delete from emp where deptno = :old.deptno;
end;

视图上的instead of触发器

sql

create [or replace] trigger trigger_name
    instead of
        event1 [or event2 or event3]
        on view_name
    [referencing OLD as old | NEW as new]
    [for each row]
trigger_body

instead of：被用于视图，当对视图进行DML操作时，对视图的DML操作被转换为对另外一些表的操作。

sql

--创建视图
create or replace view view_empinfo
as
select e.*,e.* from emp,dept d
where e.deptno = d.deptno;

---创建触发器
create of replace trigger insert_view
    instead of insert on view_empinfo
    for each row
declare
    v_count number(1);
begin
    --判断向视图插入的部门是否存在，如果该部门不存在，则向部门表中插入新数据
    --如果该部门存在，则更新部门表的部门名称和位置
    select count(*) into v_count from dept where deptno = :nwe.deptno;
    if(v_count < 1) then
        --把一部分数据插入到dept表中
        insert into dept values(:new.deptno,:new.dname,:new.loc);
    else
        --把一部分数据插入到emp表中
        insert into emp values(:new.empno,:new.ename,:new.job,:new.mgr,:new.hiredate,:new.sal,:new.comm,:new,deptno);
end;

管理触发器

启用或者禁用某个触发器

sql

alter trigger trigger_name disable|enable;

启用或者禁用某个对象上的所有触发器

sql

alter table table_name disable|enable all triggers;

重编译触发器
sql
```
alter trigger trigger_name compile;
```

数据库系统级触发器

用户触发事件：

create、alter或drop命令
登录或退出数据库连接

系统触发事件：

启动、关闭数据库
特殊错误发生

DDL触发器语法

sql

create [or replace] trigger trigger_name
    timing
        [ddl_event1 [or ddl_event2 or ...]]
        on {database|schema}
trigger_body

可以使用DDL触发器强制用户执行DDL语句的规则，例如只让用户执行create table的操作。

系统事件触发器语法

sql

create [or replace] trigger trigger_name
    timing
        [database_event1 [or database_event2 or ...]]
        on {database|schema}
trigger_body

登录/退出触发器

sql

create or replace trigger logon_trig
after logon on schema
begin
    insert into log_trig_table(user_id,log_date,action)
    values(user,sysdate,'Logging on');
end;

create or replace trigger logoff_trig
before logoff on schema
begin
    insert into log_trig_table(user_id,log_date,action)
    values(user,sysdate,'Logging off');
end;

Oracle

Golang学习笔记

学习文档链接：Go入门学习

2021-06-17 Go

SpringCloud学习笔记

学习视频链接：遇见狂神说尚硅谷

2021-05-13 SpringCloud

SpringCloud

Oracle数据库学习笔记

简单的SQL语句

数据类型

SQL语句的书写规则

SELECT

函数

多表查询

事务

DML语句

建表

数据库对象

索引

视图

同义词

序列

数据库设计范式

PL/SQL

PL/SQL块的基本结构

PL/SQL块语法规则

PL/SQL中处理变量

嵌套块和变量作用域

PL/SQL中的函数

PL/SQL中的SQL命令

条件判断

循环控制

异常处理

PL/SQL程序单元

Oracle的存储程序单元

游标

触发器

DML触发器

视图上的instead of触发器

管理触发器

数据库系统级触发器

微信扫一扫：分享

你的赏识是我前进的动力