Archive management system - school translation system. Case Description (Part of Example) There are two tables for students' information and achievements in colleges and universities. They are EMP tables and
The structure of the two tables, the structure of the two tables is as follows: (1) Student Basic Information Table Student (2) Serameters The grade requires: 1. Establish a corresponding table according to the above table structure, and write 5 combination method per table. 2, manipulating the related table, make the scholarship subsidy of "excellent performance (more than 90 points), rising 20%. 3. Establish a log and track the change of subsidies. 4, establish a test package. two. The analysis and implementation of the case 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 compound queries; requirements 3 is an investigation
The application of the trigger; the test surface of the request 4 is relatively, not only the creation of the package, but also examines the test method in PL / SQL.
. Understand the knowledge points of these inspections, you can solve one by one. Requirement 1: First, according to the structure of the previous table, two tables can be created: - Create a student basic information table CREATE TABLE EMP (EMP_ID NUMBER (5), EMP_NAME VARCHAR2 (20), EMP_SALARY NUMBER (4)); - Create a transcript CREATE TABLE DEPT (Dept_ID Number (3), de PT_NAME VARCHAR2 (20), EMP_ID NUMBER (5)); After establishing a table, you can write data in the table, where the code added to add a table is written to the corresponding stored procedure. . / * Add recorded stored procedure to EMP table * / create or report (p_emp_id number, p_emp_name
varchar2, 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; / * to the dept table add records Storage Procedure * / 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 (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 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 (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); END; Requirements 2: Give the designated department Employee salary, this is actually a composite query, first of all, the employee of all sectors is required, and then the salaries of these employees will be changed accordingly. According to this idea, the code is as follows: (Note that the department will 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 from Emp, Dept WHERE Emp.emp_id = dept.emp_id and dept.dept_id = '?? ê? 2?'); End Add_salary; Requirements 3: Building a log to form a tracking of the salary change, that is, If it is changed for a salary of a staff member, it should
The corresponding change records are all recorded. If you create a trigger for the SALARY field of the EMP table to monitor changes to Salary,
Supreme changes to record, so that the purpose of request 3 is achieved. Create or Replace Trigger Prigte or Insert or Update On EMP - Trigger Event For Each Row - This process Declare needs to call the Declare - only the trigger declaration requires the declare, the process, and functions, no salary_balance number; Begin -: New and: OLD represents the row before modifying and modified record SALARY_BALANCE =: new.salary =: Old.salary; dbms_output.put_line ('Old Salary IS:' ||; Old.Salary); DBMS_OUTPUT.PUT_LINE ('Old Salary IS:' ||: DBMS_OUTPUT.PUT_LINE ('Old Salary IS:' || To_Char (Salary_balance)); End Print_salary_change; Requirement 4: With other languages (C / C , etc. Compared to the PL / SQL tests, there are three methods: 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 handling means, use Begin ... End to the suspicious block, then you can perform an abnormal capture in Exception.
deal with. Here, you are ready to use the second method to create a test package, the concept of PL / SQL package is similar to the concept of the object oriented in the object.
Packing a set of operations and attributes, not only enhances the modularization of the program, but also improves more operations and attributes to encapsulate
Perform performance. Building a PL / SQL requires two steps: first to establish a header, similar to building a class of header files, mainly
The declaration of the process, functions, and variables in the package; the second part is mainly the block part, realizing the process and function of the previous declaration, and also need
Initialize the package and so on. According to this idea, the establishment of test pack as follows: / * 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;
