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

1. 是被命名的pl/sql块

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

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

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

必须有执行部分

可选的异常处理部分

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

三、存储过程：语法

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

create or replace procedure add_dept isv_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 datatypeIS|ASfunction_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;beginselect 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;