PostgreSQL存储过程用法实战详解

    本文实例讲述了postgreSQL存储过程用法。分享给大家供大家参考,具体如下:

    转了N多的SQL语句,可是自己用时,却到处是坑啊,啊,啊!!!!!!!!!!!!!!!

    想写一个获取表中最新ID值.

    上代码

    
    CREATE TABLE department(
      ID INT PRIMARY KEY             NOT NULL,
      d_code                   VARCHAR(50),
      d_name                   VARCHAR(50)   NOT NULL,
      d_parentID                 INT       NOT NULL DEFAULT 0
    );
    --insert into department values(1,'001','office');
    --insert into department values(2,'002','office',1);
    
    

    下面要写个存储过程,以获取表中ID的最大值:

    
    drop function f_getNewID(text,text);
    create or replace function f_getNewID(myTableName text,myFeildName text) returns integer as $$
    declare
      mysql text;
        myID integer;
    begin
      mysql:='select max( $1 ) from $2';
        execute mysql into myID using myFeildName,myTableName;
      if myID is null or myID=0 then return 1;
      else return myID+1;
       end if;
    end;
    $$ language plpgsql;
    --大家可以试一下,上面这个是会报错的
    --select f_getNewID('department','ID');
    --出错!
    
    

    看了官方文档,人家就是这么用的:

    
    EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
      INTO c
      USING checked_user, checked_date;
    
    

    你确定你看清楚了?????

    确定你读完读懂了说明书?????

    
    --这个看了?
    ---------------------------------------
    EXECUTE 'SELECT count(*) FROM '
      || quote_ident(tabname)
      || ' WHERE inserted_by = $1 AND inserted <= $2'
      INTO c
      USING checked_user, checked_date;
    
    
    
    --这个看了?
    ---------------------------------------
    EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = '
        || quote_literal(newvalue)
        || ' WHERE key = '
        || quote_literal(keyvalue);
    --=============================
    --好吧, 我改
    ------------------------------------------------------
    drop function f_getNewID(text,text);
    create or replace function f_getNewID(myTableName text,myFeildName text) returns integer as $$
    declare
      mysql text;
      myID integer;
    begin
      mysql:='select max('
        || quote_ident(myFeildName)
        ||') from '
        || quote_ident(myTableName);
      execute mysql into myID;
      --using myTableName,myFeildName;
    
      if myID is null or myID=0 then return 1;
      else return myID+1;
       end if;
    end;
    $$ language plpgsql;
    --==============================
    --漂亮,成功了!
    --But Why?
    --注意 对象(表名、字段名等)是不可以直接用变量的,要用 quote_ident()
    -------------------------------------------------------
    postgres=# select f_getnewid('department','ID');
    --错误: 字段 "ID" 不存在
    --第1行select max("ID") from department
            ^
    --查询: select max("ID") from department
    --背景: 在EXECUTE的第10行的PL/pgSQL函数f_getnewid(text,text)
    
    --===============================
    --什么情况,ID怎么会有双引号,引号,号,号???
    ----------------------------------------------------------
    --这里要感谢大神:权宗亮@飞象数据
    --改成这样:
    postgres=# select f_getnewid('department','id');
     f_getnewid
    ------------
         2
    (1 行记录)
    ----终于成功了!大小写还有区别吗??? --but why? --当在命令行输入
    CREATE TABLE role(
      ID                     INT PRIMARY KEY NOT NULL,
      r_name                   VARCHAR(50)   NOT NULL,
      r_paretnID                 INT       NOT NULL  DEFAULT 0
    );
    --结果在pgAdmin里看到的却是小写的
    
    

    
    --同样,如果是在QUERY TOOLS 下用这样的语句创建还是 所有的字体名为小写
    --如果我就想大写怎么办????
    --要这样写
    CREATE TABLE "RoleUPER"(
      "ID"                     INT PRIMARY KEY NOT NULL,
      r_name                   VARCHAR(50)   NOT NULL,
      "r_paretnID"                 INT       NOT NULL  DEFAULT 0
    );
    --再用大象看看
    
    

    可以了!

    总结一下:

    1、存储过程(FUNCITON)变量可以直接用  || 拼接。上面没有列出,下面给个栗子:

    
    create or replace function f_getNewID(myTableName text,myFeildName text) returns integer as $$
    declare
      mysql text;
        myID integer;
    begin
      mysql:='select max('|| $2 || ' ) from '||$1;
        execute mysql into myID using myFeildName,myTableName;
      if myID is null or myID=0 then return 1;
      else return myID+1;
       end if;
    end;
    $$ language plpgsql;
    
    

    2、存储过程的对象不可以直接用变量,要用 quote_ident(objVar)

    3、$1  $2是 FUNCTION 参数的顺序,如1中的 $1 $2交换,USING 后面的不换 结果 :

    
    select max(myTableName) from myFeildname
    

    4、注意:SQL语句中的大写全部会变成小写,要想大写存大,必须要用双引号。

    附:一个完整postgreSQL 存储过程示例

    
    CREATE OR REPLACE FUNCTION mt_f_avl_oee_period(i_station character varying,i_type int)
     RETURNS integer AS
    $BODY$
    DECLARE
     v_start_hour character varying;
     v_end_hour character varying;
     v_start_time character varying;
     v_end_time character varying;
     v_start_datetime timestamp;
     v_end_datetime timestamp;
     v_type int := 0;
     v_rtn int;
    /*
     v_test9_count int;
     v_test9_success int;
     v_runningtime double precision;
     v_availablerate double precision;
     */
    BEGIN
    -- hour = even, minute > 30
    -- exists
    --
    if i_type = 1 then
    SELECT EXTRACT(HOUR FROM CURRENT_TIME - interval '2 hours' ) into v_start_hour;  --two hours ago
    SELECT EXTRACT(HOUR FROM CURRENT_TIME ) into v_end_hour;  --get 'hour' of current time
    select v_start_hour || ':30' into v_start_time;
    select v_end_hour || ':30' into v_end_time;
    select mt_f_avl_oee_period_e(i_station, to_char( CURRENT_Date, 'YYYY-MM-DD'), v_start_time, v_end_time) into v_rtn;
    else
    SELECT EXTRACT(HOUR FROM CURRENT_TIME - interval '3 hours' ) into v_start_hour;
    SELECT EXTRACT(HOUR FROM CURRENT_TIME ) into v_end_hour;
    select v_start_hour || ':30' into v_start_time;
    select v_end_hour || ':00' into v_end_time;
    select mt_f_avl_oee_period_midnight(i_station, to_char( CURRENT_Date, 'YYYY-MM-DD'), v_start_time, v_end_time) into v_rtn;
    end if;
    RETURN 1;
    EXCEPTION
    WHEN others THEN
      RAISE;
    RETURN 0;
    END;
    $BODY$
     LANGUAGE plpgsql VOLATILE
     COST 100;
    ALTER FUNCTION mt_f_avl_oee_period(i_station character varying,i_type int)
     OWNER TO postgres;
    
    

    希望本文所述对大家PostgreSQL程序设计有所帮助。