oracle package cursor,ORACLE Procedure function package cursor 简介

1、PL/SQL语句块

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

基本语法:

declare

变量声明、初始化

begin

业务处理、逻辑代码

exception

异常捕获

end;

变量声明：[:=]

例：v_name varchar2(20):=’张三’;

例：见第3节

2、循环语句

loop循环语法：

loop

exitwhen表达式

end loop;

while循环语法：

while表达式loop

end loop;

for循环语法:

forin loop

end loop;

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

例：见第3节

3、if判断语句

基本语法：

ifthen

…

elseifthen

…

else

…

endif;

endif;

例：

declare

v_identitynumber(4):=0;

begin

loop

ifv_identity=1then

dbms_output.put_line('v_identity=1');

elseifv_identity=3then

dbms_output.put_line('v_identity=3');

elseifv_identity=6then

exit;

else

dbms_output.put_line('v_identity is not 1 or 3');

endif;

endif;

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

v_identity:=v_identity+1;

endloop;

exception

whenothersthendbms_output.put_line('error!');

end;

/

4、分支case

基本语法：

case

when常量then

…

when常量then

…

else

…

end case;

例：

declare

v_numbernumber(4):=3;

v_stringvarchar(20):='abc';

begin

casev_number

when1then

dbms_output.put_line('v_number is '||1);

when2then

dbms_output.put_line('v_number is '||2);

when3then

dbms_output.put_line('v_number is '||3);

endcase;

casev_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');

endcase;

exception

whenothersthendbms_output.put_line('error!');

end;

/

5、异常(exception)

声明异常语法：exception;

抛出异常语法：raise;

捕获异常语法：whenthen异常处理语句;

例：

declare

v_inputvarchar2(1):='&throw';--动态输入

v_exception_1exception;--自定义异常

v_exception_2exception;

othersexception;--系统异常

begin

ifv_input='1'then

raisev_exception_1;--抛出异常

elseifv_input='2'then

raisev_exception_2;

else

raiseothers;

endif;

endif;

exception

--捕获异常

whenv_exception_1thendbms_output.put_line('throw exception: v_exception_1');

whenv_exception_2thendbms_output.put_line('throw exception: v_exception_2');

whenothersthendbms_output.put_line('throw exception: others');

end;

/

6、游标(cursor)

声明游标语法：cursorisselect语句;

声明ref游标语法:isrefcursor;

打开游标语法：open;

移动游标并获取数据语法：fetchinto;

关闭游标语法:close;

游标属性(游标的属性必须在关闭游标之前)：

%isopen:判断游标是否打开

%notfound:找不到数据时

%found:

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

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

例：

declare

v_row t_test%rowtype;--匹配t_test表中一行所有的数据类型

cursorv_curisselect*fromt_test;--声明游标

begin

openv_cur;--打开游标

loop

fetchv_curintov_row;--将游标所在行的数据转存到v_row中

exitwhenv_cur%notfound;--当游标到最后一行时跳出

dbms_output.put_line('id = '||v_row.t_id||'name = '||v_row.t_name||'msg = '||v_row.t_msg);

endloop;

closev_cur;--关闭游标

exception

whenothersthendbms_output.put_line('throw exception: others');

end;

/

-- REF游标--

createorreplacepackageupk_select_test

astypeuc_testisrefcursor;--声明ref游标

endupk_select_test;

/

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

createorreplaceprocedureup_select_test_2

(uc_resultoutupk_select_test.uc_test)

is

begin

openuc_resultforselect*fromt_test;

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

基本语法：

createprocedure()

as|is

变量声明、初始化

begin

业务处理、逻辑代码

exception

异常捕获、容错处理

end;

参数: in|out|in out，如：v_namevarchar2

in：入参

out：出参

in out：出入参

注：as|is表示as或is

调用语法：

1)、exec;

2)、execute;

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

例：

createorreplaceprocedureup_wap(v_param1inoutvarchar2,v_param2inoutvarchar2)

is

v_tempvarchar2(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

whenothersthendbms_output.put_line('There is a error when the procedure up_wap executing!');

endup_wap;

/

--调用存储过程

declare

v_param1varchar2(20):='param1';

v_param2varchar2(20):='param2';

begin

up_wap(v_param1 => v_param1,v_param2 => v_param2);

end;

/

9、自定义函数(function)

基本语法：

createfunction()

return

as|is

变量声明、初始化

begin

业务处理、逻辑代码

return;

exception

异常捕获、容错处理

end;

参数：in入参

注：只有入参的类型。

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

例：

createfunctionuf_select_name_by_id_test(v_idinnumber)

returnvarchar2

is

v_name t_test.t_name%type;

begin

selectt_nameintov_namefromt_testwheret_id=v_id;

returnv_name;

exception

whenothersthendbms_output.put_line('error');

enduf_select_name_by_id_test;

/

selectuf_select_name_by_id_test(1)姓名fromdual;-- select调用

declare--pl/sql语句块调用

v_namevarchar2(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)中定义实现。

基本语法：

createpackage

as|is

变量声明

存储过程声明

自定义函数声明

end;

/

createpackage

as|is

存储过程的代码实现

自定义函数的代码实现

end;

/

例：

--创建包upk_hello

createorreplacepackageupk_hello

is

v_hello_worldvarchar2(20):='hello world';--声明变量

procedureup_hello_world(v_nameinvarchar2);--声明过程

functionuf_hello_world(v_nameinvarchar2)returnvarchar2;--声明函数

endupk_hello;

/

--实现包(upk_hello)里声明的方法

createorreplacepackagebodyupk_hello

is

procedureup_hello_world(v_nameinvarchar2)

is

v_stringvarchar2(100);

begin

v_string:=v_name||' say hello world!';

dbms_output.put_line(v_string);

exception

whenothersthendbms_output.put_line('error');

endup_hello_world;

functionuf_hello_world(v_nameinvarchar2)returnvarchar2

is

v_stringvarchar2(100);

begin

v_string:=v_name||' say hello world!';

returnv_string;

exception

whenothersthendbms_output.put_line('error');

enduf_hello_world;

endupk_hello;

/

--包的调用

declare

v_msgvarchar2(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;

/