Orcale数据库分支语句，存储过程，存储函数

--=====================================pl/sql
/*
PLSQL定义：在数据库服务器上保存的大段可执行方法，供其他开发人员进行调用
PLSQL语法：
[declare]  --定义部分：变量，引用变量，记录型变量，异常
begin
[exception]  --需要捕获异常时写上
end
*/

--定义number变量，定义PI常量，定义记录型变量，定义引用型变量
declare
  i number := 1;                --定义变量
  pjob varchar2(50);            --定义字符
  PI constant number := 3.14;   --定义常量
  pemp emp%rowtype;             --定义记录型变量
  pname emp.ename%type;         --定义引用型变量
begin
  select * into pemp from emp where empno = 7499;  --into给记录型变量赋值
  dbms_output.put_line('员工编号：'||pemp.empno || ',员工姓名：'||pemp.ename);
  dbms_output.put_line(i);
  --PI := PI + 1;
  dbms_output.put_line(PI);
  select ename into pname from emp where empno = 7499;
  select job into pjob from emp where empno = 7499;
  dbms_output.put_line(pname);
  dbms_output.put_line(pjob);
end;

select * from emp for update;

--=======================================if分支
/*
if判断分支语法：
begin
  if 判断条件 then
  elsif 判断条件 then
  else
  end if; 
end;
*/
--从控制台输入一个数字，如果数字是1，则输出我是1
declare
  age number := &age;
begin
  if age = 1 then
    dbms_output.put_line('我是1');
  else
    dbms_output.put_line('我不是1');
  end if;
end;

--如果输入的年龄在18岁以下，输出未成年人，18~40：成年人，40以上 老年人
declare
  age number := &age;
begin
  if age < 18 then
    dbms_output.put_line('未成年人');
  elsif age >=18 and age <= 40 then
    dbms_output.put_line('中年人');
  else
    dbms_output.put_line('老年人');
  end if;
end;

--===========================================loop 循环
/*
loop循环
语法1：
begin
  while 循环条件 loop
  end loop;
end;
语法2：(常用)
begin
  loop
    exit when 退出条件
  end loop;
end;
语法3:
begin
  for i in 1..10 loop
  end loop;
end;
                                          
*/
--语法1实现在控制台输出1~10
declare
  i number := 1;
begin
  while i <= 10 loop
    dbms_output.put_line(i);
    i := i+1;
  end loop;
end;

--语法2实现在控制台输出1~10(掌握)
declare
 i number := 1;
begin
  loop
    exit when i > 10;
    dbms_output.put_line(i);
  end loop;
end;

--语法3实现在控制台输出1~10
begin
  for i in 1..10 loop
    dbms_output.put_line(i);
  end loop;
end;

--====================================================光标（游标）
/*
光标:指定记录集，可抓取记录集中的记录
语法：cursor 游标名 is select查询语句
   
提取游标：
open 游标名;
  loop
     fetch 游标名 into 记录型变量
     exit when 游标名%notfound;
  end loop;
close 游标名;
*/
--使用光标输出emp表中7369的员工信息

declare
  cursor emp_cur is select * from emp where empno = 7499;
  pemp emp%rowtype;
begin
  open emp_cur;           --打开游标
      loop
         fetch emp_cur into pemp;     --抓取记录
         exit when emp_cur%notfound;  --notfound找不到记录就退出loop循环
         dbms_output.put_line('员工名称：'||pemp.ename);
      end loop;                       
  close emp_cur;          --关闭游标            
end;

--输出指定部门的员工信息
declare
 cursor emp_cur is select * from emp where deptno = 10;
 pemp emp%rowtype;
begin
  open emp_cur;
     loop
      fetch emp_cur into pemp;
      exit when emp_cur%notfound;
      dbms_output.put_line('员工编号'||pemp.empno||'员工姓名'||pemp.ename);
     end loop;
  close emp_cur;
end;

--给部门编号为10 的员工涨工资
declare
 cursor emp_cur(dno number) is select * from emp where deptno = dno;
 pemp emp%rowtype;
begin
  open emp_cur(10);    
    loop
      fetch emp_cur into pemp;
      exit when emp_cur%notfound;
      update emp set sal = sal + 1000 where empno = pemp.empno;
    end loop;
    commit;
  close emp_cur;
end;

select * from emp where deptno = 10;
--=====================================例外（异常） 
--系统异常，被0除的zero_divide异常
declare
  i number := 1;
begin
  i := i / 0;
exception
  when zero_divide then
    dbms_output.put_line('分母不能为0');
end;

--系统异常，设置错误的value_error异常
declare
 pname varchar2(50);
begin
  pname := 1234;
  dbms_output.put_line(pname);
exception
  when value_error then
    dbms_output.put_line('赋值错误');
end;

--系统异常，都可以通过others异常捕获
declare
 pname number;
begin
  pname := 'test';
  dbms_output.put_line(pname);
exception
  when others then
    dbms_output.put_line('赋值错误2');
  
end;

--自定义异常，查询部门编号为40员工（自定义exception）
declare
  cursor emp_cur is select * from emp where deptno = 40;
  no_date exception;
  pemp emp%rowtype;
begin
  open emp_cur;
    fetch emp_cur into pemp;
    if emp_cur%notfound then
      raise no_date;
    end if;
  close emp_cur;
exception
  when no_date then
    dbms_output.put_line('没有找到数据');
end;

--==============================================存储过程（过程）
/*
定义：保存一段可执行的sql语句，方便开发调用过程名
语法：
create [or replace] procedure 过程名(参数名  in|out 类型)
as
begin
end;
*/

--声明pro_add_sal存储过程,作用是给指定员工涨1000工资,并打印出涨前和涨后工资
create or replace procedure pro_add_sal(pno in number)
as
 totalSal number;
begin
  select sal into totalSal from emp where empno = pno; --查询工资并赋值给totalSal
  dbms_output.put_line(totalSal);
  update emp set sal = sal + 1000 where empno = pno;
  dbms_output.put_line(totalSal + 1000);
  commit;
end;

begin
  pro_add_sal(7499);
end;

select * from emp where empno = 7499;
--================================================存储函数
/*
定义：保存一段可执行的sql语句，方便开发调用过程名
语法：
create [or replace] function 方法名(参数 in|out 类型) return 参数类型
as
 定义变量名  类型要和return返回类型一致
begin
  return 变量名;
end;
*/

--声明pro_emp_totalsal存储过程,查询指定员工的年薪
create or replace procedure pro_emp_totalsal(pno number,totalsal out number)
as
begin
  select (sal*12)+nvl(comm,0) into totalsal from emp where empno = pno;
end;

declare
  total number;
begin
  pro_emp_totalsal(7499,total);
  dbms_output.put_line(total);
end;

--声明fun_emp_totalsal存储函数,查询指定员工的年薪
create or replace function fun_emp_totalsal(pno number) return number--in 可以忽略 out 不能忽略
as
 totalsal number; --和return类型一致
begin
  select (sal*12)+nvl(comm,0) into totalsal from emp where empno = pno;
  return totalsal;
end;

declare
  totalsal number;
begin
  totalsal := fun_emp_totalsal(7499);
  dbms_output.put_line(totalsal);
end;

--声明fun_emp_dname存储函数,根据部门编号查询出部门名称
create or replace function fun_emp_dname(dno number) return emp.ename%type
as
  dname emp.ename%type;
begin
  select dname into dname from dept where deptno = dno;
  return dname;
end;

declare
 dname varchar2(50);
begin
  dname := fun_emp_dname(10);
  dbms_output.put_line(dname);
end;

select * from dept;

--在select 调用存储函数
select ename,fun_emp_dname(deptno) from emp;

/*
存储过程与存储函数的区别：
1.定义的语法不一样procedure,function
2.function有返回值
3.function可以在select中进行调用
4.存储过程可以通过out类型来返回参数
*/

---存储过程根据部门编号返回员工
create or replace procedure pro_emp_list(dno number,emplist out sys_refcursor)
as
begin
  open emplist for select * from emp where deptno = dno;
end;

declare
 emplist  sys_refcursor;
 pemp emp%rowtype;
begin
  pro_emp_list(10,emplist);
    loop
       fetch emplist into pemp;
         exit when emplist%notfound;
     dbms_output.put_line(pemp.ename);
    end loop;
  close emplist;
end;

--=======================================使用jdbc访问oracle对象（掌握）
--导入Oracle10g驱动包  ojdbc14

select * from emp where empno = 7499;
--==========================================触发器
/*
触发器：满足一定设定的触发条件，立即执行

语法：create [or replace] trigger 触发器名
before|after
insert|update|delete
on 表名
for each row  --:new,:old
begin
end;

*/

--添加一个员工后打印一句话“一个新员工添加成功”
create or replace trigger tri_add
after
insert
on emp
begin
  dbms_output.put_line('一个新员工添加成功');
end;

insert into emp (ename) values ('zbz');
commit;

--不能在休息时间办理入职
/*
raise_application_error(参数1，参数2);
参数1：错误代码 在 -20001~  -20999‘
参数2：提示错误信息
*/

--不能给员工降薪
create or replace trigger tri_emp_sal
before
update
on emp
for each row
begin
  if :new.sal < :old.sal then
    raise_application_error(-20001,'不能降薪，否则删代码'); 
  end if;
end;

select * from emp where empno = 7499;
update emp set sal = sal + 1000 where empno = 7499;
commit;

--触发器应用（通过序列在插入数据的时候，将ID用序列赋值）DDH_20180110_0002

create or replace trigger tri_emp_insert
before
insert
on emp
for each row
begin
  select emp_seq.nextval into :new.empno from dual;
end;
select * from emp;