文档章节

PostgreSQL的存储过程简单入门

我爱睡觉
 我爱睡觉
发布于 2017/06/24 16:06
字数 1956
阅读 19
收藏 0

一、存储过程结构:

         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类型代替。

注:

存储floatreal类型的数据的行为非常相似,但是numeric列的行为有点不同。Numeric类型不是存储接近的数,而是在小数后面进行后超出固定长度的部分进行四舍五入。如果我们存储太大的数据到其中,INSERT将失败。还要注意floatreal也会对数字四舍五入;例如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();

 

本文转载自:http://blog.csdn.net/rachel_luo/article/details/8073458

我爱睡觉
粉丝 3
博文 2120
码字总数 0
作品 0
南昌
私信 提问
PostgreSQL入门篇学习笔记(一)

PostgreSQL最强大的开源数据库,以前一直以为是mysql。可谓无知者,无畏也。井底之娃,一个八线城市的python运维dba的悲哀。 以下把PostgreSQL简称为pg。 1,pg是唯一能做到数据零丢失的开源...

落叶刀
2015/11/21
293
0
PostgreSQL 与 MySQL 相比,优势何在?

一、 PostgreSQL 的稳定性极强, Innodb 等引擎在崩溃、断电之类的灾难场景下抗打击能力有了长足进步,然而很多 MySQL 用户都遇到过Server级的数据库丢失的场景——mysql系统库是MyISAM的,相...

leycau
2015/06/29
346
0
PostgreSQL DBA快速入门(一) - 准备和部署

PostgreSQL DBA快速入门(一): 准备和部署 MySQL和PostgreSQL - 两大开源对象关系型数据库管理系统,每一个都有大量的粉丝。这么多年来,在PostgreSQL社区中MySQL一直被消遣:默认松散的SQL...

闻术苑
2018/07/30
234
0
PostgreSQL DBA快速入门(四) - 体系架构

PostgreSQL在开源关系型数据库市场是最先进的数据库。他的第一个版本在1989年发布,从那时开始,他得到了很多扩展。根据db-enginers上的排名情况,PostgreSQL目前在数据库领域排名第四。 本篇...

闻术苑
2018/08/02
251
0
PostgreSQL安装详细步骤(windows)

PostgreSQL安装: 一、windows下安装过程 安装介质:postgresql-9.1.3-1-windows.exe(46M),安装过程非常简单,过程如下: 1、开始安装: 2、选择程序安装目录: 注:安装 PostgreSQL 的分...

引鸩怼孑
2015/04/21
359
1

没有更多内容

加载失败,请刷新页面

加载更多

OpenStack 简介和几种安装方式总结

OpenStack :是一个由NASA和Rackspace合作研发并发起的,以Apache许可证授权的自由软件和开放源代码项目。项目目标是提供实施简单、可大规模扩展、丰富、标准统一的云计算管理平台。OpenSta...

小海bug
昨天
6
0
DDD(五)

1、引言 之前学习了解了DDD中实体这一概念,那么接下来需要了解的就是值对象、唯一标识。值对象,值就是数字1、2、3,字符串“1”,“2”,“3”,值时对象的特征,对象是一个事物的具体描述...

MrYuZixian
昨天
6
0
数据库中间件MyCat

什么是MyCat? 查看官网的介绍是这样说的 一个彻底开源的,面向企业应用开发的大数据库集群 支持事务、ACID、可以替代MySQL的加强版数据库 一个可以视为MySQL集群的企业级数据库,用来替代昂贵...

沉浮_
昨天
7
0
解决Mac下VSCode打开zsh乱码

1.乱码问题 iTerm2终端使用Zsh,并且配置Zsh主题,该主题主题需要安装字体来支持箭头效果,在iTerm2中设置这个字体,但是VSCode里这个箭头还是显示乱码。 iTerm2展示如下: VSCode展示如下: 2...

HelloDeveloper
昨天
9
0
常用物流快递单号查询接口种类及对接方法

目前快递查询接口有两种方式可以对接,一是和顺丰、圆通、中通、天天、韵达、德邦这些快递公司一一对接接口,二是和快递鸟这样第三方集成接口一次性对接多家常用快递。第一种耗费时间长,但是...

程序的小猿
昨天
11
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部