One. Case introduction
A database has two tables, which is about a company employee information, salary and department information, which are EMP tables and DEPT tables, two tables
Configuration 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 examine composite query; Requirements 3 is to investigate triggers
The application; the test plane of request 4 is relatively, not only the creation of the package is not only examined, but also test methods in PL / SQL. Learned these
The knowledge point of the exam can be solved 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 procedure to EMP table * / create or report (p_emp_id number, p_emp_name varcha2, p_emp_salary
number) asv_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 procedure to the DEPT table * / create or replace procedure INS_TABLE_DEPT (p_dept_id number, p_dept_name varcha2, p_emp_id number)
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 * /
execute ins_table_emp (10000, 'a', 4000); execute ins_table_emp (10001, 'b', 2300); execute ins_table_emp (10002, 'c', 3500); execute ins_table_emp (10003, 'd', 3500); execute ins_table_emp (10004, 'e', 3500); Execute INS_TABLE_DEPT (111, 'Zhang 3r); EXECUTE INS_TABLE_DEPT (111,' Zhang 3 ', 10001); Execute INS_TABLE_DEPT (111,' Zhang 3 ', 10002); Execute INS_TABLE_DEPT (112, 'Zhao Sul', 10003); Execute INS_TABLE_DEPT (113,' Song 7 ', 10004); Requirements 2:
Give the employee of the designated department, this is actually a composite query, first of all, you need to select all the staff of the department, and then these
Employee's salary is carried out accordingly. 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 varchar2) asv_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 from
EMP, DEPT WHERE EMP.EMP_ID = dept.emp_id and dept.dept_name = v_dept_nam); End Add_salary;
Requirement 3: Create Table Salary (OLD_S Number (4), New_s Number (4) It should be corresponding
Change records are recorded. If you create a trigger for the SALARY field of the EMP table to monitor changes to Salary, make each change record
This is the purpose of requesting 3.
Create or Replace Trigger Prigte_salary_change before delete or ibrt or update on Emp - Trigger event for Each ROW - This process needs to be called each modified line
Declare - Only the trigger declaration requires the declare, the process, and the function does not need Salary_Balance Number; begin -: New: OLD represents the row before modifying and modified record SALARY_BALANCE: =: new.emp_salary-: Old .Emp_salary;
INSERT INTO SALY VALUES (: new.emp_salary, salary_balance); End Print_salary_change;
Execute add_salary ('Zhang 3'); you can see the change of the SALARY table. 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 to create a temporary intermediate table, then insert all the results of the intermediate variable involved as records into
In the intermediate table, this 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 is the second method to create a test package, the concept of PL / SQL packet is similar to the concept of the object-oriented class, the package will be set
The operation and attribute package are set together, which not only enhances the modularity of the program, but also improves the performance efficiency due to the encapsulation of more operations and attributes. build
It takes two steps to set up a PL / SQL: first build a header, similar to the establishment of a class's header file, mainly for the process, function and
Declaration of variables; the second part is mainly the block part, implement the process and functions of the previous declaration, and additional work is needed to initialize the package.
According to this idea, establish a test package as follows: Create Table DebugTable (D_Numberofline Number, D_Description Varchar2 (20), D_Valueofvariable
varchar2 (20)); / * header portion * / create or replace package debug as procedure debug (v_description varchar2, v_valueOfvariable varchar2) procedure reset; v_numberOfLine number; end debug; / * bag body portion * / 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; / * Initialization part * / begin reset; end debug;
