Oracle Procedure
一、块
块是pl/sql组成的基本单元,本篇讲述存储过程,鉴于2者有很高的相似性,且是其中一种块,所以在此做一个简单的块讲述
1、块的结构
declare
定义部分(可选)
begin
可执行部分(必选)
exception
异常处理部分(可选)
end;
2、块的分类
(1)、无名块(也叫匿名块):动态构造并只能执行一次,常用来测试或执行存储过程。
(2)、命名块:加了用<<>>括起了带标号的无名块。
(3)、子程序:包括存储过程【本篇要讲述的内容】、函数和包等。这些块一旦被定义便会存储在数据库中,可随时调用。
(4)、触发器:根据触发的事件调用。
3、举例
(1)、无名块
declare
v_name varchar2(8):='CC';
v_name2 constant varchar2(8):='CC'; --常量,constant关键字用于指定常量
v_name3 varchar2(8) default 'CC'; --和:=等价
v_name4 varchar2(8) not null :='CC'; --为not null的必须被赋值
v_name4 varchar2(8); --缺省情况下,变量被初始化为null
v_name5 boolean not null default false;
begin
sp_flux_user_list();
end;
(2)、命名块
说明:主要用于不同作用域同名变量间的引用
<<insertype1>>
declare
v_name number:=1;
begin
insert into t_type values(v_name);
<<insertype2>>
declare
v_name number:=2;
begin
dbms_output.put_line(v_name);
end;
<<insertype3>>
declare
v_name number:=2;
begin
dbms_output.put_line(v_name);
end;
<<insertype4>>
declare
v_salary number:=2;
begin
dbms_output.put_line(v_salary);
end;
end;
如上面的例子,现在引入2个概念
变量的作用域:指变量在程序中的有效范围。对于一个plsql变量,它的作用域是从该变量被声明开始到变量所在块结束。
变量的可视域:指变量前不加以限定就能直接访该变量的那一段程序。
上面例子中,insertype1块的变量v_name在到所在块结束前都是其作用域,并且在insertype4块内可视,但在insertype2和insertype3内不可视,因为insertype2和insertype3内有同名变量。
当在块中声明一个变量时,如果在子块中也声明了同名的变量,则它们的作用域没变。但在子块中,只有子块中声明的变量时可视的,这时要引用父块中的变量名时,必须加以限定。
这时要在insertype2内引用insertype1的变量就需要这样写“insertype1.v_name:=v_name”,但insertype2与insertype3的变量无法互相引用,因为作用域互不相同。
(3)、存储过程
create or replace procedure sp_flux_user_list
is
v_em emp.empno%TYPE; --表示以emp表中的empno字段的类型定义变量
v_de dept%rowtype; --表示v_de是一行数据
begin
dbms_session.set_nls('nls_date_format','"yyyy-mm-dd hh24:mi:ss"');
dbms_output.put_line(sysdate);
end sp_flux_user_list;
(4)、触发器
create or replace trigger tri_tse
before insert or update or delete on emp
begin
if to_char(sysdate,'DY','nls_date_language=american') in ('SAT','SUN') then
raise_application_error(-20001,'不能在休息日改变雇员信息');
end if;
end;
4、注意
(1)、定义部分的标识符必须以字母开头;一个或多个字母、数字、或特殊符号组成;长度不超过30;不能有空格;非保留字
plsql不区分大小写,如要区分大小写或空格或保留字等,则使用"",例如"aAt code" number;
(2)、plsql将长度为0的字符串当做null来处理。
(3)、在PLSQL里,select、DML语句可以直接写,而DDL、DCL语句不能直接写。
二、存储过程概述,存储过程大体分为这么几个部分:
1.创建语句:create or replace procedure 存储过程名
如果没有or replace语句,则仅仅是新建一个存储过程。如果系统存在该存储过程,则会报错。Create or replace procedure 如果系统中没有此存储过程就新建一个,如果系统中有此存储过程则把原来删除掉,重新创建一个存储过程。
2.存储过程名定义:包括存储过程名和参数列表。参数名和参数类型。参数名不能重复, 参数传递方式:IN, OUT, IN OUT
IN 表示输入参数,按值传递方式,并且它不允许在存储过程中被重新赋值(in相当于JAVA的final)。如果存储过程的参数没有指定存参数传递类型,默认为IN。
OUT 表示输出参数,可以理解为按引用传递方式,可以作为存储过程的输出结果,供外部调用者使用。需要注意,当一个参数被指定为OUT类型时,就算在调用存储过程之前对该参数进行了赋值,在存储过程中该参数的值仍然是null。
IN OUT 即可作输入参数,也可作输出参数,属于真正的按引用传递参数。
参数的数据类型只需要指明类型名即可,不能指定宽度,参数的宽度由外部调用者决定。过程可以有参数,也可以没有参数。
3.变量声明块:紧跟着的as (is)关键字,可以理解为pl/sql的declare关键字,用于声明变量。
变量声明块用于声明该存储过程需要用到的变量,它的作用域为该存储过程。另外这里声明的变量必须指定宽度。遵循PL/SQL的变量声明规范。且最大长度为30.
变量名[CONSTANT] 类型标识符[NOT NULL][:=值|DEFAULT 值];
4.过程语句块:从begin 关键字开始为过程的语句块。存储过程的具体逻辑在这里来实现。
5.异常处理块:关键字为exception ,为处理语句产生的异常。该部分为可选
6.结束块:由end关键字结果。
三、存储过程参数的传递方式
1.无参过程
create or replace procedure pro_time
is
v_em emp.empno%TYPE; --字段变量,表示以emp表中的empno字段的类型定义变量
v_de dept%rowtype; --记录变量,表示v_de是一行数据
begin
dbms_session.set_nls('nls_date_format','"yyyy-mm-dd hh24:mi:ss"');
dbms_output.put_line(sysdate);
dbms_output.put_line(v_de.id);--输出记录变量的某个字段
end pro_time;
2.带输入输出参数的过程
create or replace procedure pro_emp(
i_eno emp.empno%type, --默认in
i_sal in emp.sal%type,
o_name out varchar2,
num1 in out number) is
begin
update emp set sal=i_sal where empno=i_eno return ename into o_name;
num1:=i_sal*10;
commit;
end pro_emp;
调用
declare
v_eno number:=1111;
v_salary:=2500;
vn emp.ename%type;
n1 number:=30;
begin
pro_sal(v_eno,v_salary,vn,n1);
dbms_output.put_line('姓名:' || vn || '升至10倍后的工资:' || n1);
end;
说明:
我们无法在存储过程的定义中指定存储参数的宽度,也就导致了我们无法在存储过程中控制传入变量的宽度。这个宽度是完全由外部传入时决定的。
对于IN参数,其宽度是由存储过程外部决定。
对于OUT 和IN OUT 参数,其宽度是由存储过程内部决定。
比较明智的方法就是参数的数据类型使用%type,这样双方就达成了一致。但注意,使用%type为参数定义类型,该参数具有定义在形参上而不是通过实参传递的数据长度。
四、存储过程参数的默认值
说明:
可以通过default 关键字为存储过程的参数指定默认值。在对存储过程调用时,就可以省略默认值。
需要注意的是,默认值仅仅支持IN传输类型的参数,OUT 和 IN OUT不能指定默认值。。
例一:
create or replace procedure pro_test(i1 in number,
i2 in date default sysdate) is
begin
dbms_output.put_line(to_char(add_months(i2, i1),'yyyy-mm-dd'));
end pro_test;
调用
exec pro_test(1) --输出 2012-09-17(今天是2012-08-17)
exec pro_test(1,to_date('2012-01-01','yyyy-mm-dd')) --输出 2012-02-01
例二:当默认值不在最后一位怎么调用
create or replace procedure pro_test(i2 in date default sysdate,
i1 in number) is
begin
dbms_output.put_line(to_char(add_months(i2, i1),'yyyy-mm-dd'));
end pro_test;
调用
exec pro_test(i1=>1) --输出 2012-09-17(今天是2012-08-17)
五、存储过程参数的传参形式
由上面默认值不在第一位的情况可见,传参的形式有2种
第一种是位置表示法,按照参数顺序传参,例如exec pro_test(1,to_date('2012-01-01','yyyy-mm-dd')),这种形式不需要知道过程的形参名称,但必须按照参数顺序传参。
第二种是名称表示法,指定参数名称传参,例如exec pro_test(i1=>1),这种形式不需要按照参数的顺序传参,但必须知道过程形参的名称,且如果使用缺省值的参数不是存储过程最后一个参数,则只能用名称表示法。
另外,位置表示法和名称表示法在一些调用中也可以混合使用。但是,当在调用存储过程中出现了第一个名称表示法的参数时,后面的参数也必须使用名称表示法传值。
例一:
declare
v_bir varchar2(10):='1999';
v_sex varchar2(10):='1';
begin
pro_tb_flux_user_list(v_code,v_name,p_bir=>v_bir,p_sex =>v_sex);
end;
六、存储过程的结构
1.存储过程的结构:
CREATE OR REPLACE PROCEDURE myproc AS --这里写as和is是等价
/*声明变量部分*/
v_name varchar2(10); --变量
c_male constant int :=1; --常量
type au_record is record(name varchar2(10),sex number);
v_Author au_record; --记录类型
Begin
/*执行部分,必须且最重要的部分,至少包含一条可执行语句*/
Exception
/*异常处理*/
End myproc;
七、存储过程的选择语句
1、IF语句的三种形式:
IF-THEN-END IF
IF-THEN-ELSE-END IF
IF-THEN-ELSIF-ELSE-END IF
IF…ELSIF…ELSE语句
例子:
CREATE OR REPLACE PROCEDURE myproc AS
cou NUMBER;
BEGIN
cou:= 1;
IF cou>10 THEN DBMS_OUTPUT.put_line('cou = '||cou);
ELSIF cou<5 THEN DBMS_OUTPUT.put_line('值小于5');
ELSE DBMS_OUTPUT.put_line('条件不满足');
END IF;
END myproc;
2、case语句的三种形式
基本CASE 结构
CASE 选择变量名
WHEN 表达式1 THEN 语句序列1
WHEN 表达式2 THEN 语句序列2
WHEN 表达式n THEN 语句序列n
ELSE 语句序列n+l
END CASE;
表达式CASE 结构
变量:=CASE 选择变量名
WHEN 表达式1 THEN 值1
WHEN 表达式2 THEN 值2
WHEN 表达式n THEN 值n
ELSE 值n+l
END CASE;
搜索CASE 结构
CASE
WHEN 条件表达式1 THEN 语句序列1
WHEN 条件表达式2 THEN 语句序列2
WHEN 条件表达式n THEN 语句序列n
ELSE 语句序列n+1
END CASE;
八、存储过程的循环语句
1、Loop循环(do...while)
说明:此循环是先执行一次之后再进行判断
格式:
LOOP
循环的语句;
EXIT WHEN 终止条件,true则终止;
循环条件必须更改;
END LOOP;
--如果没有WHEN 条件,遇到EXIT 语句则无条件退出循环。
例子(循环输出1~10):
DECLARE
cou NUMBER;
BEGIN -- 必须给一个初始值
cou := 1;
LOOP
DBMS_OUTPUT.put_line('cou = '||cou);
cou := cou + 1;
EXIT WHEN cou>10;
END LOOP;
END;
2、While循环
说明:此循环是先判断,之后如果条件满足则执行
格式:
while(判断循环的条件,true则执行) loop
循环的语句;
循环条件的改变;
End loop;
例子:
DECLARE cou NUMBER;
BEGIN -- 必须给一个初始值
cou := 1;
WHILE(cou<10) LOOP
DBMS_OUTPUT.put_line('cou = '||cou);
cou := cou + 1;
END LOOP;
END;
3、For循环
说明:循环变量被隐式定义为integer类型的局部变量,缺省情况下,循环变量从初始值以1的增量递增到结束值,如果使用关键字reverse,则循环从结束值以1的减量递减到初始值。
格式:
FOR 控制变量 in [REVERSE] 下限..上限LOOP
语句1;
语句2;
……
END LOOP;
循环控制变量是隐含定义的,不需要要声明。
下限和上限用于指明循环次数。正常情况下循环控制变量的取值由下限到上限递增,
REVERSE 关键字表示循环控制变量的取值由上限到下限递减。
例子:
declare
v_l binary_integer:=1;
begin
for v_loop in 1..10 loop
dbms_output.put_line(v_loop);
--exit when (v_loop=8),加上这句则当v_loop=8时退出;exit则直接退出。
end loop;
end;
declare
v_l binary_integer:=1;
begin
for v_loop in reverse 1..10 loop
dbms_output.put_line(v_loop);
end loop;
end;
九、存储过程的终止循环跳转语句(EXIT和EXIT WHEN)
1、关于EXIT和EXIT WHEN的区别:
EXIT:无条件终止语句,当遇到一个exit语句,则立即终止当前的循环。
EXIT WHEN:有条件终止语句,当遇到exit when语句时,首先检测when子句中的条件,为true则终止。
2、主要作用
提前退出当前循环
提前退出嵌套循环
带标号的loop循环语句,主要用于供内层的exit语句终止外层的循环
例如:
declare
v_l binary_integer:=1;
begin
<<outer>>
for i in 1..100 loop
<<inter>>
for v_loop in 1..10 loop
dbms_output.put_line(v_loop);
exit outer when outer.i*v_loop>=20; --终止外层循环
exit when v_loop=5; --终止本层(内层)循环,等同于exit inter when v_loop=5
end loop inter;
end loop outer;
end;
十、存储过程的无条件跳转语句(GOTO语句)与NULL语句
1.GOTO语句概述
说明:GOTO为非结构化语句,跳转到某个标号,标号必须唯一,且标号后必须是一个可执行的语句或PLSQL块
限制:不能跳转到IF语句、loop语句、或子块中;不能从子程序中挑出;不能从异常处理跳转到可执行部分。
2.NULL语句概述
NULL语句不会执行任何操作,相当于一个占位符,并且会直接连接控制传递到下一条语句,使用NULL语句的好处是可以提高PL/SQL程序的可读性,使语句变得有意思合法。
例一:
DECLARE
eno emp.empno%TYPE;
sal emp.sal%TYPE;
BEGIN
eno := &en;
SELECT sal INTO sal FROM emp WHERE empno=eno;
IF sal>3500 THEN goto po1;
ELSIF sal>2000 THEN goto po2;
ELSE goto po3;END IF;
<<po1>> DBMS_OUTPUT.put_line('高工资。。。');
<<po2>> DBMS_OUTPUT.put_line('中等工资。。');
<<po3>> DBMS_OUTPUT.put_line('底工资。。。'); --标号<<po3>>后面必须要跟着可执行的语句或PLSQL块
END ;
例二:如果跳转的后面没有什么需要执行的语句了怎么办?用null语句。
DECLARE
eno emp.empno%TYPE;
sal emp.sal%TYPE;
BEGIN
eno := &en;
SELECT sal INTO sal FROM emp WHERE empno=eno ;
IF sal>3500 THEN goto po1 ;
ELSIF sal>2000 THEN goto po2 ;
ELSE goto po3 ; END IF ;
<<po1>> DBMS_OUTPUT.put_line('高工资。。。') ;
<<po2>> DBMS_OUTPUT.put_line('中等工资。。') ;
<<po3>> null; --没有null则语法错误,null显式指定一个什么也不做的操作,相当于一个占位符,使语句变得有意义合法。
END ;
十一、存储过程细节
1、删除存储过程
drop procedure procedure_name;
2、有关存错过程中使用到的集合数据类型(PL/SQL表、索引表、嵌套表、可变数组)、游标(普通游标、参数游标、游标变量、循环选择)、异常处理等在本博客的其他文章做专题详述。
Oracle Procedure 存储过程
最新推荐文章于 2024-04-02 12:41:14 发布