PL/SQL学习笔记

SQL：是单纯的SQL语句，PL/SQL：是把DML和select语句组织在 PL/SQL代码的过程性单元中，通过逻辑判断，循环等操作实现复杂的功能或者计算的程序语言：有三个部分：1）变量和类型2）控制结构3）过程和函数
PL/SQL程序结构：1）声明部分：declare 2)执行部分:begin 3)异常                           处 理：exception
PL/SQL的数据类型：
数字类型:BINARY_INTEGER,只能用于PL/SQL，是二进制类型，number的子类型有dec(38),float(38),real(18);
日期类型:
字符类型:String(只能用于PL/SQL)
布尔类型：用于存储逻辑值（TRUE,FALSE,NULL），不能向数据库中插入BOOLEAN类型的数据，不能将列表保存到boolean变量中，只能对boolean变量执行逻辑操作；
复合类型：record,associative  array,nested   table,varray
set serveroutput on;是把运行环境下的服务器打开，是一种格式类型的，就像是设定当前环境下的日期格式一样；每次运行前都要有；
变量声明：变量+变量类型：=？，记住用：=赋值，而且规定没有初始化的变量为null;%TYPE方式定义的变量表示变量具有与数据库的表中某列或者其他的变量有相同的类型：比如：v_realname account.real_name%tepy;

dbms_output是系统提供的package，其中的过程put_line实现的是输出功能，只有一个参数，为字符类型，用于接收需输出的字符串。在sql工作表子窗口中可以调用存储过程。 exec 存储过程名
　　　　　　　　　　　　begin  exec 存储过程名；
　　　　　　　　　　　　end；

流程控制语句：条件语句：if语句和case语句；循环语句：loop,while,for，
条件语句：if…then….elsif…then…else…end if;
Loop循环语句：loop…exit when<condition> …end loop;
While循环语句：while<boolean  expression> loop ….end  loop;循环语句执行顺序是先判断<boolean  expression>的真假，如果为true则循环执行，否则退出循环，在while循环语句中仍然可以使用exit或者exit  when 子句
For循环语句：for   循环计数器  in 下限..上限 loop…..end  loop;每循环一次，                                           循环变量自动加1，使用关键字reverse，循环变量自动减1，跟在in  reverse 后面的数字必须是从小到大的顺序，但不一定是整数，恶意是能够转换成整数的变量或表达式，可以使用exit或者exit  when子句退出循环，
For在corsur里的应用：里面没有open,fetch,close这些动作，集成度高使用方便，其实是fetch给了i;i的类型是cousor的返回值类型是一样的。使用它的缺点是集成度过高，对很多处理的操作是不方便的。
PL/SQL中的SQL分类：
静态SQL：静态SQL中可以使用的是DML语句和事务控制语句，记得提交commit，因为他们的是没有返回结果集的。如果要是使用DDL语句要经过转换，在PL/SQL中使用的SQL语句在编译时是明确的，静态的是在编译阶段编译，再运行。可以直接使用的
动态SQL：PL/SQL块在编译的时候SQL语句是不确定的，也就是这个时候不编译而是运行，比如根据用户输入的参数的不同而执行不同的操作，编译程序对动态语句部分不进行处理，只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。SQL语句是一个字符串，程序执行的时候才编译语句，在编译阶段是不会报错的，
静态SQL比动态SQL 效率高。
本地动态SQL执行DDL语句:begin    execute  immediate ‘DDL语句’;end;
Select语句返回的记录数，可以分为两类：1）当且仅当返回一条记录用select…into…语句实现；2）返回0条或者多条记录：用cursor实现：
用select  count(*) into 来处理返回值为null  的异常。
定义记录类型（record）：处理单行多列的数据：先定义数据类型，  再赋值；
TYPR   t_cost_rec  IS RECORD   比如：v_cost  t_cost_rec;---v_cost是记录类型的变量
给记录类型赋值：1）v_cost.base_cost:=5.9;用：=的方式。
                           2）base_cost   cost.base_cost%TYPE;--- base_cost是成员，%表示的是属性，这里用select  into  的方式赋值.只有把握到只要返回一条记录的时候使用。
Record变量的引用：记录类型变量的属性引用方法是：‘.’引用，也就是变量名.属性名，后面可以直接赋值； Select..into..和fetch..into ..返回的结果集如果是单行多列的情况，用一个变量来表示所有的变量，也就是记录类型的这个变量里面包含了单行多列要输出的所有变量；
%rowtype表示的是用表结构或视图结构定义变量；使用时record成员的名称和类型与表或者视图的列全名称和类型完全相同；比如：v_cost   cost%rowtype;
Cursor的使用：它本身对应的就是SQL语句，每使用cursor他都会开辟有自  己的存储空间，分为两类：一个是隐式的，比如：select…into …和DML语句，另外一种是：显式的,返回多条记录的select语句用它来实现，处理过程是：声明cursor（declare）打开cursor（open:执行SQL语句，产生结果集,存放在它自己私有的空间里）提取cursor（fetch:提取结果中记录，fetch..into..后面的变量要和select的变量一样，如果没有fetch则cursor%放的值是null，fetch到就返回true,相反就是false）判断cursor是不是为空（empty:结果集中是否有未提取的记录）关闭cursor (是为了释放存储空间，每一个它是一个进程)，
CURSOR的声明：在begin 之前声明，使用标准的select语句，如果需要按照指定的次序处理行，可在查询中使用order by语句，可以在查询中引用变量，但是必须在CURSOR语句之前声明变量，也就是对SQL语句的处理而已，他放的是select语句返回的多条记录。
CURSOR的打开：通过open CURSOR来执行select语句并标识结果集，select语句如果没有返回记录，不会出现异常；
CURSOR的fetch:检索当前行，把值赋给变量，变量可以是record类型或简单变量，如果是简单变量：包含相同数量的变量，把每一个变量和相应的列进行位置匹配通过循环检测CURSOR中是不是包含数据行。
CURSOR的close:处理完结果集中的数据后，应该关闭cursor,如果需要，可以再次打开该CURSOR，CURSOR一但关闭，所有和该CURSOR相关的资源都会被释放，不可再从关闭的CURSOR中提取数据，任何对关闭的CURSOR的操作都会引发INVALID_CURSOR错误，每个session能打开的CURSOR数量由open_cursor参数决定
显示游标的属性：目的是获取有关显示游标的状态信息；%isopen    ： 布尔类型 ：如果游标是打开的其值为true;    %notfound  :布尔类型：如果前一个 fetch语句没有返回一行记录，其值为true;    %found  ：布尔类型： 如果前一个fetch语句返回记录，其值为true;     %rowcount  :数值类型 :到目前为止，游标已经提取的总行数。
使用的是：select…into…语句,DML语句。。。                                               
隐式游标的属性：目的是获取有关显示游标的状态信息    SQL%isopen    ：布尔类型 ：DML执行中为true结束后为false;   SQL%notfound  :布尔类型：DML操作不成功其值为true;      SQL%found  ：布尔类型： 如果DML操作成功其值为true;    SQL%rowcount  :数值类型ML语句成功执行的数据行数，使用cursor实现。
执行的SQL语句是最近的一条SQL语句。
Collection是按某种顺序排列的一组元素，所有的元素有相同的数据类型，每个元素有唯一一个下标标识其在这一组元素中的位置。
分类：1）Associative array(关联数组)又称index-by table,使用键值访问2）Nested table 嵌套表3）Aarray数组，变长数组，定义是需要指定数组的大小
集合类里面的关联数组：
关联数组就是键值对的集合，其中键是唯一的，用于确定数组中对应的值，键可以是整数或字符串；关联数组能帮我们存放任意大小的数据集合，快速查找数组中的元素，它像一个简单的SQL表，可以按主键来检索数据。它是同种类型的一维，无边界的稀疏集合，只能用于PL/SQL。
　　　可以用主键约束的值来做key,和corsur一起来使用，for的对象是corsur里面的结果集，用关联数组的变量来挨个i.id这种方式来添加数据进去数组里面，i的数据类型是。        type t_indtab is table of number  index by binary_integer;--对关联数组的声明:t_indtab是数组名，number是存放元素的类型，index by后面的是key的类型。
提供的方法可以直接用  变量.方法名  的形式调用方法：exists(i):判断当前下标所对应的值存在不存在；first和last是获取key的值的最小值和最大值，key值放进去是已经排好序的如果集合为空则返回null；count返回联合数组的元素个数，不包括被删除的元素，对于空的数组返回值是0，建议使用前先用exists来判断一下，这样可以避免抛出异常;prior(n)和next(n)返回第n个key的前一个或者后一个key,如果不存在就返回null ;   trim（n）从最后一个元素删除n个元素;   delete删除所有元素，delete(n)删除第n个元素，delete(m,n)从第m个元素删除到第n个元素;
select real_name bulk collect into v_account from account;--v_account是一个集合类型的集成度更好的处理corsor，是一种隐式游标；优点是所有的情况下都不会报错，缺点是不好处理细节问题；比如说空值问题的处理；通过bulk collect 减少loop处理的开销，采用bulk collect可以将查询结果一次性加载到collections中，而不是通过cursor一条一条地处理。可以在select into,fetch  into  ,returning  into  语句使用bulkcolect,注意在使用bulk collect时，所有的into 变量都必须是collections。

---------异常-------过程-------函数-----------
用oerr   ora +错误端口号：可以查询出这个端口号是什么异常，处理方案是什么。
Oracle错误：1）PL/SQL错误：编译时错误，2）运行时错误：oracle错误是机制错误，plsql运行错误，用户定义的条件错误。
异常的类型：1）隐式触发：包括oracle预定义异常和非oracle预定义异常
                    2）显示触发：用户自定义异常。
Oracle错误处理机制：在程序运行期间的错误对应一个异常，当错误产生时抛出相应的异常，并被异常处理器捕获，程序控制权传递给异常处理器，有异常处理器来处理运行时错误。
捕获异常的过程：declare..异常被定义..begin..异常被触发..exception..异常被            捕获并处理..end;
传播异常的过程：declare....begin..异常被触发..exception..异常没有被捕获并传播给调用程序的环境..end;
异常的捕获规则：exception关键字，标识异常处理的开始区域，一个异常处理器可以捕获多个异常，只需要在when子句用or连接即可，允许有多个异常处理器，一个异常只能被一个处理器捕获并处理，others异常处理器总是作为异常处理部分的最后一个异常处理器，负责处理那些没有被其他异常处理器捕获的异常。
预定义异常：是已经知道要出现了什么异常就用直接捕获异常来处理异常，处理方案：我们需要做的只是捕获异常，只要when  异常的名字 then 对异常的处理是什么？比如:exception  when   no_data_found    then….;
Select …into…一般有两种异常：一种是没有返回值：on  data  found;一种是返回太多：too_many_rows;
非定义异常：它和预定义异常的都是不需要抛出手动抛出异常。处理的方案是:
1. 在declare定义块里面定义异常比如：<异常情况> exception；  2. 将定义好的异常和标准的oracle错误连接；pragma exception_init(<异常情况>,<错误代码号>)      3）在异常情况处理部分对异常进行捕获和处理when <异常情况> then…..；
             过程是:declare--associate----reference;前面两个是生命部分，后面的是异常处理器，reference不仅可以防止SQL注入也可以提高执行效率。
用户自定义异常：用户自定义异常必须在声明部分进行声明，当异常发生时，系统不能自动触发，需要用户使用raise语句，在异常处理部分捕获并处理异常。       过程是：在声明部分声明异常：异常名+exception在执行块中当错误产生时抛出异常：raise+异常名在异常处理部分捕获并处理异常。
通过SQLCODE和SQLERRM函数获取错误的相关信息：
SQLCODE：返回当前错误的代码号；SQLERRM：返回当前错误的消息文本
子程序的分类：1）匿名子程序：匿名块不存在数据库中，每次使用时都会进行编译，不能再其他块中相互调用；    2）有名子程序：命名的pl/sql块，编译并存储在数据库中，可以在任何需要的地方调用，它的组成部分有：子程序头----声明部分----可执行部分----异常处理部分（可有可无）。       有名子程序可以分为：1）procedure:过程    2）function:函数   3）package：包     4）trigger:触发器。    有名子程序的优点：1）模块化：可以将程序分解为逻辑模块   2）可重用性：可以被任意数目的程序调用   3）可维护性：简化维护操作 4） 安全性：通过设置权限，使数据更安全 。
匿名块一般很少用declare..begin..exception..end，也就是在调用存储过程的时候用，经常用的是有名块（子程序），它就是一个数据库对象。
所有的函数都放在一个包里，standard，这个包名是可以省略的。
存储过程：在有名块中，编译和运行是分开的。所有用户通过角色授予的权限，再有名块中角色是被disable的，相当于没有权限的，角色（connect），权限一直都存在的要通过grant赋予权；当有DML和TCL时一定要用权限。
语法：create or replace  procedure  过程名 (形参列表，变量名+形参类型（in,out,in out）+数据类型) is +变量的定义和初始化+begin…exception…end;
执行的过程是：创建-编译调用删除；用drop  procedure来删除；
绑定变量：当你使用动态SQL的时候一定要使用绑定变量，用“：”加变量的形式定义， 用来提高软件的性能，在PL/SQL中，能用静态sql的不用动态sql，当使用动态sql要使用绑定变量，不要直接拼接语句。
Sql分析：硬分析，软分析，
过程的参数种类：形参（在过程中定义）和实参（在调用过程的时候的（）里）…IN:只传进去就是读 ，把实参只传给过程的，也就是不能把它放在赋值号的左边，在调用之初实参传给形参   ;OUT:只传出来 ，就是写，实参传不进去的，调用完了以后将值传出去对应的变量，调用之初形参没有什么变化，调用完成之后将形参传给实参    ;   IN OUT:传进去又传出来 ，既能读也能写    ，定义过程的那个参数是形参，调用过程的传给过程的是实参，调用之初实参传给形参，调用完成时形参传给实参。
过程本身不返回值，但是过程可以返回值，通过out参数返回;
对实际参数的要求：模式为in的形参对应 的实际参数可以是常量或变量，模式为in out 或out 的形参对应的实际参数必须是变量，用于存储返回的值，所以不能是常量或表达式
对形式参数的限制：在调用的过程中，实际参数在将值传递给过程时，也传递了对变量的限制，形式参数不能声明长度，但可以使用%type来进行限制，
带参数的过程调用：
位置表示法：调用时添入所有参数，实参和形参按顺序一一对应；当有缺省的时候所有的缺省值放在最后
名字表示法:调用时给出形参名字，并给出实参，比如：（p_realname=>v_realname,p_inout=>10）;对缺省值无所谓
两种方法可以混用，混用时第一个参数必须通过位置来指定，名字表示对于参数很多时，可以提高程序的可读性。
函数：必须有返回值，而且必须有相同的数据类型的变量来接返回值
语法：create  or   replace   function 函数名（参数列表，和过程一样可以使用形参）+return 返回值类型+is +定义变量+begin+执行体+return+exception+end;
:v1号有两种含义：一种是宿主变量，另外一种是绑定变量
过程和函数的比较：


---------------包--------触发器-----------------
调用过程和函数有两种方法：一种是通过自己写的过程和函数:create or replace function和create or replace produrce;
另外一种是在包里的函数和过程：通过包名.过程名和包名.函数名的形式调用，原理是通过接口调用过程和函数的头提供怎么样的形式参数来决定调用的时候用多少实参，还有实参是常量还是变量（常量in,变量out,in out）; 如果参数里面default说明参数里面是有缺省值的，形参和实参的个数可以不一样。
Rowid记录物理位置，data_object_id：表的物理地址编号； block_no;rowid_no行的物理地址编号;file_no文件的物理地址编号;
包：（包头，包定义，包体）包只是用来定义数据结构的，实现方法的是包体：包   是一个可以将相关对象存储在一起的PL/SQL结构，分为包声明和包体两个部分，每个部分都单独被存储在数据字典中，包声明是一个操作接口，对应用来说是可见的，包体是黑盒，对应用来说隐藏了实现细节。
包的组成：
Alter view v_name compile;      视图编译       
Alter procedure proc1 compile;过程编译
Alter function fuu  compile; 方法编译
　　   Alter package pack compile;包声明编译
　　    Alter package body compile;   包体编译
  在过程和函数中定义的变量都是局部变量，在包中定义的变量是全局变量，可以在一个环境中使用，也就是一个进程中，当在不同的环境和进程中使用的时候都会初始化，包中的变量的作用范围是一个进程。
给一个包名从user_object返回 的是两个对象，一个是包一个是包体。
Grant execute on proc1 to username;----执行权限，
包的优点：
方便对存储过程和函数的组织，将相关的过程和函数组织在一起，在一个用户环境中解决命名的冲突问题；
方便对存储过程和函数的管理，在不改变包的声明定义是可以改变包体的实现，限制过程和函数的依赖性；
方便对存储过程和函数的安全性管理：整个包的访问权限只需要一次性授权，区分公用过程和私有过程。
改善性能，在被首次调用时作为一个整体全部调入内存，减少多次调用时的磁盘I/O次数。
对函数的三个动作：create or replace function.可以反复调用，一种是私有函数写在匿名块中，不能反复调用，只能在匿名块中，


触发器解决的问题：1.在做DML操作时，不需要提供主键值，系统自动生成，2.对复杂视图执行DELETE操作3.限制用户只能从指定的机器登陆。
触发器的种类：DML触发器、INSTEAD  OF触发器、系统触发器：
DML触发器：实际上就是把DML语句拆分成很多部分来操作，是针对某张表上的某个dml语句的操作，触发器类型：可以分为是语句级:update on account的只执行一次和行级的：update on account for each row,执行次数是由代码的影响记录条数决定。触发事件：  触发时间： 
Before update on account :before语句级     after update on account:after语句级before update on account for each row:before行级；     
DML触发器的触发顺序：根据触发的时间，类型不同，可以组合为四种DML触发器，触发器如箭头所示：    
DML行级触发器的语法：create or replace  trigger  触发器名   (before|after)   dml语句的类型   on  tablename  for  each  row  ………….              

触发器和函数、过程的区别是，触发器是语句触发的，不需要调用，函数和过程是需要调用的；触发器是不能自己提交的。触发器中不能有commit语句，需要通过自治事务来实现，就是大事务中套入小事务；

:OLD和: NEW是记录类型，表里有多少列就有多少成员，他们里面保存的是记录的值，只能在行级触发器里面使用，在语句级触发器里面使用会报错。
Before :new:=insert,之前可以使用new，

  PL/SQL的特点：
1.结构化模块化编程2.良好的可移植性3.良好的可维护性，4.提升系统性能5.不便于向异构数据库移植应用程序。
  触发器的重新编译：如果触发器内调用其他函数或过程，当他们被删除或修改后，触发器的状态被标识为无效，当DML语句激活一个无效触发器时，ORACLE将重新编译触发器代码，如果编译时发现错误，这将导致DML语句执行失败，调用ALTER  TRIGGER语句重新编译已创建的触发器。
比如:alter  trigger   triggername  complie;
  触发器的状态：1.有效状态（ENABLE）：当触发事件发生时，处于有效状态的trigger将被触发；2.无效状态（DISABLE）：当触发事件发生时，处于无效状态的trigger将不会被触发；3.trigger的两种状态可以互相转换，格式为：
Alter   trigger   triggername   (disable|enable);