Module 09: Creating Stored Procedures and Functions

Creating Stored Procedures and Functions

01. What is Procedure and Functions? 

Procedures and functions are named PL/SQL blocks. They are also known as subprograms. These subprograms are compiled and stored in the database. The block structure of the subprograms is similar to the structure of anonymous blocks. Subprograms can be declared not only at the schema level but also within any other PL/SQL block. We typically use a procedure to perform an action and a function to compute and return value. subprogram contains the following sections: 1.Declarative section 2.Executable section 3.Exception section.

02. Write down Differences Between Anonymous Blocks and Subprogram?

Differences Between Anonymous Blocks and Subprograms:

03. Write down the syntax of Procedure and Functions?

Procedure  Syntax:

CREATE [OR REPLACE] PROCEDURE procedure_name

[(argument1 [mode1] datatype1,

argument2 [mode2] datatype2,. . .)]

IS|AS

procedure_body;

Function  Syntax:

CREATE [OR REPLACE] FUNCTION function_name

[(argument1 [mode1] datatype1,

argument2 [mode2] datatype2,. . .)]

RETURN datatype

IS|AS

function_body;