数据库存储过程，包，函数语法

[b]drop table student;
create table student
(
stuNo int primary key,
Name varchar2(10),
address varchar2(30),
birthday date
);

insert into student values(1,'梅超风','山东',to_date('1860-02-12 12:12:23','YYYY-MM-DD HH24:MI:SS'));
insert into student values(2,'陆成风','山西',to_date('1860-2-12','YYYY-MM-DD'));
insert into student values(3,'冯默风','安徽','10-2月-1886');
insert into student values(4,'曲灵风','湖南常德',to_date('1870-02-12 12:12:23','YYYY-MM-DD HH24:MI:SS'));


set serveroutput on
Rem ===================================================================
Rem 1、 创建简单的存储过程，如何执行存储过程
Rem ===================================================================

CREATE OR REPLACE PROCEDURE my_proc6
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('这是一个简单的存储过程的例子！');
end my_proc6;
/

--下面是在PL/SQL中执行存储过程
begin
my_proc;
end;
/

--下面是在SQLPLUS中执行存储过程
execute my_proc;

Rem ===================================================================
Rem 2、 创建带参数的存储过程
Rem ===================================================================

CREATE OR REPLACE PROCEDURE sel_StuNameByNO_proc
(p_sNo student.stuNo%TYPE := 1)
AS
sName student.name%TYPE;
BEGIN
SELECT name into sName FROM student WHERE stuNo = p_sNo;

DBMS_OUTPUT.PUT_LINE('学号为：'||p_sNo||' 的姓名为：'||sName);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('学号为：'||p_sNo||' 的学员不存在');
END sel_StuNameByNO_proc;
/

--下面是在SQLPLUS中执行存储过程
execute sel_StuNameByNO_proc(p_sNo => 3); -- “=>”为指定参数赋值

execute sel_StuNameByNO_proc(2);


-- 带输出参数的存储过程
CREATE OR REPLACE PROCEDURE sel_StuName_proc
(p_sNo IN NUMBER,p_sname OUT VARCHAR2)
AS
BEGIN
SELECT name into p_sname FROM student WHERE stuNo = p_sNo;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_sname := NULL;
END sel_StuName_proc;
/

--下面是在SQLPLUS中执行存储过程

DECLARE
sName VARCHAR2(10);
sNo PLS_INTEGER;
BEGIN
DBMS_OUTPUT.PUT_LINE('请输入学号');
sNo := &sNo;
sel_StuName_proc(sNo,sName);

IF sName IS NULL THEN
DBMS_OUTPUT.PUT_LINE('学号为：'||sNo||' 的学员不存在');
ELSE
DBMS_OUTPUT.PUT_LINE('学号为：'||sNo||' 的姓名为：'||sName);
END IF;
END;

-- IN OUT 参数的过程

CREATE OR REPLACE PROCEDURE
swap(p1 IN OUT NUMBER, p2 IN OUT NUMBER)
IS
v_temp NUMBER;
BEGIN
v_temp := p1;
p1 := p2;
p2 := v_temp;
END;
/

DECLARE
num1 NUMBER := 100;
num2 NUMBER := 200;
BEGIN
swap(num1, num2);
DBMS_OUTPUT.PUT_LINE('num1 = ' || num1);
DBMS_OUTPUT.PUT_LINE('num2 = ' || num2);
END;

Rem ===================================================================
Rem 3、对存储过程授权
Rem ===================================================================

GRANT EXECUTE ON sel_StuNameByNO_proc TO SCOTT;
GRANT EXECUTE ON my_proc TO PUBLIC;

--在SCOTT模式下调用过程
EXECUTE ACCP.my_proc;

DROP PROCEDURE my_proc;

Rem ===================================================================
Rem 4、函数
Rem ===================================================================
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');[/b]

[b] [/b]