PL/SQL and SQL Server Subprograms, Procedures,

Functions, and CTEs

(Detailed Answers)
Prepared for study and assignment use

Date: 26 Dec 2025

Introduction
This document provides detailed, structured explanations for five database-programming
questions covering Oracle PL/SQL subprograms, SQL Server stored procedures, PL/SQL
functions, and SQL Server Common Table Expressions (CTEs), including recursive CTEs.
Wherever helpful, short code examples are included to illustrate syntax and execution flow.

1. What is a PL/SQL subprogram? Explain its purpose and main characteristics.

In Oracle Database, a PL/SQL subprogram is a named, reusable block of PL/SQL code that
performs a specific task and can be invoked (called) repeatedly from different parts of an
application. PL/SQL subprograms come in two primary forms: procedures and functions.

Purpose of PL/SQL subprograms

PL/SQL subprograms are used to:
 Encapsulate business logic in the database so that rules are enforced consistently across all
clients (apps, reports, integrations).
 Promote code reuse and reduce duplication by providing a single implementation that can be
called from many places.
 Improve maintainability by isolating logic behind a stable interface (name, parameters, return
value).
 Support modular design: large problems can be decomposed into smaller, testable units.
 Improve security by granting users EXECUTE privilege on subprograms without granting
direct table privileges (a common pattern).
 Potentially improve performance by reducing network round-trips and leveraging server-side
execution close to the data.

Main characteristics
A typical PL/SQL subprogram has a clear interface (its name and parameters), an
implementation (the block of statements it runs), and optional exception-handling logic. Key
characteristics include:
 Named and callable: once created, it can be called by name from PL/SQL blocks, other
subprograms, triggers, packages, and (with restrictions) from SQL.
 Parameter support: parameters can be IN (input), OUT (output), or IN OUT (both). They can
also have default values.
 Local variables and control structures: inside a subprogram you can declare variables,
cursors, and use IF/CASE, LOOP/WHILE/FOR, etc.
 Exception handling: subprograms can catch and handle exceptions using EXCEPTION
blocks and can raise custom errors.
 Compilation and dependency tracking: subprograms are compiled objects stored in the data
dictionary; Oracle tracks dependencies so recompilation happens when referenced objects
change.
 Overloading (in packages): multiple subprograms can share the same name if they have
different parameter signatures.
 Can be stored as standalone objects or inside packages: packages group related
procedures/functions and can also hide private helper routines.
 Security model: privileges can be granted on the subprogram; code can run with definer’s
rights or invoker’s rights depending on the AUTHID clause.

Simple example (procedure and function)

-- Procedure: performs an action (no return value)
CREATE OR REPLACE PROCEDURE give_bonus(p_emp_id IN NUMBER, p_amount IN
NUMBER) AS
BEGIN
UPDATE employees
SET salary = salary + p_amount
WHERE employee_id = p_emp_id;
COMMIT;
END;
/

-- Function: returns a value

CREATE OR REPLACE FUNCTION annual_salary(p_emp_id IN NUMBER)
RETURN NUMBER AS
v_salary NUMBER;
BEGIN
SELECT salary * 12 INTO v_salary
FROM employees
WHERE employee_id = p_emp_id;

RETURN v_salary;
END;
/

In practice, procedures are chosen when the primary goal is to perform actions
(INSERT/UPDATE/DELETE, calling other routines, sending messages), while functions are
chosen when the goal is to compute and return a value.

2. What are the three parts of a PL/SQL subprogram? Briefly describe each part.
A PL/SQL subprogram is commonly described as having three major parts. Different textbooks
use slightly different labels, but the same core structure applies.
Part 1: Specification (Header / Signature)
The specification (also called the header or signature) defines the subprogram interface. It
includes the subprogram name, optional parameter list (with modes IN/OUT/IN OUT), and, for
functions, the RETURN datatype. This part tells callers how to invoke the subprogram.

-- Procedure specification
PROCEDURE give_bonus(p_emp_id IN NUMBER, p_amount IN NUMBER);

-- Function specification
FUNCTION annual_salary(p_emp_id IN NUMBER) RETURN NUMBER;

Part 2: Declarative Section (Optional)

The declarative section is where you define local items used by the subprogram: variables,
constants, cursor definitions, user-defined types, and nested helper subprograms. This section
appears between the AS/IS keyword and the BEGIN keyword.

CREATE OR REPLACE PROCEDURE example_proc AS

v_total NUMBER := 0; -- local variable
c_tax CONSTANT NUMBER := 0.1; -- constant
BEGIN
-- executable statements
NULL;
END;
/

Part 3: Executable Section and Exception Section (Body)

The body contains the executable statements (between BEGIN and END) that perform the actual
work. Optionally, an EXCEPTION section can follow the executable statements to handle
runtime errors. Together, these are often called the implementation (or program body).

CREATE OR REPLACE FUNCTION safe_divide(p_a NUMBER, p_b NUMBER)

RETURN NUMBER AS
v_result NUMBER;
BEGIN
v_result := p_a / p_b; -- executable statement
RETURN v_result;

EXCEPTION
WHEN ZERO_DIVIDE THEN
RETURN NULL; -- graceful fallback
END;
/

In summary: (1) the specification defines how to call it, (2) the declarative section defines what it
needs, and (3) the body executes the logic and optionally handles exceptions.
3. What is a stored procedure in SQL Server? How is it created and executed?
In Microsoft SQL Server, a stored procedure (often shortened to 'stored proc' or 'procedure') is a
named collection of Transact-SQL (T-SQL) statements stored in the database and executed as a
single unit. Stored procedures can accept input parameters, return output parameters, and return
an integer status code. They are widely used to encapsulate data access logic, enforce business
rules, and improve performance and security.

Why stored procedures are used

 Encapsulation and reuse: centralize data logic so multiple applications call the same routine.
 Security: grant EXECUTE on a procedure without granting direct
SELECT/INSERT/UPDATE/DELETE privileges on tables.
 Performance: plans can be cached; procedure calls can reduce network traffic and allow set-
based operations close to the data.
 Maintainability: updates to logic can be deployed in one place without redeploying
application binaries.
 Consistency: validations and transactional logic are executed the same way regardless of
client.

Creating a stored procedure (T-SQL syntax)

In SQL Server, procedures are typically created using CREATE PROCEDURE (or CREATE
PROC). The procedure body uses standard T-SQL, and often includes SET NOCOUNT ON to
avoid extra rowcount messages.

-- Example: procedure to insert a customer

CREATE PROCEDURE [Link]
@FullName NVARCHAR(100),
@Email NVARCHAR(200),
@CustomerId INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO [Link](FullName, Email)

VALUES (@FullName, @Email);

-- Return the generated identity value to the caller

SET @CustomerId = SCOPE_IDENTITY();
END;
GO

Executing a stored procedure

SQL Server provides two common commands to run a stored procedure: EXEC and EXECUTE
(they are synonyms). You can pass parameters positionally or by name.
-- Execute with named parameters and capture an OUTPUT value
DECLARE @NewId INT;

EXEC [Link]
@FullName = N'Ram Shrestha',
@Email = N'ram@[Link]',
@CustomerId = @NewId OUTPUT;

SELECT @NewId AS CreatedCustomerId;

Return status codes and OUTPUT parameters

A SQL Server procedure can return an integer status code using RETURN. This is often used to
signal success (0) or an error condition (non-zero). Note that RETURN can return only an INT.
For richer outputs, use OUTPUT parameters or result sets (SELECT).

CREATE PROCEDURE [Link]

@CustomerId INT
AS
BEGIN
SET NOCOUNT ON;

DELETE FROM [Link] WHERE CustomerId = @CustomerId;

IF @@ROWCOUNT = 0
RETURN 1; -- not found

RETURN 0; -- success
END;
GO

DECLARE @rc INT;

EXEC @rc = [Link] @CustomerId = 10;
SELECT @rc AS ReturnCode;

Transactions and error handling

Procedures frequently use explicit transactions (BEGIN TRAN / COMMIT / ROLLBACK) and
error-handling blocks (TRY...CATCH). This is essential when multiple statements must succeed
or fail as a unit.

CREATE PROCEDURE [Link]

@FromAccount INT,
@ToAccount INT,
@Amount DECIMAL(18,2)
AS
BEGIN
 SET NOCOUNT ON;
BEGIN TRY
BEGIN TRAN;

UPDATE [Link]
SET Balance = Balance - @Amount
WHERE AccountId = @FromAccount;

UPDATE [Link]
SET Balance = Balance + @Amount
WHERE AccountId = @ToAccount;

COMMIT TRAN;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRAN;
THROW; -- rethrow error with original details
END CATCH
END;
GO

In real systems, additional safeguards are common: validating account existence, preventing
negative balances, using appropriate isolation levels, and applying locking hints only when
justified.

4. What is a PL/SQL function? Differentiate between scalar functions and table-

valued functions.
A PL/SQL function is a named PL/SQL subprogram that returns a single value to the caller.
Unlike a procedure, a function must include a RETURN clause in its specification and must
return a value (using RETURN <expression>) from its body. Functions can accept parameters
(usually IN parameters) and are commonly used for calculations, data transformations, and
reusable validations.

Key properties of PL/SQL functions

 Must return a value: the RETURN datatype is declared in the function signature.
 Can be invoked from PL/SQL and (if the function is SQL-usable) from SQL statements such
as SELECT, WHERE, or ORDER BY.
 Can be deterministic (optionally declared with DETERMINISTIC) if it always returns the
same result for the same inputs—useful for query optimization and function-based indexes.
 Should avoid side effects if intended for SQL usage (e.g., avoid
DML/COMMIT/ROLLBACK in a function used inside a query).
 Supports exception handling and can raise application errors using
RAISE_APPLICATION_ERROR.
PL/SQL function example
CREATE OR REPLACE FUNCTION calc_discount(p_amount NUMBER)
RETURN NUMBER AS
v_discount NUMBER;
BEGIN
IF p_amount >= 1000 THEN
v_discount := p_amount * 0.10; -- 10%
ELSIF p_amount >= 500 THEN
v_discount := p_amount * 0.05; -- 5%
ELSE
v_discount := 0;
END IF;

RETURN v_discount;
END;
/

Important note: the question also asks about scalar and table-valued functions. Those terms are
primarily used in SQL Server terminology. In Oracle, you will often hear about scalar functions
versus functions that return collections (pipelined table functions). Below is a clear
differentiation aligned with SQL Server usage, while mapping the concept back to Oracle.

Scalar functions
A scalar function returns a single atomic value (one value per invocation), such as an INT,
NUMBER, VARCHAR, DATE, or similar. It behaves like an expression: you pass in parameters
and you get one value back.
Common uses:
 Calculations (tax, discounts, scoring, formatting).
 Normalization (trimming text, converting units).
 Business rules that return true/false or a status code.
 Deriving a single value from table lookups (e.g., current price for a product).
SQL Server scalar UDF example:

CREATE FUNCTION [Link](@First NVARCHAR(50), @Last NVARCHAR(50))

RETURNS NVARCHAR(120)
AS
BEGIN
RETURN LTRIM(RTRIM(@First)) + N' ' + LTRIM(RTRIM(@Last));
END;
GO

SELECT [Link](FirstName, LastName) AS FullName

FROM [Link];
Table-valued functions (TVFs)
A table-valued function returns a result set (a table) rather than a single value. It can be queried
like a table using FROM, JOIN, APPLY, etc. In SQL Server, TVFs come in two main forms:
 Inline TVF: defined by a single SELECT statement; tends to optimize well because it can be
expanded like a view.
 Multi-statement TVF: builds a table variable using multiple statements (INSERT/UPDATE),
then returns it; may have different performance characteristics.
SQL Server inline TVF example:

CREATE FUNCTION [Link](@CustomerId INT)

RETURNS TABLE
AS
RETURN
(
SELECT [Link], [Link], [Link]
FROM [Link] o
WHERE [Link] = @CustomerId
);
GO

SELECT *
FROM [Link](42);

SQL Server multi-statement TVF example:

CREATE FUNCTION [Link](@CustomerId INT)

RETURNS @Stats TABLE
(
CustomerId INT,
OrderCount INT,
TotalSpent DECIMAL(18,2)
)
AS
BEGIN
INSERT INTO @Stats(CustomerId, OrderCount, TotalSpent)
SELECT
@CustomerId,
COUNT(*),
COALESCE(SUM(TotalAmount), 0)
FROM [Link]
WHERE CustomerId = @CustomerId;

RETURN;
END;
GO
SELECT *
FROM [Link](42);

Key differences: scalar vs table-valued functions

Scalar Function Table-Valued Function (TVF)
Returns exactly one value per call (e.g., Returns a table (multiple rows and columns)
NUMBER, INT, VARCHAR). per call.
Used in expressions: SELECT list, WHERE Used in FROM/JOIN/APPLY like a table or
clause, ORDER BY, computed columns. view.
Typically small, computation-focused Used to encapsulate reusable queries or
routines. complex row-producing logic.
In SQL Server, may impact performance if Inline TVFs often optimize well; multi-
executed row-by-row over large sets statement TVFs can be less optimal in some
(depending on version and function type). cases.
Oracle equivalent: standard PL/SQL function Oracle equivalent: pipelined table functions or
returning a scalar datatype. functions returning a collection type (queried
with TABLE()).

5. What is a Common Table Expression (CTE) in SQL Server? Explain the concept
of a recursive CTE.
A Common Table Expression (CTE) in SQL Server is a named, temporary result set that exists
only for the duration of a single statement (such as SELECT, INSERT, UPDATE, DELETE, or
MERGE). A CTE is defined using the WITH keyword and can be referenced like a table or view
within the statement that immediately follows it.

Why CTEs are useful

 Improves readability by breaking complex queries into logical steps.
 Allows reusing a derived result set multiple times within the same statement.
 Can replace many subqueries and make intent clearer.
 Enables recursion: ideal for hierarchical or graph-like data (org charts, category trees, bill-of-
materials).

Basic (non-recursive) CTE structure

WITH RecentOrders AS
(
SELECT OrderId, CustomerId, OrderDate, TotalAmount
FROM [Link]
 WHERE OrderDate >= DATEADD(day, -30, GETDATE())
)
SELECT CustomerId, COUNT(*) AS OrderCount, SUM(TotalAmount) AS TotalSpent
FROM RecentOrders
GROUP BY CustomerId;

Here, RecentOrders is the CTE name. It behaves like a temporary view that is only visible to the
SELECT statement that follows.

Recursive CTE: concept and components

A recursive CTE is a CTE that references itself. It is used to iteratively build a result set, starting
from a base (anchor) set of rows and repeatedly applying a recursive step until no new rows are
produced.
A recursive CTE has three essential parts:
1. Anchor member: the initial query that produces the starting rows (level 0 or level 1).
2. Recursive member: a query that references the CTE itself to produce the next level of rows.
3. Termination condition: recursion stops naturally when the recursive member returns no rows
(or when a maximum recursion limit is reached).

Example: organizational hierarchy (manager -> employees)

-- Table assumption: Employees(EmployeeId, FullName, ManagerId)
WITH OrgChart AS
(
-- 1) Anchor member: start with the top-level manager (CEO)
SELECT
[Link],
[Link],
[Link],
0 AS Level
FROM [Link] e
WHERE [Link] IS NULL

UNION ALL

-- 2) Recursive member: find employees who report to the current level

SELECT
[Link],
[Link],
[Link],
[Link] + 1 AS Level
FROM [Link] e
INNER JOIN OrgChart oc
ON [Link] = [Link]
)
SELECT EmployeeId, FullName, ManagerId, Level
FROM OrgChart
ORDER BY Level, FullName;

How recursion works in this query

Step-by-step execution is conceptualized as follows:
1) SQL Server runs the anchor query once, producing the initial set (e.g., the CEO).
2) SQL Server then runs the recursive query, using the rows produced so far to find the next
level (direct reports).
3) The newly found rows are added to the CTE result set.
4) The recursive query repeats, now using the expanded set, until it finds no further children.
5) Finally, the outer SELECT reads the fully built result set.

MAXRECURSION and safety considerations

SQL Server protects against infinite recursion by applying a default recursion limit of 100
iterations. You can change it using the OPTION (MAXRECURSION n) query hint. Using
MAXRECURSION 0 means 'no limit', which should be used carefully.

-- Increase recursion depth to 500 levels

WITH OrgChart AS ( ... )
SELECT *
FROM OrgChart
OPTION (MAXRECURSION 500);

Common safety and correctness issues include:

- Cycles in hierarchical data (e.g., A manages B and B manages A). This can cause infinite
recursion unless you detect cycles.
- Very deep hierarchies, which may require increasing MAXRECURSION.
- Performance: ensure the join columns (e.g., ManagerId) are indexed and recursion is not
producing excessive rows.

Example: recursive CTE with cycle detection (pattern)

A common technique to prevent cycles is to carry a 'path' column and ensure the next node is not
already in the path. This example demonstrates the idea; exact implementation varies by schema.

WITH OrgChart AS
(
SELECT
[Link],
[Link],
[Link],
CAST(CONCAT('/', [Link], '/') AS VARCHAR(MAX)) AS Path,
0 AS Level
 FROM [Link] e
WHERE [Link] IS NULL

UNION ALL

SELECT
[Link],
[Link],
[Link],
CAST(CONCAT([Link], [Link], '/') AS VARCHAR(MAX)) AS Path,
[Link] + 1 AS Level
FROM [Link] e
INNER JOIN OrgChart oc
ON [Link] = [Link]
WHERE [Link] NOT LIKE CONCAT('%/', [Link], '/%') -- prevents
cycles
)
SELECT EmployeeId, FullName, ManagerId, Level, Path
FROM OrgChart
OPTION (MAXRECURSION 500);

Conclusion
PL/SQL subprograms (procedures and functions) are core building blocks for server-side
programming in Oracle, providing modularity, reuse, and centralized business logic. SQL Server
stored procedures offer similar benefits in the Microsoft ecosystem. Functions can be scalar
(single-value) or table-valued (result-set returning), and CTEs provide a powerful, readable way
to structure queries—including recursive CTEs for hierarchical problems.