Scribd
Upload
7 views2 pages

PL SQL

The document provides SQL examples demonstrating the creation and use of views, functions, procedures, and triggers. It includes code snippets for each SQL concept, showcasing how to create a view for customer details, a function to find the maximum of two numbers, a procedure to find the minimum, and a trigger to display salary changes. Each example is accompanied by the expected output when executed in an SQL prompt.

Uploaded by

Cheryl Prithika
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
7 views2 pages

PL SQL

The document provides SQL examples demonstrating the creation and use of views, functions, procedures, and triggers. It includes code snippets for each SQL concept, showcasing how to create a view for customer details, a function to find the maximum of two numbers, a procedure to find the minimum, and a trigger to display salary changes. Each example is accompanied by the expected output when executed in an SQL prompt.

Uploaded by

Cheryl Prithika
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd

3. Write a program to explain views in SQL.

SQL> create view customer_details as select id,name,age from customer;


View created.

SQL> select * from customer_details;


ID NAME AGE
---------- ------------ ----------
1 Ramesh 32
2 Khilan 25
3 Kaushik 23
4 Chaitali 25
5 Hardik 27
6 Komal 22
6 rows selected.

4.Write a program to explain functions in SQL.


Steps: Write the below code in Notepad and then copy and paste it in SQL prompt.
DECLARE
a number;
b number;
c number;
FUNCTION findMax(x IN number, y IN number)
RETURN number
IS
z number;
BEGIN
IF x > y THEN
z:= x;
ELSE
Z:= y;
END IF;
RETURN z;
END;
BEGIN
a:=100;
b:= 15;
c := findMax(a, b);
dbms_output.put_line(' Maximum of (100,15): ' || c);
END;
/
Sql> SET SERVEROUTPUT ON;

When the above code is executed at the SQL prompt, it produces the
following result
Maximum of (23, 45) : 45

PL/SQL procedure successfully completed.


5. Write a program to explain procedures in SQL.

DECLARE
a number;
b number;
c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS
BEGIN
IF x < y THEN
z:= x;
ELSE
z:= y;
END IF;
END;
BEGIN
a:= 23;
b:= 45;
findMin(a, b, c);
dbms_output.put_line(' Minimum of (23, 45) : ' || c);
END;
/

When the above code is executed at the SQL prompt, it produces the
following result −
Minimum of (23, 45) : 23

PL/SQL procedure successfully completed.

6. Write a program to explain triggers in SQL

CREATE OR REPLACE TRIGGER display_salary_changes


BEFORE DELETE OR INSERT OR UPDATE ON customer
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.salary - :OLD.salary;
dbms_output.put_line('Old salary: ' || :OLD.salary);
dbms_output.put_line('New salary: ' || :NEW.salary);
dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/
When the above code is executed at the SQL prompt, it produces the following result
Trigger created.

You might also like