--建表
CREATE TABLE Employees
(
Id serial,
Name VARCHAR(100),
DateOfBirth Date,
City VARCHAR(100),
Designation VARCHAR(100),
JoiningDate Date
);
--创建存储过程实现数据插入
CREATE OR REPLACE PROCEDURE PRO_ODS_TO_EDC_PROCESS
(
EmpId INOUT INT,
EmpName VARCHAR(100),
EmpDob DATE,
EmpCity VARCHAR(100),
EmpDesignation VARCHAR(100),
EmpJoiningDate DATE
)
LANGUAGE plpgsql AS
$$
BEGIN
INSERT INTO Employees (Name,DateOfBirth,City,Designation,JoiningDate) VALUES
(EmpName,
EmpDob,
EmpCity,
EmpDesignation,
EmpJoiningDate
) RETURNING Id INTO EmpId;
END
$$;
--调用存储过程
CALL PRO_ODS_TO_EDC_PROCESS(null,'Peter Parker','1997-10-01','New York' ,'Web Developer','2020-11-01');
--查询结果
select * from Employees;
简单的postgersql存储过程样例
最新推荐文章于 2023-06-13 16:42:30 发布