PL/SQL 基础

Pl/sql 学习

Pl/sql：oracle的过程性sql语言，是交互式sql的过程化扩展。它提供了一种执行程序逻辑，声明内存变量和执行循环条件，且同时可以在任何地方使用sql访问数据库的方式。Pl/sql的内存变量同样可被用作sql的search_condition中出现的常量的位置。

一个pl/sql程序块由三部分组成：

1）  定义内存变量的DECLEAR部分；

2）  可执行语句的BEGIN-END部分

3）  执行语句之后，END之前可能出现第三部分EXCEPTION部分。

示例：

Declare

       I        integer                      --local variable without initial value

       Total  integer                      --local variable with initial value

Begin

       For I in 1..100 loop

              Total := total+I;

       End loop

       Insert into result (rvalue) values (total);        --insert answer into database table result

End;

程序通过循环从1-100相加，并储存结果到result表中的rvalue列。

注释：--开头，同于标准sql

赋值操作符： :=，而=用于比较，同于标准sql

简单的循环结构：

For counter in[reverse] lower_bound..higher_bound loop

       --sequence of statements

End loop

循环也可以因为某种选定条件而结束，无需到达计数限制才结束。循环结构如下：

Loop

       --sequence of statement

       --exit loop at any point with: exit when condition

       Exit when condition(i>100)

End loop

 

程序逻辑也可以放在pl/sql函数中，然后在pl/sql程序块中执行。

示例：

Create function sum_n(n integer) return integer is

       I        integer;

       Total  integer := 0;

Begin

       For I in 1..n loop

              Total := total+i;

       End loop

       Return total;           --return result to sql or pl/sql caller

End;

在形如create function语句执行之后，函数将像表或视图一样作为长期对象存在。可以被带任何整数表达式的参数的sql语句调用，例如：

Select sum_n(10) from orders; --这个语句没意义，仅仅为了满足语法要求，返回多个值，每个值对应orders中的一行

Select qty,sum_n(qty),2*sum_n(qty*qty) from orders where aid = “a 04” ;

--在函数sum_n的表达式中使用实际的列值。

在pl/sql中declare中可以使用任意oracle中的列类型。

在pl/sql函数中，不能有对数据库作任何变动的操作，但是可以有select语句，因为select语句不会对数据库引起变动。

示例：

Create function name_city(custid char) return char is

       Custname char(20);

       Custcity char(20);

Begin

       Select cname,city into custname,custcity from customers where cid = custid;

       Return rtrim(custname)||’(’||rtrim(custcity)||’)     --trim extra space at right;

End;

函数以name(city)的形式返回。

在第一行为参数和返回指定字符串类型的长度是不对的，因为系统将自行调整长度。

该函数使用了单个行选择，通过“游标”，pl/sql还可以处理多个行的选择。

 

Pl/sql函数没有使用传值调用，即参数为实际传入值本身，这使得在函数体内不能改变参数的值，若有试图这样的改变将引起错误。所以pl/sql函数的参数只读。

 

对象-关系模型，对象构造器，计算表中的行对象时，必须使用value()的格式。

如：update people p set p=inc_age(value(p)) where p.age<40;

 

if-else语句结构：

If condition then

       Statements

[Else

       Statements]

End if

 

在oracle中用pl/sql实现方法：

创建对象的语法：

CREATE TYPE typename AS OBJECT

(attrname datatype {,attrname datatype …}

MEMBER FUNCTION methodname [(param type {,param type …})]

              RETURN datatype,

       {,MEMBER FUNCTION methodname [(param type{,param type …})]

              RETURN datatype …});

 

示例：

定义对象point_t和rectangle_t。为rectangle_t定义两个方法：

Area()和inside(point_t)

Create type point_t as object（

       X int,

       Y int

);

Create type rectangle_t as object (

       Pt1 point_t,            --lower left

       Pt2 point_t,            --upper right

       Member function inside(p point_t)

              Return int,

       Member function area

              Return int

);

 

Create type语句仅包含可以调用方法的代码所需的信息，具体的执行逻辑独立出现的create type body中。

 

创建point_t和rectangle_t类型的对象表，并插入一些值:

Create table points of point_t (primary key (x,y));

Create table rects of rectangle_t(primary key (pt1.x,pt1.y,pt2.x,pt2.y));

Insert into rects values (point_t(1,2),point_t(3,4));

Insert into points values (2,3);

查询points表中所有在rects表中每个矩形内部的点：

Select distinct p.x,p.y from points p,rects r where r.inside(value(p)) > 0;

Value(p)用法不明。

 

Create type body语句用于定义方法的执行逻辑：

Create type body rectangle_t as

       Member function area return int is               --all logic is pl/sql

       Begin

              Return (self.pt2.x-self.pt1.x) * (self.pt2.y-self.pt1.y);

       End;

       Member function inside(p in point_t) return int is

       --inside(point_t)方法体略，使用if-else语句判断。

End;

注：self参数指代操作对象本身，也是只读的。

       CREATE [OR REPLACE] TYPE BODY type –这里可选的or replace表示在再一次创建一个类型之前可以不用drop type body。