What are PL/SQL Subprograms?
Named PL/SQL blocks of code which can be invoked using parameters are called PL/SQL sub programs.
What are the parts of PL/SQL Subprograms? Explain with an example
A PL/SQL program has 3 basic parts
- Declaration Block
- Executable Block
- Exception Block
Example :
DECLARE annual_salary NUMBER (8,2)
BEGIN
Select base*12 into annual_salary from employee where employee_id=6;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('There is no data for this employee');
END;
Advantages of PL/SQL Subprograms.
Advantages of PL/SQL subprograms are
1. The application makes a single call to the database to run a block of statements which improves performance against running SQL multiple times. This will reduce the number of calls between the database and the application.
2. PL/SQL is secure since the code resides inside the database thus hiding internal database details from the application. The application will only make a call to the PL/SQL sub program
3. PL/SQL and SQL go hand in hand so there would be no need of any translation required between PL/SQL and SQL.
PL/SQL has two types of subprograms called procedures and functions. Explain them.
-
Procedures: A procedure is a reusable, named block of code which accepts parameters and may or may not return any value after completion of execution.
Three types of parameters can be used in a Procedure:
a. IN parameter
b. OUT parameter
c. IN/OUT parameter
Since a procedure is sub program it has a DECLARATION, EXECUTABLE and EXCEPTION blocks.
Syntax:
CREATE OR REPLACE PROCEDURE SAMPLE//this statement creates a new
//procedure or replace is one
//already exists with the same name
IS
( emp_id IN OUT integer
dept_id IN integer
annual_sal OUT integer
)
AS
BEGIN
select employee_id , base_pay*12 into emp_id , annual_sal
from employee where employee_id=emp_id and department_id=dept_id;
END;
-
Functions:A function is also a subprogram which computes and returns a value. The function can also be used as part of an Sql. Statement.
Example:
CREATE OR REPLACE FUNCTION EmpName
(emp_id in integer
) return varchar2
IS
employee_name varchar2(100);
BEGIN
select emp_name into employee_name from employee where emp_id=emp_id;
RETURN employee_name;
EXCEPTION
WHEN NO_DATA_FOUND
employee_name='NA';
END;
- A function will always return a value. A procedure may or may not return a value.
- The return statement in a function returns the results of the function to the calling program. A procedure only returns control to the calling program
- Functions can be called in SQL statement(s). Procedures cannot be called in SQL statement(s).
How does subprogram pass information? What are actual and formal subprogram parameters? Explain with an example
Sub programs pass information using parameters. There a two types of parameters actual parameters and formal parameters. Formal parameters are parameters that are used in the sub program definition. Actual parameters are the actual values passed to the formal parameters when the sub program is called.
Example:
CREATE OR REPLACE FUNCTION Employee
(emp_id IN integer , grp_id IN integer ) //formal parameters
return integer
AS
base integer;
BEGIN
select base_salary to base from employee where employee_id=emp_id and department_id = dept_id;
RETURN base;
EXCEPTION
WHEN NO_DATA_FOUND
base=0;
END;
Employee(12345 , 67); //actual parameters are values passed to the function
Define three parameter modes, IN (the default), OUT, and IN OUT that can be used with any subprogram.
An IN parameter is used to send input values to the sub program. The IN parameters values cannot be changed You can assign value either using the := operator or the DEFAULT key word.
An OUT parameter is the value returned to the calling program.The value is initially set as NULL.
An I N OUT parameter can be used as an input and an output parameter.It can be assigned a default value like an IN parameter.Any changes made to the parameter during the execution of the sub program is returned as the OUT parameter value.
Example:
(parameter_1 IN VARCHAR2 := 'ABC',
parameter_2 IN VARCHAR2 DEFAULT 'ABC',
parameter_3 OUT number,
parameter_4 IN OUT number DEFAULT '1')
Depicts example for default values for subprogram parameters.
Example:
CREATE OR REPLACE PROCEDURE sample
(name IN varchar2 :='SAMPLE',
address IN varchar2,
emp_id OUT integer ,
emp_date IN OUT date)
AS
BEGIN
END;
Overview of Table Functions with an example.
Table functions are functions that return a collection of rows. They can be used as a table , view or also a part of the query. The input can be a collection or an cursor.
Example:
CREATE TYPE person as OBJECT // create a declaration of the object
(name as varchar2(100),
address as varchar2 (200));
CREATE TYPE person_array as a TABLE of person; // create a table of the object declared
What are Pipelined Table Functions? Explain how to implement it.
Pipelined table functions let us fetch results as they are processed and not wait for a complete execution. This improves performance and usage of memory resources.
Example:
CREATE TYPE numb_array as table of number;
CREATE FUNCTION num_func(numb IN number)
RETURN numb_array
PIPELINED
AS
BEGIN
FOR I IN 1..100
PIPE ROW(I);
END LOOP;
RETURN;
END;
Pipeline functions are used when a data source is required which is not a table used in the select statement.
What is a Recursive Subprogram? Explain with an example of this kind.
A recursive subprogram is a subprogram that calls itself. Each call creates a new instance of the subprogram.
Example: Finding factorial of a given integer
FUNCTION factorial(input_n INTEGER) RETURN integer
IS
BEGIN
IF input_n=1 THEN
RETURN 1;
ELSE
RETURN input_n * factorial(input_n-1);
END IF;
END factorial;
Explain how to call external subprograms from any PL/SQL block.
First you will have write a Java class and then create a Procedure to call the PL/SQL block
CREATE OR REPLACE AND RESOLVE JAVA SOURCE HelloWorld
AS
public Class HNClass ()
{
public static void HNFunc(String arg)
{
System.out.println(“Hello World”+arg);
}
}
CREATE OR REPLACE PROCEDURE call_java IS
PROCEDURE execute() AS LANGUAGE JAVA NAME
'HNClass.HNFunc(java.lang.String)';
END;
What is PL/SQL server page? Explain its working.
PL SQL server page are database procedures that run on the database server to produce dynamic content in Web applications.
<%@ plsql procedure="show_items_available " %>
<HTML>
<HEAD><TITLE>Show Items Available </TITLE></HEAD>
<BODY>
<UL>
<% FOR item IN (SELECT product_name, list_price
FROM item_sale
ORDER BY list_price DESC) LOOP %>
<LI>
Item = <%= item.product_name %><BR>
Price = <% = item.list_price %><BR>
<% END LOOP; %>
</UL>
</BODY>
</HTML>
