17. PL/SQL Packages | PL/SQL Developer Course | TechView Team | Hindi
#techviewteam #plsql
In this video we discuss about oracle PL/SQL Packages practical example.
This is 17 th video from series of videos about PL/SQL. so, you are requested to watch complete playlist for better understanding.
---video notes start from here-----
create or replace PACKAGE customer_credit AS
PROCEDURE find_credit(c_id customers.customer_id%type);
END customer_credit;
create or replace PACKAGE BODY customer_credit AS
PROCEDURE find_credit(c_id customers.customer_id%TYPE) IS
c_credit customers.credit_limit%TYPE;
BEGIN
SELECT credit_limit INTO c_credit
FROM customers
WHERE customer_id = c_id;
dbms_output.put_line('Credit Limit: '|| c_credit);
END find_credit;
END customer_credit;
-------USING PACKAGE----
DECLARE
c_id customers.customer_id%type := &cc_id;
BEGIN
customer_credit.find_credit(c_id);
END;
/
----------------------
create or replace PACKAGE pkg_customer AS
PROCEDURE find_credit(c_id customers.customer_id%type);
-- Adds a customer
PROCEDURE addCustomer(
c_name customers.Name%type,
c_addr customers.address%type,
c_website customers.website%type,
c_email customers.email_id%type,
c_credit customers.credit_limit%type);
-- Removes a customer
PROCEDURE delCustomer(c_id customers.customer_id%TYPE);
-- Removes a customer
PROCEDURE getCustomer(c_id customers.customer_id%TYPE);
--Lists all customers
PROCEDURE listCustomer;
END pkg_customer;
/
--body---
create or replace PACKAGE BODY pkg_customer AS
PROCEDURE find_credit(c_id customers.customer_id%TYPE) IS
c_credit customers.credit_limit%TYPE;
BEGIN
SELECT credit_limit INTO c_credit
FROM customers
WHERE customer_id = c_id;
dbms_output.put_line('Credit Limit: '|| c_credit);
END find_credit;
--add customer
PROCEDURE addCustomer(
c_name customers.Name%type,
c_addr customers.address%type,
c_website customers.website%type,
c_email customers.email_id%type,
c_credit customers.credit_limit%type)
IS
BEGIN
INSERT INTO customers (name,address,website,email_id,credit_limit)
VALUES(c_name, c_addr, c_website, c_email, c_credit);
END addCustomer;
----del customer
PROCEDURE delCustomer(c_id customers.customer_id%type) IS
BEGIN
DELETE FROM customers
WHERE customer_id = c_id;
END delCustomer;
--list single customer
PROCEDURE getCustomer
(c_id customers.customer_id%type)
IS
LV_NAME VARCHAR2(100);
BEGIN
SELECT name INTO LV_NAME FROM CUSTOMERS
WHERE CUSTOMER_ID = c_id;
DBMS_OUTPUT.PUT_LINE('Customer name:' || LV_NAME);
END getCustomer;
---list all customer
PROCEDURE listCustomer IS
c_id varchar2(20);
c_name varchar2(200);
c_address varchar2(400);
CURSOR c_customers is
SELECT customer_id, name, address FROM customers ;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_address;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_address);
END LOOP;
--CLOSE c_customers;
END listCustomer ;
END pkg_customer;
----------
-----USAGE----------
DECLARE
c_id customers.customer_id%type:= 322;
BEGIN
-- pkg_customer.find_credit(c_id);
--pkg_customer.addcustomer( 'TechView Enterprises', 'TechView Team ,India', 'www.techviewteam.com', '[email protected]',3500);
-- pkg_customer.addcustomer( 'TechView Team', 'TechView Team India', 'www.techviewteam.com','[email protected]', 7500);
-- pkg_customer.getcustomer(c_id);
--pkg_customer.listcustomer;
--pkg_customer.delcustomer(c_id);
-- pkg_customer.listcustomer;
END;
/
----------------Video notes ends here-----
Regards
TechView Team