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;