Procedure & Functions in PL/SQL

Hi Everyone, Today's topic is Procedure & Functions

PROCEDURE:

The PL/SQL stored procedure or simply a procedure is a PL/SQL block which performs one or more specific tasks. It is just like procedures in other programming languages.

A PL/SQL procedure is a reusable unit that encapsulates specific business logic of the application.

Creating Procedure:

Syntax:

CREATE [OR REPLACE] PROCEDURE procedure_name  
    [ (parameter [,parameter]) ]  
IS 
    [declaration_section]  
BEGIN 
   executable_section  
[EXCEPTION  
  exception_section]  
END [procedure_name];

Example:

create or replace procedure "INSERTEMP"    
(id IN NUMBER,    
name IN VARCHAR2,
salary IN NUMBER)    
is    
begin    
insert into employee values(id,name,salary);    
end;

output:

Procedure created.

Call Existing Procedure:

BEGIN    
   insertemp(102,'kabir',50000);  
   dbms_output.put_line('record inserted successfully');    
END;

Now, see the "Employee" table, you will see one record is inserted.

DROP procedure:

Syntax:

DROP PROCEDURE procedure_name;

Example:

DROP PROCEDURE  instertemp;

Output:

Procedure dropped.

FUNCTION:

The PL/SQL Function is very similar to PL/SQL Procedure.

 The main difference between procedure and a function is, a function must always return a value, and on the other hand a procedure may or may not return a value.

Except this, all the other things of PL/SQL procedure are true for PL/SQL function too.

Create Function:

Syntax:

CREATE [OR REPLACE] FUNCTION function_name [parameters]  
[(parameter_name [IN | OUT | IN OUT] type [, ...])]  
RETURN return_datatype  
{IS | AS}  -- IS or AS
BEGIN 
 < function_body >  
END [function_name];

Example:

create or replace function adder(n1 in number, n2 in number)    
return number    is 
n3 number(8);    
begin 
n3 :=n1+n2;    
return n3;    
end;

Output:

Function created.

Call Function:

DECLARE 
n3 number(2);    
BEGIN   n3 := adder(11,22);    
dbms_output.put_line('Addition is: ' || n3);    
END;

Output:

Statement processed.
Addition is: 33

DROP FUNCTION:

Syntax:

DROP FUNCTION function_name;

Keep Updated. We will upload some advance example later.

Thank you for reading.

Ask your Doubt or Query in Comment Sections.

0 comments