PL/SQL Subprograms

What Are PL/SQL Subprograms?

  • A PL/SQL subprogram is a named PL/SQL block that can be called with a set of parameters.
  • You can declare and define a subprogram within either a PL/SQL block or another subprogram.
  • A subprogram consists of a specification and a body.
  • A subprogram can be a procedure or a function.
  • Typically, you use a procedure to perform an action and a function to compute and return a value.
  • Subprograms can be grouped into PL/SQL packages.

The Benefits of Using PL/SQL Subprograms

Subprograms: Stored procedures and function

  • Easy maintenance
  • Improved performance
  • Improved data security and integrity
  • Improved code clarity

PROCEDURE


Syntax:

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter1 [mode] datatype1,
parameter2 [mode] datatype2, ...)]
IS|AS

[local_variable_declarations; ...]
BEGIN--actions;
END [procedure_name];

Example:

CREATE OR REPLACE PROCEDURE test
IS
BEGIN
dbms_output.put_line('Hi This is Bhavesh Lakhani');
END test;

Script output:

C:\Users\bhavesh1.lakhani\Desktop\Procedure test.png

Calling Procedure

EXECUTE test;

Function


Function should have at least one return statement.

Syntax to Create function

CREATE [OR REPLACE] FUNCTION function_name[(parameter1 [mode1]datatype1, . . .)]

RETURN datatype IS|AS

[local_variable_declarations; . . .]

BEGIN--actions;

RETURN expression;

END [function_name];

Example:

CREATE OR REPLACE FUNCTION get_sal(p_id employees.employee_id%TYPE)
RETURN NUMBER IS
v_sal employees.salary%TYPE := 0;
BEGIN
SELECT salary INTO v_sal
FROM employees
WHERE employee_id = p_id;
RETURN v_sal;
END get_sal;

Invoke the function as an expression or as a parameter value

EXECUTE dbms_output.put_line(get_sal(100))

Output:

As a PL/SQL expression, get the results using host variables

VARIABLE b_salary NUMBER;
EXECUTE :b_salary := get_sal(100);
print b_salary;

Script output:

Difference between Anonymous Block and Subprogram


Leave a Reply

Your email address will not be published. Required fields are marked *