Oracle PL and SQL Introductory case practice
- 2020-06-03 08:39:01
- OfStack
The ORACLE tutorial you are looking at is :Oracle PL/SQL Primer case practice. I have already known the basics of PL/SQL programming. This paper will combine one case to deepen the understanding of these knowledge points.
1. Case introduced
There are two tables in a database, which are about employee information, salary and department information of a company. They are emp and dept respectively. The structure of the two tables is as follows:
The requirements are as follows:
1. Set up corresponding tables according to the above table structure, and write 5 groups of legal data in each table.
2. Manipulated related tables to increase the salary of employees in "technical Department" by 20%.
3. Keep a journal to track salary changes.
4. Set up the test package.
2. Case analysis and implementation
From the introduction of the previous case, it is not difficult to see that the investigation point 1 is the basic SQL statement; Requirement 2 mainly investigates compound query; Requirement 3 is to examine the application of triggers; Requirement 4 has a relatively large number of investigations, which not only examine the creation of packages, but also the testing methods in PL/SQL. Understand the knowledge points of these investigations, you can solve 11.
Request 1:
First, two tables can be created according to the structure of the previous table:
-- Create a staff table
- department of table
Once you have created the table, you can write the data into it. Here, you write the code to add the table record to the corresponding stored procedure.
Requirement 2:
Pay increases for employees in a given department, which is actually a composite query, require all employees in that department to be selected first, and then the salaries of those employees to be changed accordingly. According to this idea, the code is as follows:
(Note: As a parameter, this stored procedure is more flexible.)
Requirements:
Keep a journal to keep track of salary changes. That is, if you make a change to an employee's salary, you should write down all the changes. If you create a trigger on the salary field of the emp table to monitor changes to salary and record each change, you achieve the purpose of Requirement 3.
Requires 4:
Compared with other languages (c/c++, etc.), the test of PL/SQL has its differences, which can be summarized into three methods:
1. Use the PUT_LINE method of the DBMS_OUTPUT package to display intermediate variables to see if there are logic errors in the program.
2. Methods for inserting test tables. You create a temporary intermediate table and then insert the results of all the intermediate variables involved into the intermediate table as records, querying the results of the table to see how the program is performing.
3. Use exception handling and begin for suspicious segments... end, and you can then do exception capture processing in exception.
The concept of packages in PL/SQL is similar to the concept of classes in object-oriented. Packages encapsulate a set of operations and attributes in one, which not only enhances the modularity of the program, but also improves execution efficiency by encapsulating more operations and attributes. There are two steps to setting up an PL/SQL. The first step is to set up a header, which is similar to setting up a header file for a class. Part 2 is mainly about the package body, implementing the previously declared procedures and functions, and initializing the package.
According to this idea, the test package is established as follows:
3. summary
Based on the answers to the four questions above, the main parts of PL/SQL are basically integrated. Although many areas are only involved in the relatively shallow level, it is not difficult to go further with this foundation.
In short, PL/SQL programming and other languages programming has a definite difference, only grasp its characteristics in order to better grasp the database development knowledge.
1. Case introduced
There are two tables in a database, which are about employee information, salary and department information of a company. They are emp and dept respectively. The structure of the two tables is as follows:
The requirements are as follows:
1. Set up corresponding tables according to the above table structure, and write 5 groups of legal data in each table.
2. Manipulated related tables to increase the salary of employees in "technical Department" by 20%.
3. Keep a journal to track salary changes.
4. Set up the test package.
2. Case analysis and implementation
From the introduction of the previous case, it is not difficult to see that the investigation point 1 is the basic SQL statement; Requirement 2 mainly investigates compound query; Requirement 3 is to examine the application of triggers; Requirement 4 has a relatively large number of investigations, which not only examine the creation of packages, but also the testing methods in PL/SQL. Understand the knowledge points of these investigations, you can solve 11.
Request 1:
First, two tables can be created according to the structure of the previous table:
-- Create a staff table
- department of table
Once you have created the table, you can write the data into it. Here, you write the code to add the table record to the corresponding stored procedure.
Requirement 2:
Pay increases for employees in a given department, which is actually a composite query, require all employees in that department to be selected first, and then the salaries of those employees to be changed accordingly. According to this idea, the code is as follows:
(Note: As a parameter, this stored procedure is more flexible.)
Requirements:
Keep a journal to keep track of salary changes. That is, if you make a change to an employee's salary, you should write down all the changes. If you create a trigger on the salary field of the emp table to monitor changes to salary and record each change, you achieve the purpose of Requirement 3.
Requires 4:
Compared with other languages (c/c++, etc.), the test of PL/SQL has its differences, which can be summarized into three methods:
1. Use the PUT_LINE method of the DBMS_OUTPUT package to display intermediate variables to see if there are logic errors in the program.
2. Methods for inserting test tables. You create a temporary intermediate table and then insert the results of all the intermediate variables involved into the intermediate table as records, querying the results of the table to see how the program is performing.
3. Use exception handling and begin for suspicious segments... end, and you can then do exception capture processing in exception.
The concept of packages in PL/SQL is similar to the concept of classes in object-oriented. Packages encapsulate a set of operations and attributes in one, which not only enhances the modularity of the program, but also improves execution efficiency by encapsulating more operations and attributes. There are two steps to setting up an PL/SQL. The first step is to set up a header, which is similar to setting up a header file for a class. Part 2 is mainly about the package body, implementing the previously declared procedures and functions, and initializing the package.
According to this idea, the test package is established as follows:
3. summary
Based on the answers to the four questions above, the main parts of PL/SQL are basically integrated. Although many areas are only involved in the relatively shallow level, it is not difficult to go further with this foundation.
In short, PL/SQL programming and other languages programming has a definite difference, only grasp its characteristics in order to better grasp the database development knowledge.