数据库——存储过程、存储函数与包

前言

一道题目问“存储过程、存储函数与包作用和联系”，发现自己对于这几个概念还不太了解，就此记录一下

正文

过程化SQL块主要有两种类型，即命名块和匿名块。匿名块每次执行都要进行编译，它不能被存储到数据库中，也不能在其他过程化SQL块中调用。过程，包和函数是命名块，它们被编译后保存在数据库中，称为持久性存储模块，可以被反复调用，运行速度较快

存储过程

存储过程是由过程化SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，因此称它为存储过程，使用时只要调用即可。

• 不带参数

create procedure 存储过程名
as
begin
	SQL语句1
    SQL语句2
...
end

例子：创建一个存储过程procOther，在studentcourse表中，如果学号为“8001”的平均成绩大于等于80分则输出信息“成绩优秀”，否则输出信息“成绩一般”

create procedure proOther2
as avg_grade float
begin
	select avg_grade = avg(grade)
	from studentcourse
	where sno='8001'
	if avg_grade >=80 then
	dbms_output.put_line('成绩优秀')
	else
	dbms_output.put_line('成绩一般')
	end if
end

• 带参数
  • 带输入参数
    例：创建一个带输入参数的存储过程ProcGetStuInfo,功能：根据输入的院系名称，获取属于该院系的所有学生信息

create procedure ProcGetStuInfo(intput_DepartmentName nvarchar(20))
as
begin
	select sno,sname,sex,sage
	from Department d,Student s
	where d.DepartmentID=s.DepartmentID and
	DepartmentName=intput_DepartmentName
end

• 带输出参数
  例：创建一个存储过程procGetMyScore,带有1个输出参数v_grade,功能：查找学号为“8001”这个学生所选修的课程号为“001”的这门课程的成绩

create procedure procGetMyScore(v_grade out float)
as
begin 
	select v_grade = grade
	from StudentCourse
	where sno='8001'and cno='001'
end

存储函数

用户自定义函数和存储过程一样，包含一系列SQL语句，不同之处在于函数必须指定返回类型。

• 标量函数
  返回单个标量值的函数，标量值是指一行一列的结果集

create function 函数名（参数名 数据类型）
return 返回数据类型
as
begin
	函数体
	return 标量表达式
end

例：创建一个标量函数fnGetYearSalary,它带有一个输入参数v_Rate,返回一个标量值：年薪，数据类型money

create function fnGetYearSalary(v_Rate money)
return money
as
begin
	set YearSalary=v_Rate *365
	return YearSalary
end

• 表值函数
  表值函数以表的形式返回一个值，即它返回的是一个表
  例：创建一个表值函数fnGetStudent1,它返回一个表，功能：根据输入的院系名称，查找所有属于该院系的学生信息

create function 函数名（参数名 数据类型）
return table
as
return(
	select 语句
)

create function fnGetStudent1(v_DepartmentName nvarchar(20))
return table
as
return (
	select sno,sname,sex,sage
	from Student s,Department d
	where s.DepartmentID=d.DepartmentID and d.DepartmentName=v_DepartmentName
)

包

包是一组相关的过程、函数及其使用的游标，变量等构成的程序单元，存储于数据库中供用户使用，由此可见包可以方便地将存储过程和函数组织到一起简化应用设计、提高性能、实现信息隐藏、子程序重载
关于一篇