如何理解postgresql的存储过程

在b/s系统的构建过程中，数据库操作几乎成为了一个必不可少的操作。调用存储过程实现数据库操作使很多程序员使用的方法，而且大多数的程序员都是能使用存储过程就使用存储过程，很少直接使用sql语句，所以存储过程是很有用而且很重要的。

存储过程简介

存储过程的定义：完成一定功能的可重复调用的程序

简单的说，存储过程是由一些sql语句和控制语句组成的被封装起来的过程，它驻留在数据库中，可以被客户应用程序调用，也可以从另一个过程或触发器调用。它的参数可以被传递和返回。与应用程序中的函数过程类似，存储过程可以通过名字来调用，而且它们同样有输入参数和输出参数。

根据返回值类型的不同，我们可以将存储过程分为三类：返回记录集的存储过程，返回数值的存储过程（也可以称为标量存储过程），以及行为存储过程。顾名思义，返回记录集的存储过程的执行结果是一个记录集，典型的例子是从数据库中检索出符合某一个或几个条件的记录；返回数值的存储过程执行完以后返回一个值，例如在数据库中执行一个有返回值的函数或命令；最后，行为存储过程仅仅是用来实现数据库的某个功能，而没有返回值，例如在数据库中的更新和删除操作。

存储过程的通俗解释：你使用手机拨打A同事的手机，需要一个一个号码的输入，然后才能拨打
而如果你把这个号码设置为快速拨号，那么你只要长按1(自己设置的数字键)就可以直接拨打电话了
把这个号码设置为快速拨号的过程你就可以理解为创建存储过程。

使用存储过程的好处

相对于直接使用sql语句，在应用程序中直接调用存储过程有以下好处：

(1)减少网络通信量。调用一个行数不多的存储过程与直接调用sql语句的网络通信量可能不会有很大的差别，可是如果存储过程包含上百行sql语句，那么其性能绝对比一条一条的调用sql语句要高得多。

(2)执行速度更快。有两个原因：首先，在存储过程创建的时候，数据库已经对其进行了一次解析和优化。其次，存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用。

(3)更强的适应性：由于存储过程对数据库的访问是通过存储过程来进行的，因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动，而这些改动不会对应用程序造成影响。

(4) 布式工作：应用程序和数据库的编码工作可以分别独立进行，而不会相互压制。

由以上的分析可以看到，在应用程序中使用存储过程是很有必要的。

存储过程的定义规范

一，存储过程的格式：

Create or replace function 过程名(参数名 参数类型,…..) returns 返回值类型 as
                   $body$
 
                            //声明变量
                            Declare
                            变量名变量类型；
                            如：
                            flag Boolean;
 
                            变量赋值方式（变量名类型 ：=值；）
                            如：
                            str  text :=值; / str  text;  str :=值；
 
                            Begin
                                     函数体；
 
                             return 变量名； //存储过程中的返回语句
 
                            End;
                   $body$
         Language plpgsql;

二，变量类型：
有整数类型(Smallint,Int等)，浮点类型(float)，时间类型(date,time,timestamp,interval)，还有其他特殊类型比如inet，point等。
三，连接字符：
Postgresql存储过程中的连接字符使用的是"||"
四，控制结构：
使用LOOP，EXIT，CONTINUE，WHILE， 和FOR 语句，可以控制PL/pgSQL 函数重复一系列命令。需要使用的时候请查详细资料。
五，异常捕获：

EXCEPTION
WHEN 错误码（如：STRING_DATA_RIGHT_TRUNCATION：字串数据右边被截断） THEN
        /**后台打印错误信息*/
        RAISE NOTICE '错吴信息';

或者错误码为UNDEFINED_TABLE时可以执行创建表的操作然后在入数据。

存储过程的示例

例子1：

/**
批量插入一批数据，经纬度字段值要满足中国地理位置上的经纬度范围；
注：时间不能指定为同一时间，否则会扫描全表，导致性能低下。下列脚本未考虑时间的分段，采用的一个时间点。
*/
create or replace 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();

例子2：此例子是审计的生产环境的例子

-- 存储过程
CREATE OR REPLACE FUNCTION audit_event_partition_trigger()
RETURNS TRIGGER AS $$
DECLARE date_text TEXT;
DECLARE end_date_text TEXT;
DECLARE insert_statement TEXT;
BEGIN
    SELECT to_char(NEW.time_event, 'YYYY_MM_DD') INTO date_text;
    SELECT to_char(NEW.time_event + INTERVAL '1 DAY', 'YYYY_MM_DD') INTO end_date_text;
    insert_statement := 'INSERT INTO audit_event_' || date_text || ' VALUES($1.*)';
    EXECUTE insert_statement USING NEW;
    RETURN NULL;
    EXCEPTION
    WHEN UNDEFINED_TABLE
    THEN
        EXECUTE 
            'CREATE TABLE IF NOT EXISTS audit_event_'  || date_text || '(CHECK ( time_event >= ''' || date_text || ''' AND  time_event < '''|| end_date_text ||''' )) INHERITS (audit_event)';
        RAISE NOTICE 'CREATE NON-EXISTANT TABLE audit_event_%', date_text;
        EXECUTE
            'CREATE INDEX audit_event_' ||  date_text || '_event_msg_body ON audit_event_'  || date_text || ' USING gin(event_msg_body gin_trgm_ops); ' 
            || 'CREATE INDEX audit_event_' || date_text || '_gid ON audit_event_'  || date_text || ' USING btree(gid);' 
            || 'CREATE INDEX audit_event_' || date_text || '_id ON audit_event_'  || date_text || ' USING btree(id);' 
            || 'CREATE INDEX audit_event_' || date_text || '_module ON audit_event_'  || date_text || ' USING btree(module);' 
            || 'CREATE INDEX audit_event_' || date_text || '_access_time ON audit_event_'  || date_text || ' USING btree(access_time);' 
            || 'CREATE INDEX audit_event_' || date_text || '_mid ON audit_event_'  || date_text || ' USING btree(mid);' 
            || 'CREATE INDEX audit_event_'  || date_text || '_time_event ON audit_event_' || date_text || ' USING btree(time_event);';
        EXECUTE insert_statement USING NEW;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql

这个是由触发器来引发执行的存储过程：

DROP TRIGGER IF EXISTS auto_insert_into_audit_event_tbl_partiton ON audit_event;
CREATE TRIGGER  auto_insert_into_audit_event_tbl_partiton BEFORE INSERT OR UPDATE ON  audit_event  
FOR EACH ROW
EXECUTE PROCEDURE audit_event_partition_trigger()

reference
对存储过程的一些理解
简单通俗的解释一下存储过程是干什么的
分表分库设计