【PLSQL】变量声明,结构语句,cursor游标

************************************************************************
  ****原文:blog.csdn.net/clark_xu 徐长亮的专栏
************************************************************************

         PLSQLORACLE在标准SQL基础上增加了过程化处理,把DMLSELECT语句组织在PLSQL代码的过程性单元中。

         PLSQL扩展了变量和类型,控制语句,过程和函数。

1.1 PLSQL程序结构

PLSQL块包括:

         声明部分:DECLARE

         执行部分:begin

         异常处理:exception

         例如:

         declare

                   v_AccountID number(5) :=1001;

                   v_RealName varchar(20)

         Begin

                   select real_name into v_RealName

                   from account where id=v-Accountid

         exception

                   when no_data_found then

                            insert into Fee_log(descr)

                            values(“account 1001 does not exist”)

                            commit;

                   end;

1.2 PLSQL运行过程

客户端应用程序PLSQL-----PLSQL引擎(过程性语句执行器)----sql语句执行器

注释:

         单行注释:——

         多行注释:/*  */

1.3变量和数据类型

标量类型包括:数字型,字符型,日期型,布尔型

复杂类型包括:recordassociative array,nested table, varray

1.3.1 标量类型

数字类型:

u  number

u  number的子类型dec(38),float(38),real(38)

u  binary_integer(只能用于plsql)

字符型:

u  varchar2,varchar

u  string(只能用于plsql)

u  char

u  long

Date

Boolean

u  用于存储逻辑值

u  不能像数据库中插入boolean类型的数据

u  不能讲列植保存到boolean变量中;

u  只能对boolean变量执行逻辑操作;

1.3.2变量声明

         语法:

         var_name type [constant] [not null] [:=value];

         PLSQL规定没有初始化的变量为NULL

         例如:

                   declare

                            v_realName varchar2(20);

                           v_baseCost number(7,2) :=5.9;

                            v_count binary_integer :=0;

1.3.3 %TYPE方式

变量具有和数据库的表中某列或其他变量相同的类型;

declare v_realName varchar2(20);

declare v_realName account.real_name%TYPE;

declare

v_tempvar number(7,3) not null :=12.3;

v_anothervar v_tempvar%type =12.3;

1.4 条件语句

if Boolean_expression1 Then

ELSIF Boolean_expression2 then

ELSE

END IF;

1.5 Loop循环语句

loop

         statement1;

         statement2;

                   exit when condition

end loop;

 

1.6 while循环语句

while Boolean_expresstion loop

         statement1;

         statement2;

end loop;

1.7 for循环语句

for 循环器 in下限。。上限 loop

         statement1;

         statement2;

                   exit when condition

end loop;

1.8 PLSQL中的DML

可以在PLSQL中石油标准SQL

1.       DML

2.       事务控制语句TCL

例如,begin

                   insert into host(id) values(’10.0.2.1’);

                   commit;

          end;

 

PLSQL中本地动态SQL是使用execute immediate语句来执行ddl语句

例如,begin

                   execute immediate ‘CREATE TABLE TEST(c1 number)’;

          end;

DML语句使用本地动态sql来实现的(使用execute immediate语句实现)

例如,begin

                   execute immediate ‘insert into test values (1)’;

           end;

sql语句的编译发生在PLSQL语句块的编译阶段;

sql语句的执行发生在PLSQL语句的执行阶段;

         所有一般这样写:

                   begin

                            execute immediate ‘CREATE TABLE TEST(c1 number)’;

                            execute immediate ‘insert into test values (1)’;

                   end;

                   此时PLSQL编译的时候,create语句insert语句为字符串,不需要sql语句编译;

         因此,PLSQL语句完成建表和插入数据的时候,必须使用execute immediate;

1.9 PLSQL语句中的SELECT语句

根据select语句返回的记录数,实现分为两类:

         当仅返回一条记录的时候:

                   select … into …语句实现;

         当返回0条或者多条记录的时候

                   cursor指针来实现

若结果是单行单列,into字句后用标量类型,与字段类型相同;

若查询结果为单行多列,into子句后的变量个数,顺序,数据类型和select语句后面的目标匹配,也可以用记录record类型类记录;

1.9.1 record类型

语法定义:

         type t_cout_rec is record             //记录类型为t_cost_rec

         (        base_cost cost.base_cost%type,

                   base_duration cost.base_duration%type;

         unit_cost cost.unit_cost%type

)

 

v_cost t_cost_rec;

v_cost_1 t_cost_rec;

1.9.2 %ROWTYPE

         用表结构和视图结果定义变量,record类型;record成员的名称和类型与表或者视图的列名称和类型完全相同;

         例如,v_cost cost%ROWTYPE;

1.9.3 record变量的引用

         begin

                   v_cost.base_cost:=5.9

                   v_cost.base_duration:=20;

                   v_cost.unit_cost:=0.4

                   v_cost_1=:=v_cost;

                   select base_cost,base_duration,unit_cost int v_cost_1 from cost;

         end;

        

         inert语句和update语句中使用记录类型变量

         例如,假设cost_t1表事先存在

                   begin

                            insert into cost_t1 values v_cost;

                            update cost_t1 set row=v_cost_1;

                            commit;

                   end;

1.10 cursor游标

根据select语句返回的记录数,若返回记录数为0条或者多条用cursor实现;

         oracle所知晓的每一个sql语句都有唯一的cursor与之想对应;

         oracle使用专用的sql工作去来执行sql语句,存储处理信息,这个工作去称为“cursor”

cursor分为隐式cursor和显式的cursor

         隐式cursor:

                   --select .. into语句

                   --DML语句

1.10.1 显式cursor的处理:

         declare声明游标:床和命名一个sql工作区;

         open打开游标

         fetch提取游标

         判断是否empty:结果集中是否有未提取的记录;

         close关闭游标

1.10.2 显式cursor的属性:

         %isopen, 布尔类型,

         %notfound,布尔类型,如果前一个fetch语句没有返回一行记录,其值为true;

         %found,布尔,如果前一个fetch语句返回记录,其值为true,与%notfound;

         %rowcount,数值,到目前为止,cursor已提取的总行数;

1.10.3 隐式cursor的属性

sql%isopen, 布尔类型,DML执行中未true,结束后为false

sql%notfound,布尔类型,与sql%found返回值相反

sql%found,       布尔类型,值为trueDML操作成功

sql%rowcount, 数值类型,表示DML语句成功执行的数据行数;

1.10.4 cursor的使用

声明

在游标声明中使用标准的select语句,如果需要按照指定的次序处理行,可在查询中使用order by字句;可以在查询中引用变量,但是必须在cursor语句之前声明这些变量

         cursor c_service_id(p_cost_id number) is

         select id from service where cost_id=p_cost_id;

open

通过open cursor来执行select语句并标识结果集;select语句如果没有返回记录,不会出现异常语法;

         open c_service_id(5);

fetch cursor   

检索当前行,把值赋给变量;变量可以是record类型或者简单变量;如果是简单变量,要求如下:包含相同数量的变量;把每一个变量和相应的列进行位置匹配。通过循环检测cursor中是否包含数据行;

         fetch cursor_name into [var1,var2,…| recore_name];

         fetch c_service_id into v_service_id;

1.10.5 结果集提前的控制

使用循环处理显式cursor结果集的多行数据;每次fetch一行,反复进行;使用%notfound属性检测一次不成功的提前操作;使用显式cursor的属性检测每一次的提前是否成功,避免出现无限循环;

1.10.6 关闭游标

处理完结果集中的是数据后,应该关闭cursor;如果需要,可以再次打开该curosr

cursor一旦关闭,所有和该cursor相关的资源都会被释放,不可以再从关闭的cursor中提前数据,可以引起invalid_cursor错误;

         close c_service_id;

************************************************************************
  ****原文:blog.csdn.net/clark_xu 徐长亮的专栏
************************************************************************

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。