Oracle基础学习

• oracle实现汉字按照拼音、笔画和部首排序

  • Oracle9i之前，中文是按照二进制编码进行排序的。
  • 在oracle9i中新增了按照拼音、部首、笔画排序功能。
  • 设置NLS_SORT值SCHINESE_RADICAL_M 按照部首（第一顺序）、笔划（第二顺序）排序
  • SCHINESE_STROKE_M 按照笔划（第一顺序）、部首（第二顺序）排序
  • SCHINESE_PINYIN_M 按照拼音排序
    1: 按照笔划排序 select * from 表名 order by nlssort(字段名,'NLS_SORT=SCHINESE_STROKE_M');
2: 按照部首排序 select * from 表名 order by nlssort(字段名,'NLS_SORT=SCHINESE_RADICAL_M');
3: 按照拼音排序，此为系统的默认排序方式 select * from 表名 order by nlssort(字段名,'NLS_SORT=SCHINESE_PINYIN_M');

• 使select语句使查询结果自动生成序号:select rownum,字段 from 表;

• 快速做一个和原表一样的备份表:create table 新表 as (select * from 旧表);

---

• Oracle时间
  • date:精确到秒

写法	含义	显示值
year	年
yy	两位年	21
yyy	三位年	021
yyyy	四位年	2021
Month
mm	两位月	11
mon	字符集表示	11月/nov
month	字符集表示	11月/november
day
dd	当月第几天	02
ddd	当年第几天	307
dy	当周第几天简写	星期五/fir
day	当周第几天全写	星期五/friday
ddspath	日期在月份中的位置	02
Hour
hh	12小时进制	03
hh24	24小时进制	15
minute
mi	60进制	45
seconed
ss	60进制	25
其他
q	季度	4
ww	当年第几周	44
W	当月第几周	1

获取当前季度：select to_char(sysdate,'q') from dual;

• 集合操作符
  • minus是获取第一张表独有的数据
  • intersect是获取两张表中都有的数据
  • union是整合两张表的数据，都有的只显示一次
  • union all是纯粹的两张表数据整合
  • select 字段 from 表1 minus select 字段 from 表2

---

• 序列
  • sequence:序列号生成器，为表中行自动生成序列号，产生等间隔数值（类型为数字），不占用磁盘空间，占用内存
  • 创建序列:

CREATE SEQUENCE 序列名
 [INCREMENT BY n]->定义序列步长，默认为1，负值递减
 [START WITH n]->初始值，默认1
 [{MAXVALUE/ MINVALUE n| NOMAXVALUE}]->最大值 默认没有
 [{CYCLE|NOCYCLE}]->达到最大值，是否循环
[{CACHE n| NOCACHE}]->存放序列内存块大小,默认20
;

• NEXTVAL,CURRVAL
  • NEXTVAL
    第一次NEXTVAL返回的是初始值；随后的NEXTVAL会自动增加INCREMENT BY的值。调用一次增加一次，可以同一个语句多次调用
  • CURRVAL ,当前值，第一次NEXTVAL初始化之后才能使用，否则出错
  • 使用Sequence的环境
    • 不包含子查询、snapshot、VIEW的 SELECT 语句
      -INSERT语句的子查询中
    • INSERT语句的VALUES中
    • UPDATE 的 SET中

---

• 存储过程

  • 创建

  create [or replace] procedure 存储过程名（param1 in type，param2 out type）
  as
     变量1 类型（值范围）;
     变量2 类型（值范围）;
  begin
     select count(*) into 变量1 from 表A where列名=param1；
     if (判断条件) then
          select 列名 into 变量2 from 表A where列名=param1；
          dbms_output.Put_line('打印信息');
     elsif (判断条件) then
          dbms_output.Put_line('打印信息');
     else
          raise 异常名（NO_DATA_FOUND）;
     end if;
  exception
     when others then
          rollback;
  end;
  

  • 参数

    • in :传入参数，存储过程运行时已完成初始化
    • out:在存储过程中赋值，传回
    • in out:接收原有值，并返回更新后的值

  • 变量类型声明

    • 变量 基础类型
    • 变量 指定数据表%rowtype->表中一行数据的变量类型
    • 变量 指定数据表.指定字段%type;->表中指定字段的变量类型

  • into:赋值关键字

  • 异常(Exception)：

    • 预定义异常：由PL/SQL定义的异常。由于它们已在standard包中预定义了，因此，这些预定义异常可以直接在程序中使用，而不必再定义部分声明。如：
      NO_DATA_FOUND->select/insert不返回任何行，
      TOO_MANY_ROWS->返回多行
    • 非预定义异常：用于处理预定义异常所不能处理的Oracle错误。
    • 自定义异常：用户自定义的异常，需要在定义部分声明后才能在可执行部分使用。用户自定义异常对应的错误不一定是Oracle错误，例如它可能是一个数据错误。

  • 游标：

    • 游标的属性及意义
      • SQL%ROWCOUNT，成功执行的数据行数
      • SQL%FOUND，值为TRUE代表增删改或单行查询操作成功
      • SQL%NOTFOUND，值为TRUE代表增删改或单行查询操作失败
      • SQL%ISOPEN ，处于执行过程中为真，结束后为假

  • 使用游标

    • 声明游标，进行查询:

       CURSOR 游标名称 IS SELECT 字段1, 字段2 FROM 表;
      

    • 打开游标
      open cursor;

    • 使用游标

     Fetch 游标  Into 变量;
    If 变量%Notfound Then
                ...
    else
                ...
     End If;   
    

    • 关闭游标
      close 游标;

---

• forall
  • 基于下标，遍历到的下标必须存在，否则报错：forall i in 最小值 … 最大值
  • 基于下标，跳过下标不存在的元素：forall i in indices of 集合变量
  • 基于元素值：forall i in values of 集合变量

---

• BULK COLLECT
  • 能进行批量检索，会将检索结果结果一次性绑定到一个集合变量中，而不是通过游标cursor一条一条的检索处理
  • SELECT 字段 BULK COLLECT INTO 变量（集合） FROM 表 where 条件;
  • fetch into

//创建游标
cursor 游标 is select 字段 from 表 where 条件
//创建和游标元素类型一样的指定大小的联合数组类型
type 集合变量类型 is varray(大小) of 游标%rowtype
//声明是集合数组类型的变量
集合变量 集合变量类型;
begin 
    fetch 游标 bulk collect into 集合变量 [limit rows];
    //遍历集合，forall批量操作
    //SAVE EXCEPTIONS->当在执行过程中如果遇到异常，数据处理会继续向下进行，发生的异常信息会保存到SQL%BULK_EXCEPTONS的游标属性中
        forall i in 起始值（1） .. 集合变量.count  SAVE EXCEPTIONS
            update 表 set a=b where 集合变量(i);
        COMMIT;
    END

• RETURNING INTO
  • 在进行insert、update和delete操作的时候，都可以在末尾加入returning into字句。这字句的作用是将进行DML操作影响到数据行的列值，保存进指定的PL/SQL变量中。
    insert into 表 vlues (值1，值2，值3) returning 字段1,字段2 into 变量1 ,变量2