PL/SQL学习笔记

--设置屏幕输出为on，即在屏幕上显示相应的输出。
SQL> set serveroutput on;
--dbms_output.put_line();相当于Java中的System.out.println();
SQL> begin
  2  dbms_output.put_line('hello!');
  3  end;
  4  /
 
--PL/SQL 中声明变量用declare ，变量名在前，类型在后；约定俗成的变量命名以“V_”开头
SQL> declare
  2  v_name varchar2(10);
  3  begin
  4  v_name := 'myname';
  5  dbms_output.put_line(v_name);
  6  end;
  7  /
 
  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;
  /
  --变量声明，使用%type属性
    declare
    v_empno number(4);
    v_empno2 emp.empno%type;
    v_empno3 v_empno2%type;
   
   
--Table变量类型 即相当于Java中的数组类型
declare
--声明类型type_table_emp_empno是一个table类型，里边存放的是emp表中的empno类型的数据，用binary_integer类型的索引；该类型的变量的下标可以是负数。
  type type_table_emp_empno is table of emp.empno%type index by binary_integer;
  --定义变量v_empnos 是上面声明的类型
  v_empnos type_table_emp_empno;
 
--Record变量类型，相当于Java中的类
declare
  type type_record_dept is record --声明类型type_record_dept 是一个record
    {
      deptno dept.deptno%type,
      dname dept.dname%type,
      loc dept.loc%type
    };
  v_temp type_record_dept;  --定义变量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; --声明变量v_temp为 dept 整张表的行结构，当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;
 
--SQL语句的运用
declare
  v_ename emp.ename%type;
  v_sal emp.sal%type;
  begin
  --用select语句时必须要有返回值，并且返回值只能有一条
    select ename,sal into v_ename,v_sal from emp where empno = 7369;
    dbms_output.put_line(v_ename);
  end;
 
declare
  v_deptno dept.deptno%type :=50;
  v_name dept.dname%ytpe :='aaa';
  v_loc dept.loc%type := 'BJ';
  begin
    insert into dept2 values(v_deptno ,v_dname,v_loc);
    commit;
  end;
--PL/SQL中执行DDL语句需要在前面加上execute immediate 语句
  begin
    execute immediate  'create table t (nnn varchar2(10) default ''aaa'' )'; --用两个单引号对DDL语句中的单引号进行转义
  end;
 
--if语句
--取出7369的薪水，如果<1200，则输出'low'，如果<200则输出'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;
 
--PL/SQL中的循环
declare
  i binary_integer :=1;
  begin --相当于do..while循环
    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;
  --从1到10倒序循环
  for k in reverse 1..10 loop
    dbms_output.put_line(k);
  end loop;
end;

--错误处理
declare
  v_empno emp.empno%type;
  begin
    select empno into v_empno from emp where deptno=10;
    exception
      when too_many_rows then
        dbms_output.put_line('太对记录了');
      when others then
        dbms_output.put_line('error!');
  end;
   
--错误日志的记录
create table errlog
(
  id number primary key,
  errcode number,
  errmsg varchar2(1024),
  errdate date
)
create sequence seq_errlog_id start with 1 increment by 1; --创建errlog表的递增序列

declare
  v_deptno dept.deptno%type := 10;
  v_errcode number;
  v_errmsg errlog.errmsg%type;
  begin
    delete from dept where deptno = v_deptno;
    commit;
  exception
    when others then
      rollback;
      v_errcode := SQLCODE;
      v_errmsg :=SQLERRM;
      insert into errlog values (seq_errlog_id.nextval,v_errcode,v_errmsg,sysdate);
      commit;
  end;
 
--PL/SQL中cursor（游标）的使用  游标有四个属性：isopen、notfound、found、recount
declare
  cursor c is
    select * from emp;
  v_emp c%rowtype;
  begin
    open c;
    fetch c into v_emp;
    dbms_output.put_line(v_emp.ename);
    close c;
  end;
 
--游标配合循环的运用
  declare
  cursor c is
    select * from emp;
  v_emp c%rowtype;
  begin
    open c;
    loop
      fetch c into v_emp;
      exit when (c%notfound);
      dbms_output.put_line(v_emp.ename);
    end loop; 
    close c;
  end;
--用while循环的实现
  declare
  cursor c is
    select * from emp;
  v_emp c%rowtype;
  begin
    open c;
    fetch c into v_emp;
    while(c%found) loop
      dbms_output.put_line(v_emp.ename);
      fetch c into v_emp;
    end loop; 
    close c;
  end;
   
--用for循环的实现
declare
  cursor c is
    select * from emp;
  begin
    for v_emp in c  loop
      dbms_output.put_line(v_emp.ename);
    end loop;
  end;
--带参数的游标的实例： 
declare
  cursor c(v_deptno emp.deptno%type,v_job emp.job%type) is
    select ename,sal from emp where deptno = v_deptno and job = v_job;
  begin
    for v_emp in c(30,'CLERK')  loop
      dbms_output.put_line(v_emp.ename);
    end loop;
  end;
 
--可更新的游标
declare
  cursor c  is select * from emp2 for update;
  begin
    for v_temp in c loop
      if(v_temp.sal < 2000) then
        update emp2 set sal = sal *2 where current of c;
        elsif (v_temp.sal = 5000) then
          delete from emp2 where current of c;
      end if ;
    end loop;
    commit;
  end;
 
 
--存储过程
create or replace procedure p
  is
  cursor c  is select * from emp2 for update;
  begin
    for v_temp in c loop
      if(v_temp.sal < 2000) then
        update emp2 set sal = sal + 20 where current of c;
        elsif (v_temp.sal = 5000) then
          update emp2 set sal = sal + 50 where current of c;
          else
            update emp2 set sal = sal +30 where current of c;
      end if ;
    end loop;
    commit;
  end;
 
--运行过程的命令如下；
execute p;
或者
begin
  p;
end;

--创建带参数的存储过程
create or replace procedure p
  (v_a in number,v_b in number,v_c out number,v_d in out number)
  is
  begin
    if(v_a > v_b) then
      v_c := v_a;
      else
        v_c := v_b;
    end if ;
    v_d := v_d +1;
  end;
--调用带参数的过程的实例：
declare
  v_a number := 3;
  v_b number := 4;
  v_c number;
  v_d number := 5;
  begin
      p(v_a,v_b,v_c,v_d);
      dbms_output.put_line(v_c);
      dbms_output.put_line(v_d);
      end;
 
--函数
create or replace function sal_tax
  (v_sal number)
  return number
  is
  begin
    if(v_sal < 2000) then
      return 0.1;
      elsif(v_sal <2750) then
        return 0.15;
        else
          return 0.20;
    end if ;
  end;
--创建函数后调用同其他函数一样，例如：
select sal_tax(sal) , lower(ename) from emp;

--触发器
create table emp2_log
(
uname varchar2(20),
action varchar2(10),
atime date
);
--创建触发器
create or replace trigger trig
  after insert or delete or update on emp2 for each row
  begin
    if inserting then
      insert into emp2_log values (USER,'insert',sysdate);
      elsif updating then
        insert into emp2_log values (USER ,'update' ,sysdate);
        elsif deleting then
          insert into emp2_log values(USER,'delete',sysdate);
    end if;
  end;
 
--创建一触发器，解决当你执行update dept set deptno = 99 where deptno = 10;时，会报emp表中已经有相应的引用的错 
create or replace trigger trig
  after update on dept  for each row
  begin
    update emp set deptno = :NEW.deptno where deptno =:OLD.deptno;
  end;
--当执行一条语句时既检查约束条件也触发相应的触发器，则相应的触发器先被触发！