oracle 笔记 III 之存储过程与函数

DML(Manipulation):数据操作语言
CRUD
DDL(Definition): 数据定义语言,与表，索引，同义词有关
create,alter,drop,rename,truncate(清空)
DCL(Control): 数据控制语言，与权限有关
grant,revoke
TCL(Transaction Control): 事务控制语言,与事务有关
commit,rollback,savepoint
==========================
存储过程和存储函数，相当重要，在java需要调用存储过程和函数
写一个例子：写一个函数完成如下功能，输入两个department_id,dept_1,dept_2,返回他们平均工资较高的那个部门中 manager 的salary
函数版:
create or replace function get_mgr(
 dept_id_1 employess.department_id%type,
 dept_id_2 employees.department_id%type,
 )
 return number
 is
       max_sal_1 employees.salary%type;
       max_sal_2 employees.salary%type;
       dept_id employees.department_id%type;
       mgr_id employees.manager_id%type;
       sal employees.salary%type;
begin
      select max(salary) into max_sal_1  from employees
      where department_id = dept_id_1;
      select max(salary) into max_sal_2  from employees
      where department_id = dept_id_2;
      if max_sal_1 > max_sal_2 then
         dept_id := dept_id_1;
      else
          dept_id := dept_id_2;
      end if;
      select manager_id into mgr_id from departments where department_id = dept_id;
      select salary into sal from employees where employee_id = mgr_id;
       return sal;
end;
sql版:
第一步 查询给定的两个部门中高的平均工资:30,80
select max(avg_sal)
from (select avg(salary) avg_sal from emplo