当前位置：首页 > article >正文

Stored procedures in PostgreSQL

article 2024/11/13 4:32:59

select 存储过程，在现了解的情况，还是没有mysql,sqlserver等好写好用。

--postgreSQL 11.0 以下版本
create or replace FUNCTION procInsertSchool
(
    pSchoolId Char(5),
    pSchoolName VarChar(100),
    pSchoolTelNo VarChar(8)
)
RETURNS void
language plpgsql
as $$
begin
IF NOT EXISTS (SELECT * FROM School WHERE SchoolId=pSchoolId)  then-- 存在相同的名称不添加  and SchoolId=@SchoolId  and SchoolName=@SchoolName
INSERT INTO School
(
    SchoolId ,
    SchoolName ,
    SchoolTelNo
)
    VALUES
(
    pSchoolId ,
    pSchoolName ,
    pSchoolTelNo
);
end if;
end;
$$

---postgreSQL 11.0 以上版本
DROP PROCEDURE IF EXISTS procInsertSchoolOutput;

CREATE PROCEDURE procInsertSchoolOutput
(
    pSchoolName NVarChar(5),
    pSchoolTelNo VarChar(500),
    pSchoolId int  inout
)
language plpgsql
as $$
begin
IF NOT EXISTS (SELECT * FROM School WHERE SchoolName=pSchoolName)    -- 如果存在相同的记录，不添加
INSERT INTO School
(
    SchoolName,
    SchoolTelNo
)
    VALUES
(
    pSchoolName ,
    pSchoolTelNo
 
);
 
end;
$$

--返回值 inout
create or replace procedure geovindustat(
   inout total int default 0
)
as
$$
begin
  select count(*) into total from geovindu;
end;
$$
language plpgsql;
 
call geovindustat();
 
 
-- 函数查询
CREATE OR REPLACE FUNCTION fungeovindu()
RETURNS table(tid int,tname varchar(50),TelNo varchar(11))
AS $$
BEGIN
    RETURN QUERY(SELECT * FROM geovindu);
END;
$$ LANGUAGE plpgsql
 
select * from fungeovindu();

-- 查询
 
DROP PROCEDURE IF EXISTS procSelectSchool
 
CREATE or replace PROCEDURE procSelectSchool
(
    pStudentId char(5)
)
language plpgsql SECURITY DEFINER
as $$
DECLARE schoolrow record;
begin
for schoolrow in (SELECT * FROM School WHERE SchoolId = pStudentId)
    loop
RAISE NOTICE 'Id is : % , SchoolName is : %', schoolrow."SchoolId",schoolrow."SchoolName"
    end loop;
end;
$$