oracle PL/SQL小结

PL/SQL：

代码块:
DECLARE       -optional
BEGIN            -required
EXCEPTION   -optional
END               -required

若使用dbms_output输出时，先要设置 set serveroutput on;  显示输出。  

有时候我们明明在用dbms_output.put()  函数输出值，但是并没有显示出来，就像丢失了一样。这可能是由于put()函数负责将括号中的数值输出，但是由于不知道一行输出几个，则存储在缓冲区中，我们需要在连续put()之后需要用dbms_output.new_line()将缓冲区的内容写在屏幕上。

pl/sql中for循环体中，

for i in 1..X(select * from emp) loop

end loop;

i不需要在declare中声明，这是一个独立的代码块，即使在外部声明了，for循环代码块中也是独立的一个。其可以自己适应不同的数据类型。

pl/sql中跳出循环可以用exit。跳出当前最近的一层循环。

取余函数为mod(m,n)----m%n。

pl/sql中varchar类型常量用单引号。

pl/sql中定义复合类型的语句为  type record_name is record
  (
       var_name1 datatype1,
       var_name2 datatype2
  );

或者隐式类型定义 %rowtype %type. 

 如  rtx emp.empno %type  定义rtx为表emp中empno的类型。

       rtx emp%rowtype  定义rtx为emp表中所有列属性的复合类型。

pl/sql中我们可以直接用into来获取select语句中取出的属性值。

如select num,avgsal,job_title into rtx from XXX   注意这里取出的集合中存在多个元素则要使用相应长度的数组存储，否则会报错。

pl/sql中声明数组

TYPE myarray IS VARRAY(number) of typename;     -----定义数组类型和元素个数

v_array myarray := myarray(初始化参数);

注意数组使用的时候必须先初始化，且初始化参数的个数小于元素个数的最大值。

在代码块中初始化语句

v_array.extend(个数) -----默认初始化一个，也可初始化多个，但数组总个数必须小于元素个数的最大值

v_array.extend       -----初始化一个

v_array.count(实例化的个数）-----查询已初始化元素的个数，可用于for循环中做跳出条件

type myarray is table of number;  可变长度的数组，可以初始化任意数量的元素。
type myarray is table of number index by varchar2(20);    索引值为字符，不需要实例化。即哈希表或plsql表    

索引可为varchar、Binary_integer 不能为浮点数型。

游标cursor：在数据库中，表的更新多数都使用游标进行。这就相当于C++、C#中的迭代器的作用。

在declare中声明 cursor  cur_sal is select sal from emp;  -------定义时未执行查询，即不分配内存也不执行查询。

在begin中执行   open cur_sal;                                       -------实际执行查询并存入。

                        fetch cur_sal into v_sal;                       -------取出当前游标所指向的值，并自然向下移动一个
                        while cur_sal%FOUND loop                 -------%取出性质  %FOUND最近一次取出数据操作是否有数据返回。

                        fetch cur_sal into v_sal;
                        end loop;

                        close cur_sal;                                      -------释放内存区域

另外一种游标遍历的方式:  for rec in cur_sal loop 自动形成open fetch close rec也自行适配游标
       end loop;
或者直接用for循环执行SQL语句:  for rec in (sql语句) loop
          end loop;
cur_sal%rowcount   表示游标已经读取过多少行数据  从1开始

带参数的游标声明语句:
cursor cur_sal(p_deptno in number) is select ename from emp where deptno = p_deptno;

更新游标所指向的行:
cursor cur_sal(p_deptno in number) is select ename from emp where deptno = p_deptno for update；  申请独占锁    别人无法访问
update emp set sal = 10000+sal where current of cur_sal;   

系统隐式游标sql  sql%rowcount  显示更新或插入了多少行

                                sql%FOUND  显示是否取得值

                                sql%NOTFOUND 与上面相反

                                sql%isopen   永远显示false  系统游标只在DML语句执行之前打开,在之后关闭

异常处理:

pl/sql中异常分为系统异常或自定义异常。

系统异常有通常有5种，即zero_divide、value_error(类型转换错误)、too_many_rows、no_data_found、DUP_VAL_ON_INDEX(重复)。

自定义异常通常在declare中声明异常类型:

myexception exception;

并在代码中使用raise myexception 来触发异常。

并在exception部分来处理触发的异常。  when myexception then  XXXXX、when other then XXX 处理所有未有专门处理函数的异常

注意：1.不能给同一个异常多个不同的处理函数。

          2.不可能存在多个异常同时发生。

一般在declare与exception中触发的异常由外层处理。或内存无处理程序也在交由外层处理。

一般处理函数SQLCODE、SQLERRM

其他的异常定义函数raise_application_error(ERROR_NUMBER,ERROR_MSG,[keep_error]);

                             ERROR_NUMBER:-20000~20999之间。

                             ERROR_MSG:小于500个字符。

或 进行预定义，在declare部分声明  PRAGMA EXCEPTION_INIT(异常变量名,ORACLE_ERROR_NUMBER);    将一个异常变量与一个 错误编码绑定

注意要点:

1.就算相同的异常变量名, 外层声明的异常与内层声明的异常也不想同.所以当内层无相应的异常处理函数时,外层也无法通过查找异常变量名来处理,只能当作others来处理.

如:           declare

                   e_new exception;

                begin

                   declare

                       e_new exception;

                   begin

                        raise e_new;

                    end; 

exception

                     when e_new then                    ----- 这里只能用 others 来接受异常并处理.

                              XXXXXX;

                end;

2.外层无法处理外层未声明但是内层中存在的异常.

如上面,若外层没有声明e_new.则在外层无法编译 when e_new then

3.核心 异常也是变量,符合变量的作用域.