oracle存储过程

set serveroutput on;//

--最简单的语句块
begin
dbms_output.put_line('HelloWorld!');
end;

--带声明的语句块
declare 
  v_name varchar2(20);
begin
  v_name := 'myname';
  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;




--变量声明
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 := 'myname';
begin
  dbms_output.put_line('v_temp value:' || v_sal); 
end;



--变量声明，使用%type属性
declare
   v_empno number(4);
   v_empno2 emp.empno%type;
   v_empno3 v_empno2%type;
begin
   dbms_output.put_line('Test');
end;
 
--table变量
declare 
    type type_table_emp_empno is table of emp.empno%type index by binary_integer;
    v_empnos type_table_emp_empno;
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:='aaaa';
   v_temp.loc:='bj';
   dbms_output.put_line(v_temp.deptno || ' ' ||v_temp.dname);
end;




--使用%rowtype声明record变量
declare
   v_temp dept%rowtype;
begin
   v_temp.deptno:=50;
   v_temp.dname:='aaaa';
   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 ename,sal into v_ename,v_sal from emp where empno = 7369;
   dbms_output.put_line(v_ename || ' ' || v_sal);
end;



--PL SQL里建表语句
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;



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;




--使用loop和cursor
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 loop和cursor
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 和 cursor,最好使
declare 
    cursor c is
         select * from emp;
begin
    for v_emp in c loop   --v_emp自动声明，自动next
     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;


--存储过程
create or replace procedure p
is 
    cursor c is 
          select * from emp2 for update;
begin
    for v_emp in c loop
         if (v_emp.deptno = 10) then
               update emp2 set sal = sal+10 where current of c;
         elsif (v_emp.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;




--带参数的存储过程
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 varchar2(10),
atime date
);



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




update dept set deptno = 99 where deptno = 10; --因为deptno被人参考，不可被执行


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