14. PL/SQL RECORDS | PL/SQL Developer Course | TechView Team | Hindi
#techviewteam #plsql
In this video we discuss about oracle PL/SQL Records with practical example.
This is 14th video from series of videos about PL/SQL. so, you are requested to watch complete playlist for better understanding.
---video notes start from here-----
PL/SQL can handle the following types of records −
Table-based
DECLARE
record_name table_name%ROWTYPE;
Cursor-based records
DECLARE
CURSOR c_contacts IS
SELECT first_name, last_name, phone
FROM contacts;
r_contact c_contacts%ROWTYPE;
User-defined records
DECLARE
-- define a record type
TYPE r_customer_contact_t
IS
RECORD
(
customer_name customers.name%TYPE,
first_name contacts.first_name%TYPE,
last_name contacts.last_name%TYPE );
-- declare a record
r_customer_contacts r_customer_contact_t;
BEGIN
NULL;
END;
-------TABLE BASED---------------
DECLARE
customer_rec customers%rowtype;
BEGIN
SELECT * into customer_rec
FROM customers
WHERE id = 5;
dbms_output.put_line('Customer ID: ' || customer_rec.id);
dbms_output.put_line('Customer Name: ' || customer_rec.name);
dbms_output.put_line('Customer Address: ' || customer_rec.address);
END;
/
-----------
----CURSOR BASED------
DECLARE
CURSOR c_customers is
SELECT customer_id, name, address FROM customers
ORDER BY CUSTOMER_ID;
CUSTOMER_REC C_CUSTOMERS%ROWTYPE;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into CUSTOMER_REC;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(CUSTOMER_REC.customer_id || ' ' ||CUSTOMER_REC.name || ' ' || CUSTOMER_REC.addrESS);
END LOOP;
--CLOSE c_customers;
END;
/
-----------------
---User-Defined Records---------
DECLARE
TYPE R_CUSTOMER IS RECORD
( CUSTOMER_ID NUMBER,
NAME VARCHAR2(200),
ADDRESS VARCHAR(200)
);
IN_CUSTOMER R_CUSTOMER;
BEGIN
IN_CUSTOMER.CUSTOMER_ID:=423;
in_customer.NAME:='abc';
in_customer.ADDRESS:='JAIPUR, RAJASTHAN';
DBMS_OUTPUT.PUT_LINE(IN_CUSTOMER.CUSTOMER_ID||' '||in_customer.NAME ||' '||in_customer.ADDRESS);
END;
------------------
Regards
TechView Team