Oracle PL/SQL entry case practices

The ORACLE tutorial is Oracle PL/SQL getting started. I have already learned the basics of PL/SQL programming. This article will use a case study to deepen my understanding of these knowledge points.

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

-- Department table

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.

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 .)

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.

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:

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.