数据库存储过程，包，函数语法
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;
