Oracle的PL/SQL编程和游标

• PL/SQL的基本构成

• 数据类型 

• 集合类型

• 基本控制语句

• 异常处理

系统异常的几种: 

1. cursor_already_open 当游标已经open,执行open,产生此异常.
2. dup_val_on_index    insert操作进,违反了unique约束,产生此异常.
3. invalid_number  
4. no_ data_found
5. too_many_rows  多个记录的select into 的语句,产生此异常.
6. 等等...

declare
       l_dept departments%rowtype;
begin
     l_dept.department_id=100;
     l_dept.department_name='dong';
     insert into departments(department_id,department_name)

                         values(l_dept.department_id,l_dept.department_name);  
exception
     when dup_val_on_index then
          dbms_output.put_line('违反unique约束'); 
      when others then
          dbms_output.put_line('不明的异常'); 
end;

自定义的异常:

create or replace function divide(a int,b int) return int
   is
   begin
        if(b=0) then
                raise_application_error('-20001','second parameter not is zero');  --throws exception
        end if;
        return a/b;
   end;
   --PL/SQL语言部分
   declare
           a int:=10;
           b int:=0;
           c int:=0;
   begin
        c:=divide(a,b);
    exception
         when others then
               if sqlcode=-20001 then
                   dbms_output.put_line('myerror');
               end if;
   end;

delare中产生的异常不会被Exception捕捉到,系统会报错

declare
       i number default 'sfd';              --declare声明块中的异常由系统msgbox出来
begin
     null;
exception
         when others then
         dbms_output.put_line('exceptin catch');
end;

显式游标:

--根据输入的部门号查找hr.employees表的记录
declare
       cursor emp_cur(deptid in number) is select * from hr.employees where department_id=deptid;
       emp_row hr.employees%rowtype;
begin

open emp_cur(30);
     loop
         fetch emp_cur into emp_row;
         exit when emp_cur%notfound;
         dbms_output.put_line(emp_row.employee_id||'is'||emp_row.first_name||emp_row.first_name);
     end loop;
     close emp_cur;

open emp_cur(60);
     loop
         fetch emp_cur into emp_row;
         exit when emp_cur%notfound;
         dbms_output.put_line(emp_row.employee_id||'is'||emp_row.first_name||emp_row.first_name);
     end loop;
     close emp_cur;
end;

 

declare
     cursor cur  is  select  *  from scott.emp;
     r scott.emp % rowtype;
begin
     open cur;
     loop
         fetch cur into r;
         -- 如果comm列的值为NULL,则输入nodata
          if  r.comm  is   null  then
            dbms_output.put_line( ' nodata ' );
          else
             dbms_output.put_line(r.comm || '' );
         end  if ;
         exit when cur % notfound; -- cur % found,cur % isopen,cur % rowcount
     end loop;
     close cur;
end;

--循环游标(优点:更为简洁,不用声明变量,也不需要声明游标变量,隐式的打开和关闭游标)

declare
     cursor cur  is  select  *  from scott.emp;
begin
      for  emprow  in  cur   -- ( for emprow  : cur) 
     loop 
          if  emprow.comm  is   null  then
            dbms_output.put_line( ' nodata ' );
          else
             dbms_output.put_line(emprow.comm || '' );
         end  if ;
     end loop;
end;

隐式的游标

begin
     update scott.emp set empno=empno ;   
         if sql%found then
            dbms_output.put_line(sql%rowcount);
         else
             dbms_output.put_line( 'notfound' );
         end if;
end

 另外一种隐式的游标用FOR LOOP语句（隐式的打开和关闭游标）

begin
for dept in (select * from departments)
loop
    dbms_output.put_line(dept.department_id);
    dbms_output.put_line(dept.department_name);
end loop;
end;

Ref cursor(变化的游标)

declare
       type emp_cursortype is ref cursor return scott.emp%rowtype;
        cur emp_cursortype;
       emprow scott.emp%rowtype;
begin
    open cur for select * from scott.emp;
    loop
        fetch cur into emprow;
        if emprow.comm is null then
           dbms_output.put_line( 'nodata' );
        else
           dbms_output.put_line(emprow.comm|| '' );
        end if;
        exit when cur%notfound;
    end loop;
    close cur;

    dbms_output.put_line( '查找COMM>100' );

    open cur for select * from scott.emp where comm> 100 ;
    loop
        fetch cur into emprow;
        if emprow.comm is null then
           dbms_output.put_line( 'nodata' );
        else
           dbms_output.put_line(emprow.comm|| '' );
        end if;
        exit when cur%notfound;
    end loop;
    close cur;
end;

   用select 语句为集合变量复值 

declare
    TYPE namearray IS TABLE OF VARCHAR2(40) INDEX BY binary_integer;
           names    namearray;
    TYPE idarray IS TABLE OF integer INDEX BY binary_integer;
           empnos   idarray;
BEGIN
--返回多个记录时赋予集合数据
    SELECT ename,empno bulk collect INTO names,empnos  FROM scott.emp;
    FOR i IN names.first..names.last
    LOOP
            dbms_output.put_line(names(i)|| ' '||empnos(i));
    END LOOP;
END;

•  

游标