`

过程、函数、包的语法

 
阅读更多
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');

分享到:
评论

相关推荐

    C语言函数大全语法着色版.rar

    《C语言函数大全语法着色版》是一款专为C语言...通过CHM文件的搜索功能,用户可以迅速定位到所需函数或概念,极大地便利了学习和开发过程。因此,《C语言函数大全语法着色版》是C语言初学者和进阶者的宝贵参考资料。

    oracle存储过程_函数_语法_大全_详解

    ### Oracle存储过程、函数语法详解 #### 一、概述 Oracle数据库系统因其高效的数据处理能力,在企业级应用中被广泛采用。存储过程和函数作为Oracle数据库的重要组成部分,可以帮助开发人员编写更加灵活、高效的...

    oracle函数大全及存储过程语法

    本文将详细介绍Oracle中的函数和存储过程的语法,帮助你深入理解和运用这些核心概念。 一、Oracle函数大全 Oracle函数分为内置函数和自定义函数两大类。内置函数包括数学函数、字符串函数、日期时间函数、转换函数...

    oracle函数大全与存储过程语法-中文.rar

    本资源包“oracle函数大全与存储过程语法-中文.rar”涵盖了从Oracle 9i到10G版本的函数和存储过程的详细信息,旨在帮助用户深入理解和应用这些关键概念。 1. **Oracle函数大全**: - 函数是预定义的代码块,接受一...

    Oracle存储过程、函数和包

    - **定义**:包是一组相关的存储过程、函数和类型定义的集合。 - **组成**:包由两部分组成——包规范和包体。 - 包规范:声明了包中包含的存储过程和函数的接口。 - 包体:包含了实际的实现代码。 - **创建**: ...

    oracle高级语法(事物、函数、存储过程、触发器、异常)[参照].pdf

    Oracle 高级语法是指在 Oracle 数据库管理系统中使用的高级语法结构,包括事务、函数、存储过程、触发器、异常等。这些语法结构是 Oracle 数据库开发和管理的核心内容,对于数据库开发者和管理员来说是必备的知识。 ...

    Oracle中的函数、存储过程、包

    - **包**是封装相关函数和过程的容器,类似于Java中的接口,提供了一种组织和命名空间管理的方法。 - **包头(Specification)**:定义包的接口,包括函数和过程的签名,不包含实现细节。 - **包体(Body)**:...

    C语言函数大全(语法着色版)

    《C语言函数大全》是一部专为C语言学习者编写的参考书籍,其语法着色版使得代码更加醒目,便于理解。这份资源包含了丰富的C语言函数信息,是学习和提升C编程技能的重要工具。 C语言是一种基础且强大的编程语言,它...

    oracle 函数大全 存储过程语法

    本篇文章将针对“Oracle函数大全”和“存储过程语法”进行深入的探讨。 首先,Oracle函数是预定义的代码块,它们接收输入参数并返回一个值。函数分为内置函数和自定义函数。内置函数包括数学函数(如:ROUND、TRUNC...

    oracle函数大全及存储过程语法.rar

    本资源"oracle函数大全及存储过程语法.rar"显然是一个关于Oracle函数和存储过程的详细指南,涵盖了各种常用和高级的函数以及存储过程的编写和使用方法。 一、Oracle函数 Oracle提供了丰富的内置函数,包括数值函数...

    Oracle常用的一些语法、命令和函数

    这只是Oracle庞大功能库中的一小部分,实际上Oracle还包括了日期函数、聚合函数、序列操作、索引管理、触发器、存储过程、游标等多种功能,对于数据库管理员和开发人员来说,掌握这些基础知识是至关重要的。

    SQL高效学习语言包(基本语法,函数,存储过程,事务,高效示例)

    这个"SQL高效学习语言包"涵盖了SQL的基本语法、函数、存储过程以及事务处理,是学习和掌握SQL SERVER的重要资源。 一、SQL基本语法 SQL的基本语法包括数据查询、数据插入、数据更新和数据删除等操作。例如,SELECT...

    02-存储过程、函数、包和触发器

    在Oracle数据库中,存储过程、函数、包和触发器是PL/SQL编程的重要组成部分,它们提供了高级的数据处理和业务逻辑管理功能。 存储过程是预编译的PL/SQL代码块,设计用于执行特定任务。当你需要多次执行相同或类似的...

    RACLE_的过程,函数,包等创建

    ### Oracle的过程、函数与包创建详解 #### 一、过程(Procedure) 在Oracle数据库中,过程是一种存储在数据库中的程序单元,它不返回任何值,但可以接受输入参数,并通过输出参数传递数据。 ##### 创建过程的语法...

    SQL语句大全(存储过程、函数、基本语法)

    这个压缩包中的"SQL语句大全"很可能是一个详细的指南,涵盖了SQL的各种方面,包括基本语法、函数以及存储过程。下面将对这些主题进行深入的阐述。 首先,SQL的基本语法是所有查询的基础。它包括了SELECT语句,用于...

    plsql教程大全(语法入门、操作plsql、存储过程、函数、触发器、游标、包、返回类型)

    语法入门、操作plsql、存储过程、函数、触发器、游标、包、返回类型;一个execle表轻松搞定。

Global site tag (gtag.js) - Google Analytics