Procedure in SQL

Syntax
CREATE [OR REPLACE] PROCEDURE Your_Procedure_Name
[(parameter1_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS} 
BEGIN 
   Procedure_Body_Area
END;
Example
CREATE OR REPLACE PROCEDURE welcomeMessage
AS 
BEGIN 
   dbms_output.put_line('Hello ! welcome to SQL World!');

END;
How to Execute
Execute welcomeMessage
Hello ! welcome to SQL World!
or
BEGIN
    welcomeMessage
END

How to Drop procedure
DROP PROCEDURE welcomeMessage

Points

• Procedure can call the functions.
• Function cannot call procedure.
• Procedure may or may not return values.
• Try-Catch can be used in procedure.
• Transaction can be used in procedure.
• Procedure can take both input as well as output parameters.
• Procedure can use SELECT statements as well as DML statements also.
• Procedure is used to take certain actions not only for calculation purpose.