Click on the Compile button to compile this specification block. A successful compile message should be displayed in the Messages box at the bottom if it is completes successfully.
Next we need to create the body block for this package. Perform the following operations to cre-ate this body part:
1) Right-click on our new created package, FACULTYCOURSE, under the Packages folder in the left, and select the Create Body item to add a package body into this package.
2) In the opened Package Body wizard, enter the code that is highlighted in Figure 6.43.
3) Click on the drop-down arrow and select Compile to compile this package body. A Compiled message should be displayed in the Messages window if the compile is suc-cessful, as shown in Figure 6.44.
4) Your finished package, FACULTYCOURSE, is shown in Figure 6.44.
Two queries are included in this stored procedure; the first one is to query a faculty _ id from the Faculty Table based on the input faculty _ name, and the second one is to query all course _ id values from the Course Table based on the queried faculty _ id from the first query. As we know, there is no faculty _ name column available in the Course Table, and the only relationship between each course _ id and the associated faculty member is faculty _ id, which is a primary key in the Faculty Table but a foreign key in the Course Table. In order to get all course _ id values related to a faculty member, we have to perform two queries from two Tables. The advantage of using a stored procedure is that we can combine these two queries into a single procedure to speed up this process.
FIGURE 6.44 The completed Oracle FacultyCoursepackage (Copyrighted by Oracle and used with permission).
Let’s have a closer look at this piece of code to see how it works (Figure 6.43).
A. A local variable, facultyID, which will work as an intermediate variable to hold the returned faculty _ id from the first query, is declared with a data type of VARCHAR2(10).
B. Starting from BEGIN, our first data query statement is generated. It is used to get the faculty _ id from the Faculty Table based on the input parameter facultyName, which is the first argument of this procedure. An SELECT . . . INTO statement is uti-lized to temporarily store the returned faculty _ id into the local variable facultyID.
C. The OPEN Faculty _ Course FOR command is used to assign the returned data col-umns from the second query to the cursor variable Faculty _ Course. Starting from this line, the second query is declared, and it is to get all course _ id values taught by the selected faculty member from the Course Table based on the local variable’s value, facultyID, which is obtained from the first query previously. The queried results are assigned to the cursor variable Faculty _ Course.
Now let’s run our package by clicking the Run button (green arrow), as shown in Figure 6.44.
A Run PL/SQL wizard is displayed, as shown in Figure 6.45.
Enter the desired faculty name, such as Ying Bai, into the Input Value box, as shown in Figure 6.45, and click on the OK button to run this package. To check the run result, click on the Output Variables tab on the bottom. One can find that all courses taught by this faculty mem-ber are retrieved and displayed in the Output Variables—Log wizard, as shown in Figure 6.46.
The development of our Oracle package is complete, and now we can close Oracle SQL Developer and return to our Java application program to call this package to perform our course query to our Course Table.