存储过程
定义
存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。
语法
create or replace procedure
<过程名>[参数名 参数模式(in/out/int out) 数据类型:=value]
begin
PL/SQL语句;
end;
参数模式
- In 参数
输入参数，调用时存储过程待接收的参数
可以是常数，数据量，初始化变量或表达式
创建带in参数存储过程
create or replace procedure
pro_InsertClassInfo(strClassID varchar2,strClassName Varchar2)
is
begin
insert into classinfo(classid,classname)
values(strClassID,strClassName);
end;
调用存储过程
exec pro_insertclassinfo('201611520','计算机科学与技术');
示列
创建存储过程，若记录存在则更新，不存在则添加记录
create or replace procedure
pro_merageclassinfo(strid varchar2,strname varchar2)
is
recount int;
begin
select count(*) into recount from classinfo where classid=strid;
if recount>0 then
update classinfo set classname=strname where classid=strid;
else
insert into classinfo(classid,classname) values (strid,strname);
end if;
end;
exec pro_merageclassinfo('201611520','大数据与智能工程');
// classid已存在执行更新操作
exec pro_merageclassinfo('201811520','区块链技术');
//新纪录执行插入操作
- Out 参数
输出参数，存储过程运行后的结果赋值给输出参数，显示到屏幕
参数只能是变量，不能是常数或表达式
create or replace procedure
pro_getavgscore(strno varchar2,avgscore out number)
is
begin
select avg(studscore) into avgscore from studscoreinfo
where studno=strno;
end;
调用存储过程
declare
avgscore number;
begin
pro_getavgscore('20010704035',avgscore);
dbms_output.put_line(avgscore);
end;
<meta charset="utf-8">
picone.png
- In Out 参数
输入和输出，接受值并返回已更新的值
参数只能是变量，不能是常数或表达式
创建存储过程
create or replace procedure
Pro_getstudscore(strno varchar2,avgscore in out number)
is
begin
dbms_output.put_line(avgscore+5);
select avg(studscore) into avgscore
from studscoreinfo
where studno=strno;
end;
调用存储过程
declare
avgscore number;
begin
Pro_getstudscore('20010505001',avgscore);
dbms_output.put_line(avgscore);
end;
函数
语法
create or replace function
<函数名>[参数1，参数2...参数n]
return 返回值的数类型
is
begin
PL/SQL语句;
end;
参数说明
(变量名1 数据类型1:=value1,变量名2 数据类型2:=value2)
函数实例
学生成绩统分函数，规则：多选或错选或不选给0分,少选给选对的分
create or replace function
getitemscore(Stand_ans varchar ,custor_ans varchar2)//参数是数据表中的标准答案和学生的答案
return int //函数的返回值是int类型
is
Lencustor int:=length(custor_ans);//获取学生答案的长度
begin
if Lencustor>length(Stand_ans) or Custor_ans is null then
return 0;//多选或没选返回0分
end if;
for i in 1..Lencustor
loop
if instr(Stand_ans,substr(custor_ans,i,1)) = 0 then
return 0;//如果学生答案中有记录没在标准答案中，即有错选答案
end if;
end loop;
return Lencustor; //没有错选，给选对的分，分数就是答案的长度
end;
测试这个统分函数
select getitemscore('ABC','AC') from dual;
//少选的情况
select getitemscore('AC','ABC') from dual;
//多选的情况
select getitemscore('ABC','ACD') from dual;
//错选的情况
picthree.png
统计600个学生共60000条记录的分数
select studno,
sum(getitemscore(stand_anx,custor_anx))*100/
sum(Length(stand_anx)) as studscore
from answer
group by studno
picfour.png
ps:六万条数据统分，set timing on,所花时间是1.46s,oracle是真的牛逼
练习
- 编写一个函数，实现求N!（即N 的阶乘），测试5!（即5 的阶乘）。
A.创建函数
create or replace function getjc(n int)
return number
is
k number:=1;
begin
for i in 1..n
loop
k:=k*i;
end loop;
return k;
end;
B.调用函数
select getjc(5) from dual;
- 创建一个简单的存储过程，求S=1!+2!+3!+4!+…+N!，直到S 大于10000 时N 的值和S的值。(注：阶乘可以写一个函数完成)
A.创建存储过程
create or replace procedure
proce_getsumjc(k out number,s out number)
is
i number:=1;
begin
s:=0;
k:=1;
loop
k:=K*i;
i:=i+1;
s:=s+k;
if s > 10000 then
exit;
end if;
end loop;
dbms_output.put_line('s='||s);
dbms_output.put_line('n='||k);
end;
B.调用存储过程
declare
K number;
s number;
begin
proce_getsumjc(k,s);
end;
- 创建一个带输入参数的存储过程，输入分数参数，执行存储过程得到平均分大于该分数的学生统计成绩信息（包括学号，姓名，平均分，课程门数字段）。
A.创建存储过程
create or replace procedure procedure_avg(inputscore number)
is
sstudno studinfo.studno%Type;
sstudname studinfo.studname%Type;
savgscore studscoreinfo.studscore%Type;
coursecount number;
cursor mycur is
select s.studno,studname,avg(studscore),count(*) count
from studinfo s,studscoreinfo ss
where s.studno=ss.studno
group by s.studno,studname
having avg(studscore)> inputscore;
begin
open mycur;
fetch mycur into sstudno,sstudname,savgscore,coursecount;
while mycur%found
loop
dbms_output.put_line(sstudno||sstudname||savgscore||coursecount);
fetch mycur into sstudno,sstudname,savgscore,coursecount;
end loop;
close mycur;
end;
//这里会查询出多条记录，oracle并不能操作一个结果集，只能通过游标取出每条记录，我将下一篇博客学习游标
B.调用存储过程
exec procedure_avg(80);
- 创建带两个输入参数和一个输出参数的存储过程，执行存储过程时，输入参数为分数段，输出参数为得到该分数段的人数。
A.创建存储过程
create or replace procedure
procedure_count(minscore in number,maxscore in number,countp out number)
is
begin
select count(*) into countp
from
(
select studno
from studscoreinfo
group by studno
having avg(studscore) between minscore and maxscore
);
dbms_output.put_line(countp);
end;
B.调用存储过程
declare
countp number;
begin
procedure_count(60,70,countp);
end;
- 创建一个学生成绩统计函数（GetEveryItemScore），多选记 0 分，少选记选对分。
A.创建函数
Create Or Replace Function GetItemScore(Stand_Ans
varchar2,Custor_Ans varchar2)
return int is
LenCustor int:=length(Custor_Ans);
begin
if LenCustor>length(stand_ans) or Custor_Ans IS NULL then
return 0;//多选或没选返回0分
end if;
for i in 1..LenCustor
loop
if instr(stand_ans,substr(custor_ans,i,1))=0 then
return 0;
end if;//如果学生答案中有记录没在标准答案中，即有错选答案
end loop;
return LenCustor;//返回答案的长度即得分
end;
B.调用函数
select getitemscore('ABC','BC') from dual;
- 将当前文件夹下电子表格(StudScore.xls)所有数据导入到自己的数据库中，利用题目 5 中创建的函数统计各学生成绩。
A.创建表
答案表
create table Answer
(
stand_anx varchar2(15),
custor_anx varchar2(15),
studno varchar2(20)
)
成绩表
create table studscore
(
studno varchar2(15) primary key,
studname varchar2(15),
studscore number(4,1)
)
B.导入数据
load data infile 'c:\csv'
append into table answer
fields terminated by ','
(stand_anx,custor_anx,studno)
load data infile 'c:\StudScore.csv'
append into table studscore
fields termiated by ','
(studno,studname,studscore)
C.计算得分
select studno,
sum(getitemscore(stand_anx,custor_anx))*100/
sum(Length(stand_anx)) as studscore
from answer
group by studno
- 利 用 6 题 查 询 结 果 ， 创 建 一 个 学 生 成 绩 统 计 视 图 （ ViewGetStudScore ）， 包 括StudNo,StudScore 字段。并写出将视图中的成绩更新到表 StudScore 中的 SQL 语句。
merge into studscore
using v_getstudcore
on(v_getstudcore.studno=studscore.studno)
when matched then
update set studscore.studscore=v_getstudcore.studscore
- 利用 7题的结果，将表StudScore中的前20名（以分数高低排）导入新表(Top20StudScore)，包括学号、姓名、成绩字段。
create table top20studscore
as
select * from
(
select * from studscore
order by score desc
)A
where rownum<=20