当外部过程和内嵌过程定义了相同名字的变量的时候，在内嵌过程中如果直接写这个变量名是没有办法访问外部过程的变量的，可以通过给外部过程定义一个名字<<outername>>,通过outername变量名来访问外部过程的变量(待测试..)。
declare
v_i1binary_integer:=1;
begin
declare
v_i2binary_integer:=2;
begin
dbms_output.put_line(v_i1);
dbms_output.put_line(v_i2);
end;
dbms_output.put_line(v_i1);
--dbms_output.put_line(v_i2); 解开后执行Oracle会提示“必须说明标识符'V_I2'”
end;
六、PLSQL流程控制
if判断
declare
v_b boolean:=true;
begin if v_b then
dbms_output.put_line('ok');
end if;
end;
if else判断
declare
v_b boolean:=true;
begin
if v_b then
dbms_output.put_line('ok');
else
dbms_output.put_line('false');
end if;
end;
if elsif else判断
declare
v_name varchar2(20):='cheng';
begin
if v_name='0701' then
dbms_output.put_line('0701');
elsif v_name='cheng' then
dbms_output.put_line('cheng');
else
dbms_output.put_line('false');
end if;
end;
loop循环
注意推出exit是推出循环，而不是推出整个代码块
declare
v_i binary_integer:=0;
begin
loop
if v_i>10 then
exit;
end if;
v_i:=v_i+1;
dbms_output.put_line('hehe');
end loop;
dbms_output.put_line('over');
end;
loop简化写法
declare
v_i binary_integer :=0;
begin
loop
exit when v_i>10;
v_i :=v_i+1;
dbms_output.put_line('hehe');
end loop;
dbms_output.put_line('over');
end;
while循环
declare
v_i binary_integer:=0;
begin
while v_i<10 loop
dbms_output.put_line('hello'||v_i );
v_i:=v_i+1;
end loop;
dbms_output.put_line('over');
end;
for循环
注意不需要声明变量
begin
for v_i in 0..10 loop
dbms_output.put_line('hello'||v_i);
end loop;
dbms_output.put_line('over');
end;
七、PLSQL异常处理
1、声明异常
异常名 EXCEPTION；
2、抛出异常
RAISE 异常名
3、处理异常
抛出异常后的逻辑代码不会被继续执行
异常的定义使用
begin
dbms_output.put_line(1/0);
exception
when othersthen
dbms_output.put_line('error');
end;
declare
e_myException exception;
begin
dbms_output.put_line('hello');
raisee_myException; --raise抛出异常,用此关键字，抛出后转到自定义的e_myException ，执行其里面的putline函数后，再跳到end处，结束PL/SQL块，raise接下面的2句不会继续执行。
dbms_output.put_line('world');
dbms_output.put_line(1/0);
exception
whene_myException then
dbms_output.put_line(sqlcode); --当前会话执行状态，错误编码
dbms_output.put_line(sqlerrm); --当前错误信息
dbms_output.put_line('my error');
when othersthen
dbms_output.put_line('error');
end;
八、PLSQL游标和goto语句
备注：下面提到的游标为静态cursor，包括显示和隐式。
游标，从declare、open、fetch、close是一个完整的生命旅程。当然了一个这样的游标是可以被多次open进行使用的，显式cursor是静态cursor，她的作用域是全局的，但也必须明白，静态cursor也只有pl/sql代码才可以使用它。静态游标变量是在定义时就必须指定SQL语句。
cursor 游标(结果集)用于提取多行数据，定义后不会有数据，使用后才有。一旦游标被打开，就无法再次打开(可以先关闭，再打开)。
declare
cursor c_studentis select * from book;
begin
open c_student;
close c_student;
end;
第二种游标的定义方式，用变量控制结果集的数量。
declare
v_id binary_integer;
cursor c_student isselect * from book where id>v_id;
begin
v_id:=10;
open c_student;
close c_student;
end;
第三种游标的定义方式，带参数的游标，用的最多。
declare
cursor c_student(v_idbinary_integer) is select * from book where id>v_id;
begin
open c_student(10);
close c_student;
end;
游标的使用，一定别忘了关游标。
declare
v_student book%rowtype;
cursor c_student(v_idbinary_integer) is select * from book where id>v_id;
begin
open c_student(10);
fetch c_student intov_student;
close c_student;
dbms_output.put_line(v_student.name);
end;
如何遍历游标fetch
游标的属性 %found，%notfound,%isopen,%rowcount。
- %found:若前面的fetch语句返回一行数据，则%found返回true，如果对未打开的游标使用则报ORA-1001异常。
- %notfound，与%found行为相反。
- %isopen，判断游标是否打开。
- %rowcount:当前游标的指针位移量，到目前位置游标所检索的数据行的个数，若未打开就引用，返回ORA-1001。
注：
no_data_found和%notfound的用法是有区别的，小结如下
1)SELECT. . . INTO 语句触发 no_data_found；
2)当一个显式光标(静态和动态)的 where 子句未找到时触发 %notfound；
3)当UPDATE或DELETE语句的where 子句未找到时触发 sql%notfound；
4)在光标的提取(Fetch)循环中要用 %notfound 或%found 来确定循环的退出条件，不要用no_data_found。
下面是几个实例：
create table BOOK
(
ID VARCHAR2(10) not null,
BOOKNAME VARCHAR2(10) not null,
PRICE VARCHAR2(10) not null,
CID VARCHAR2(10) not null
);
--insert
create or replace procedure say_hello(
i_name in varchar2,
o_result_msg out varchar2
)
as
v_price varchar2(100);
e_myException exception;
begin
insert into book(id,bookname,price)values (1,2,3);
o_result_msg := 'success';
exception
when others then
rollback;
o_result_msg := substr(sqlerrm, 1, 200);
end;
--update or delete
create or replace procedure say_hello(
i_name in varchar2,
o_result_msg out varchar2
)
as
v_price varchar2(100);
e_myException exception;
begin
update book set price = '55' wherebookname = i_name;
delete from book where bookname =i_name;
if sql%notfound then
raisee_myException;
end if;
/*
if sql%rowcount = 0 then--写法2
raisee_myException;
end if;
*/
o_result_msg := 'success';
exception
when e_myException then
rollback;
o_result_msg := 'update or delete dail';
end;
--select
create or replace procedure say_hello(
i_name in varchar2,
o_result_msg out varchar2
)
as
v_price varchar2(100);
e_myException exception;
begin
select price into v_price from bookwhere bookname = i_name;
o_result_msg := 'success';
exception
when no_data_found then
rollback;
o_result_msg := 'select into dail';
end;
loop方式遍历游标
declare
v_bookname varchar2(100);
cursor c_book(i_idnumber) is select bookname from book where id = i_id;
begin
Open c_book(i_id);
Loop
Fetch c_bookinto v_bookname;
exit whenc_student%notfound;
update book set price = '33' where bookname = v_bookname;
End Loop;
Close c_book;
end;
或
declare
v_bookname varchar2(100);
cursor c_book(i_idnumber) is select bookname from book where id = i_id;
begin
Open c_book(i_id);
Fetch c_book intov_bookname;
While c_book%Found
Loop
update book set price = '33' where bookname = v_bookname;
Fetch c_book intov_bookname;
End Loop;
Close c_book;
end;
while循环遍历游标，注意，第一次游标刚打开就fetch，%found为null，进不去循环
解决方法：while nvl(c_student%found,true) loop
declare
v_bookname varchar2(100);
cursor c_book(i_id number) isselect bookname from book where id = i_id;
begin
Open c_book(i_id);
while nvl(c_book%found,true)--或这种写法：while c_book%found is null or c_book%foundloop
Fetchc_book into v_bookname;
updatebook set price = '33' where bookname = v_bookname;
End Loop;
Close c_book;
end;
for循环遍历，最简单，用的最多，不需要 声明v_student,Open和Close游标和fetch操作(不用打开游标和关闭游标，实现遍历游标最高效方式)
declare
cursor c_book(i_id number) isselect bookname from book where id = i_id;
begin
for cur in c_book(i_id) --直接将入参i_id传入cursor即可
loop
updatebook set price = '53' where bookname = cur.bookname;
end loop;
end;
goto例子，一般不推荐使用goto，会使程序结构变乱
declare
i number:=0;
begin
if i=0 then
gotohello;
end if;
<<hello>>
begin
dbms_output.put_line('hello');
gotoover;
end;
<<world>>
begin
dbms_output.put_line('world');
gotoover;
end;
<<over>>
dbms_output.put_line('over');
end;
九、Oracle存储过程
在谈存储过程书写中的一些规则时，先看一下执行它的规则，在命令窗口执行存储过程sp_get_product_prompt
set serveroutput on
var ret1 varchar2(200);
var ret2 varchar2(200);
exec sp_get_product_prompt(83,:ret1,:ret2); --或execute
print ret1;
print ret2;
或
set serveroutput on
declare
ret1 varchar2(200);
ret2 varchar2(200);
begin
sp_get_product_prompt(83,ret1,ret2);
dbms_output.put_line(ret1);
dbms_output.put_line(ret2);
end;
存储过程入参，不论类型，缺省情况下值都为null,入参和出参不能有长度,其中关键字as可以替换成is,存储过程中变量声明在as和begin之间，同时，存储过程中可以再调用其它的存储过程，如果要保证存储过程之间的事务处理不受影响，可以定义为自治事务。
create or replace procedure say_hello(
v_name in varchar2,
v_flag number,
o_ret out number
)
as
begin
if v_name is null and v_flag is null then --v_name和v_flag都等于null
o_ret := 10;
else
o_ret := 100;
end if;
end;
对于入参为null情况下给予缺省值
create or replace procedure say_hello(
i_name in varchar2,
i_flag number,
o_ret out number
)
as
v_name varchar2(100);
begin
if i_name is null then
v_name := '0';
else
v_name := i_name;
end if;
insert into phone(..,wname..,)values(..,v_name,..);
end;
或直接在insert语句中调用nvl函数赋缺省值
insert intophone(..,wname..,) values(..,nvl(v_name,' '),..); ----如果将' '写成'',则insert进来的v_name值还是为''等价于null值
带一个参数的存储过程
输入参数in，输入参数不能进行:=赋值，但可以将它赋给as后面定义的变量；
输入参数in，可以作为变量进行条件判断；
默认不写就是in；
存储过程没有重载，这个有参的say_hello会替代已经存在的无参say_hello。
create or replace procedure say_hello(v_name in varchar2)
as
begin
--v_name:='a'; --存储过程入参v_name不能做为赋值目标
dbms_output.put_line('hello '||v_name);
end;
存储过程输入参数作为变量进行条件判断
create or replace procedure say_hello(
i_opFlag in number
)
as
v_name varchar2(100);
begin
if i_opFlag = 1 then
v_name :='0';
else
v_name :='haha';
end if;
dbms_output.put_line('hello'||v_name);
end;
利用存储过程中定义的变量对入参的空值处理：
create or replace procedure say_hello(
i_name in varchar2
)
as
v_name varchar2(100);
begin
if i_name is null then
v_name :='0';
else
v_name :=i_name;--将入赋值给定义变量
end if;
dbms_output.put_line('hello'||v_name);
end;
多个参数的存储过程
create or replace procedure say_hello(
v_first_name in varchar2,
v_last_name in varchar2)
as
begin
dbms_output.put_line('hello'||v_first_name||'.'||v_last_name);
end;
out输出参数
用于利用存储过程给一个或多个变量赋值，类似于返回值
create or replaceprocedure say_hello(
v_name in varchar2,
v_content out varchar2
)
begin
v_content:='hello'||v_name;
end;
调用：
declare
v_con varchar2(200);
v_in varchar2(20):='wang';
begin
say_hello(v_in,v_con);
dbms_output.put_line(v_con);
end;
in out参数，既赋值又取值
create or replace procedure say_hello(v_name in outvarchar2)
as
begin
v_name:='hi '||v_name;
end;
调用：
declare
v_inout varchar2(20):='wangsu';
begin
say_hello(v_inout);
dbms_output.put_line(v_inout);
end;
对存储过程入参赋缺省值
create or replace procedure say_hello(
v_name varchar2 default 'susu',
v_content varchar2 default 'hello'
)
as
begin
dbms_output.put_line(v_name||''||v_content);
end;
调用：(用指明形参名的方式调用更好)
begin
say_hello();
end;
或
begin
say_hello('cheng');
end;
或
begin say_hello(v_name=>'cheng'); end;
十、PLSQL中的function
FUNCTION和PROCEDURE的区别
1、函数有返回值，过程没有
2、函数调用在一个表达式中，过程则是作为pl/sql程序的一个语句
过程和函数都以编译后的形式存放在数据库中，函数可以没有参数也可以有多个参数并有一个返回值。
过程有零个或多个参数，没有返回值。
函数和过程都可以通过参数列表接收或返回零个或多个值，函数和过程的主要区别不在于返回值，而在于他们的调用方式，过程是作为一个独立执行语句调用的，函数以合法的表达式的方式调用。
create or replace function func(v_name in varchar2)
return varchar2
is
begin
return(v_name||' hello');
end;
调用：
declare
v_name varchar2(20);
begin
v_name:=func('cheng');
dbms_output.put_line(v_name);
end;
带out参数的函数
create or replace function func(
v_name in varchar2,
v_content out varchar2
)
return varchar2
is
begin
v_content:=v_name||' hello';
return v_content;
end;
调用：
declare
v_name varchar2(20);
v_name1 varchar2(20);
begin
v_name1:=func('susu',v_name);--返回v_name值
dbms_output.put_line(v_name1);--打印func结果
dbms_output.put_line(v_name);--打印v_name结果
end;
带in out 参数的函数
create or replace function func(
v_name in out varchar2)
return varchar2
is
begin
v_name:=v_name||' hello';
return 'cheng';
end;
调用：
declare
v_inout varchar2(20):='world';
v_ret varchar2(20);
begin
v_ret:=func(v_inout);--返回调用v_inout值(作为出参)
dbms_output.put_line(v_ret);--打印func结果
dbms_output.put_line(v_inout);--返回v_name结果
end;
到此这篇关于Oracle中PL/SQL用法的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持。
标签：Oracle
相关阅读 >>
oracle生成不重复票号与lpad,rpad与nextval函数解析
更多相关阅读请进入《oracle》频道 >>
数据库系统概念 第6版
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。
感谢您的支持，我会继续努力的!
扫码打赏，您说多少就多少
打开支付宝扫一扫，即可进行扫码打赏哦
分享从这里开始，精彩与您同在
相关推荐
评论
管理员已关闭评论功能...