Oracle数据库，PL/SQL编程笔录
PL/SQL:
是Oracle对SQL语言过程的程序化拓展
基本语法结构：
[declare ...声明变量] begin ...程序块 [Exception ...异常处理] end;
=====================
--异常部分
exception
when 异常类型 then
异常处理逻辑：一般把异常信息写入log表中
变量的语法
变量名 类型（长度）【类型可以直接写成表名.列名%type 或者记录型表名%rowtype】；
赋值： 变量名 := 变量值 （快速冲数据中提取数据 可以使用select 列名 into 变量名 from 表名 where 条件）
条件判断
if 条件1 then ...业务逻辑1 elsif 条件2 then ...业务逻辑2 else ...业务逻辑3 end if;
循环-无条件循环-条件循环-for循环
--无条件循环 loop ...循环语句 [exit when 条件] end loop; --这种会进入死循环，一般加上限制条件 exit when 条件 --条件循环 while 条件 loop ...循环语句 end loop; --for循环，也是比较常用的一种循环 for [] in ... loop ...循环语句 end loop;
游标
是系统为用户开设的一个数据缓冲区，存放SQL查询的一个结果集
--在声明区声明游标 cursor cur_name is sql语句 --使用游标语法 open cur_name loop fetch cur_name into 变量 exit cur_name%notfound end loop; cose cur_name --for循环可以简化，就不需要open cose fetch exit了哟
包（package）
包是一组相关过程，函数变量和游标等PL/SQL程序设计元素的组合，类似于C和Java语言中的类，具备面向对象的特点，是对这些pl/sql程序设计元素封装。包中的程序元分为公共和私有有两种元素。区别是允许访问程序的范围不同，公共元素不仅可以被包中 的过程、函数范围，还可以被包之外的pl/sql程序访问，私有元素不仅可以被包中 的过程或者函数访问。包不仅可以使程序模块化，对外隐藏包内信息，而且还可以提高程序效率。因为当首次访问包中的过程或者函数时，Oracle会将整个包加入内存，当再次访问包内元素时，Oracle直接从内存中读取，无需在做i/o磁盘操作，从而提高程序效率。
一个包由两分开的部组成：
包定义（package）：包定义部分声明包内数据类型、变量、常量、游标、子程序和异常处理等元素。这些都是公共元素
包主体（package body）：是包定义的部分具体实现，它定义了包定义部分所声明的游标和子程序，在包体中还可以声明私有元素
包定义和包主体分开编译，并作为两部分分开的对象存储在数据字典中
建包的基本语法（create package）
create [or replace] package pak_name is|as [公有数据类型定义] [公有游标定义] [公有常量、变量声明] [公有过程、函数声明] end pak_name；
建立包体的基本语法（create package body） 包主体名一定要用对应的包名
create [or replace] package body pak_name is|as [私有数据类型定义] [私有常量、变量声明] [私有过程、函数声明]
[公有游标定义]
[公有过程、函数]
begin
pl/sql语句
end;
end pak_name；
删除包和包主体
drop package pak_name drop package body pak_name
细节：
1、包体中要实现的过程和函数，应当在包规范中声明
2、调用包中的某个函数和过程的时候，需要使用对应的方法才可以调用
3、如何调用包的过程和函数，当调用包的过程和函数时，在过程和函数前需要带有包名，如果要访问其它方案的包，还需要在包名前加方案名
调用方式：
包名.过程名/函数名（参数......）
也可以直接select 包名.函数名(参数,......) from table_name
存储函数
一般再多表关联可以使用存储函数
create [or replace] function fn_name ( 参数名 参数类型， 参数名 参数类型 ...) is [变量声明] begin ...业务逻辑 return 结果变量
[excelption
...异常处理] end;
存储过程
create [or replace] procedure pr_name ( 参数名 类型， 参数名 类型， 参数名 类型) as ...变量声明 begin ...业务逻辑 [exception ...异常处理] end; --参数的三种模式 --IN 传入参数(默认) --OUT 传出参数，主要用于返回程序运行结果 --IN OUT 传入传出参数
--调用存储过程
方式一
begin
pr_name()
end；
方式二
call pr_name();
触发器
自动执行的“存储过程”，会被Oracle自动调用
用于数据确认，实施复杂的安全检查、做审计、跟踪表上的数据操作，数据的备份和同步
分类：前置触发器（before）\ 后置触发器（after）
create [or replace] trigger tr_name befare | after [delete][[or] insert][[or] update [of 列名]] --在做什么操作触发，列名可以用逗号隔开 on table_name [for each row][where 条件] --for each row行级触发器 where 返回false true declare ...声明 begin ...程序块 end;
伪记录变量 :old :new
| 触发语句 | :old | :new |
| insert | 所有字段都是null | 将要插入的数据 |
| update | 更新以前该行的值 | 更新后的值 |
| delete | 删除以前该行的值 | 所有字段都是null |
字符函数
| 函数 | 说明 |
| ascll | 返回对应字符的十进制值 |
| chr | 给出十进制返回字符 |
| concat | 拼接两个字符串，与||相同 |
| initcat | 将字符串的第一个字母变为大写 |
| instr | 找出某个字符串的位置 |
| instrb | 找出某个字符串的位置和字节数 |
| length | 以字符串给出字符串的长度 |
| lengthb | 以字节给出字符串的长度 |
| lower | 将字符串转换成小写 |
| lpad | 使用指定的字符在字符的左边填充 |
| ltrim | 在左边裁剪掉指定的字符 |
| rpad | 使用指定的字符在字符右边填充 |
| rtrim | 在右边裁剪掉指定的字符 |
| replace | 执行字符串搜索和替换 |
| substr | 取字符串的子串 |
| substrb | 取字符串的子串（以字节） |
| soundex | 返回同一个音字符串 |
| translate | 执行字符串搜索和替换 |
| trim | 裁剪掉前面或后面的字符串 |
| upper | 将字符串转换成大写 |
数值函数
| 函数 | 说明 |
| ABS(value) | 绝对值 |
| CEIL(value) | 大于等于value的最小整数 |
| COS(value) | 余弦 |
| COSH(value) | 反余弦 |
| EXP(value) | e的value次幂 |
| FLOOP(value) | 小于或等于value的最大整数 |
| IN(value) | value的自然对数 |
| LOG(value) | value的以10为底的对数 |
| MOD(value,divisor) | 求模 |
| POWER(value,exponent) | value的exponent次幂 |
| ROUND(value,precision) | 按precision精度4舍5入，常用 |
| SIGN(value) |
value为正返回1；为负返回-1；为0返回0 |
| TRUNC(value,precision) |
按precision截取value，常用 |
日期函数
| 函数 | 说明 |
| ADD_MONTHS | 在日期date上增加count个月 |
| CREAREST(date1,...) | 从日期列表中选出最晚的日期 |
| LAST_DAY(date) | 返回日期date所在月的最后一天 |
| LEAST(date,...) | 从日期列表选出最早的日期 |
| NEXT_DAY(date,'day') | 给出日期date之后下一天的日期，这里day为星期 |
| NEW_TIME(date,'this','other') | 给出this时区=other时区的日期和时间 |
| TRUNC(date，'年月日') | 截取日期 |
转换函数
| 函数 | 说明 |
| chartorowid | 将字符转换到rowid类型 |
| to_char | 将日期格式转到字符串 |
| to_date | 按照指定格式将字符串转换到日期型 |
| to_number | 将字符串转数字 |
其它函数
nvl(检查的value,为null返回的值)
nvl2(检查的value，返回value不为null的值，如果为null的值)
条件取值 decode
decode(条件，值1，翻译值1，值2，翻译值2，...,值n,翻译值n,缺省值)
索引
索引适用于加速数据存取的数据对象，合理的使用索引可以大大降低I/O读取的次数，从而提高数据的访问性能，索引是需要占据存储空间，可以理解为是一种特殊的数据，类似于“树”。
普通索引
--创建语法
create index index_name on table_name(列名)
唯一索引
--创建语法 create unique index index_name on table_name(列名)
复合索引 :基于两个以上列创建一个索引
--创建语法 create index index_name on table_name(列名1，列名2，...)
反向键索引
使用场景：当某个字段的值为连续增长的值，如果构建标准索引，会形成歪脖子树，这样会增加查询的次数，性能会下降，建立反向键索引，可以使索引的值变得不规则，从而使索引树能均匀分布
--创建语法 create index index_name on table_name(列名) reverse
位图索引
使用场景：适合创建在低基数列上
位图索引不直接存储rowid,而是存储字节位到列上rowid的映射
优点：减少时间响应，节省空间占用
--创建语法 create bitmap index index_name on table_name(列名)
序列
序列是Oracle提供的，用于产生一系列唯一数字的数据库对象
--创建序列语法 create sequence seq_name
通过序列的伪列类访问序列的值，nextval 返回序列的下一个值，currval 返回序列的当前值
创建复杂序列
create sequence seq_name [increment by n] --递增的序列值是n,如果n是正数就递增，n是负数就递减，默认是1 [start wilh n] --开始的值，递增默认是maxvalue 递减是minvalue [maxvalue n|nomaxvalue] --最大值 [minvalue n|nominvalue] --最小值 [cyclle|nocycle] --循环/不循环 [cache n|nocache]--分配并存入到内存中
视图
视图是一种数据库对象，是从一个或者多个数据库表或视图导出的虚表，视图所对应的数据并不是真存储在视图中，而是存储在引用的数据库表中，视图的结构和数据是对数据库表进行查询的结果。
优点：简化数据操作，视图可以简化用户处理数据的方式
着重于特定数据，不必要的数据或敏感数据可以不出现在视图中
视图提供了一个简单而有效的安全机制，可以定制不同用户 对数据的访问权限
提供向后兼容性，视图使用户能够在表的架构更改时为表创建后兼容接口
--创建视图和修改语法 create [or replace] [force] view V_name as subquery [with check option] --插入或修改的数据必须满足视图定义的约束 [with read only] --不能进行任何DML操作 --选项解释 --or replace ：若创建的视图已经存在，oracle子重建该视图 --force ：不管基本是否存在Oracle都会自动创建该视图 --subquery：一条完整的select语句，可以在该语句中定义别名
--删除视图语法
drop view v_name
物化视图
create meterialized view mv_name [build immediate | build deferred] refresh [fast | complete | force ] [ on [commit |demand] | start with (start_time) next (next_time)] as subquery --选项解释 --build immediate：是在创建物化视图生产数据，默认是build immediate --build deferred：是在创建时不生成数据，以后根据需要再生成数据 --refresh（刷新）：指当基表发生DML操作后，物化视图何时采用那种方式和基表进行同步，refresh后跟着的是刷新方法有三种：fast | complete | force，默认是force --fast：增量更新 --complete：完全刷新 --force：自动选择
--subquery：一条完整的select语句，可以在该语句中定义别名
