The design of the storage sub-program

---student table
CREATE TABLE Student (
SNO VARCHAR2 (7) PRIMARY KEY,
Sname VARCHAR2 (18),
SSex CHAR (3),
Sage SMALLINT,
Sdept VARCHAR2 (50),
Savggrade number (3,0),
Spicture BLOB
);
SELECT * from Student;
---schedule
CREATE TABLE Course (
Cno VARCHAR2 (4) PRIMARY KEY,
Cname VARCHAR2 (50),
Cpno VARCHAR2 (4),--Preamble Lesson
Ccredit number (2,0),
FOREIGN KEY (cpno) REFERENCES Course (Cno)
);

---selected timetable
CREATE TABLE SC (
Sno VARCHAR2 (7),
Cno VARCHAR2 (4),
Grade number (3,0),
PRIMARY KEY (SNO,CNO),
FOREIGN KEY (Sno) REFERENCES Student (Sno),
FOREIGN KEY (Cno) REFERENCES Course (Cno)
);

Test data:
INSERT into student (sno,sname,ssex,sage,sdept) VALUES (' 9512101 ', ' Li Yong ', ' male ', 19, ' computer Department ');
INSERT into student (sno,sname,ssex,sage,sdept) VALUES (' 9512102 ', ' Liu Chen ', ' Male ', 20, ' computer Department ');
INSERT into student (sno,sname,ssex,sage,sdept) VALUES (' 9521101 ', ' Zhang Li ', ' Male ', 22, ' Information system ');
INSERT into student (sno,sname,ssex,sage,sdept) VALUES (' 9521102 ', ' Wu Bing ', ' female ', 21, ' Information System ');
INSERT into student (sno,sname,ssex,sage,sdept) VALUES (' 9531101 ', ' Qianxiaoping ', ' female ', 18, ' math Department ');
INSERT into student (sno,sname,ssex,sage,sdept) VALUES (' 9531102 ', ' King strong ', ' male ', 19, ' math Department ');

Insert into course values (' 1 ', ' data processing ', null,2);
Insert into course values (' 2 ', ' math ', null,2);
Insert into course values (' 3 ', ' Operating system ', ' 1 ', 3);
Insert into course values (' 4 ', ' C language programming ', ' 1 ', 4);
Insert into course values (' 5 ', ' data structure ', ' 4 ', 4);
Insert into course values (' 6 ', ' Database ', ' 5 ', 4);
Insert into course values (' 7 ', ' Information system ', ' 6 ', 4);

INSERT into SC values (' 9512101 ', ' 1 ', 92);
INSERT into SC values (' 9512101 ', ' 2 ', 85);
INSERT into SC values (' 9512101 ', ' 3 ', 88);
INSERT into SC values (' 9512102 ', ' 2 ', 90);
INSERT into SC values (' 9512102 ', ' 3 ', 100);

SELECT * from Student;
SELECT * from course;
SELECT * FROM SC;

--2.1
Create or replace procedure Select_avg_proc
(P_sno in Student.sno%type,
P_avg out Student.savggrade%type)
As
Begin
Select AVG (grade) into P_avg from SC where sc.sno=p_sno;
End
--Test a specified parameter--
Declare
V_sno student.sno%type: = ' 9512101 ';
V_avg Student.savggrade%type;
Begin
Select_avg_proc (V_SNO,V_AVG);
Dbms_output.put_line (' Study number for ' | | v_sno| | ' The average student's grade is ' | | NVL (v_avg,0));
End

--Test two input parameters--
Declare
V_avg Student.savggrade%type;
Begin
Select_avg_proc (&V_SNO,V_AVG);
Dbms_output.put_line (' Study number for ' | | &v_sno| | ' The average student's grade is ' | | NVL (v_avg,0));
End





--2.2
Create or Replace function Select_avg_fun (P_sno student.sno%type)
return number
As
V_avg Student.savggrade%type;
Begin
Select AVG (grade) into V_avg from SC where sc.sno=p_sno;
return v_avg;
End
--Test a specified parameter--
Declare
V_sno student.sno%type: = ' 9512101 ';
V_avg Student.savggrade%type;
Begin
V_avg: =select_avg_fun (V_SNO);
Dbms_output.put_line (' Study number for ' | | v_sno| | ' The average student's grade is ' | | NVL (v_avg,0));
End
--Test two input parameters--
Declare
V_avg Student.savggrade%type;
Begin
Select_avg_proc (&V_SNO,V_AVG);
V_avg: =select_avg_fun (&V_SNO);
Dbms_output.put_line (' Study number for ' | | &v_sno| | ' The average student's grade is ' | | NVL (v_avg,0));
End

--2.3
Create or replace procedure Get_info_proc
(P_sno in Student.sno%type,
P_avg out Student.savggrade%type,
Sc_cursor out Sys_refcursor
)
As
Begin
Select AVG (grade) into P_avg from SC where sc.sno=p_sno;
Open sc_cursor for select Course.cname, Sc.grade from COURSE,SC where Sno=p_sno;
End

--Test A
Declare
V_avg Student.savggrade%type;
Sc_cursor Sys_refcursor;
V_cname Course.cname%type;
V_grade Sc.grade%type;
Begin
Get_info_proc (&p_sno,v_avg,sc_cursor);
Dbms_output.put_line (' Study number for ' | | &p_sno| | ' The average student's grade is ' | | NVL (v_avg,0));
Dbms_output.put_line ('--------------------------------------------------');
Loop
Fetch sc_cursor into V_cname,v_grade;
Exit when Sc_cursor%notfound;
Dbms_output.put_line (' Course Name ' | | Rpad (v_cname,20, ') | | ' Course results are ' | | NVL (v_avg,0));
End Loop;
End

--Defining package specifications
Create or Replace package student_pkg
As
Type mycursor is REF CURSOR;
Procedure Select_all (outcursor out mycursor);
Procedure Selecte_all_by_sno (P_sno in Varchar2,outcursor out mycursor);
Procedure Delete_all_by_sno (P_sno in varchar2);
End student_pkg;


--Defining the package body
Create or replace package body student_pkg
As
Procedure Select_all (Outcursor out MyCursor)
As
Begin
Open Outcursor for select * from student;
End Select_all;

Procedure Selecte_all_by_sno (P_sno in Varchar2,outcursor out MyCursor)
As
Begin
Open outcursor for ' select * from student where Sno=:sno ' VC
--Replace with variable P_sno: Sno
Using P_sno;
End Selecte_all_by_sno;

Procedure Delete_all_by_sno (P_sno in VARCHAR2)
As
Begin
Delete from student where Sno=p_sno;
End Delete_all_by_sno;
End student_pkg;



Declare
V_outcursor Sys_refcursor;
V_student_row Student%rowtype;
V_sno student.sno%type: = ' 9512101 ';
Begin
Student_pkg.selecte_all_by_sno (V_sno,v_outcursor);
Loop
Fetch v_outcursor into V_student_row;
Exit when V_outcursor%notfound;
Dbms_output.put_line (v_student_row.sno| | ' ' | | V_student_row. Sname);
End Loop;
End

The design of the storage sub-program