Update Data to the Course Table Using Callable Statements – Insert, Update and Delete Data from Databases

7.6.2   Update Data to the Course Table Using Callable Statements

Copy the project OracleCallableInsert, change its name to OracleCallableUpdate and save it to the folder Class DB Projects\Chapter 7 on your computer. We will build the data update function with the CallableStatement method in the following procedures:

FIGURE 7.33   The new inserted course, CSE-549.

1) Build our stored procedure, UpdateCourse, using the Oracle SQL Developer.
2) Develop the code for the Update button in the CourseFrame Form window to execute the CallableStatement method to call our stored procedure UpdateCourse to update a course record in the Course Table in our sample database.

3) Confirm and validate this course update action using the code we built for the Select button event handler.

Now let’s start from the first step.

7.6.2.1  Develop the Oracle Stored Procedure UpdateCourse()
Generally, we do not need to update a course _ id when we update a course record in the Course Table, since a better way to do that is to insert a new course record and delete the old one. The main reason for this is that a very complicated cascade operation would be performed if the course _ id were updated, since it is a primary key in the Course Table and foreign key in the StudentCourse Table. To update a primary key, one needs to update the foreign key first in the child Tables and then update the primary key in the parent Table. This will make our update

operation much more complicated and potentially confusing. In order to avoid this confusion, in this section, we will update a course record by changing any column other than course _ id, and this is a popular way to update a Table and is widely implemented in most database applications.

Perform the following steps to build the Oracle stored procedure DeleteCourse with Oracle SQL Developer:

1) Open Oracle SQL Developer and click on the system database, XE, under Recent to connect to the Oracle database by entering the system password, oracle _ 18c. Then expand the Other Users folder and our sample database folder, CSE _ DEPT.

2) Right-click on the Procedures folder and select the New Procedure item.
3) In the opened Create Procedure wizard, enter UPDATECOURSE in the Name: box as the procedure’s name.

4) Click on the green plus symbol (+) in the upper-right corner to add all seven input param-eters one by one with the data type shown in Figure 7.34.

5) Click on the OK button to open the procedure code window.

FIGURE 7.34   The finished Create Procedure wizard (Copyrighted by Oracle and used with permission).

FIGURE 7.35   The finished code body for the procedure.