PROCEDURES

A procedure is a module performing one or more actions; it does not

need to return any values. The syntax for creating a procedure is as fol-lows:

CREATE OR REPLACE PROCEDURE name

[(parameter[, parameter, ...])]

AS

[local declarations]

BEGIN

executable statements

[EXCEPTION

exception handlers]

END [name];

A procedure may have 0 to many parameters. This will be covered in the

next lab. Every procedure has two parts: (1) the header portion, which

comes before AS (sometimes you will see IS—they are interchangeable),

keyword (this contains the procedure name and the parameter list),

(2) the bodys, which is everything after the IS keyword. The word

REPLACE is optional. When the word REPLACE is not used in the header

of the procedure, in order to change the code in the procedure, it must be

dropped first and then re-created. Since it is very common to change the

code of the procedure, especially, when it is under the development, it is

strongly recommended to use the OR REPLACE option.

There are two main views in the data dictionary that provide information on

stored code. They are the USER_OBJECTS view, to give information about the

objects, and the USER_SOURCE, to give the text of the source code. Remem-ber,

the data dictionary also has an ALL_ and DBA_ version of these views.

If you receive an error, then type the command:

Show error

You can also add to the command:

L start_line_number end_line_number

to see a portion of the code in order to isolate errors

PARAMETERS

Parameters are the means to pass values to and from the calling environ-ment

to the server. These are the values that will be processed or returned

via the execution of the procedure. There are three types of parameters:

IN, OUT, and IN OUT.

MODES

Modes specify whether the parameter passed is read in or a receptacle for

what comes out.

Mode Description Usage

IN Passes a value into the program Read only value

Constants, literals, expressions

Cannot be changed within program

Default mode

OUT Passes a value back from the Write only value

program Cannot assign default values

Has to be a variable

Value assigned only if the program

is successful

IN OUT Passes values in and also send Has to be a variable

values back Value will be read and then written

EXCUTE FIND_NAME (127, NAME)

PROCEDURE FIND_NAME ( ID IN NUMBER, NAME OUT VARCHAR2)

Formal parameters do not require constraints in datatype—for example,

instead of specifying a constraint such as VARCHAR2(60), you just say

VARCHAR2 against the parameter name in the formal parameter list. The

constraint is passed with the value when a call is made.

MATCHING ACTUAL AND FORMAL PARAMETERS

Two methods can be used to match actual and formal parameters: posi-tional

notation and named notation. Positional notation is simply associa-tion

by position: The order of the parameters used when executing the

procedure matches the order in the procedure’s header exactly.

EXAMPLE Create and then call it.

Red – PL/SQL code; Green – SQL code. This gives you a view how the two languages mix.

--procedure.sql

CREATE OR REPLACE PROCEDURE find_name

(i_cust_no IN NUMBER,

o_cust_name OUT VARCHAR2)

AS

BEGIN

SELECT cust_name

INTO o_cust_name

FROM customers

WHERE cust_no = i_cust_no;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Error in finding :i_cust_no '||i_cust_no);

END find_name;

--now run it below

DECLARE

v_local_cust_name customers.cust_name%TYPE;

BEGIN

find_name (701, v_local_cust_name); --calling procedure

DBMS_OUTPUT.PUT_LINE

('Customer 701 is: '||v_local_cust_name||'.');

END;

Functions

A function that is stored in the database is much like a procedure in

that it is a named PL/SQL block that can take parameters and be in-voked.

There are key differences both in the way it is created and how it

is used. In this chapter, you will cover the basics of how to create, make

use of, and drop a function.

FUNCTION BASICS

Functions are another type of stored code and are very similar to proce-dures.

The significant difference is that a function is a PL/SQL block that

returns a single value. Functions can accept one, many, or no parameters,

but a function must have a return clause in the executable section of the

function. The datatype of the return value must be declared in the header

of the function. A function is not a stand-alone executable in the way

that a procedure is: It must be used in some context. You can think of it

as a sentence fragment. A function has output that needs to be assigned

to a variable, or it can be used in a SELECT statement.

FUNCTION SYNTAX

The syntax for creating a function is as follows:

CREATE [OR REPLACE] FUNCTION function_name

(parameter list)

RETURN datatype

IS

BEGIN

<body>

RETURN (return_value);

END;

The function does not necessarily have any parameters, but it must have

a RETURN value declared in the header, and it must return values for all

the varying possible execution streams. The RETURN statement does not

have to appear as the last line of the main execution section, and there

may be more than one RETURN statement (there should be a RETURN

statement for each exception). A function may have IN, OUT, or IN OUT

parameters, but you rarely see anything except IN parameters since it is

bad programming practice to do otherwise.

CREATE OR REPLACE FUNCTION show_description

(i_model_name varchar2)

RETURN varchar2

AS

v_description varchar2(50);

BEGIN

SELECT description

INTO v_description

FROM models

WHERE model_name = i_model_name;

RETURN v_description;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

RETURN('The Model is not in the database');

WHEN OTHERS

THEN

RETURN('Error in running show_description');

END;

--CALL THE FUNCTION IN CONTEXT

DECLARE

v_description VARCHAR2(50);

BEGIN

v_description := show_description('&sv_model');

DBMS_OUTPUT.PUT_LINE(v_description);

END;

--CAL FUNCTION IN A SELECT

DECLARE

v_description VARCHAR2(50);

begin

SELECT show_description('ASTON V8')

INTO v_description

FROM models

WHERE MODEL_NAME = 'ASTON V8';

DBMS_OUTPUT.PUT_LINE(v_description);

END;