Module 1

Fundamentals of PL/SQL:.
Step 1: Open Microsoft sql Server management studio

Step 2: Click file→ new file→ DataBase QueryEngin→ connect Database

Step 3: Create Database

create database student1

use student1
create table std1(sid int, sname varchar(20),doj datetime)
insert into std1 values(1,'gaytri','7/8/2020')

insert into std1 values(1,'gaytri','7/8/2020')

insert into std1 values(2,'preety','7/8/2020')
insert into std1 values(3,'meghna','7/8/2020')
insert into std1 values(4,'Prajakta','7/8/2020')
insert into std1 values(5,'sarita','7/8/2020')
insert into std1 values(6,'gaytri','7/8/2020')
insert into std1 values(7,'preety','7/8/2020')

1
insert into std1 values(8,'meghna','7/8/2020')
insert into std1 values(9,'Prajakta','7/8/2020')
insert into std1 values(10,'sarita','7/8/2020')

select * from std1

Output:

Using case expression:

In SQL Server, the CASE expression is used to return values based on conditional
logic, similar to an IF-ELSE statement but inline within a query. You can use CASE in
SELECT statements, WHERE clauses, and even in UPDATE statements.

Because CASE is an expression, you can use it in any clause that accepts an
expression such as SELECT, WHERE, GROUP BY, and HAVING.

select * from std1 where sid>=2

select * from std1 where sid between 3 and 5

2
select * from std1 where sid in (3,5)

select * from std1 where sname like '%a'

select * from std1 where sname like 's%'

select * from std1 order by sname

select * from std1 order by sname desc

3
select sum(sid) as total from std1

–Declarations

declare
@a int
set @a=10
print @a

declare @V varchar(20)
set @V='hello'
print 'value is' +@V

–if Loop
declare
@a int
set @a=56

4
 if @a>=35
print 'pass'
else
print 'fail'

–While loop
declare
@a int
set @a=1
while(@a<=8)
begin
print 'value is'+str(@a)
set @a=@a+1
end

GOTO, and NULL Statements.

DECLARE @Counter INT = 1

-- Start of the block

PRINT 'Start of the block'

-- Label
Increment:
SET @Counter = @Counter + 1

PRINT 'Counter is ' + CAST(@Counter AS VARCHAR)

5
–Note:cast is use for change data types

-- GOTO statement
IF @Counter < 5
GOTO Increment

PRINT 'End of the block'

Implicit Cursor Example

When you execute a SELECT statement, SQL Server creates an implicit cursor to
process the result set. You typically use this with control-of-flow statements, such as
IF, WHILE, or CASE.

In PL/SQL, implicit cursors are automatically created by Oracle whenever a SQL

statement (such as SELECT, INSERT, UPDATE, or DELETE) is executed. Unlike
explicit cursors, implicit cursors are managed entirely by Oracle, and you don't need
to declare or define them.

To create a customer data set in SQL Server, you can design a table that captures
common details about customers. Below is an example of how you could define the
structure for a Customer table:

CREATE TABLE Customers (

CustomerID INT IDENTITY(1,1) PRIMARY KEY, -- Unique
identifier for each customer
FirstName NVARCHAR(50) NOT NULL, -- Customer's first
name
LastName NVARCHAR(50) NOT NULL, -- Customer's last name
Email NVARCHAR(255) UNIQUE NOT NULL, -- Email address
Phone NVARCHAR(25), -- Phone number
AddressLine1 NVARCHAR(255), -- Address (Line 1)

6
 AddressLine2 NVARCHAR(255), -- Address (Line 2)
City NVARCHAR(100), -- City
State NVARCHAR(50), -- State or Province
PostalCode NVARCHAR(20), -- Postal/Zip code
Country NVARCHAR(50) DEFAULT 'Unknown', -- Country
DateOfBirth DATE, -- Date of birth
CreatedDate DATETIME DEFAULT GETDATE(), -- When the record
was created
IsActive BIT DEFAULT 1 -- Whether the customer is active
(1 = Yes, 0 = No)
);

INSERT INTO Customers (FirstName, LastName, Email, Phone,

AddressLine1, City, State, PostalCode, Country, DateOfBirth)
VALUES
('John', 'Doe', '[Link]@[Link]', '123-456-7890', '123
Elm St', 'Los Angeles', 'CA', '90001', 'USA', '1985-06-15'),
('Jane', 'Smith', '[Link]@[Link]', '987-654-3210',
'456 Oak St', 'New York', 'NY', '10001', 'USA', '1990-12-05'),
('Ali', 'Khan', '[Link]@[Link]', '555-666-7777', '789
Pine St', 'Mumbai', 'MH', '400001', 'India', '1980-01-01');

SELECT * FROM Customers;

INSERT INTO Customers (FirstName, LastName, Email)

VALUES ('Emily', 'Jones', '[Link]@[Link]');

-- Implicit cursor for a SELECT operation

SELECT FirstName, LastName FROM Customers;

7
Explanation:

●​ The INSERT and SELECT statements automatically create

implicit cursors.
●​ You don't see or control these cursors directly; SQL
Server internally processes the data row by row.

2. Explicit Cursor

An explicit cursor is declared and controlled explicitly by

the user. It allows you to process rows in a result set one at
a time using a procedural approach.

Characteristics:

●​ You define and control the cursor.

●​ Used for complex row-by-row processing in a loop.
●​ Requires the following steps:
1.​Declare the cursor.
2.​Open the cursor.
3.​Fetch rows from the cursor.
4.​Close the cursor.

DECLARE @FirstName NVARCHAR(50), @LastName NVARCHAR(50);

DECLARE customer_cursor CURSOR FOR

SELECT FirstName, LastName FROM Customers;

-- Open the cursor

OPEN customer_cursor;

-- Fetch rows from the cursor

FETCH NEXT FROM customer_cursor INTO @FirstName, @LastName;

-- Loop through the rows

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Customer Name: ' + @FirstName + ' ' + @LastName;
FETCH NEXT FROM customer_cursor INTO @FirstName,
@LastName;

8
END;

-- Close and deallocate the cursor

CLOSE customer_cursor;
DEALLOCATE customer_cursor;

Explanation:

1.​Declare: The customer_cursor is declared for the SELECT

query.
2.​Open: Opens the cursor for processing.
3.​Fetch: Retrieves rows one by one into variables
@FirstName and @LastName.
4.​Loop: Processes each row using a WHILE loop.
5.​Close & Deallocate: Closes the cursor and releases
resources.

parameterized cursor:

9
A parameterized cursor is an explicit cursor that accepts
parameters to make it more flexible and dynamic. Instead of
hardcoding the values in the query, you can pass parameters to
the cursor at runtime. This allows the same cursor to be
reused for different inputs.
CREATE PROCEDURE GetCustomersByCountry
@Country NVARCHAR(50)
AS
BEGIN
DECLARE @FirstName NVARCHAR(50), @LastName NVARCHAR(50);

-- Declare a cursor with a parameter

DECLARE country_cursor CURSOR FOR
SELECT FirstName, LastName
FROM Customers
WHERE Country = @Country;

-- Open the cursor

OPEN country_cursor;

-- Fetch rows from the cursor

FETCH NEXT FROM country_cursor INTO @FirstName, @LastName;

-- Loop through the rows

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Customer: ' + @FirstName + ' ' + @LastName;
FETCH NEXT FROM country_cursor INTO @FirstName,
@LastName;
END;

-- Close and deallocate the cursor

CLOSE country_cursor;
DEALLOCATE country_cursor;
END;

10
Execution:

To use the parameterized cursor, call the stored procedure and

pass the desired value for the parameter:

Query to execute:

EXEC GetCustomersByCountry @Country = 'USA';

Output:

Advantages of Parameterized Cursors

1.​Flexibility: Reusable for different inputs.

2.​Dynamic Queries: Queries can adapt to the input
parameters.
3.​Reduced Code Duplication: A single cursor declaration can
handle multiple scenarios.
4.​Improved Readability: Parameters make the intent clear
and the code easier to maintain.

Collection and Composite Data Types - Working with Collections,Working with

Composite Data Types

collections and composite data types are typically handled

through table types, arrays, and structured data types. While
SQL Server doesn't have the same native PL/SQL collection
types (like VARRAY, Associative Array, or Nested Table), it
supports collections through table-valued parameters (TVPs)

11
and user-defined table types. Similarly, composite data types
are handled via user-defined types (UDTs) and structured data
types.

Working with Collections in SQL Server:

Imagine you want to insert a list of employees into an
Employees table. Instead of inserting each employee
individually in separate queries, you can use a table-valued
parameter (TVP) to insert multiple rows in one go.

Steps:

1. Create a Table to Store Employees:

First, we need a table to store employee data.

CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY,

EmployeeName NVARCHAR(100) );

2. Create a Table Type:

Next, create a table type to define the structure of the

collection that you'll pass as a parameter. In this case, the
table type will have two columns: EmployeeID and EmployeeName.

CREATE TYPE [Link] AS TABLE

(
EmployeeID INT,
EmployeeName NVARCHAR(100)
);

This EmployeeType will serve as the collection of employees

that you can pass to a stored procedure.

3. Create a Stored Procedure to Insert Employees:

12
Now, let's create a stored procedure that will accept the
table type (collection of employees) as a parameter and insert
the rows into the Employees table.

CREATE PROCEDURE [Link]

@Employees [Link] READONLY
AS
BEGIN
-- Insert all rows from the table-valued parameter into the
Employees table
INSERT INTO Employees (EmployeeID, EmployeeName)
SELECT EmployeeID, EmployeeName
FROM @Employees;
END;

Notice that the table-valued parameter is marked as READONLY.

SQL Server requires table parameters to be read-only.

4. Use the Stored Procedure to Insert Data:

Now, let's use the stored procedure to insert multiple

employees at once.

1.​Declare a variable of type [Link] to hold the

collection of employee data.
2.​Insert values into the variable.
3.​Pass the variable as a parameter to the stored procedure.

DECLARE @NewEmployees [Link];

-- Insert some employee data into the table-valued parameter

INSERT INTO @NewEmployees (EmployeeID, EmployeeName)
VALUES (1, 'John Doe'),
(2, 'Jane Smith'),
(3, 'Alice Johnson');

-- Call the stored procedure to insert the data into the

Employees table
EXEC [Link] @Employees = @NewEmployees;

13
5. Verify the Data:

Finally, verify that the employees were inserted correctly by

querying the Employees table.

SELECT * FROM Employees;

Working with Composite Data Types:

In SQL Server, composite data types are types that can hold multiple values or
represent a collection of different types. These are typically handled using:

●​ Table Variables
●​ User-Defined Types (UDTs)
●​ Structured Data Types (via CREATE TYPE)
●​ XML or JSON (for storing hierarchical data)

Example 1: Table Variables (Composite Data Type)

14
A table variable allows you to store rows and work with them like a temporary table,
but the data scope is limited to the batch, stored procedure, or function in which it's
declared.

DECLARE @EmployeeDetails TABLE (

EmployeeID INT,

FirstName NVARCHAR(50),

LastName NVARCHAR(50),

Department NVARCHAR(50)

);

-- Insert some data into the table variable

INSERT INTO @EmployeeDetails (EmployeeID, FirstName, LastName,

Department)

VALUES

(1, 'John', 'Doe', 'HR'),

(2, 'Jane', 'Smith', 'IT'),

(3, 'Sam', 'Johnson', 'Finance');

-- Query the data from the table variable

SELECT * FROM @EmployeeDetails;

Explanation:

15
 ●​ @EmployeeDetails is a table variable that holds a set of rows with columns
such as EmployeeID, FirstName, LastName, and Department.
●​ You can insert data into this variable and perform queries just like a regular
table.

Example 2: User-Defined Types (UDTs) (Composite Data Type)

A user-defined type (UDT) allows you to create a custom data type that can be
used in table definitions, variables, and parameters.

-- Create a user-defined type to represent an employee address

CREATE TYPE EmployeeAddress AS TABLE (

StreetAddress NVARCHAR(255),

City NVARCHAR(100),

State NVARCHAR(50),

ZipCode NVARCHAR(10)

);

-- Declare a variable of the custom EmployeeAddress type

DECLARE @Address EmployeeAddress;

-- Insert some address data into the table variable

INSERT INTO @Address (StreetAddress, City, State, ZipCode)

VALUES

('123 Main St', 'New York', 'NY', '10001'),

('456 Oak Ave', 'Los Angeles', 'CA', '90001');

-- Query the custom UDT table variable

16
SELECT * FROM @Address;

17
Module-2

1. Creation of Procedures in PL/SQL

Definition of a Procedure

A procedure in PL/SQL is a subprogram that can take parameters and execute a

sequence of SQL and PL/SQL statements. Unlike functions, procedures do not
necessarily return a value.

Syntax for Creating a Procedure

CREATE OR REPLACE PROCEDURE procedure_name

( parameter1 datatype, parameter2 datatype, ... )

IS

-- Declaration section

BEGIN

-- Executable section (SQL and PL/SQL statements)

EXCEPTION

-- Exception-handling section

END procedure_name;

Example:

Step 1: Create table

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

FirstName NVARCHAR(50),

LastName NVARCHAR(50),

Department NVARCHAR(50)

18
);

–Step2: Then insert some test data:

INSERT INTO Employees (EmployeeID, FirstName, LastName,

Department)

VALUES (101, 'John', 'Doe', 'IT'), (102, 'Jane', 'Smith',

'HR');

Step3: Modify the Procedure to Use the Correct Schema

If your table is under the dbo schema (default schema), try:

CREATE PROCEDURE [Link]

@EmployeeID INT

AS

BEGIN

SET NOCOUNT ON;

SELECT EmployeeID, FirstName, LastName, Department

FROM [Link] -- Ensure the correct schema

WHERE EmployeeID = @EmployeeID;

END;

Step4:Then execute:

EXEC [Link] @EmployeeID = 101;

19
2. Functions in PL/SQL
A Function is a reusable block of SQL that returns a value. Unlike Stored
Procedures, Functions must return a value and cannot modify data (i.e., they cannot
use INSERT, UPDATE, or DELETE).

Types of Functions in SQL Server

1. Scalar Functions → Returns a single value.

2. Table-Valued Functions (TVF) → Returns a table.

○​ Inline Table-Valued Function

○​ Multi-Statement Table-Valued Function

[Link] Function Example

A scalar function returns a single value, such as a calculation.

Example: Function to Calculate Bonus

Step1:

20
CREATE FUNCTION CalculateBonus(@Salary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
AS
BEGIN
DECLARE @Bonus DECIMAL(10,2);
SET @Bonus = @Salary * 0.10; -- 10% Bonus Calculation
RETURN @Bonus;
END;
Step2: Executing the Function
SELECT [Link](5000) AS BonusAmount;

21
[Link]-Valued Functions (TVF)

Inline Table-Valued Function Example

Returns a table based on a query.

Example: Function to Get Employees by Department

CREATE FUNCTION GetEmployeesByDepartment(@Dept NVARCHAR(50))

RETURNS TABLE

AS

RETURN

SELECT EmployeeID, FirstName, LastName, Department

FROM Employees

WHERE Department = @Dept

);

Step2:Executing the Function

SELECT * FROM [Link]('IT');

[Link]-Statement Table-Valued Function

Returns a table but allows multiple statements within it.

1️ Check If the Salary Column Exists

Run this query to verify if the Salary column exists:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS

22
WHERE TABLE_NAME = 'Employees';
If Salary is missing, check if another column (like
EmployeeSalary) exists.
If Employees does not exist, create it.

2️ If Salary Column Is Missing, Add It

If the column does not exist, add it using:

ALTER TABLE Employees ADD Salary DECIMAL(10,2);

Then, insert sample data:

UPDATE Employees SET Salary = 7000 WHERE EmployeeID = 101;

UPDATE Employees SET Salary = 8000 WHERE EmployeeID = 102;

–Multi-Statement Table-Valued Function

Example: Function to Return High-Salary Employees

CREATE FUNCTION GetHighSalaryEmployees(@MinSalary

DECIMAL(10,2))

RETURNS @HighSalaryTable TABLE

EmployeeID INT,

FirstName NVARCHAR(50),

LastName NVARCHAR(50),

Salary DECIMAL(10,2)

AS

BEGIN

INSERT INTO @HighSalaryTable

23
 SELECT EmployeeID, FirstName, LastName, Salary

FROM Employees

WHERE Salary > @MinSalary;

RETURN;

END;

Executing the Function:

SELECT * FROM [Link](6000);

24
3. Creation of Trigger :

A Trigger is a special type of stored procedure that automatically runs when an

event (INSERT, UPDATE, DELETE) occurs in a table.

Example: Row-Level Trigger for Preventing Negative Salaries.

This trigger prevents inserting or updating employees with a negative salary.

Step 1: Create the Trigger

CREATE TRIGGER trg_PreventNegativeSalary

ON Employees

AFTER INSERT, UPDATE

AS

BEGIN

IF EXISTS (SELECT 1 FROM inserted WHERE Salary < 0)

BEGIN

RAISERROR ('Salary cannot be negative!', 16, 1);

ROLLBACK TRANSACTION;

END;

END;

Step 2: Test the Trigger

UPDATE Employees SET Salary = -5000 WHERE EmployeeID = 101;

25
[Link] Statement level trigger:

A Statement-Level Trigger fires once per SQL statement, regardless of how many
rows are affected. This is the default behavior of triggers in SQL Server.

Example: Statement-Level Trigger for Auditing Inserts

This trigger logs every INSERT operation into an audit table.

Step 1: Create an Audit Table

CREATE TABLE Employee_Audit (
AuditID INT IDENTITY PRIMARY KEY,
ActionPerformed NVARCHAR(50),
ActionDate DATETIME DEFAULT GETDATE(),
AffectedRows INT
);

Step 2: Create the Trigger

CREATE TRIGGER trg_AfterInsertEmployee

26
ON Employees
AFTER INSERT
AS
BEGIN
-- Logs statement-level changes
DECLARE @RowCount INT;
SET @RowCount = (SELECT COUNT(*) FROM inserted);

INSERT INTO Employee_Audit (ActionPerformed, AffectedRows)

VALUES ('INSERT', @RowCount);
END;

Step 3: Test the Trigger

INSERT INTO Employees (EmployeeID, FirstName, LastName,

Department, Salary)
VALUES (201, 'John', 'Doe', 'IT', 6000),
(202, 'Jane', 'Smith', 'HR', 7000);

SELECT * FROM Employee_Audit;

27
3. Create instead of trigger:

An INSTEAD OF Trigger replaces the default behavior of INSERT, UPDATE, or

DELETE operations. This is useful for enforcing business rules or working with
views that do not allow direct modifications.

Example 1: INSTEAD OF DELETE (Prevent Deletion, Mark as Inactive)

This trigger prevents employee deletion and marks them as inactive instead.

Step 1: Add an IsActive Column

ALTER TABLE Employees ADD IsActive BIT DEFAULT 1;

Step 2: Create the Trigger

CREATE TRIGGER trg_InsteadOfDeleteEmployee

ON Employees

28
INSTEAD OF DELETE

AS

BEGIN

-- Mark employees as inactive instead of deleting them

UPDATE Employees

SET IsActive = 0

WHERE EmployeeID IN (SELECT EmployeeID FROM deleted);

END;

Step 3: Test the Trigger:

DELETE FROM Employees WHERE EmployeeID = 101; -- This will NOT

delete the row

SELECT * FROM Employees WHERE EmployeeID = 101;

29
Example 2: INSTEAD OF INSERT (Enforce Salary Limits)

This trigger prevents inserting employees with salaries below

3000.

Step 1: Create the Trigger

CREATE TRIGGER trg_InsteadOfInsertEmployee

ON Employees

INSTEAD OF INSERT

AS

BEGIN

IF EXISTS (SELECT 1 FROM inserted WHERE Salary < 3000)

BEGIN

RAISERROR ('Salary must be at least 3000!', 16, 1);

ROLLBACK TRANSACTION;

END

ELSE

BEGIN

INSERT INTO Employees (EmployeeID, FirstName,

LastName, Department, Salary, IsActive)

SELECT EmployeeID, FirstName, LastName, Department,

Salary, 1

FROM inserted;

END

END;

30
Step 2: Test the Trigger

INSERT INTO Employees (EmployeeID, FirstName, LastName,

Department, Salary)

VALUES (105, 'Mike', 'Lee', 'Finance', 2500); -- This will fail

31
4. Handling exceptions-

SQL Server provides error handling using TRY...CATCH blocks to gracefully

handle exceptions like division by zero, constraint violations, deadlocks, or
conversion errors.

Basic TRY...CATCH Example

This block catches any error and logs it instead of failing the transaction.

BEGIN TRY

-- Attempt a division by zero (will cause an error)

DECLARE @Result INT;

SET @Result = 10 / 0; -- Division by zero error

PRINT 'Result: ' + CAST(@Result AS NVARCHAR(50));

END TRY

BEGIN CATCH

-- Catch the error and display a message

PRINT 'An error occurred!';

PRINT 'Error Message: ' + ERROR_MESSAGE(); -- Shows the

actual error message

END CATCH;

32
1. Creation of user defined exception:

In SQL Server, you can create user-defined exceptions using

the RAISERROR statement to generate custom error messages when
a specific condition occurs. This is particularly useful when
you need to enforce business rules or constraints that are not
covered by built-in constraints.

Example: User-Defined Exception for Invalid Salary

Step 1: Create the User-Defined Exception with RAISERROR

CREATE PROCEDURE InsertEmployeeWithSalaryCheck

@EmployeeID INT,

@FirstName NVARCHAR(50),

@LastName NVARCHAR(50),

@Department NVARCHAR(50),

@Salary DECIMAL(10,2)

AS

BEGIN

33
 BEGIN TRY

-- User-defined exception: Check for salary less than

3000

IF @Salary < 3000

BEGIN

-- Raise an error with a custom message and

severity

RAISERROR ('Salary must be at least 3000!', 16,

1);

END

-- Insert employee if salary is valid

INSERT INTO Employees (EmployeeID, FirstName,

LastName, Department, Salary)

VALUES (@EmployeeID, @FirstName, @LastName,

@Department, @Salary);

END TRY

BEGIN CATCH

-- Catch any other errors

PRINT 'An error occurred: ' + ERROR_MESSAGE();

END CATCH

END;

Explanation:

1.​RAISERROR Function:
○​ Message: 'Salary must be at least 3000!' is the
custom message.

34
 ○​ Severity: 16 indicates a general error that can be
corrected by the user.
○​ State: 1 is the error state (you can use different
values, but 1 is commonly used).
2.​Salary Check: If the salary is below 3000, we raise the
custom error.
3.​Error Handling: If the salary is valid, the employee is
inserted into the Employees table. Otherwise, the
RAISERROR stops execution and triggers the CATCH block.

Step 2: Test the Stored Procedure

Test Case 1: Salary Below 3000

EXEC InsertEmployeeWithSalaryCheck 101, 'John', 'Doe', 'IT',

2500;

The insert operation fails because the salary is below the defined threshold, and
the custom error message is shown.

Test Case 2: Salary Above 3000

35
EXEC InsertEmployeeWithSalaryCheck 102, 'Jane', 'Smith', 'HR',
4000;

Expected Result:

The employee is successfully inserted into the Employees table as the salary is
valid.

[Link] of system defined exception

In SQL Server, system-defined exceptions are automatically generated by the

database engine in response to certain error conditions. These exceptions include
errors like primary key violations, foreign key violations, constraint violations,
division by zero, and many others.

You can handle these exceptions using the TRY...CATCH block, where the
system-generated error is captured in the CATCH block using system-defined
functions such as ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE(),
and ERROR_LINE().

Example: Handling System-Defined Exceptions with TRY...CATCH

Scenario: We’ll simulate two system-defined exceptions:

1.​ Division by zero (which is a system-defined exception).

2.​ Primary key violation (when attempting to insert a duplicate value into a
column with a unique constraint).

Step1:Create a Sample Table

CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName
NVARCHAR(50), LastName NVARCHAR(50), Department NVARCHAR(50),
Salary DECIMAL(10, 2) );

Step2: Handling Division by Zero (System-Defined Exception)

We will use a division by zero error to simulate a common system-defined

exception.

BEGIN TRY

-- Attempt division by zero (system-defined exception)

DECLARE @Result INT;

SET @Result = 10 / 0; -- Division by zero error

36
 PRINT 'Result: ' + CAST(@Result AS NVARCHAR(50));

END TRY

BEGIN CATCH

-- Handle the error

PRINT 'An error occurred!';

PRINT 'Error Message: ' + ERROR_MESSAGE(); --

System-generated message

PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS

NVARCHAR(50)); -- Severity level

PRINT 'Error Line: ' + CAST(ERROR_LINE() AS NVARCHAR(50));

-- Line number where error occurred

END CATCH;

Step 3: Handling Primary Key Violation (System-Defined

Exception)

We will simulate a primary key violation by attempting to

insert a duplicate EmployeeID.

BEGIN TRY

37
 -- Insert employee (This will succeed)

INSERT INTO Employees (EmployeeID, FirstName, LastName,

Department, Salary)

VALUES (1, 'John', 'Doe', 'HR', 5000);

-- Attempt to insert another employee with the same

EmployeeID (Primary key violation)

INSERT INTO Employees (EmployeeID, FirstName, LastName,

Department, Salary)

VALUES (1, 'Jane', 'Smith', 'IT', 6000); -- This will

cause a primary key violation

END TRY

BEGIN CATCH

-- Handle the error

PRINT 'An error occurred!';

PRINT 'Error Message: ' + ERROR_MESSAGE(); --

System-generated message

PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS

NVARCHAR(50)); -- Severity level

PRINT 'Error Line: ' + CAST(ERROR_LINE() AS NVARCHAR(50));

-- Line number where error occurred

END CATCH;

38
In this case, SQL Server automatically generates a primary key
violation error when trying to insert a duplicate EmployeeID.

Explanation of System-Defined Exceptions:

1.​Division by Zero:
○​ SQL Server automatically raises a system-defined
exception for division by zero (10 / 0 in this
example).
○​ The error message will be "Divide by zero error
encountered.", and the severity will be 16 (a
typical user error).
2.​Primary Key Violation:
○​ When you attempt to insert a duplicate value into a
column with a PRIMARY KEY constraint, SQL Server
raises a primary key violation.
○​ The error message will mention "Violation of PRIMARY
KEY constraint" and give details of the duplicate
key.
○​ The severity level for this error is 14 (related to
user data errors).

System-Defined Functions for Error Handling:

39
 ●​ ERROR_MESSAGE(): Returns the error message text.
●​ ERROR_SEVERITY(): Returns the error severity level.
●​ ERROR_STATE(): Returns the error state number.
●​ ERROR_LINE(): Returns the line number where the error
occurred.
●​ ERROR_PROCEDURE(): Returns the name of the procedure or
trigger that caused the error (if applicable).

5. Creation of Package in PL/SQL

What Are Packages in SQL Server?

In SQL Server, a Package is not a built-in concept as it is in Oracle PL/SQL, where

a package consists of two parts:

1.​ Specification: Defines the public interface (declarations of procedures,

functions, and variables).
2.​ Body: Contains the actual code for the procedures, functions, and other
elements.

However, in SQL Server, we don’t have the same concept of packages as in Oracle.
But you can achieve a similar result using schemas, stored procedures,
functions, and views within the same schema.

What SQL Server Offers Instead of Packages:

1.​ Schemas: These can be used to group related database objects (tables,
views, functions, stored procedures, etc.).
2.​ Stored Procedures: To group logic and queries into reusable blocks of code.
3.​ Functions: To create reusable functions for calculations and transformations.
4.​ Views: To encapsulate complex queries.

You can think of schemas and stored procedures as the way SQL Server achieves
packaging logic and objects together.

Creating a Schema in SQL Server (Analogous to a Package)

A schema is a container for database objects. It’s a logical grouping of related

objects.

Step 1: Create a Schema

CREATE SCHEMA HR;

Step 2: Create a Stored Procedure within the Schema

40
CREATE PROCEDURE [Link]

@EmployeeID INT

AS

BEGIN

SELECT EmployeeID, FirstName, LastName, Department, Salary

FROM Employees

WHERE EmployeeID = @EmployeeID;

END;

Step 3: Create a Function within the Schema

CREATE FUNCTION [Link] (@EmployeeID INT)

RETURNS DECIMAL(10, 2)

AS

BEGIN

DECLARE @Salary DECIMAL(10, 2);

SELECT @Salary = Salary

FROM Employees

WHERE EmployeeID = @EmployeeID;

RETURN @Salary;

END;

Step 4: Call Procedures and Functions within the Schema

EXEC [Link] 101;

41
-- Calling the function from the HR schema

SELECT [Link](101);

Stored Procedures & Functions – Grouped Together in a Schema

By grouping stored procedures and functions under the same schema, you can
effectively create a package-like structure.

Example: "HR" Package in SQL Server

1.​ Create a schema for the package (HR in this case).

2.​ Create stored procedures and functions within that schema to encapsulate
HR-related logic.
3.​ Invoke the procedures/functions using the schema name
([Link], [Link]).

Key Points:

●​ Schemas are used to logically group related objects (tables, views, functions,
and procedures).
●​ Stored Procedures and Functions are the main components of SQL Server
that serve a similar purpose to the package body in Oracle.
●​ Unlike Oracle, SQL Server does not have the PACKAGE and PACKAGE BODY
structure. Instead, we organize code in schemas.

42