oracle存储过程和存储函数sql

备注:oracle数据库表字段和表名最好都用大写,不然都要用双引号,比如:表名为:"Patient"     字段名为:"PatientID"

若是创建的时候用的表名为PATIENT和字段为PATIENTID   使用的时候就不用双引号

 

--带输出参数的存储过程
CREATE OR REPLACE PROCEDURE INSERTPATIENT(
SURNAME VARCHAR,
FIRSTNAME VARCHAR,
PREFIX VARCHAR,
SEX VARCHAR,
DOB DATE,
NEWPATIENTID OUT INT)
AS
BEGIN
SELECT SEQ_PATIENTID.NEXTVAL INTO NEWPATIENTID FROM DUAL;
INSERT INTO PATIENT (PATIENTID, FAMILYNAME, GIVENNAME, TITLE, GENDER, DATEOFBIRTH)
VALUES (NEWPATIENTID, SURNAME, FIRSTNAME, PREFIX, SEX, DOB);
END;

 

--带结果集的存储过程
CREATE OR REPLACE PROCEDURE LOOKUPPATIENT (
PATIENTID INT,
P_CURSOR OUT SYS_REFCURSOR)
AS
BEGIN
OPEN P_CURSOR FOR SELECT FAMILYNAME, GIVENNAME, TITLE, GENDER, DATEOFBIRTH FROM PATIENT WHERE PATIENTID = PATIENTID;
END;

 

--存储函数返回结果集
CREATE OR REPLACE FUNCTION LOOKUPPATIENTFUN(
PATIENTID INT)
RETURN SYS_REFCURSOR
AS
P_CURSOR SYS_REFCURSOR;
BEGIN
OPEN P_CURSOR FOR SELECT FAMILYNAME, GIVENNAME, TITLE, GENDER, DATEOFBIRTH FROM PATIENT WHERE PATIENTID = PATIENTID;
RETURN P_CURSOR;
END;

posted @ 2022-05-18 15:56  TwinStudio  阅读(114)  评论(0编辑  收藏  举报