EX-8 PROCEDURE AND FUCTIONS
---------------------------
PROCEDURES
----------
1. CREATE A PROCEDURE TO ADD DETAILS INTO BRAND
-----------------------------------------------
SQL
---
create or replace procedure addBrand(
b_brandId in number,
b_brandName in varchar2,
b_rating in float,
b_slogan in varchar2) is
begin
insert into brand (brandid, brandname, rating, slogan)
values (b_brandId, b_brandName, b_rating, b_slogan);
end;
/
OUTPUT
------
SQL> execute addBrand(127, 'maze', 4.0, 'run');
PL/SQL procedure successfully completed.
SQL> select * from brand where brandId = 127
2 ;
BRANDID BRANDNAME RATING
---------- ------------------------------ ----------
127 maze 4
2. CREATE A PROCEDURE TO UPDATE PRICE OF PRODUCT
------------------------------------------------
SQL
---
create or replace procedure updatePrice(
p_productId in number,
p_productPrice in number) is
begin
update product set price = p_productPrice
where productId = p_productId;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Update successfully');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
end;
/
OUTPUT
------
Price can only be increased, not decreased.
SQL> select productId, price from product where productId = 132;
PRODUCTID PRICE
---------- ----------
132 3000
SQL> execute updatePrice(132, 3500);
Update successfully
PL/SQL procedure successfully completed.
SQL> select productId, price from product where productId = 132;
PRODUCTID PRICE
---------- ----------
132 3500
3. CREATE PROCEDURE TO DELETE A BRAND
-------------------------------------
SQL
---
create or replace procedure deleteBrand(
b_brandId in number) is
begin
delete from brand where brandId = b_brandId;
commit;
DBMS_OUTPUT.PUT_LINE('Update successfully');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
end;
/
RESULT
------
SQL> execute deleteBrand(947);
Delete successful
PL/SQL procedure successfully completed.
SQL> select * from brand where brandid = 947;
no rows selected
FUNCTIONS
---------
1. CALCULATE TOTAL NO OF PRODUCT WITH CATEGORY
----------------------------------------------
SQL
---
create or replace function getProductCategory(
p_categoryId in number)
return number
is
v_pc number;
begin
select count(*) into v_pc from product
where categoryId = p_categoryId;
return v_pc;
end;
/
RESULT
------
SQL> SELECT getProductCategory(2000) AS category_count FROM dual;
CATEGORY_COUNT
--------------
5
SQL> select count(*) as count from product where categoryid = 2000;
COUNT
----------
5
2. FUNCTION TO GET AVG RATING OF BRAND
--------------------------------------
SQL
---
create or replace function getRatingBrand(
p_brandId in number)
return number
is
v_rating number;
begin
select avg(rating) into v_rating from product
where brandId = p_brandId;
return v_rating;
end;
/
RESULT
------
SQL> SELECT getRatingBrand(920) AS AvgRating FROM dual;
AVGRATING
----------
4.75
SQL> select avg(rating) as avg from product where brandid = 920;
AVG
----------
4.75
3. FUNCTION TO GENERATE FIBONACCI SERIES
----------------------------------------
SQL
---
CREATE OR REPLACE FUNCTION fibonacci(
n IN NUMBER)
RETURN VARCHAR2
IS
fib_series VARCHAR2(9000);
a NUMBER := 0;
b NUMBER := 1;
temp NUMBER;
BEGIN
fib_series := '0';
IF n = 1 THEN
RETURN fib_series;
END IF;
FOR i IN 2 .. n LOOP
fib_series := fib_series || ',' || b;
temp := b;
b := a + b;
a := temp;
END LOOP;
RETURN fib_series;
END fibonacci;
/
OUTPUT
------
SQL> SELECT fibonacci(10) AS fibonacci_series FROM dual;
FIBONACCI_SERIES
---------------------------------------------------
0,1,1,2,3,5,8,13,21,34
4. FUNCTION THAT USES AN IMPLICIT CURSOR TO CALCULATE TOTAL QUANTITY
OF PRODUCT IN A GIVE CATEGORY
---------------------------------------------------------------------
SQL
---
CREATE OR REPLACE FUNCTION getTotalQuantity(
p_categoryId IN NUMBER
)
RETURN NUMBER
IS
v_total_quantity NUMBER := 0;
BEGIN
FOR product_rec IN (
SELECT quantity
FROM product
WHERE categoryId = p_categoryId
)
LOOP
v_total_quantity := v_total_quantity + product_rec.quantity;
END LOOP;
RETURN v_total_quantity;
END getTotalQuantity;
/
OUTPUT
------
SQL> SELECT getTotalQuantity(2000) AS TotalQuantity FROM dual;
TOTALQUANTITY
-------------
45
5. FUNCTION TO RETURN DETAILS OF BRAND
--------------------------------------
SQL
---
CREATE OR REPLACE FUNCTION getBrandDetails(
brandId IN NUMBER
)
RETURN brand % ROWTYPE
IS
brandDetails brand % ROWTYPE;
BEGIN
select * into brandDetails
from brand
where brandId = [Link];
return brandDetails;
END;
/
OUTPUT
------
SQL> DECLARE
2 brandDetailsVar brand%ROWTYPE;
3 BEGIN
4 brandDetailsVar := getBrandDetails(920);
5 DBMS_OUTPUT.PUT_LINE('Brand Name: ' || [Link]);
6 END;
7 /
Brand Name: Samsung
PL/SQL procedure successfully completed.
6. FUNCTION TO CALCULATE TOTAL PRICE OF ORDER
---------------------------------------------
SQL
---
CREATE OR REPLACE FUNCTION calculateOrderPrice(
p_custId IN NUMBER
)
RETURN NUMBER
IS
totalPrice NUMBER;
BEGIN
select sum(totalPrice) into totalPrice from orders
where customerId = p_custId;
return totalPrice;
END;
/
OUTPUT
------
SQL> SELECT calculateOrderPrice(2) AS totalPrice FROM dual;
TOTALPRICE
----------
135000
SQL> SELECT SUM(TOTALPRICE) FROM ORDERS WHERE CUSTOMERID = 2;
SUM(TOTALPRICE)
---------------
135000
8. USING EXPLICIT CURSOR RETUEN BRAND WITH RATING > 4.2
-------------------------------------------------------
SQL
---
CREATE OR REPLACE FUNCTION getHighRatingBrand
RETURN SYS_REFCURSOR
IS
brandCus SYS_REFCURSOR;
BEGIN
OPEN brandCus FOR
SELECT * FROM brand WHERE rating > 4.2;
RETURN brandCus;
END;
/
OUTPUT
------
SQL> VAR rc REFCURSOR;
SQL> BEGIN
2 :rc := getHighRatingBrand();
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> PRINT rc;
BRANDID BRANDNAME RATING
---------- ------------------------------ ----------
SLOGAN
------------------------------
920 Samsung 4.7
Imagine
924 Sony 4.3
Be Moved
925 LG 4.8
Lifes Good
BRANDID BRANDNAME RATING
---------- ------------------------------ ----------
SLOGAN
------------------------------
928 Gucci 4.7
Quality is remembered
931 Intel 4.9
Experience Whats Inside
926 Huawei 4.6
Make It Possible
BRANDID BRANDNAME RATING
---------- ------------------------------ ----------
SLOGAN
------------------------------
929 Canon 4.4
Delighting You Always
932 Nestle 4.6
Good Food, Good Life
1234 NIKE 4.8
Just Do It
9. FUNCTION TO RETRIEVE A TABLE
--------------------------------
SQL
---
CREATE OR REPLACE FUNCTION getBrandTable
RETURN brand_table_type PIPELINED
IS
v_brand_record brand_record_type; -- Declare variable to hold brand record
CURSOR c_brands IS
SELECT brandId, brandName, rating
FROM brand;
BEGIN
FOR brand_rec IN c_brands LOOP
-- Populate brand record
v_brand_record := brand_record_type(brand_rec.brandId, brand_rec.brandName,
brand_rec.rating);
-- Pipe brand record out of the function
PIPE ROW(v_brand_record);
END LOOP;
RETURN;
END;
/
OUTPUT
------
SQL> SELECT * FROM TABLE(getBrandTable);
BRANDID
----------
BRANDNAME
--------------------------------------------------------------------------------
RATING
----------
920
Samsung
4.7
924
Sony
4.3
BRANDID
----------
BRANDNAME
--------------------------------------------------------------------------------
RATING
----------
925
LG
4.8
928
Gucci
BRANDID
----------
BRANDNAME
--------------------------------------------------------------------------------
RATING
----------
4.7
931
Intel
4.9
926
BRANDID
----------
BRANDNAME
--------------------------------------------------------------------------------
RATING
----------
Huawei
4.6
927
Philips
4.2
10. FUNCTION USED TO RETRIEVE CUSTOMERNAME
------------------------------------------
SQL
---
CREATE OR REPLACE FUNCTION GetCustomerName (
p_CustomerID IN NUMBER,
p_CustomerName OUT VARCHAR2
) RETURN VARCHAR2
AS
BEGIN
SELECT firstName INTO p_CustomerName
FROM person
WHERE userId = (SELECT userId FROM customer WHERE customerID = p_CustomerID);
RETURN p_CustomerName;
END;
/
OUTPUT
------
SQL> DECLARE
2 v_CustomerID NUMBER := 1562;
3 v_CustomerName VARCHAR2(100);
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE('Customer Name: ' || GetCustomerName(v_CustomerID,
v_CustomerName));
6 END;
7 /
Customer Name: Krishna
PL/SQL procedure successfully completed.