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

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

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;

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;

    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;

  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)

is
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)

is
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
is
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 

is
  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

is
  procedure up_hello_world(v_name in varchar2)

  is
    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
  is
    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;

/