Oracle必备知识

超、凢脫俗 2021-10-03 02:40 730阅读 0赞

#### SQL分为三种类型 ####

> DML: Data Manipulation Language 数据处理语言  
> DDL: Data Defintion Language 数据定义语言  
> DCL: Data Control Language 数据控制语言

*  授权访问权限、撤销访问权限、提交事务处理、事务处理回退、设置保存点、对数据路特定部分进行锁定
 *  oracle注释：`---`
 *  凡是空值参与运算，结果都为空(null)
 *  取别名：`select t_name as name from table`
 *  连接符：`||` 把列于列字符连接在一起/字段合并
 *  去除重复行：`select distinct 列名 from table`
 *  时间转换：`to_char(列名,'yyyy-mm-dd')`
 *  两者之间：`between ... and ...`
 *  包含：`in`
 *  模糊查询: `like`
 *  是空：`is null`
 *  不: `not`
 *  like中查询下划线\_： `select * from table where 列名 like '%\_%' escape '\'`
 *  排序：`order by` `desc: 倒序` `asc: 正序` 默认正序

#### 单行函数：字符、通用、数值、日期、转换 ####

*  字符函数：大小写控制函数、字符控制函数
    
     *  小写: `lower(列名)`
     *  大写: `upper(列名)`
     *  单词首字母大写: `initcap(列名)`
     *  连接字符串: `concat(列名,列名)`
     *  截取: `substr(列名,a,b)` a：第几位开始(下标从1开始); b：几位值
     *  判断某个字符在字符串中的首个位置: `instr(列名,'值')`
     *  长度: `length(列名)`
     *  左对齐: `lpad(列名,num,'*')` num: 多少位为基础; \*: 用来补充
     *  右对齐: `rpad(列名,num,'*')`
     *  移除首尾: `trim('移除值' from 列名)`
     *  将一个字符串中的一个字符替换成另一个字符: `replace(列名,'要替换的值','替换成的值')`
 *  数字函数：
    
     *  四舍五入: `round()`
     *  截断: `trunc()`
     *  求余: `mod()`
 *  通用函数:
    
     *  `nvl(a,b)`: 当a为null时，展示b
     *  `nvl2(a,b,c)`: 当a不为null返回b,为null 返回c
     *  `nullif(a,b)`: 当a等于b时返回null,不等返回a
     *  `coalesce(a,b,c)`: 当a有值时返回a,当a为null时,返回b,当a、b为null时,返回c
 *  条件表达式 if - then - else
    
     *  `case`: `case t_code when code then name end`: 当t\_code 等于 code 返回 name
     *  `decode`: `decode(t_name,name,code)`: 当t\_name 等于 name 返回 code
 *  日期 (当前时间sysdate)
    
     *  在日期上加上或减去一个数字结果仍为日期。
     *  两个日期相减返回的日期之间相差的天数。(日期尽量不做加运算,无意义)
     *  可以用数字除24,向日期中加上或减去天数
     *  两个日期相差的月数: `months_between(create_time,update_time)`
     *  向指定日期中加上若干月数: `add_months(create_time,2)`
     *  指定日期的下一个星期对应的日期: `next_day(sysdate,'星期三')`
     *  本月最后一天: `last_day(sysdate)`
     *  日期四舍五入: `round()`
     *  日期截取: `trunc()`
 *  转换类型
    
     *  varchar2 or char ———— number
     *  varchar2 or char ———— date
     *  date ———— varchar2
     *  number ———— varchar2
     *  `to_date()、to_char()、to_number()`
 *  to\_char函数
 *  to\_number 函数
    
     *  字符串转为数字: `select to_number('1,234,567.89','999,999,999.99')+100 from dual;`
 *  时间格式参数
    
     *  年: `YEAR`
     *  ISO标准的四位年份: `IYYY`
     *  四位年份: `YYYY`
     *  年份的最后三位: `YYY`
     *  年份的最后两位: `YY`
     *  年份的最后一位: `Y`
     *  季度(1,2,3,4): `Q`
     *  月,例子 03 : `MM`
     *  月份简写: `Mon`
     *  同Mon: `Month`
     *  罗马数字月: `RM`
     *  该月的第几个星期: `W`
     *  该年中的第几个星期: `WW`
     *  Iso标准的年中的第几周: `IW`
     *  一周中的星期几,星期一代表第一天: `D`
     *  当天时星期几: `DAY`
     *  同DAY: `Dy`
     *  该月中的第几天: `DD`
     *  该年中的第几天: `DDD`
     *  小时,按12小时计: `HH`
     *  小时,按24小时计: `HH24`
     *  分: `MI`
     *  秒: `SS`
     *  当天零点到现在经过的秒数: `SSSSS`
     *  毫秒: `SSxFF`
     *  前缀,可以在参数前面，去掉前后无用的空格和0
        
         *  例如 `to_char(sysdate,'MM') 结果是03,如果换成FM 变成to_char(sysdate,'FMMM') 结果就是3`
 *  to\_char()将日期date转化成varchar2字符串的语法格式：
    
     *  得到当前时间,24小时制: `TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') --结果：'2019-11-20 12:23:41'`
     *  12小时制,自动根据时间判断午前还是午后,午前(AM|A.M.)或午后(PM|P.M.): `TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS AM') --结果：'2019-11-27 04:58:31 下午'`
     *  公元(AD|A.D.)或公元前(BC|B.C.)自动根据时间判断是AD还是BC: `TO_CHAR(SYSDATE,'YYYY-MM-DD AD') --结果：2019-11-27 公元`
     *  CC与SCC 世纪数,使用SCC时,公元前为负数,公元后为整数;使用CC结果为SCC的绝对值: `TO_CHAR(SYSDATE,'CC') --结果：'21';TO_CHAR(SYSDATE,'SCC') --结果：' 21'`
 *  内连接: 合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表的不匹配的行。
 *  外连接:两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的行，这种连接称为左(或右)外连接，没有匹配的行时，结果表中相应的列为空(null)，外连接的where 子句条件类似于内部连接，但连接条件中没有匹配行的表的列后面要加外连接运算符，即用圆括号括起来的加号(+).
 *  分组函数：分组函数作用于一组数据，并对一组数据返回一个值。
    
     *  group by
     *  having 过滤分组结果集
 *  组函数
    
     *  avg: 平均
     *  count: 计数,返回不为空的数据 需要验证
     *  max: 最大
     *  min: 最小
     *  stddev：返回expr的样本标准偏差。它可用作聚集和分析函数。
     *  sum: 总和
     *  count(distinct 列名) : 返回列名非空且不重复的记录总数

不能在where子句中使用组函数  
可以在having子句中使用组函数  
多个子查询 any all

> 常见的数据库对象  
> \- 表：基本的数据存储集合，由行和列组成。  
> \- 视：从表中抽出的逻辑上相关的数据集合。  
> \- 序列：提供有规律的数值。  
> \- 索引：提高查询的效率。  
> \- 同义词：给对象起别名。

> 建表命名规则：  
> \- 必须以字母开头  
> \- 必须在1-30个字符之间  
> \- 必须只能包含A-Z,a-z,0-9,\_,$和\#  
> \- 必须不能和用户定义的其他对象重名  
> \- 必须不能是Oracle的保留字

> 创建表  
> \- create table 语句  
> \- create table 权限  
> \- 存储空间  
> \- 必须指定:表名、列名、数据类型  
> \- 建表时 小数类型 number(10,2) 代表十位,两位小数

第一种
    	create table emp1(
    		id number(10),
    		name varchar2(20),
    		salary number(10,2),
    		create_time date		
    	)
    	
    	第二种  复制emp1表并且修改字段名，还有一定数据，不要数据设置一定条件即可
    	create table emp2
    	as select id e_id,name e_name,salary e_salary,create_time
    	from emp1
    	可加where 条件

> 数据类型  
> \- varchar2(size):可变长字符数据  
> \- char(size):定长字符数据  
> \- number(p,s):可变长数值数据  
> \- date:日期型数据  
> \- long: 可变长字符数据，最大可达到2G  
> \- clob:字符数据，最大可达到4G  
> \- raw(long waw):原始的二进制数据  
> \- blob: 二进制数据，最大可达到4G  
> \- bfile:存储外部文件的二进制数据，最大可达到4G  
> \- rowid:行地址

> alter table 语句  
> \- 追加新的列：alter table emp1 add(email varchar2(20));  
> \- 修改现有的列：alter table emp1 modify(id number(10));  
> \- 为新追加的列定义默认值：alter table emp1 modify(salary number(10,2) default 200);  
> \- 删除一个列: alter table emp1 drop column email;  
> \- 重命名表的一个列名: alter table emp1 rename column salary to sal;  
> \- rollback;回滚

> 删除表 drop table emp3;  
> 数据和结构都被删除  
> 所有正在运行的相关事务被提交  
> 所有相关索引被删除  
> drop table 语句不能回滚

*  truncate table emp1;清空表数据 表结构还在 释放存储空间

> 修改表名称 rename emp2 to emp5; 不可回滚

*  多行注释/\*\*/
 *  创建表加数据: insert into ()select \* from table
 *  事务：一组逻辑操作单元，使数据从一种状态变换到另一种状态。
 *  使用savepoint 语句在当前事务中创建保存点。
 *  使用rellback to savepoint 语句或滚到创建的保存点。

> 什么是约束(Constraint)  
> \- 约束是表级的强制规定

*  五种约束
    
     *  not null——不为空
     *  unique——唯一
     *  primary key——主键
     *  foreign key——外键
     *  check——定义一行满足的条件

> 如果不指定约束名，Oracle server自动按照Sys\_cn 的格式指定约束名  
> 创建和修改约束  
> 建表的同时、建表之后  
> 可以在表级或列级定义约束  
> 可以通过数据字典视图查看约束

> 作用范围  
> \- 列级约束只能作用在一个列上  
> \- 表级约束可以作用在多个列上（当然表级约束也可以作用在一个列上）  
> \- 定义方式:列约束必须跟在列的定义后面，表约束不与列一起，而是单独定义。

*  非空 (not null)约束只能定义在列上

create table emp(
    	列级约束
    	id number(10) constraint emp_id_uk unique,
    	email varchar2(60),
    	表级约束
    constraint emp_email_uk unique(email)
    )

> 添加约束的语法  
> \- 使用alter table 语句  
> \- 添加或删除约束，但是不能修改约束  
> \- 有效化或无效化约束  
> \- 添加not null约束要使用modify语句  
> \- alter table emp modity(enpname varchar2(60) not null);

- alter table emp drop constraint emp_name_;
    - alter table emp add constraint emp_name_uk unique;

*  无效化约束
    
     *  在alter table 语句中使用disable 子句将约束无效化。
     *  alter table emp disable constraint emp\_email\_uk;
 *  查询约束
    
     *  `select constraint_name,constraint_type,search_condition from user_constraints where table_name='表名';`
 *  查询定义约束的列
    
     *  `select constraint_name,column_name from user_cons_columns where table_name = '表名';`
 *  视图是一种虚表
    
     *  视图建立在已有表的基础上，视图赖以建立的这些表称为基表。
     *  向视图提供数据内容的语句为select语句，可以将视图理解为存储起来的select语句。
     *  视图向用户提供基表数据的另一种表现形式。
 *  为什么使用视图？
    
     *  控制数据访问
     *  简化查询
     *  避免重复访问相同的数据
 *  创建视图

create view empview
    as 
    select * from user where class_id = 12;

*  视图表修改原表也修改、删除同理
 *  修改视图
 *  使用create or replace view 子句修改视图
 *  屏蔽视图操作 在创建视图语句后加上 `with read only`
 *  视图中定义以下函数时不能使用delete、update(多一个：列的定义为表达式)、insert(比修改多一个:表中非空的列在视图定义中未包括)
    
     *  组函数
     *  group by 子句
     *  distinct 关键字
     *  rownum 伪列
 *  删除视图 ：drop view 表名;
 *  Ton-n分析 类似于分页
    
     *  top-n分析查询一个列中最大或最小的N个值
     *  最大和最小的值的集合是Top—N分析所关心的
     *  注意:对rownum 只能使用<或<=,而用=,>,>=都将不能返回任何数据。
 *  序列:提供有规律的数值
 *  索引:提高查询的效率
 *  同义词:给对象起别名

> 序列:可供多个用户来产生唯一值的数据库对象  
> \- 自动提供唯一的数值  
> \- 共享对象  
> \- 主要用于提供主键值  
> \- 将序列值装入内存可以提高访问效率

create sequence语句  定义序列
    	create sequence sequence
    	increment by n  --每次增长的数值
    	start with n  --从哪个值开始
    	maxvalue n 
    	minvalue n
    	cycale/nocycle  --是否需要循环
    	cache n/nocache  -- 是否缓存登录
    	
    例子:
    create sequence 名字唯一(序列名)
    increment by 10 -- 每次增长10
    start with 10  -- 从10开始增长
    maxvlaue 100  --提供最大值
    cycle  -- 需要循环
    nocache  -- 不需要缓存登录

查询序列值

*  `select 序列名.nextval from dual;`  
    查询当前的序列值
 *  `select 序列名.currval from dual;`

修改序列  
需改序列的增量，最大值，最小值，循环选项，或是否装入内存

alter sequence depe_deptid_seq
    increment by 20
    maxvalue 9999999
    nocache
    nocycle;

> 需改序列的注意事项

*  1.必须是序列的拥有着或对序列有later 权限
 *  2.只有将来的序列值会被改变
 *  3.改变序列的初始值只能通过删除序列之后重建序列的方法实现

> 使用序列

*  1.将序列值装入内存可提高访问效率
 *  2.序列在下列情况下出现裂缝
    
     *  回滚
     *  系统异常
     *  多个表同时使用同一序列
 *  3.如果不将序列的值装入内存(nocache)，可使用表user\_sequences 查看序列当前的有效值

删除序列  
1.使用drop sequence 语句删除序列  
2.删除之后，序列不能再次被使用  
`drop sequence empseq;`

> 索引

*  1.一种独立于表的模式对象，可以存储在表不同的磁盘或表空间中
 *  2.索引被删除或损坏，不会对表产生影响，其他影响的只是查询的速度
 *  3.索引一旦建立，Oracle 管理系统会对其进行自动维护，而且由Oracle管理系统决定何时使用索引。用户不用在查询语句中指定使用哪个索引
 *  4.再删除一个表时，所有基于该表的索引会自动被删除
 *  5.通过指针加速oracle服务器的查询速度
 *  6.通过快速定位数据的方法，减少磁盘I/O

创建索引  
1.自动创建:在定义primary key或 unique约束后系统自动在相应的列上创建唯一性索引  
2.手动创建:用户可以在其它列上创建非唯一的索引，以加速查询  
在一个或多个列上创建索引  
`create index index on table(column[column]...);`  
在表employees的列last\_name上创建索引  
`create index emp_last_name_index on employees(last_name);`

> 什么时候创建索引

*  1.列中数据分布范围很广
 *  2.列经常在where子句或连接条件中出现
 *  3.表经常被访问而且数据量很大，访问的数据大概占数据总量的2%到4%

> 什么时候不要创建索引

*  1.表很小
 *  2.列不经常作为连接条件或出现在where子句中
 *  3.查询的数据大于2%到4%
 *  4.表经常更新

同义词\_synonym  
使用同义词范文相同的对象  
1.方便访问其它用户的对象  
2.缩短对象名字的长度

create synonym e from empolyees;
    select * from e;

创建用户  
使用 create user 语句创建用户  
`create user identified by password;`

创建用户之后，DBA会赋予用户一些权限 grant  
`grant privilege [,privilege...] to user [,user| role, public。。。]`

create session 创建会话  
create table 创建表  
create sequence 创建序列  
create view 创建视图  
create procedure 创建过程

`alter user qm quota unlimited on users;`

> 对象权限  
> \- 不同的对象具有不同的对象权限  
> \- 对象的拥有者拥有所有权限  
> \- 对象的拥有者可以向外分配权限

分配表的查询权限  
`grant select on table to sue,rich;`  
分配表中各个列的更新权限  
`grant update on scott.departments to user用户;`

with grant option 和public 关键字  
with grant option 使用户同样具有分配权限的权力  
`grant select,insert on departments to scott with grant option;`  
向数据库中所有用户分配权限  
`grant select on alice.departments to public;`

> 收回对象的权限  
> \- 使用revoke 语句收回权限  
> \- 使用 with grant option 子句所分配的权限同样被收回

revoke 
    on 
    from

查询权限分配情况  
role\_sys\_privs : 角色拥有的系统权限  
role\_tab\_privs : 角色拥有的对象权限  
user\_role\_privs : 用户拥有的角色  
user\_tab\_privs\_made : 用户分配的关于表对象权限  
user\_tab\_privs\_recd : 用户拥有的关于表对象权限  
user\_col\_privs\_made : 用户分配的关于列的对象权限  
user\_col\_privs\_recd : 用户拥有的关于列的对象权限  
user\_sys\_privs : 用户拥有的系统权限

create user : 创建用户  
grant : 分配权限  
create role : 创建角色  
alter user : 修改用户密码  
revoke : 收回权限

set操作符

union操作符:返回两个查询的结果集的并集
    union all
    intersect 交集
    minus  差集

> 在使用set操作符时注意以下事项  
> \- 在select列表的列名和表达式在数量和数据类型上要相对应。  
> \- 括号可以改变执行顺序  
> \- order by 子句：只能在语句的最后出现、可以使用第一个查询中的列名，别名或相对位置

> 高级子查询  
> \- 子查询是嵌套在sql语句中的另一个select语句

*  多列子查询

select name,uid,fid from user
    where (uid,fid)in(select uid,fid from user where status=1);

子查询尽量用别名

*  exists操作符 not exists
 *  检查在子查询中是否存在满足条件的行
 *  如果在子查询中存在满足条件的行
    
     *  不在子查询中继续查找
     *  条件返回true
 *  如果在子查询中不满足条件的行
    
     *  条件返回false
     *  继续在子查询中查找
 *  with子句
    
     *  使用with子句，可以避免在select语句中重复书写相同的语块
     *  with子句将该子句中的语句执行一次并存储到用户的临时表空间中
     *  使用with子句可以提高查询效率
     *  例如  
        `with number as (select iphone from user where name ='baby')`  
        `select name from user where iphone = number;`

#### pl/sql ####

程序有三个块组成，即声明部分、执行部分、异常处理部分

declare: 声明部分，在此声明pl/sql用到的变量，类型及游标，以及局部的存储过程和函数
    
    begin：执行部分：过程及sql语句，即程序的主要部分
    
    exception: 执行异常部分：错误处理
    
    end; 
    
    其中执行部分是必须的
    
    打印：dbms_output.put_line();

<table> 
 <thead> 
  <tr> 
   <th align="center">标识符</th> 
   <th align="center">命名规则</th> 
   <th align="center">例子</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">程序变量</td> 
   <td align="center">V_name</td> 
   <td align="center">V_name</td> 
  </tr> 
  <tr> 
   <td align="center">程序常量</td> 
   <td align="center">C_name</td> 
   <td align="center">C_comp_name</td> 
  </tr> 
  <tr> 
   <td align="center">游标变量</td> 
   <td align="center">Name_cursor</td> 
   <td align="center">Emp_cursor</td> 
  </tr> 
  <tr> 
   <td align="center">异常标识</td> 
   <td align="center">E_name</td> 
   <td align="center">E_too_many</td> 
  </tr> 
  <tr> 
   <td align="center">表</td> 
   <td align="center">Name_table</td> 
   <td align="center">Emp</td> 
  </tr> 
  <tr> 
   <td align="center">记录类型</td> 
   <td align="center">Name_record</td> 
   <td align="center">Emp_record</td> 
  </tr> 
  <tr> 
   <td align="center">Sql*Plus替代变量</td> 
   <td align="center">P_name</td> 
   <td align="center">P_sal</td> 
  </tr> 
  <tr> 
   <td align="center">绑定变量</td> 
   <td align="center">G_name</td> 
   <td align="center">G_year_sal</td> 
  </tr> 
 </tbody> 
</table>

select ... into ...
    select name into gname from emp where id=1;

<table> 
 <thead> 
  <tr> 
   <th align="center">类型</th> 
   <th align="center">子类</th> 
   <th align="center">说明</th> 
   <th align="center">范围</th> 
   <th align="center">Oracle限制</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">Char</td> 
   <td align="center">Character<br>String<br>Rowid<br>Nchar</td> 
   <td align="center">定长字符串<br><br><br>民族语言字符集</td> 
   <td align="center">0——&gt;32767<br>可选，确省=1</td> 
   <td align="center">2000</td> 
  </tr> 
  <tr> 
   <td align="center">Varchar2</td> 
   <td align="center">Varchar,String<br>Nvarchar2</td> 
   <td align="center">可变字符串<br>民族语言字符集</td> 
   <td align="center">0——&gt;32767<br>4000</td> 
   <td align="center">4000</td> 
  </tr> 
  <tr> 
   <td align="center">Binary_integer</td> 
   <td align="center"></td> 
   <td align="center">带符号整数，为整数计算优化性能</td> 
   <td align="center"></td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center">Number(p,s)</td> 
   <td align="center">Desc<br>Double<br>precision<br>Integer<br>int<br>numeric<br>Real<br>Small int</td> 
   <td align="center">小数，number的子类型高精度实数<br>整数，number的子类型<br>整数,number的子类型<br>与number等价<br>与number等价<br>整数,比integer小<br><br></td> 
   <td align="center"></td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center">Long</td> 
   <td align="center"></td> 
   <td align="center">变长字符串</td> 
   <td align="center">0——&gt;2147483647</td> 
   <td align="center">32767字节</td> 
  </tr> 
  <tr> 
   <td align="center">Date</td> 
   <td align="center"></td> 
   <td align="center">日期类型</td> 
   <td align="center">公元前4712年1月1日至公元后4712年12月31日</td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center">Boolean</td> 
   <td align="center"></td> 
   <td align="center">布尔型</td> 
   <td align="center">true,false,null</td> 
   <td align="center">不使用</td> 
  </tr> 
  <tr> 
   <td align="center">Rowid</td> 
   <td align="center"></td> 
   <td align="center">存放数据库行号</td> 
   <td align="center"></td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center">Urowid</td> 
   <td align="center"></td> 
   <td align="center">通用行标识符,字符类型</td> 
   <td align="center"></td> 
   <td align="center"></td> 
  </tr> 
 </tbody> 
</table>

declare
    ---声明一个记录类型
    type record_type is record(
        v_sal empuser.name%type,
        v_email empuser.email%type,
        v_date empuser.date%type
    );
    --- 定义一个记录类型的成员变量
        v_emp_record emp_record;
    begin
        select name,email,date into record from empuser where id=1;
        dums_output.put_line(v_emp_record.name);
    end;

流程控制  
1.条件判断(两种)  
方式一: if … then elseif then … else … end if;  
方式二: case … when … then … end;  
2.循环结构(三种)  
方式一: loop … exit when … end loop;  
方式二: while … loop … end loop;  
凡是三: for i in … loop … end loop;  
3. goto 、 exit  
游标的使用(类式于java中的Iterator)  
异常的处理(三种)  
会写一个存储函数(有返回值)、存储过程(没有返回值)  
会写一个触发器

# 总结 #

##### 使用%type #####

> 定义一个变量，其数据类型已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同，这时可以使用%Type。

###### 使用%type特性的优点在于 ######

*  所引用的数据库的列的数据类型可以不必知道;
 *  所引用的数据库的列的数据类型可以实时改变;

###### 使用%rowtype ######

> pl/Sql 提供%rowtype操作符，返回一个记录类型，其数据类型和数据库表的数据结构相一致。

*  所引用的数据库中列的个数和数据类型可以不必知道。
 *  所引用的数据库中列的个数和数据类型可以实时改变。

#### 关系运算符 ####

#### 一般运算符 ####

<table> 
 <thead> 
  <tr> 
   <th align="center">运算符</th> 
   <th align="center">意义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">+</td> 
   <td align="center">加号</td> 
  </tr> 
  <tr> 
   <td align="center">-</td> 
   <td align="center">减号</td> 
  </tr> 
  <tr> 
   <td align="center">*</td> 
   <td align="center">乘号</td> 
  </tr> 
  <tr> 
   <td align="center">/</td> 
   <td align="center">除号</td> 
  </tr> 
  <tr> 
   <td align="center">:=</td> 
   <td align="center">复赋值号</td> 
  </tr> 
  <tr> 
   <td align="center">=&gt;</td> 
   <td align="center">关系号</td> 
  </tr> 
  <tr> 
   <td align="center">…</td> 
   <td align="center">范围运算符</td> 
  </tr> 
  <tr> 
   <td align="center">两个竖杠</td> 
   <td align="center">字符连接符</td> 
  </tr> 
 </tbody> 
</table>

#### 逻辑运算符 ####

<table> 
 <thead> 
  <tr> 
   <th align="center">运算符</th> 
   <th align="center">意义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">is null</td> 
   <td align="center">是空值</td> 
  </tr> 
  <tr> 
   <td align="center">between and</td> 
   <td align="center">介于两者之间</td> 
  </tr> 
  <tr> 
   <td align="center">in</td> 
   <td align="center">在一列值中间</td> 
  </tr> 
  <tr> 
   <td align="center">and</td> 
   <td align="center">逻辑与</td> 
  </tr> 
  <tr> 
   <td align="center">or</td> 
   <td align="center">逻辑与</td> 
  </tr> 
  <tr> 
   <td align="center">not</td> 
   <td align="center">取反，如is null,not in</td> 
  </tr> 
 </tbody> 
</table>

#### 变量赋值 ####

> 在pl/sql编码中，变量赋值是一个值得注意的地方，他的语法如下:  
> \- variable := expression;  
> \- variable 是一个pl/sql变量，expression是一个pl/sql表达式

#### 字符及数字运算特点 ####

> 空值加数字仍是空值: null + <数字>=null  
> 空值加(连接)字符，结果为字符: null|| <字符串>=<字符串>

#### boolean 赋值 ####

> 布尔值只有true,false及null 三个值

#### 数据库赋值 ####

> 数据库赋值是通过select 语句来完成的，每次执行的select语句就赋值一次，一般要求被赋值的变量与select中的列名要一一对应。

不能将select语句中的列赋值给布尔变量。

#### 可转化的类型赋值 ####

*  char 转化为 number
    
     *  使用to\_number函数来完成字符到数字的转换，如: v\_total := to\_number(‘100.0’)+price;
 *  number 转换为char:
    
     *  使用to\_char函数可以实现数字到字符的转换，如：v\_comn := to\_char(‘123.45’)||‘元’;
 *  字符串转换日期
    
     *  使用to\_date函数可以实现 字符到日期的转换，如: v\_date := to\_date(‘2019.01.01’,‘yyyy.mm.dd’);
 *  日期转换为字符
    
     *  使用to\_char 函数可以实现日期到字符的转换，如: v\_to\_day := to\_char(sysdate,‘yyyy.mm.dd hh24:mi:ss’);

#### 变量作用范围及可见性 ####

> 在pl/sql编程中,如果在变量的定义上没有做到统一的话，可能会隐藏一些危险的错误，这样的原因主要是变量的作用范围所致。与其它高级语言类式，pl/sql的变量作用范围特点是:  
> \- 变量的作用范围是在你所引用的程序单元(块、子程序、包)内。即从声明变量开始到该块的结束。  
> \- 一个变量(标识)只能在你所引用的块内是可见的。  
> \- 当一个变量超出了作用范围，pl/sql引擎就释放用来存放该变量的空间(因为他可能用不了)。  
> \- 在子块中重新定义该变量后，它的作用仅在该块内。

#### 注释 ####

> 在pl/sql里,可以使用两种符号来写注释,即:  
> \- 使用双‘-’(减号)加注释,它的作用范围只能在一行有效  
> \- 使用/\*\*/来加一行或多行注释  
> 注 被释放存放在数据库中的pl/sql程序，一般系统自动将程序头部的注释去掉。只有在procedure之后的注释才能被保留;另外程序中的空行也自动被去掉。

#### pl/sql流程空值语句 ####

> 介绍pl/sql的流程控制语句，包括如下三类  
> 控制语句: if 语句  
> 循环语句: loop 语句,exit 语句  
> 顺序语句: goto 语句,null 语句

##### 条件语句 #####

if <布尔表达式> then
        pl/sql 和 sql 语句;
    end if;
    
    if <布尔表达式> then
        pl/sql 和 sql 语句;
    else
        其他语句;
    end if;
    
    if <布尔表达式> then
        pl/sql 和 sql 语句;
    elseif <其他布尔表达式> then
        其他语句;
    elseif <其他布尔表达式> then
        其他语句;
    else
        其他语句;
    end if;

else 不能写成 elseif

##### case 表达式 #####

v_name :=
    case name when expression1 then result1
        when expression2 then result2
        when expressionN then resultN
        else result+1
    end;

##### 循环 #####

*  1.简单循环

loop
        要执行的语句;
        exit when <条件语句>; /*条件满足，退出循环语句*/
    end loop;

*  2.while 循环(相较1,推荐使用2)

while <布尔表达式> loop
        要执行的语句;
    end loop;

*  3.数字式循环

for 循环计数器 in [reverse] 上限...下限 loop
        要执行的语句;
    end loop;
    每循环一次,循环变量自动加1;使用关键字reverse,循环变量自动减1。跟在in reverse 后面的数字必须是从小到大的顺序,而且必须是整数,不能是变量或表达式。可以使用exit退出循环。

##### goto #####

> pl/sql 中goto 语句是无条件跳转到指定的标号去的意思.语法如下  
> goto label;  
> <> /*标号是用<< >>括起来的标识符*/  
> if i = 50 then goto label;  
> endif;  
> <>  
> dbms\_output.put\_line(‘跳出’);

#### 游标的使用 ####

> 在pl/sql程序中，对于处理多行记录的事务经常使用游标来实现。

概念:

> 为了处理sql语句，oracle必须分配一篇叫上下文(contenxt area)的区域来处理所必须的信息，包括要处理的行的数目，一个指向语句被分析以后的表示形式的指针以及查询的活动集(active set)  
> \- 游标是一个指向上下文的句柄(handle)或指针。通过游标，pl/sql可以控制上下文区和处理语句时上下文会发生些什么事情。

对于不同的sql语句，游标的使用情况不同。

<table> 
 <thead> 
  <tr> 
   <th align="center">Sql语句</th> 
   <th align="center">游标</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">非查询语句</td> 
   <td align="center">隐式的</td> 
  </tr> 
  <tr> 
   <td align="center">结果是单行的查询语句</td> 
   <td align="center">隐式的或显示的</td> 
  </tr> 
  <tr> 
   <td align="center">结果是多行的查询语句</td> 
   <td align="center">显示的</td> 
  </tr> 
 </tbody> 
</table>

显示游标处理  
显示游标处理需四个pl/sql步骤

*  定义游标: 就是定义一个游标名，以及与其相对应的select语句。
    
     *  格式: cursor cursor\_name \[(parameter\[,parameter\]…)\]is select\_statement;
     *  游标参数只能为输入参数，其格式为
     *  parament\_name\[in\] datetype\[\{:=| default\}expression\]
     *  在指定数据类型时，不能使用长度约束。如number(4)、char(10)等都是错误的。
 *  打开游标: 就是执行游标所对应的select 语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。如果游标查询语句中带有for update选项，open语句还将锁定数据库表中游标结果集合对应的数据行。
    
     *  格式
     *  open cursor\_name\[(\[parament =>\]value\[,\[parament =>\] value\]…)\];
     *  在游标传递参数时，可以使用与函数参数相同的传值方法，即位置表示法和名称表示法。pl/sql程序不能用open语句重复打开一个游标。
 *  提取游标数据: 就是检索结果集合中的数据行，放入指定的输出变量中。
    
     *  格式
     *  fetch cursor\_name into \{variable\_list | record\_variable\};
 *  对该记录进行处理
 *  继续处理，直到活动集合中没有记录
 *  关闭游标: 当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源，并使用该游标的工作区变成无效，不能再使用fetch语句取其中数据。关闭后的游标可以使用open语句重新打开。
    
     *  格式
     *  close cursor\_name;  
        注：定义的游标不能有into子句。

举例

--打印
    declare
        v_sal emp.salary%type;
    
        --定义游标
        cursor emp_sal_cursor is select salary from emp where deptid=20;
    begin
        -- 打开游标
        open emp_sal_cursor;
        -- 提取游标
        fetch emp_sal_cursor into v_sal;
    
        -- found 判断还有没有值
        while emp_sal_cursor%found loop
            dbms_output.put_line(v_sal);
            fetch emp_sal_cursor into v_sal;
        end loop;
        -- 关闭游标
        close emp_sal_cursor;
    end;

##### 游标属性 #####

*  %found : 布尔属性，当最近一次读记录时成功返回，则指为true;
 *  %notfound : 布尔属性，与found相反。
 *  %isopen 布尔属性，当游标已打开时返回true;
 *  %rowcount 数字型属性，返回已从游标中读取的记录数。

#### 游标的for循环 ####

> pl/sql语言提供了游标的for循环语句，自动执行游标的open、fetch、close语句和循环语句的功能;当进入循环时，游标for循环语句自动打开游标,并提取第一行游标数据,当程序处理完当前所提取的数据而进入下一次循环时，游标for循环语句自动提取下一行数据供程序处理,当提取完结果集合中的所有数据行后结束循环,并自动关闭游标。  
> \- 格式:  
> \- for index\_variable in cursor\_name\[value\[,value\]…\]loop  
> – 游标数据处理代码  
> end loop;  
> \- 其中:  
> index\_variable 为游标的for循环语句隐含声明的索引变量,该变量为记录变量,其结构与游标查询语句返回的结构集合的结构相同。在程序中可以通过引用该索引记录变量元素来读取所提取的游标数据,index\_variable中各元素的名称与游标查询语句选择列表中所制定的列名相同。如果在游标查询语句的选择列表中存在计算列,则必须为这些计算列指定别名后才能通过游标for循环语句中的索引变量来访问这些列数据。

##### 异常处理 #####

> 一个优秀得程序都应该能够正确处理各种出错情况，并尽可能从错误中恢复。oracle提供异常情况(exception)和异常处理(exception handler)来实现错误处理。

*  异常处理概念
    
     *  异常情况处理(exception)是用来处理正常执行过程中未预料地事件,程序块地异常处理预定义的错误和自定义的错误,由于pl/sql程序块一旦产生异常而没有指出如何处理时,程序就会自动终止整个程序运行。

有三种类型的异常错误:

*  预定义(predefined)错误
    
     *  oracle预定义的异常情况大约有24个。对这种异常情况的处理,无需在程序中定义,由oracle自动将其引发。
 *  非预定义(predefinrd)错误
    
     *  及其他标准的oracle错误。对这种异常情况的处理,需要用户在程序中定义,然后由oracle自动将其引发。
 *  用户定义(user\_define)错误
    
     *  程序执行过程中,出现编程人员认为的非正常情况。对这种异常情况的处理,需要用户在程序中定义,然后显示地在程序中将其引发。
 *  异常处理部分一般放在pl/sql程序体地后半部,结构为:

exception 
        when first_exception then <code to handle first exception>
        when second_exception then <code to handle first exception>
        when others then <code to handle others exception>
    end;

异常处理可以按任意次序排列,但others必须放在最后。

##### 预定义地异常处理 #####

预定义说明的部分oracle异常错误

<table> 
 <thead> 
  <tr> 
   <th align="center">错误号</th> 
   <th align="center">异常错误信息名称</th> 
   <th align="center">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">ORA-0001</td> 
   <td align="center">Dup_val_on_index</td> 
   <td align="center">试图破环一个唯一性限制</td> 
  </tr> 
  <tr> 
   <td align="center">ORA-0051</td> 
   <td align="center">Timeout-on-resource</td> 
   <td align="center">在等待资源时发生超时</td> 
  </tr> 
  <tr> 
   <td align="center">ORA-0061</td> 
   <td align="center">Transaction-backed-out</td> 
   <td align="center">由于发生死锁事务被撤销</td> 
  </tr> 
  <tr> 
   <td align="center">ORA-1001</td> 
   <td align="center">Invalid-CURSOR</td> 
   <td align="center">视图使用一个无效的游标</td> 
  </tr> 
  <tr> 
   <td align="center">ORA-1012</td> 
   <td align="center">Not-logged-on</td> 
   <td align="center">没有连接到Oracle</td> 
  </tr> 
  <tr> 
   <td align="center">ORA-1017</td> 
   <td align="center">Login-denied</td> 
   <td align="center">无效的用户名/口令</td> 
  </tr> 
  <tr> 
   <td align="center">ORA-1403</td> 
   <td align="center">No_data_found</td> 
   <td align="center">select into 没有找到数据</td> 
  </tr> 
  <tr> 
   <td align="center">ORA-1422</td> 
   <td align="center">Too_many_rows</td> 
   <td align="center">select into 返回多行</td> 
  </tr> 
  <tr> 
   <td align="center">ORA-1476</td> 
   <td align="center">Zero-divide</td> 
   <td align="center">试图被零除</td> 
  </tr> 
  <tr> 
   <td align="center">ORA-1722</td> 
   <td align="center">Invalid-number</td> 
   <td align="center">转换一个数字失败</td> 
  </tr> 
  <tr> 
   <td align="center">ORA-6500</td> 
   <td align="center">Storage-error</td> 
   <td align="center">内存不够引发的内部错误</td> 
  </tr> 
  <tr> 
   <td align="center">ORA_6501</td> 
   <td align="center">Program-error</td> 
   <td align="center">内部错误</td> 
  </tr> 
  <tr> 
   <td align="center">ORA-6502</td> 
   <td align="center">Value-error</td> 
   <td align="center">转换或截断错误</td> 
  </tr> 
  <tr> 
   <td align="center">ORA-6511</td> 
   <td align="center">Cursor-into-null</td> 
   <td align="center">视图为null对象的属性赋值</td> 
  </tr> 
  <tr> 
   <td align="center">ORA-6531</td> 
   <td align="center">Collection-is-null</td> 
   <td align="center">视图将exits以外的集合(collection)方法应用于一个null pl/sql表上或varray上</td> 
  </tr> 
  <tr> 
   <td align="center">ORA-6532</td> 
   <td align="center">subscript-outside-limit</td> 
   <td align="center">对嵌套或varray索引得引用超出声明范围以外</td> 
  </tr> 
  <tr> 
   <td align="center">ORA-6533</td> 
   <td align="center">subscript-beyond-count</td> 
   <td align="center">对嵌套或varray索引得引用大于集合中元素得个数</td> 
  </tr> 
 </tbody> 
</table>

*  对这种异常情况的处理,只需在pl/sql块的异常处理部分,直接引用相应的异常情况名，并对其完成相对应的异常错误处理即可。

##### 处理异常示例 #####

declare
        v_sal emp.salary%type;
    begin
        select salary into v_sal from emp where emp_id = 20;
    
        dbms_output.put_line(v_sal);
    exception
        when too_many_rows then dbms_output.put_line('输出的行数太多了！！');
    end;

##### 非预定义的异常处理 #####

> 对于这类异常情况的处理,首先必须对非定义的Oracle错误进行定义。步骤如下

在pl/sql块的定义部分定义异常情况: <异常情况> exception;

将其定义好的异常情况，与标准的Oracle错误联系起来，使用pragma exception\_init 语句:Pragma exception\_init(<异常情况>，<错误代码>)；

在pl/sql块的异常情况处理部分对异常情况做出相应的处理。

declare
        e_deleteid_exception exception;
        pragam exception_init(e_deleteid_exception,-2292);
    begin
        delete from emp where emp_id = 100;
    exception 
        when e_deleteid_exception then dbms_output.put_line('违反完整性约束条件，故不可删除此用户');
    end;

##### 用户自定义的异常处理 #####

> 当与一个异常错误相关的错误出现时，就会隐含触发该异常错误。用户定义的异常错误是通过显式使用ralse语句来触发。当引发一个异常错误时，控制就转向到exception块异常错误部分，执行错误处理代码。对于这类异常情况的处理，步骤如下:

*  在pl/sql块的定义部分定义异常情况: <异常情况> exception;
 *  ralse<异常情况>;
 *  在pl/sql块的异常情况处理部分对异常情况做出相应的处理。

示例:

declare
        e_too_high_sal exception;
        v_sal emp.salary%type;
    begin 
        select salary into v_sal from emp where emp_id = 20;
        if v_sal > 100 then
        raise e_too_high_sal;
        end if;
    exception
        when e_too_high_sal then dbms_output.put_line('你工资有点低！');
    end;

# 存储函数和过程 #

> Oracle提供可以把pl/sql程序存储在数据中，并可以在任何地方来运行它。这样就叫存储过程或函数。过程和函数统称为pl/sql子程序，他们是被命名的pl/sql块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程则不反回数据。

#### 1.创建函数 ####

*  建立内嵌函数: 语法如下:

create [or replace]function function_name
        [argment[{in|in out}]type,
        argment[{in|out|in out}]type]
        [authid definer|current_user]
        return return_type;
    
    {is|as}
        <类型.变量的说明>
    begin
        function_body
    exception
        其他语句
    end;

说明:

*  or replace 为可选，有了它，可以或者创建一个新函数或者替换相同名字的函数，而不会出现冲突
 *  函数名后面是一个可选的参数列表，其中包含in,out 或in out标记。参数之间用逗号隔开。in参数标记表示传递给函数的值在该函数执行中不改变；out标记表示一个值在函数中进行计算并通过该参数传递给调用语句；in out 标记表示传递给函数的值可以变化并传递给调用语句。若省略标记，则参数隐含为in。
 *  因为函数需要返回一个值，所以return包含返回结果的数据类型。

示例:

create or replace function function_name(dept_id number,salary number)
    return number
    is
        -- 函数使用过程中，需要声明的变量、记录类型、cursor
    begin
        -- 函数的执行体
    exception
        -- 处理函数执行过程中的异常
    end;
    
    -- 创建一个hello world 函数 返回hello world
    
    create or replace function hello_world
    return varchar2
    is 
    begin
        return 'hello world';
    end;

调用

begin
        dbms_output.put_line(hello_world);
    end;
    或者使用
    select hello_world fro dual;

查看报错信息:`show errors;`

# 触发器 #

> 触发器是许多关系数据库系统都提供的一项技术。在oracle系统里，触发器类似过程和函数，都有声明，执行和异常处理过程的pl/sql块。

##### 触发器类型 #####

> 触发器在数据库里以独立的对象存储，它与存储过程不同的是，存储过程通过其他程序来启动运行或直接启动运行，而触发器是由一个事件来启动运行。及触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。所以运行触发器就叫触发或点火(firing)。Oracle事件指的是对数据库地表进行的insert、update及delete操作对视图进行类似的操作。Oracle将触发器的功能扩展到了触发oracle，如数据库的启动与关闭等。

*  DML触发器
    
     *  oracle可以在dml语句进行触发，可以在dml操作前或操作后进行触发，并且可以对每个行或语句操作上进行触发。
 *  替代触发器
    
     *  由于在oracle里，不能直接对由两个以上的表建立的视图进行操作。所以给出了替代触发器。
 *  系统触发器
    
     *  它可以在oracle数据库系统的事件中进行触发，如oracle系统的启动与关闭等。

> 触发器组成  
> \- 触发事件: 即在何种情况下触发trigger; 例如: insert、update,delete  
> \- 触发事件: 即该trigger是在触发事件发生之前(before)还是之后(after)触发，也就是触发事件和该trigger的操作顺序。  
> \- 触发器本身: 即该trigger被触发之后的目的和意图，正是触发器本身要做的事情。例如：pl/sql块。  
> \- 触发频率: 说明触发器内定义的动作被执行的次数。即语句级(statement)触发器和行级(row)触发器。语句级(statement)触发器:是指当某触发事件发生时，该触发器只执行一次；行级(row)触发器: 是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。

##### 创建触发器 #####

创建触发器的一般语法是
    create [or replace] trigger trigger_name
        {before|after}
        {insert|delete|update[of column[,column ...]]}
        on [schema.]table_name
        [for each row]
        [when condition]
        trigger_body;
    其中:
        before和after指出触发器的触发时序分别为前触发和后触发方式，前触发是在执行触发事件之前触发当前所创建的触发器，后触发是在执行触发事件之后触发当前所创建的触发器。
        for each row 选项说明触发器为行触发器。行触发器和语句触发器的区别表现在:行触发器要求当一
    
    示例:  没更改一次emp表打印一次helloworld
    create or replace trigger update_emp
    after
        update on emp
    begin
        dbms_output.put_line('helloworld')
    end;

##### :new :old修饰符 #####

示例:
    create or replace trigger update_emp
    after
        update on emp
    begin
        dbms_output.put_line('old:salary'|| :old.salary ||','||'new salary:'||:new.salary);
    end;