oracle PL/SQL基础学习

–if else语句

declare
  v_sal NUMBER(7,2);
BEGIN
  SELECT sal into v_sal from emp
  where ename='SCOTT';
  DBMS_OUTPUT.PUT_LINE('SCOTT工资:'||v_sal);
  if v_sal<1000 then
      dbms_output.PUT_LINE('scott1000');
  else
    if 1000<=v_sal and v_sal<2000 then
      dbms_output.PUT_LINE('scott工资在1000到2000之间');
    else
      dbms_output.PUT_LINE('scott工资高于2000');
    end if;
  end if;
end;

–测试dmbs，在DBMS Output下，点击左上角按钮开启dmms set

serveroutput on
exec dbms_output.put_line('scotts');

–if elsif else 语句

declare
  v_sal number(7,2);
begin
  select sal into v_sal from emp
  where ename='SCOTT';
  dbms_output.put_line('scott的工资是'||v_sal);
  if v_sal<1000 then
    dbms_output.put_line('工资低于1000');
  elsif v_sal>1000 and v_sal<=2000 then
    dbms_output.put_line('工资在1000到2000之间');
  else
    dbms_output.put_line('工资大于2000');
  end if;
end;

–case when

declare
  v_sal number(7,2);
begin
  select sal into v_sal from emp
  where ename='SCOTT';
  dbms_output.put_line('scott`s sal is'||v_sal);
  case 
    when v_sal<1000 then
      dbms_output.put_line('scott sal low than 1000');
    when v_sal>1000 and v_sal<=2000 then
      dbms_output.put_line('scott sal between 1000 and 2000');
    else
      dbms_output.put_line('scott sal height than 2000');
  end case;
end;

–loop循环

declare
  v_i number := 1;
  v_s number := 0;
begin
  loop
    exit when v_i>100;
      v_s := v_s+v_i;
      v_i:=v_i+1;
  end loop;
  dbms_output.put_line(v_s);
end;

–exception

/*
  异常分为预定义异常、非预定义异常、自定义异常
  预定义异常：由oracle定义，异常发生时自动触发,大约20个左右
  非预定义异常：由用户关联oracle提供的异常代码，用PRAGMA EXCEPTION_INIT(异常名，异常代码);
  自定义异常：必须声明，并且用RAISE语句显式引发
*/
/*
  EXCEPTION
    WHEN name1 [OR name2] THEN
     语句1
    WHEN name3 [OR name4] THEN
     语句2
    ...
    WHEN OTHERS THEN
      语句3
  END;
*/

–预定义异常

declare
  v_name emp.ename%type;
begin
  select ename into v_name from emp
  where empno = &emp_no;
    dbms_output.put_line('employee name:'||v_name);
  exception
    when no_data_found then
      dbms_output.put_line('not exist this employee,please input'||v_name);
end;
--select empno from emp where ename='SCOTT';

–非预定义异常

declare
  e_null_error EXCEPTION;
  pragma exception_init(e_null_error,-1400);--由pragma exception_init（）关联
begin
  insert into dept values(null,'demo1','bj1');
exception
  when e_null_error then
    dbms_output.put_line('cannot insert NULL into the table of dept');
end;

–自定义异常

declare
  v_sal number;
  e_sal_error exception;
begin
  select sal into v_sal from emp where empno=&emp_no;
  if v_sal>=2500 then
    dbms_output.put_line('this employee1`s sal is'||v_sal);
    raise e_sal_error;--由raise手动引发,自定义异常不一定是错误，可能是程序员根据编程和调试需要而为特殊情况所定义的
  end if;
exception
  when e_sal_error then
    dbms_output.put_line('this employee`s sal more than 2500');
end;

–过程和函数

/*
  以上为匿名plsql块，缺点是每次执行都需要被重新编译，并且不能被存储在数据库中
  过程与函数式命名的plsql块，被存储在数据库中，可以被其他plsql块使用
  格式：create [or replace] procedure procedure_name(arg1 [IN | OUT ] argtype1,arg2 [IN | OUT ] argtype2...)
          as | is
            声明部分
          BEGIN
            执行部分
          EXCEPTION
            异常部分
          END procedure_name;
*/

create or replace procedure query_emp
  (v_no IN emp.empno%type,
   v_name OUT emp.ename%type,
   v_sal OUT emp.sal%type)
IS
  e_sal_error exception;
begin
  select ename,sal into v_name,v_sal from emp where empno = v_no;
  if v_sal>=2500 then
    DBMS_OUTPUT.PUT_LINE('this employee`s sal is'||v_sal);
    raise e_sal_error;
  end if;
exception
  when no_data_found then
    DBMS_OUTPUT.PUT_LINE('there is not exist this employee'||v_no);
  when e_sal_error then
    DBMS_OUTPUT.PUT_LINE('this employee`s sal more than 2500');
end query_emp;

--  sql/plus中
variable a1 varchar2(16);
variable a2 number;
execute query_temp(7788, :a1, :a2);

--PL/SQL块中
declare
  v_a1 emp.ename%TYPE;
  v_a2 emp.sal%TYPE;
begin
  query_emp(v_name => v_a1,v_sal => v_a2, v_no =>7788);
end;

--返回值：函数有且只有1个返回值，而存储过程可以通过OUT参数返回多个
--调用： 函数可以在查询语句中直接调用，而存储过程必须单独调用
--函数一般用来计算并返回一个计算结果，而存储过程一般是用来完成特定的数据操作

–自定义错误代码及消息文本

--为应用程序提供一种与oracle交互、比较自由的方法（并不一定是错误，可能仅仅为了提示）
--RAISE_APPLICATION_ERROR(ERROR_CODE,MESSAGE[, TRUE | FALSE])
--ERROR_CODE:自己定义的代码 范围为（-20000~-20999之间）
--MESSAGE:消息文本,第三个参数为可选，默认FALSE，如果为false，则用当前message代替错误消息栈中的消息，反之。


create or replace procedure query_comm_if_null
(v_no in emp.empno%TYPE)
is
  v_comm emp.comm%TYPE;
begin
  select comm into v_comm from emp where empno = v_no;
  if v_comm is NULL OR v_comm = 0 then
    RAISE_APPLICATION_ERROR(-20001, 'this employee has not comm');
  end if;
exception
  when NO_DATA_FOUND then
    dbms_output.put_line('there is not exist this employee'||v_no);
end query_comm_if_null;

--调用
declare
  e_comm_null exception;
  pragma exception_init(e_comm_null,-20001);
begin
  dbms_output.put_line('employee num is 5678');
  <<block_1>>
    begin
      query_comm_if_null(5678);
    end block_1;
    dbms_output.put_line('employee num is 7788');
    <<block_2>>
    begin
      query_comm_if_null(7788);
    exception
      when e_comm_null then
        dbms_output.put_line('add comm for this employee');
    end block_2;
end;

–游标

--分为显式游标和隐式游标
--每个用户回话中可以打开多个游标，其数量由数据库初始化参数文件中的OPEN_CURSORS参数定义
/*
一、声明游标
CURSOR cursor_name[(arg1 arg1_type [,arg2 arg2_type]......)]参数只能是输入参数
[RETURN return_datatype]
select_statement;


二、打开游标
OPEN cursor_name[(arg1 arg_datatype[,arg2 arg2_type])......];


三、提取游标
游标打开后，游标指针指向结果集第一行，如果要提取结果集中的数据，就需要提取游标，语法：
FETCH cursor_name INTO {variable_list | record_variable};


该语句执行时，每次返回一个数据行，然后指针自动移动到下一个数据行，当检索到最后一行时候，再执行该语句
操作将失败，并使游标属性%notfound值为true


四、关闭游标
当提取和处理完游标后，应该及时关闭游标，以释放他所占用系统资源：
CLOSE cursor_name;




*/


declare
  type DeptRecord IS RECORD
    (deptno dept.deptno%TYPE,
     dname dept.dname%TYPE,
     loc dept.loc%TYPE
    );
    v_deptrecord DeptRecord;
    v_dept_name dept.dname%TYPE;
    v_dept_loc dept.loc%TYPE;
    CURSOR c1 --声明游标
    IS
      select dname,loc from dept where deptno <= 10;
    Cursor c2(v_dept_no number) --有参数游标
    IS
      select dname,loc from dept where deptno <= v_dept_no;
    CURSOR c3(v_dept_no number) --有返回数据类型游标
      return DeptRecord
    IS
      select deptno,dname,loc from dept where deptno <= v_dept_no;
    CURSOR c4(v_dept_no number)
    IS
      select deptno,dname,loc from dept where deptno <= v_dept_no;
    v_dept_rec c4%ROWTYPE; --基于游标定义记录变量，比DeptRecord方便
begin
  OPEN c1;
  LOOP
    FETCH c1 into v_dept_name,v_dept_loc; --提取游标
    if c1%found then
      dbms_output.put_line('c1:'||v_dept_name||' '||v_dept_loc);
    else
      dbms_output.put_line('c1:'||'process over！！');
      exit;
    end if;
  end loop;
  open c2(20);
  loop
    fetch c2 into v_dept_name,v_dept_loc;
    exit when c2%notfound;
    dbms_output.put_line('c2:'||v_dept_name||' '||v_dept_loc);
  end loop;
  open c3(v_dept_no=>30);
  loop
    fetch c3 into v_deptrecord;
    exit when c3%notfound;
      dbms_output.put_line('c3:'||v_deptrecord.deptno||' '||v_deptrecord.dname);
    end loop;
    open c4(40);
    loop
      fetch c4 into v_dept_rec;
      exit when c4%notfound;
      dbms_output.put_line('c4:'||v_dept_rec.deptno||' '||v_dept_rec.dname);
    end loop;
    close c1;
    close c2;
    close c3;
    close c4;
end;

--游标FOR循环
--FOR index_variable IN cursor_name[(value1[,value2]......)] LOOP
--语句段
--END LOOP


declare
  CURSOR c1(v_dept_no NUMBER DEFAULT 10)
    IS
      SELECT deptno,dname FROM dept WHERE deptno <= v_dept_no;
    BEGIN
      DBMS_OUTPUT.PUT_LINE('arg is 30');
      FOR c1_rec IN c1(30) LOOP
        DBMS_OUTPUT.PUT_LINE('c1:'||c1_rec.deptno||' '||c1_rec.dname);
      END LOOP;
      DBMS_OUTPUT.PUT_LINE('use v_dept_no default arg 10');
      FOR c1_rec IN c1 LOOP
        DBMS_OUTPUT.PUT_LINE('c1:'||c1_rec.deptno||' '||c1_rec.dname);
      END LOOP;
END;

–使用游标更新或删除数据

/*
  如果用显式游标更新或删除数据，则必须要求游标查询语句中使用FOR UPDATE选项，以便在打开游标时
  锁定游标结果集所在数据库表中对应的数据行，语法：
  SELECT column_list FROM table_list FOR UPDATE
  使用FOR UPDATE打开游标之后就可以在UPDATE和DELETE语句中使用WHERE OF 子句，修改或删除游标结果集
  中当前行所对应的数据了，语法：
  WHERE CURRENT OF cursor_name
*/
declare
  v_emp_rec emp%ROWTYPE;
  CURSOR c1
    is
      select * from emp FOR UPDATE;
  begin
    open c1;
    LOOP
      fetch c1 into v_emp_rec;
      exit when c1%notfound;
      if v_emp_rec.deptno>=30 then
        update emp set comm=800 
        where current of c1;
      end if;
    end loop;
    commit;
    close c1;
end;

select * from emp where deptno>=30

–触发器

/*
  1.触发器不接受参数
  2.一个表上最多可以有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个，各个触发器之间不能相互矛盾
  3.在一个表上触发器越多，对在该表上DML操作的性能影响就越大
  4.触发器最大为32KB
  5.在DML触发器中只能使用DML语句（SELECT,INSERT,UPDATE,DELETE）
  6.在系统触发器中只能包含DDL语句（CREATE,ALTER,DROP）
  7.触发器中不能包含事物控制语句（COMMIT,ROLLBACK,SAVEPOINT）
  8.在触发器中调用的任何过程、函数都不能使用事务控制语句
  9.在触发器主体中不能声明任何LONG和BLOB变量
  10.不同触发器（DML触发器、INSTEAD OF触发器、系统触发器）语法格式和作用都有较大区别
*/
--DML触发器
--定义在一个表和简单视图上的触发器，是常用的传统触发器
--语句级触发器
--语法格式：
--CREATE [OR REPLACE] TRIGGER tigger_name
--{BEFORE | AFTER} --触发时机
--{INSERT | UPDATE |DELETE} --触发事件
--[OR {INSERT |DELETE |UPDATE}......]
--ON table_name --制定触发对象，即该触发器被创建在那个表或视图上
--PL/SQL_block | CALL procedure_name; --制定触发器触发时候要执行的pl/sql程序块或使用
--CALL调用的子程序


create or replace trigger tr_emp_time
before insert
    or update
    or delete
  on emp
  begin
    if  (to_char(sysdate,'DAY') in ('星期六','星期日'))
      or (to_char(sysdate,'HH24') not between 8 and 18) then
        RAISE_APPLICATION_ERROR(-20001,'cannot change emp,because the time is not right');
    end if;
end;


update emp set comm=200 where deptno>20;

--行级触发器（可以对列值进行访问，在列明前加OLD.限定词表示变化前的值，
--          加NEW.表示边变化后的值,在PL/SQL块中使用时候还要加上：，
--          when 条件中不用加）
--语法：
/*
  CREATE [OR REPLACE] TRIGGER tigger_name
  {BEFORE | AFTER}
  {INSERT | DELETE | UPDATE [OF column1 [,column2,......]]}
  [OR {INSERT | DELETE |UPDATE [OF column3 [column4,......]]}]
  ON table_name
  FOR EACH ROW --表示该触发器为行级触发器
  [WHEN condition]
  PL/SQL_block | CALL procedure_name;
*/

create or replace trigger tr_emp_sal_comm
before 
  update OF sal,comm
  or delete
ON emp
FOR EACH ROW
WHEN (OLD.job='SALESMAN')
BEGIN
  CASE
    when updating('sal') then
      if :new.comm < :old.comm then
        raise_application_error(-20002,'sale man  sal cannot low');
      end if;
    when updating('comm') then
      if :new.comm < :old.comm then
        raise_application_error(-20003,'sale man`s comm cannot low');
      end if;
    when deleting then
        raise_application_error(-20004,'cannot delete the table of emp');
  end case;
end;

update emp set comm=200 where job = 'SALESMAN'

--INSTEAD OF触发器
/*
定义在复杂视图上的触发器
复杂视图特征：
1.具有集合操作符（UNION,UNION ALL,INTERSECT,NIMUS）
2.具有分组函数（MIN,MAX,SUM,AVG,COUNT等）
3.具有GROUP BY,CONNET BY,START WITH等子句
4.具有DISTINCT关键字
5.具有多表连接查询
*/
--查询触发器
select trigger_name,status from user_triggers where table_name='EMP'
--禁止触发器
ALTER TRIGGER tr_emp_time DISABLE;
--激活触发器
ALTER TRIGGER tr_emp_time ENABLE;
--禁止或激活表上所有触发器
--ALTER TABLE dept DISABLE/ENABLE ALL TRIGGER;
--重新编译触发器
ALTER TRIGGER tr_emp_time COMPILE;
--删除触发器
DROP TRIGGER tr_emp_time;