oracle常用sql三连击——03

--------------------oracle第三天------------------
PLSQL
是对sql语言的过程化扩展语言

-----------plsql语法(中括号中的可有可无，begin和end成对出现。）
[declare ]     --声明

begin
  
[exception]      --异常
  
end;

---可声明：  变量 常量   异常  游标
declare
---java中  private String  v_name="老王";（java是面向对象的，所以有私有公有一说，但面向过程的不存在这种区别。）
v_name varchar2(30):='老王';  -- := 在plsql中相当于java中的=    plsql中的= 相当于java中==
v_sex constant number(1):=1;    --常量在声明时一定要给值（加constant便是常量。相当于java中的final。）
v_sal emp.sal%type:=980;     --引用型变量emp.sal%type   相当于 number(7,2)（emp表中sal的类型）
    --（比方要将emp表中的sal的值赋值给我的v_sal，这样就需要字段的类型和长度满足，但我又不知道emp表中的sal的类型和长度，便可用引用型变量。）
v_row emp%rowtype;           --记录型变量（记录emp表的一整条数据。此处记录是名词。）
begin
  v_name :='老李';
  select sal,ename into v_sal,v_name from emp where empno=7788;

   select * into v_row from emp where empno=7788;
  --v_sex:=0;（v_sex是常量，不能再重新赋值。重新赋值报错。）
 dbms_output.put_line(v_name);  --dbms_output.put_line()是打印。声明v_name是老王，打印出来是老王。但begin里赋值操作后再打印便是老李了。
 dbms_output.put_line(v_sal); 
 dbms_output.put_line(v_row.empno||v_row.ename||v_row.job);
end;

  select sal   from emp where empno=7788;
  
  
-----------------------if判断----
--语法1、
if 条件 then 逻辑处理
end if;
--语法2,、
if 条件 then 逻辑处理1
else 逻辑处理2
end if;
--语法3、
if 条件1 then 逻辑处理1
elsif  条件2 then 逻辑处理2
elsif  条件3 then 逻辑处理3
................
else   逻辑处理N
end if;  

--判断年龄 如果是18以下 输出‘未成年人’ 18---60 成年人 60以上 老年人
declare
v_age number(3):=#    --&这个符号可以弹出一个窗口，哪怕只写个&a都可以弹出。可在弹出的窗口里赋值。后面的num是随便写的，写aabc都行。
begin
if v_age < 18 then 
  dbms_output.put_line('未成年人');  
elsif v_age>=18 and v_age<=60  then 
  dbms_output.put_line('成年人'); 
else   dbms_output.put_line('老年人'); 
end if;
end;    
-----------------------循环----
-语法1、 无条件循环  有条件退出
loop

end loop;
--
declare
 v_num number(9):=1;
begin
  loop
   --if v_num>100 then exit;
   --end if;
   exit when v_num>100;--这句等于上两行if的简写。
   dbms_output.put_line(v_num);
   v_num:=v_num+1;    --没有++或者--的概念。
  end loop;
end;
--语法2、 有条件循环
while 条件
  loop
  end loop;
------
declare 
 v_num number(8):=1;
begin
  while  v_num<=200    --此处没有分号。while loop end loop是一整句话。
  loop
     dbms_output.put_line(v_num);
      v_num:=v_num+1;
  end loop;
end;  
--语法3 for 循环  
for   变量 in 起始值..终止值
  loop
    
  
  end loop;
---
【declare 
 v_num number(8):=1;】--（中括号里的可有可无。）
begin
  for   v_num in 1..105
  loop
   dbms_output.put_line(v_num); --输出的便已经是1到105了，v_num会依次加。
  end loop;
end;

--------------------------------------------

--打印出所有员工信息（上面的plsql还做不到，最多是接收一条记录的变量。）
---- 游标（程序员习惯叫法） 光标（Oracle内部称谓） cursor  里面放的是多条记录  相当于java中的 ResultSet. rs  while(rs.next){}
cursor   游标名称  is sql查询语句    --游标的声明方式。

declare
cursor   c_emp  is select * from emp;
v_row emp%rowtype;
begin
 open c_emp;    --打开游标
 loop  
  fetch c_emp into v_row;     --获取每个游标中的值
  exit when c_emp%notfound;   --当循环游标时找不到记录后就退出循环    
    --(当上面两行两句顺序反了时结果最后一条会重复一次，也就是会多走一次。因为c_emp%notfound的指针指向的是前面的fetch后的c_emp，而前面的是有值的。
    --而即使多出来的一次循环fetch没有取到值，依然能够打印出结果来。因为plsql的机制便是取最后一条有值的内容。）
  dbms_output.put_line(v_row.empno||v_row.ename);
 end loop;
 close c_emp;   --关闭游标
end;
-----给指定部门的所有员工涨1000元工资
cursor   游标名称(参数1 数据类型,参数2 数据类型。。。。。。)  is sql查询语句    --此处数据类型不管是什么类型后面都不能写长度(应该是用了默认长度）。

--游标的使用
open 游标(参数值)；
loop
  fetch  游标 into 变量
  exit when 条件;
  逻辑处理
end loop;
close 游标;

--plsq实现
declare 
cursor   c_empsal(dno number)  is select * from emp where deptno=dno;
v_row emp%rowtype; 
begin
  open c_empsal(10);-- 游标中的sql语句在open时才会被执行（declare声明时是没有执行的。）
loop
  fetch c_empsal into v_row;
  exit when c_empsal%notfound;
  update emp set sal=sal+1000 where empno=v_row.empno;
end loop;
close c_empsal;
end;

----------------异常 例外（Oracle内部叫例外）  exception--------
-- 预定义异常
declare
v_age number(3);
excp_age exception;  --自定义异常
begin
  v_age:=150;
  if v_age>100 then
   raise excp_age;  --raise 抛异常 相当于java中的 throw --（正常不是异常的，自定义成一个异常，然后抛出他，再在下面捕获。）
  end if;   
--v_age:=1/0;  
dbms_output.put_line(v_age);
exception
  when excp_age then 
    raise_application_error(-20001,'年龄太大不适合学java');
  --oracle给我们提供了一个错误代码区间 -20001---- -29999
   --dbms_output.put_line('年龄太大不适合学java');
 when VALUE_ERROR then
    dbms_output.put_line('长度不够');
 when zero_divide then --（0为除数的异常）
   v_age:=0; 
dbms_output.put_line('除数不能为0，我给了个默认值：'||v_age);
 when others then
   dbms_output.put_line('未知异常');
end;

-------------存储过程  procedure   ****重点（在这上面的那些plsql语句块没有名字。）（在工具pl/sql里像视图一样左边有对应的菜单，如果语法有错误的话上面也会有个小叉号）
定义：是一段被命名化的plsql语句块，是预编译到了oracle数据库中
语法：
create  [or replace]  procedure 存储过程的名称(参数N [in]/out 数据类型)--（参数也不加长度）(默认就是in，in是输入，out是输出。）（or replace最好写上。）
is | as   --代替了declare（is或者as都行）（是不能省略的，即使没声明值。）

begin
  plsql语句
end;

--打印出某个员工的年薪 （打印一般是输入）
create   or replace   procedure proc_emp_sal(eno  emp.empno%type)
is 
year_sal number(7,2); --（此处要有分号）
begin
  select sal*12+nvl(comm,0) into year_sal  from emp where empno=eno;
  dbms_output.put_line(year_sal);
end;

--如何使用存储过程
方式1、
call proc_emp_sal(7788);
方式2、
begin
  proc_emp_sal(7369);
end;

---返回指定员工的年薪（返回一般是输出）
create or replace procedure proc_emp_sal(eno     emp.empno%type,
                                         yearsal out number)
is
begin
  select sal * 12 + nvl(comm, 0) into yearsal from emp where empno = eno;
end;
--使用带out参数的存储过程
declare
v_sal number;
begin
 proc_emp_sal(7788,v_sal); 
 dbms_output.put_line(v_sal);
end;
----------存储函数-----  function ****重点
语法：
create  [or replace]  function 存储函数的名称(参数N [in]/out 数据类型)
return 数据类型
is | as   --代替了declare
begin
  plsql体；
  return 具体值;
end;
---返回指定员工的年薪
create  or replace  function func_emp_sal(eno number)
return number
is
year_sal number;
begin
  select sal*12+nvl(comm,0) into year_sal from emp where empno=eno;
  return year_sal;
end;
--使用存储函数（不能用call，只能用plsql的通用。）
begin
  dbms_output.put_line(func_emp_sal(7788));
end;

---小结 存储函数和存储过程的区别
1、语法上有区别
2、使用方式：项目之间多用存储过程来相互调用数据
（即两个不同的项目之间需要调用对方的数据，又不能让对方知道表结构及数据直接操作，可以提供给对方一个存储过程，告诉对方参数及返回值便可使用。）
（两个项目之间也可调用存储函数，但不这样，太low！直接存储过程就可以。）
3、存储函数一般会被存储过程或sql语句调用，存储过程不能被sql语句调用（存储过程能被存储函数调用。）
select ename, sal*12+nvl(comm,0) yearsal from emp;
--sql中调用存储函数的例子
select ename, func_emp_sal(empno) yearsal from emp;    --（存储过程或存储函数比普通sql效率高！这句例子效率比上面那个高。因为是预编译的。）

--------------jdbc调用存储过程和存储函数请参考java工程----  *****重点

---jdbc调用游标用：下面是创建游标.也是个存储过程.
create or replace procedure proc_emp_dept(dno in number,c_emp out sys_refcursor)    --(sys_refcursor是系统引用型游标。）
is
begin
  open c_emp for select * from emp where deptno=dno;
end;

--触发器   trigger   ****重点

--触发器的作用（跟约束很像。触发器能干的约束不一定能干，约束能干的触发器都能干。）
1、可以安全性检查数据
2、可以保证数据的完整性
3、可以跟踪数据
4、备份数据
--语法
create [or replace] trigger 触发器名称
before|after    --（看触发是要在sql操作之前还是之后。）
delete | update [of 列名] | insert -- delete  or  update or insert --（如果要针对所有操作都触发该触发器，则用or连起来。）
on 表名
[for each row]    --（可以有可以无，但有一种情况必须有！）

[declare]

begin
  
end;
--- 当插入一条员工数据后 打印出‘一条员工数据已插入’
create or replace trigger tri_insert_emp
after
insert
on emp
--for each row
begin
  dbms_output.put_line('一条员工数据已插入');
end;

insert into emp(empno,ename,deptno) values(1111,'TOM',10);

-- 今天‘2016-11-19’系统维护，不能操作员工数据
create or replace trigger tri_exc_emp
before
insert or update or delete
on emp
--for each row
declare
 v_date varchar2(20);
begin
  select to_char(sysdate,'yyyy-mm-dd') into v_date from dual;
  if v_date='2016-11-19' then 
    raise_application_error(-20002,'今天系统维护，不能操作员工数据');
  end if;
end;
--验证：
insert into emp(empno,ename,deptno) values(2222,'JERRY',20);
delete from emp where empno=1111;
update emp set ename='JJJJ' where empno=1111;

--数据备份 当在修改员工工资时把原工资和现在的工资都放到一张备份表中
--创建备份表
create table emp_sal(
eid number(8) primary key,
empno number(8),--员工编号
sal0 number(7,2),---原工资
sal1 number(7,2),--修改后的工资
createtime date   --修改时间
)
--给备份表创建序列（主键一般都从序列取，自增的。）
create sequence seq_emp_sal;
--行级触发器  加上for each row 就是语句级触发器
create or replace trigger tri_emp_sal
after
update of sal
on emp
for each row   --语句级触发器
declare
begin
  insert into emp_sal values(seq_emp_sal.nextval,:new.empno,:old.sal,:new.sal,sysdate );
end;
--伪记录
--       :new   :old    --（Oracle提供的两个对象，记录操作之前和操作之后的数据。）（必须是for each row 语句级触发器才能用。)
update  
delete    null   
insert           null    --（delete的没有新的，insert的没有旧的。）

--验证
update emp set sal=sal+1000 where empno=7788;
select * from emp;
select * from emp_sal;

--做一个触发器，能正常执行这段sql
insert into emp_sal (empno) values (7788);

--分析：在数据插入之前从序列中取值赋给即将插入的那条数据
create or replace trigger tri_emp_sal_eid
before
insert
on emp_sal
for each row
declare
begin
  if :new.eid is null then  --做判断：如果eid为空那就从序列中取值（如果没有这句则即使eid有值也还会从序列取。实际应用如果是主键：就应该没有这句，否则会主键冲突。）
  select seq_emp_sal.nextval into :new.eid  from dual;   
  end if;
end;
--验证
insert into emp_sal (empno) values (7788);
insert into emp_sal (eid,empno) values (100,7788);
select * from emp_sal;

--以下方式删除数据之后如何还原
delete from emp where empno=1111
select * from emp

--数据恢复代码,重点备用!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    (这个语句中的时间只能是数据被删之前最近的时间，太远的没有。且也必须那时表存在，且表之后没有修改过，比方删除了重新创建，这个新表已经不算了。
    这个是Oracle自带的快照机制，不需要我们做什么操作，该语句可将最近的指定时间的数据快照查出来，赋值给我们命名的emp_bak表，
    然后我们可将emp_bak表中的数据导出为sql，将表名替换为原表名emp，再将数据插回原表。）
    注：！！！只能是删除了数据才可利用快照恢复，表删除或修改了都无效。
create table emp_bak
as
select * from emp as of TIMESTAMP 
to_timestamp('20161119 162000','yyyymmdd hh24miss');

insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (1111, 'TOM', null, null, null, null, null, 10);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMI_TH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 800, 300, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250, 500, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250, 1400, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850, null, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 3450, null, 10);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 4000, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 6000, null, 10);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500, 0, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950, null, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 2300, null, 10);
commit;