PL/SQL笔记

--语句块的组成

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;
/

 

--SQL语句的运用

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_emp emp%rowtype;
begin
    select * into v_emp from emp where empno = 7369;
    dbms_output.put_line(v_emp.ename);
end;
/

--insert语句

declare
    v_deptno dept.deptno%type := 50;
    v_dname dept.dname%type := 'aaaa';
    v_loc dept.loc%type := 'bj';
begin
    insert into dept2 values(v_deptno, v_dname, v_loc);
    commit;
end;
/

declare
    v_deptno emp2.deptno%type := 50;
    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 (nnn varchar2(20) default ''aaa'')';
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;
/

--循环

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;
/

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;
/

 

--游标

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;
/

--游标when用法

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_temp in c(30,'CLERK') loop
      dbms_output.put_line(v_temp.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.deptno = 10) then
         update emp2 set sal = sal + 10 where current of c;
      elsif (v_temp.deptno = 20) then
         update emp2 set sal = sal + 20 where current of c;
      else
         update emp2 set sal = sal + 50 where current of c;
      end if;
    end loop;
    commit;
end;
/

--执行 

exec p;

或

begin;
 p;
end;

--带参数的存储过程

create or replace procedure p
     (v_a in number, v_b number, v_ret out number, v_temp in out number)
is
begin
   if (v_a > v_b) then
      v_ret := v_a;
   else
      v_ret := v_b;
   end if;
   v_temp := v_temp + 1;
end;
/

--实验

declare
 v_a number := 3;
 v_b number := 4;
 v_ret number;
 v_temp number := 5;
begin
 p(v_a, v_b, v_ret, v_temp);
 dbms_output.put_line(v_ret);
 dbms_output.put_line(v_temp);
end;
/

--函数
 

create or replace function sal_tax
  (v_sal number)
  return number
is
begin
   if (v_sal < 2000) then
      return 0.10;
   elsif (v_sal < 2750) then
      return 0.15;
   else
      return 0.20;
   end if;
end；
/

--触发器

create table emp2_log
(
uname varchar2(20),
action varchar(10),
atime date
);

-----------

create or replace trigger trig
  after insert or update or delete on emp2
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 emp2 set sal = sal * 2 where deptno = 30;

--------

create or replace trigger trig
  after insert or update or delete 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;
/

--------查看触发日志

select * from emp2_log;

--不提倡使用

create or replace trigger trig
 after update on dept for each row
begin
 update emp2 set deptno = :NEW.deptno where deptno = :OLD.deptno;
end;
/