Chapter 09 Creating Procedures 01

What Are Procedures?

• Are a type of subprogram that perform an action
• Can be stored in the database as a schema object.
• Promote reusability and maintainability

Creating Procedures:Overview

Creating Procedures with the SQL CREATE OR REPLACE Statement

• Use the CREATE clause to create a stand-alone procedure that is stored in the Oracle database.
• Use the OR REPLACE option to overwrite an existing procedure.

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter 1 [mode] datatype1,
  parameter 2 [mode] datatype2,...)]
IS | AS
[local_variable_declareations,...]
BEGIN
        --actions
END [procedure_name];

Creating a Procedure And Invoking a Procedure

CREATE OR REPLACE PROCEDURE add_dept
IS
        v_dept_id       dept.department_id%TYPE;
        v_dept_name     dept.department_name%TYPE;
BEGIN
        v_dept_id := 280;
        v_dept_name := 'ST-Curriculum';

        INSERT INTO dept(department_id,department_name) VALUES(v_dept_id,v_dept_name);

        DBMS_OUTPUT.PUT_LINE(' Inserted ' || SQL%ROWCOUNT || 'row');
        COMMIT;
END;
SQL> EXEC add_dept;
Inserted 1row

PL/SQL procedure successfully completed.

The END Label

• You can append the name of the procedure directly after the END keyword when you complete your procedure.

PROCEDURE display_stroes(region IN VARCHAR2) IS 
BEGIN

...

END display_stores;

• This name serves as a label that explicitly links the end of the program with its begining.
• You should,as a matter of habit,use an END label.It is especially important to do so when you have a procedure that spans more than a single page,or is one in a series of procedures and functions in a packages body.

Naming Conventions of PL/SQL Structures Examples

PL/SQL Structure	Convention	Example
Variable	v_variable_name	v_rate
Constant	c_constant_name	c_rate
Subprogram parameter	p_parameter_name	p_id
Bind(host) Variable	b_bind_name	b_sarlary
Cursor	cur_currsor_name	cur_emp
Record	rec_record_name	rec_emp
Type	type_name_type	ename_table_type
Exception	e_exception_name	e_products_invalid
File handle	f_file_handle_name	f_file

What are parameters and parameter modes?

• Are declared after the subprogram name in the PL/SQL header
• Pass or communicate data between the calling environment and the subprogram.
• Are used like local variables but are dependent on their parameter-passing mode:
• -An IN parameter mode(default)  provides values for a subprogram to process
• -An OUT parameter mode returns a value to the caller
• -AN IN OUT parameter mode supplies an input value,which may be returned(output) as a modifyed value.

Formal and Actual Prameters

• Formal parameters:Local variables declared in the parameter list of a subprogram specifictiona
• Actual parameters(or arguments):Literal values,variables and expression used in the parameter list of the calling subprogram.

Procedure Parameter Modes

• Parameter modes are specified in the formal parameter declaration,after the parameter name and before its data type.
• The IN mode is the default if no mode is specified.

CREATE PROCEDURE proc_name(param_name [mode] datatype)

 Comparing the Parameter Modes

IN	OUT	IN OUT
Default mode	Must be specified	Must be specified
Value is paased into subprogram	Value is returned to the calling enviroment	Value passed into subprogram;value retuend to calling enviroment
Forma parameter acts as a constan	Uninitalized  variable	Initialized variable
Actual parameter can be a literal,exception,constan,or initialized variable	Must be a variable	Must be a variable
Can be assgined a default value	Can not be assigned a default value	Cannot be assigned a default value.