创建存储过程和函数【weber出品必属精品】

浏览数：25 / 时间：2015年06月09日

一、什么是存储过程和函数

1. 是被命名的pl/sql块

2. 被称之为pl/sql子程序

3. 与匿名块类似，有块结构:

声明部分是可选的（没有declare关键字）

必须有执行部分

可选的异常处理部分

二、匿名块和子程序之间的区别

三、存储过程：语法

CREATE [OR REPLACE] PROCEDURE procedure_name
[(argument1 [mode1] datatype1,
argument2 [mode2] datatype2,
. . .)]
IS|AS
procedure_body;

create or replace procedure add_dept is
v_deptment_id dept.deptno%type;
v_deptment_name dept.dname%type;
begin
  v_deptment_id :=60;
  v_deptment_name := ‘YWB‘;
  insert into dept(deptno,dname) values(v_deptment_id,v_deptment_name);
  commit;
  dbms_output.put_line(‘插入了:‘||sql%rowcount||‘行‘);
  end;

使用匿名块调用存储过程：

begin
  add_dept;
end;

四、函数：

CREATE [OR REPLACE] FUNCTION function_name
 [(argument1 [mode1] datatype1,
  argument2 [mode2] datatype2,
  . . .)]
RETURN datatype
IS|AS
function_body;

函数与存储过程的区别：函数必须返回数据，存储过程可以返回数据，也可以不返回数据

create or replace function check_sal return boolean is
  dept_id emp.deptno%type :=10;
  emp_no emp.empno%type :=7788;
  salary emp.sal%type;
  avg_sal emp.sal%type;
begin
select sal into salary from emp where empno=emp_no;
 select avg(sal) into avg_sal from emp where deptno=dept_id;
 if salary>avg_sal then
   return true;
   else
     return false;
     end if;
   exception 
     when no_data_found then
     return null;
  end;

在匿名块中调用函数：

begin
  if( check_sal is null) then
  dbms_output.put_line(‘由于程序异常，输出null‘);
  elsif (check_sal) then
     dbms_output.put_line(‘工资高于平均工资‘);
    else
       dbms_output.put_line(‘工资低于平均工资‘);
       end if;
  end;

给函数传递参数：

create or replace function check_sal(empno number) return boolean is
  dept_id employees.department_id%type;
  sal     employees.salary%type;
  avg_sal employees.salary%type;
begin
  select salary, department_id
    into sal, dept_id
    from employees
   where employee_id = empno;
  select avg(salary)
    into avg_sal
    from employees
   where department_id = dept_id;
  if sal > avg_sal then
    return true;
  else
    return false;
  end if;
exception
  when no_data_found then
    return null;
end;

create or replace function check_sal(empno number) return number is
  dept_id employees.department_id%type;
  sal     employees.salary%type;
  avg_sal employees.salary%type;
begin
  select salary, department_id
    into sal, dept_id
    from employees
   where employee_id = empno;
  select avg(salary)
    into avg_sal
    from employees
   where department_id = dept_id;
  if sal > avg_sal then
    return 1;
  elsif (sal = avg_sal) then
    return 2;
  else
    return 3;
  end if;
exception
  when no_data_found then
    return null;
end;

begin
  if (check_sal(200) is null) then
    dbms_output.put_line(‘由于程序异常，输出NULL‘);
  elsif (check_sal(200) = 1) then
    dbms_output.put_line(‘工资高于平均工资‘);
  elsif (check_sal(200) = 2) then
    dbms_output.put_line(‘工资等于平均工资‘);
  else
    dbms_output.put_line(‘工资低于平均工资‘);
  end if;
end;