Oracle学习笔记

【菜鸟教程SQL】https://www.runoob.com/sql/sql-tutorial.html

一、数据库基本概念

1.1 构成

数据、字段、记录

1.2 特点

• 永久存储
• 有组织
• 可共享

1.2 DBMS

数据库管理系统（管理数据）：

• 软件
• 数据库
• 数据库管理员

1.3 数据库

二、Oracle安装与启动

2.1 版本

2.2 OEM数据库管理

优点

启动

2.3 SQL Plus

使用SQL Plus程序打开命令行窗口。

三、结构

3.1 逻辑存储结构

Code

数据库
  - 表空间1
  	- 段1
  	  - 区1
  	  	- 数据块
  	  - 区2
  	- 段2
  	- 段3
  - 表空间2
  - 表空间3

3.2 数据块

Oracle逻辑存储结构中的最小逻辑单位。

大小由SB_BLOCK_SIZE决定。

组成：

Code

数据块
  - 块头
  	- 数据库标题
  	- 表目录
  	- 行目录
  - 存储区
    - 自由空间
    - 行数据

3.3 数据区

由连续的数据块结合而成。

Oracle存储分配的最小单位。

3.4 段

数据段：存储表中所有数据

索引段：存储表最佳查询的所有索引数据

临时段：存储表排序操作期间建立的临时表的数据

回滚段：存储修改之前的位置和值

3.5 表空间

表空间是数据库的最大逻辑划分区域。

一个表空间有一个或多个数据据文件组成，一个数据文件只属于一个表空间。

表空间的大小是它对应的数据文件大小的总和。

默认创建的表空间

• 系统表空间
• 辅助表空间
• 撤销表空间
• 用户表空间

3.6 总结（重要）

【B站视频解释 5:18开始】https://www.bilibili.com/video/BV1AE411p79z?p=3

• 实例：在Oracle的数据库中可有多个实例，通常我们只用一个实例
• 用户：一个实例下有多个用户
• 表空间：一个实例下有个表空间，表空间是逻辑概念，一个表空间对应着一个或多个物理存储文件（.dbf或.ora）。也就是说表空间仅仅是一个概念，用户产生的数据存放在表空间（磁盘上对应目录）中仅此而已。
• 用户和表空间的关系：一个用户有一个默认表空间。一个表空间可以为多个用户作为默认表空间，但是用户和用户之间的数据是隔离的，数据不会混。
• Oracle数据源：
  • driver：oracle.jdbc.OracleDriver
  • url：jdbc:oracle:thin:@localhost:1521:实例名
  • username
  • password

TIP : 可以理解为用户就是MySQL中database的概念

• 模式：一个用户一般对应一个schema,该用户的schema名等于用户名，并作为该用户缺省schema。这也就是我们在企业管理器的方案下看到schema名都为数据库用户名的原因。Oracle数据库中不能新创建一个schema，要想创建一个schema，只能通过创建一个用户的方法解决(Oracle中虽然有create schema语句，但是它并不是用来创建一个schema的)，在创建一个用户的同时为这个用户创建一个与用户名同名的schem并作为该用户的缺省shcema。即schema的个数同user的个数相同，而且schema名字同user名字一一 对应并且相同，所有我们可以称schema为user的别名，虽然这样说并不准确，但是更容易理解一些。

四、数据类型

4.1 CHAR(n) 和 VARCHAR2(n)

字符串类型

CHAR(N)：固定长度（速度快）

VARCHAR2(N)：长度可变（耗空间）

4.2 NUMBER(m, n)

数字类型

m：总位数

n：小数位数

m-n：整数位数

例子：

number(6,2) ==> 1234.56

4.3 DATE 和 TIMESTAMP

时间类型

DATE：保存时间，不含毫秒

TIMESTAMP：精确到毫秒

4.4 CLOB 和 BLOB

大数据存储

CLOB：可以存放海量文字（推荐）

BLOB：存放图片、电影、音乐等二进制文件

五、CURD命令操作

5.1 创建表空间

相当于MYSQL中的数据库。

注意：只有sys用户具有创建表空间的权限，除非sys给其他用户授权。

SYS直接创建

sql

CREATE TABLESPACE LiYu
	-- 存盘位置
	DATAFILE 'G:\ORACLE\ORADATA\ORCL\LiYu.DBF'
	-- 存储大小
	SIZE 50M
	-- 本地管理
	EXTENT MANAGEMENT LOCAL
	-- 自动扩容
	AUTOALLOCATE;

SYS授权

sql

-- 创建用户
CREATE USER wbw IDENTIFIED BY Bow1024
	ACCOUNT UNLOCK
    DEFAULT TABLESPACE LiYu;

sql

-- 授权
GRANT CREATE TABLESPACE TO wbw;
-- 赋予DBA权限（可选）还有其他角色 CONNECT 和 RESOURCE 
GRANT DBA TO wbw;

5.2 表操作

5.2.1 CREATE 创建表

sql

CREATE TABLE 表名(
	字段名称 字段类型 [DEFAULT 默认值],
    字段名称 字段类型 [DEFAULT 默认值]
);

注意：

• 表名要以字母开头（不建议汉字）

• 长度1~30个字符

• 不能用保留字

其他关键字

• NOT NULL：非空
• PRIMARY KEY：主键

5.2.2 DROP 删除表

sql

DROP TABLE 表名称;

5.2.3 ALTER 修改表

ADD 添加列

sql

ALTER TABLE 表名 ADD(字段名 字段类型 DEFAULT 默认值, ...);

MODIFY 修改字段类型

sql

ALTER TABLE 表名 MODIFY(字段名 字段类型 DEFAULT 默认值);

RENAME 修改字段名称

sql

ALTER TABLE 表名 RENAME COLUMN 原名称 TO 修改后名称;

DROP 删除字段

sql

ALTER TABLE 表名 DROP COLUMN 列名称;

5.2.4 INSERT INTO 插入记录

单条插入

sql

INSERT INTO 表名(字段名, 字段名, ...) VALUES(值, 值, ...);

批量插入

Code

insert all into student values('张三', 18, to_date('2015-01-01','yyyy-mm-dd'))
into student values('李四', 17, to_date('1997-12-03','yyyy-mm-dd'))
into student values('王五', 17, to_date('2000-03-01','yyyy-mm-dd'))
select 1 from dual;

• 和mysql不同，这里要用 insert all into
• 和Mysql直接用逗号不一样，中间要用 into 表名 values(...)，但是不用逗号分割
• 最后一句要用 select 1 from dual;

5.2.5 UPDATE 修改记录

更新单条记录

sql

UPDATE 表名 SET 列名=新值 [条件];

批量更新

添加where条件。

5.2.6 DELETE FROM (TRUNCATE) 删除记录

删除指定条件记录，不加条件删除整个表：

sql

DELETE FROM 表名 [条件];

删除所有数据：

sql

TRUNCATE table 表名; -- 推荐

5.3 SELECT 查询数据

5.3.1 选择、投影、连接

• 选择：表中的一行记录
• 投影：表中的某几列
• 连接
  • 等值连接：表A的X列和表B的Y列值相等的组合成新的表
  • 自然连接：在AB两表中相同字段列上，进行等值连接

5.3.2 简单查询

sql

SELECT 【查询需求】FROM 表名称 [表别名] [查询条件];

查询需求

• DISTINCT：重复项过滤，只保留一条记录
• *：查询所有列
• 列名称 [AS] [列别名]：查询指定列，多个列用逗号分隔

查询表：

如果要查询多个表，FROM后面加上多个表名，用逗号隔开

5.3.3 子查询

即将一个select语句的查询结果，作为另一个select语句的条件的查询。

单行子查询

只返回一个数值，如使用MAX()函数，返回一个最大值。

多行子查询

返回多行记录，可以理解为返回一个临时表。

5.3.4 复杂查询

sql

SELECT子句 [ INTO 子句 ] 
FROM 子句
[ WHERE 子句 ] 
[ GROUP BY 子句] 
[ HAVING 子句 ] 
[ ORDER BY 列名 ASC|DESC ]

注意：

WHERE 后不能跟聚合函数

GROUP BY 后面可以跟聚合函数

ASC：升序、DESC：降序。可以有多个列，按顺序进行前后排序。

5.3.5 空值比较

sql

... where xxx is null;

5.4 高级查询

5.4.1 LIKE 模糊查询

sql

SELECT column_name(s)
FROM table_name
WHERE column_name [NOT] LIKE pattern;

在 SQL 中，通配符与 SQL LIKE 操作符一起使用。

SQL 通配符用于搜索表中的数据。

在 SQL 中，可使用以下通配符：

通配符	描述
%	替代 0 个或多个字符
_	替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist] 或 [!charlist]	不在字符

5.4.2 IN、ANY、ALL 范围关键字

IN

IN 操作符允许您在 WHERE 子句中规定多个值。

sql

SELECT column_name(s)
FROM table_name
WHERE column_name [NOT] IN (value1,value2,...);

ANY

查询符合条件中任意一个就可以。

ALL

查询符合所有条件的。

5.4.3 BETWEEN 范围关键字

BETWEEN 操作符选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期。

sql

SELECT column_name(s)
FROM table_name
WHERE column_name [NOT] BETWEEN value1 AND value2;

5.4.4 NULL 空值比较

NULL 值代表遗漏的未知数据。

默认地，表的列可以存放 NULL 值。

sql

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS [NOT] NULL

5.4.5 INNER JOIN 内连接

常用的多表关联查询，INNER关键字可以省略，当只使用JOIN时，表示内连接。

sql

SELECT column_name(s)
FROM table1
[INNER] JOIN table2
ON table1.column_name=table2.column_name;

注释：INNER JOIN 与 JOIN 是相同的。

5.4.6 LEFT JOIN 左外连接

sql

SELECT column_name(s)
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column_name=table2.column_name;

注释：在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN。

5.4.7 RIGHT JOIN 右外连接

sql

SELECT column_name(s)
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column_name=table2.column_name;

注释：在某些数据库中，RIGHT JOIN 称为 RIGHT OUTER JOIN。

5.4.8 FULL JOIN 全外连接

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.

FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

sql

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

5.4.9 NATURAL JOIN 自然连接

Oracle会将第一个表中的列与第二个表中的列具有相同名称的列进行自动连接。用户不需要明确指定列，由系统自动完成。

sql

SELECT empno,ename,job,dname
FROM table1
NATURAL JOIN table2
WHERE 条件;

5.4.10 自连接

自连接主要用在自参考表上显示上下级关系或层次关系。

5.5 统计

5.5.1 聚合函数

• AVG：平均数
• COUNT
• MAX
• MIN
• SUM
• VARIANCE：方差
• STDDEV：标准差

5.5.2 GROUP BY 函数

经常于聚集函数一起使用。

sql

SELECT job,avg(sal),sum(sal),max(sal),count(sal) from emp GROUP BY job;

5.5.3 HAVING 子句

通常与GROUP BY一起使用，吐过没有GROUP BY那么与WHERE效果一样。

但是HAVING子句中可以包含聚合函数，WHERE则不可以。

5.5.4 ORDER BY 排序

• ASC：升序（默认）
• DESC：降序

六、视图

一个虚拟表，视图并不在数据库中存储数据值。但是对视图进行修改数据，实际上就是再修改实际数据，修改实际表也会影响视图。

6.1 创建、修改

sql

CREATE [OR REPLACE] VIEW <视图名> [别名]
AS <查询子句>
[with read only]

6.2 查询

查询视图就和查询普通的数据表一样的操作

6.3 更新

可更新视图满足以下条件:

• 没有使用连接函数、聚合函数和组函数;
• 创建视图的 SELECT语句中没有聚合函数且没有 GROUP BY、 ONNECT BY、
  START WITDISTINCT子句及关键字
• 创建视图没有包含只读属性。

然后就和操作普通表一样进行INSERT \ UPDATE \ DELETE

6.4 删除视图

sql

DROP VIEW 视图名;

七、索引

如果不用索引，那么查询的时候会把表里全部记录都读取出来。用了索引后，只读取索引列。然后根据ROWID快速找到表中对应记录。

7.1 创建索引

创建表时，如果表中包含唯一关键字或主关键字，会自动为这两种关键字所包含的列建立索引。

在创建索引时，Oracle首先对将要建立索引的字段进行排序，然后将排序后的字段值和对应记录的ROWID存储在索引段中。

7.1.1 注意事项

1. 索引应该建立在 WHERE子句频繁引用表列上,如果在大表上频繁使用某列或某几个列作为条件执行索引操作,并且检索行数低于总行数15%,那么应该考虑在这些列上建立索引。
2. 限制表中索引的个数。索引主要用于加快查询速度,但会降低DM操作的速度。索引越多,DM操作速度越慢,尤其会极大地影响 INSERT和DELETE操作的速度。因此,规划索引时,必须仔细权衡查询和DML的需求。
3. 指定索引块空间的使用参数。基于表建立索引时, Oracle会将相应表列数据添加到索引块。为索引块添加数据时, OraclePCTFREE会按照参数在索引块上预留部分空间,该预留空间时为将来的 INSERT操作准备的。如果将来在表上执行大量 INSERT操作,那么应该在建立索引时设置较大的 PCTFREE
4. 将表和索引部署到相同的表空间,可以简化表空间的管理;将表和索引部署到不同的表空间,可以提高访问性能。
5. 当在大表上建立索时,使用 NOLOGGING选项可以最小化重做记录使用选项可以节省重做日志空间、降低索引建立时间、提高索引并行建立的性能。不要在小表上建立索引。
6. 为了提高多表连接的性能,应该在连接列上建立索引。

7.1.2 B树索引（默认）

sql

CREATE INDEX 索引名 ON 表名(列名[ASC|DESC], ...) [pctfree 预留空闲空间值] [tablespace 索引段所在的表空间];

7.1.3 函数索引

让索引有一定的计算能力。

sql

CREATE INDEX 索引名 ON 表名(LOWER(ename));

7.1.4 位图索引

当列的基数很低时，为其建立B树索引显然不合适。“基数低”表示在索引列中，所有取值的数量比表中行的数据量少。（如，性别，只有男或者女两个选项。但是，记录却有10 000条。Oracle推荐当一个列的基数小于1%时，不建议用B树，而用位图索引）

sql

CREATE BITMAP INDEX 索引名 ON 表名(列名[ASC|DESC], ...);

7.2 维护索引

7.2.1 修改索引名

sql

ALTER INDEX 索引名 RENAME TO 新索引名;

7.3 删除索引

sql

DROP INDEX 索引名;

八、数据的完整性和约束

SQL 约束用于规定表中的数据规则。

如果存在违反约束的数据行为，行为会被约束终止。

约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

8.1 创建约束

8.1.1 SQL CREATE TABLE + CONSTRAINT 创建表时约束

sql

CREATE TABLE *table_name*
(
    *column_name1 data_type*(*size*) *constraint_name*,
    *column_name2 data_type*(*size*) *constraint_name*,
    *column_name3 data_type*(*size*) *constraint_name*,
    ....
);

在 SQL 中，我们有如下约束：

• NOT NULL - 指示某列不能存储 NULL 值。
• UNIQUE - 保证某列的每行必须有唯一的值。
• PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
• FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
• CHECK - 保证列中的值符合指定的条件。
• DEFAULT - 规定没有给列赋值时的默认值。

8.1.2 非空约束

空值（NULL），不存在的值。而不是0或者空字符串。

创建表时约束

sql

-- not null
create table Books {
	BookNo number(4) not null,
	bookname varchar2(20)
};

已经存在的表

sql

alter table books modify bookname not null;

8.1.3 主键约束

主键约束 = 非空约束 + 唯一性约束

sql

-- primary key
create table member3 {
	mid number primary key,			-- 主键约束 = 非空约束 + 唯一性约束
	name varchar2(20) not null,		-- 非空约束
	email varchar(20) constraint email_uk unique		-- constraint约束，约束名，唯一约束
}

8.1.4 唯一性约束

上面例子中的Email

8.1.5 外键约束

创表时候

Code

CREATE TABLE departments(
	dep_id NUMBER primary key,
	dep_name VARCHAR2(20)	
);

CREATE TABLE employees(
	emp_id NUMBER primary key,
	emp_name VARCHAR2(20),
	dep_id,
	foreign key(dep_id) references departments(dep_id)
);

创表之后

sql

alter table 表名
add constraint 约束名
foreign key(本表列名)
references 其他表名(其他列名)

如果两个列名相同，第二个可以省略。

• 在定义外键约束时，还可以通过关键字NO ACTION ，那么当删除被引用表中被引用列的数据时将违反外键约束。
• 使用SET NULL关键字，那么当删除被引用表中被引用列的数据时，外键表中外键列被设置为NULL。要注意外键列必须要支持NULL值
• 使用CASCADE关键字，那么当删除被引用表中被引用列的数据时，外键表中对应的数据也将被删除（即，级联删除）

8.2 删除约束

sql

alter table 表名 drop constraint 约束名;

8.3 禁用约束

如果直到要插入的数据都是符合条件的，那么可以在插入的时候先关闭约束检查。以达到提高性能。

1. 在定义约束时禁用（disable）

   sql

   create table 表名 (
   	Age int constraint Age_CK check (age > 0 and age < 120) disable
   )

2. 禁用已经存在的约束

   sql

   alter table 表名 disable constraint 约束名;

8.4 激活

sql

alter table 表名 enable [novalidate | validate] constraint 约束名;

• novalidate：表示在激活约束时不验证表中已经存在的数据是否满足约束，如过没有该关键字，或者使用validate关键字，则在激活的时候检查。

九、PL/SQL

9.1 运算符

9.1.1 算术运算符

Code

+
-	（对日期也有效）
*
/
**	（指数）
||	（连接字符）

extract(year from date1)，改函数用来取date1的年部分。

9.1.2 关系运算符

Code

=、 <>或!= 、 <、 >、 >=、 <=
BETWEEN ... END ...
IN ...
LIKE ...
... IS NULL

9.1.3 逻辑运算符

Code

AND
OR 
NOT

9.1.4 其他

Code

:=	赋值符号

9.2 变量、常量、数据类型

9.2.1 定义变量、常量

1. 变量

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

   长度并不是所有类型都有的。

2. 常量

   <常量名> constant <数据类型>:=<常量值>;

3. 初始化：如果没有赋值，那么自动为NULL

9.2.2 基本数据类型

数值类型

• NUMBER、NUMBER(P, S)：整数和浮点数
• PLS_INTEGER、BINARY_INTEGER：只能存整数

字符类型

• VARCHAR2(maxlength)：maxlength最大值是32767字节，数据库里的是4000字节。不能赋值给数据库的，只能给LONG。
• CHAR(maxlength)：最大长度32767字节，默认长度是1。后面空格补齐
• LONG：可变字符串
• NCHAR、NVARCHAR2：PL/SQL8.0以后加入的

日期类型

• DATE：7字节，分别存世纪、年、月、天、时、分、秒

布尔类型

• BOOLEAN：取值为TRUE,FALSE,NULL

9.2.3 特殊数据类型

%TYPE

根据已有字段数据类型自动推断

Code

var_name 表名.列名%TYPE

RECORD

可以理解为自定义结构类型，可以存储由多个列值组成的一行数据。

要先定义，才能声明（和C语言的结构体那样）

Code

type my_record_type is record (
  变量名1 数据类型 [not null][:=默认值],
    ...
  变量名n 数据类型 [not null][:=默认值]
);

%ROWTYPE

根据已有记录行自动推断类型

Code

var_row 表名%ROWTYPE

9.3 基本程序结构和语句

9.3.1 程序块

PL/SQL程序以块为单位

1. 声明部分
2. 执行部分
3. 异常处理部分

Code

[DECLARE]
BEGIN
[EXCEPTION]
END

示例：

Code

SQL> declare
	  a int:=100;
	  b int:=200;
	  c number;
	begin
	  c:=a+b;
	  dbms_output.put_line(c);
	exception
	  when zero_divide then
        dbms_output.put_line("除数不能为0");
	end;

9.3.2 选择语句

1. if…then

   Code

   BEGIN
   	if 1 > 0 then
   		dbms_output.put_line('yes');
   	end if;
   END;

2. if…then…else

   Code

   BEGIN
   	if -1 > 0 then
   		dbms_output.put_line('yes');
   	else 
   	    dbms_output.put_line('no');
     end if;
   END;

3. if…then…elsif

   Code

   DECLARE
     num NUMBER:=0;
   BEGIN
     if num > 0 then
       dbms_output.put_line('>');
     elsif num = 0 then
       dbms_output.put_line('==');
     end if;
   END;

9.3.3 循环语句

1. while

   Code

   DECLARE
     num NUMBER := 0;
   BEGIN
     while num < 10 loop
       dbms_output.put_line(num);
       num := num + 1;
     end loop;
   END;

2. for

   Code

   BEGIN
     for i in reverse 5..10 loop
   	  dbms_output.put_line(i);
     end loop;
   END;
   
   输出：
   10
   9
   8
   7
   6
   5

   其中，[reverse]可选，不写就是递增。写了就是递减。后面给一个范围。

9.3.4 选择和跳转

1. case

   Code

   DECLARE
   	num NUMBER := 2;
   BEGIN
   	case num
   		when 1 then
   			dbms_output.put_line(1);
   		when 2 then
   			dbms_output.put_line(2);
   			dbms_output.put_line('...');
   		when 3 then
   			dbms_output.put_line(3);
   		end case;
   END;

2. GOTO

   Code

   DECLARE
   	num NUMBER := 0;
   BEGIN
   	while num < 5 loop 
   		<<top>>
   		num := num + 1;
   		if num = 3 then
   			goto top;
   		end if;
   		dbms_output.put_line(num);
   	end loop;
   END;
   
   输出：
   1
   2
   4
   5

9.3.5 异常

1. 异常处理语法（及自定义异常）

   • 声明异常
   • 为内部异常命名
   • 异常定义
   • 异常处理
   Code

   DECLARE
   	num NUMBER := 0;
   	my_exception EXCEPTION; -- 声明异常
   	my_exception2 EXCEPTION;
   	my_exception3 EXCEPTION;
   	PRAGMA EXCEPTION_INIT(my_exception, -8888);	-- 为内部异常命名,关联错误编号(要负数)和异常名
   BEGIN
   	if num = 0 then
   		RAISE my_exception3;	-- 抛出异常
   	end if;
   
   EXCEPTION
   	when my_exception then	-- 接受处理异常
   		dbms_output.put_line('my_exception');
   	when my_exception2 then
   		dbms_output.put_line('my_exception2');
   	when others then
   		dbms_output.put_line('other');
   END;
   
   输出：
   other

2. 预定义异常

   系统内部预定义的异常。（太多了，去百度查把）

3. 空操作和空值

   即，占位。可以在IF语句中直接用NULL来占位，表示什么都不做。

   Code

   DECLARE
   	num NUMBER := 0;
   BEGIN
   	if num = 0 then
   		NULL;
   	end if;
   	dbms_output.put_line('hello oracle');
   END;

9.4 系统内置函数

Code

from dual	-- dual是一个临时表，系统内置的。用这些函数要from表，所以用一个临时表来计算

9.4.1 字符类

ASCII(ch) 字符转数字

用于返回对应字符的ASCII码

CHR(i) 数字转字符

用于返回ASCII码对应的字符

CONCAT(s1,s2) 字符串连接

将S2连接到S1后面

INICAP(s) 首字母大写

将s中的单词，首字母大写。单词之间用空格、控制符、标点符号来区分。

INSTR(s1,s2[,i] [,j]) 查询子字符串

查找s2在s1中，从i位置开始，第j次出现的位置。（下标从1开始，如果i是负数，则反向查找。但是返回的下标还是正序）。没找到返回0

LENGTH(s) 计算长度

如果s为null，那么返回值为null

LOWER(s) / UPPER(s) 小/大写

所有字母转为大小写

LTRIM(s1,s2) / RTRIM / TRIM 左/右/左右清除字符串

删除字符串s1中左侧/右侧/两端的s2（如果不写s2，那么默认空格）

REPLACE(s1,s2[,s3]) 替换

用s3替换s1中出现s2的所有地方（s3默认是空字符串）

SUBSTR(s,i[,j]) 取子串

截取字符串s，从i开始，长度为j的字符串（如果省略j，默认取到尾部）

9.4.2 数字类

ABS(n) 绝对值

CELL(n) 向上取整

COS(n) 余弦【2PI = 360°】

EXP(n) n次幂

FLOOR(n) 向下取整

LOG(n1,n2) 对数

MOD(n1,n2) 取余

POWER(n1,n2) 次方

ROUND(n1,n2) 四舍五入

n2是小数点右边几位（要整数）。如果n2是负数，截尾在小数点左边

SIGN(n)

负数返回-1，0返回0，正数返回1

SQTR(n) 开方

TRUNC(n1,n2) 截取小数

截取小数点n2位的数，默认0。如果n2是负数，截尾在小数点左边

9.4.3 日期和时间类

ADD_MONTHS(d,i)

当前日期d加上i个月

LAST_DAY(d)

返回d日期，该月最后一天

NEW_TIME(d1,t1,t2)

时区转换？将t1时区中的d1时间转换成t2时区的时间

SYSDATE() 当前时间

9.4.4 转换类

to_char(日期变量, ‘YYYY-MM-DD’)

9.5 自定义函数

函数必须有返回值！

9.5.1 函数的定义

Code

create or replace function my_fun(num NUMBER) return number is
	var_tmp number;
BEGIN
	var_tmp := num + 1;
	return(var_tmp);
END;

其中 or replace 可选，如果存在会替换。return 后面跟着类型，begin 和 end之间要有return语句。

9.5.2 函数的调用

Code

BEGIN
	dbms_output.put_line('new number: ' || my_fun(0));
END;

9.5.3 函数的删除

Code

drop function my_fun;

9.6 游标

类似C语言指针对查询结果集进行操作。

9.6.1 显示游标

声明游标

Code

cursor 游标名[(参数名 参数类型[:=默认值])...]
[return 返回类型]
is select语句;

返回语句可以不写，如果写了select查询结果的类型则必须要符合返回类型。

参数主要是用来放在select语句的where中用的。

打开游标

Code

open 游标名(参数列表);

打开以后，游标会指向select的结果集的第一个记录之前的位置。（有一个属性%found用来判断游标是否有数据）

读取游标

Code

fetch 游标名 into 变量名;

将游标指向下一个位置，并且把指向的记录赋值给变量。（最后一条记录之后的记录 和 第一条之前的记录都是不存在的。调用会报错）。一般配合循环语句while。

关闭游标

Code

close 游标名;

例子

Code

DECLARE
    -- 声明游标(这是一个游标变量，类型是后面select返回的记录类型)
    cursor cursor_stu(var_age NUMBER:=18) is select * from student where age = var_age;
   
    -- 定义临时存放游标的记录类型
    var_tmp student%ROWTYPE;
BEGIN
    -- 打开游标
    open cursor_stu(17);
    
    -- 读取游标（这里要先获取一次，移动到第一条记录，不然条件过不了）
    fetch cursor_stu into var_tmp;
    while cursor_stu%found loop
        dbms_output.put_line('姓名：'||var_tmp.name||';年龄：'||var_tmp.age||'出生日期：'||var_tmp.birthday);
        fetch cursor_stu into var_tmp;	-- 读取下一条
    end loop;
    
    -- 关闭游标
    close cursor_stu;
END;	-- 注意结尾END后面还有一个分号（;）

9.6.2 隐式游标

这个一般适用于update、delete这样的处理数据的操纵语句。（就是那个结果：Affected rows: 1）

使用时需要在属性前面加上隐式游标的默认名称——SQL

Code

BEGIN
	update student set age = 18 where age = 12;
	if SQL%notfound then
		dbms_output.put_line('没有数据被修改');
	else
		dbms_output.put_line('修改的行数：'||SQL%rowcount);
	end if;
END;

9.6.3 游标变量

声明游标变量

Code

type 游标变量类型名 is ref cursor
return 返回类型;

• 返回类型是记录类型

打开游标变量

Code

open 游标变量 for select语句;

例子

Code

DECLARE
	-- 定义游标变量类型（type_stu_ref是一个游标类型，类型是student%ROWTYPE）
	type type_stu_ref is ref cursor return student%ROWTYPE;
	-- 定义游标变量（cursor_stu是一个游标，类型是type_stu_red）
	cursor_stu type_stu_ref;	
	
	-- 自定义类型（匹配游标变量类型的）
	type my_type is RECORD(
		name student.name%TYPE,
		age student.age%TYPE,
		birthday student.birthday%TYPE
	);
	-- 利用自定义临时变量（var_tmp%TYPE 和 my_type 和 type_stu_ref 都是匹配的）
	var_tmp my_type; 
BEGIN
	-- 打开游标
	open cursor_stu for select * from student where age = 17;
	
	--读取游标
	fetch cursor_stu into var_tmp;
	while cursor_stu%found loop
		dbms_output.put_line('姓名：'||var_tmp.name||';年龄：'||var_tmp.age||'出生日期：'||var_tmp.birthday);
		fetch cursor_stu into var_tmp;
	end loop;
	
	-- 关闭游标(使用关闭后的游标是非法的)
	close cursor_stu;
END;

9.6.4 游标表达式（暂无，书上没例子）

Code

CURSOR(subquery)

十、程序包

10.1 程序包的规范

其实就类似JAVA的接口或者抽象类，只是声明了变量、类型、游标、函数、过程等，而没有具体的实现。

Code

create [or replace] package 程序包规范名 is
    [declare_variable];
    [declare_type];
    [declare_cursor];
    [declare_function];
    -- 如：function fun_avg_sal(num_deptno number) return number;
    [declare_procedure];
    -- 如：procedure pro_regulate_sal(var_job varchar2);
end [包名];

10.2 程序包的主体

在主体中实现对应“规范”中声明的函数和存储过程，就像JAVA中对接口或者抽象类进行实现。如果没有实现，直接调用规范里的声明会报错。

Code

create [or replace] package body 程序包规范名（这里同名噢，前面多了一个body关键字） is
    [程序包主体的内部变量]
    [游标主体]
    [从“规范”中引入的函数头部声明]
    function fun_avg_sal(num_deptno number) return number is
    	num_avg_sal number; -- 定义内部变量
    begin
        spl语句;
    [exception]
        ...
    end [fun_avg_sal];
    -- 过程也是一样
end 包名;

十一、存储过程

和函数基本一样，就是少了一个返回类型。

不能在SQL语句中被调用。

11.1 创建 存储过程

Code

create [or replace] procedure 存储过程名[(参数列表...)] is | as
begin
	plsql语句;
[exception]
	...
end [存储过程名];

定义时候空参不需要（），但是调用的时候要加（）

11.2 调用 存储过程

Code

call 存储过程名;

也可以在PL/SQL块中使用：

Code

begin
	存储过程名;
end

11.3 删除 存储过程

Code

drop 存储过程名;

十二、触发器

就是在指定对象上执行某操作的前后进行一些自己的操作，或者用自己的操作直接替换掉该操作。

12.1 创建 触发器

Code

create [or replace] trigger 触发器名
	[before | after | instead of] 触发事件（如：insert、update、create等,多个用or连接）
	on 表名 | 视图名 | 用户模式名 | 数据库名
	[for each row [when 条件]]
begin
	plsql语句;
end 触发器名;

解释：在（表 | 视图 | 用户模式 | 数据库）上进行insert、update、create等操作时，在执行该操作的前（before）、后（after）执行的plsql，或者用plsql语句代替该操作。如果设置了行级触发器（for each row）那么对于每一行的操作都会触发一次，还可以增加条件，使符合条件的执行触发器。如果米有指定行级触发器那么不管操作了多少行数据，都只会触发一次。

12.2 :new 和 :old

可以通过:new.字段名获取更新后的值。

通过:old.字段名获取更新前的值。

12.3 启用/禁用 触发器

Code

alter trigger [schema.]触发器名 DISABLE | ENABLE;

12.4 删除 触发器

Code

drop trigger 触发器名;