Database Case Analysis

Data | database
Archives Management System--student status translation system
A Case Introduction
There are two tables in the Student Management database, which are about the information of the students in a certain university, and they are the EMP table and

Dept table, the structure of the two tables is as follows:
(1) Student basic information table student
(2) Grade of the score sheet
Requirements are as follows:
1, according to the above table structure to establish the corresponding table, and each table to write 5 sets of legitimate data.
2, the control of related tables, so that "outstanding (90 points)" of the student scholarship subsidy rose 20%.
3, the establishment of a journal to track the changes in subsidies.
4, set up test kits.
Two The analysis and realization of the case
From the introduction of the previous case, it is not difficult to see that the 1 point of Inquiry is the basic SQL statement, the request 2 main study compound query;

The application of the trigger, requiring 4 of the study surface is relatively more, not only to investigate the package creation, but also to investigate the test method in Pl/sql

。 Understanding the knowledge of these visits, you can solve them all.
Requirement 1:
First, you can create two tables based on the structure of the previous table:
--Create basic Student Information table
CREATE TABLE EMP (emp_id number (5), Emp_name varchar2 (), emp_salary number (4));
--Create a score sheet
CREATE TABLE Dept (dept_id Number (3), Dept_name varchar2 (), emp_id number (5));
Once you have established the table, you can write the data to the table, where you write the code that adds the table record to the appropriate stored procedure.
/* Stored procedures for adding records to the EMP table * *
Create or Replace procedure ins_table_emp (p_emp_id number,p_emp_name

Varchar2,p_emp_salary number) as
V_EMP_ID number:=p_emp_id;
V_emp_name Varchar2 (a): =p_emp_name;
V_emp_salary number:=p_emp_salary;
Begin
INSERT into EMP values (v_emp_id,v_emp_name,v_emp_salary);
End Ins_table_emp;
/* Stored procedures for adding records to the Dept table * *
Create or Replace procedure ins_table_dept (p_dept_id number,p_dept_name

VARCHAR2,P_EMP_ID number) as
V_DEPT_ID number:=p_dept_id;
V_dept_name Varchar2 (a): =p_dept_name;
V_EMP_ID number:=p_emp_id;
Begin
INSERT INTO Dept values (V_DEPT_ID,V_DEPT_NAME,V_EMP_ID);
End Ins_table_emp;
/* Call the corresponding stored procedure implementation record add * *
Begin
Ins_table_emp (10000, ', 4000);
Ins_table_emp (10001, '?? Èy ', 2300);
Ins_table_emp (10002, ' 3?t ', 3500);
Ins_table_emp (10003, ' à??? ', 3500);
Ins_table_emp (10004, ' Á?ò? ', 3500);
Ins_table_dept ("Dd?t2", 10000);
Ins_table_dept ("Dd?t2", 10001);
Ins_table_dept ("Dd?t2", 10002);
Ins_table_dept (112, '?? Ê?2? ', 10003);
Ins_table_dept (113, ' êd3?2? ', 10004);
End
Requirement 2:
Giving a salary increase to a designated department, which is actually a composite query, requires the first of all employees in the department to be selected, and then

Make consequential changes to the salaries of these employees. In accordance with this idea, the code is as follows:
(Note that the department that will pay a raise as a parameter is more flexible in such a stored procedure.) ）
Create or Replace procedure add_salary (P_dept_name varchar2) as
V_dept_name Varchar2 (a): =p_dept_name;
Begin
Update EMP set EMP. Emp_salary=emp. emp_salary*1.2 where EMP. emp_id in (select

Emp. emp_id from emp,dept where EMP. Emp_id=dept. emp_id and Dept. Dept_id= '?? Ê?2? ');
End Add_salary;
Requirement 3:
Create a journal to track changes in salary, that is to say, if you make a change to a worker's salary, you should

The corresponding change records are all written down. If you create a trigger on the salary field of the EMP table to monitor changes to the salary, place each

Changes to record, so that the purpose of the request 3.
Create or Replace Trigger Print_salary_change
Before delete or insert or update on emp--trigger event
For each row--you need to call this procedure for every modified row
Declare--only the declaration of a trigger needs to be declare, and neither procedure nor function requires
Salary_balance number;
Begin
--:new and: Old represents the record of the line before and after modification
Salary_balance=:new.salary=:old.salary;
Dbms_output. Put_Line (' Old salary are: ' | |: old.salary);
Dbms_output. Put_Line (' Old salary are: ' | |: new.salary);
Dbms_output. Put_Line (' Old salary are: ' | | to_char (salary_balance));
End Print_salary_change;
Requirement 4:
Compared with other languages (C + +, etc.), Pl/sql test has its own differences, summed up there are three ways:
1, using the Dbms_output package Put_Line method to display the intermediate variables, in order to observe whether the program has logic errors.
2, insert the test table method. That is, create a temporary intermediate table, and then take the results of all the intermediate variables involved as records

Inserted into the middle table, so that you can query the results in the table to see how the program is performing.
3. Use the exception handling method to use the begin ... end of the suspicious program segment, and then you can catch the exception in the exception

Processing.
Here we are going to use the second method to build a test package, and the concept of pl/sql is similar to the concept of class in object-oriented, package

Encapsulating a set of actions and attributes together not only enhances the modularity of the program, but also increases the number of operations and attributes that are encapsulated

Execution effectiveness. It takes two steps to build a pl/sql: First, you have to build a header, similar to building a class headers, which are mainly

The procedure in the package, the declaration of functions and variables, the second part is mainly the package body part, implements the procedure and the function which the preceding declaration, moreover also needs

Initialization of the package, and so on.
Based on this idea, set up the test package as follows:
/* Baotou Part * *
Create or replace package debug as
Procedure Debug (v_description varchar2, v_valueofvariable varchar2)
procedure Reset;
V_numberofline number;
End Debug;
/* Package Parts * *
Create or Replace package body debug AS
Procedure Debug (v_description varchar2, v_valueofvariable varchar2) is
Begin
INSERT INTO debugtable
VALUES (v_numberofline,v_description, v_valueofvariable);
v_numberofline:=v_numberofline+1;
End Debug;
Procedure Reset is
Begin
V_numberofline:=1;
Delete from debugtable;
End Reset;
/* Initialize PART * *
Begin
Reset
End Debug;