数据库存储过程，包，函数语法(二)
=================
REM 函数只能带有IN参数，不能带有IN OUT 或 OUT参数
REM 形式参数必须只使用数据库类型，不得使用PL、SQL类型
REM 函数的返回类型也必须是数据库类型
-- 一个简单的函数
CREATE OR REPLACE FUNCTION fun_hello
RETURN VARCHAR2
IS
BEGIN
RETURN '看，函数就这么简单吧.......';
END;
/
-- 执行函数
SELECT fun_hello FROM DUAL;
CREATE OR REPLACE FUNCTION verrify_stuNo
(sNo PLS_INTEGER) RETURN VARCHAR2
IS
max_no PLS_INTEGER;
min_no PLS_INTEGER;
BEGIN
SELECT MAX(stuNO),MIN(stuNO) INTO max_no,min_no
FROM student;
IF sNo >= min_no AND sNo min_no AND sNo sNo;
PROCEDURE stu_proc(sNo NUMBER)
IS
stu_rec student%ROWTYPE;
BEGIN
OPEN stu_cur(sNo); --打开游标
LOOP
FETCH stu_cur INTO stu_rec;
EXIT WHEN stu_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(stu_rec.stuNo||' '||stu_rec.name||' '||
stu_rec.address||' '||stu_rec.birthday);
END LOOP;
CLOSE stu_cur; --关闭游标
END;
END pack_stu_cur;
/
-- 调用程序包中过程
EXEC pack_stu_cur.stu_proc(2);
/
Rem ===================================================================
Rem 程序包中的REF游标
Rem ===================================================================
Rem 可以使用程序包中的REF游标从
Oracle存储过程返回记录集，
Rem Oracle存储过程本来不能返回值，但是可以利用IN OUT模式的
Rem 游标变量参数返回结果集
CREATE OR REPLACE PACKAGE pack_stu_ref
AS
TYPE stu_cur_type IS REF CURSOR RETURN student%ROWTYPE;
PROCEDURE stu_ref_proc(stu_rec IN OUT stu_cur_type);
END pack_stu_ref;
/
CREATE OR REPLACE PACKAGE BODY pack_stu_ref
AS
PROCEDURE stu_ref_proc(stu_rec IN OUT stu_cur_type)
IS
BEGIN
OPEN stu_rec FOR SELECT * FROM student;
END stu_ref_proc;
END pack_stu_ref;
/
--程序包调用
VARIABLE l_stu REFCURSOR;
EXECUTE pack_stu_ref.stu_ref_proc(:l_stu);
PRINT l_stu;
Rem ===================================================================
Rem 程序包中使用RECORD类型
Rem ===================================================================
CREATE OR REPLACE PACKAGE pack_test_rec
as
TYPE l_stu_type IS RECORD( --自定义记录类型
sNo student.stuNo%type,
sName student.name%type,
sAddress student.address%type
);
CURSOR stu_cur RETURN l_stu_type;
PROCEDURE stu_cur_proc;
END pack_test_rec;
/
CREATE OR REPLACE PACKAGE BODY pack_test_rec
AS
CURSOR stu_cur RETURN l_stu_type
IS SELECT stuNo,name,address FROM student;
PROCEDURE stu_cur_proc IS
stu_rec l_stu_type;
BEGIN
OPEN stu_cur;
LOOP
FETCH stu_cur INTO stu_rec;
EXIT WHEN stu_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(stu_rec.sNo||' '||
stu_rec.sName||' '||stu_rec.sAddress);
END LOOP;
CLOSE stu_cur;
END;
END pack_test_rec;
/
EXEC pack_test_rec.stu_cur_proc;
COLUMN LINE FORMAT 999
COLUMN TEXT FORMAT A70
SELECT line,text FROM USER_SOURCE WHERE NAME=UPPER('P2');