Oracle常用语法

--创建表   
--创建部门表   
create table department_13(department_id number(6), department_name varchar(25),   
                           manager_id number(6), location_id number(4));   
--使用子查询创建表   
create table department_13_temp as select department_id, department_name from department_13;   
  
--修改表   
--增加字段   
alter table department_13_temp add(manager_id number(6));   
--删除字段   
alter table department_13_temp drop column manager_id;   
--修改字段名称   
alter table 表名 rename column 原列名 to 新列名;   
--修改字段类型   
alter table department_13_temp modify(manager_id varchar(6));   
--修改字段大小   
alter table department_13_temp modify(manager_id number(4));   
  
--删除表   
drop table department_13_temp;   
  
--数据字典表   
select table_name from dba_tables;   
select table_name from user_tables;   
--察看用户拥有的数据库对象类型   
select distinct object_type from user_objects;   
  
  
--约束   
--创建非空约束(同时也是列级约束)   
create table department_13(department_id number(6)  constraint dept_13_id not null, department_name    
  
varchar(25),   
                           manager_id number(6), location_id number(4));   
--创建唯一性约束(同时也是表级约束)   
create table department_13(department_id number(6), department_name varchar(25),   
                           manager_id number(6), location_id number(4),   
                           constraint dep_id_13_uni unique(department_id));   
--创建主键约束   
create table department_13(department_id number(6), department_name varchar(25),   
                           manager_id number(6), location_id number(4),   
                           constraint dep_id_13_pri primary key(department_id));   
  
--创建外键约束   
create table employee_13(employee_id number(6), employee_name varchar(25), email varchar(28), hire_date    
  
date,   
                         job_id varchar(20), salary number(8,2),commission_pct number(2,2),   
                         manager_id number(6), department_id number(6),   
                         constraint emp_13_foreign  foreign key(department_id)   
                          references department_13(department_id));   
--check约束   
create table employee_13_temp(employee_id number(6), employee_name varchar(25), email varchar(28),    
  
hire_date date,   
                         job_id varchar(20), salary number(8,2),commission_pct number(2,2),   
                         manager_id number(6), department_id number(6),   
                         constraint emp_sal_min check(salary > 8888));   
--增加约束   
alter table employee_13_temp add constraint emp_13_pri primary key(employee_id);   
alter table employee_13_temp modify(salary not null);   
  
--删除约束   
alter table employee_13_temp drop constaint emp_13_pri;   
--删除被外键参照的主键约束   
alter table department_13 drop primary key cascade;   
  
--手工创建索引   
create Index emp_13_sal on employee_13(salary);   
  
--删除索引   
drop index emp_13_sal;   
  
--创建序列   
create sequence hospital_id   
minvalue 1  
maxvalue 999999999999  
start with 11  
increment by 1  
cache 10;   
  
  
--创建视图   
create or replace view emp_13_11 as select employee_id, employee_name, salary, job_id from employee_13    
  
where department_id = 11;   
create or replace view emp_13_dept as select d.department_name, d.manager_id, e.employee_name, e.salary    
  
from employee_13 e, department_13 d   
  where e.department_id = d.department_id and e.department_id = 11;   
  
create or replace view emp_13_dept_temp as select d.department_name, d.manager_id, e.employee_name,    
  
e.salary from employee_13 e, department_13 d   
  where e.department_id = d.department_id;   
  
--删除视图   
drop view emp_13_11;   
  
--查找出薪水最高的三个员工的信息(Top-N分析法):使用到了行内视图   
select rownum, employee_name, salary from (select employee_name, salary from employee_13 order by    
  
salary desc) where rownum <=3;   
  
select * from (select employee_name, salary from employee_13 order by salary desc) where rownum <=3;   
  
--创建一个同义词   
create synonym ct from System.emp_13_dept_temp;   
--删除同义词   
drop synonym ct   
  
  
/*  
 *数据操作语句(操作表的数据)  
 */  
  
--Insert语句   
insert into department_13 values(13,'测试部',120,119);   
insert into department_13 values(28,null,null,113);   
insert into department_13 values(&department_id, '&department_name',&manager_id, &location_id);   
  
--Update语句    
update employee_13 set salary=66566 where employee_id = 3;   
  
--merge语句(数据合并语句)   
merge into depat_13_temp a   
using department_13 b   
on(a.department_id = b.department_id)   
when matched then   
  update set   
     a.department_name = b.department_name,   
     a.manager_id = b.manager_id,   
     a.location_id = b.location_id   
when not matched then   
  insert(a.department_id, a.department_name, a.manager_id, a.location_id)   
  values(b.department_id, b.department_name, b.manager_id, b.location_id);   
  
--提交事务   
update department_13 set manager_id = 120 where department_id = 14;   
commit;   
  
--察看自动提交环境变量   
show autocommit;   
--打开自动提交   
set autocommit on;   
  
--savepoint   
  
update department_13 set manager_id=130 where department_id > 14;   
  
savepoint undo1;   
  
delete from department_13 where department_id > 14;   
  
savepoint undo2;   
  
rollback to undo1;   
  
--SELECT语句   
--带算书表达式的select语句   
select employee_id, employee_name, salary, salary*12 from employee_13;   
--带连接表达式的select语句   
select employee_name|| '的年薪是:'|| salary*12 ||'美元'from employee_13;   
  
--对空值的引用   
select employee_name, salary, salary*(1+commission_pct) "奖金" from employee_13;   
--字段别名   
select employee_name "姓名", salary "薪水", salary*(1+commission_pct) "奖金" from employee_13;   
  
--去掉重复值   
select distinct salary from employee_13;   
  
--带条件的查询   
select employee_id, employee_name, salary from employee_13 where department_id = 10;   
--得到当前日期格式字符串   
select * from v$nls_parameters;   
--得到系统当前日期   
select sysdate from dual;   
  
--比较操作符   
--between..and   
select employee_name, job_id, hire_date from employee_13 where salary between 4000 and 7000;   
  
--in   
select employee_name, job_id, hire_date from employee_13 where salary in(6111,4111,7222);   
  
--like   
select employee_name, job_id, hire_date from employee_13 where employee_name like '李%';   
  
--is null  
select employee_name, job_id, hire_date from employee_13 where commission_pct is null;   
  
--比较操作的逻辑运算符   
-AND   
select employee_name, job_id, hire_date from employee_13 where salary between 4000 and 7000 and job_id    
  
= '软件架构师';   
-- and .. or   
select employee_name,  salary from employee_13 where (job_name = '软件工程师' or job_name = '软件架构师   
  
') and salary > 4000;   
  
--排序显示   
--单字段排序   
select employee_name, salary from employee_13 order by salary desc;   
--组合字段排序(主排序字段相同时，按照辅助排序字段排序)   
select employee_name, salary, hire_date from employee_13 order by salary desc,hire_date desc;   
  
  
/**  
 * SQL函数  
 */  
--单行函数   
--字符函数   
--大小写转换函数   
select employee_id, salary from employee_13 where lower(employee_name) = 'draglong';   
select employee_id, salary from employee_13 where upper(employee_name) = 'DRAGLONG';   
select employee_id, salary from employee_13 where Initcap(employee_name) like 'D%';   
--字符处理函数   
select replace('db2', 'oracle') from dual;   
select employee_name, concat(employee_name, job_name) name, length(employee_name) len, instr   
  
(employee_name,'g') ins   
  from employee_13 where substr(employee_name,1,5) = 'dragl';   
  
--日期函数   
select employee_name, job_name, (sysdate-hire_date)/7 weeks from employee_13;   
select hire_date, months_between(sysdate,hire_date) week, add_months(hire_date,6) week2, next_day   
  
(sysdate,'星期六') nextday,   
                  last_day(hire_date) from employee_13;   
--round函数   
select employee_name, hire_date, round(hire_date,'MONTH') from employee_13;   
--trunc函数   
select trunc(sysdate,'D'), trunc(sysdate,'MM'),trunc(sysdate,'MONTH'), trunc(sysdate,'DD') from dual;   
  
--转换函数   
--日期转换为字符TO_CHAR(字段名,'格式字符串')   
select employee_name, to_char(hire_date, 'MM/YY') from employee_13;   
select employee_name, to_char(hire_date, 'YEAR"年"MM"月"DD"日"') from employee_13;   
  
select employee_name, to_char(hire_date,'"北京时间"YYYY"年"MONDD"日"HH24"时"MI"分"SS"秒"') FROM    
  
employee_13;   
select employee_name, to_char(hire_date,'YYYYspth"年"MONDD"日"HH24"时"MI"分"SS"秒"') FROM employee_13;   
  
--数字转换为字符TO_CHAR(字段名,'格式字符串')   
select employee_name, to_char(salary,'$99,999.99') from employee_13;   
select employee_name, to_char(salary,'L99,999.99') from employee_13;   
select employee_name, to_char(salary,'$00,000.00') from employee_13;   
--字符型日期转换为日期型日期to_date('日期字段值','格式字符串')   
insert into employee_13 values(213,null,null,to_date('2007年04月28  
  
日','YYYY"年"MM"月"DD"日"'),null,null,null,null,null);   
  
--第五类函数   
/**  
 *为空处理函数  
 */  
--NVL函数   
select employee_name "姓名", salary "薪水", salary*(1+nvl(commission_pct,0)) "奖金" from employee_13;   
--NVL2   
select employee_name "姓名", salary "薪水", nvl2(commission_pct,'架构师','工程师') "级别" from    
  
employee_13;   
  
--NULLIF   
select employee_name,length(employee_name) a, job_name, length(job_name) b, nullif(length   
  
(employee_name),length(job_name)) result from employee_13;   
  
--COALESCE(取得列表中的第一个非空值)   
select employee_name, coalesce(commission_pct,salary) from employee_13;   
  
  
/**  
 *CASE语句  
 */  
select employee_name, job_name, salary,   
  case job_name when '软件工程师' then 0.40*salary   
                when '软件架构师' then 0.30*salary   
                when '系统架构师' then 0.20*salary   
  else      salary end "加薪幅度"  
from employee_13;   
  
/**  
 *DECODE语句  
 */  
select employee_name, salary, job_name,   
      decode(job_name, '软件工程师',0.40*salary,   
                       '软件架构师',0.30*salary,   
                       '系统架构师',0.20*salary,   
                       salary) "工资涨幅"  
from employee_13;   
  
/**  
 * 分组函数(多行函数)  
 */  
select employee_name, salary,avg(salary),count(salary),max(salary),min(salary),sum(salary) from    
  
employee_13;   
  
--Group by语句   
select department_id, avg(salary) from employee_13 group by department_id;   
  
/**  
 *多表连接和子查询  
 */  
--等值连接   
select  d.department_name, e.employee_name, d.department_id, e.salary   
from employee_13 e, department_13 d   
where e.department_id = d.department_id   
      and d.department_name = '开发部';   
--非等值连接   
select  d.department_name, e.employee_name, d.department_id, e.salary   
from employee_13 e, department_13 d   
where e.department_id = d.department_id   
      and e.salary between 4000 and 7000;   
--左外连接   
select  d.department_name, e.employee_name, d.department_id, e.salary   
from employee_13 e, department_13 d   
where e.department_id(+) = d.department_id;   
--右外连接   
select  d.department_name, e.employee_name, d.department_id, e.salary   
from employee_13 e, department_13 d   
where e.department_id = d.department_id(+);   
  
--自连接   
select worker.employee_name || ' work for ' || manager.employee_name from employee_13 worker,    
  
employee_13 manager   
            where worker.manager_id = manager.employee_id;   
  
--sql 1999  
--cross join   
select employee_name, department_name from employee_13 cross join department_13;   
--natural join   
select department_name from department_13 natural join employee_13;   
--join...using(使用指定的字段进行等值连接)   
select department_name from department_13 join employee_13 using(department_id);   
  
--join..on(不同名称的字段上的等值连接)   
select department_name from department_13 d join employee_13 e on(d.department_id = e.employee_id);   
--左外连接   
select  d.department_name, e.employee_name, d.department_id, e.salary   
from employee_13 e right outer join department_13 d   
on d.department_id = e.department_id;   
--全连接   
select e.employee_name, e.department_id, d.department_name from employee_13 e full outer join    
  
department_13 d   
 on(d.department_id = e.department_id);   
  
  
/**  
 *子查询   
 */  
select employee_name, salary from employee_13 where salary>(   
                        select salary from employee_13 where employee_name = '高伟祥') order by salary;   
  
--单行比较操作   
select employee_name, job_name, salary from employee_13 where job_name =(select job_name from    
  
employee_13 where employee_id = 2);   
select employee_name, job_name, salary from employee_13 where salary =(select min(salary) from    
  
employee_13);   
  
select department_id, min(salary) from employee_13 group by department_id having min(salary) > (select    
  
min(salary) from employee_13 where department_id = 10);   
  
--多行比较操作   
select employee_id, employee_name, job_name, salary from employee_13 where department_id in(   
                 select department_id from employee_13 where job_name = '软件工程师') AND    
                 job_name <>'软件工程师';   
  
select employee_name, salary, job_name from employee_13 where salary < ANY(   
                            select salary from employee_13 where job_name = '软件工程师') and   
                            job_name <>'软件工程师';    
  
select employee_name, salary, job_name from employee_13 where salary < ALL(   
                            select salary from employee_13 where job_name = '软件工程师') and   
                            job_name <>'软件工程师';    
  
select employee_name,job_name,salary from employee_13 where department_id in (select department_id from    
  
employee_13 where job_name='软件工程师' and job_name<>"软件工程师');   
  
select replace('Oracle Sql','Oracle','DB2') from dual;   
  
/**  
 *PL/SQL开发  
 */  
--变量定义   
set serveroutput on   
declare   
  id number(6,4):=0;   
  hire_date date:=sysdate+7;   
  v_tax_rate constant number(3,2):=8.25;   
  v_valid boolean not null:=true;   
begin   
  dbms_output.put_line('编号是: ' ||id);   
  dbms_output.put_line('入职时间: ' ||hire_date);   
  dbms_output.put_line('税率: ' ||v_tax_rate);   
  if v_valid then   
    dbms_output.put_line('这是真的');   
  else  
    dbms_output.put_line('这是假的');   
  end if;   
end;   
/   
  
--表类型复合变量的定义   
set serveroutput on   
declare   
  type name_table_type is table of varchar(26) index by binary_integer;   
  t_name name_table_type;   
begin   
  t_name(1):='陈龙';   
  t_name(2):='李林波';   
  t_name(3):='阿猫';   
  dbms_output.put_line('第一个数据为: '||t_name(1));   
  dbms_output.put_line('第二个数据为: '||t_name(2));   
  dbms_output.put_line('第三个数据为: '||t_name(3));   
end;     
/   
  
--记录类型变量的定义   
set serveroutput on;   
declare   
  type dept_record_type is record(   
    department_id number(6),   
    department_name varchar(20),   
    manager_id number(6),   
    location_id number(6));   
  d_dept dept_record_type;   
begin   
  d_dept.department_id :=111;   
  d_dept.department_name := '开发部';   
  d_dept.manager_id := 110;   
  d_dept.location_id := 119;   
  dbms_output.put_line(d_dept.department_id);   
  dbms_output.put_line(d_dept.department_name );   
  dbms_output.put_line(d_dept.manager_id);   
  dbms_output.put_line(d_dept.location_id);   
end;     
/   
  
//查找数据库中的记录存放在记录类型变量中   
set serveroutput on;   
declare   
  type dept_record_type is record(   
    department_id number(6),   
    department_name varchar(20),   
    manager_id number(6),   
    location_id number(6));   
  d_dept dept_record_type;   
begin   
  select department_id, department_name, manager_id, location_id into    
       d_dept.department_id, d_dept.department_name, d_dept.manager_id, d_dept.location_id from    
  
department_13 where department_id = 11;   
  dbms_output.put_line(d_dept.department_id);   
  dbms_output.put_line(d_dept.department_name );   
  dbms_output.put_line(d_dept.manager_id);   
  dbms_output.put_line(d_dept.location_id);   
end;     
/   
  
--使用%type属性定义变量   
set serveroutput on   
declare   
  e_name employee_13.employee_name%type;   
  e_sal number(8,2);   
  e_min_sal e_sal%type:= 1888;   
begin   
  e_name:='jack';   
  e_sal := 1899;   
  e_min_sal:=e_sal/3;   
  dbms_output.put_line(e_name);   
  dbms_output.put_line(e_sal);   
  dbms_output.put_line(e_min_sal);   
end;   
/   
  
//查找数据库中的记录存放在%type属性定义的变量中   
set serveroutput on   
declare   
  e_name employee_13.employee_name%type;   
  e_sal number(8,2);   
  e_min_sal e_sal%type:= 1888;   
begin   
  select employee_name, salary, salary/3 into e_name,e_sal,e_min_sal from employee_13 where employee_id    
  
= 2;   
  dbms_output.put_line(e_name);   
  dbms_output.put_line(e_sal);   
  dbms_output.put_line(e_min_sal);   
end;   
/   
  
--%rowtype属性定义的变量   
set serveroutput on   
declare   
  r_dept department_13%rowtype;   
begin   
  r_dept.department_id := 115;   
  r_dept.department_name := 'temp';   
  r_dept.manager_id := 111;   
  r_dept.location_id:=112;   
  dbms_output.put_line(r_dept.department_id);   
  dbms_output.put_line(r_dept.department_name);   
  dbms_output.put_line(r_dept.manager_id);   
  dbms_output.put_line(r_dept.location_id);   
end;   
/   
  
--pl/sql程序块   
set serveroutput on   
DECLARE   
  v_weight   NUMBER(3) := 100;   
  v_message   VARCHAR2(255) := 'Outer Value';   
BEGIN   
    DECLARE   
      v_weight  NUMBER(3) := 1;   
      v_message  VARCHAR2(255) := 'Inner value';   
      BEGIN   
 v_weight   := v_weight + 1;   
 v_message := 'Put' ||  v_message;   
        dbms_output.put_line(v_weight);   
        dbms_output.put_line(v_message);   
      END;   
    v_weight   := v_weight + 1;   
    v_message  := 'Put'|| v_message;   
    dbms_output.put_line(v_weight);   
    dbms_output.put_line(v_message);   
END;   
/   
  
--PL/SQL中的select语句   
set serveroutput on   
declare   
  e_name employee_13.employee_name%type;   
  e_sal number(8,2);   
  e_min_sal e_sal%type:= 1888;   
begin   
  select employee_name, salary, salary/3 into e_name,e_sal,e_min_sal from employee_13;   
  dbms_output.put_line(e_name);   
  dbms_output.put_line(e_sal);   
  dbms_output.put_line(e_min_sal);   
end;   
/   
  
--修改当前会话的语言环境   
alter session set nls_language=american;   
--pl/sql中的insert ,update, delete   
begin   
  insert into department_13 values(321,'test',111,null);   
  update department_13 set manager_id = 112 where department_id =12;   
  delete department_13 where department_id = 15;   
  commit;   
end;   
/   
  
--pl/sql中的条件分支语句   
--if...then   
set serveroutput on   
declare   
  v_sal number;   
begin   
  select salary into v_sal from employee_13 where employee_id = 2;   
  IF v_sal < 3000 THEN   
    dbms_output.put_line('薪水较低');   
  ELSIF v_sal < 10000 THEN   
    dbms_output.put_line('中等薪水');   
  ELSE   
    dbms_output.put_line('薪水很高');   
  END IF;   
end;   
/   
  
set serveroutput on   
declare   
  v_sal number;   
begin   
  select salary into v_sal from employee_13 where employee_id = 2;   
  IF v_sal < 3000 THEN   
    update employee_13 set job_name='软件工程师';   
    commit;   
  END IF;   
  IF v_sal >6000 THEN   
    update employee_13 set job_name='软件架构师';   
    commit;   
  END IF;   
end;   
  
--简单循环   
declare    
 v_number number:=1;   
begin   
 loop   
   insert into test_table values('姓名'||v_number,v_number*10);   
   v_number:= v_number+1;   
   exit when v_number>10;   
  end loop;   
end;   
/   
  
--for循环   
set serveroutput on   
declare   
  type t_number is table of number index by binary_integer;   
  v_1 t_number;   
  v_total number;   
begin   
  for v_count IN 1..10 LOOP   
    v_1(v_count):=v_count;   
    dbms_output.put_line(v_1(v_count));   
  end loop;   
  v_total := v_1.COUNT;   
  dbms_output.put_line(v_total);   
end;   
/   
  
--for循环和if的结合使用   
begin   
  for v_1 in 1..10 loop   
   insert into test_table values('陈龙'||v_1, v_1*10);   
   if v_1 =5 then   
     exit;   
   end if;   
  end loop;   
end;   
/   
  
--while循环   
declare    
  v_1 number:=1;   
begin    
  while v_1 <=10 loop   
   insert into test_table values('draglong'||v_1, v_1+10);   
   v_1:= v_1+1;   
  end loop;   
end;   
/   
  
--游标   
--使用游标取得记录   
set serveroutput on   
declare   
  e_name employee_13.employee_name%type;   
  e_sal  employee_13.salary%type;   
  e_job  employee_13.job_name%type;   
  cursor cl is   
    select employee_name, salary, job_name from employee_13 where job_name='超人';   
  begin   
    open cl;   
    if cl%isopen then    
      loop    
        fetch cl into e_name, e_sal,e_job;   
        dbms_output.put_line(e_name);   
        dbms_output.put_line(e_sal);   
        dbms_output.put_line(e_job);   
      
       exit when cl%notfound;   
      end loop;   
      dbms_output.put_line('取得的游标记录数为: '||cl%rowcount);   
      close cl;   
    end if;   
end;   
/   
  
--游标的复合类型变量的使用   
set serveroutput on   
declare   
  cursor cl is    
     select employee_name, salary, job_name from employee_13;   
  emp_record cl%rowtype;   
begin   
  open cl;   
  loop    
     fetch cl into emp_record;   
     dbms_output.put_line(emp_record.employee_name);   
     dbms_output.put_line(emp_record.salary);   
     dbms_output.put_line(emp_record.job_name);   
     exit when cl%notfound;   
     end loop;   
      dbms_output.put_line('取得的游标记录数为: '||cl%rowcount);   
      close cl;   
end;   
/   
--for循环取得游标记录   
set serveroutput on   
declare   
  v_number number;   
  cursor cl is    
   select employee_name, salary, job_name from employee_13;   
begin   
  for v1 in cl loop   
     dbms_output.put_line(v1.employee_name);   
     dbms_output.put_line(v1.salary);   
     dbms_output.put_line(v1.job_name);   
     
     v_number:=v1.COUNT;    
  end loop;   
     --dbms_output.put_line('取得的游标记录数为: '||v_number);    
end;     
/   
  
  
/**  
 *异常处理部分  
 */  
--预定义异常   
  
set serveroutput on   
declare   
  e_name employee_13.employee_name%type;   
  e_sal number(8,2);   
  e_min_sal e_sal%type:= 1888;   
begin   
  select employee_name, salary, salary/3 into e_name,e_sal,e_min_sal from employee_13 where salary >    
  
2000;   
  dbms_output.put_line(e_name);   
  dbms_output.put_line(e_sal);   
  dbms_output.put_line(e_min_sal);   
exception   
  when NO_DATA_FOUND THEN   
    dbms_output.put_line('没有符合条件的数据');   
  when TOO_MANY_ROWS THEN   
    dbms_output.put_line('数据库中存在多条记录，不符合查找的要求');   
  when others then   
    dbms_output.put_line('其他错误');   
end;   
/   
  
--非预定义异常   
set serveroutput on   
declare   
  EX EXCEPTION;   
  pragma EXCEPTION_INIT(EX, -01400);   
begin   
  insert into department_13(department_id)values(null);   
exception   
  when EX then   
   dbms_output.put_line('ora-1400 occurred 必须插入有效的部门编号');   
end;   
/   
  
  
set serveroutput on   
declare   
  EX EXCEPTION;   
  pragma EXCEPTION_INIT(EX, -2292);   
begin   
  delete from department_13 where department_id = 10;   
exception   
  when EX then   
   dbms_output.put_line('ora-2292 occurred 该条记录已经被其他字表参照');   
end;   
/   
  
--用户自定义异常   
set serveroutput on   
declare   
  e_employee EXCEPTION;   
  v1 number;   
begin   
  select count(*) into v1 from employee_13 where department_id = 12;   
  dbms_output.put_line(v1);   
  if v1 > 0 then   
    raise e_employee;   
  else  
   delete from department_13 where department_id = 12;   
  end if;   
exception   
  when e_employee then   
   dbms_output.put_line('部门不能删除，因为部门存在员工');   
end;   
/   
       
--创建错误日志表   
create table log_table_error(code number(30), message varchar(200), info varchar(200));   
--when others子句   
  
declare    
  v_ErrorCode number;   
  v_ErrorMessage varchar(200);   
  v_CurrentUser varchar(8);   
  v_Information varchar(100);   
  v_name varchar(30);   
begin   
  select employee_name into v_name from employee_13;   
exception   
  when others then    
    v_ErrorCode := SQLCODE;   
    v_ErrorMessage:= SQLERRM;   
    v_CurrentUser:=USER;   
    v_information:='Error encountered on ' || to_char(sysdate) || 'by database user ' || v_CurrentUser;   
    insert into log_table_error values(v_ErrorCode, v_ErrorMessage,v_Information);   
end;   
/   
  
/**  
 *存储过程  
 */  
--创建修改数据的存储过程   
CREATE OR REPLACE PROCEDURE UpdateEmployeeSalary(   
  v_emp_id  IN NUMBER,  v_new_salary IN NUMBER)   
IS   
BEGIN   
  UPDATE employee_13   
    SET salary=v_new_salary   
  WHERE employee_id = v_emp_id;   
  COMMIT;   
END;   
/   
  
--创建追加数据的存储过程   
CREATE OR REPLACE PROCEDURE AddDepartment(   
  dept_id department_13.department_id%type,   
  dept_name department_13.department_name%type,   
  manag_id department_13.manager_id%type,   
  location_id department_13.location_id%type)   
IS   
BEGIN   
  INSERT INTO department_13 VALUES(dept_id, dept_name, manag_id, location_id);   
  COMMIT;   
END;   
/   
  
  
--存储过程调用存储过程   
CREATE OR REPLACE PROCEDURE process_updateSalary(v_emp_id  IN NUMBER,  v_new_salary IN NUMBER)   
IS   
BEGIN   
  UpdateEmployeeSalary(v_emp_id, v_new_salary);   
END;   
  
--创建函数   
CREATE OR REPLACE FUNCTION tax_rate(v_value IN NUMBER) RETURN NUMBER   
IS   
BEGIN   
  IF v_value >1600 THEN   
    RETURN(v_value*0.11);   
  ELSE   
    RETURN(0);   
  END IF;   
END tax_rate;    
/   
  
CREATE OR REPLACE FUNCTION rick_tax(r_test IN NUMBER)   
        return NUMBER   
        IS   
        temp NUMBER;   
        begin   
             select salary into temp from employee_13_rick where employee_id = r_test;   
  
             IF temp > 1600 THEN   
                RETURN (temp*0.11);   
             ELSE   
                RETURN (0);   
             END IF;   
        END rick_tax;   
  
CREATE OR REPLACE FUNCTION Select_salary(id IN NUMBER) RETURN NUMBER   
IS   
v_salary  NUMBER;   
cursor cl is   
 SELECT SALARY FROM employee_13 WHERE employee_id=id;   
BEGIN    
open cl;   
loop   
  fetch cl into v_salary;    
    exit when cl%notfound;   
  end loop;   
  close cl;   
return(v_salary*0.11);   
END Select_salary;   
/   
  
--创建包   
--创建包头   
CREATE OR REPLACE PACKAGE dml_dept   
IS   
  PROCEDURE insert_dept(p_id number, p_name varchar, m_id number, l_id number);   
  PROCEDURE delete_dept(p_id number);   
  PROCEDURE update_dept(p_id number, m_id number);   
  FUNCTION  select_manager(p_id number) RETURN NUMBER;   
END dml_dept;   
/   
--创建包体   
CREATE OR REPLACE PACKAGE BODY dml_dept   
IS   
  --插入数据的存储过程   
  PROCEDURE insert_dept(p_id number,p_name varchar,m_id number,l_id number)   
    IS   
      v_1 number;   
  BEGIN   
    select count(*) into v_1 from department_13 where department_id = p_id;   
    if v_1 > 0 then   
      dbms_output.put_line('这个部门已经存在,不需要加入');   
    else  
      insert into department_13 values(p_id,p_name, m_id, l_id);   
    end if;   
  end;   
  --删除数据的存储过程   
  procedure delete_dept(p_id number)   
    is   
      v1 number;   
    begin   
      select count(*) into v1 from department_13 where department_id = p_id;   
      if v1 > 0 then   
        delete department_13 where department_id = p_id;   
      end if;   
  end;   
  --修改数据的存储过程   
  procedure update_dept(p_id number, m_id number)   
  is   
   v1 number;   
  begin   
   update department_13 set manager_id = m_id where department_id = p_id;   
  end;   
  
  --查找数据的函数     
 function select_manager(p_id number) return number   
 is   
   v1 number;   
 begin     
   select department_id into v1 from department_13 where manager_id = p_id;   
   dbms_output.put_line('部门编号是：'|| v1);   
   return(v1);   
 end;   
end dml_dept;   
/   
  
  
--在Oracle中建一个编号会自动增加的字段,以利于查询    
　　1、建立序列：    
　　    
　　CREATE SEQUENCE checkup_no_seq    
　　NOCYCLE    
　　MAXVALUE 9999999999    
　　START WITH 2;    
　　2、建立触发器：    
　　    
　　CREATE OR REPLACE TRIGGER set_checkup_no    
　　BEFORE INSERT ON checkup_history    
　　FOR EACH ROW    
　　DECLARE    
　　next_checkup_no NUMBER;    
　　BEGIN    
　　--Get the next checkup number from the sequence    
　　SELECT checkup_no_seq.NEXTVAL    
　　INTO next_checkup_no    
　　FROM dual;    
　　--use the sequence number as the primary key    
　　--for the record being inserted    
　　:new.checkup_no := next_checkup_no;    
　　END;