关于数据库中函数和过程的定义和实例
用delimiter命令来把语句定界符从 ;变为//。这样就允许在程序体用;定界符传递到服务器，而不是被mysql自己来解释。
mysql> delimiter //
补充练习：procedure 过程
PL/SQL：Procedure Language/SQL
函数（function ):计算，一定要有返回值
过程（procedure)：随便干啥，可以没有返回值
包（package）
触发器（trigger）（类似监听器，看着数据库，是否有异常，是隐含运行的）
--输入班号，输出最高分的学生名字和总成绩
-----------------------------
create or replace procedure cal_peng(
p_class_id in number, --in可以省略 --长度不要写
p_student_name out char,
p_total_score out number --头
)
------------------------------
is
-- 定义变量(可以不写) --变量
----------------------------
begin
--程序体
--体
end;
----------------------------
select *
from (
select stu.student_name,
sum(per.test_score) total_score
from t_student_peng stu
join
t_performance_peng per
on stu.student_id=per.student_id
where stu.class_id=1
group by stu.student_name
order by total_score desc)
where rownum<2;
--输入班号，输出最高分的学生名字和总成绩
create or replace procedure cal_peng(
p_class_id in number,
p_student_name out char,
p_total_score out number
)
is
begin
select student_name,total_score
into p_student_name,p_total_score
from (
select stu.student_name,
sum(per.test_score) total_score
from t_student_peng stu
join
t_performance_peng per
on stu.student_id=per.student_id
where stu.class_id=p_class_id
group by stu.student_name
order by total_score desc)
where rownum<2;
end;
/
set serveroutput on --打开输出开关，之后会有显示 --默认输出是off关闭的，不显示
--匿名块，用来测试过程或函数
declare
p_student_name char(20);
p_total_score number;
begin
cal_peng(&no,P_student_name, --&no 代表随着运行可以自己输入想要的数字
P_total_score);
--向控制台输出变量值，system.out.print
dbms_output.put_line(p_student_name);
dbms_output.put_line(p_total_score);
end;
/
--可以用&abc符号表示在运行时输入变量值，在测试时用
函数
--计算学生总分：学生表，成绩表
select *
from (
select stu.student_name,
sum(per.test_score) total_score
fr
om t_student_peng stu
join
t_performance_peng per
on stu.student_id=per.student_id
where stu.class_id=1
group by stu.student_name
order by total_score desc)
where rownum<2;
create or replace function maxScore_peng
(p_class_id number)
return number
is
v_total_score number;
begin
--v_total_score赋值为指定班的最高分
select total_score into v_total_score
from (
select stu.student_name,
sum(per.test_score) total_score
from t_student_peng stu
join
t_performance_peng per
on stu.student_id=per.student_id
where stu.class_id=p_class_id
group by stu.student_name
order by total_score desc)
where rownum<2;
return v_total_score;
end;
/
select maxscore_peng(1) from dual;
select maxscore_peng(2) from dual;
select stu.student_name,sum(per.test_score)
from t_student_peng stu
join t_performance_peng per
on stu.student_id=per.student_id
where stu.class_id =1
group by stu.student_name
having sum(per.test_score)=maxscore_peng(1); --1班的最高分
create or replace function maxScore_peng1
(p_class_id number)
return number
is
v_total_score number;
begin
--v_total_score 赋值为指定班的最高分
select student_id into v_student_id
from (
select stu.student_name,
sum(per.test_score) total_score
from t_student_peng stu
join
t_performance_peng per
on stu.student_id=per.student_id
where stu.class_id=p_class_id
group by stu.student_id,stu.student_name
order by total_score desc)
where rownum<2;
return v_total_score;
end;
/
删除函数：
mysql> drop function first_func ；
查看函数
1) show function status
显示数据库中所有函数的基本信息
2)查看某个具体函数
mysql>show create function function;
函数只能通过return语句返回单个值或者表对象。而存储过程不允许执行return，但是通过out参数返回多个值。
函数是可以嵌入在sql中使用的,可以在select中调用，而存储过程不行。
函数限制比较多，比如不能用临时表，只能用表变量．还有一些函数都不可用等等．而存储过程的限制相对就比较少
一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。
当存储过程和函数被执行的时候，SQL Manager会到procedure cache中去取相应的查询语句，如果在procedure cache里没有相应的查询语句，SQL Manager就会对存储过程和函数进行编译。
Procedure cache中保存的是执行计划 (execution plan) ，当编译好之后就执行procedure cache中的execution plan，之后SQL SERVER会根据每个execution plan的实际情况来考虑是否要在cache中保存这个plan，评判的标准一个是这个execution plan可能被使用的频率；其次是生成这个plan的代价，也就是编译的耗时。保存在cache中的plan在下次执行时就不用再编译了。
--
授权共享
grant select on emp_peng1 to scott; --只给了scott 查看的权限
授权之后 scott 就可以查看用户open的emp_peng1表
select * from open.emp_peng1
--取消授权
revoke select on emp_peng1 from scott