工欲善其事必先利其器

Oracle-PL/SQL编程_oracle

文章目录

一、概述

PL/SQL是在sql的基础上做了扩充，是为了处理sql无法进行的一些复杂的业务逻辑的处理。

1、程序结构

一个完整的PL/SQL语句块一般有3个部分组成。
1：声明部分（declare）
2：执行部分（begin）
3：异常处理部分（exception）

declare
  声明部分
  begin
    执行部分
  exception
    异常处理部分
  end

二、常量

1、基本数据类型

1：数值类型
number，number(x,y)，x是有效位，y是小数位，例如number(5,2)表示定义一个有效位是5位，小数位是2的。
2：字符类型
varchar2，char
varchar2用于存储可变长度的字符串，char类型用于存储指定长度的字符串。
3：日期类型
date,timestamp
data类型存储日期和时间信息，timestamp存储于data类似，不过还包括秒的小数部分
4：布尔类型
boolean
5：lob类型
blob,clob,nclob,bfile
clob,nclob存放文本数据，blob存放二进制数据，bfile存放指向操作系统文件的指针。

2、特殊数据类型

除了上面的基本数据类型，PL/SQL还提供了3种特殊的数据类型，这3种数据类型都是在基本数据类型基础上建立的。

语法格式如下

type myrecord is record(
field1 datatype [not null][:=default value]
)

定义一个记录类型myrecord，用于存储student表中的学生姓名和班级名称

declare
    type myrecord is record
    my_name varchar2<20>,
    my_class varchar2<20>;
    Myrecord myrecord;
    begin
        select name,class into myrecord from student where id = 1;
        dbms_output.put_line<'所查询的学生姓名是：' || Myrecord.my_name ||',他的班级是：‘|| Myrecord.my_class >;
    end;

3、%type类型

在oracle中我们经常用到字段的数据类型，当我们定义一个数据类型想和表中已有的字段类型一致的时候我们可以使用%type类型，例如我们声明一个与student表中name字段类型一致的var_name

var_name student.name%type

4、%rowtype类型

通过名字可以看出rowtype是表示数据表中某一行的所有数据类型，所以用他定义以后可以记录一行数据。例如声明一个用于存储student表中每行记录的变量var_row

var_row student %rowtype

声明一个用于存储student表中id=1的一行记录var_row,并显示基本信息

declare
    var_row student%rowtype;
begin
    select * into var_row from student where id =1;    
    dbms_output.put_like<'所查询的学生姓名是：' || var_row.name>
end;

5、记录表类型

上面我们说的那些类型只能存储一个数据，或者一行数据，不能存储多行数据，现在我们需要存储多行数据。语法如下

type

定义一个记录表类型用于存储student表中的数据，并显示

declare
    type mytabletype is table of student%rowtype index by binary_integer;
    Mytable mytabletype;
begin
    select * into mytable<1> from student where id = 1;
    select * into mytable<2> from student where id = 2;
    dbms_output.put_line<'学生姓名是：'|| mytable<1>.name>;
    dbms_output.put_line<'学生姓名是：'|| mytable<2>.name>;
end;

三、变量

1、变量的定义

我们必须先在声明部分定义变量，与其他编程语言不通，PL/SQL语言中要求变量名在前，数据类型在后面，定义变量的语法如下。

变量名 数据类型 [默认值 |: = 初始值]

例如下面的代码

var_name varchar2(20):='张三'

2、变量的赋值

1：直接赋值

name:='张三';
age:=age+10;

2：交互赋值

name:=&temp;

运行此代码会提示输入temp的值，然后把输入的值赋给name变量

3：使用select查询语句中的into子句

select name into name_temp from student where id = 1;

3、变量的作用域

变量的作用域，在一般的PL/SQL快中，变量的作用域从声明开始，一直到块的结束。如果存在块嵌套，则外部声明的为全局变量，可以在外部使用也可以在内部使用。

四、常量的定义

1、常量

常量的值在程序运行过程中保持不变，常量的声明方式与变量类似，但要包括关键字constant，常量定义的同时要赋予初值，如下所示。

pi constant number:=3.1415;

五、条件语句

if
case

1、if语句

有三种形式：if…then语句、if…then…else语句和if…then…elsif语句。

1、if…then语句

基本语法格式如下

if 条件表达式 then
语句序列;
end if;

当条件表达式结果是true时，程序运行then下面的语句序列，如果为false时，则跳过then下面的内容而去运行end if后面的语句。

2、if…then…else语句

基本语法格式如下

if 条件表达式 then
语句序列1;
else
语句序列2;
end if;

当条件表达式结果是true时，程序运行语句序列1，如果为false时，运行语句序列2。

3、if…then…elseif语句

基本语法格式如下

if 条件表达式 1 then
语句序列1;
elseif 条件表达式2
语句序列2;
...
else
条件表达式n
end if;

当条件表达式结果是true时，程序运行语句序列1，如果为false时，运行语句序列2。

2、case语句

语法如下

case 选择器表达式
    when 条件 1 then 语句序列 1;
    when 条件 2 then 语句序列 2;
    ...
    when 条件 n then 语句序列 n;
    else 语句序列 n+1;
    end case;

六、循环语句

loop
while
for

1、loop循环

这种循环将循环条件包含在循环体内，loop循环会先执行一次循环体，然后判断是否满足设定的条件来决定循环是否继续执行。语法格式如下

loop
语句序列;

首先执行语句序列，然后根据条件表达式的值来判断下一步的操作，如果条件表达式的值为true，则退出循环体，如果条件表达式的值为false，则继续执行循环体。

2、while循环

while循环是先判断条件，如果条件成立就执行循环体，如果不成立，就退出循环，语法格式如下。

while 条件表达式 loop
语句序列;
end loop;

运行的时候，首先判断条件表达式，如果结果是true，则运行循环体内的语句序列，如果为false，则退出循环。

3、for循环

前面两种循环都要根据条件是否成立来决定循环次数，for循环可以自己定义循环次数。语法格式如下

for 循环变量 in [reverse] 开始数值...结束数值 loop
语句序列;
end loop;

当循环变量大于开始数值，小于结束数值的时候，执行语句序列，否则退出循环，默认情况下循环变量时循环递增的，如果使用了reverse参数，则循环递减。

七、异常处理

一个完整的PL/SQL语句块一般有3个部分组成，其中第3个部分就是异常处理部分，oracle提供了异常情况（exception）和异常处理（exception handler）来实现错误处理。一个错误对应一个异常，当出现错误的时候，异常处理器会捕获对应的异常，由异常处理器来处理运行时的错误。

1、异常的种类

oracle运行时错误可以分为oracle错误和用户自定义错误，与此对应，根据异常产生的机制和原理，可将Oracle的系统异常分为3种。
1：预定义异常：对应于oracle错误，是Oracle系统自身提供的，用户可以在PL/SQL异常处理部分使用名称对它们进行表示。对这些异常情况的处理，用户无须在程序中定义，是由oracle自动触发。
2：非预定义异常：即其他标准的oracle错误，对这种异常情况的处理，需要用户在程序中定义，然后由oracle自动将其引发。
3：用户定义异常：程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理需要用户在程序中定义，然后显示的在程序中将其引发。

2、异常处理过程

1、异常定义

异常变量 excption

如果是非预定义的异常，还需要为错误编号关联这个异常变量，语法如下

prama excption_int(异常变量,-#####)

其中，“#####” 为oracle的错误编号

2、异常关联

在执行部分当错误发生的时候关联与错误对应的异常，由于系统可以自动识别oracle内部错误，所以当错误发生时系统会自动关联与之对应的预定义异常或非预定义异常，但是用户定义的错误，系统无法自动识别，需要用户编程用于关联，关联的语法如下。

raise user_define_recption

3、异常捕获与处理

当错误产生的时候，在异常处理部分通过异常处理器捕获异常并进行异常处理。语法如下。

exception
when 异常 1 [OR异常......]then 处理序列语句 1;
when 异常 2 [OR异常......]then 处理序列语句 2;
...
when 异常 n [OR异常......]then 处理序列语句 n;
end;

一个异常只能被一个异常处理器捕获，并进行处理
一个处理器可以捕获多个异常，此时通过OR连接

3、异常处理实例

查询student表中name为“张三”的学生信息，如果不存在这个同学，则输出“没有数据记录返回”，如果存在多个记录，则输出“返回数据记录超过一行”。

declare
    var_name student.name%type;
begin
    select name into var_name from student where name = '张三';
    if sql%found then        --如果是一行，就显示结果
        dbms_output.put_line<'学生姓名：' || var_name>;
    end if;
exception
    when too_many_rows then    --捕获异常
        dbms_output.put_line<'返回数据记录超过一行'>;
    when no_data_found then
        dbms_output.put_line<'没有数据记录返回'>;
    end;

八、总结

这里的相关内容还没有整理完毕，文章后面持续更新，建议收藏。

文章中涉及到的命令大家一定要像我一样每个都敲几遍，只有在敲的过程中才能发现自己对命令是否真正的掌握了。