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

7.6   PERFORM DATA MANIPULATION USING CALLABLE STATEMENTS

In Section6.3.7.5 in Chapter 6, we provided a very detailed discussion of the data query from the Faculty Table in our sample database using the CallableStatement method. Some basic and fundamental ideas and techniques using the CallableStatement method and stored proce-dures were given in detail, with some real sample projects. Refer to that section to get a clear picture and understanding of the CallableStatement object. In this section, we will use this method to perform data manipulations against the Course Table in our sample database, CSE _ DEPT.

First let’s take care of the data insertion to the Course Table in our sample Oracle 18c XE data-base using the CallableStatement method.

7.6.1   Insert Data to the Course Table Using Callable Statements

In Section 6.3.8 in Chapter 6, we built a project, OracleSelectCourse, with a graphical user interface, including the CourseFrame Form window, and we want to use that CourseFrame Form window from that project with some modifications in our new project. You can find that project in the folder Class DB Projects\Chapter 6 in the Students folder at the CRC Press ftp site. Copy that project and change its name to OracleCallableInsert and save it to the folder Class DB Projects\Chapter 7 on your computer. We will build the data insertion function with the CallableStatement method in the following procedures:

1) Build our stored procedure, InsertNewCourse, using the Oracle SQL Developer.
2) Develop the code for the Insert button in the CourseFrame Form window to execute the CallableStatement method to call our stored procedure InsertNewCourse to insert the new course record into the Course Table in our sample database.

3) Confirm and validate the new course insertion using the code we built for the Select button event handler.

Now let’s start from the first step.

7.6.1.1  Develop the Oracle Stored Procedure InsertNewCourse()
Recall that when we built our sample database, CSE _ DEPT, in Chapter 2, there was no fac-ulty name column in the Course Table, and the only relationship between the Faculty and the Course Tables was the faculty _ id, which is a primary key in the Faculty Table but a foreign key in the Course Table. As the project runs, the user needs to insert new course records based on the faculty name, not the faculty ID. Therefore, for this new course data insertion, we need to perform two queries with two Tables: first we need to make a query to the Faculty Table to get the desired faculty _ id based on the faculty name selected by the user, and second we can insert a new course record based on the faculty _ id we obtained from our first query into the Course Table. These two queries can be combined into a single stored procedure.