plsql 函数与过程的定义及应用

plsql 函数与过程的定义及应用

存储过程与函数1、存储过程存储过程的参数形式参数和实际参数，例如，有如下一个存储过程，该过程接收一个作家代码和一个工资值，将该作家的工资改为接收到的工资值。代码create or replace procedure

updateauths( p_authscode auths.author_code%type,

p_authssalary auths.salary%type) as begin update auths set salary=p_authssalary where

author_code=p_authscode; commit; end updateauths; create or replace procedure updateauths(p_authscode auths.author_code%type,p_authssalary

auths.salary%type)asbeginupdate auths set

salary=p_authssalary where

author_code=p_authscode;commit;end updateauths;下面的PL/SQl块调用updateauths存储过程，将代码为A00011的作家的工资改为350元。代码declare v_authorcode auths.author_code%type:='A00011'; v_salary

auths.salary%type:=350; begin

updateauths(v_authorcode,v_salary); end;

declarev_authorcode

auths.author_code%type:='A00011';v_salary

auths.salary%type:=350;beginupdateauths(v_authorcode, v_salary);end;

v_authorcode、v_salary作为参数传递到存储过程updateauths中，这些参数是实际参数，简称实参。

p_authscode、p_authssalary就是形式参数，简称形参。参数定义中，IN、OUT和IN OUT代表参数的三种不同模式：IN：当调用存储过程时，该模式的形参接收对应实参的值，并且该是只读的，即不能被修改。默认为IN。

OUT：该形参被认为只能写，既只能为其赋值。在存储过程中不能读它的值。返回时，将该形参值传给相应的实参。IN OUT：都允许。代码create or replace procedure updateauthssalary( p_author_code in out

auths.author_code%type, p_salary in number, p_name out https://www.360docs.net/doc/8417246799.html,%type) is v_salary_temp number; --定义存储过程中的局部变量begin select salary into v_salary_temp from auths where author_code=p_author_code; if

v_salary_temp300 then update auths set salary=p_salary where author_code=p_author_code; end if; select name into p_name from auths where author

code=p_author_code; end updateauthssalary; create or replace procedure updateauthssalary(p_author_code in out auths.author_code%type,p_salary in number,p_name

out https://www.360docs.net/doc/8417246799.html,%type) isv_salary_temp number; --定义存储过程中的局部变量beginselect salary into v_salary_temp from auths where author_code=p_author_code;if

v_salary_tempupdate auths set salary=p_salary where author_code=p_author_code;end if;select name into

p_name from auths where author

code=p_author_code;end updateauthssalary;(1)参数的数据类型在定义一个存储过程参数时，不能指定CHAR类型和VARCHAR2类型形参的长度，也不能指定NUMBER形参的精度和标度。这些约束由实参来传递。例如，下面的存储过程定义不合法，将产生一个编译错误：代码create or replace procedure proc_auths( --参数定义了类型长度，将产生编译错误。p_code in out varchar2(6), p_salary out number(8,2)) as begin select salary into p_salary from auths where author_code=p_code; end proc_auths; create or replace procedure proc_auths(--参数定义了类型长度，将产生编译错误。p_code in out varchar2(6),p_salary out number(8,2)) asbeginselect salary into p_salary from auths where author_code=p_code;end proc_auths;修改上面存储过程的定义为：代码create or replace procedure

proc_auths( --参数定义了类型长度，将产生编译错误。

p_code in out varchar2, p_salary out number) as begin

select salary into p_salary from auths where

author_code=p_code; end proc_auths; create or replace procedure proc_auths(--参数定义了类型长度，将产生编译错误。p_code in out varchar2,p_salary out number) asbeginselect salary into p_salary from auths where author_code=p_code;end proc_auths;

p_code的长度约束和p_salary的精度，标度约束由实参传递。代码delcare v_code varchar2(6); v_salary

number(8,2); begin v_code:='A00001';

proc_auths(v_code,v_salary); end; delcarev_code

varchar2(6);v_salary

number(8,2);beginv_code:='A00001';proc_auths(v_code,v _salary);end;注意，如果使用%type为参数定义类型，那么该参数具有定义在形参上而不是通过实参传递的数据长度。代码create or replace procedure query_salary( p_code in out auths.author_code%type, p_salary out

auths.salary%type) as create or replace procedure

query_salary(p_code in out

auths.author_code%type,p_salary out auths.salary%type) as--那么由于author_code的长度为6，因此p_code的长度也为6。(2)参数的传值方式位置表示法、名称表示法如有这样的存储过程代码create or replace procedure

insert_auths( p_code auths.author_code%type, p_name https://www.360docs.net/doc/8417246799.html,%type, p_sex auths.sex%type, p_birthdate auths.birthdate%type) as create or replace procedure insert_auths(p_code auths.author_code%type,p_name https://www.360docs.net/doc/8417246799.html,%type,p_sex auths.sex%type,p_birthdate auths.birthdate%type) as下面进行两种方法的调用：代码declare v_code varchar2(6); v_name varchar2(12); v_sex number(1); v_birthdate date; begin v_code:='A00021';

v_name:='张'; v_sex:=1; v_birthdate:='5-seq-70'; --实参的位置顺序与形参的位置顺序相对应。---位置表示法

insert_auths(v_code,v_name,v_sex,v_birthdate); --实参名与形参名对应，这样就可以重新排列参数的先后顺序。---命名表示法end; declarev_code varchar2(6);v_name varchar2(12);v_sex number(1);v_birthdate

date;beginv_code:='A00021';v_name:='张

';v_sex:=1;v_birthdate:='5-seq-70';--实参的位置顺序与形参的位置顺序相对应。---位置表示法

insert_auths(v_code,v_name,v_sex,v_birthdate);--实参名与形参名对应，这样就可以重新排列参数的先后顺序。---命名表示法end;注意，位置表示法和命名表示法在一些调用中也可以混合使用。但是，如果出现第一个用命名表示法的参数时，后面的参数也必须使用命名表示法传值。(3)参数的缺

省值如可以这样：

p_entry_date_time auths.entry_date_time%type:sysdate,

p_sex auths.sex%type default 1 2、创建函数函数与存储过程非常类似，都有三种模式的参数。它们都可以存储在数据库中(当然过程与函数也可以不在于数据库中)，并且在块中

调用。与存储过程不同，存储过程只能作为一个PL/SQL

语句调用，而函数作为表达式的一部分调用。并且它们的定义、可执行、异常处理部分是不同的。例如，如作家表中

男作家或女作家的工资在200元以上的人数大于百分之七十，则下面的函数返回TRUE，否则返回FALSE：代码create or replace function salarystat( p_sex auths.sex%type) return boolean is v_currentsexauthors number;

v_maxauthors number; v_returnvalue boolean; v_percent constant number:=70; begin --获得满足条件的作家的最大数。select count(author_code) into v_maxauthors from auths where sex=p_sex and salary>=200; select

count(author_code) into v_currentsexauthors from auths where sex=p_sex;

if(v_maxauthors/v_currentsexauthors*100)>v_percent then v_returnvalue:=true; else v_returnvalue:=false; end if; return v_returnvalue; end salarystat; create or replace function salarystat(p_sex auths.sex%type)return boolean

isv_currentsexauthors number;v_maxauthors

number;v_returnvalue boolean;v_percent constant number:=70;begin--获得满足条件的作家的最大数。select count(author_code) into v_maxauthors from auths where sex=p_sex and salary>=200;select count(author_code) into v_currentsexauthors from auths where

sex=p_sex;if(v_maxauthors/v_currentsexauthors*100)>v_p ercent

thenv_returnvalue:=true;elsev_returnvalue:=false;end

if;return v_returnvalue;end salarystat;下面进行调用：代码declare cursor c_auths is select distinct sex from auths; begin for v_authsrecord in c_auths loop if

salarystat(v_authsrecord.sex) then update auths set salary=salary-50 where sex=v_authsrecord.sex; end if; end loop; end; declarecursor c_auths isselect distinct sex from auths;beginfor v_authsrecord in c_auths loopif salarystat(v_authsrecord.sex) thenupdate auths set salary=salary-50 where sex=v_authsrecord.sex;end if;end loop;end;

return也可以用在存储过程中。在这种情况下，它没有参数。当执行了不带参数的return语句后，立刻将控制返回到调用环境，并将OUT和IN OUT模式的形参的当前值传给实参，

然后继续执行调用存储过程后的语句。在使用函数与存储

过程时，一般情况下，如果只有一个返回值，则使用函数；如果有多个返回值则使用存储过程。尽管函数的参数可以是OUT模式，但是一般不这样使用。3、删除过程与函数

drop procedure procedurename;

drop function functionname; 4、库存子程序和局部子程序

前面的子程序都是存储在数据库中的子程序，即库存子程序。这些子程序是由ORACLE命令创建的，并可在其它的

PL/SQL块中调用。它们在创建时要进行编译，并将编译后

的代码存储在数据库中。当子程序被调用时，编译后的代码从数据库中读出并执行。一个子程序也可以在块的定义部

分创建，这样的子程序被叫作局部子程序。下面定义了一

个局部函数formatname：代码declare cursor c_allauthors is select name,sex from auths; v_formattedname

varchar2(60); function formatname(p_name in

varchar2,p_sex in number) return varchar2 is v_sex varchar2(16); begin if p_sex=1 then v_sex:='男'; else

v_sex:='女'; end if; return p_name||'('||v_sex||')'; end formatname; begin for v_authsrecord in c_allauthors loop

v_formattedname:=

formatname(v_https://www.360docs.net/doc/8417246799.html,,v_authsrecord.sex); dbms_output.put_line(v_formattedname); end loop; end;

declarecursor c_allauthors isselect name,sex from auths;v_formattedname varchar2(60);function formatname(p_name in varchar2,p_sex in number)return varchar2 isv_sex varchar2(16);beginif p_sex=1

thenv_sex:='男';elsev_sex:='女';end if;return

p_name||'('||v_sex||')';end formatname;beginfor

v_authsrecord in c_allauthors

loopv_formattedname:=formatname(v_https://www.360docs.net/doc/8417246799.html,, v_authsrecord.sex);dbms_output.put_line(v_formattednam e);end loop;end;如上例，在无名块的定义部分创建了formatname函数。这个函数只在创建它的块中可用，它的作用域从创建它开始到结束。局部子程序只能在定义部分的最后被创建，如果将formatname函数移到上面，将会出现编译错误。子程序必须先定义再引用。[@more@]