Oracle之SQL语句的使用(一)

终于把Oracle的基础学完了，记录一下Oracle的基础练习。

1.显示出表employees中的全部job_id（不能重复）
select distinct job_id from employees;

2.显示出表employees的全部列，各个列之间用逗号连接，列头显示成OUT_PUT
select employee_id||','||first_name||','||last_name||','||email||','||phone_number||','||hire_date||','||job_id||','||salary||','||commission_pct||','||manager_id||','||department_id from employees;

3.查询工资大于12000的员工姓名和工资
select last_name,salary from employees where salary>12000;

4.查询员工号为176的员工的姓名和部门号
select last_name,department_id from employees where employee_id=176;

5.选择工资不在5000到12000的员工的姓名和工资
select last_name,salary from employees 
--where salary<5000 or salary>12000
where salary not between 5000 and 1200

6.选择雇用时间在1998-02-01到1998-05-01之间的员工姓名，job_id和雇用时间
方式一：
select last_name,job_id,hire_date from employees
where to_char(hire_date,'yyyy-mm-dd') between '1998-02-01' and '1998-05-01'

方式二：
select last_name,job_id,hire_date from employees
where hire_date between to_date('1998-02-01','yyyy-mm-dd') and to_date('1998-05-01','yyyy-mm-dd')


7.选择在20或50号部门工作的员工姓名和部门号
select last_name,department_id from employees where department_id in(20,50)

8.选择在1994年雇用的员工的姓名和雇用时间
select last_name,hire_date from employees where to_char(hire_date,'yyyy')='1994'

9.选择公司中没有管理者的员工姓名及job_id
select last_name,job_id from employees where manager_id is null

10.选择公司中有奖金的员工姓名，工资和奖金级别
select last_name,salary,commission_pct from employees where commission_pct is not null

11.选择员工姓名的第三个字母是a的员工姓名
select last_name from employees where last_name like '__a%'

12.选择姓名中有字母a和e的员工姓名
select last_name from employees where last_name like '%e%a%' or last_name like '%a%e%'

13.显示系统时间(注：日期+时间)
select to_char(sysdate,'yyyy-mm-dd HH:mi:ss') from dual

14.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
select employee_id,last_name,salary,salary*1.2 "new salary" from employees

15.将员工的姓名按首字母排序，并写出姓名的长度（length）
select last_name,length(last_name) from employees order by length(last_name) desc

16.查询各员工的姓名，并显示出各员工在公司工作的月份数（worked_month）。
select last_name,round(months_between(sysdate,hire_date),1) worked_month from employees

17.查询员工的姓名，以及在公司工作的月份数（worked_month），并按月份数降序排列
select last_name,round(months_between(sysdate,hire_date),1) worked_month from employees order by worked_month desc

18.做一个查询，产生下面的结果
<last_name> earns <salary> monthly but wants <salary*3>
select last_name||' earns '||salary||' monthly but wants '||salary*3 from employees

19.使用decode函数，按照下面的条件：
    job                  grade
    AD_PRES                A
    ST_MAN                 B
    IT_PROG                C
    SA_REP                 D
    ST_CLERK               E

方式一：    
select last_name,job_id,
decode(job_id,'AD_PRES','A'
              ,'ST_MAN','B'
              ,'IT_PROG','C'
              ,'SA_REP','D'
              ,'ST_CLERK','E'
              ,'F') grade
from employees

方式二：
select last_name,job_id,
case job_id
when 'AD_PRES' THEN 'A'
when 'ST_MAN'  THEN 'B'
when 'IT_PROG' THEN 'C'
when 'SA_REP'  THEN 'D'
when 'ST_CLERK' THEN 'E'
else 'F'
end grade
from employees  

20.显示所有员工的姓名，部门号和部门名称。
说明：+在哪边，那么就是与其相反的外连接
方法一：
select last_name,e.department_id,department_name
from employees e,departments d
where e.department_id=d.department_id(+)



方法二：
select last_name,e.department_id,department_name
from employees e
left outer join
departments d
on e.department_id=d.department_id

方法三：
select last_name,e.department_id,department_name
from employees e
left join
departments d
on e.department_id=d.department_id

21.查询90号部门员工的job_id和90号部门的location_id
方式一：
select distinct e.job_id,d.location_id
from employees e
inner join 
departments d
on e.department_id=d.department_id
where e.department_id=90

方式二：
select distinct e.job_id,d.location_id
from employees e
left outer join 
departments d
on e.department_id=d.department_id
where e.department_id=90

22.选择所有有奖金的员工的last_name , department_name , location_id , city
select 
e.last_name,d.department_name,d.location_id,l.city
from employees e
inner join departments d
on e.department_id=d.department_id
inner join locations l
on d.location_id=l.location_id
where e.commission_pct is not null

23.选择city在Toronto工作的员工的last_name , job_id , department_id , department_name 
select e.last_name,e.job_id,e.department_id,d.department_name
from employees e join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id
where lower(l.city)='toronto'

24.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，
employees   Emp#    manager Mgr#

select
e1.last_name "employees",
e1.employee_id "Emp#",
e2.last_name "manager",
e2.employee_id "Mgr#" 
from employees e1
left join
employees e2
on e1.manager_id=e2.employee_id

=========================分组函数===================================

25.查询公司员工工资的最大值，最小值，平均值，总和
select max(salary),min(salary),avg(salary),sum(salary) from employees

26.查询各job_id的员工工资的最大值，最小值，平均值，总和
select job_id, max(salary),min(salary),avg(salary),sum(salary)
from employees
group by job_id

27.选择具有各个job_id的员工人数
说明：count(employee_id)带有去除employee_id为空的情况
select job_id,count(employee_id)
from employees
group by job_id 

28.查询员工最高工资和最低工资的差距（DIFFERENCE）
select max(salary),min(salary), max(salary)-min(salary) 差值 from employees

29.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
说明：此处where必须跟在from的后面
select manager_id,min(salary)
from employees
where manager_id is not null
group by manager_id
having min(salary)>6000

30.查询所有部门的名字，location_id，员工数量和工资平均值
select department_name,location_id,
count(*),avg(salary)
from employees e
right outer join
departments d
on e.department_id=d.department_id
group by d.location_id,d.department_name

31.查询公司在1995-1998年之间，每年雇用的人数，结果类似下面的格式
total   1995    1996    1997    1998
20       3        4       6       7

select count(employee_id) "total",
count(decode(to_char(hire_date,'yyyy'),'1995','1',null)) "1995",
count(decode(to_char(hire_date,'yyyy'),'1996','1',null)) "1996",
count(decode(to_char(hire_date,'yyyy'),'1997','1',null)) "1997",
count(decode(to_char(hire_date,'yyyy'),'1998','1',null)) "1998"
from employees
where to_char(hire_date,'yyyy')
in('1995','1996','1997','1998')

=========================子查询===================================

32.查询和Zlotkey相同部门的员工姓名和雇用日期
select last_name,hire_date,department_id from employees
where department_id=(
      select department_id 
      from employees
      where lower(last_name)='zlotkey'
) and lower(last_name)<>'zlotkey'

33.查询工资比公司平均工资高的员工的员工号，姓名和工资。
select employee_id,last_name,salary
from employees 
where salary>
(select avg(salary) from employees)

34.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
方式一：
select employee_id,last_name,salary
from employees e1
where salary > (
              select avg(salary)
               from employees e2
               where e1.department_id = e2.department_id
               group by department_id
               )

方式二：
select employee_id,last_name,salary
from employees e
inner join 
(select department_id,avg(salary) avg_i
from employees 
where department_id is not null
group by department_id) e1
on e.department_id=e1.department_id
where e.salary>e1.avg_i

35.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
select employee_id,last_name 
from employees
where department_id in
(
select distinct department_id from employees
where last_name like '%u%'
)

36. 查询在部门的location_id为1700的部门工作的员工的员工号
select last_name, employee_id from employees
where department_id in
(
select distinct department_id from departments where location_id=1700
) and last_name not like '%u%'


37.查询管理者是King的员工姓名和工资
select last_name,salary,manager_id from employees
where manager_id 
in (select employee_id from employees where last_name='King')

=========================创建和管理表=====================================
38. 创建表dept1
name    Null?   type
id             Number(7)
name           Varchar2(25)

39. 将表departments中的数据插入新表dept2中
create table dept2 
as 
select * from departments

40. 创建表emp5
name    Null?      type
id                Number(7)
First_name        Varchar2(25)
Last_name         Varchar2(25)
Dept_id           Number(7)

create table emp5(
      id number(7),
      First_name varchar2(25),
      Last_name varchar2(25),
      Dept_id number(7)
)

41. 将列Last_name的长度增加到50
alter table emp5 modify(Last_name varchar2(50))

42. 根据表employees创建employees2
create table employees2 
as
select * from employees

43. 删除表emp5
drop table emp5

44.将表employees2重命名为emp5
alter table employees2 rename to emp5;
或
rename employees2 to emp5；

45.在表dept和emp5中添加新列test_column，并检查所作的操作
alter table dept add(test_column varchar2(20));
desc dept;

46.在表dept和emp5中将列test_column设置成不可用，之后删除
alter table dept set unused column test_column 
删除：alter table dept drop unused columns

47.直接删除表emp5中的列 dept_id
alter table emp5 drop column dept_id;


=========================数据处理=====================================
48. 运行以下脚本创建表my_employees
Create table my_employee ( 
  id  number(3),
  first_name varchar2(10),
  Last_name  varchar2(10),
  User_id    varchar2(10),
  Salary     number(5)
);

49. 显示表my_employee的结构
DESC my_employee;

50. 向表中插入下列数据
ID  FIRST_NAME  LAST_NAME   USERID   SALARY
1   patel         Ralph      Rpatel   895
2   Dancs         Betty      Bdancs   860
3   Biri          Ben        Bbiri    1100
4   Newman        Chad       Cnewman  750
5   Ropeburn      Audrey     Aropebur 1550

insert into  my_employee values(1,'patel','Ralph','Rpatel',895)

51. 提交
commit;

52. 将1号员工的last_name修改为“drelxer”
update my_employee set last_name='drelxer' where id=1

53. 将所有工资少于900的员工的工资修改为1000
update my_employee set salary='1000' where salary<900

54. 检查所作的修正
SELECT * FROM my_employee WHERE salary < 900

55.提交
COMMIT;

56. 删除所有数据
DELETE FROM my_employee;

57. 检查所作的修正
SELECT * FROM my_employee;

58. 回滚
ROLLBACK;

59.清空表my_employees
TRUNCATE TABLE my_employee 

=========================约束=====================================
60. 向表emp2的id列中添加PRIMARY KEY约束（my_emp_id_pk）
alter table emp2 add constraint my_emp_id_pk primary key(EMPLOYEE_ID);

62. 向表dept2的id列中添加PRIMARY KEY约束（my_dept_id_pk）
alter table dept2 add  constraint my_dept_id_pk primary key(DEPARTMENT_ID)

63. 向表emp2中添加列dept_id，并在其中定义FOREIGN KEY约束，与之相关联的列是dept2表中的id列。
alter table emp2
add(dept_id number(7)
constraint emp2_dept_id_fk
references 
dept2(department_id)
)

=========================视图=====================================
64. 使用表employees创建视图employee_vu，其中包括姓名（LAST_NAME），
员工号（EMPLOYEE_ID），部门号(DEPARTMENT_ID).
create view employee_vu
as
select last_name,employee_id,department_id
from employees

65. 显示视图的结构
desc employee_vu;

66. 查询视图中的全部内容
select * from employee_vu;

67. 将视图中的数据限定在部门号是80的范围内
create or replace
view employee_vu
as 
select * from employees 
where department_id=80

68. 将视图改变成只读视图
create or replace
view employee_vu
as 
select * from employees 
where department_id=80
with read only

=========================其他数据库对象==============================
69. 创建序列dept_id_seq，开始值为200，每次增长10，最大值为10000
create sequence dept_id_seq
start with 200
maxvalue 10000
increment by 10

查询当前系列号 
select dept_id_seq.currval from dual;

查询下一个序列号
select dept_id_seq.nextval from dual;

70. 使用序列向表dept中插入数据
insert into dept values(dept_id_seq.nextval,'YANG','BeiJing');

============================PL/SQL================================
==============基础
注意：使用PL/SQL时一定要确保服务输出是打开的
命令：set serveroutput on

71. 申明一个变量来记录206号员工的salary
declare
 v_sal number(20);
begin
  select salary into v_sal from employees
  where employee_id=206;

  dbms_output.put_line(v_sal);
end;

72. 申明多个变量来记录206号员工的salary，last_name,hire_date
方式一：
declare
 v_salary number(8,2);
 v_last_name varchar2(25);
 v_hire_date date;
begin
  select salary,last_name,hire_date into
  v_salary,v_last_name,v_hire_date
  from employees
  where employee_id=206;

  dbms_output.put_line(v_last_name||','||v_salary||','
  ||to_char(v_hire_date,'yyyy-mm-dd'));
end;

方式二：
declare
 --自定义类型
 type employee_type is record(
      v_salary number(8,2),
      v_last_name varchar2(25),
      v_hire_date date
 );

 v_employee_type employee_type;
begin
  select salary,last_name,hire_date into
  v_employee_type.v_salary,
  v_employee_type.v_last_name,
  v_employee_type.v_hire_date
  from employees
  where employee_id=206;

  dbms_output.put_line(v_employee_type.v_last_name||','||v_employee_type.v_salary||','
  ||to_char(v_employee_type.v_hire_date,'yyyy-mm-dd'));
end;

方式三：
declare
 --自定义类型
 type employee_type is record(
      --动态获取变量类型，即根据表的属性来确定变量类型
      v_salary employees.salary%type,
      v_last_name employees.last_name%type,
      v_hire_date employees.hire_date%type
 );

 v_employee_type employee_type;
begin
  select salary,last_name,hire_date into
  v_employee_type.v_salary,
  v_employee_type.v_last_name,
  v_employee_type.v_hire_date
  from employees
  where employee_id=206;

  dbms_output.put_line(v_employee_type.v_last_name||','||v_employee_type.v_salary||','
  ||to_char(v_employee_type.v_hire_date,'yyyy-mm-dd'));
end;


方式四：
declare
 --申明一个记录类型的变量
 v_employee_type employees%rowtype;
begin
  select salary,last_name,hire_date into
  v_employee_type.salary,
  v_employee_type.last_name,
  v_employee_type.hire_date
  from employees
  where employee_id=206;

  dbms_output.put_line(v_employee_type.last_name||','||v_employee_type.salary||','
  ||to_char(v_employee_type.hire_date,'yyyy-mm-dd'));
end;

方式五：
declare
 --申明一个记录类型的变量
 v_employee_type employees%rowtype;
 v_employee_id number(6);
begin
  v_employee_id:=206;

  select salary,last_name,hire_date into
  v_employee_type.salary,
  v_employee_type.last_name,
  v_employee_type.hire_date
  from employees
  where employee_id=v_employee_id;

  dbms_output.put_line(v_employee_type.last_name||','||v_employee_type.salary||','
  ||to_char(v_employee_type.hire_date,'yyyy-mm-dd'));
end;

73. 删除206号员工的记录
declare
 --申明一个记录类型的变量
 v_employee_id number(6);
begin
  v_employee_id:=206;

  delete from employees where employee_id=v_employee_id;
  dbms_output.put_line('206号员工被删除了');
end;

74. 要求: 查询出 150号 员工的工资, 若其工资大于或等于 10000 则打印 'salary >= 10000'; 
若在 5000 到 10000 之间, 则打印 '5000<= salary < 10000'; 否则打印 'salary < 5000'
方式一：
declare
  v_salary employees.salary%type;
begin
  select salary into v_salary 
  from employees where employee_id=150;
  if v_salary>=10000 then dbms_output.put_line('salary>=10000');
  elsif 5000<=v_salary and v_salary<10000
  then dbms_output.put_line('5000<=salary<10000'); 
  else 
      dbms_output.put_line('salary<5000');  
  end if;
end;

方式二：
declare
  v_salary employees.salary%type;
  v_case number(1);
  v_msg varchar2(30);
begin
  select salary into v_salary 
  from employees where employee_id=150;

  v_case:=trunc(v_salary/5000);
  v_msg:=case v_case
              when 0 then 'salary < 5000'
              when 1 then '5000<= salary < 10000'
              else 'salary >= 10000'
             end; 
     dbms_output.put_line(v_salary ||','||v_case||','||v_msg);          
end;

75. 使用 CASE ... WHEN ... THEN ... ELSE ... END;
要求: 查询出 122 号员工的 JOB_ID,
 若其值为 'IT_PROG', 则打印 'GRADE: A'; 
            'AC_MGT', 打印 'GRADE B', 
            'AC_ACCOUNT', 打印 'GRADE C'; 
            否则打印 'GRADE D'

declare
  v_job_id varchar2(10);
  v_last_name varchar2(25);
  v_grade varchar2(10);
begin
  select job_id,last_name into v_job_id,v_last_name
  from employees  where employee_id=122;

  v_grade:=case v_job_id 
           when 'IT_PROG' then 'GRADE A'
           when 'AC_MGT'  then 'GRADE B'
           when 'AC_ACCOUNT' then 'GRADE C'
           else 'GRADE D'
           end;
   dbms_output.put_line(v_last_name||'`s job_id is '||v_grade);
end;


76.使用循环语句打印 1 - 100.（三种方式）
方式一：LOOP 循环体 EXIT WHEN 循环条件 END LOOP
declare
  v_number number(3);
begin
  v_number:=1;
  loop 
    dbms_output.put_line(v_number);
    v_number:=v_number+1;
  exit when
       v_number>100;
  end loop;
end;

方式二：LOOP 循环体 EXIT WHEN 循环条件 END LOOP
declare
  v_number number(3);
begin
  v_number:=1;
  loop 
    dbms_output.put_line(v_number);
  exit when
       v_number=100;
       v_number:=v_number+1;
  end loop;
end;

方式三：while 循环条件 loop 循环体 end loop
declare
  v_number number(3):=1 ;
begin
  while v_number<=100 loop
  dbms_output.put_line(v_number);
  v_number:=v_number+1;
  end loop;
end;

方式四：
begin
  for i in 1..100 loop
      dbms_output.put_line(i);
  end loop;
end;

77. 综合使用 if, while 语句, 打印 1 - 100 之间的所有素数
(素数: 有且仅用两个正约数的整数, 2, 3, 5, 7, 11, 13, ...).
方式一：
declare
 v_number number(3):=1;
 v_temp_number number(3):=1;
 v_temp_loop number(3):=2;
 v_flag number(1):=1;
begin
  while v_number<=100 
    loop
        v_temp_number:=sqrt(v_number);
        while  v_temp_loop<=v_temp_number loop
             if mod(v_number,v_temp_loop)=0 then
               v_flag:=0;
              goto label;
             end if;
             v_temp_loop:=v_temp_loop+1;
        end loop;
        if(v_flag=1) 
        then dbms_output.put_line(v_number);
        end if;
       <<label>>
        v_flag:=1;
        v_temp_loop:=2;       
        v_number:=v_number+1;
    end loop;
end;

方式二：
declare
  v_j number(3):=2;
  v_i number(3):=2;
  v_flag number(1):=1;
begin
  for i in 1..100 loop
      v_j:=sqrt(i);
      while v_i<=v_j loop
        if mod(i,v_i)=0 
        then
        v_flag:=0;
        goto label;
        end if;
        v_i:=v_i+1;
      end loop;
      if v_flag=1
      then 
        dbms_output.put_line(i);
      end if;
      <<label>>
      v_flag:=1;
      v_j:=2;
      v_i:=2;
  end loop;
end;

方式三：
declare
  v_flag number(1):=1;
begin
  for i in 1..100 loop
      for j in 2..sqrt(i)loop
          if i mod j=0 then
          v_flag:=0;
          goto label;
          end if;
      end loop;
      if v_flag=1
      then 
        dbms_output.put_line(i);
      end if;
      <<label>>
      v_flag:=1;
  end loop;
end;

78.打印1——100的自然数，当打印到50时，跳出循环，输出“打印结束”
方式一：
begin
  for i in 1..100 loop
      if i=50
      then
        goto label;
      end if;
      dbms_output.put_line(i);
  end loop;
  <<label>>
  dbms_output.put_line('打印结束');
end;

方式二：
begin
  for i in 1..100 loop
      if i=50
      then
        dbms_output.put_line('打印结束');
        exit;
      end if;
      dbms_output.put_line(i);
  end loop;
end;

==============游标
79. 打印出 80 部门的所有的员工的工资:salary: xxx
declare
 --1.定义游标
 cursor salary_cursor is select salary from employees
 where department_id=80;
 v_salary employees.salary%type;
begin
 --2.打开游标
 open salary_cursor;

 --3.提取游标
 fetch salary_cursor into v_salary;

 --4.对游标进行循环操作
 while salary_cursor%found loop
       dbms_output.put_line('salary: '||v_salary);
       fetch salary_cursor into v_salary;
 end loop;

 --5.关闭游标
 close salary_cursor;
end;

80. 打印出 80 部门的所有的员工的工资: Xxx 's salary is: xxx
declare
 --1.定义游标
 cursor salary_cursor is select last_name,salary from employees
 where department_id=80;
 v_salary employees.salary%type;
 v_last_name employees.last_name%type;
begin
 --2.打开游标
 open salary_cursor;

 --3.提取游标
 fetch salary_cursor into v_last_name,v_salary;

 --4.对游标进行循环操作
 while salary_cursor%found loop
       dbms_output.put_line(v_last_name||'`s salary is '||v_salary);
       fetch salary_cursor into v_last_name,v_salary;
 end loop;

 --5.关闭游标
 close salary_cursor;
end;

81. 打印出 manager_id 为 100 的员工的 last_name, email, salary 信息(使用游标, 记录类型)
方式一：
declare
 --1.定义游标
 cursor salary_cursor is select salary,last_name,email from employees
 where manager_id=100;
 --自定义列表属性的位置要跟查询列表的位置是一致的
 type type_employee  is record(
 v_salary employees.salary%type,
 v_last_name employees.last_name%type,
 v_email employees.email%type
 );

 v_employee type_employee;
begin
 --2.打开游标
 open salary_cursor;

 --3.提取游标
 fetch salary_cursor into v_employee;

 --4.对游标进行循环操作
 while salary_cursor%found loop
       dbms_output.put_line(v_employee.v_last_name||'`s salary is '||v_employee.v_salary||' and email is  '||v_employee.v_email);
       fetch salary_cursor into v_employee;
 end loop;

 --5.关闭游标
 close salary_cursor;
end;

方式二：
declare
 --1.定义游标
 cursor salary_cursor is select salary,last_name,email from employees
 where manager_id=100;
begin

 for v_employee in salary_cursor loop
      dbms_output.put_line(v_employee.last_name||'`s salary is '||v_employee.salary||' and email is  '||v_employee.email);
 end loop; 

end;

82.利用游标, 调整公司中员工的工资:     
    工资范围       调整基数
    0 - 5000       5%
    5000 - 10000   3%
    10000 - 15000  2%
    15000 -        1%

方式一：
declare
 --1.定义游标
 cursor salary_cursor is 
 select salary,employee_id from employees;
 temp number(4,2);
 temp_employee_id employees.employee_id%type;
 temp_employee_salary employees.salary%type;
begin
 --打开游标
 open salary_cursor;
 --提取游标
 fetch salary_cursor into temp_employee_salary,temp_employee_id;

 while salary_cursor%found loop
      if temp_employee_salary<5000 
      then temp:=0.05;
      elsif temp_employee_salary>=5000 and temp_employee_salary<10000
      then temp:=0.03;
      elsif temp_employee_salary>=10000 and temp_employee_salary<15000
      then temp:=0.02;
      else temp:=0.01;
      end if;
      update employees set salary=salary*(1+temp) where employee_id=temp_employee_id;

       fetch salary_cursor into temp_employee_salary,temp_employee_id;
 end loop;

 --关闭游标
 close salary_cursor;
end;

方式二：
使用SQL中的 decode 函数
update employees set salary=salary*(1+decode(trunc(salary/5000),0,0.05,
                                         1,0.03,
                                         2,0.02,0.01))

方式三：
declare
  cursor salary_cursor is select salary,employee_id from employees;
  temp number(4,2);
begin
  for c in salary_cursor loop
      if c.salary<=5000
      then temp:=0.05;
      elsif c.salary<=10000
      then temp:=0.03;
      elsif c.salary<=15000
      then temp:=0.02;
      else temp:=0.01;
      end if;

      update employees set salary=salary*(1+temp) where employee_id=c.employee_id;
  end loop;
end;

83. 用带游标的参数进行80号部门员工工资的调整
注意："=>"是赋值符号
declare
  cursor salary_cursor(dep_id number) is 
  select salary,employee_id from employees
  where department_id=dep_id;
  temp number(4,2);
begin
  for c in salary_cursor(dep_id => 80) loop
      if c.salary<=5000
      then temp:=0.05;
      elsif c.salary<=10000
      then temp:=0.03;
      elsif c.salary<=15000
      then temp:=0.02;
      else temp:=0.01;
      end if;

      update employees set salary=salary*(1+temp) where employee_id=c.employee_id;
  end loop;
end;

84.隐式游标: 更新指定员工 salary(涨工资10)，如果该员工没有找到，则打印”查无此人” 信息
begin
         update employees set salary = salary + 10 where employee_id = 1001;

         if sql%notfound then
            dbms_output.put_line('查无此人!');
         end if;
end;

==============异常处理
预定义异常，即mysql已经定义好的异常
declare

  v_sal employees.salary%type;
begin
  select salary into v_sal
  from employees
  where employee_id >100;

  dbms_output.put_line(v_sal);

exception
  when Too_many_rows then dbms_output.put_line('输出的行数太多了');
end;

非预定义异常，即mysql未定义的异常，需要用户自定义
declare

  v_sal employees.salary%type;
  --声明一个异常
  delete_mgr_excep exception;
  --把自定义的异常和oracle的错误关联起来
  PRAGMA EXCEPTION_INIT(delete_mgr_excep,-2292);

  --声明一个异常
  too_high_sal exception;
begin

  select salary into v_sal
  from employees
  where employee_id =100;

  if v_sal > 1000 then
     raise too_high_sal;
  end if;

  delete from employees
  where employee_id = 100;

  dbms_output.put_line(v_sal);

exception
  when Too_many_rows then dbms_output.put_line('输出的行数太多了');
  when delete_mgr_excep then dbms_output.put_line('Manager不能直接被删除');
  --处理异常
  when too_high_sal then dbms_output.put_line('工资过高了');
end;

==============函数
85. 函数的 helloworld: 返回一个 "helloworld" 的字符串
create or replace function hello_func
return varchar2
is
begin
  return 'HelloWord!';
end;

执行函数：
select hello_func() from dual;
或
begin
  dbms_output.put_line(hello_func());
end;

86. 返回一个"helloworld: atguigu"的字符串，其中atguigu 由执行函数时输入。
create or replace function hello_func(input_s varchar2)
return varchar2
is
begin
  return 'HelloWord '||input_s;
end;

执行函数：
select hello_func('我是输入参数') from dual;
或者
begin
 dbms_output.put_line('我是输入参数');
end;

87.创建一个存储函数，返回当前的系统时间
create or replace function define_variable
return date
is
v_date date;
begin
  v_date:=sysdate;
  return v_date;
end;

执行函数
select define_variable from dual;
或者
begin
  dbms_output.put_line(define_variable);
end;

88. 定义带参数的函数: 两个数相加
create or replace function addSum(a number,b number)
return number
is
 v_sum number;
begin
  v_sum:=a+b;
  return v_sum;
end;

执行函数
select addSum(3,4) from dual;

89. 定义一个函数: 获取给定部门的工资总和, 要求:部门号定义为参数, 工资总额定义为返回值.
create or replace function sumSalary(dept_id number)
return number
is
 cursor c_salary is select salary from employees
 where department_id=dept_id;
 sum_salary number(10):=0;
begin
  for c in c_salary loop
      sum_salary:=sum_salary+c.salary;
  end loop;
  return sum_salary;
end;

执行函数：
select sumSalary(90) from dual;

90. 关于 OUT 型的参数: 因为函数只能有一个返回值, PL/SQL 程序可以通过 OUT 型的参数实现有多个返回值

要求: 定义一个函数: 获取给定部门的工资总和 和 该部门的员工总数(定义为 OUT 类型的参数).
要求: 部门号定义为参数, 工资总额定义为返回值.

create or replace function sumSalary(dept_id number,v_total out number)
return number
is
 cursor c_salary is select salary from employees
 where department_id=dept_id;
 sum_salary number(10):=0;
begin
  v_total:=0;
  for c in c_salary loop
      sum_salary:=sum_salary+c.salary;
      v_total:=v_total+1;
  end loop;
  return sum_salary;
end;

执行函数
declare
  v_total number(3):=0;
begin
  dbms_output.put_line(sumSalary(80,v_total));
  dbms_output.put_line('v_total'||v_total);
end;


==============存储函数
91. 定义一个存储过程: 获取给定部门的工资总和(通过 out 参数), 要求:部门号和工资总额定义为参数
create or replace procedure sumSalary_proc(dept_id number,v_salary out number)
is
 cursor c_salary is select salary 
 from employees where department_id=dept_id;
begin
  v_salary:=0;
 for c in c_salary loop
     v_salary:=v_salary+c.salary;
 end loop;
 dbms_output.put_line('v_salary '||v_salary);
end;

执行函数
declare
  v_dept_id number(3):=80;
  v_salary number(10):=0;
begin
  sumSalary_proc(v_dept_id,v_salary);
end;

92. 自定义一个存储过程完成以下操作: 
对给定部门(作为输入参数)的员工进行加薪操作, 若其到公司的时间在 (? , 95) 期间,    为其加薪 %5
                                                               [95 , 98)             %3       
                                                               [98, ?)               %1
得到以下返回结果: 为此次加薪公司每月需要额外付出多少成本(定义一个 OUT 型的输出参数).

create or replace procedure sumSalary_dept_id_proc(dept_id number,v_salary out number)
is
       cursor c_info is select * from employees where department_id=dept_id;
       temp number(4,2);
       tempDate varchar2(2);
begin
  v_salary:=0;
  for c in c_info loop
      tempDate:=to_char(c.hire_date,'yy');
      if tempDate<95
      then temp:=0.05;
      elsif tempDate<98
      then temp:=0.03;
      else
        temp:=0.01;
      end if;  
      update employees set salary=salary*(1+temp) where employee_id=c.employee_id;
  end loop;
end;

执行函数：
declare
 v_salary number(10):=0;
begin
  sumSalary_dept_id_proc(80,v_salary);
end;

==============触发器
注意：要想使dbms_output.put_line有效，一定要先执行 
set serveroutput on

触发器实例
create or replace trigger emp_tri
after
update on dept
begin
   dbms_output.put_line('我更新了...');
end;

编写一个触发器, 在对 dept 记录进行删除的时候, 在 dept_bak 表中备份对应的记录
第一：
创建dept_bak的空表
create table dept_bak as select * from dept1 where 1=2;

第二：
创建触发器
注意：old和new不能用在表级触发器
create or replace trigger copy_dept1_bak
before 
delete on dept1
for each row
begin
       insert into dept1_bak values(:old.id,:old.name);
end;