实验11-存储过程

# 实验11、存储过程
## 11.1 实验目的
	为了提高对基本SQL语言的综合运用能力，掌握存储过程的基本操作包括创建存储过程、执行存储过程、修改和删除存储过程等。
## 11.2 实验内容
1. 创建存储过程
* 创建一个不包含任何参数的简单存储过程：例如创建一个名为proc_CO的存储过程，查询所有课程的信息。	
* 创建一个带有输入参数的存储过程：例如创建一个名为proc_RE的存储过程，查询指定课程的学生成绩信息。
* 创建一个带有输入参数和输出参数的存储过程：例如创建一个名为pro_课程号的存储过程，返回指定教室所授的课程号。
* 创建一个带有输入参数的嵌套调用的存储过程：例如创建一个名为proc_嵌套_学生成绩的存储过程，要求返回指定学生姓名的学习成绩信息。
2. 执行存储过程
* 执行不带参数的存储过程，例如执行proc_CO；
* 执行带参数的存储过程，例如执行proc_RE；
* 执行带输入参数和输出参数的存储过程，例如执行pro_课程号;
3. 修改存储过程
4. 删除存储过程
5. 查看存储过程
## 11.3 实验步骤
1. 创建存储过程
* 创建一个不包含任何参数的简单存储过程proc_CO，查询所有课程的信息。
```sql
CREATE OR REPLACE PROCEDURE proc_CO () 
AS
DECLARE 
stu_Cno  VARCHAR(6) ;
stu_Cname  VARCHAR(25) ;
cou_Credit  INT;
CURSOR  C  IS  SELECT Courses.Cno, Courses.Cname, Courses.CreditHour  FROM Courses;  
BEGIN 
   OPEN C; 
   LOOP 
      FETCH C INTO stu_Cno, stu_Cname, cou_Credit; 
      EXIT WHEN C%NOTFOUND;  
      RAISE  info 'Cno: % , Cname: % , CreditHour: %' , stu_Cno, stu_Cname, cou_Credit;
   END LOOP; 
   CLOSE C; 
END;  
```
* 创建一个带有输入参数的存储过程proc_RE，查询指定课程的学生成绩信息。其中输入参数用于接收课程号。
```sql
CREATE OR REPLACE PROCEDURE proc_RE(inCno varchar(6)) 
as 
DECLARE
stu_Sno  VARCHAR(10) ;
stu_Sname  VARCHAR(16) ;
cou_name  VARCHAR(25) ;
cou_grade   INT;
CURSOR  C  IS  select Students.Sno,Students.Sname,Courses.Cname,Reports.Grade  from  Students,Courses,Reports
where Students.Sno=Reports.Sno and Reports.Cno=Courses.Cno and Courses.Cno=inCno;
BEGIN 
   OPEN C; 
   LOOP 
      FETCH C INTO stu_Sno, stu_Sname, cou_name,cou_grade; 
      EXIT WHEN C%NOTFOUND;  
      RAISE  info 'Sno: % , Sname: % , Cname: % , Grade: %' , stu_Sno, stu_Sname, cou_name, cou_grade;
   END LOOP; 
   CLOSE C; 
END;  
```
* 创建一个带有输入参数和输出参数的存储过程proc_课程号，返回指定教师所授课程的课程号。其中输入参数用于接收教师的教师编号，输出参数用于返回该教师所授课程的课程号
```sql
create or replace procedure proc_TCNO(Ttno Varchar(6),out Tcno Varchar2(1000)) 
AS
DECLARE
Tea_lno    Varchar(10);
CURSOR C IS select Teachers.Cno from Teachers where Teachers.Tno=Ttno;
begin
    OPEN C; 
    LOOP 
      FETCH C INTO Tea_lno;
      if Tcno ISNULL then
      Tcno  :=Tea_lno;
      else
      EXIT WHEN C%NOTFOUND;  
      Tcno  :=Tcno ||','||Tea_lno;
      END IF;
   END LOOP; 
   CLOSE C;  
end;
```
* 创建并执行带有输入参数的嵌套调用的存储过程proc_嵌套_学生成绩，输入学生的姓名，可以查询学生的每门课的成绩。首先创建一个带有输入和输出参数的存储过程proc_STU，查询指定学生姓名的学生编号，输入参数保存在“@Sname”，输出参数保存在“@Sno”中，最后根据Sno在Reports表中查询成绩。
```sql
create or replace procedure proc_STU(TSname Varchar(16),out TSno Varchar2(6)) 
AS
DECLARE
ttSno Varchar2(6);
CURSOR C IS SELECT Students.Sno from Students where Students.Sname = TSname;  
begin 
open C;
fetch C into ttSno;
TSno :=ttSno;
RAISE  info'Sno: % ',TSno;
close C;
end;

CREATE OR REPLACE PROCEDURE proc_RE(TSname Varchar(16),out TSno Varchar2(6)) 
as 
DECLARE
stu_Sname  VARCHAR(16) ;
cou_name  VARCHAR(25) ;
cou_grade   INT;
stu_Sno  VARCHAR(6) ;
statement  VARCHAR2(200); 
TYPE CURSOR_TYPE IS REF CURSOR; 
C CURSOR_TYPE;  
BEGIN 
RAISE  info'stu_Sno: % ','start';
statement  :='call proc_STU(:col_1,:col_2)';
EXECUTE IMMEDIATE statement 
        USING IN TSname, OUT TSno; 
  stu_Sno :=TSno;
RAISE  info'stu_Sno: % ', stu_Sno;
   OPEN  C for  select Students.Sno,Students.Sname,Courses.Cname,Reports.Grade  from  Students,Courses,Reports
Where Students.Sno=Reports.Sno and Reports.Cno=Courses.Cno and Students.Sno= stu_Sno;
   LOOP 
      FETCH C INTO stu_Sno, stu_Sname, cou_name,cou_grade; 
      EXIT WHEN C%NOTFOUND;  
      RAISE  info 'Sno: % , Sname: % , Cname: % , Grade: %' , stu_Sno, stu_Sname, cou_name, cou_grade;
   END LOOP; 
   CLOSE C; 
END;  
```

* 使用游标实现以下存储过程：该存储过程根据传入一个参数删除符合条件的学生记录。例如传入学分，当学分达到这个值以上的学生就作为已经完成了学业，可以删除该学生的记录。
```sql
CREATE OR REPLACE PROCEDURE sp_delete_graduate ( min_credit  integer)
AS
DECLARE
    stu_sno  VARCHAR(10) ;
CURSOR  C  IS SELECT  Sno  FROM Students WHERE creditHours>= min_credit ;  
BEGIN 
   OPEN C; 
   LOOP 
      FETCH C INTO stu_sno; 
      EXIT WHEN C%NOTFOUND;  
       delete from Reports where Sno=stu_sno;
       delete from  Students where Sno = stu_sno;
   END LOOP; 
   CLOSE C; 
END;  
```
2. 执行存储过程
* 执行不带参数的存储过程proc_CO，查询所有课程的信息。
```sql
Call  proc_CO ();
```
* 执行带输入参数的存储过程proc_RE，查询指定课程的成绩信息。
```sql
Call proc_RE('c01') ;
```
* 执行带输入和输出参数的存取过程proc_课程号，查询指定教师编号所授课程的课程号，输入参数的值由变量“@Tno”给出，输出参数的值保存在“@Cno”中。
```sql
call proc_TCNO('T01',@Tcno);
```

3. 修改存储过程
修改简单存储过程proc_CO，使它只查询课程号和课程名两类信息。
```sql
CREATE OR REPLACE PROCEDURE proc_CO () 
AS
DECLARE 
stu_Cno  VARCHAR(6) ;
stu_Cname  VARCHAR(25) ;
CURSOR  C  IS  SELECT Courses.Cno, Courses.Cname FROM Courses;  
BEGIN 
   OPEN C; 
   LOOP 
      FETCH C INTO stu_Cno, stu_Cname; 
      EXIT WHEN C%NOTFOUND;  
      RAISE  info 'Cno: % , Cname: % ' , stu_Cno, stu_Cname;
   END LOOP; 
   CLOSE C; 
END;  
```
4. 删除存储过程
```sql
drop procedure proc_CO;
```
删除存储过程，例如删除proc_CO存储过程。
注意删除之前，要查看存储过程的嵌套关系，如果某个存储过程被另一个存储过程调用，则不能轻易被删除。
5. 查看存储过程
* 查看存储过程proc_嵌套_学生成绩的所有者、创建时间和各个参数的信息。
```sql
SELECT * from pg_proc where proname='proc_re';
```

* 查看存储过程的依赖关系proc_嵌套_学生成绩
```sql
SELECT prosrc from pg_proc where proname='proc_re';
```
## 11.4 实验要求
1. 实验之前请细细阅读实验总体要求与说明指导书
2. 在OpenGauss的控制台环境中，完成以上实验(1)-(5)步所有SQL数据定义操作，并完成实验报告。

大数据学习

导航

最近发表

友情链接