oracle存储过程学习

一、存储过程:

     概念:感觉就是让查询数据库的增删改查单一的调用 , 添加入if...then...else等逻辑及程序块使得数据库的操作富有逻辑性.其中主要的为: 1)判断语句: if...then...else   2)循环语句: for while等  3)块结构: 应该是对应java中的方法   4)命名变量  5)命名过程: 应该对应的是java的类.

***个人感觉就是将以往单一的数据库操作 , 现在可以使得富有逻辑性的一连串的数据库操作变成一条sql即可避免了数据库的频繁调用. 而且对于存储过程的写法就应该像java的方法一样,它是定义一种富有逻辑性的对数据库的操纵***

 

二、存储过程例子1：

   create procedure add_cust(c_name in varchar(20) , c_num  in integer)

           as   //表示为实现

               declare   //声明变量关键字

                       tempvar1  number(16,2);     //变量1

                       tempvar2  varchar(5);          //变量2

               begin    //语句块的开始

               select sum(cust_num) into tempvar1 from customers;  //从表中查询数据设入变量内  into关键字

               tempvar2 := 'this is temp var'   //   := 是分配标示,可理解为赋值.

               insert into customers( cust_name , cust_num) values (c_name , c_num);

               update salesreps set s_c_no = c_num where s_c_name = c_name ;

               commit;//提交    

           end;   //语句块的结束

***   上述为创建存储过程 , 在控制台查看时可以execute add_cust('***' , 123)   ,在存储过程中调用其他存储过程用call关键字进行调用.   ***

 

三、语句块:

    可以看做方法的实现部分完整格式如下:[]表示可选择 

   [declare]...设置变量...

   begin   ...开始标示..

   exception  ...异常处理区域...

   end   ...完结标示...

 

四、函数:

     函数与存储过程的唯一差别是函数是有返回值的,而存储过程无返回值.同时sql语句内可以使用函数作为条件判断.

     create function get_total(c_num in integer)   return number(16 , 2)  //return标示返回的类型

        as    declare   totaltemp number(16 , 2);

        begin   select sum(amount)  into totaltemp  from orders where cust = c_num;

        return totaltemp ; end;   //返回值 注意返回值不是必须为变量可以使sql操作的的返回数据.

 

**select * from customers where get_total(custom_num) //sql是可以直接调用函数的. ***

 

五、in out和inout输入输出参数:

     in标示输入参数, 而当存储过程想返回一个或多个数值时将用到输出参数 见例:

        create procedure get_infor(var1 in integer , var2 out varchar(16) , var3 out integer)

          as  begin   select count(city) , city into var3，var2 from world where country = var1 groupby city

        end;

**调用时**

         declare  citytemp , citycounter;

         execute get_infor(5 , citytemp, citycounter)  //注意out必须是参数不能是值,而in是必须是值或带值的参数.

**对于inout可以看做给出初始值的out类型参数***

 

六、条件执行

   if  条件  then  条件成了执行语句  else  条件不成立执行语句   //可以嵌套没什么区别 注意elseif关键字

 

七、循环执行

   例1:  for item_count in 1..10 loop  //定义循环10次

               ......

             exit when(item_count = specialNumber);   //定义特殊值离开循环

          end loop; //结束标识

 

   例2:   loop

                  ...

              exit when(.....);

            end loop;

   

   例3：  while(...条件...)

                loop       .....   end  loop

***跳出语句有 exit for ; exit while ; exit foreach及continue对应的三个,意义与java一致 , 同时oracle支持goto式带标识符( XXX:)的离开方式   ***  

 

八、异常处理:

格式:    exception

           when no_data_found  //该异常是oracle自带的异常.其他可以查阅. 

           then  ....操作...

           when  others //其他异常捕获

           then   ...操作... 

 九、触发器:

   它是一种特殊的存储过程,它是当表发生insert,update等操作时oracle自动调用的存储过程.

   一般格式:

       例1： create trigger upd_test

            before insert on 表名 //before标识插入前执行该触发器,after则标识插入后执行.insert可以被update等换

               for each row //每个列

               when(post . 列名 is not null)  //这里post标识过去将被替代的数据,  pre则为新的数据

            begin

               ...具体操作..

            end;

        例2：create trigger upd_test

            before update of 列名 on 表明

               for each row //每个列

               when(new.quota is not null)  //new.quota标识新的待入库的数据

            begin

               ...具体操作..

            end;