DBMS Unit V

 

(PL/SQL)

PROCEDURAL LANGUAGE / STRUCTURED QUERY LANGUAGE

---

1) Explain about the structure of PL/SQL program. How to create and execute a PL/SQL program?

a) PL/SQL

• PL/SQL stands for Procedural Language / Structured Query Language.
• It is an extension of SQL.
• It contains SQL commands along with block of statements, declarations, control statements, operations, procedures, functions and exception handling.

Steps to create & execute PL/SQL program

1. Double click on SQL icon. It will display the dialogue box window.
2. Type the username and password, then click on Connect.
3. It will display the SQL prompt to the user.
4. Type ed or edit command; it displays Notepad editor.
5. Type the program.
6. Save the program with an extension of .sql.
7. Exit from the editor.
8. Execute the program using:

@filename.sql

Structure of PL/SQL

A PL/SQL program may contain different sections. It contains:

• Declarations
• Functions
• Procedures
• Exceptions
• Trigger
• SQL statements

It is represented as follows:

DECLARE
    variable declaration;
BEGIN
    statement;
EXCEPTION
    statement;
END;
/

Example

DECLARE
    a NUMBER;
    b NUMBER;
    c NUMBER;
BEGIN
    a := 10;
    b := 20;
    c := a + b;
    DBMS_OUTPUT.PUT_LINE(c);
END;
/

---

2) Write about Function in PL/SQL ?

• A function is a sub-program and it performs a specific task.
• It is used to increase the clarity of the program.
• It can reduce the code of the program.
• It is possible to create user-defined functions.
• It can be created by using the CREATE OR REPLACE command.

Syntax

CREATE OR REPLACE FUNCTION functionname(argument)
RETURN datatype
IS
    variable declaration;
BEGIN
    statement;
    RETURN value;
END;
/

Example

CREATE OR REPLACE FUNCTION add(a NUMBER, b NUMBER)
RETURN NUMBER
IS
    c NUMBER;
BEGIN
    c := a + b;
    RETURN c;
END;
/

The function can be executed by using the SELECT command.

SELECT add(10,20) FROM dual;

Output:

30

---

3) Write about Stored Procedures in PL/SQL?

• A procedure is a sub-program and it performs a specific task.
• It is used to represent the clarity of the program.
• It can reduce the code of the program.
• It is also possible to create user-defined procedures in PL/SQL.
• It does not return a value in the program.
• It can be created by using the CREATE OR REPLACE PROCEDURE command.

Syntax

CREATE OR REPLACE PROCEDURE procedure_name
IS
BEGIN
    statements;
END;
/

Example

CREATE OR REPLACE PROCEDURE add(a IN NUMBER, b IN NUMBER)
IS
    c NUMBER;
BEGIN
    c := a + b;
    DBMS_OUTPUT.PUT_LINE(c);
END;
/

It can be executed by using the EXEC command:

EXEC procedure_name(argument);

Example:

EXEC add(10,20);

Output:

30

---

4) Write about Operators in SQL?

To perform an operation between any two operands, it is called an operator.

SQL contains various types of operators:

1. Arithmetic operators
2. Relational operators
3. Logical operators
4. Comparison operators
5. Assignment operators

---

Arithmetic Operators

These are used to perform arithmetic operations.

Operator	Meaning	Example
+	Addition	a+b
-	Subtraction	a-b
*	Multiplication	a*b
/	Division	a/b

---

Relational Operators

These are used to perform conditions. It returns either TRUE or FALSE.

Operator	Meaning	Example
<	Less than	a<b
>	Greater than	a>b
>=	Greater than or equal	a>=b
<=	Less than or equal	a<=b
=	Equal	a=b
!=	Not equal	a!=b

---

Logical Operators

These are used to combine two or more conditional expressions. They return either TRUE or FALSE.

AND

It returns TRUE if all the conditions are true.

OR

It returns TRUE if any one of the conditions is true.

NOT

It returns TRUE if the condition is false.

---

Comparison Operators

These are used to display statements based on user requirements.

LIKE

It is used to display the statements based on user requirements.

SELECT * FROM emp
WHERE ename LIKE 'A%';

IN

It is used to display the statements based on user requirements.

SELECT * FROM emp
WHERE deptno IN (10,30);

BETWEEN

It is used to display the statement based on user requirements.

SELECT * FROM emp
WHERE deptno BETWEEN 10 AND 30;

---

Assignment Operator

It is used to assign the value to an expression.

It is denoted by the := operator.

Example:

a := 10;
b := 20;
c := a + b;

---

5) Write about Conditional and Branching 

Statements in PL/SQL?

• The statements are executed based on the condition.
• It contains IF and CASE statements.

There are different types of IF statements in PL/SQL:

1. Simple IF
2. Nested IF
3. IF-ELSE
4. ELSE-IF ladder

---

Simple IF

In this functionality, the statements are executed only if the condition is true.

Syntax

IF condition THEN
    statements;
END IF;

Example

IF n > 0 THEN
    DBMS_OUTPUT.PUT_LINE('zero');
END IF;

---

Nested IF

It is also called a compound IF or multiple IF statement, i.e., one IF statement contains 

another IF statement.

Syntax

IF condition THEN
    IF condition THEN
        statements;
    END IF;
END IF;

Example

IF n > 0 THEN
    IF n > 10 THEN
        DBMS_OUTPUT.PUT_LINE('zero');
    END IF;
END IF;

---

IF-ELSE

It contains two blocks. The first block is called the true block and the second block is called

the false block.

Syntax

IF condition THEN
    statements;
ELSE
    statements;
END IF;

Example

IF n > 0 THEN
    DBMS_OUTPUT.PUT_LINE('zero');
ELSE
    DBMS_OUTPUT.PUT_LINE('negative');
END IF;

---

ELSE-IF Ladder

In this functionality, the ELSE block contains another IF statement.

Syntax

IF condition THEN
    statements;
ELSIF condition THEN
    statements;
ELSE
    statements;
END IF;

---

CASE Statement

It is a multi-way branching statement.

It depends on the value of the statement being executed.

Syntax

CASE variable
    WHEN value THEN
        statements;
    WHEN value THEN
        statements;
    ...
    ELSE
        statements;
END CASE;

Example

CASE n
    WHEN 0 THEN
        DBMS_OUTPUT.PUT_LINE('zero');
    WHEN 1 THEN
        DBMS_OUTPUT.PUT_LINE('one');
END CASE;

---

6) Write about Looping Statements in PL/SQL?

Looping statements are used to execute statements up to a finite number of times.

It is called as repetition.

It can be divided into two types:

1. Entry control loop
2. Simple loop

---

Entry Control Loop

In this functionality, first it checks the condition. After that, the block is executed 

up to a finite number of times.

It contains FOR and WHILE statements.

FOR Loop

Syntax

FOR var IN val1..val2
LOOP
    statements;
END LOOP;

Example

FOR i IN 1..10
LOOP
    DBMS_OUTPUT.PUT_LINE(i);
END LOOP;

WHILE Loop

Syntax

WHILE condition
LOOP
    statements;
END LOOP;

Example

i := 1;
WHILE i <= 10
LOOP
    DBMS_OUTPUT.PUT_LINE(i);
    i := i + 1;
END LOOP;

---

Simple Loop

In this functionality, the statement is enclosed between LOOP and END LOOP.

Syntax

LOOP
    statements;
END LOOP;

Example

LOOP
    DBMS_OUTPUT.PUT_LINE('Hello');
END LOOP;

---

7) Write about Wild Characters in SQL?

Wild characters are used to display statements based on user requirements. 

These are the special characters in SQL.

These operators can be performed through LIKE operator.

Symbols

• % – underscore / wildcard pattern for any number of characters
• _ – used to display statements based on one character

Example using %

SELECT * FROM emp
WHERE ename LIKE 'S%';

This displays the employee details whose name must start with S and after 

that any number of characters can occur.

Example using _

SELECT * FROM emp
WHERE ename LIKE 'S_';

This displays employee details where the name contains the specified number of 

characters according to the pattern.

---

8) Write about Data Types in SQL?

Data types are used to represent the type of data. A range of data types are supported in PL/SQL.

Various data types for creating variables and columns are:

i) CHAR

• It is a fixed-length character data type.
• The maximum size is 2000 bytes and default size is 1 byte.

ii) VARCHAR2

• It is a variable-length character data type.
• The maximum size is up to 4000 bytes.

iii) NUMBER

• It is used to represent numeric data.
• The maximum precision is up to 38.

iv) DATE

• It is used for date and time.
• It contains 7 bytes.

v) BLOB

• It is an unstructured binary data type.
• The maximum size is 4GB.

vi) RAW

• It is a variable-length binary data type.
• The size is up to 2000 bytes.

Example

DECLARE
    a NUMBER;
    b NUMBER;
    c NUMBER;
BEGIN
    a := 10;
    b := 20;
    c := a + b;
    DBMS_OUTPUT.PUT_LINE(c);
END;
/

Create Student Table

CREATE TABLE student
(
    sno NUMBER,
    sname VARCHAR2(20),
    m1 NUMBER,
    m2 NUMBER,
    m3 NUMBER,
    total NUMBER
);

---

9) Write about Triggers in PL/SQL?

• A database trigger is a stored trigger.
• It is a statement of the system that executes automatically when there is any modification to the database.
• In a trigger, we first specify when the trigger is to be executed.
• It is executed by following events.

Events

• DDL operation
• DML operation

Types of Triggers

There are different types of triggers in SQL. They are:

1. Before Trigger
2. After Trigger
3. Row Trigger
4. Statement Trigger

---

Before Trigger

It is executed before the trigger DML commands like:

• INSERT
• DELETE
• UPDATE

After Trigger

It is executed after the DML commands like:

• INSERT
• DELETE
• UPDATE

Row Trigger

It is executed for each and every row which is performing INSERT, DELETE or UPDATE commands.

Statement Trigger

It is executed only once for each statement.

---

Advantages of Triggers

• It avoids invalid transactions.
• It provides a method to check the data integrity of the database.
• It allows you to execute the query only once when required.
• It helps to automate the data alterations.
• It allows easy auditing of data.
• It finds errors at the database level.

Syntax

CREATE OR REPLACE TRIGGER triggername
BEFORE/AFTER (INSERT/DELETE/UPDATE)
ON tablename
FOR EACH ROW
BEGIN
    statements;
END;
/

Example

CREATE OR REPLACE TRIGGER t
AFTER INSERT ON student
FOR EACH ROW
BEGIN
    UPDATE student
    SET total = m1 + m2 + m3;
END;
/

The DROP command is used to delete the trigger from the database.

DROP TRIGGER triggername;

Example:

DROP TRIGGER t;

---

10) Write about Operator Precedence in SQL?

• Operator precedence is used when an expression is evaluated.
• Every operator has a precedence.

The list of precedence is represented as follows:

1. () – Parentheses
2. * / – Multiplication and Division
3. + - – Addition and Subtraction
4. = != <> – Relational operators
5. IS NULL, IS NOT NULL
6. BETWEEN
7. NOT
8. AND
9. OR

Example

6 + 7 * 2

Here, * has higher precedence than +.

So first:

7 * 2 = 14

Then:

6 + 14 = 20

Therefore, the result is:

20