报表查询之存储过程细节要点

 

存储过程|细节|要点

 

一. 语法声明

创建存储过程语法:

CREATE[OR REPLACE]PROCEDURE [存储过程名称]

[ (argment [ { IN | IN OUT }] Type,

      argment [ { IN | OUT | IN OUT } ] Type ]

{ IS | AS}

<类型.变量的说明>

BEGIN

<执行部分>

EXCEPTION

<可选的异常错误处理程序>

END;

PS：红色必须、灰色可选、蓝色表示内容区域

二. 细节/要点

1. 过程参数：输入参数(IN) /  输出参数(OUT) /  输入输出参数(IN OUT)

                          这里值得注意的是,OUT返回值除了基础数据类型,还可以是一个集合,如:OUT sys_refcursor

2. 过程内部<类型.变量的说明>：

    第一种方式：

[变量名] [变量类型](长度) --例如:l_user_name  varchar2(50);

    第二种方式：

[变量名] [变量类型](长度) := 值 --例如:l_user_name  varchar2(50):= 'weiwei';

    第三种方式【自定义变量类型】：

create or replace type t_vc is table of varchar2(100); -- 创建自定义变量类型

[变量名] [自定义变量类型] -- 例如:l_array  t_vc;

游标声明：

                      第一种方式：显示游标 (CURSOR)

CURSOR 游标名称 IS 查询语句; 例如:CURSOR account_cursor IS select user_account, ORG_ID from auth_user;

                      第二种方式：动态游标 (Ref CURSOR)

TYPE 类型名 IS REF CURSOR;别名 类型名;例如:TYPE cur_recyle IS REF CURSOR;c_cursor cur_recyle;

3. 过程内部<执行部分>：

   根据项目实例，分析执行部分中通常使用的技术要点：

   【INTO 的用法】

select myconvert(v_staffAccs,',') into l_array from dual; 

-- 这里用到了myconvert拆分函数、以及l_array自定义变量类型,作用是将过程的传入参数v_staffAccs以逗号分隔赋值到l_array自定义变量中。

-- dual是一张系统虚拟表，用来构成select的语法规则，用处例如：

   select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;--获得当前系统时间

   【循环的用法】

FOR语法： for 变量名 in 开始数值...结束数值 loop<执行内容>end loop; 

WHILE语法：while 条件 loop <执行内容>      end loop;

   【控制流语句用法】

if 条件 then     <逻辑处理> elsif 条件 then    <逻辑处理>else    <逻辑处理>end if;

-- IF ELSIF ELSE例子:declarenum number := 2;beginif num = 1 then     dbms_output.put_line('==> 1'); elsif num = 2 then    dbms_output.put_line('==> 2'); else    dbms_output.put_line(num);end if;end;

casewhen 条件 then      <逻辑处理> when 条件 then     <逻辑处理> else       <逻辑处理>end case;

-- case例子:declarecasewhen num = 1 then      dbms_output.put_line('==> 1'); when num = 2 then     dbms_output.put_line('==> 2'); else       dbms_output.put_line(num);end case; end;

   【执行动态的SQL语句】

语法：execute immediate 'SQL语句'例子：execute immediate 'select DUMMY from dual';

为何要这样动态执行？请看这个例子：select * from 'tsm_record_call'||'_20131121';这样执行会告诉你表名不存在。

从动态语句检索值(INTO子句)：execute immediate 'SQL语句' into 变量名; 

例子:execute immediate 'select DUMMY from dual' into str_content;

给动态语句传值(USING 子句)：execute immediate 'SQL语句' (or (into 变量名)) using 需要替换的值或变量;

例子：execute immediate 'select DUMMY from dual where DUMMY =:1' into str_content using '123';

例子：如果参数是多个 execute immediate 'select DUMMY from dual where DUMMY =:1 or DUMMY =:2' into str_content using '123','234';

                     execute immediate参考网页地址：点击打开链接

   【游标的生命周期】

OPEN c_cursor FOR -- 打开游标 FOR指向查询语句 sqlStr USING v_groupId,v_org_id,v_plansum; -- 动态查询语句 USING 加入动态参数Loop -- 打开一个循环  FETCH c_cursor into l_res_cust_id; -- 调用游标结果 赋值到 指定变量,此时,Loop循环每次循环FETCH只取NEXT数据,这里游标集合将会一条一条执行  EXIT WHEN c_cursor%NOTFOUND; -- 如果当前结果集没有内容 则跳出Loop循环  if 1=1 then     <逻辑内容>  else     goto startmark; -- 跳出循环end Loop; -- 结束循环close c_cursor; -- 循环执行结束,果断关闭游标<<startmark>> -- 跳出循环处IF c_cursor%ISOPEN = TRUE THEN -- 如果游标打开 则关闭  CLOSE c_cursor;END IF;

【游标的属性】

属性游标提供了属性，用来判断游标目前所处状态和游标的一些相关信息。

%isopen 判断游标是否处于打开状态(true|false);

%found %notfound 查看游标中是否还有数据行或者是没有数据行(ture|false);

%rowcount     返回游标中数据行的当前行数;

这些属性可以帮助在使用显示游标的时候去判断游标的打开和关闭状态，存在数据和不存在数据的状态，还可以取得当前游标数据集中的行数。

【异常处理】

异常里主要是进行回滚操作，返回错误自定义区分错误类型的错误编码，以及回滚事务rollback；

在存储过程begin块中执行UPDATE或INSERT操作后需要commit提交事务；

例如如下代码片段：

  exception    when no_data_found then    v_code   := '0002';    v_result := '没有找到相应数据！';    rollback;    when others then    v_code   := '1111';    v_result := '系统异常';    rollback;

【其他】

%TYPE 和 %ROWTYPE用法：点击打开链接