以下为《Oracle存储过程常用技巧》的无排版文字预览，完整格式请下载

下载前请仔细阅读文字预览以及下方图片预览。图片预览是什么样的，下载的文档就是什么样的。

我们在进行pl/sql编程时打交道最多的就是存储过程了。存储过程的结构是非常的简单的，我们在这里除了学习存储过程的基本结构外，还会学习编写存储过程时相关的一些实用的知识。如：游标的处理，异常的处理，集合的选择等等 1.存储过程结构 1.1 第一个存储过程

Java代码
?

create?or?replace?procedure?proc1( ??

??p_para1?varchar2, ??

??p_para2?out?varchar2, ??

??p_para3?in?out?varchar2 ??

)as? ??

?v_name?varchar2(20); ??

begin ??

??v_name?:=?'张某某'; ??

??p_para3?:=?v_name; ??

??dbms_output.put_line('p_para3:'||p_para3); ??

end;??

上面就是一个最简单的存储过程。一个存储过程大体分为这么几个部分: 创建语句：create or replace procedure 存储过程名 如果没有or replace语句，则仅仅是新建一个存储过程。如果系统存在该存储过程，则会报错。Create or replace procedure 如果系统中没有此存储过程就新建一个，如果系统中有此存储过程则把原来删除掉，重新创建一个存储过程。 存储过程名定义：包括存储过程名和参数列表。参数名和参数类型。参数名不能重复， 参数传递方式:IN, OUT, IN OUT IN 表示输入参数，按值传递方式。 OUT 表示输出参数，可以理解为按引用传递方式。可以作为存储过程的输出结果，供外部调用者使用。 IN OUT 即可作输入参数，也可作输出参数。 参数的数据类型只需要指明类型名即可，不需要指定宽度。 参数的宽度由外部调用者决定。 过程可以有参数，也可以没有参数 变量声明块：紧跟着的as (is )关键字，可以理解为pl/sql的declare关键字，用于声明变量。 变量声明块用于声明该存储过程需要用到的变量，它的作用域为该存储过程。另外这里声明的变量必须指定宽度。遵循PL/SQL的变量声明规范。 过程语句块：从begin 关键字开始为过程的语句块。存储过程的具体逻辑在这里来实现。 异常处理块：关键字为exception ，为处理语句产生的异常。该部分为可选 结束块：由end关键字结果。 1.2 存储过程的参数传递方式 存储过程的参数传递有三种方式:IN,OUT,IN OUT . IN 按值传递，并且它不允许在存储过程中被重新赋值。如果存储过程的参数没有指定存参数传递类型，默认为IN

Java代码
?

create?or?replace?procedure?proc1( ??

??p_para1?varchar2, ??

??p_para2?out?varchar2, ??

??p_para3?in?out?varchar2 ??

)as? ??

?v_name?varchar2(20); ??

begin ??

??p_para1?:='aaa'; ??

??p_para2?:='bbb'; ??

??v_name?:=?'张某某'; ??

??p_para3?:=?v_name; ??

??dbms_output.put_line('p_para3:'||p_para3); ??

??null; ??

end; ??

???? ??

Warning:?Procedure?created?with?compilation?errors ??

??

SQL>?show?error; ??

Errors?for?PROCEDURE?LIFEMAN.PROC1: ??

??

LINE/COL?ERROR ??

--------?---------------------------------------------------------------------- ??

8/3??????PLS-00363:?expression?'P_PARA1'?cannot?be?used?as?an?assignment?target ??

8/3??????PL/SQL:?Statement?ignored??

这一点与其它高级语言都不同。它相当于java在参数前面加上final关键字。 OUT 参数：作为输出参数，需要注意，当一个参数被指定为OUT类型时，就算在调用存储过程之前对该参数进行了赋值，在存储过程中该参数的值仍然是null.

Java代码
?

create?or?replace?procedure?proc1( ??

??p_para1?varchar2, ??

??p_para2?out?varchar2, ??

??p_para3?in?out?varchar2 ??

)as? ??

?v_name?varchar2(20); ??

begin ??

??v_name?:=?'张某某'; ??

??p_para3?:=?v_name; ??

??dbms_output.put_line('p_para1:'||p_para1); ??

??dbms_output.put_line('p_para2:'||p_para2); ??

??dbms_output.put_line('p_para3:'||p_para3); ??

end; ??

??

SQL>?var?p1?varchar2(10); ??

SQL>?var?p2?varchar2(10); ??

SQL>?var?p3?varchar2(10); ??

SQL>?exec?:p1?:='aaaa'; ??

SQL>?exec?:p2?:='bbbb'; ??

SQL>?exec?:p3?:='cccc'; ??

SQL>?exec?proc1(:p1,:p2,:p3); ??

p_para1:aaaa ??

p_para2: ??

p_para3:张某某 ??

SQL>?exec?dbms_output.put_line(:p2); ??

??

??

PL/SQL?procedure?successfully?completed ??

p2 ??

---------??

INOUT 是真正的按引用传递参数。即可作为传入参数也可以作为传出参数。

Java代码
?

1.3?存储过程参数宽度 ??

create?or?replace?procedure?proc1( ??

??p_para1?varchar2, ??

??p_para2?out?varchar2, ??

??p_para3?in?out?varchar2 ??

)as? ??

?v_name?varchar2(2); ??

begin ??

??v_name?:=?p_para1; ??

end; ??

??

SQL>?var?p1?varchar2(10); ??

SQL>?var?p2?varchar2(20); ??

SQL>?var?p3?varchar2(30); ??

SQL>?exec?:p1?:='aaaaaa'; ??

SQL>?exec?proc1(:p1,:p2,:p3); ??

???? ??

???? ??

ORA-06502:?PL/SQL:?numeric?or?value?error:?character?string?buffer?too?small ??

ORA-06512:?at?"LIFEMAN.PROC1",?line?8??

ORA-06512:?at?line?1??

首先，我们要明白，我们无法在存储过程的定义中指定存储参数的宽度，也就导致了我们无法在存储过程中控制传入变量的宽度。这个宽度是完全由外部传入时决定的。 我们再来看看OUT类型的参数的宽度。

Java代码
?

create?or?replace?procedure?proc1( ??

??p_para1?varchar2, ??

??p_para2?out?varchar2, ??

??p_para3?in?out?varchar2 ??

)as? ??

?v_name?varchar2(2); ??

begin ??

??p_para2?:='aaaaaaaaaaaaaaaaaaaa'; ??

end; ??

SQL>?var?p1?varchar2(1); ??

SQL>?var?p2?varchar2(1); ??

SQL>?var?p3?varchar2(1); ??

SQL>?exec?:p2?:='a'; ??

SQL>?exec?proc1(:p1,:p2,:p3);??

在该过程中，p_para2被赋予了20个字符a. 而在外部的调用过程中，p2这个参数仅仅被定义为varchar2(1). 而把p2作为参数调用这个过程，却并没有报错。而且它的真实值就是20个a

Java代码
?

SQL>?select?dump(:p2)?from?dual; ??

DUMP(:P2) ??

--------------------------------------------------------------------------- ??

Typ=1?Len=20:?97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97??

p2 ??

--------- ??

aaaaaaaaaaaaaaaaaaaa ??

???? ??

????再来看看IN?OUT参数的宽度 ??

create?or?replace?procedure?proc1( ??

??p_para1?varchar2, ??

??p_para2?out?varchar2, ??

??p_para3?in?out?varchar2 ??

)as? ??

?v_name?varchar2(2); ??

begin ??

??p_para3?:='aaaaaaaaaaaaaaaaaaaa'; ??

end; ??

??

SQL>?var?p1?varchar2(1); ??

SQL>?var?p2?varchar2(1); ??

SQL>?var?p3?varchar2(1); ??

SQL>?exec?proc1(:p1,:p2,:p3);??

执行这个过程，仍然正确执行。 可见，对于IN参数，其宽度是由外部决定。 对于OUT 和IN OUT 参数，其宽度是由存储过程内部决定。 因此，在写存储过程时，对参数的宽度进行说明是非常有必要的，最明智的方法就是参数的数据类型使用%type。这样双方就达成了一致。 1.3 参数的默认值 存储过程的参数可以设置默认值

Java代码
?

create?or?replace?procedure?procdefault(p1?varchar2, ??

????????????????????????????????????????p2?varchar2?default?'mark') ??

as? ??

begin ??

??dbms_output.put_line(p2); ??

end; ??

??

SQL>?set?serveroutput?on; ??

SQL>?exec?procdefault('a');??

mark 可以通过default 关键字为存储过程的参数指定默认值。在对存储过程调用时，就可以省略默认值。 需要注意的是：默认值仅仅支持IN传输类型的参数。OUT 和 IN OUT不能指定默认值 对于有默认值的参数不是排在最后的情况。

Java代码
?

create?or?replace?procedure?procdefault2(p1?varchar2?default?'remark', ??

????????????????????????????????????????p2?varchar2?) ??

as? ??

begin ??

??dbms_output.put_line(p1); ??

end;??

第一个参数有默认值，第二个参数没有。如果我们想使用第一个参数的默认值时 exec procdefault2('aa'); 这样是会报错的。 那怎么变呢？可以指定参数的值。

Java代码
?

SQL>?exec?procdefault2(p2?=>'aa');??

remark 这样就OK了，指定aa传给参数p2 2. 存储过程内部块 2.1 内部块 我们知道了存储过程的结构，语句块由begin开始，以end结束。这些块是可以嵌套。在语句块中可以嵌套任何以下的块。

Java代码
?

Declare?…?begin?…?exception?…?end; ??

create?or?replace?procedure?innerBlock(p1?varchar2) ??

as? ??

??o1?varchar2(10)?:=?'out1'; ??

begin ??

??dbms_output.put_line(o1); ??

??declare? ??

????inner1?varchar2(20); ??

??begin ??

????inner1?:='inner1'; ??

????dbms_output.put_line(inner1); ??

??

????declare? ??

??????inner2?varchar2(20); ??

????begin ??

??????inner2?:=?'inner2'; ??

??????dbms_output.put_line(inner2); ??

????end; ??

??exception? ??

????when?others?then ??

??????null; ??

??end; ??

end;??

需要注意变量的作用域。 3.存储过程的常用技巧 3.1 哪种集合? 我们在使用存储过程的时候经常需要处理记录集，也就是多条数据记录。分为单列多行和多列多行，这些类型都可以称为集合类型。我们在这里进行比较这些集合类型，以便于在编程时做出正确的选择。 索引表,也称为pl/sql表，不能存储于数据库中，元素的个数没有限制,下标可以为负值。

Java代码
?

type?t_table?is?table?of?varchar2(20)?index?by?binary_integer; ??

?v_student?t_table;??

varchar2(20)表示存放元素的数据类型，binary_integer表示元素下标的数据类型。 嵌套表，索引表没有 index by子句就是嵌套表，它可以存放于数据中，元素个数无限，下标从1开始，并且需要初始化

Java代码
?

type?t_nestTable?is?table?of?varchar2(20); ??

v_class?t_nestTable?;??

仅是这样声明是不能使用的，必须对嵌套表进行初始化，对嵌套表进行初始化可以使用它的构造函数

Java代码
?

v_class?:=t_nestTable('a','b','c');??

变长数组，变长数组与高级语言的数组类型非常相似，下标以1开始，元素个数有限。

Java代码
?

type?t_array?is?varray?(20)?of?varchar2(20);??

varray(20)就定义了变长数组的最大元素个数是20个 变长数组与嵌套表一样，也可以是数据表列的数据类型。 同时，变长数组的使用也需要事先初始化。 类型 可存储于数据库 元素个数 是否需初始化 初始下标值 索引表 否 无限 不需 嵌套表 可 无限 需 1 可变数组 可 有限(自定义) 需 1 由此可见，如果仅仅是在存储过程中当作集合变量使用，索引表是最好的选择。 3.2 选用何种游标? 显示游标分为：普通游标，参数化游标和游标变量三种。 下面以一个过程来进行说明

Java代码
?

create?or?replace?procedure?proccursor(p?varchar2) ??

as? ??

v_rownum?number(10)?:=?1; ??

cursor?c_postype?is?select?pos_type?from?pos_type_tbl?where?rownum?=1; ??

cursor?c_postype1?is?select?pos_type?from?pos_type_tbl?where?rownum?=?v_rownum; ??

cursor?c_postype2(p_rownum?number)?is?select?pos_type?from?pos_type_tbl?where?rownum?=?p_rownum; ??

type?t_postype?is?ref?cursor?; ??

c_postype3?t_postype; ??

v_postype?varchar2(20); ??

begin ??

??open?c_postype; ??

??fetch?c_postype?into?v_postype; ??

??dbms_output.put_line(v_postype); ??

??close?c_postype; ??

??open?c_postype1; ??

??fetch?c_postype1?into?v_postype; ??

??dbms_output.put_line(v_postype); ??

??close?c_postype1; ??

??open?c_postype2(1); ??

??fetch?c_postype2?into?v_postype; ??

??dbms_output.put_line(v_postype); ??

??close?c_postype2; ??

??open?c_postype3?for?select?pos_type?from?pos_type_tbl?where?rownum?=1; ??

??fetch?c_postype3?into?v_postype; ??

??dbms_output.put_line(v_postype); ??

??close?c_postype3; ??

end;??

cursor c_postype is select pos_type from pos_type_tbl where rownum =1 这一句是定义了一个最普通的游标，把整个查询已经写死,调用时不可以作任何改变。 cursor c_postype1 is select pos_type from pos_type_tbl where rownum 内容过长，仅展示头部和尾部部分文字预览，全文请查看图片预览。 bleItemDTO"> ??

??? ??

??? ??

? ??

??

?? ??

????select?*?from?table(cast?(procpkg.procpostype(#value#)?as?PosTypeTable)) ??

????

Dao的写法跟普通查询一样

Java代码
?

public?List?queryPostype()?{ ??

??return?this.getSqlMapClientTemplate().queryForList("pos_dayprocset.procPostype",?null); ??

}??

有几点需要注意，这里不能使用索引表，而是嵌套表。 另外就是把嵌套表强制转换为普通表。

[文章尾部最后500字内容到此结束,中间部分内容请查看底下的图片预览]请点击下方选择您需要的文档下载。

1. A3报纸模版
2. Oracle存储过程常用技巧
3. JDK13连接SQL Server数据库
4. 社媒推广工具
5. 常考的3类介词固定搭配
6. 实验3 跨交换机的vlan划分
7. oracle第七章教案
8. 投诉教案unit-4 I’ll have to have my watch replaced
9. 大学英语期末考试写作必背范文
10. 03-土地利用规划图-Model
11. XX四中七年级数学寒假作业第四周测试幂的运算易错题汇总
12. Ubuntu16.04设置静态网络
13. 2018年1-4月雅思口语题库
14. Challenge to All演讲稿
15. ASCII码表
16. Unit 4 Space Exploration背诵单

以上为《Oracle存储过程常用技巧》的无排版文字预览，完整格式请下载

下载前请仔细阅读上面文字预览以及下方图片预览。图片预览是什么样的，下载的文档就是什么样的。