PERFORM DATA MANIPULATION USING CALLABLE STATEMENTS 3 – Insert, Update and Delete Data from Databases

A. The top seven items are the input parameters we added into this procedure.
B. A local variable, facultyID, is declared, and we need to use it as an intermediate vari-able to hold the query result from the first query from the Faculty Table.
C. The first query is executed to get the faculty_id based on the input FACULTYNAME.
D. The second query, INSERT INTO, is created with seven nominal input data columns fol-lowed by associated seven input parameters.

Your completed stored procedure is shown in Figure 7.28.
Now expand the Compile icon and click on the Compile item to compile our procedure. A Compiled statement should be displayed in the Message window if everything is fine. Also, you can find our procedure, INSERTNEWCOURSE, is just under the Procedures folder in the left pane (Figure 7.28). You may need to refresh that folder by right-clicking on the folder and selecting the Refresh item in the popup menu if you cannot find our procedure.

We can directly test the stored procedure in the Oracle SQL Developer environment to confirm its performance. To do this test, just click on the green arrow button shown in Figure 7.28 to run this procedure.

When the Run PL/SQL wizard appears, as shown in Figure 7.29, enter the following input parameters as a new course record for faculty member Jenney King into the Input Value col-umn, as shown in Figure 7.29:

Course_ID: CSE-668
Course: Neural Network
Credit: 4
Classroom: TC-303
Schedule: T-H: 2:00–4:00 PM
Enroll: 26
FacultyName: Jenney King

To check this run result, one can open the COURSE Table in the Oracle SQL Developer environ-ment to check the insertion. To do this, expand the Tables folder just under our user database, CSE _ DEPT; right-click on our COURSE Table; and select Open item. In the opened COURSE Table, click on the Data tab on the top to open the data view for all courses. Scroll down along the

FIGURE 7.29  The run status of the procedure INSERTNEWCOURSE() (Copyrighted by Oracle and used with permission).

Table, and you will find that our new inserted course, CSE-668, with all related fields, has been inserted into this Table, as shown in the highlighted line in Figure 7.30.

It is highly recommended to remove this new inserted course from the Course Table, since we may need to perform this insert action again later when we test this function in the NetBeans environment.

Now close the Oracle SQL Developer, and we can continue to develop the code for the CallableStatement method in our project to call this stored procedure to perform a new course insertion action against our sample database.