PL SQL

CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );
VALUES (6, 'Komal', 22, 'MP', 4500.00 );
Function:-
CREATE OR REPLACE FUNCTION totalCustomers
RETURN number IS
total number(2) := 0;
BEGIN
SELECT count(*) into total
FROM customers;
RETURN total;
END;
DECLARE
c number(2);
BEGIN
c := totalCustomers();
dbms_output.put_line('Total no. of Customers: ' || c);
END;
Procedures:-
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;
/
Example 2----
DECLARE
c_id int;
c_name customers.name%TYPE;
c_addr customers.address%TYPE;
c_sal customers.salary%TYPE;
PROCEDURE details(c_id IN INT)
AS
BEGIN
SELECT name, address, salary INTO c_name, c_addr, c_sal
FROM customers
WHERE id = c_id;
dbms_output.put_line
('Customer ' ||c_name || ' from ' || c_addr || ' earns ' || c_sal);
END;
BEGIN
c_id:= 5;
details(c_id);
END;
Output—
Statement processed.
Customer Hardik from Bhopal earns 8500
Trigers:
CREATE OR REPLACE TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON customers
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;
UPDATE customers
SET salary = salary + 500
WHERE id = 2;
output
1 row(s) updated.
Old salary: 2500
New salary: 3000
Salary difference: 500
Cursors:-
DECLARE
c_id customers.id%type;
c_name customers.name%type;
c_addr customers.address%type;
CURSOR c_customers is
SELECT id, name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_addr;

EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customers;
END;
Output—
1 Ramesh Ahmedabad
2 Khilan Delhi
3 kaushik Kota
4 Chaitali Mumbai
5 Hardik Bhopal
6 Komal MP
7 Kriti HP
Pakages:-
Specification---
CREATE OR REPLACE PACKAGE c_package AS
PROCEDURE addCustomer(c_id customers.id%type,
c_name customers.Name%type,
c_age customers.age%type,
c_addr customers.address%type,
c_sal customers.salary%type);
PROCEDURE delCustomer(c_id customers.id%TYPE);
END c_package;
Body-----
CREATE OR REPLACE PACKAGE BODY c_package AS
PROCEDURE addCustomer(c_id customers.id%type,
c_name customers.Name%type,
c_age customers.age%type,
c_addr customers.address%type,
c_sal customers.salary%type)
IS
BEGIN
INSERT INTO customers (id,name,age,address,salary)
VALUES(c_id, c_name, c_age, c_addr, c_sal);
END addCustomer;
PROCEDURE delCustomer(c_id customers.id%type) IS
BEGIN
DELETE FROM customers
WHERE id = c_id;
END delCustomer;
END c_package;
DECLARE
code customers.id%type:= 8;
BEGIN
c_package.addcustomer(8, 'Rajnish', 25, 'Chennai', 3500);
c_package.delcustomer(code);
END;
Output-----
Views:-
CREATE VIEW low_sal AS
SELECT *
FROM CUSTOMERS
WHERE SALARY < 4000;
SELECT * FROM rlow_sal;
Output---
I
NAME AGE ADDRESS SALARY
D
1 Ramesh 32 Ahmedabad 2000
2 Khilan 25 Delhi 3000
3 kaushik 23 Kota 2000
Independent or stand alone function
CREATE OR REPLACE FUNCTION calculate_bonus (cust_id IN CUSTOMERS.ID%TYPE)
RETURN DECIMAL
IS
bonus DECIMAL(18,2);
BEGIN
SELECT CASE
WHEN salary <= 2000 THEN salary * 0.05
WHEN salary <= 3000 THEN salary * 0.03
ELSE salary * 0.02
END
INTO bonus
FROM CUSTOMERS
WHERE ID = cust_id;
RETURN bonus;
END calculate_bonus;
/
SELECT ID, NAME, SALARY, calculate_bonus(ID) AS bonus
FROM CUSTOMERS;
ID NAME SALARY BONUS
1 Ramesh3500 70
2 Khilan 3000 90
3 kaushik 2000 100
4 Chaitali 6500 130
5 Hardik 8500 170
6 Komal 4500 90
7 Kriti 7500 150

PL SQL

Uploaded by

Copyright:

Available Formats

PL SQL

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

PL SQL

Uploaded by

Copyright:

Available Formats

CREATE TABLE CUSTOMERS(

ID INT NOT NULL,

NAME VARCHAR (20) NOT NULL,

AGE INT NOT NULL,

ADDRESS CHAR (25),

SALARY DECIMAL (18, 2),

PRIMARY KEY (ID)

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (6, 'Komal', 22, 'MP', 4500.00 );

CREATE OR REPLACE FUNCTION totalCustomers

dbms_output.put_line('Total no. of Customers: ' || c);

PROCEDURE findMin(x IN number, y IN number, z OUT number) IS

dbms_output.put_line(' Minimum of (23, 45) : ' || c);

PROCEDURE details(c_id IN INT)

SELECT name, address, salary INTO c_name, c_addr, c_sal

CREATE OR REPLACE TRIGGER display_salary_changes

BEFORE DELETE OR INSERT OR UPDATE ON customers

FOR EACH ROW

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);

SET salary = salary + 500

SELECT id, name, address FROM customers;

FETCH c_customers into c_id, c_name, c_addr;

dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);

CREATE OR REPLACE PACKAGE c_package AS

PROCEDURE addCustomer(c_id customers.id%type,

PROCEDURE delCustomer(c_id customers.id%TYPE);

CREATE OR REPLACE PACKAGE BODY c_package AS

PROCEDURE addCustomer(c_id customers.id%type,

INSERT INTO customers (id,name,age,address,salary)

VALUES(c_id, c_name, c_age, c_addr, c_sal);

PROCEDURE delCustomer(c_id customers.id%type) IS

DELETE FROM customers

c_package.addcustomer(8, 'Rajnish', 25, 'Chennai', 3500);

CREATE VIEW low_sal AS

WHERE SALARY < 4000;

SELECT * FROM rlow_sal;

Independent or stand alone function

CREATE OR REPLACE FUNCTION calculate_bonus (cust_id IN CUSTOMERS.ID%TYPE)

WHEN salary <= 2000 THEN salary * 0.05

WHEN salary <= 3000 THEN salary * 0.03

ELSE salary * 0.02

SELECT ID, NAME, SALARY, calculate_bonus(ID) AS bonus

ID NAME SALARY BONUS

3 kaushik 2000 100

4 Chaitali 6500 130

5 Hardik 8500 170