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