PostgreSQL 存储过程（六）

超、凢脫俗 2023-10-17 17:08 11阅读 0赞

PostgreSQL函数也称为**PostgreSQL存储过程。**

PostgreSQL函数或存储过程是存储在数据库服务器上并可以使用SQL界面调用的一组SQL和过程语句(声明，分配，循环，控制流程等)。 它有助于您执行通常在数据库中的单个函数中进行多次查询和往返操作的操作。

您可以在许多语言(如SQL，PL/pgSQL，C，Python等)中创建PostgreSQL函数。

**存储过程有如下特性：**

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

**基于SQL的存储过程定义**

CREATE OR REPLACE FUNCTION function_name([ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ])
    [RETURNS rettype]
    AS $$
        $BODY$;
    $$ LANGUAGE SQL;

--案例1）两数求和的方法
    create or replace function fun_add(numA numeric,numB numeric) --参数
    returns numeric  --返回的类型
    as $$
       select numA+numB;  --操作
    $$ language sql;
    
    --调用存储过程
    select fun_add(100,20);

![Center][]

--案例2）两数求和的方法
     --包含输入参数in和输出参数out;由于存在输出参数；这里不需要returns部分
     create or replace function fun_add2(in numA numeric,in numB numeric,out sum numeric)
     as $$
      select numA+numB;
     $$ language sql;
     
    --调用存储过程
    select fun_add2(100,200);

![Center 1][]

--案例3：两数求和，积的方法
     --在函数定义中，可以写多个SQL语句，不一定是SELECT语句，可以是其它任意合法的SQL。
    --但最后一条SQL必须是SELECT语句并且该SQL的结果将作为该函数的输出结果。
     create or replace function fun_add3(in numA numeric,in numB numeric,out sum numeric,out mul numeric)
     as $$
       select * from student;
       select numA+numB,numA*numB;
     $$ language sql;
    
    --调用存储过程
    select fun_add3(10,5);

![Center 2][]

--案例4：如果表company是多条数据，那么会返回多条吗？
    create or replace function fun_add4()
    returns real  --返回类型
    as $$
      select salary from company;
    $$ language sql;
    
    --调用存储过程 (查看结果是返回第一条数据哦！)
    select fun_add4();

![Center 3][]

--案例5 查询company所有的数据
    --需要该函数返回最后一条SELECT语句的所有行，可以将函数的返回值定义为集合，即SETOF sometype。
    create or replace function fun_add5()
    returns setof real  --返回类型
    as $$
      select salary from company;
    $$ language sql;  ----制定sql语言
    
    --调用存储过程 
    select fun_add5();

![Center 4][]

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

CREATE [OR REPLACE] FUNCTION function_name (arguments)   
    RETURNS return_datatype AS $variable_name$  
       DECLARE  
        declaration;  
        [...]  
      BEGIN  
        < function_body >  
        [...]  
        RETURN { variable_name | value }  
      END; 
      LANGUAGE plpgsql;

参数说明:  
function\_name：指定函数的名称。  
\[OR REPLACE\]：是可选的，它允许您修改/替换现有函数。  
RETURN：它指定要从函数返回的数据类型。它可以是基础，复合或域类型，或者也可以引用表列的类型。  
function\_body：function\_body包含可执行部分。  
plpgsql：它指定实现该函数的语言的名称。

--案例6：统计公司的个数
    create or replace function fun_total()
    returns integer as $total$
    declare
       total integer;  --声明变量
     begin
       select count(1) into total from company; --统计个数
       return total;  --返回值
     end;
     $total$ language plpgsql;  --制定语言
    
    --调用存储过程 
    select fun_total()

![Center 5][]

--案例7：查询公司所的信息 
    --第一种方式：  {声明setof 某表/某视图 返回类型 }
    --解决了动态返回结果集字段的问题。
    create or replace  function fun_all() 
    returns setof company 
    as $body$
    declare
     result record; --
    begin
     --循环
      for result in select * from company loop
        return next result;
      end loop;
      return;
    end;
    $body$ language plpgsql;
    
    
    --调用存储过程 
    select fun_all();
    
    --第二种方式：
    CREATE OR REPLACE FUNCTION fun_all2 () 
    RETURNS SETOF company 
    AS  $body$ 
    SELECT * from company; 
    $body$ language sql;
    
    --调用存储过程 
    select fun_all2();
    
    
    --第三种方式：
    --声明refcursor返回类型；可以用jdbc来调用
    CREATE OR REPLACE FUNCTION fun_all3 () 
    RETURNS refcursor 
    AS  $body$ 
    DECLARE 
    result refcursor;  --返回游标
    BEGIN 
    open result for select * from company; --你可以任意选择你想要返回的表和字段 
    return result; 
    END; 
    $body$ language plpgsql;
    
    --调用存储过程 
    
    --jdbc调用
    Connection conn = ConnectionPool.getConn(); 
    CallableStatement proc = conn.prepareCall("{ ? = call fun_all3() }"); 
    proc.execute(); 
    ResultSet rs = (ResultSet) proc.getObject(1);

![Center 6][]

[Center]: https://img-blog.csdn.net/20171201155724180?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaGx4MjAwODA4MDg=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 1]: https://img-blog.csdn.net/20171201155753279?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaGx4MjAwODA4MDg=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 2]: https://img-blog.csdn.net/20171201155823398?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaGx4MjAwODA4MDg=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 3]: https://img-blog.csdn.net/20171201155846383?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaGx4MjAwODA4MDg=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 4]: https://img-blog.csdn.net/20171201155908647?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaGx4MjAwODA4MDg=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 5]: https://img-blog.csdn.net/20171201160034434?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaGx4MjAwODA4MDg=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 6]: https://img-blog.csdn.net/20171201160116894?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaGx4MjAwODA4MDg=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center