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 );
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—
Statement processed.
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-----
Statement processed.
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
