存储过程和存储函数的简单应用举例

存储过程和存储函数的简单应用举例

指存储在数据库中的供所有用户程序调用的子程序加存储过程、存储函数

1．创建存储过程
create procedure命令

（1）为所有的员工 涨工资
SQL> create or replace procedure updateSal
  2  as
  3   cursor cl is select sal,empno from emp;
  4   emp_sal emp.sal%type;
  5   emp_no emp.empno%type;
  6  begin
  7      open cl;
  8      loop
  9           fetch cl into emp_sal,emp_no;
 10           exit when cl%notfound;
 11           update emp set sal = emp_sal*1.1 where empno=emp_no;
 12      end loop;
 13      close cl;
 14  end;
 15  /
Procedure created

存储过程的执行为：
SQL> begin
  2  updateSal;
  3  end;
  4  /
PL/SQL procedure successfully completed

也可以这么执行
SQL> exec updateSal;
PL/SQL procedure successfully completed



（2）为指定的员工涨10%的工资
SQL> create or replace procedure updateSal(emp_no number)
  2      as
  3      emp_sal emp.sal%type;
  4      begin
  5  
  6          select sal into emp_sal from emp where empno=emp_no;
  7  
  8          update emp set sal = emp_sal*1.1 where empno=emp_no;
  9     end;
 10  /
 
Procedure created
 
SQL> begin
  2  updateSal(7369);
  3  end;
  4  /
PL/SQL procedure successfully completed
或
SQL> exec updateSal(7369); 
PL/SQL procedure successfully completed

注:存储过程在创建的时候不能写declare声明


存储函数
求某个员工的年薪收入
SQL> create or replace function sumSal(emp_no number) 
  2  return number --必须有返回值的类型	
  3  as
  4  --声明变量
  5  emp_sal emp.sal%type;
  6  emp_comm emp.comm%type;
  7  total emp.sal%type;
  8  begin
  9    select sal,comm into emp_sal,emp_comm from emp where empno=emp_no;
 10    total := emp_sal*12+nvl(emp_comm,0);
 11    return total;-- 返回值的类型必须与返回的类型相同
 12  end;
 13  /
 
Function created
 
SQL> begin
  2  dbms_output.put_line(sumSal(7369));
  3  end;
  4  /
 
11616
PL/SQL procedure successfully completed


注：存储函数在创建的时候必须的几个条件为：
（1）--function声明（参数值必须有类型）  function声明的参数必须有类型
（2） return number --必须有返回值的类型	
（3）return total;--返回值的类型必须与返回的类型相同

建立存储函数的语法：
CREATE [OR REPLACE] FUNCTION 函数名(参数列表) 
 RETURN  函数值类型
AS
PLSQL子程序体；