如何使用PostgreSQL中的存储过程将数据插入表中 [英] How to insert data into table using stored procedures in postgresql

问题描述

CREATE TABLE app_for_leave
(
  sno integer NOT NULL,
  eid integer,
  ename varchar(20),
  sd date,
  ed date,
  sid integer,
  status boolean DEFAULT false,
  CONSTRAINT pk_snoa PRIMARY KEY (sno)
);

基本插入为::

INSERT INTO app_for_leave(sno, eid, sd, ed, sid, status)
 VALUES(1,101,'2013-04-04','2013-04-04',2,'f' );

...

INSERT INTO app_for_leave(sno, eid, sd, ed, sid, status) VALUES (?, ?, ?, ?, ?, ?);

我的要求：：如何使用存储过程将数据插入表中 ？

My Requirement:: How to insert data into a table using stored procedures?

推荐答案

PostgreSQL 直到PG11才支持存储过程。在此之前，您可以使用函数获得相同的结果。例如：

PostgreSQL didn't support stored procedures until PG11. Prior to that, you could get the same result using a function. For example:

CREATE FUNCTION MyInsert(_sno integer, _eid integer, _sd date, _ed date, _sid integer, _status boolean)
  RETURNS void AS
  $BODY$
      BEGIN
        INSERT INTO app_for_leave(sno, eid, sd, ed, sid, status)
        VALUES(_sno, _eid, _sd, _ed, _sid, _status);
      END;
  $BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

然后可以这样称呼它：

select * from MyInsert(1,101,'2013-04-04','2013-04-04',2,'f' );

与真正的存储过程相比，Pg的存储函数的主要限制是：

The main limitations on Pg's stored functions - as compared to true stored procedures - are:

1. 无法返回多个结果集


2. 不支持自主交易（函数中的BEGIN，COMMIT和ROLLBACK）


3. 不支持SQL标准的CALL语法，尽管ODBC和JDBC驱动程序将为您转换调用。

示例

从PG11开始，创建过程语法为来提供对事务的支持。

Starting from PG11, the CREATE PROCEDURE syntax is introduced which provides support for transactions.

CREATE PROCEDURE MyInsert(_sno integer, _eid integer, _sd date, _ed date, _sid integer, _status boolean)
LANGUAGE SQL
AS $BODY$
    INSERT INTO app_for_leave(sno, eid, sd, ed, sid, status)
    VALUES(_sno, _eid, _sd, _ed, _sid, _status);   
$BODY$;

可以通过以下方式调用：

Which could be called with:

CALL MyInsert(1,101,'2013-04-04','2013-04-04',2,'f' );

这篇关于如何使用PostgreSQL中的存储过程将数据插入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！