精华内容
下载资源
问答
  • Postgresql存储过程

    2012-05-25 10:56:16
    存储过程 postgresql postgresql存储过程
  • PostgreSQL存储过程

    千次阅读 2019-03-07 10:52:32
    PostgreSQL存储过程 PostgreSQL存储过程几年前写过很多,但是几年不碰又陌生了,今天给客户写了一个存储过程,查了一些资料,记录一下: –创建测试表 create table student (id integer, name varchar(64)); create...

    PostgreSQL存储过程
    PostgreSQL存储过程几年前写过很多,但是几年不碰又陌生了,今天给客户写了一个存储过程,查了一些资料,记录一下:

    –创建测试表
    create table student (id integer, name varchar(64));
    create table employees (id integer, age integer);

    –table_new 需要在外部创建
    create table table_new (id integer, name varchar(64), age integer);

    –插入测试数据
    insert into student select generate_series(1, 100), ‘lili_’ || cast(random()*100 as varchar(2));
    insert into employees select generate_series(1, 50), random()*100;

    注:generate_series函数:
    http://www.cnblogs.com/mchina/archive/2013/04/03/2997722.html

    select count() from student;
    select count(
    ) from employees;

    –存储过程
    create or replace function P_DWA_ERP_LEDGER_JQ_MONTH_NEW( v_mouth varchar(8), out v_retcode text, out v_retinfo text, out v_row_num integer)
    AS
    &BODY&
    declare
    begin
    insert into table_new(id, name, age) select t.id, t.name, m.age from student t, employees m where t.id=m.id;

    GET DIAGNOSTICS V_ROW_NUM := ROW_COUNT;
    
    -- 执行成功后的返回信息
    V_RETCODE := 'SUCCESS';
    V_RETINFO := '结束';
    
    --异常处理
    EXCEPTION
    WHEN OTHERS THEN
        V_RETCODE := 'FAIL';
        V_RETINFO := SQLERRM;
    

    end;

    B O D Y BODY BODY
    language plpgsql;

    –调用存储过程
    select * from P_DWA_ERP_LEDGER_JQ_MONTH_NEW(‘12’);

    –查看结果
    select count(*) from table_new;
    delete from table_new;
    vacuum table_new;

    create or replace function test() returns integer
    AS
    B O D Y BODY BODY
    declare
    v_mouth varchar(8);
    v_retcode text;
    v_retinfo text;
    v_row_num integer;
    begin
    v_mouth := 12;
    select * from P_DWA_ERP_LEDGER_JQ_MONTH_NEW(v_mouth) into v_retcode, v_retinfo, v_row_num;
    raise notice ‘P_DWA_ERP_LEDGER_JQ_MONTH_NEW result is: %, %, %, %’, v_mouth, v_retcode, v_retinfo, v_row_num;
    return 0;
    end;

    B O D Y BODY BODY
    language plpgsql;

    select test();

    一、写法示例

    http://panyongzheng.iteye.com/blog/2194815

    PostgreSQL的存储过程简单入门 http://blog.csdn.net/rachel_luo/article/details/8073458
    存储过程事物 http://www.php100.com/manual/PostgreSQL8/tutorial-transactions.html
    PL/pgSQL - SQL存储过程语言 https://wiki.postgresql.org/wiki/9.1第三十九章

    postgreSQL存储过程写法示例http://blog.sina.com.cn/s/blog_448574810101f64u.html
    结构
    PL/pgSQL是一种块结构的语言,比较方便的是用pgAdmin III新建Function,填入一些参数就可以了。基本上是这样的

    二、处理异常

    转自:https://www.cnblogs.com/lottu/p/7410978.html

    1. 异常错误处理
        在PL/pgSQL函数中,如果没有异常捕获,函数会在发生错误时直接退出,与其相关的事物也会随之回滚。我们可以通过使用带有EXCEPTION子句的BEGIN块来捕获异常并使其从中恢复。见如下声明形式:

    [ <> ]
    [ DECLARE
    declarations ]
    BEGIN
    statements
    EXCEPTION
      WHEN condition [ OR condition … ] THEN
    handler_statements
      WHEN condition [ OR condition … ] THEN
       handler_statements
    END;

    如果没有错误发生,只有BEGIN块中的statements会被正常执行,然而一旦这些语句中有任意一条发生错误,其后的语句都将被跳过,直接跳转到 EXCEPTION块的开始处。此时系统将搜索异常条件列表,寻找匹配该异常的第一个条件,如果找到匹配,则执行相应的 handler_statements,之后再执行END的下一条语句。如果没有找到匹配,该错误就会被继续向外抛出,其结果与没有EXCEPTION子 句完全等同。如果此时handler_statements中的语句发生新错误,它将不能被该EXCEPTION子句捕获,而是继续向外传播,交由其外层 的EXCEPTION子句捕获并处理。

    在PostgreSQL中可以利用RAISE语句报告信息和抛出错误,其声明形式为:

    RAISE level ‘format’ [, expression [, …]];
    这里包含的级别有DEBUG(向服务器日志写信息)、LOG(向服务器日志写信息,优先级更高)、INFO、NOTICE和WARNING(把信息写到服务器日志以及转发到客户端应用,优先级逐步升高)和EXCEPTION抛出一个错误(通常退出当前事务)。某个优先级别的信息是报告给客户端还是写到服务器日志,还是两个均有,是由log_min_messages和client_min_messages这两个系统初始化参数控制的。
    在format部分中,%表示为占位符,其实际值仅在RAISE命令执行时由后面的变量替换,如果要在format中表示%自身,可以使用%%的形式表示,见如下示例:
    RAISE NOTICE ‘Calling cs_create_job(%)’,v_job_id;–v_job_id变量的值将替换format中的%。
    RAISE EXCEPTION ‘Inexistent ID --> %’,user_id;

    简单来说:

    –抛出异常
    RAISE EXCEPTION ‘你出问题了。该修修!’;
    –使用SQLERRM 来显示错误信息。
    RAISE EXCEPTION ‘(%)’, SQLERRM;
    见如下示例:

    CREATE OR REPLACE FUNCTION GETEXCEPTION(v_phone text) RETURNS void AS
    B E G I N I F v p h o n e = ′ i p h o n e ′ T H E N R A I S E E X C E P T I O N ′ 你 出 问 题 了 。 该 修 修 ! ′ ; E L S I F v p h o n e = ′ s a m s u n g ′ T H E N R A I S E E X C E P T I O N ′ 你 会 爆 炸 , 离 你 远 点 ! ′ ; e l s e R E T U R N ; E N D I F ; E X C E P T I O N W H E N o t h e r s T H E N R A I S E E X C E P T I O N ′ ( E N D BEGIN IF v_phone = &#x27;iphone&#x27; THEN RAISE EXCEPTION &#x27;你出问题了。该修修!&#x27;; ELSIF v_phone = &#x27;samsung&#x27; THEN RAISE EXCEPTION &#x27;你会爆炸,离你远点!&#x27;; else RETURN; END IF; EXCEPTION WHEN others THEN RAISE EXCEPTION &#x27;(%)&#x27;, SQLERRM; END BEGINIFvphone=iphoneTHENRAISEEXCEPTION;ELSIFvphone=samsungTHENRAISEEXCEPTION;elseRETURN;ENDIF;EXCEPTIONWHENothersTHENRAISEEXCEPTION(END LANGUAGE PLPGSQL;

    参考文献:http://www.postgres.cn/docs/9.3/plpgsql-errors-and-messages.html

    三、里面有如何调用一个有out参数的存储过程

    原创文章,转载请务必将下面这段话置于文章开头处(保留超链接)。

    本文转发自Jason’s Blog,原文链接 http://www.jasongj.com/2015/12/27/SQL4_存储过程_Store Procedure/

    存储过程简介
    什么是存储过程
      百度百科是这么描述存储过程的:存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL语句集,存储在数据库中,首次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果有)来执行它。它是数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过程。
      
      维基百科是这样定义的:A stored procedure (also termed proc, storp, sproc, StoPro, StoredProc, StoreProc, sp, or SP) is a subroutine available to applications that access a relational database management system (RDMS). Such procedures are stored in the database data dictionary。

    PostgreSQL对存储过程的描述是:存储过程和用户自定义函数(UDF)是SQL和过程语句的集合,它存储于数据库服务器并能被SQL接口调用。

    总结下来存储过程有如下特性:

    存储于数据库服务器
    一次编译后可多次调用
    设计良好的数据库应用程序很可能会用到它
    由SQL和过程语句来定义
    应用程序通过SQL接口来调用
    使用存储过程的优势及劣势
      首先看看使用存储过程的优势

    减少应用与数据库服务器的通信开销,从而提升整体性能。笔者在项目中使用的存储过程,少则几十行,多则几百行甚至上千行(假设一行10个字节,一千行即相当于10KB),如果不使用存储过程而直接通过应用程序将相应SQL请求发送到数据库服务器,会增大网络通信开销。相反,使用存储过程能降低该开销,从而提升整体性能。尤其在一些BI系统中,一个页面往往要使用多个存储过程,此时存储过程降低网络通信开销的优势非常明显
    一次编译多次调用,提高性能。存储过程存于数据库服务器中,第一次被调用后即被编译,之后再调用时无需再次编译,直接执行,提高了性能
    同一套业务逻辑可被不同应用程序共用,减少了应用程序的开发复杂度,同时也保证了不同应用程序使用的一致性
    保护数据库元信息。如果应用程序直接使用SQL语句查询数据库,会将数据库表结构暴露给应用程序,而使用存储过程是应用程序并不知道数据库表结构
    更细粒度的数据库权限管理。直接从表读取数据时,对应用程序只能实现表级别的权限管理,而使用存储过程是,可在存储过程中将应用程序无权访问的数据屏蔽
    将业务实现与应用程序解耦。当业务需求更新时,只需更改存储过程的定义,而不需要更改应用程序
    可以通过其它语言并可及其它系统交互。比如可以使用PL/Java与Kafka交互,将存储过程的参数Push到Kafka或者将从Kafka获取的数据作为存储过程的结果返回给调用方
      当然,使用存储过程也有它的劣势

    不便于调试。尤其在做性能调优时,以PostgreSQL为例,可使用EXPLAIN ANALYZE检查SQL查询计划,从而方便的进行性能调优。而使用存储过程时,EXPLAIN ANALYZE无法显示其内部查询计划
    不便于移植到其它数据库。直接使用SQL时,SQL存于应用程序中,对大部分标准SQL而言,换用其它数据库并不影响应用程序的使用。而使用存储过程时,由于不同数据库的存储过程定义方式不同,支持的语言及语法不同,移植成本较高
    存储过程在PostgreSQL中的使用
    PostgreSQL支持的过程语言
      PostgreSQL官方支持PL/pgSQL,PL/Tcl,PL/Perl和PL/Python这几种过程语言。同时还支持一些第三方提供的过程语言,如PL/Java,PL/PHP,PL/Py,PL/R,PL/Ruby,PL/Scheme,PL/sh。

    基于SQL的存储过程定义
    CREATE OR REPLACE FUNCTION add(a INTEGER, b NUMERIC)
    RETURNS NUMERIC
    AS S E L E C T a + b ; SELECT a+b; SELECTa+b; LANGUAGE SQL;
      调用方法

    SELECT add(1,2);
    add

    3
    (1 row)

    SELECT * FROM add(1,2);
    add

    3
    (1 row)

    上面这种方式参数列表只包含函数输入参数,不包含输出参数。下面这个例子将同时包含输入参数和输出参数

    CREATE OR REPLACE FUNCTION plus_and_minus
    (IN a INTEGER, IN b NUMERIC, OUT c NUMERIC, OUT d NUMERIC)
    AS S E L E C T a + b , a − b ; SELECT a+b, a-b; SELECTa+b,ab; LANGUAGE SQL;
     调用方式

    SELECT plus_and_minus(3,2);
    add_and_minute

    (5,1)
    (1 row)

    SELECT * FROM plus_and_minus(3,2);
    c | d
    —±–
    5 | 1
    (1 row)

    该例中,IN代表输入参数,OUT代表输出参数。这个带输出参数的函数和之前的add函数并无本质区别。事实上,输出参数的最大价值在于它为函数提供了返回多个字段的途径。

    在函数定义中,可以写多个SQL语句,不一定是SELECT语句,可以是其它任意合法的SQL。但最后一条SQL必须是SELECT语句,并且该SQL的结果将作为该函数的输出结果。

    CREATE OR REPLACE FUNCTION plus_and_minus
    (IN a INTEGER, IN b NUMERIC, OUT c NUMERIC, OUT d NUMERIC)
    AS S E L E C T a + b , a − b ; I N S E R T I N T O t e s t V A L U E S ( ′ t e s t 1 ′ ) ; S E L E C T a − b , a + b ; SELECT a+b, a-b; INSERT INTO test VALUES(&#x27;test1&#x27;); SELECT a-b, a+b; SELECTa+b,ab;INSERTINTOtestVALUES(test1);SELECTab,a+b; LANGUAGE SQL;

    其效果如下

    SELECT * FROM plus_and_minus(5,3);
    c | d
    —±–
    2 | 8
    (1 row)

    SELECT * FROM test;
    a

    test1
    (1 row)

    基于PL/PgSQL的存储过程定义

    PL/pgSQL是一个块结构语言。函数定义的所有文本都必须是一个块。一个块用下面的方法定义:

    [ <

    中括号部分为可选部分

    块中的每一个declaration和每一条statement都由一个分号终止
    块支持嵌套,嵌套时子块的END后面必须跟一个分号,最外层的块END后可不跟分号
    BEGIN后面不必也不能跟分号
    END后跟的label名必须和块开始时的标签名一致
    所有关键字都不区分大小写。标识符被隐含地转换成小写字符,除非被双引号包围
    声明的变量在当前块及其子块中有效,子块开始前可声明并覆盖(只在子块内覆盖)外部块的同名变量
    变量被子块中声明的变量覆盖时,子块可以通过外部块的label访问外部块的变量
      声明一个变量的语法如下:

    name [ CONSTANT ] type [ NOT NULL ] [ { DEFAULT | := } expression ];

    使用PL/PgSQL语言的函数定义如下:

    CREATE FUNCTION somefunc() RETURNS integer AS $$
    DECLARE
    quantity integer := 30;
    BEGIN
    – Prints 30
    RAISE NOTICE ‘Quantity here is %’, quantity;
    quantity := 50;

    -- Create a subblock
    DECLARE
        quantity integer := 80;
    BEGIN
        -- Prints 80
        RAISE NOTICE 'Quantity here is %', quantity;
        -- Prints 50
        RAISE NOTICE 'Outer quantity here is %', outerblock.quantity;
    END;
    
    -- Prints 50
    RAISE NOTICE 'Quantity here is %', quantity;
    RETURN quantity;
    

    END;
    $$ LANGUAGE plpgsql;

    声明函数参数

    如果只指定输入参数类型,不指定参数名,则函数体里一般用 1 , 1, 1n这样的标识符来使用参数。

    CREATE OR REPLACE FUNCTION discount(NUMERIC)
    RETURNS NUMERIC
    AS KaTeX parse error: Can't use function '$' in math mode at position 19: …GIN RETURN $̲1 * 0.8; END; LANGUAGE PLPGSQL;

    但该方法可读性不好,此时可以为$n参数声明别名,然后可以在函数体内通过别名指向该参数值。

    CREATE OR REPLACE FUNCTION discount(NUMERIC)
    RETURNS NUMERIC
    AS KaTeX parse error: Can't use function '$' in math mode at position 30: …otal ALIAS FOR $̲1; BEGIN RE… LANGUAGE PLPGSQL;

    笔者认为上述方法仍然不够直观,也不够完美。幸好PostgreSQL提供另外一种更为直接的方法来声明函数参数,即在声明参数类型时同时声明相应的参数名。

    CREATE OR REPLACE FUNCTION discount(total NUMERIC)
    RETURNS NUMERIC
    AS B E G I N R E T U R N t o t a l ∗ 0.8 ; E N D ; BEGIN RETURN total * 0.8; END; BEGINRETURNtotal0.8;END; LANGUAGE PLPGSQL;

    返回多行或多列

    使用自定义复合类型返回一行多列
      PostgreSQL除了支持自带的类型外,还支持用户创建自定义类型。在这里可以自定义一个复合类型,并在函数中返回一个该复合类型的值,从而实现返回一行多列。

    CREATE TYPE compfoo AS (col1 INTEGER, col2 TEXT);

    CREATE OR REPLACE FUNCTION getCompFoo
    (in_col1 INTEGER, in_col2 TEXT)
    RETURNS compfoo
    AS D E C L A R E r e s u l t c o m p f o o ; B E G I N r e s u l t . c o l 1 : = i n c o l 1 ∗ 2 ; r e s u l t . c o l 2 : = i n c o l 2 ∣ ∣ r ′ e s u l t ′ ; R E T U R N r e s u l t ; E N D ; DECLARE result compfoo; BEGIN result.col1 := in_col1 * 2; result.col2 := in_col2 || &#x27;_result&#x27;; RETURN result; END; DECLAREresultcompfoo;BEGINresult.col1:=incol12;result.col2:=incol2result;RETURNresult;END; LANGUAGE PLPGSQL;

    SELECT * FROM getCompFoo(1,‘1’);
    col1 | col2
    ------±---------
    2 | 1_result
    (1 row)

    使用输出参数名返回一行多列

    在声明函数时,除指定输入参数名及类型外,还可同时声明输出参数类型及参数名。此时函数可以输出一行多列。

    CREATE OR REPLACE FUNCTION get2Col
    (IN in_col1 INTEGER,IN in_col2 TEXT,
    OUT out_col1 INTEGER, OUT out_col2 TEXT)
    AS B E G I N o u t c o l 1 : = i n c o l 1 ∗ 2 ; o u t c o l 2 : = i n c o l 2 ∣ ∣ r ′ e s u l t ′ ; E N D ; BEGIN out_col1 := in_col1 * 2; out_col2 := in_col2 || &#x27;_result&#x27;; END; BEGINoutcol1:=incol12;outcol2:=incol2result;END; LANGUAGE PLPGSQL;

    SELECT * FROM get2Col(1,‘1’);
    out_col1 | out_col2
    ----------±---------
    2 | 1_result
    (1 row)

    实际项目中,存储过程经常需要返回多行记录,可以通过SETOF实现。使用SETOF返回多行记录

    CREATE TYPE compfoo AS (col1 INTEGER, col2 TEXT);

    CREATE OR REPLACE FUNCTION getSet(rows INTEGER)
    RETURNS SETOF compfoo
    AS B E G I N R E T U R N Q U E R Y S E L E C T i ∗ 2 , i ∣ ∣ t ′ e x t ′ F R O M g e n e r a t e s e r i e s ( 1 , r o w s , 1 ) a s t ( i ) ; E N D ; BEGIN RETURN QUERY SELECT i * 2, i || &#x27;_text&#x27; FROM generate_series(1, rows, 1) as t(i); END; BEGINRETURNQUERYSELECTi2,itextFROMgenerateseries(1,rows,1)ast(i);END; LANGUAGE PLPGSQL;

    SELECT col1, col2 FROM getSet(2);
    col1 | col2
    ------±-------
    2 | 1_text
    4 | 2_text
    (2 rows)

    本例返回的每一行记录是复合类型,该方法也可返回基本类型的结果集,即多行一列。

    使用RETURN TABLE返回多行多列

    CREATE OR REPLACE FUNCTION getTable(rows INTEGER)
    RETURNS TABLE(col1 INTEGER, col2 TEXT)
    AS B E G I N R E T U R N Q U E R Y S E L E C T i ∗ 2 , i ∣ ∣ t ′ e x t ′ F R O M g e n e r a t e s e r i e s ( 1 , r o w s , 1 ) a s t ( i ) ; E N D ; BEGIN RETURN QUERY SELECT i * 2, i || &#x27;_text&#x27; FROM generate_series(1, rows, 1) as t(i); END; BEGINRETURNQUERYSELECTi2,itextFROMgenerateseries(1,rows,1)ast(i);END; LANGUAGE PLPGSQL;

    SELECT col1, col2 FROM getTable(2);
    col1 | col2
    ------±-------
    2 | 1_text
    4 | 2_text
    (2 rows)

    使用EXECUTE语句执行动态命令  此时从函数中读取字段就和从表或视图中取字段一样,可以看此种类型的函数看成是带参数的表或者视图。

    有时在PL/pgSQL函数中需要生成动态命令,这个命令将包括他们每次执行时使用不同的表或者字符。EXECUTE语句用法如下:

    EXECUTE command-string [ INTO [STRICT] target] [USING expression [, …]];
      此时PL/plSQL将不再缓存该命令的执行计划。相反,在该语句每次被执行的时候,命令都会编译一次。这也让该语句获得了对各种不同的字段甚至表进行操作的能力。

    command-string包含了要执行的命令,它可以使用参数值,在命令中通过引用如$1,$2等来引用参数值。这些符号的值是指USING字句的值。这种方法对于在命令字符串中使用参数是最好的:它能避免运行时数值从文本来回转换,并且不容易产生SQL注入,而且它不需要引用或者转义。

    CREATE TABLE testExecute
    AS
    SELECT
    i || ‘’ AS a,
    i AS b
    FROM
    generate_series(1, 10, 1) AS t(i);

    CREATE OR REPLACE FUNCTION execute(filter TEXT)
    RETURNS TABLE (a TEXT, b INTEGER)
    AS KaTeX parse error: Can't use function '$' in math mode at position 78: …cute where a = $̲1' USING fi… LANGUAGE PLPGSQL;

    SELECT * FROM execute(‘3’);
    a | b
    —±–
    3 | 3
    (1 row)

    SELECT * FROM execute(‘3’’ or ‘‘c’’=’‘c’);
    a | b
    —±–
    (0 rows)

    当然,也可以使用字符串拼接的方式在command-string中使用参数,但会有SQL注入的风险。

    CREATE TABLE testExecute
    AS
    SELECT
    i || ‘’ AS a,
    i AS b
    FROM
    generate_series(1, 10, 1) AS t(i);

    CREATE OR REPLACE FUNCTION execute(filter TEXT)
    RETURNS TABLE (a TEXT, b INTEGER)
    AS B E G I N R E T U R N Q U E R Y E X E C U T E ′ S E L E C T ∗ F R O M t e s t E x e c u t e w h e r e b = ′ ′ ′ ∣ ∣ f i l t e r ∣ ∣ ′ ′ ′ ′ ; E N D ; BEGIN RETURN QUERY EXECUTE &#x27;SELECT * FROM testExecute where b = &#x27;&#x27;&#x27; || filter || &#x27;&#x27;&#x27;&#x27;; END; BEGINRETURNQUERYEXECUTESELECTFROMtestExecutewhereb=filter;END; LANGUAGE PLPGSQL;

    SELECT * FROM execute(3);
    a | b
    —±–
    3 | 3
    (1 row)

    SELECT * FROM execute(‘3’’ or ‘‘c’’=’‘c’);
    a | b
    ----±—
    1 | 1
    2 | 2
    3 | 3
    4 | 4
    5 | 5
    6 | 6
    7 | 7
    8 | 8
    9 | 9
    10 | 10
    (10 rows)

    从该例中可以看出使用字符串拼接的方式在command-string中使用参数会引入SQL注入攻击的风险,而使用USING的方式则能有效避免这一风险。

    PostgreSQL中的UDF与存储过程
      本文中并未区分PostgreSQL中的UDF和存储过程。实际上PostgreSQL创建存储与创建UDF的方式一样,并没有专用于创建存储过程的语法,如CREATE PRECEDURE。在PostgreSQL官方文档中也暂未找到这二者的区别。倒是从一些资料中找对了它们的对比,如下表如示,仅供参考。
    UDF VS. Stored Precedure

    多态SQL函数
      SQL函数可以声明为接受多态类型(anyelement和anyarray)的参数或返回多态类型的返回值。

    函数参数和返回值均为多态类型。其调用方式和调用其它类型的SQL函数完全相同,只是在传递字符串类型的参数时,需要显示转换到目标类型,否则将会被视为unknown类型。

    CREATE OR REPLACE FUNCTION get_array(anyelement, anyelement)
    RETURNS anyarray
    AS KaTeX parse error: Can't use function '$' in math mode at position 19: … SELECT ARRAY[$̲1, $2]; LANGUAGE SQL;

    SELECT get_array(1,2), get_array(‘a’::text,‘b’::text);
    get_array | get_array
    -----------±----------
    {1,2} | {a,b}
    (1 row)

    函数参数为多态类型,而返回值为基本类型

    CREATE OR REPLACE FUNCTION is_greater(anyelement, anyelement)
    RETURNS BOOLEAN
    AS KaTeX parse error: Can't use function '$' in math mode at position 13: SELECT $̲1 > $2; LANGUAGE SQL;

    SELECT is_greater(7.0, 4.5);
    is_greater

    t
    (1 row)

    SELECT is_greater(2, 4);
    is_greater

    f
    (1 row)

    输入输出参数均为多态类型。这种情况与第一种情况一样。

    CREATE OR REPLACE FUNCTION get_array
    (IN anyelement, IN anyelement, OUT anyelement, OUT anyarray)
    AS KaTeX parse error: Can't use function '$' in math mode at position 13: SELECT $̲1, ARRAY[$1, $2… LANGUAGE SQL;

    SELECT get_array(4,5), get_array(‘c’::text, ‘d’::text);
    get_array | get_array
    -------------±------------
    (4,"{4,5}") | (c,"{c,d}")
    (1 row)

    函数重载(Overwrite)
      在PostgreSQL中,多个函数可共用同一个函数名,但它们的参数必须得不同。这一规则与面向对象语言(比如Java)中的函数重载类似。也正因如此,在PostgreSQL删除函数时,必须指定其参数列表,如:

    1
    DROP FUNCTION get_array(anyelement, anyelement);

    另外,在实际项目中,经常会用到CREATE OR REPLACE FUNCTION去替换已有的函数实现。如果同名函数已存在,但输入参数列表不同,会创建同名的函数,也即重载。如果同名函数已存在,且输入输出参数列表均相同,则替换。如果已有的函数输入参数列表相同,但输出参数列表不同,则会报错,并提示需要先DROP已有的函数定义。

    展开全文
  • PostgreSQL 存储过程

    2014-02-20 09:36:56
    PostgreSQL 存储过程定义格式如下: ■结构 PL/pgSQL是一种块结构的语言,比较方便的是用pgAdmin III新建Function,填入一些参数就可以了。 基本上是这样的: CREATE OR REPLACE FUNCTION 函数名(参数1,[整型 ...

    PostgreSQL 存储过程定义格式如下:

    ■结构 PL/pgSQL是一种块结构的语言,比较方便的是用pgAdmin III新建Function,填入一些参数就可以了。

    基本上是这样的:

    CREATE OR REPLACE FUNCTION 函数名(参数1,[整型 int4, 整型数组 _int4, ...])  RETURNS 返回值类型 AS  $BODY$  DECLARE 变量声明  BEGIN 函数体  END;  $BODY$  LANGUAGE ‘plpgsql’ VOLATILE;

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

    ■赋值 赋值和Pascal有点像:“变量 := 表达式;”  有些奇怪的是连接字符串的是“||”,比如 sql := ‘SELECT * FROM’ || table || ‘WHERE …’;

    ■判断   判断又和VB有些像:  IF 条件 THEN …  ELSEIF 条件 THEN …  ELSE …  END IF;

     ■循环   循环有好几种写法:  WHILE expression LOOP  statements  END LOOP;  还有常用的一种是:(从1循环到9可以写成FOR i IN 1..9 LOOP)  FOR name IN [ REVERSE ] expression .. expression LOOP  statements  END LOOP;

     ■其他   还有几个常用的函数:  SELECT INTO record …; 表示将select的结果赋给record变量(RECORD类型)  PERFORM query; 表示执行query并丢弃结果  EXECUTE sql; 表示执行sql语句,这条可以动态执行sql语句(特别是由参数传入构造sql语句的时候特别有用)

    --简单的例子:

    例1:无返回值

    CREATE OR REPLACE FUNCTION 函数名称( 参数1,参数2,...)
       AS
    $BODY$
    DECLARE  --定义 
    BEGIN
    INSERT INTO "表名" VALUES(参数1,参数2,...);
    END
    $BODY$
    LANGUAGE 'plpgsql' VOLATILE;  -- 最后别忘了这个。

    例2:有返回值

    CREATE OR REPLACE FUNCTION 函数名称(deptcode VARCHAR(20) ,deptname VARCHAR(60) ,pycode VARCHAR(60),isenabled CHAR(1))
    RETURNS BOOLEAN  --返回值,布尔类型
    AS
    $body$
    DECLARE
    deptcode VARCHAR(20);
    deptname VARCHAR(60);
    pycode  VARCHAR(60);
    isenabled CHAR(1);
    BEGIN
    UPDATE "deptDict" SET deptcode=deptcode,deptname=deptname,pycode=pycode,isenabled=isenabled,updatedhisdatetime=CURRENT_TIMESTAMP
    WHERE deptcode=deptcode;

    RETURN TRUE; 
    END
    $body$ 
    LANGUAGE 'plpgsql' VOLATILE;

    最后再加上如何执行这个存储过程(函数)

    -- 执行存储过程方法1
    SELECT * FROM 函数名称(参数1,参数2,...)
    -- 执行存储过程方法2
    SELECT  函数名称('0参数1,参数2,...)

    展开全文
  • 初识PostgreSQL存储过程

    2020-09-10 11:16:53
    主要介绍了初识PostgreSQL存储过程,本文讲解了PostgreSQL中存储过程的语法,并给出了一个操作实例,需要的朋友可以参考下
  • postgresql存储过程

    2011-10-11 15:22:07
    最近自学postgresql存储过程,先把自己的理解及代码上传。海青大家多指教。
  • postgresql 存储过程

    2019-02-20 17:18:35
    1 查询存储过程 select pg_proc.proname, prosrc from pg_proc where proname = 'radacct_update_deviceid';  'radacct_update_deviceid 是存储过程的名称 2、创建存储过程 这个是无返回值的存储过程 CREATE...

    1 查询存储过程

    select  pg_proc.proname, prosrc  from pg_proc  where proname = 'radacct_update_deviceid'; 

    'radacct_update_deviceid 是存储过程的名称

    2、创建存储过程

    这个是无返回值的存储过程

    CREATE OR REPLACE FUNCTION function_dev () 
    RETURNS void AS $$
    BEGIN  
      UPDATE t_wifi SET dev='17fe';
    END; 
    $$
    LANGUAGE plpgsql ;

    当创建好以后,在触发器中准备使用时,会报ERROR:  function insert_cdr must return type "trigger"这个错误。

    先删除这个存储过程

    DROP FUNCTION function_dev () 

    然后再重新创建

    DROP FUNCTION rzhflows_update_devmac() 
    CREATE OR REPLACE FUNCTION rzhflows_update_devmac () 
    RETURNS trigger AS $$
    BEGIN  
      UPDATE t_wifi_rzhflows SET devmac='17:43:23:6a:d7:fe';
    END; 
    $$
    LANGUAGE plpgsql ;

     

    3 行触发器和语句触发器的区别

    语句级触发器执行每个SQL时,只执行一次 ;行级触发器每行都会执行一次

    展开全文
  • 主要介绍了PostgreSQL存储过程用法,结合具体实例详细分析了PostgreSQL数据库存储过程的定义、使用方法及相关操作注意事项,并附带一个完整实例供大家参考,需要的朋友可以参考下
  • PostgreSql 存储过程介绍

    千次阅读 2019-05-18 09:07:28
    PostgreSql 存储过程介绍 本文我们了解PostgreSQL存储过程。 概述 PostgreSQL中可以使用多种过程语言实现用户定义函数和存储过程对数据库功能进行扩展,这通常称为存储过程存储过程一般用于创建触发器或自定义...

    PostgreSql 存储过程介绍

    本文我们了解PostgreSQL的存储过程。

    概述

    PostgreSQL中可以使用多种过程语言实现用户定义函数和存储过程对数据库功能进行扩展,这通常称为存储过程。

    存储过程一般用于创建触发器或自定义聚合函数。另外存储过程还有许多过程特性,例如控制结构和复杂的计算。方便你更容易、更有效地开发自定义函数。

    如果没有定义函数或存储过程,也可以通过DO命令执行过程代码块。

    实现语言

    PostgreSQL 中的过程语言主要分为两组:

    • 任何用户可以使用的安全语言。如SQL和PL/pgSQL 是安全语言。
    • 仅超级用户使用的沙箱环境语言。因为沙箱语言提供了绕过安全限制并允许访问外部资源的能力。C语言是沙箱语言的一个例子。

    PostgreSQL默认支持三种过程语言: SQL, PL/pgSQL, C语言。我们也可以加载其他语言扩展,如:Perl, Python, TCL等。

    存储过程优势

    主要包括下列几点:

    • 减少应用和数据库服务器之间的网络传输。所有SQL语句被包装在一个过程中,应用程序紧紧发送一个函数调用命令即可得到执行结果,而不需要发送多次SQL语句,等待每次调用结果。
    • 提升应用性能。因为自定义函数或过程是在PostgreSQL数据库服务器中是预编译的。
    • 在多个应用中可以重用。一旦开发了一个函数,就可以在任何应用中重复使用。

    存储过程劣势

    包括下面三点:

    • 开发效率不高。因为存储过程编程需要特定技能,很多开发者没有掌握。
    • 代码版本难以管理,其调试不方便。
    • 对其他数据库系统过程或函数程序不兼容。如Mysql、Microsoft SQL server等。

    总结

    本文,我们介绍了 PostgreSQL存储过程,并讨论了其优缺点。

    展开全文
  • POSTGRESQL 存储过程实战.pdf
  • 项目中用到了Mybatis调用PostgreSQL存储过程(自定义函数)相关操作,由于PostgreSQL自带数组类型,所以有一个自定义函数的入参就是一个int数组,形如: 代码如下:CREATE OR REPLACE FUNCTION “public”.”func_...
  • postgreSQL 存储过程存储过程调用

    千次阅读 2020-10-27 11:11:30
    在 pgadmin 上执行下面语句,新建存储过程,返回adw.trn_node_variables 表的 count 数,adw 是 shcema 名 create or replace function adw.count() returns integer as $BODY$ declare counts integer; begin ...
  • 通过简单的Java“ SProcService”接口使PostgreSQL存储过程(SProcs)可用的库,包括自动对象序列化和反序列化(使用typemapper和Convention-over-configuration)。 支持水平数据库分片(分区/访问逻辑位于应用...
  • Mybatis调用Oracle存储过程PostgreSql存储过程的差异 DemoService代码 Oracle: public String callProc()throws Exception { Map<String, Object> param=new HashMap<>(); param.put("P_UUID", "sid...
  • PostgreSQL函数也称为PostgreSQL存储过程。 PostgreSQL函数或存储过程是存储在数据库服务器上并可以使用SQL界面调用的一组SQL和过程语句(声明,分配,循环,控制流程等)。 它有助于您执行通常在数据库中的单个函数中...
  • postgresql存储过程输出参数

    千次阅读 2019-06-15 15:24:36
    postgresql存储过程输入输出 postgresql不区分存储过程和函数,即这两个是一个东西。存储过程通常用于比较复杂的操作中。 创建存储过程时 create (or replace) function 存储过程名称 这个or replace是如果存在...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 33,998
精华内容 13,599
关键字:

postgresql存储过程