Oracle数据库（索引、视图、伪列与伪表）

知识点一、PL/SQL
Procedure Language/SQL是一门编程语言，Oracle公司对SQL进行了扩展，扩展后的SQL，就叫PL/SQL，功能更强大，面向过程语言，模块化编程语言，用于开发基于数据库的应用程序。下面关于PL/SQL编程基础主要介绍两点PL/SQL程序块于PL/SQL语句。

1、PL/SQL程序块

PL/SQL程序的基本单元，按照指定的方式，进行定义的一段程序。

   匿名块
      不能被其他程序调用
      可以临时使用
      匿名块的代码不会存储在数据库中
   命名块
      触发器
      存储过程
      函数
      包
      包体
      有名字的程序块
      可以重复调用
      会被编译并存储在数据库中
   子程序
      命名块
      有时候特指存储过程和函数

关于匿名块介绍

   语法：  [declare]  --定义部分：定义变量
          begin  --执行部分：sql语句、pl/sql块 
          [exception]  --异常处理
          end;  --块结束标记
   定义部分：用于定义常量、变量、游标、异常、数据类型
   执行部分：实现应用模块功能，包含了要执行的PL/SQL语句和SQL语句
   异常处理：处理执行部分可能出现的运行错误 
    begin
        dbms_output.put_line('hello world!');
    end;
    PL/SQL中 ';'只表示语句的结束，运行需要输入'/'
    set serveroutput on; 默认输出关闭，如需输出结果，需要手动设置开启
    dbms_output  Oracle提供的系统包
    put_line是dbms_output包下的过程，用于输出字符串信息
    PL/SQL程序块可以使用Oracle提供的SQL函数

关于变量和常量介绍

   定义变量或常量语法：
      变量名 [constant] 数据类型 [not null] [:= | default 表达式]
      := 赋值运算符
      constant 表示定义的是常量，必须对它初始化
      not null 用于指定变量不能为null，声明为not null的变量必须有初始化赋值

2、PL/SQL编程基础

具有特殊含义的符号

  算术运算符
     + - * / **(幂运算符)

  比较（关系）运算符
      >  >=  <  <=
      不等于：！=  <>  ^=
      等于：=

  is null      is not null
  like
  between...and...
  in()

  逻辑运算符
     and  or  not

  赋值运算符
     :=
  字符串连接符
     ||

  语句终止符
    ;

  双引号变量符
    “变量名”  常用在用关键字定义变量名或变量名含有空格等情况时

  注释符
     --单行注释
     /*
      *多行注释
      */

  范围操作符
         ..     1..10       1到10

  属性提示符
       %        %type       %rowtype

标识符

     可用于所有需要起名字的地方
     直接写，要以英文字母开头
     如果不以英文字母开头，要以双引号引起来
     标识符能直接使用的字符大小写英文字母、数字、_ 、$、#，
     如果要包含其他的字符，    则必须用双引号变量符，
     不能直接是oracle关键字，如果是，必须用双引号变量符
     最大长度为30个字符

文本（字面量）

       能直接写的值
       数字文本
          整数、浮点数、科学计数法 5E6 → 5×10^6、幂操作 5**2 → 5^2
       字符文本
          用单引号引起来的单个字符
       字符串文本
          用单引号引起来的两个或两个以上的字符
          字符串中’‘代表一个普通字符’
       布尔文本
          true false
       日期时间文本
          用字符串表示的
          日期字符串必须符合oracle对时间的默认显示格式‘dd-mon-yy’

块的嵌套
PL/SQL块可以嵌套，嵌套层次没有限制

     declare
         v_name varchar2(30) := '外层块';
      begin
         declare 
            v_name varchar2(30) :='内层块';
         begin 
            dbms_output.put_line(v_name);
         end;
         dbms_output.put_line(v_name);
      end;
   
   
1
2
3
4
5
6
7
8
9
10
   
   

    
    
   
   
   
   
1
2
3
4
5
6
7
8
9
10

子块：被嵌套的块
主块：包含子块的块
变量的作用范围：
子块可以使用主块定义的任何标识符
主块不能引用子块的任何标识符
子块中变量与主块变量同名，则子块中使用的是自己

3、PL/SQL语句

语句必须以;结束
表达式不是语句，可以作为语句一部分
赋值语句：变量名 := 值|表达式
null语句
null
空值，表示未知，不存在
作为一条语句，表示空语句，是一条PL/SQL语句，
什么都不做 null;
begin
null;
end;
执行select语句
在PL/SQL中执行select语句时，必须使用into子句把查询结果赋给变量
(否则，运行时提示在此 SELECT 语句中缺少 INTO 子句)
into子句
作用select语句的查询结果赋给一些变量，用记录类型变量接收值
一行结果列值的个数必须与用于接收值的变量的个数相同
查询结果必须且只能返回1行，否则运行时提示返回的行数过多或未找到数据

declare
              v_id t_class.id%type;
              v_name t_class.name%type;
            begin
              select * into v_id, v_name from t_class;
              dbms_output.put_line(v_id);
            end;
   
   
1
2
3
4
5
6
7
   
   

    
    
   
   
   
   
1
2
3
4
5
6
7

执行DML语句

    PL/SQL中，可以直接使用DML语句、commit、rollback，PL/SQL默认开启了事务

分支语句
条件表达式的结果的类型必须是boolean

null，Unkown，–> false

if then
    if 条件表达式 then
        语句块
end if;
   
   
1
2
3
4
   
   

    
    
   
   
   
   
1
2
3
4

if then-else
    if 条件表达式 then
        语句块1
else
    语句块2
end if;
   
   
1
2
3
4
5
6
   
   

    
    
   
   
   
   
1
2
3
4
5
6

分支语句代码：
if then-elsif then-else
    if 条件表达式1 then
        语句块1
    elsif 条件表达式2 then
        语句块2
    elsif 条件表达式 then
        语句块3
    .
    .
    .
    else
        语句块
    end if;
   
   
1
2
3
4
5
6
7
8
9
10
11
12
13
14
   
   

    
    
   
   
   
   
1
2
3
4
5
6
7
8
9
10
11
12
13
14

declare
  is_married boolean;
begin
  if is_married then
    dbms_output.put_line('2 > 1');   
  elsif false then
    dbms_output.put_line('3 > 1');
  else
    dbms_output.put_line('我是else');
  end if;
end;

   
   
1
   
   

    
    
   
   
   
   
1

case.when语句

case [val]
    when 表达式1 then 
        语句块1;
    when 表达式2 then
        语句块2;
    .
    .
    .
    [else]
end case;
   
   
1
2
3
4
5
6
7
8
9
10
   
   

    
    
   
   
   
   
1
2
3
4
5
6
7
8
9
10

当指定了case关键字后的val的时候，表示的是用val与when后的值进行匹配
当没有指定case关键字后的val的时候，表示的是when后的表达式的结果类型必须为boolean

case.when语句代码：
begin
  case
    when false then
      dbms_output.put_line(1);
    when false then
      dbms_output.put_line(2);
    else
      dbms_output.put_line(3);
  end case;
end;
   
   
1
2
3
4
5
6
7
8
9
10
11
   
   

    
    
   
   
   
   
1
2
3
4
5
6
7
8
9
10
11

循环语句

loop
    基本循环
    默认循环条件为true
    loop
        /*循环体*/
    end loop;
    退出循环：结束循环语句的执行
        exit;直接退出
        exit when 条件表达式; 有条件退出
    结束本次循环，继续下一次循环：
        continue; 直接继续
        continue when 条件表达式; 有条件继续

循环代码（1）
declare
  v_loop number := 0;
begin
  loop     
    v_loop := v_loop + 1;
--    if v_loop > 10 then
      exit when v_loop > 10;
--    end if;
    dbms_output.put_line('xxxxxx');
  end loop;
end;
   
   
1
2
3
4
5
6
7
8
9
10
11
12
   
   

    
    
   
   
   
   
1
2
3
4
5
6
7
8
9
10
11
12

循环代码（2）
declare
  v_loop number := 0;
begin
  loop
    v_loop := v_loop + 1;
   if v_loop = 1 then
      continue;
    end if;
--      continue when v_loop = 4;
        dbms_output.put_line('xxxxxx');
  end loop;
end;
   
   
1
2
3
4
5
6
7
8
9
10
11
12
13
   
   

    
    
   
   
   
   
1
2
3
4
5
6
7
8
9
10
11
12
13

for-each增强型for循环

for
    for varName in [reverse] 开始值..结束值 
    loop
        /*循环体*/
    end loop;
varName，Oracle会自动定义，不需要显示定义它
自动修改循环变量的值
循环变量的初始值：如果按升序循环，初始值就是指定范围的开始值
                  如果按降序循环，初始值就是指定范围的结束值

增强型for循环代码：
declare
 couter number := 5;
begin
  for couter  in 10..1
  loop
    dbms_output.put_line(couter);
  end loop;
end;
   
   
1
2
3
4
5
6
7
8
9
   
   

    
    
   
   
   
   
1
2
3
4
5
6
7
8
9

while循环

while 条件表达式 
loop
    /*循环体*/
end loop;
   
   
1
2
3
4
   
   

    
    
   
   
   
   
1
2
3
4

while循环代码：
declare
 counter number := 1;
begin
  while counter < 10 
  loop
    dbms_output.put_line(counter);
    counter := counter + 3;
  end loop;
end;
   
   
1
2
3
4
5
6
7
8
9
10
   
   

    
    
   
   
   
   
1
2
3
4
5
6
7
8
9
10

知识点二、游标（cursor）

游标，存放的是指针，指针就是地址，游标是系统为用户分配的一块缓冲区，用于存放select语句的执行结果，游标作用：用于对查询语句返回的多条(0、1、多条)进行处理，利用游标可以从结果集中每次提取一条记录，关于游标存在游标变量、显示游标、定义显示游标等很多知识点，再次知识简单介绍一下。

  定义显示游标
     cursor 游标名 is select语句
     select语句不执行

  打开游标：
     open 游标名;
     分配一块空间，执行select语句，把查询结果集放到分配的中
     同时把这块空间的地址保存在游标中

  从游标中取数据：
     fetch 游标名 into var1,....|record
     从游标中存放的地址对应的内存空间中取数据
     一次取一行，每取一次之后，游标指针会自动移动到下一行

  关闭游标：
     close 游标名;
     游标用完后，要关闭，未关闭的游标会占用内存，耗费系统资源
     关了之后，就不可以再使用，否则无效的游标

  显示游标的属性：
     %found 最近一次取数据(fetch)，有没有取到数据，取到则值为true，没有取到值false
     %notfound
     %isopen判断游标是否打开
     %rowcount 用于记录当前从游标中已经取的记录个数

  使用游标步骤（较为麻烦，一般不适用下面这种方式，有简单的方式，下面会介绍）：
         打开游标
         从游标中取数据
         开始循环
         处理从游标取得数据
         退出循环
         关闭游标

游标for循环

忽略了显示的打开游标、关闭游标、从游标中取数据
for 记录变量名 in 游标名
loop
    /*循环体*/
end loop;
记录变量名,该变量不需要显示定义，记录类型，结构与查询结果相同

例：为每个员工的名字统一加后缀 _emp
方法一：

    declare
       cursor mycursor is select * from emp;
    begin
       for emp_record in mycursor
       loop
          update emp set ename = emp_record.ename || '_emp' where empno = emp_record.empno;
       end loop;
       commit;
    end;
   
   
1
2
3
4
5
6
7
8
9
   
   

    
    
   
   
   
   
1
2
3
4
5
6
7
8
9

方法二：

  declare
       cursor mycursor is select * from emp;
       emp_record emp%rowtype;
    begin
       open mycursor;
       loop
          fetch mycursor into emp_record;
          if mycursor%found then
             update emp set ename = emp_record.ename || '_emp' where empno = emp_record.empno;
          else
             exit;
       end loop;
       close mycursor;
       commit;
    end;
   
   
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
   
   

    
    
   
   
   
   
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

知识点三、异常

在这里只是简单说明一下数据库中异常的常识，用的异常的地方还需要查找资料。

异常的组成部分：
    ORA-01476: 除数为 0
    01476      错误编号
    除数为 0， 错误信息
    ZERO_DIVDE 错误名
预定义异常：
    no_date_found   01403   select未查到数据
    too_many_rows   01422   返回的结果查过一行
    ZERO_DIVDE      01476   除数为0
    value_error         数据超出变量长度
    cursor_already_open    游标已经打开
    invalid_cursor      在不合法的游标上进行了操作
    invalid_number      字符串转换为数字错误
异常处理部分
    exception
        when 异常名 then 异常处理
        .
        .
        .
        when others then 其他异常处理
    预定义函数
        sqlcode 返回错误代码
        sqlerrm  返回错误消息
自定义异常
    异常名字 exception;
    pragma exception_init(异常名字,oracle_error_number)
    oracle_error_number:-20000~-20999