/*
  PL/SQL : Oracle内部语言
  SQLServer的是:TSQL
  Procedural Language/SQL 是oracle在标准的SQL语言上的扩展,PL/SQL不仅允许嵌入SQL语言,还可以
  定义变量和常量,允许使用条件语句和循环语句,允许使用例外处理各种错误,这样使得它的功能变得
  更加的强大.

  优点:提高应用程序的运行性能,模块化的设计思想,减少网络传输量,提高安全性

  SQL语言:有两种SQL1992/SQL1996  没有循环控制结构

  PL/SQL块由三个部分组成:定义部分,执行部分,例外处理部分
  declare
       定义部分 -- 定义常量,变量,游标,例外,复杂数据类型
  begin
       执行部分 -- 要执行的PL/SQL语句和SQL语句
  exception
       例外部分 -- 处理运行的各种错误
  end;

  调用过程
  exec 过程名
  call 过程名                
*/

--匿名块


--简单打印一句话

--打开显示
set serveroutput on ;

begin
  dbms_output.put_line('Hello PL/SQL!');
end ;
/


/*
    变量声明的规则
    1 变量名不能够使用保留字
    2 第一个字符必须是字母
    3 变量名最多包含30个字符
    4 不要与数据库的表或者列同名
    5 每一行只能声明一个变量

    常用的变量类型
    1 binary_integer : 整数,主要用来计数而不是用来表示字段类型
    2 number: 数字字符
    3 char : 定长字符串
    4 varchar2 : 变长字符串
    5 data : 日期
    6 long : 长字符串最长2GB
    7 boolean : 布尔类型,可以取值true|false和null值

*/


--声明变量
declare v_name varchar2(20) ;

declare
  v_temp number(1);
  v_count binary_integer :=0;
  v_sal number(7,2) :=4000.00;
  v_date date :=sysdate ;
  v_pi constant number(3,2) :=3.14;
  v_valid boolean :=false;
  v_name varchar2(20) not null :='hello plsql' ;
begin 
  dbms_output.put_line('v_temp value :'||v_temp);
end;

--根据表中字段类型声明变量类型的
declare 
  v_empno number(4);
  v_empno2 emp.empno%type;
  v_empno3 v_empno2%type;
begin
  dbms_output.put_line('test');
end;

/*
  复杂变量
  Table变量类型 : 集合  (表示集合时要自己定义的一个新的数据类型)
  Record变量类型 : 类 (代表实体,一条记录)
*/
--定义Table变量类型
--Table 变量类型(数组)
declare
  type type_newname is table of emp.empno%type index by binary_integer; --声明一个数组类型 index by binary_integer 指系统创建一个主键索引，以引用记录表变量中的特定的行。
    v_empnos type_newname ;  --用数组类型定义变量
begin
  v_empnos(0) :=7369;
  v_empnos(2) :=7839;
  v_empnos(-1):=9999;
  dbms_output.put_line(v_empnos(-1));
end;

--Record变量类型
declare
  type type_record_dept is record
    (
      deptno dept.deptno%type,
      dname dept.dname%type,
      loc dept.loc%type
    );
  v_temp type_record_dept ;
begin 
  v_temp.deptno :=50 ;
  v_temp.dname  :='aaa';
  v_temp.loc    :='bj';
  dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;

--使用%rowtype 声明record变量
declare
  v_temp dept%rowtype;   --直接用一张表的行来定义record,表结构变化时record也在变
begin
  v_temp.deptno :=50;
  v_temp.dname  :='aaa';
  v_temp.loc    :='bj';
  dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);
end ;

/*
    PL/SQL中的SQL语句
    select 语句返回有且只有一条记录
*/
--select语句
declare
  v_ename emp.ename%type;
  v_sal emp.sal%type;
begin
  select ename,sal into v_ename,v_sal from emp where empno =7369;
  dbms_output.put_line(v_ename || ' '||v_sal);
end;

declare
  v_temp emp%rowtype;
begin
  select * into v_emp from emp where empno=7369;
  dbms_output.put_line(v_temp.ename);
end;


--insert 语句
declare
  v_deptno dept.deptno%type :=10;
  v_dname dept.dname%type :='aaa';
  v_loc dept.loc%type :='beijing' ;
begin
  insert into dept2 values(v_deptno,v_dname,v_loc);
  commit;
end;

--update语句
declare
  v_deptno emp2.deptno%type :=10 ;
  v_count number;
begin
  update emp2 set sal = sal/2 where deptno = v_deptno ;
  --select deptno into v_deptno from emp2 where empno = 7369;
  --select count(*) into v_count from emp2 ;
  dbms_output.put_line(sql%rowcount||'条记被影响');
  commit ;
end;

--DDL语句
begin
  execute immediate 'create table t_temp(nnn varchar2(20) default "aaa")' ;
end ;

--语句块
declare
  v_name varchar2(20);
begin
  v_name := 'jack' ;
  dbms_output.put_line(v_name);
end;

--带异常捕获的语句块
declare
  v_num number :=0 ;
begin
  v_num :=2/v_num ;
  dbms_output.put_line(v_num);
exception
  when others then
    dbms_output.put_line('error');
end;

--分支语句
--if 取出7369的薪水，如果<1200，则输出'low'，如果<2000则输出'middle'，否则'high'
declare 
  v_sal emp.sal%type;
begin
  select sal into v_sal from emp where empno=7369;
  if(v_sal<1200) then
    dbms_output.put_line('low');
  elsif(v_sal<2000) then
    dbms_output.put_line('middle');
  else 
    dbms_output.put_line('high');
  end if;
end;

--循环  (相当于do...while)
declare
  i binary_integer :=1;
begin
  loop 
    dbms_output.put_line(i);
      i := i+1 ;
      exit when(i>=11);
  end loop;
end;

declare
  j binary_integer :=1;
begin
  while j<11 loop
    dbms_output.put_line(j);
      j := j+1 ;
  end loop ;
end;

begin
  for k in 1..10 loop 
    dbms_output.put_line(k);
  end loop;
  for k in reverse 1..10 loop 
    dbms_output.put_line(k);
  end loop;
end;

--错误处理
declare
  v_temp number(4);
begin
  select empno into v_temp from emp where deptno =10 ;
exception
  when too_many_rows then
    dbms_output.put_line('太多记录了');
  when others then
    dbms_output.put_line('error');
end;

declare
  v_temp number(4);
begin
  select empno into v_temp from emp where empno=2222;
exception
  when no_data_found then
    dbms_output.put_line('没数据');
end;

--错误记录
create table errorlog
(
  id number primary key,
  errcode number,
  errmsg varchar2(1024),
  errdate date
);

create sequence seq_errorlog_id start with 1 increment by 1;

declare
  v_deptno dept.deptno%type :=10 ;
  v_errcode number ;
  v_errmsg varchar2(1024) ;
begin
  delete from dept where deptno=v_deptno;
  commit;
exception
  when others then
    rollback;
    v_errcode :=SQLCODE;
    v_errmsg  :=SQLERRM;
    insert into errorlog values(seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate);
    commit;
end;

select * from errorlog;


触发器

/*
  触发器 trigger
  触发器必须依附于表才能起作用
*/

--创建日志表 emp_log,用于记录对表emp的所有操作日志
create table emp_log
(
  uname varchar2(20),
  action varchar2(10),
  atime date
);

--创建触发器
/*
  触发条件:insert|delete|update 对某张表做插入|删除|更新的操作
  触发时间:after|before 操作之后|操作之前
  触发行:each row 操作影响一行触发一次，不写表求一次操作触发一次
*/
create or replace trigger trig
  after insert or delete or update on emp for each row
begin
  if inserting then
    insert into emp_log(uname,action,atime) values(USER,'insert',sysdate);
  elsif updating then
    insert into emp_log(uname,action,atime) values(USER,'update',sysdate);
  elsif deleting then
    insert into emp_log(uname,action,atime) values(USER,'delete',sysdate);
  end if ;
end;
/

--触发
update emp set sal=sal*2 where deptno=30;
delete from emp where empno = 7369 ;


--查看日志表
select * from emp_log;

--? : 有外键关系时,主表中的主键存在外表的引用关系,因此不能随意更新,但可以用触发器解决这个问题
    --如: update dept set deptno=99 where deptno=10

--删除触发器
drop trigger trig;

create or replace trigger trig
  after update on dept for each row
begin
  update emp set deptno = :NEW.deptno where deptno = :OLD.deptno ;
end;
/
/*
  通常一条update语句会产生新旧两个状态 :NEW代表新状态 :OLD代表旧状态
*/
update dept set deptno=99 where deptno=10;