The Oracle tutorial you are looking at is the Oracle Pl/sql introductory case practice. We have already learned the basics of pl/sql programming, and this article will combine a case to deepen understanding of these knowledge points.
A Case Introduction
A database has two tables that are about a company's employee information, salary, and departmental information, each of which is an EMP table and a Dept table, and the two tables are structured as follows:
Requirements are as follows:
1, according to the above table structure to establish the corresponding table, and each table to write 5 sets of legitimate data.
2, the operation of related tables, so that the "technology department" staff's salary rose 20%.
3, the establishment of a journal to track salary changes.
4, set up test kits.
Two The analysis and realization of the case
From the introduction of the previous case, it is not difficult to see that the 1 point of Inquiry is the basic SQL statement, request 2 mainly investigate the compound query, the requirement 3 is to examine the application of the trigger, the requirement of 4 of the study surface is relatively more, not only to investigate the creation of the package, but also Understanding the knowledge of these visits, you can solve them all.
Requirement 1:
First, you can create two tables based on the structure of the previous table:
--Create Employee table
--Department table
Once you have established the table, you can write the data to the table, where you write the code that adds the table record to the appropriate stored procedure.
Requirement 2:
A salary increase for an employee in a given department is actually a composite query that first requires all employees in the department to be selected and then the salary of those employees will be changed accordingly. In accordance with this idea, the code is as follows:
(Note that the department that will pay a raise as a parameter is more flexible in such a stored procedure.) ）
Requirement 3:
Create a journal to track changes in salary, that is to say, if you change the salary of an employee, you should write down the corresponding change record. If you create a trigger on the salary field of the EMP table to monitor changes to the salary, log each change so that you can achieve the purpose of Requirement 3.
Requirement 4:
Compared with other languages (C + +, etc.), Pl/sql test has its own differences, summed up there are three ways:
1, using the Dbms_output package Put_Line method to display the intermediate variables, in order to observe whether the program has logic errors.
2, insert the test table method. That is, create a temporary intermediate table, and then insert the results of all the intermediate variables involved into the middle table as records, so that you can query the results of the table to see how the program is performing.
3, use the exception handling means, to suspect the program section use begin ... end, then can carry on exception capture processing in exception.
Here you are going to use the second method to build a test package, the concept of pl/sql is similar to that of object-oriented classes, where packages encapsulate a set of actions and attributes together, not only to enhance the modularity of the program, but also to improve execution performance by encapsulating more operations and attributes. It takes two steps to build a pl/sql: The first is to establish a header, similar to the creation of a class of headers, which mainly on the package of procedures, functions and variables of the Declaration; The second part is mainly the package body part, the implementation of the previous declaration of the process and functions, in addition to the package initialization and so on.
Based on this idea, set up the test package as follows:
Three Summary
Comprehensive before the answer to 4 questions, the basic pl/sql of the main part of the integration came in, although many places just involved relatively superficial level, but with this foundation, it is not difficult to go further.
In short, pl/sql programming and other language programming has a certain difference, the reader can only grasp its characteristics to better grasp the knowledge of database development.
