PL/SQL语句块基本语法（ORACLE存储过程，函数，包，游标）

朴灿烈づ我的快乐病毒、 2022-05-24 07:44 244阅读 0赞

1、  PL/SQL语句块

PL/SQL语句块只适用于Oracle数据库，使用时临时保存在客户端，而不是保存在数据库。

基本语法:

declare

变量声明、初始化

begin

业务处理、逻辑代码

exception

异常捕获

end;

变量声明：<变量名>  <类型及长度>  \[:=<初始值>\]

例：v\_name varchar2(20):=’张三’;

例：见第3节

2、  循环语句

loop循环语法：

loop

exit  when  表达式

end loop;

while循环语法：

while 表达式 loop

end loop;

for循环语法:

for  <变量>  in  <变量取值范围（小值..大值，如1..100）> loop

end loop;

for循环的变量可不做声明及初始化。

例：见第3节

3、  if判断语句

基本语法：

if  <表达式>  then

…

else  if  <表达式>  then

…

else

…

end  if;

例：

declare

v\_identity number(4):=0;

begin

loop

if v\_identity=1 then

dbms\_output.put\_line('v\_identity=1');

else if v\_identity=3 then

dbms\_output.put\_line('v\_identity=3');

else if v\_identity=6 then

exit;

else

dbms\_output.put\_line('v\_identity is not 1 or 3');

end if;

end if; \-- 注意，有多少个if就要有多少个end if结束标志。

v\_identity:=v\_identity+1;

end loop;

exception

when others then dbms\_output.put\_line('error!');

end;

4、  分支case

基本语法：

case  <变量>

when  常量  then

…

when  常量  then

…

else

…

end case;

例：

declare

v\_number number(4):=3;

v\_string varchar(20):='abc';

begin

case v\_number

when 1 then

dbms\_output.put\_line('v\_number is '||1);

when 2 then

dbms\_output.put\_line('v\_number is '||2);

when 3 then

dbms\_output.put\_line('v\_number is '||3);

end case;

case v\_string

when 'ab' then

dbms\_output.put\_line('v\_string is '||'ab');

when 'bc' then

dbms\_output.put\_line('v\_string is '||'bc');

else \-- 缺省匹配

dbms\_output.put\_line('v\_string is other value');

end case;

exception

when others then dbms\_output.put\_line('error!');

end;

5、  异常(exception)

声明异常语法：<异常名>  exception;

抛出异常语法：raise  <异常名>;

捕获异常语法：when  <异常名>  then  异常处理语句;

例：

declare

v\_input varchar2(1):='&throw';\-- 动态输入

v\_exception\_1 exception; \-- 自定义异常

v\_exception\_2 exception;

others exception; \-- 系统异常

begin

if v\_input='1' then

raise v\_exception\_1; \-- 抛出异常

else if v\_input='2' then

raise v\_exception\_2;

else

raise others;

end if;

exception

\-- 捕获异常

when v\_exception\_1 then dbms\_output.put\_line('throw exception: v\_exception\_1');

when v\_exception\_2 then dbms\_output.put\_line('throw exception: v\_exception\_2');

when others then dbms\_output.put\_line('throw exception: others');

end;

6、  游标(cursor)

声明游标语法：cursor  <游标名>  is  select语句;

声明ref游标语法:<游标名>  is  ref  cursor;

打开游标语法：open  <游标名>;

移动游标并获取数据语法：fetch  <游标名>  into  <用于保存读取的数据的变量的名>;

关闭游标语法:close  <游标名>;

游标属性（游标的属性必须在关闭游标之前）：

%isopen: 判断游标是否打开

%notfound: 找不到数据时

%found:

%rowcount: 返回当前游标已扫描的数据行数量

游标分类：1、显示游标（自定义游标）；2、隐示游标（系统游标）；3、REF游标

例：

declare

v\_row t\_test%rowtype; \-- 匹配t\_test表中一行所有的数据类型

cursor v\_cur is select \* from t\_test;\-- 声明游标

begin

open v\_cur;\-- 打开游标

loop

fetch v\_cur into v\_row;\-- 将游标所在行的数据转存到v\_row中

exit when v\_cur%notfound; \-- 当游标到最后一行时跳出

dbms\_output.put\_line('id = '||v\_row.t\_id||'  name = '||v\_row.t\_name||'  msg = '||v\_row.t\_msg);

end loop;

close v\_cur;\-- 关闭游标

exception

when others then dbms\_output.put\_line('throw exception: others');

end;

\-- REF游标 --

create or replace package upk\_select\_test

as type uc\_test is ref cursor; \-- 声明ref游标

end upk\_select\_test;

\-- 存储过程中调用ref游标，并将查询结果以游标的方式返回

create or replace procedure up\_select\_test\_2

(uc\_result out upk\_select\_test.uc\_test)

begin

open uc\_result for select \* from t\_test;

end up\_select\_test\_2;

7、  通配类型操作符

%type: 通配某行某列数据类型，如v\_name t\_test.t\_name%type;通配表t\_test中的t\_name。

%rowtype: 通配一行所有列的数据类型，如 v\_row t\_test%rowtype;匹配t\_test表中一行

所有的数据类型。

8、  存储过程(procedure)

基本语法：

create  procedure  <过程名>(<参数列表,无参时忽略>)

as|is

变量声明、初始化

begin

业务处理、逻辑代码

exception

异常捕获、容错处理

end  <过程名>;

参数:<参数名> in|out|in out  <参数类型，无长度说明> ，如：v\_name  varchar2

in：入参

out：出参

in out：出入参

注：as|is表示as或is

调用语法：

1)、exec  <过程名>;

2)、execute  <过程名>;

3)、在PL/SQL语句块中直接调用。

例：

create or replace procedure up\_wap(v\_param1 in out varchar2,v\_param2 in out varchar2)

v\_temp varchar2(20);

begin

dbms\_output.put\_line('交换前参数1：'||v\_param1||'  参数2：'||v\_param2);

v\_temp:=v\_param1;

v\_param1:=v\_param2;

v\_param2:=v\_temp;

dbms\_output.put\_line('交换后参数1：'||v\_param1||'  参数2：'||v\_param2);

exception

when others then dbms\_output.put\_line('There is a error when the procedure up\_wap executing!');

end up\_wap;

\-- 调用存储过程

declare

v\_param1 varchar2(20):='param1';

v\_param2 varchar2(20):='param2';

begin

up\_wap(v\_param1 => v\_param1,v\_param2 => v\_param2);

end;

9、  自定义函数(function)

基本语法：

create  function  <函数名>(<参数列表,无参时忽略>)

return  <返回值类型，无长度说明>

as|is

变量声明、初始化

begin

业务处理、逻辑代码

return  <返回的值>;

exception

异常捕获、容错处理

end  <函数名>;

参数：in  入参

注：只有入参的类型。

在存储过程和自定义函数中的参数的传递（入参和出参）不能使用%type或%rowtype匹配，不能使用空值null，但是存储过程可以返回空值。

例：

create function uf\_select\_name\_by\_id\_test(v\_id in number)

return varchar2

v\_name t\_test.t\_name%type;

begin

select t\_name into v\_name from t\_test where t\_id=v\_id;

return v\_name;

exception

when others then dbms\_output.put\_line('error');

end uf\_select\_name\_by\_id\_test;

select uf\_select\_name\_by\_id\_test(1) 姓名 from dual;\-- select调用

declare \--pl/sql语句块调用

v\_name varchar2(20);

begin

v\_name:=uf\_select\_name\_by\_id\_test(1);

dbms\_output.put\_line('name = '||v\_name);

end;

10、包(package)

封装，可以封装过程(procedure)、函数(function)和变量。

注意，在包(package)中声明的过程(procedure)和函数(function)必须在包的实现体

（package body）中定义实现。

基本语法：

create  package  <包名>

as|is

变量声明

存储过程声明

自定义函数声明

end  <包名>;

create  package  <包名,与声明部分一致>

as|is

存储过程的代码实现

自定义函数的代码实现

end  <包名>;

例：

\-- 创建包upk\_hello

create or replace package upk\_hello

v\_hello\_world varchar2(20):='hello world'; \-- 声明变量

procedure up\_hello\_world(v\_name in varchar2);\-- 声明过程

function uf\_hello\_world(v\_name in varchar2) return varchar2;\-- 声明函数

end upk\_hello;

\-- 实现包(upk\_hello)里声明的方法

create or replace package body upk\_hello

procedure up\_hello\_world(v\_name in varchar2)

v\_string varchar2(100);

begin

v\_string:=v\_name||' say hello world!';

dbms\_output.put\_line(v\_string);

exception

when others then dbms\_output.put\_line('error');

end up\_hello\_world;

function uf\_hello\_world(v\_name in varchar2) return varchar2

v\_string varchar2(100);

begin

v\_string:=v\_name||' say hello world!';

return v\_string;

exception

when others then dbms\_output.put\_line('error');

end uf\_hello\_world;

end upk\_hello;

\-- 包的调用

declare

v\_msg varchar2(100);

begin

upk\_hello.up\_hello\_world('bing');

v\_msg:=upk\_hello.uf\_hello\_world('admin');

dbms\_output.put\_line(v\_msg);

dbms\_output.put\_line(upk\_hello.v\_hello\_world);

end;

PL/SQL语句块基本语法（ORACLE存储过程，函数，包，游标）

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关【Oracle】存储过程基本语法

相关 oracle存储过程的if for 游标的语法

相关 Oracle存储过程基本语法介绍

相关 oracle 存储过程以及plsql语句块

相关 PL/SQL语句块基本语法（ORACLE存储过程，函数，包，游标）

相关 Oracle：PLSQL——游标

相关 Oracle 匿名块和命名块存储过程函数包触发器

相关 oracle（sql）基础篇系列（五）——PLSQL、游标、存储过程、触发器

相关 Oracle--plsql创建存储过程

相关 oracle存储过程--游标