6.3.7.5.7 The Syntax of Creating Stored Procedures in the Oracle Database
The syntax of creating a stored procedure in the Oracle database is shown in Figure 6.25.
The keyword REPLACE is used for the modified stored procedures. Recall that in SQL Server, the keyword ALTER is used for any stored procedure that has been modified since it was created. In Oracle, the keyword CREATE OR REPLACE is used to represent any procedure that is either newly created or modified.
Following the procedure’s name, all input or output parameters are declared inside the braces.
After the keyword AS, the stored procedure’s body is displayed. The body begins with the keyword BEGIN and ends with the keyword END. Note that a semicolon must follow each query statement and the keyword END.
An example of creating a stored procedure in an Oracle database is shown in Figure 6.26.
The length of data type for each parameter is not necessary, since this allows parameters of vary-ing length.
6.3.7.5.8 The Syntax of Creating Packages in the Oracle Database
To create a stored procedure that returns data, one needs to embed the stored procedure into a pack-age. The syntax of creating a package is shown in Figure 6.27.
The syntax of creating a package contains two parts: the package definition part and the package body part. The returned data type, CURSOR, is defined first, since the cursor can be used to return a group of data. Following the definition of the cursor, the stored procedure, or, more precisely, the protocol of the stored procedure, is declared with the input and output parameters (the cursor works as the output argument). Following the package definition part is the body part. The protocol of the stored procedure is re-declared at the beginning, and then the body begins with the opening of the cursor and assigns
FIGURE 6.25 The syntax of creating a stored procedure in the Oracle database.
FIGURE 6.26 An example of creating a stored procedure in the Oracle database.
FIGURE 6.27 The syntax of creating a package in the Oracle database.
the returned result of the following SELECT statement to the cursor. Similarly, each statement must end with a semicolon, including the END command.
An example of creating a FacultyPackage in the Oracle database is shown in Figure 6.28. The stored procedure is named SelectFacultyID and has two parameters: the input parame-ter FacultyName and the output parameter FacultyID. The keywords IN and OUT following the associated parameters are used to indicate the input/output direction of the parameter. The length of the stored procedure name is limited to 30 letters in Oracle. Unlike stored procedure names created in SQL Server, there is no prefix applied for each procedure’s name.
Unlike Server Explorer provided by Visual Studio. NET, Oracle Database 18c XE does not pro-vide a valid GUI to assist users to build customized databases, including stored procedures and packages. Fortunately, one of the powerful tools used to support various developments for Oracle databases, Oracle SQL Developer, provides all kinds of GUIs and components to enable users to build those elements in a very friendly environment.
In this section, we will use Oracle SQL Developer as a tool to build our Oracle packages and stored procedures, since this tool provides a GUI to allow users to create and manipulate database components, including packages and stored procedures, directly in the Developer environment.