Oracle之存储过程与存储函数

一、存储过程（不可以通过return返回函数值）

1.定义

存储过程是存储在数据库中提供所有用户程序调用的子程序，定义存储过程的关键字为procedure

2.创建语法

    create [or replace] procedure 存储过程名 [(参数1 in|out 类型，参数2 in|out 类型..)] --in输入参数,out输出参数
        is|as            --相当于PLSQL语句中的declare，用来声明变量，游标等，但是不可以省略
            变量名　类型; --声明变量
        begin
            程序代码体    --业务逻辑
　　    end;

3.调用存储过程 

（1）方式一

    call 存储过程名称(参数1,参数2,...);

（2）方式二（使用最多）

    declare

    begin
      存储过程名称(参数1,参数2,...);
    end;

4.实例

--给指定员工涨薪,并打印涨薪前和涨薪后的工资
/*
    参数 : in 员工编号
    参数 : in 涨多少
    
    声明一个变量 : 存储涨工资前的工资
    
    查询出当前是多少
    打印涨薪前的工资
    更新工资
    打印涨薪后的工资          
*/
create or replace procedure proc_updatesal(vempno in number,vnum in number)
is
   --声明变量.记录当前工资
   vsal number;    
begin
  --查询当前的工资
  select sal into vsal from emp where empno = vempno;
  --输出涨薪前的工资
  dbms_output.put_line('涨薪前:'||vsal);
  --更新工资
  update emp set sal = vsal + vnum where empno = vempno;
  --输出涨薪后的工资
  dbms_output.put_line('涨薪后:'||(vsal+vnum));
  --提交
  commit;
end;

二、存储函数（可以通过return返回函数值）

1.定义

存储函数是存储在数据库中提供所有用户程序调用的子程序，定义存储函数的关键字为function 

2.创建语法

    --in输入参数,out输出参数
    create [or replace] function 存储函数的名称 [(参数1 in|out 类型，参数2 in|out 类型..)] return 参数类型 
        is|as            --相当于PLSQL语句中的declare，用来声明变量，游标等，但是不可以省略
            变量名　类型; --声明变量
        begin
            程序代码体    --业务逻辑
　　    end;

3.调用存储函数 

（1）方式一

    call 存储函数名称(参数1,参数2,...);

（2）方式二（使用最多）

    declare

    begin
      存储函数名称(参数1,参数2,...);
    end;

4.实例

--查询指定员工的年薪--存储函数来实现
/*
    参数 : 员工的编号
    返回 : 年薪          
*/
create or replace function func_getsal(vempno number) return number
is
  --声明变量.保存年薪
  vtotalsal number;     
begin
  select sal*12 + nvl(comm,0) into vtotalsal from emp where empno = vempno;
  return vtotalsal;
end;


--调用存储函数
declare
  vsal number;
begin
  vsal := func_getsal(7788);
  dbms_output.put_line(vsal);
end;

--查询指定员工的年薪--存储过程来实现
--参数: 员工编号
--输出: 年薪
create or replace procedure proc_gettotalsal(vempno in number,vtotalsal out number)
is
       
begin
  select sal*12 + nvl(comm,0) into vtotalsal from emp where empno = vempno;
end;


declare
  vtotal number;
begin
  proc_gettotalsal(7788,vtotal);
  dbms_output.put_line('年薪:'||vtotal);
end;