Body rule is that students can according to pass English level 4, 6, 8 body degree English courses, through English four class is equivalent to a degree in English at 75 points, through the English level 6 is equivalent to a degree in English at 85 points, through English grade eight is equivalent to a degree in English at 95 points, can also according to the written approval of the instructor's application body other courses,
Body application of information including: student student number, apply for body course number, apply for body corresponding scores, apply for body reason, state (including pending, has agreed to, has been dismissed, the default value is to be processed)
Database structure design, and implement the following "body to deal with" stored procedure: the incoming application id, the application is approved, the stored procedure will set the body whether to approve the status of the item, modify student achievement table, still should check, if applied 4,6,8 application as body, with the corresponding body rules after the course grade is correct, if not correct, in the correct execution,
A task 2: writing test cases to test the stored procedure
Write each use case for a storage process, should the test data is added to a stored procedure, after the call to apply for a stored procedure, and need to verify execution in the process of storage data is correct, after such a test case is a context-free, use case features list:
Table 1, to empty students
2, empty student achievement table
3, the empty body form
4, in the student table to insert a record
5, inserted in the body form a record
6, call the body for the stored procedure
7, verify whether students have the data in the table right
8, return test result
CodePudding user response:
CREATE OR REPLACE PROCEDURE PROP_APPLY_FOR_NOEXAMPLE (applyNo in varchar2 (32), isApproval in Boolean)
As v_sNO varchar2 (32);
V_cNO varchar2 (32).
V_score varchar2 (5);
V_reason varchar2 (100);
V_state char (1);
The begin
Student id, select students applying for body course number, for body course corresponding scores, apply for body reason, state into v_sNO v_cNO, v_score, v_reason, v_state where application id=applyNo;
While v_state==has agreed & amp; & Apply for loop body course number==English
If v_reason==4 then
V_score:=75
end if;
If v_reason==6 then
V_score:=85
end if;
If v_reason==8 then
V_score:=95
end if;
Update t_course set grades=v_score where sNo=v_sNO;
end loop;
end;