7.6.3 Delete Data from the Course Table Using Callable Statements
Copy the project OracleCallableUpdate, change its name to OracleCallable Delete and save it to the folder Class DB Projects\Chapter 7 on your computer. We will build the data delete function with the CallableStatement method in the following procedures:
FIGURE 7.40 The codes for the stored procedure DeleteCourse() (Copyrighted by Oracle and used with permission).
1) Build our stored procedure, DeleteCourse, using the Oracle SQL Developer.
2) Develop the code for the Delete button in the CourseFrame Form window to execute the CallableStatement method to call our stored procedure, DeleteCourse, to delete a course record from the Course Table in our sample database.
3) Confirm and validate this course delete action using the code we built for the Select button event handler.
Now let’s start from the first step.
7.6.3.1 Develop the Stored Procedure DeleteCourse()
Perform the following steps to build the Oracle stored procedure DeleteCourse with Oracle SQL Developer:
1) Open the 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 DELETECOURSE into the Name: box as the procedure’s name.
4) Click on the green + icon to add only one input parameter, CID, which is equivalent to an input (IN) parameter course _ id with the VARCHAR2 data type, and click on the OK button to continue.
5) In the opened code wizard, enter the code shown in Figure 7.40 into the code window as the body of this procedure.
6) Click on the Compile icon (Figure 7.40) to compile this procedure. A Compiled state-ment should be displayed in the Message window if everything is fine.
Your finished stored procedure should match the one shown in Figure 7.40. Let’s have a closer look at this piece of code to see how it works.
A. The only input to this stored procedure is the course _ id that is a primary key to the Course Table. Here we use CID as a dynamic parameter for this stored procedure.
B. The DELETE statement is created with CID as this deleting criterion.
FIGURE 7.41 The run result of the stored procedure DeleteCourse() (Copyrighted by Oracle and used with permission).
To run and test the stored procedure, click on the green arrow button on the top to open the Run PL/SQL wizard. Enter CSE-549 into the associated Value column in this wizard and click on the OK button to run this procedure. The run result is shown in Figure 7.41.
To confirm the data delete action, you can open the COURSE Table in this Oracle SQL Developer to check it. Go to the COURSE Table located under the Tables folder in our sample database, CSE _ DEPT, in Developer, expand the Tables folder and click on the COURSE Table to open it. You may need to refresh the Table by clicking on the Refresh icon in the upper-left corner. Then you can see that course CSE-549 has been deleted from this Table.
It is highly recommended to recover this deleted course record to its original values, since we need to call the CallableStatement object to run this stored procedure again when we test our project later. You can do this inside the Oracle SQL Developer by opening the Course Table and inserting this course record at the bottom line of that Table. When it is done, right-click on the new inserted data line and select the Commit Changes item from the popup menu to make this insertion effective.
Refer to Table 7.7 to get more details to recover this deleted course record.
Before you can close the Oracle SQL Developer, go to File > Save All to save there covered job and any other modifications to the Table.
Next we need to build code for the Delete button click event handler in the CourseFrame form to call this stored procedure to perform the data delete action.
