oracle的plsql语言学习总结及重点游标

一、Oracle的内部编程语言

（一）认识PLSQL

PLSQL 是Oracle公司在SQL基础上进行扩展而成的一种过程语言。PLSQL提供了典型的高级语言特性，包括封装，例外处理机制，信息隐藏，面向对象等；并把最新的编程思想带到了数据库服务器和工具 集中。可以把PLSQL理解成IDEA+ORACLE集成在一起了。

1、每一种数据库都有自己的一个内部编程语言。

2、pl_sql：procedure language 和 Structured Query Language（过程语言和结果化查询语言）。

3、pl：procedure language过程语言即带有分支和循环的语言。

对于一个复杂的查询逻辑：例如如果大于100，加10；如果小于100，加5；这种逻辑通过sql就很难实现，需要和pl结合实现。

（二）PLSQL语法

plsql结构块:在PL/SQL块中End关键字用分号结尾，用符号“/”执行；

要在窗口界面显示输出结果，即使用dbms_output.put_line()语句输出变量值，需要先执行 set serveroutput on 命令。

DECLARE --- 可选

　　变量声明定义：声明PL/SQL用到的变量,类型及游标，以及局部的存储过程和函数

BEGIN　---- 必选

       SQL 和PLSQL 语句： 过程及SQL 语句 , 即程序的主要部分

EXCEPTION ---- 可选

　　错误处理

END；---- 必选

1、实例：

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;

二、变量的声明

（一）、变量声明规则

1、变量名不能够使用保留字

2、第一个字符不必须为字母

3、变量名最多包含30个字符

4、不要与数据库的表或者列同名

5、每一行只能声明一个变量

（二）、常用变量类型

1、binary_integer：整数，主要用来计数而不是用来表示字段类型

2、number：数字类型

3、char：定长字符串

4、varchar2：变长字符串

5、date：日期

6、long：长字符串，最大2G

7、boolean：布尔类型，可以取值true、false和null值。

（三）、%type属性的使用

在plsql中，--表示注释一行；

%type属性可以用来定义变量的类型同数据库中的字段的类型保持一致。

例如：

declare

       v_empno emp.empno%type;    --表示v_empno同emp表中的empno字段类型保持一致。即使修改emp表的empno字段类型，也保持一致。

begin

       dbms_output.put_line(‘test’);

end;

（四）、定义复合变量

1、table：类似java中的数组；record：类似C中的结构体；

2、table变量类型（先定义该类型，再使用该类型来定义变量）

定义格式：type  type_table_emp_empno is table of emp.empno%type index by binary_integer;其中type关键字表示定义一种类型；index by表示下标的数据类型；

实例：

Declare

      type tt is table of emp.empno%type index by binary_integer;

      vs tt;

begin

     vs(0):=0;

     vs(2):=2;

     vs(-1):=11;

dbms_output.put_line(vs(-1));

end；

3、record变量类型（记录表中各个字段的数据类型）

使用%rowtype属性来声明record变量,即：表名+%rowtype则表示变量为record的变量。

实例：

Declare

       V_temp dept%rowtype； --表示变量v_temp是记录表dept每个字段的数据类型的record变量；

Begin

       v_temp.deptno:=50;

       v_temp.dname:=’adada’;

       v_temp.loc:=’beijing’;

       dbms_output.put_line(v_temp.deptno||v_temp.dname);

end;

三、游标（cursor）（重点）

游标类似java中集合的迭代器，类似指针指向结果。

1、游标的概念

游标本质是一块内存区域，由系统或用户以变量的形式定义，可以提高数据库数据处理速度。游标的工作机制是一种能从包括多条数据记录的结果集中每次提取一条记录的机制，即游标提供了在逐行的基础上操作表中数据的方法。

游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。 

在MS SQL SERVER 中并没有一种描述表中单一记录的表达形式，除非使用where 子句来限制只有一条记录被选中。因此我们必须借助于游标来进行面向单条记录的数据处理。我们常常会遇到这样情况，即从某一结果集中逐一地读取一条记录。那么如何解决这种问题呢？游标为我们提供了一种极为优秀的解决方案。

（一）、游标有两种类型：显式游标和隐式游标。在前述程序中用到的SELECT...INTO...查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。 
游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。 
隐式游标 
如前所述，DML操作和单行SELECT语句会使用隐式游标，它们是： 
* 插入操作：INSERT。 
* 更新操作：UPDATE。 
* 删除操作：DELETE。 
* 单行查询操作：SELECT ... INTO ...。 
当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。隐式游标可以使用名字SQL来访问，但要注意，通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。

（一）隐式游标

当执行一条DML语句后，DML语句的结果保存在四个游标属性中，这些属性用于控制程序流程或者了解程序的状态。当运行DML语句时，PL/SQL打开一个内建游标并处理结果，游标是维护查询结果的内存中的一个区域，游标在运行DML语句时打开，完成后关闭。隐式游标只使用SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT三个属性.SQL%FOUND,SQL%NOTFOUND是布尔值，SQL%ROWCOUNT是整数值。

1. 隐式游标的属性                 返回值类型                    意    义   
2. SQL%ROWCOUNT              整型              代表DML语句成功执行的数据行数   
3. SQL%FOUND                     布尔型            值为TRUE代表插入、删除、更新或单行查询操作成功   
4. SQL%NOTFOUND             布尔型             与SQL%FOUND属性返回值相反   
5. SQL%ISOPEN                    布尔型             DML执行过程中为真，结束后为假  

注意：sql%rowcount就是一个属性值。

使用实例：

declare
n number;
begin
  insert into test values(1,'小明测试');
  n:=sql%rowcount;   --增加、修改、删除语句影响的行数
  dbms_output.put_line(n);
  end;

 

（二）显示游标

1、声明游标：

CURSOR 游标名[(参数1 数据类型[，参数2 数据类型...])] 
IS SELECT语句; 
参数是可选部分，所定义的参数可以出现在SELECT语句的WHERE子句中。如果定义了参数，则必须在打开游标时传递相应的实际参数。

2、打开游标

OPEN 游标名[(实际参数1[，实际参数2...])]; 
打开游标时，SELECT语句的查询结果就被传送到了游标工作区。 

3、提取数据（使用循环，使用for循环即可）

FETCH 游标名 INTO 变量名1[，变量名2...]; 
或 
FETCH 游标名 INTO 记录变量; 
游标打开后有一个指针指向数据区，FETCH语句一次返回指针所指的一行数据，要返回多行需重复执行，可以使用循环语句来实现。控制循环可以通过判断游标的属性来进行。

4、关闭游标

CLOSE 游标名; 
显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。

实例：

1. DECLARE  
2.   CURSOR emp_cursor IS    
3.   SELECT empno, ename FROM emp;   
4. BEGIN  
5. FOR Emp_record IN emp_cursor LOOP    --使用for循环会自动打开、关闭游标。   
6.     DBMS_OUTPUT.PUT_LINE(Emp_record.empno|| Emp_record.ename);   
7.     END LOOP;   
8.     END;  

注意：使用DBMS_OUTPUT.PUT_LINE命令时，要使用命令打开服务，set serveroutput on；

参考文档：https://www.cnblogs.com/heshan664754022/archive/2013/05/22/3092437.html

四、存储过程（procedure）

 存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。存储过程即一个有名字的plsql的语句块。

（一）、存储过程格式：

create[or replace] procedure 存储过程名称

       (参数名 [in]/out 数据类型)   --参数声明，其中参数可以省略
is/as   --关键字

       变量名    数据类型；      --变量声明

begin   --关键字

       plsql语句  --执行过程
end ；--关键字

（二）、存储过程参数说明：

1、参数  IN ：表示输入参数，是参数的默认模式。

2、OUT表示返回值参数，即out模式参数最终的值会赋值给调用时对应的<实参变量>。 

3、OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程，类型可以使用任意Oracle中的合法类型。

4、IN OUT表示该参数可以向该过程中传递值，也可以将某个值传出去。

5、存储过程参数不带取值范围，in表示传入，out表示输出; 变量带取值范围，后面接分号;

6、存储过程参数中有out和in out类型，不能直接传数，必须传变量。

（三）、存储过程实例

Create or replace procedure p ( va in number,vb number, vret our number,vtemp in out number)

Is

Begin

If(va>vb) then

      Vret :=va;

Else

      Vret :=vb;

End if;

Vtemp := vtemp+1;

end;

调用该存储过程P；

Declare

 Va number :=3;

 Vb number :=4;

 Vret number;

 Vtemp number:=5;

Begin

       P(va,vb,vret,vtemp);

       Dbms_output.put_line(vret);

       Dbms_output.put_line(vtemp);

End;

（四）、函数

函数和存储过程类似,函数使用和常用函数使用方法一样，例如：max（）等函数。

1、自定义函数语法：

create[or replace] function 函数名称(参数名 [in]/out 数据类型)   --参数声明，多个参数用逗号分隔，其中参数可以省略
return return_type  --函数返回参数类型，注意这里没有分号
is/as   --关键字
    变量名    数据类型；      --变量声明
begin   --关键字
    plsql语句  --执行过程，函数体
end ；--关键字

注意：不要遗漏函数返回值类型。

实例：

create or replace function function1(para1 in number, para2 in number)   
return number   
as   
begin  
  if para1 > para2 then  
      return para1;  
  else  
      return para2;   
  end if;  
end function1; 

使用方式：select function1(666, 333) from dual;

五、触发器（trigger）

       触发器在数据库里以独立的对象存储，它与存储过程和函数不同的是，存储过程与函数需要用户显示调用才执行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。所以运行触发器就叫触发或点火（firing）。

       触发器常用来完成由数据库的完整性约束难以完成的复杂业务规则的约束，或用来监视对数据库的各种操作，实现审计的功能。（复杂的业务逻辑通过后台java代码实现）。

（一）、触发器使用格式

触发器使用格式类似于存储过程，格式如下：

1 2 3 4 5 6

create [or replace] tigger 触发器名 触发时间 触发事件 on 表名 [for each row] begin  pl/sql语句 end

注意事项：

 1、触发器名：触发器对象的名称。由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途。
 2、触发时间：指明触发器何时执行，该值可取：
 3、before：表示在数据库动作之前触发器执行;
 4、after：表示在数据库动作之后触发器执行。
 5、触发事件：指明哪些数据库动作会触发此触发器：（多个触发事件用or）
 6、insert：数据库插入会触发此触发器;
 7、update：数据库修改会触发此触发器;
 8、delete：数据库删除会触发此触发器。
 9、表 名：数据库触发器所在的表。
 10、for each row：对表的每一行触发器执行一次。如果没有这一选项，则只对整个表执行一次。

（二）for each row 场景中两个关键字 :old 和:new

1、:old.字段名表示获取修改之前的值；

2、:new.字段名表示获取修改之后的值。

实例：

create or replace trigger tri_log
after insert or update or delete on dept
for each row --for each 行级触发，执行语句每影响一行就触发执行触发器一次，默认是语句级触发
begin
if inserting then
insert into log values(user,'insert',sysdate);
elsif updating then
insert into log values(user,'update',sysdate);
elsif deleting then
insert into log values(user,'delete',sysdate);
end if;
end;

select * from dept;
insert into dept values(80,'技术部','湖南衡阳');

六、递归（recursion）

       假设你正在为一个新闻网站开发一个评论功能，读者可以评论原文甚至相互回复。

这个需求并不简单，相互回复会导致无限多的分支，无限多的祖先-后代关系。这是一种典型的递归关系数据。

       对于树状结构的存储和展示：可以设计以下表来实现：

Create table article（

       Id      number primary key，

       Pid     number，

       Isleaf   number（1），  --0表示非叶子节点；1表示叶子节点； 便于查询

       Alevel   number（2）   --表示树状结构层级

）

查询：select a.* from article a join article ap on a.id = ap.pid where ap.id=4

利用存储过程实现树状结构的展示：

create or replace procedure p (vp article.pid%type,vl binary_integer)

 is

        cursor c is select * from article where pid=vp;

        vps varchar2(1024) :='';

begin

       for i in 1..vl loop

             vps := vps ||'***';

       end loop;

       for va in c loop

             dbms_output.put_line(vps ||va.cont);

             if(va.isleaf = 0) then

                    p(va.id,vl+1);

             end if;

       end loop;

end;

（一）for循环

数值型FOR循环之所以被称为数值型，原因在于它需要1个整数作为自己的终止值。其结构如下:

FOR  loop_counter  IN [REVERSE] lower_limit. .upper_limit  LOOP 

  statement 1; 

  statement 2; 

  ……

  statement n; 

END LOOP;

 

--特点：

--这是最常用的、最简单的循环

--1、循环计数器变量loop_counter是隐式声明的record记录变量。

--不需要你在declare部分声明

或者采用这种建表结构：

1 2 3 4 5 6

CREATE TABLE Comments( CId int PK, ArticleId int, CommentBody int, FOREIGN KEY(ArticleId) REFERENCES Articles(Id) )

父子关系表：

1 2 3 4 5 6 7

CREATE TABLE TreePaths( pid    int, id        int, PRIMARY KEY(ancestor,descendant),    --复合主键 FOREIGN KEY (ancestor) REFERENCES Comments(CommentId), FOREIGN KEY (descendant) REFERENCES Comments(CommentId) )

（二）oracle提供start with  connect by语句实现递归

1、oracle使用connect by prior实现递归查询，可查询树结构的所有记录：

语法：select * from table_name start with 条件1 connect by prior条件2 order by条件3；

（1）start with 条件1是指定遍历的起始条件；

注意：start with 在向下查询时，仅有一点不同，后接子节点列则查询会比后接父节点列多一条记录，多出来的一条记录就是起始节点列，即，后接子节点列会查出起始行，后接父节点列则不会查询出起始行，是直接查询子节点行开始；

（2）connect by 条件2是指定连接条件，其中prior和子节点在一起，就是向下查询；prior和父节点在一起，就是向上查询。

（3）order by 条件3对查询结果排序；

（三）Oracle查询实例

查询id为1的数据的所有子节点

select * from student

start with id=1

connect by prior id=pid

查询id为1的数据的所有父节点

select * from student

start with id=1

connect by id=prior pid

其中connect by的讲解

connect by 用于存在父子，祖孙，上下级等层级关系的数据表进行层级查询。

　　语法格式:
　　　　{ CONNECT BY [ NOCYCLE ] condition [AND condition]... [ START WITH condition ]
　　　　| START WITH condition CONNECT BY [ NOCYCLE ] condition [AND condition]...
　　　　}

　　特殊词讲解：

　　　　start with: 指定起始节点的条件

　　　　connect by: 指定父子行的条件关系

　　　　prior: 查询父行的限定符，格式: prior column1 = column2 or column1 = prior column2 and ... ，

　　　　nocycle: 若数据表中存在循环行，那么不添加此关键字会报错，添加关键字后，便不会报错，但循环的两行只会显示其中的第一条

　　　　循环行: 该行只有一个子行，而且子行又是该行的祖先行

　　　　connect_by_iscycle: 前置条件:在使用了nocycle之后才能使用此关键字，用于表示是否是循环行，0表示否，1 表示是

　　　　connect_by_isleaf: 是否是叶子节点，0表示否，1 表示是

　　　　level: level伪列,表示层级，值越小层级越高，level=1为层级最高节点

或者程序结构

public class TreeNode implements Serializable {

private Integer cid;

private String cname;

private Integer pid;

private List nodes = new ArrayList();

public TreeNode() {

}

//getter、setter省略

}

---------------测试程序----------------

public class TreeNodeTest {

@Test

public void loadTree() throws Exception{

System.out.println(JsonUtils.javaToJson(recursiveTree(1)));

}

/**

* 递归算法解析成树形结构

*

* @param cid

* @return

* @author jiqinlin

*/

public TreeNode recursiveTree(int cid) {

//根据cid获取节点对象(SELECT * FROM tb_tree t WHERE t.cid=?)

TreeNode node = personService.getreeNode(cid);

//查询cid下的所有子节点(SELECT * FROM tb_tree t WHERE t.pid=?)

List childTreeNodes = personService.queryTreeNode(cid);

//遍历子节点

for(TreeNode child : childTreeNodes){

TreeNode n = recursiveTree(child.getCid()); //递归

node.getNodes().add(n);

}

return node;

}

}