Oracle：3、plsql程序相关笔记

最新推荐文章于 2021-03-18 14:55:54 发布

博风

最新推荐文章于 2021-03-18 14:55:54 发布

阅读量438

点赞数

分类专栏： # Oracle 文章标签： oracle plsql

本文链接：https://blog.csdn.net/bof_jangle/article/details/51050419

版权

Oracle 专栏收录该内容

16 篇文章 1 订阅

订阅专栏

一、变量申明

1.1普通变量的申明

declare
 out_date DATE;
 out_text varchar2(50);
 out_num  binary_integer;
 out_boolean  boolean;
begin
 ----pl/sql程序的执行部分----
end;

这些变量只可以在该程序块中使用。

1.2%TYPE变量

var_name table.column%type; -- 声明表中某列格式一样的数据类型。
begin
 select [column] into var_name from [table] where ... -- 将某列的结果赋值给var_name变量。
end;

1.3自定义复合变量：

type record_name is record(
field1_name data_type [not null] [:=default_value],
...
fieldn_name data_type [not null] [:=default_value]
);

record_name变量名，field1_name字段名，data_type类型。可选的有是否为空，默认值。
通过record_name.field1_name来获取数据。
可通过select [column],[column] into record_name from table_name where...来插入数据。即将某表的4个字段值赋值给record_name（按顺序匹配）。

1.4%ROWTYPE类型

结合%TYPE和自定义类型，有%ROWTYPE类型：

row_typename table_name%rowtype;

如此是将表table_name的表结构作为row_typename变量的数据结构，如此他可以接收table_name表的数据。

select * into row_typename from table_name where ...

如此可用row_typename.xxx调用数据。

二、判断

2.1 if判断


if <condition> then
   ...
elseif <condition> then
   ...
else
   ...
end if;

2.2 case判断

case <selector>
 when <expreseeion1> then ...;
 when <expreseeion2> then ...;
 ...
 else
 ...
end case;

case
 when <condition1> then ...;
 when <condition2> then ...;
 ...
 else
 ...
end case;

存在<selector> 则由when子句判断是否匹配selector，不存在<selector>则由when子句逻辑做判断。(与SQL标准的简单CASE和搜索CASE类似）

三、循环

3.1 loop循环

begin
 loop
  ...
 (exit when [condition])
 end loop;
end;

3.2 while循环

begin
 while [condition] loop
  ...
 end loop;
end;

3.3 for循环

begin
 (i自增)
 for i in 1…100 loop
  ...
 end loop;
 (i自减)
 for i in reverse 1…100 loop
  ...
 end loop;
end;

四、游标

%FOUND ：如果sql语句至少影响了一行数据，返回true，否则返回false
%NOTFOUND ：与%FOUND相反
%ISOPEN ：游标是否打开。打开为TRUE，关闭为FALSE。
%ROWCOUNT ：返回影响的行数。

对于隐式游标而言，每次执行的sql语句，默认的该sql语句的游标名称为SQL，故我们调用时：SQL%ROWCOUNT如此使用。则可调用隐式游标。

4.1隐式游标for循环

begin
 for p in (select * from table_name)
 loop
  ...
   p.column（通过循环变量名.列名，访问数据内容）
  ...
 end loop;
end;

(select * from table_name) 其实是一个游标，显式的游标for循环如下：

declare
 cursor cursor_name is select * from table_name;
begin
 for p in cursor_name loop
 ...
   p.column（通过循环变量名.列名，访问数据内容）
 ...
 end loop;
end;

4.2传统的游标使用

其实，游标for循环就是结合了游标与for循环而得到的简易写法，oracle在游标for循环中隐式的执行了相关的操作。其游标的使用过程包含：
i、定义游标

cursor cursor_name[(parameter[,parameter]...)]
[return return_type]
is select_statement;

这个定义类似其他编程语言函数的定义，它可以有输入参数parameter，可以有返回值(也可以没有输入参数和返回值)。
最后必须跟上的是 select语句。用于将游标指向一个结果集。一般parameter用于select语句的where子句中。
parameter参数形式：
parameter_name [in] datatype [{:=|defalut} expression]
如：

cursor cursor_name(p in number defalut 20)
is select * from table_name
where column_no=p;

ii、打开游标

open cursor_name[(value[,value]...)];

如：

open cursor_name(9)          --不输入则默认参数20.

此过程包含两个步骤：

将符合条件的记录送入内存。
将指针指向第一条记录。

iii、提取游标数据

fetch cursor_name into {variable_list|record_variable}

如：

fetch cursor_name into row_type;

将游标的一行数据赋值给对应的变量（变量row_type需要定义），并将指针指向下一行。
数据的提取一般会结合循环，我们一般会用到%FOUND来判断游标中是否还有数据：

while cursor_name%found loop
  ...
 end loop;

iv、关闭游标

 close cursor_name;

五、异常

exception下的内容就异常处理区域。在...2中出现的异常会转至exception中，通过when子句判断异常是哪种类型。
其中异常分三类：

5.1预定义异常(21个)：


declare
...1
begin
...2
exception
 when <exception> then
 ...
 when others then
 if sqlcode=-1843 then
 ...
end;

预定义异常及oracle已经将其命名的异常，可以这exception块中直接引用。如：

when <exception> then ...

5.2非预定义异常：

Oracle定义了上千个异常，但只有错误编号和相关描述，未进行命名。故可以通过sqlcode的值来判断异常类型。

when others then
if sqlcode=-1843 then
...

也可以在申明时，申明一个异常，绑定相关的异常代码，之后就可以像预定义异常一样使用了。

declare
exception_name exception;   --声明一个异常。
pragma exception_init(exception_name,-1843)    --绑定异常代码。
begin
...
exception
 when exception_name then
 ...
end;

注：exception_init函数是编译时运行的函数，故只能在代码的申明部分。

5.3用户定义的异常

如其他编程语言一样，我们可以主动的抛出异常(自定义异常需要先进行声明):

raise exception_name;

如：

declare
 exception_name exception; --先声明自定义异常
begin
 ...
 if <condition> then
  raise exception_name;  --主动抛出异常
 end if;
exception
 when exception_name then --进行异常处理
 ...
end;