psql 存储过程

佚名 6年前 (2019-04-08) 随笔 830人围观抢沙发百度已收录

--添加人员和虹膜注册信息
CREATE OR REPLACE FUNCTION AddPersonInfoAndIrisEnrollInfo(personName character, workSn character, sex integer, createTime character, updateTime character, eyeFlag integer)
  RETURNS integer AS
$BODY$
DECLARE currentid INTEGER;
BEGIN
        
    -- 插入人员信息
    INSERT INTO person_base(name, won, sex, crme, upe)  VALUES (personName,workSn,sex,createTime,updateTime);
    -- 获取插入人员的id
    currentid := currval('persid'); --括号中是序列
     
    -- 插入人员虹膜注册信息
    INSERT INTO person_enroll_info(persid, eylag, enrolype) VALUES(currentid,eyeFlag,createTime);

    RETURN currentid;
    
EXCEPTION
        WHEN OTHERS THEN
        RAISE NOTICE 'CAUGHT OTHERS';
        RAISE NOTICE '%' ,sqlerrm;
        RAISE NOTICE '%' ,sqlstate;
        RETURN -2;
    
END;
$BODY$ LANGUAGE plpgsql;