Oracle PL/SQL entry case practices)

I. Case Study

A database has two tables about employee information, salary, and department information of a company. They are EMP and dept tables. The structure of the two tables is as follows:

The requirements are as follows:

1. Create a corresponding table according to the structure of the table above, and write 5 combination data into each table.

2. The related table is manipulated to increase the salaries of employees in the "Technical Department" by 20%.

3. Create logs to track salary changes.

4. Create a test package.

Ii. Case Analysis and Implementation

From the introduction of the previous case, it is not difficult to see that 1 test point is the basic SQL statement; 2 test the composite query; 3 test the application of the trigger; and 4 test more, not only the creation of packages, but also the test methods in PL/SQL. After learning about these knowledge points, you can solve them one by one.

Requirement 1:

First, you can create two tables based on the structure of the preceding table:

-- Create employee table

Create Table EMP (emp_id number (5), emp_name varchar2 (20), emp_salary number (4 ));

-- Department table

Create Table dept (dept_id number (3), dept_name varchar2 (20), emp_id number (5 ));

After creating a table, you can write data to the table. Here, the code for adding table records is written to the corresponding stored procedure.

/* Stored procedure 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)

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;

/* Stored procedure 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)

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 to add */

Begin

Ins_table_emp (10000, '', 4000 );

Ins_table_emp (10001 ,'?? ? ', 2300 );

Ins_table_emp (10002, '3? T', 3500 );

Ins_table_emp (10003, <??? ', 3500 );

Ins_table_emp (10004, á? Why? ', 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 ,'?? Why? 2? ', 10003 );

Ins_table_dept (113, 'prop D3? 2? ', 10004 );

End;

Requirement 2:

To raise salaries for employees in a specified department, this is actually a compound query. First, we need to select all employees in the Department and then make corresponding changes to the salaries of these employees. The Code is as follows:

(Note that the department to be raised is used as a parameter. Such a stored procedure is more flexible .)

Create or replace procedure add_salary (p_dept_name varchar2)

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 = '?? Why? 2? ');

End add_salary;

Requirement 3:

The establishment log forms a tracking of salary changes. That is to say, if a employee's salary is changed, all corresponding changes should be recorded. If you create a trigger for the salary field of the EMP table to monitor changes to the salary and record each change, this achieves the goal of requirement 3.

Create or replace trigger print_salary_change

Before delete or insert or update on EMP -- trigger event

For each row -- this process must be called for each row modified

Declare -- only the declaration of the trigger requires declare, And neither process nor function is required.

Salary_balance number;

Begin

--: New and: Old indicate the row's records before and after modification.

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 with other languages (such as C/C ++), PL/SQL testing has its own differences. There are three methods to test PL/SQL:

1. Use the put_line method of the dbms_output package to display the intermediate variable to check whether the program has a logical error.

2. Insert a test table. Create a temporary intermediate table and insert the results of all the involved intermediate variables into the intermediate table as records. In this way, you can query the results of the table to observe program execution.

3. Use Exception Handling Methods and use BEGIN… for suspicious program segments... End, and then exception capture and processing can be performed in the exception.

Here we are going to use the second method to create a test package. The package concept in PL/SQL is similar to the class concept in object-oriented, and the package encapsulates a group of operations and attributes together, it not only enhances the modularization of the program, but also improves the execution efficiency by encapsulating more operations and attributes. Two steps are required to create a PL/SQL statement: First, you must create a header file, similar to creating a Class header file, which mainly declares the process, functions, and variables in the package; the second part is the package body, which implements the previously declared process and functions, and also needs to initialize the package.

Based on this idea, build the test package as follows:

/* Header part */

Create or replace package debug

Procedure debug (v_description varchar2, v_valueofvariable varchar2)

Procedure reset;

V_numberofline number;

End debug;

/* Package part */

Create or replace package body debug

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;

Iii. Summary

Based on the answers to the previous four questions, the main parts of PL/SQL are basically integrated. Although many of them only involve relatively superficial layers, with this foundation, it is not difficult to go deeper.

In short, PL/SQL programming is different from other programming languages. Only by grasping the characteristics of PL/SQL programming can readers better master the knowledge of database development.