ORACLE之存储过程和函数

存储过程

定义

存储过程（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;

示列

• 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;
//错选的情况

统计600个学生共60000条记录的分数

 
select studno,
sum(getitemscore(stand_anx,custor_anx))*100/
sum(Length(stand_anx)) as studscore
from answer
group by studno

ps:六万条数据统分，set timing on,所花时间是1.46s,oracle是真的牛逼

练习

1. 编写一个函数，实现求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;

2. 创建一个简单的存储过程，求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;

3. 创建一个带输入参数的存储过程，输入分数参数，执行存储过程得到平均分大于该分数的学生统计成绩信息（包括学号，姓名，平均分，课程门数字段）。

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); 

4. 创建带两个输入参数和一个输出参数的存储过程，执行存储过程时，输入参数为分数段，输出参数为得到该分数段的人数。

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;

5. 创建一个学生成绩统计函数（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;

6. 将当前文件夹下电子表格(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

7. 利 用 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

8. 利用 7题的结果，将表StudScore中的前20名（以分数高低排）导入新表(Top20StudScore)，包括学号、姓名、成绩字段。

create table top20studscore
as
select * from 
(
select * from studscore
order by score desc
)A
where rownum<=20