PostgreSQL教程

PostgreSQL的存储过程简单入门

本文主要是介绍PostgreSQL的存储过程简单入门,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

一、存储过程结构:

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

                   $body$

                            //声明变量

                            Declare

                            变量名变量类型;

                            如:

                            flag Boolean;

                            变量赋值方式(变量名类型 :=值;)

                            如:

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

                            Begin

                                     函数体;

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

                            End;

                   $body$

         Language plpgsql;

二、变量类型 :

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

整数数据类型:

子类型

标准名

描述

Smalll integer

Smallint

一个2字节的符号型整数,可以存储-32768到32767的数字

Integer

Int

一个4字节的符号型整数,可以存储-2147483648到2147473647的数字

Serial

和integer一样,除了它的值通常是由PostgreSQL自动输入的。

浮点数据类型:(浮点数据也可以再细分,分为提供通用功能的浮点值和固定精度的数字)

子类型

标准名

描述

float

float(n)

支持最少精度为n,存储为最多8字节的浮点数。

float8

real

双精度(8字节)浮点数字

numeric

numeric(p,s)

拥有p个数字的实数,其中小数点后有s位。不像float,这始终是一个确切的数字,但工作效率比普通浮点数字低。

money

numeric(9,2)

PostgreSQL特有的类型,但在其他数据库里也普遍存在。Money类型从PostgreSQL 8.0开始不赞成使用,且可能在以后版本中取消。你应该使用number类型代替。

注:

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

时间数据类型:

定义

意义

date

存储日期信息

time

存储时间信息

timestamp

存储日期和时间

interval

存储timestamp之间差别的信息

timestamptz

PostgreSQL扩展的类型,存储包含时区信息的timestamp

特殊数据类型:

定义

意义

box

矩形盒子

line

一组点

point

一对几何学的数字

lseg

一条线段

polygon

一条封闭的几何线

cidr或inet

一个IPv4的地址,录入192.168.0.1

macaddr

以MAC地址(以太网卡物理地址)

注: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

[ <<label>> ]
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();

这篇关于PostgreSQL的存储过程简单入门的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!