过程、函数、包的语法

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 <= max_no THEN
		RETURN '您输入的学号有效.......';
	ELSE
		RETURN '学号超出范围......';
	END IF;
END;
/

-- 在PL/SQL中执行函数
declare
msg varchar2(100);
begin 
msg := verrify_stuNo(12);
dbms_output.put_line(msg);
end;
/


Rem ===================================================================
Rem 5、自主事务处理
Rem ===================================================================
PRAGMA AUTONOMOUS_TRANSACTION;  --开启自主事务

CREATE OR REPLACE PROCEDURE p1
AS
	sAddress VARCHAR2(20);
BEGIN
	SELECT address INTO sAddress FROM student WHERE stuNo=2;
	DBMS_OUTPUT.PUT_LINE('address:'||sAddress);
	ROLLBACK;		--回滚事务
END;
/

CREATE OR REPLACE PROCEDURE p2
AS
	sAddress VARCHAR2(20);
BEGIN
	UPDATE student SET address='体育中心' WHERE stuNo=2;

	p1();		--调用过程p1

	SELECT address INTO sAddress FROM student WHERE stuNo=2;
	DBMS_OUTPUT.PUT_LINE('address:'||sAddress);
END;
/

EXECUTE p2;


Rem ===================================================================
Rem 6、程序包
Rem ===================================================================

-- 创建程序包规范

CREATE OR REPLACE PACKAGE pack_stu
IS
	PROCEDURE sel_StuNameByNO_proc(p_sNo student.stuNo%TYPE := 1);
	FUNCTION verrify_stuNo (sNo PLS_INTEGER) RETURN VARCHAR2;
END pack_stu;
/


-- 创建程序包主体

CREATE OR REPLACE PACKAGE BODY pack_stu
AS
	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;


	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 < max_no THEN
			RETURN '您输入的学号有效.......';
		ELSE
			RETURN '学号超出范围......';
		END IF;
	END verrify_stuNo;
END pack_stu;
/

-- 执行程序包中的过程和函数

EXECUTE pack_stu.sel_StuNameByNO_proc(2);	--执行过程

DECLARE										--执行函数
	sNo PLS_INTEGER;
	msg VARCHAR2(100);
BEGIN
	sNo := &sNo;
	msg :=  pack_stu.verrify_stuNo(sNo);
	DBMS_OUTPUT.PUT_LINE(msg);
END;
/


Rem ===================================================================
Rem 程序包中的游标
Rem ===================================================================
Rem 在程序包中定义游标规范，在程序包主体中定义游标主体，
Rem 在程序包的子程序中打开和使用游标

-- 创建程序包规范

CREATE OR REPLACE PACKAGE pack_stu_cur
AS
	CURSOR stu_cur(sNo NUMBER)
	RETURN student%ROWTYPE;
	PROCEDURE stu_proc(sNo NUMBER);
END pack_stu_cur;
/

-- 创建程序包主体

CREATE OR REPLACE PACKAGE BODY pack_stu_cur
AS
	CURSOR stu_cur(sNo NUMBER) RETURN student%ROWTYPE
		IS SELECT * FROM student WHERE stuNo>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');