Chapter 15 How to Create Stored Procedures and Function PL/SQL
You can use the ____________ statement to call a procedure.
CALL
You use the ________ ________ statement to create a stored procedure.
CREATE PROCEDURE
It's a good programming practice to code your ____________ ____________ statements so that they list the required parameters ____________, followed by ____________ ____________.
CREATE PROCEDURE; first; optional parameters
You can code a stored procedure to execute _____________ statements such as the DROP TABLE statement.
DDL (The pic: A stored procedure that drops a table)
To declare an optional parameter, you use the ____________ keyword to assign it a default value.
DEFAULT
To delete a user-defined function from the database, use the _____________ _____________ statement.
DROP FUNCTION; (Pic: The function in this figure uses the get_balance_due function. As a result, if you drop the get_balance_due function, the function in this figure won't work)
To drop a stored procedure from the database, use the _____________ _____________ statement.
DROP PROCEDURE
To identify an input/output parameter, you must code the ____________ ____________ keywords.
IN OUT
____________ ____________ accept values that are passed from the calling program. These values cannot be changed by the body of the stored procedure.
Input parameters
____________ ____________ can store an initial value that's passed from the calling program. However, the body of the stored procedure can change this parameter.
Input/output parameters
To identify an output parameter, you must code the ____________ keyword. To use an output parameter, the calling program must declare a variable to store its value, and it must include this variable in the parameter list.
OUT
____________ ____________ are parameters that don't require that a value be passed from the calling program.
Optional parameters
____________ ____________ store values that are passed back to the calling program.
Output parameters
What is the syntax of the RAISE statement?
RAISE <exception_name>
The ____________ statement raises a predefined exception.
RAISE; (commonly used, predefined exceptions in pic)
To raise an exception you use the ____________ keyword followed by the ____________.
RAISE; exception_name
The _____________ procedure raises an application error with the specified error number and message. When you use this statement, the error number parameter must be between _____________ and _____________.
RAISE_APPLICATION_ERROR; -20000, -20999
You use the _____________ keyword to specify the value that's returned by the function.
RETURN
You can use the _____________ clause to catch predefined exceptions.
WHEN
You can use the _____________ _____________ _____________ clause to catch application errors.
WHEN OTHERS THEN
If the procedure called by the application program doesn't have a commit statement in it, then it is up to the _____________ _____________ to commit the changes done by the procedure.
application program
The _____________ of a package contains the code for its procedures and functions. Within the _____________, the names, parameters, and return types must match the names, parameters, and return types in the specification.
body; (Pic: example of body and DROP package body)
(SQL DEVELOPER): To set a _____________, display the procedure or function edit window. Then, click in the margin to the left of the line of code where you want to set the breakpoint. After that, a red dot will mark the breakpoint. (2)To begin debugging, click the Compile for Debug button in the Edit window. Then, click the Debug button to display the Debug PL/SQL dialog box. Here, you can use the assignment statements that are after the BEGIN keyword to set the input parameters. Use the Step Over, Step Into, Step Out to step through the code one statement at a time starting at a break point. The step Through buttons are located in the debugging window, to the right of the search field.
breakpoint; (Pic: (2) illustration)
Stored procedures are ____________ before they are stored in the database.
compiled
It's generally considered a good practice to validate the data within a stored procedure before using the data. This is referred to as ____________ ____________.
data validation
Raised errors are returned to the caller like errors that are raised by the _____________ _____________.
database engine
By default, parameters are defined as ____________ parameters. As a result, the ____________ keyword is optional for identifying input parameters.
input; IN
What is data validation? If the data isn't valid, you can execute code that makes it valid, or you can ____________ ____________ ____________, which returns an error to the calling program.
it is preventing errors by checking the parameters before they're used to make sure they're valid; raise an error
A function can't _____________ _____________ to the database such as executing an INSERT, UPDATE, or DELETE statement.
make changes; (Pic: a function that accepts a single parameter that specifies the ID for an invoice, and it returns the balance due for that invoice)
When you use a script to call a procedure, you can pass parameters by position or by ____________. When you pass parameters by name, you code the name of each parameter, followed by the association operator (____________), followed by the value. In this case, the parameters don't have to be in the same order as they appear in the CREATE PROCEDURE statement.
name; =>
To declare a parameter within a stored procedure, you code the ____________ of the parameter followed by its ____________ ____________. If you declare two or more parameters, you separate the parameters with ____________.
name; data type; commas
A _____________ is a container that allows you to organize and group related stored procedures and user-defined functions.
package
You can use ____________ to pass one or more values from the calling program to the stored procedure or from the procedure to the calling program.
parameters
When you use a CALL statement to call a procedure, you pass parameters by ____________. This means that you can list them in the same order as they appear in the CREATE PROCEDURE statement.
position
A _____________ _____________ returns a single value of any data type.
scalar-valued function
The _____________ of a package lists the names and parameters for its procedures and functions. In addition, it specifies the return types for its functions.
specification
A stored procedure can also be called a ________ or a ________.
sproc, procedure
A ________ ________ is an executable database object that contains a block of PL/SQL code.
stored procedure
A function can accept input parameters that work like the input parameters for a _____________ _____________.
stored procedure
A _____________ _____________ (UDF) is an executable database object that contains a block of PL/SQL code. A UDF is also called a _____________ _____________.
user-defined function; stored function
