存储过程和函数

最新推荐文章于 2023-05-11 14:05:36 发布

coolbaby1984

最新推荐文章于 2023-05-11 14:05:36 发布

阅读量437

点赞数

分类专栏： oracle 文章标签：存储 java 数据库 insert function date

本文链接：https://blog.csdn.net/coolbaby1984/article/details/4708961

版权

oracle 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

存储过程与函数

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

Java代码

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)
as
begin
  update auths set salary=p_authssalary where author_code=p_authscode;
  commit;
end updateauths;

下面的PL/SQl块调用updateauths存储过程，将代码为A00011的作家的工资改为350元。

Java代码

declare
v_authorcode auths.author_code%type:='A00011';
v_salary auths.salary%type:=350;
begin
updateauths(v_authorcode,v_salary);
end;

declare
  v_authorcode auths.author_code%type:='A00011';
  v_salary auths.salary%type:=350;
begin
  updateauths(v_authorcode,v_salary);
end;

v_authorcode、v_salary作为参数传递到存储过程updateauths中，这些参数是实际参数，简称实参。
p_authscode、p_authssalary就是形式参数，简称形参。
参数定义中，IN、OUT和IN OUT代表参数的三种不同模式：
IN：当调用存储过程时，该模式的形参接收对应实参的值，并且该是只读的，即不能被修改。默认为IN。
OUT：该形参被认为只能写，既只能为其赋值。在存储过程中不能读它的值。返回时，将该形参值传给相应的实参。
IN OUT：都允许。

Java代码

create or replace procedure updateauthssalary(
p_author_code in out auths.author_code%type,
p_salary in number,
p_name out auths.name%type) is
v_salary_temp number; --定义存储过程中的局部变量
begin
select salary into v_salary_temp from auths where author_code=p_author_code;
if v_salary_temp<300 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 auths.name%type) is
  v_salary_temp number; --定义存储过程中的局部变量
begin
  select salary into v_salary_temp from auths where author_code=p_author_code;
  if v_salary_temp<300 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;

(1)参数的数据类型
在定义一个存储过程参数时，不能指定CHAR类型和VARCHAR2类型形参的长度，也不能指定NUMBER形参的精度和标度。这些约束由实参来传递。
例如，下面的存储过程定义不合法，将产生一个编译错误：

Java代码

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)) as
begin
  select salary into p_salary from auths where author_code=p_code;
end proc_auths;

修改上面存储过程的定义为：

Java代码

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) as
begin
  select salary into p_salary from auths where author_code=p_code;
end proc_auths;

p_code的长度约束和p_salary的精度，标度约束由实参传递。

Java代码

delcare
v_code varchar2(6);
v_salary number(8,2);
begin
v_code:='A00001';
proc_auths(v_code,v_salary);
end;

delcare
  v_code varchar2(6);
  v_salary number(8,2);
begin
  v_code:='A00001';
  proc_auths(v_code,v_salary);
end;

注意，如果使用%type为参数定义类型，那么该参数具有定义在形参上而不是通过实参传递的数据长度。

Java代码

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)参数的传值方式
位置表示法、名称表示法
如有这样的存储过程

Java代码

create or replace procedure insert_auths(
p_code auths.author_code%type,
p_name auths.name%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 auths.name%type,
  p_sex auths.sex%type,
  p_birthdate auths.birthdate%type) as

下面进行两种方法的调用：

Java代码

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;

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;

注意，位置表示法和命名表示法在一些调用中也可以混合使用。但是，如果出现第一个用命名表示法的参数时，后面的参数也必须使用命名表示法传值。
(3)参数的缺省值
如可以这样：
p_entry_date_time auths.entry_date_time%type:sysdate,
p_sex auths.sex%type default 1

2、创建函数
函数与存储过程非常类似，都有三种模式的参数。它们都可以存储在数据库中(当然过程与函数也可以不在于数据库中)，并且在块中调用。
与存储过程不同，存储过程只能作为一个PL/SQL语句调用，而函数作为表达式的一部分调用。并且它们的定义、可执行、异常处理部分是不同的。
例如，如作家表中男作家或女作家的工资在200元以上的人数大于百分之七十，则下面的函数返回TRUE，否则返回FALSE：

Java代码

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

下面进行调用：

Java代码

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;

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;

return也可以用在存储过程中。在这种情况下，它没有参数。当执行了不带参数的return语句后，立刻将控制返回到调用环境，并将OUT和IN OUT模式的形参的当前值传给实参，然后继续执行调用存储过程后的语句。
在使用函数与存储过程时，一般情况下，如果只有一个返回值，则使用函数；如果有多个返回值则使用存储过程。尽管函数的参数可以是OUT模式，但是一般不这样使用。

3、删除过程与函数
drop procedure procedurename;
drop function functionname;

4、库存子程序和局部子程序
前面的子程序都是存储在数据库中的子程序，即库存子程序。这些子程序是由ORACLE命令创建的，并可在其它的PL/SQL块中调用。它们在创建时要进行编译，并将编译后的代码存储在数据库中。当子程序被调用时，编译后的代码从数据库中读出并执行。
一个子程序也可以在块的定义部分创建，这样的子程序被叫作局部子程序。
下面定义了一个局部函数formatname：

Java代码

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_authsrecord.name,v_authsrecord.sex);
dbms_output.put_line(v_formattedname);
end loop;
end;

coolbaby1984

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
存储过程和函数

存储过程与函数 1、存储过程存储过程的参数形式参数和实际参数，例如，有如下一个存储过程，该过程接收一个作家代码和一个工资值，将该作家的工资改为接收到的工资值。Java代码 create or replace procedure updateauths( p_authscode auths.author_code%type, p_authssala
复制链接

扫一扫