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;