As we discussed in the last section, compared with the Statement interface, the advantage of using a PreparedStatement interface is that it can perform a dynamic query with known or unknown dynamic parameters as inputs. Most of the time, those dynamic parameters are input parameters and can be defined as IN variables. However, you do not need to specify those param-eters with an IN keyword when using a PreparedStatement interface.
The difference between the PreparedStatement and CallableStatement interfaces is:
unlike the PreparedStatement interface, the CallableStatement interface has both input and output-
parameters, which are indicated with IN and OUT keywords, respectively. In order to set up values for input parameters or get values for the output parameters, you have to use either a setXXX() method or a getXXX() method. However, the point is that before you can use any getXXX() method to pick up the values of output parameters, you must first register the output parameters to allow the CallableStatement interface to recognize them.
Just as we did in Section 6.3.7.5.9, open the Oracle SQL Developer and perform the following operations to create this package:
1) In the opened Oracle SQL Developer, click on the XE service under the Recent tab.
2) In the popup wizard, enter our password, oracle _ 18c, into the Password box, and click on the OK button to connect to our service and sample database.
3) Then expand the two folders XE and Other Users and our sample database folder,
CSE _ DEPT.
4) Right-click on the Packages folder and select the New Package item.
5) In the opened Create Package wizard, enter the package name, FACULTYCOURSE, into the Name box, as shown in Figure 6.41. Click on the OK button.
Each package has two parts: the definition or specification part and the body part. First let’s cre-ate the specification part by entering the code shown in Figure 6.42.
The prototype of the procedure SelectFacultyCourse() is declared in line 3. Two argu-ments are used for this procedure. The input parameter facultyName is indicated as an input by using the keyword IN, followed by the data type of VARCHAR2, which is equivalent to a String in Java. The output parameter is a cursor named Faculty _ Course followed by a keyword, OUT. Each PL/SQL statement must end with a semicolon, and this rule also applies to the END statement.
FIGURE 6.42 The definition code for the Oracle package FacultyCourse.
FIGURE 6.43 The code for the body part of the Oracle package FacultyCourse.