存储过程与函数

一、存储过程

（1）存储过程是一种PL/SQL块，以命名的数据库对象形式存储于数据库当中。

（2）可以传递参数给存储过程。存储过程可以有返回值，也可以没有返回值，存储过程的返回值必须通过参数带回；

 

例题：输入员工号，输出员工姓名

declare

v_empno emp.empno%type:='&请输入员工号：';

v_ename emp.ename%type;

begin 

  select ename into v_ename from emp where empno=v_empno;

  dbms_output.put_line(v_ename);

  exception

    when no_data_found

      then dbms_output.put_line('没有该员工！');

      when others then

        dbms_output.put_line('鹿小葵');

  end;

 

 

过程参数的三种模式：

in 用于接受调用程序的值 、 默认的参数模式

out用于调用程序返回值

in out 用于接受调用程序的值，并向调用程序返回更新的值

 

1、in 类型的参数

create or replace procedure p_a(v_empno in number) is

v_ename emp.ename%type;

begin

   select ename into v_ename from emp where empno=v_empno;

  dbms_output.put_line(v_ename);

  exception

    when no_data_found

      then dbms_output.put_line('没有该员工！');

      when others then

        dbms_output.put_line('鹿小葵');

end;

 

--调用存储过程---不带参数的存储过程

call p_a(7369);

--存储过程最大的用处就是抽取数据

 

 

2、out 类型的参数

create or replace procedure p_b(v_empno in number,v_ename out varchar2) is

begin

  select ename into v_ename from emp where empno=v_empno;

  exception

    when no_data_found

      then dbms_output.put_line('没有该员工');

      when others then

        dbms_output.put_line('777777');

  end;

 

--调用

declare

v_a emp.ename%type;

begin

  p_b(7788,v_a);

 dbms_output.put_line('员工姓名为'||v_a);

end;

 

 

 

3、in out 类型参数

 

例题：输入两个数返回两个数的和和两个数的差。

create or replace procedure p_c(a in out number,b in out number)is

begin 

  a:=a+b;--返回两数的和

  b:=a-2*b;--返回两数的差

  end;

 

--调用

declare

a number:='&a';

b number:='&b';

begin

  p_c(a,b);

  dbms_output.put_line('和：'||a||' ,差：'||b);

  end;

 

--注意

（1）没有参数的时候不需要加括号。也就是第1条语句不能这样写：

create or replace procedure myproc()

（2）如果有insert,update,delete语句，则一定要有commt语句

 

 

例题：用存储过程 编写一个计算器

（1）create or replace procedure p_jsq(a number,b varchar2,c number) is

begin

  if b='+' then

   dbms_output.put_line(a+c);

  elsif b='-' then

   dbms_output.put_line(a-c);

  elsif b='*' then

   dbms_output.put_line(a*c);

  elsif b='/' then

   dbms_output.put_line(a/c);

  else

   dbms_output.put_line('运算符不正确');

 end if;

  end;

 

call p_jsq(2,'+',3);

 

（2）declare

num varchar2(100):='&1';

num1 number(10,2);

begin

  if instr(num,'+')!=0 

    then num1 := substr(num,0,instr(num,'+')-1) + substr(num,instr(num,'+')+1);

  elsif instr(num,'-')!=0

    then num1 := substr(num,0,instr(num,'-')-1) - substr(num,instr(num,'-')+1);

  elsif instr(num,'*')!=0

    then num1 := substr(num,0,instr(num,'*')-1) * substr(num,instr(num,'*')+1);

  else

    num1 := substr(num,0,instr(num,'/')-1) / substr(num,instr(num,'/')+1); 

  end if;

  dbms_output.put_line(num1);

exception

  when others then

  dbms_output.put_line('运算错误');

end;

 

二、函数

（1）函数是可以返回值的命名的 PL/SQL 子程序。

（2）函数的返回值类型也必须是数据库类型。

 

1、创建无参函数

 

（1）例题：

create or replace function f_a

return varchar2 

is

begin

return'你们最棒';

  end;

  

调用

select f_a() from dual;

declare 

v_a varchar2(20);

begin

  v_a:=f_a();

  dbms_output.put_line(v_a);

  end;

  

（2）例题：定义函数MAX_EMP_SAL，没有参数，该函数返回所有员工薪水的最大值。

create or replace function MAX_EMP_SAL

return number 

is

    maxvalue number;

begin

    select max(sal) into maxvalue from emp;

    return maxvalue;

end;

 

调用  select max_emp_sal from dual

 

 

2、创建带参函数

 

（1）例题：定义一个函数是一个数的两倍

 create or replace function f_b(v_a number)

 return number

 is

 v_b number;

 begin

   v_b:=2*v_a;

   return v_b;

   end; 

 

 调用 select f_b(5) from dual; 

  

 declare 

 v_a number;

 begin 

   v_a:=f_b(10);

   dbms_output.put_line(v_a);

   end;  

 

 

（2）例题：输入员工编号，返回该员工的工资等级 f_c

方法一：

create or replace function f_d(v_x number)

return number

is v_y number;

begin 

select grade into v_y from emp a,salgrade b where a.sal between b.losal and b.hisal and a.empno=v_x; 

return v_y;

end;

 

方法二：

create or replace function f_c(v_a number)

return number

is v_b number;

begin

select grade into v_b from salgrade where (select sal from emp where empno=v_a) between losal and hisal;

return v_b;

end;

 

调用

select f_c(7788) from dual

select f_d(7788) from dual

declare

v_a number;

begin v_a:=f_d(7788);

  dbms_output.put_line(v_a);

   end;  

 

例题：定义函数MATH_SUM，带两个数值参数，该函数返回两个参数的和。定义好函数后，请用此函数计算员工薪水和员工编号的和。

create or replace function math_sum(value1 number, value2 number) 

return number

is

    newvalue number;

begin

    newvalue := value1+value2;

    return newvalue;

end;

 

调用：select empno,sal, math_sum(empno, sal) from  emp

 

三、存储过程与函数的区别

1 关键字不同 procedure function

2 规格说明部分 函数有且只有一个return  存储过程没有return

3 执行部分 函数中必须有return ,且return类型必须与规格说明中的类型一致，

存储过程当中也可以有return 但意义完全不同

4 调用 函数用select语句、pl/sql语句调用，存储过程用call 或pl/sql 调用

5 过程不返回任何值，函数必须返回单个值