Procedure – Function

Procedure	Function
Procedure (Stored Procedure) is a named PL/SQL Block which performs one or more specific task. The General Syntax to create a procedure is: CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters] IS   Declaration section BEGIN   Execution section   EXCEPTION     Exception section END; Procedure can be executed in two ways: 1. From the SQL prompt. EXECUTE [or EXEC] procedure_name; 2. Within another procedure – simply use the procedure name. procedure_name;	Function is a named PL/SQL Block which is similar to a procedure, but the difference is function must always return a value. The General Syntax to create a function is: CREATE [OR REPLACE] FUNCTION function_name [parameters] RETURN return_datatype; IS|AS   Declaration_section BEGIN   Execution_section   Return return_variable;   EXCEPTION     exception section     Return return_variable; END; Function can be executed in the following ways: 1. We can assign it to a variable: employee_name := employer_details_func; 2. As a part of a SELECT statement: SELECT employer_details_func FROM dual; 3. In a PL/SQL Statements: dbms_output.put_line(employer_details_func);
There are three types of parameter to pass to procedure and function: 1. IN parameter. By default, if it is not explicitly defined, then parameter is IN type parameter. This type of parameter is a read only parameter. The General syntax to pass IN parameter is: CREATE [OR REPLACE] PROCEDURE procedure_name (param_name1 IN datatype, param_name12 IN datatype ... )); 2. OUT parameter. This is a write-only parameter. The General syntax to create an OUT parameter is: CREATE [OR REPLACE] PROCEDURE procedure_name (param_name OUT datatype) 3. IN OUT parameter. The IN OUT parameter allows us to pass values into a procedure and get output values from the procedure. The General syntax to create an IN OUT parameter is: CREATE [OR REPLACE] PROCEDURE procedure_name (param_name IN OUT datatype)

 

References :

• http://www.plsql-tutorial.com/
• http://en.wikipedia.org/wiki/PL/SQL
• PL SQL Programming