PostgreSQL

最新推荐文章于 2023-09-23 13:40:31 发布

On_the_orad

最新推荐文章于 2023-09-23 13:40:31 发布

阅读量428

点赞数

分类专栏： PostgreSQL 文章标签： postgresql 存储过程

PostgreSQL 专栏收录该内容

0 篇文章 0 订阅

订阅专栏

一、存储过程结构：

     Create or replace function 过程名(参数名 参数类型,…..) returns 返回值类型 as

               $body$



                        //声明变量

                        Declare

                        变量名变量类型；

                        如：

                        flag Boolean;



                        变量赋值方式（变量名类型 ：=值；）

                        如：

                        str  text :=值; / str  text;  str :=值；



                        Begin

                                 函数体；



                         return 变量名； //存储过程中的返回语句



                        End;

               $body$

     Language plpgsql;

二、变量类型：

除了postgresql内置的变量类型外，常用的还有 RECORD ，表示一条记录。

整数数据类型：
这里写图片描述
浮点数据类型：（浮点数据也可以再细分，分为提供通用功能的浮点值和固定精度的数字）

注：存储float和real类型的数据的行为非常相似，但是numeric列的行为有点不同。Numeric类型不是存储接近的数，而是在小数后面进行后超出固定长度的部分进行四舍五入。如果我们存储太大的数据到其中，INSERT将失败。还要注意float和real也会对数字四舍五入；例如123.456789被四舍五入为123.457。

时间数据类型：
这里写图片描述
特殊数据类型：

注：PostgreSQL也允许你使用SQL命令CREATE TYPE在数据库中建立你自己的类型。这通常不需要，而且在一定程度上，它是PostgreSQL独有的。

数组

通常，一个数组需要通过使用一个附加表实现。但是，数组的能力有时候很有用。建立数组的方法有两种：传统的PostgreSQL的方法和SQL99标准的方法。

PostgreSQL样式的数组
要将一个表的列定义为数组，你可以简单地在类型后面添加[]；不需要定义元素的个数。即使定义了个数，也不会强制要求存储的个数。

Eg：

test=> CREATE TABLE empworkday (

test(> refcode char(5),

test(> workdays int[]

test(> );

往数组列中插入值：

test=> INSERT INTO empworkday VALUES(‘val01′,‘{0,1,0,1,1,1,1}’);

test=> INSERT INTO empworkday VALUES(‘val02′,‘{0,1,1,1,1,0,1}’);

SQL99样式的数字

在SQL99标准中，必须指出元素的个数。

Eg:

test=> CREATE TABLE empworkday (

test(> refcode char(5),

test(> workdays int array[7]

test(> );

test=> INSERT INTO empworkday VALUES(‘val01′,‘{0,1,0,1,1,1,1}’);

test=> INSERT INTO empworkday VALUES(‘val02′,‘{0,1,1,1,1,0,1}’);

三、连接字符：

     Postgresql存储过程中的连接字符不再是“+”,而是使用“||”。

四、控制结构：

1、if 条件（五种形式）

IF … THEN

IF … THEN … ELSE

IF … THEN … ELSE IF

IF … THEN … ELSIF … THEN … ELSE

IF … THEN … ELSEIF … THEN … ELSE（注：ELSEIF 是ELSIF 的别名）

2、循环

使用LOOP，EXIT，CONTINUE，WHILE，和FOR 语句，可以控制PL/pgSQL 函数重复一系列命令。

1）、LOOP

[ <> ]

LOOP

statements

END LOOP [ label ];

LOOP 定义一个无条件的循环，无限循环，直到由EXIT或者RETURN语句终止。可选的label 可以由EXIT 和CONTINUE 语句使用，用于在嵌套循环中声明应该应用于哪一层循环。

2）、EXIT

EXIT [ label ] [ WHEN expression ];

如果没有给出label， 那么退出最内层的循环，然后执行跟在 END LOOP 后面的语句。 如果给出 label， 那么它必须是当前或者更高层的嵌套循环块或者语句块的标签。然后该命名块或者循环就会终止，而控制落到对应循环/块的 END 语句后面的语句上。

如果声明了WHEN，循环退出只有在expression 为真的时候才发生，否则控制会落到EXIT 后面的语句上。

EXIT 可以用于在所有的循环类型中，它并不仅仅限制于在无条件循环中使用。在和 BEGIN 块一起使用的时候，EXIT 把控制交给块结束后的下一个语句。

例如:

Loop 循环

If … then 条件判断

Exit ; 条件成立，则退出循环。

End if;

End loop;

3）、CONTINUE

CONTINUE [label ] [ WHENexpression ];

如果没有给出 label，那么就开始最内层的循环的下一次执行。也就是说，控制传递回给循环控制表达式（如果有），然后重新计算循环体。如果出现了label，它声明即将继续执行的循环的标签。

如果声明了 WHEN，那么循环的下一次执行只有在expression 为真的情况下才进行。否则，控制传递给CONTINUE 后面的语句。

CONTINUE 可以用于所有类型的循环；它并不仅仅限于无条件循环。

例如:

LOOP

一些计算

EXIT WHEN count > 100;

CONTINUE WHEN count < 50;

一些在count 数值在 [50 .. 100] 里面时候的计算

END LOOP;

4）、WHILE

[ <<label>> ]

WHILE expression LOOP

    statements

END LOOP [ label ];

只要条件表达式为真，WHILE语句就会不停在一系列语句上进行循环. 条件是在每次进入循环体的时候检查的.

例如:

WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP

-- 可以在这里做些计算

END LOOP;

WHILE NOT BOOLEAN_expression LOOP

-- 可以在这里做些计算

END LOOP;

5）、FOR（整数变种）

[ <<label>> ]

FOR name IN [ REVERSE ] expression .. expression LOOP

    statements

END LOOP [ labal ];

这种形式的FOR对一定范围的整数数值进行迭代的循环。变量name 会自动定义为integer类型并且只在循环里存在。给出范围上下界的两个表达式在进入循环的时候计算一次。迭代步进值总是为 1，但如果声明了REVERSE就是 -1。

一些整数FOR循环的例子∶

FOR i IN 1..10 LOOP 表示1循环到10

这里可以放一些表达式

RAISE NOTICE 'i IS %', i;

END LOOP;

FOR i IN REVERSE 10..1 LOOP

这里可以放一些表达式

END LOOP;

如果下界大于上界（或者是在 REVERSE 情况下是小于），那么循环体将完全不被执行。而且不会抛出任何错误。

3、异常捕获

    EXCEPTION

WHEN 错误码（如：STRING_DATA_RIGHT_TRUNCATION：字串数据右边被截断） THEN

    /**后台打印错误信息*/

    RAISE NOTICE '错吴信息';

五、示例代码：

/**

批量插入一批数据，经纬度字段值要满足中国地理位置上的经纬度范围；

注：时间不能指定为同一时间，否则会扫描全表，导致性能低下。下列脚本未考虑时间的分段，采用的一个时间点。

create orreplace function intobatch() returns integer as

$body$

declare

skyid integer;

lot float;

     lat float;

     sex varchar;

     level integer;

     ctime int :=1325404914;

     num integer :=0;

     total integer :=0;

begin

               lot='73.6666666';

               lat='3.8666666';

               FOR skyid IN 404499817 ..404953416 loop

                         if(lot > 135.0416666) then

         lot=73.6666666;

                         end if;

                    if(lat > 53.5500000) then

         lat=3.8666666;

                         end if;

                         if(skyid%2 <> 0) then

                                                    sex='1';

                                                    level=0;

                         else

                                                    sex='2';

                                                    level=1;

                         end if;



                        INSERT INTO user_last_location(user_id,app_id,lonlat,sex,accurate_level,lonlat_point,create_time)

VALUES(skyid,2934,ST_GeomFromText(‘POINT(‘||lot||’ ‘||lat||’)’,4326),sex,level,POINT(lot,lat),to_timestamp(ctime));

                        lot=lot+0.1;

                        lat=lat+0.1;

                        skyid=skyid+1;



    end loop;        

               return skyid;  

end

$body$

languageplpgsql;

SELECT *from intobatch();