One. Case introduction
There are two tables for a database, which is about a company employee data, salary and department information. They are the EMP tables and DEPT tables, and the structure of the two tables is as follows:
Requirements are as follows:
1. Establish a corresponding table according to the above table structure and write 5 combination method per table.
2, manipulate the related table, so that the salary of the employee of the Technical Department has risen by 20%.
3. Establish a log to track the salary changes.
4, establish a test package.
two. Analysis and Implementation of Cases
It is not difficult to see from the introduction of the previous case, requiring 1 inspection point as the basic SQL statement; Requirements 2 Mainly to investigate composite queries; Requirements 3 is to investigate the application of triggers; Requirements 4's inspection planes, not only examine the creation of the package, not only Moreover, the test method in PL / SQL is also investigated. Understand the knowledge points of these inspections, you can solve one by one.
Requirement 1:
First, two tables can be created according to the structure of the previous table:
- Creating an employee table
Create Table EMP (EMP_ID NUMBER (5), EMP_NAME VARCHAR2 (20), EMP_SALY NUMBER (4));
- Department table
CREATE TABLE DEPT (DEPT_ID NUMBER (3), DEPT_NAME VARCHAR2 (20), EMP_ID NUMBER (5));
After establishing a table, you can write data in the table, where the code to add a table record is written to the corresponding stored procedure.
/ * Add recorded stored procedures to EMP tables * /
Create or report (p_emp_id number, p_emp_name varcha2, p_emp_salary number) AS
v_emp_id number: = p_emp_id;
V_EMP_NAME VARCHAR2 (20): = 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;
/ * Add recorded stored procedures to the DEPT table * /
CREATE or REPLACEDURE INS_TABLE_DEPT (p_dept_id number, p_dept_name var) AS
v_dept_id number: = p_dept_id;
v_dept_name varchar2 (20): = 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 added * /
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 (111, 'DD? T2?', 10000);
INS_TABLE_DEPT (111, 'DD? T2?', 10001);
INS_TABLE_DEPT (111, 'DD? T2?', 10002);
INS_TABLE_DEPT (112, '?? ê? 2?', 10003);
INS_TABLE_DEPT (113, 'êD3? 2?', 10004);
Require 2:
For the employee of the designated department, this is actually a composite query, first of all, you need to select the staff of all sectors, and then modify the salary of these employees. According to this idea, the code is as follows:
(It should be noted that: the department to be salary as a parameter, such a stored procedure is more flexible.)
Create or Replace Procedure Add_salary (p_dept_name varcha2) AS
v_dept_name varchar2 (20): = p_DEPT_NAME;
Begin
Update Emp Set Emp.emp_salary = Emp.emp_salary * 1.2 WHERE Emp.emp_id in (SELECT EMP.EMP_ID FROMP, DEPT WHERE EMP.EMP_ID = Dept.emp_id and dept.dept_id = '??? 2?';
End add_salary;
Requirement 3:
Establishing a log to form a tracking of the salaries, that is, if it is changed to a salary of a staff, he should record all its corresponding change records. If you create a trigger for the Salary field of the EMP table to monitor changes to Salry, record each change, so that the purpose of request 3 is achieved.
Create Or Replace Trigger Prug_salary_change
Before delete or insert or update on EMP - triggering events
For each row - This process needs to be called every modification
Declare - Only the trigger declaration requires Declare, procedures, and functions
SALARY_BALANCE NUMBER;
Begin
-: New and: OLD represents the row before modifying and modified records
SALARY_BALANCE =: new.salary =: Old.salary;
DBMS_OUTPUT.PUT_LINE ('Old Salary IS:' ||: Old.salary);
DBMS_OUTPUT.PUT_LINE ('Old Salary IS:' ||: New.SAlary;
DBMS_OUTPUT.PUT_LINE ('Old Salary IS:' || to_CHAR (SALARY_BALANCE));
End Print_salary_change;
Requirement 4:
Compared to other languages (C / C , etc.), the PL / SQL test has its differences, and there are three ways:
1. Use the PUT_LINE method of the DBMS_OUTPUT package to display the intermediate variable to observe if the program has a logic error.
2, the method of inserting the test table. That is, a temporary intermediate table is created, and then all the results of the intermediate variables involved as records into the intermediate table, which can look at the results in the table to observe the execution of the program.
3. Use an exception handle to use the begin ... END to the suspicious block, and then you can perform an exception capture process in Exception.
Here, you are ready to use a second method to create a test package, the concept of PL / SQL packets is similar to the concept of object-oriented classes, and package a set of operations and attributes together, not only enhanced the modularity of the program, but also The performance performance is improved due to more operation and attributes encapsulated. Build a PL / SQL requires two steps: First, you want to build a header, similar to the establishment of a class's header file, a statement of the process, functions, and variables in the package; the second part is mainly an enabling part, implementing the previous declaration The process and function, and the package is also required to initialize the package. According to this idea, the establishment of the test package is as follows:
/ * Baotou section * /
Create Or Replace Package Debug As
Procedure debug (v_description varchar2, v_valueofvariable varchar2)
PROCEDURE RESET;
v_numberofline number;
End Debug;
/ * Body part * /
Create or Replace Package Body Debug As
Procedure debug (v_description varchar2, v_valuofvariable 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;
/ * Initialization part * /
Begin
RESET;
End Debug;
three. summary
The answer to 4 questions in front of the integration, basically combined with the main part of PL / SQL, although many places just involved more shallow levels, but with this foundation, it is not difficult.
In short, PL / SQL programming has a certain difference from other language programming. The reader only has the ability to grasp the development of database development only.
