1. Create a Nested Table Type
CREATE TYPE employee_type AS OBJECT (
emp_id NUMBER,
emp_name VARCHAR2(50)
);
CREATE TYPE employee_table AS TABLE OF employee_type;
2. Insert Values into a Nested Table
DECLARE
emp_table employee_table;
BEGIN
emp_table := employee_table();
emp_table.EXTEND;
emp_table(1) := employee_type(1, 'Alice');
emp_table.EXTEND;
emp_table(2) := employee_type(2, 'Bob');
END;
3. Create a Table with a Nested Table Column
CREATE TABLE department (
dept_id NUMBER,
dept_name VARCHAR2(50),
employees employee_table
) NESTED TABLE employees STORE AS employees_nt;
4. Insert Rows into the Table with a Nested Table Column
DECLARE
emp_table employee_table;
BEGIN
emp_table := employee_table();
emp_table.EXTEND;
emp_table(1) := employee_type(1, 'Alice');
emp_table.EXTEND;
emp_table(2) := employee_type(2, 'Bob');
INSERT INTO department VALUES (1, 'HR', emp_table);
END;
5. Query Nested Table Data
SELECT dept_id, dept_name, COLUMN_VALUE AS employee
FROM department d, TABLE(d.employees);
6. Update a Nested Table
DECLARE
emp_table employee_table;
BEGIN
SELECT employees INTO emp_table FROM department WHERE dept_id = 1;
emp_table(1).emp_name := 'Alicia';
UPDATE department SET employees = emp_table WHERE dept_id = 1;
END;
7. Delete from a Nested Table
DECLARE
emp_table employee_table;
BEGIN
SELECT employees INTO emp_table FROM department WHERE dept_id = 1;
emp_table.DELETE(1); -- Removes the first employee
UPDATE department SET employees = emp_table WHERE dept_id = 1;
END;
8. Count Employees in a Nested Table
DECLARE
emp_table employee_table;
emp_count NUMBER;
BEGIN
SELECT employees INTO emp_table FROM department WHERE dept_id = 1;
emp_count := emp_table.COUNT;
DBMS_OUTPUT.PUT_LINE('Employee Count: ' || emp_count);
END;
9. Check for Empty Nested Table
DECLARE
emp_table employee_table;
BEGIN
SELECT employees INTO emp_table FROM department WHERE dept_id = 1;
IF emp_table.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('No Employees in this Department');
ELSE
DBMS_OUTPUT.PUT_LINE('Employees exist');
END IF;
END;
10. Loop Through Nested Table
DECLARE
emp_table employee_table;
BEGIN
SELECT employees INTO emp_table FROM department WHERE dept_id = 1;
FOR i IN 1 .. emp_table.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_table(i).emp_id || ', Name: ' || emp_table(i).emp_name);
END LOOP;
END;
11. Create a Nested Table of Numbers
CREATE TYPE number_table AS TABLE OF NUMBER;
12. Insert Values into a Nested Table of Numbers
DECLARE
num_table number_table;
BEGIN
num_table := number_table();
num_table.EXTEND(3);
num_table(1) := 10;
num_table(2) := 20;
num_table(3) := 30;
END;
13. Sum Values in a Nested Table of Numbers
DECLARE
num_table number_table;
total_sum NUMBER := 0;
BEGIN
num_table := number_table(10, 20, 30);
FOR i IN 1 .. num_table.COUNT LOOP
total_sum := total_sum + num_table(i);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total Sum: ' || total_sum);
END;
14. Nested Table with Composite Types
CREATE TYPE address_type AS OBJECT (
street VARCHAR2(50),
city VARCHAR2(50)
);
CREATE TYPE address_table AS TABLE OF address_type;
15. Insert Composite Type into a Nested Table
DECLARE
addr_table address_table;
BEGIN
addr_table := address_table();
addr_table.EXTEND;
addr_table(1) := address_type('123 Main St', 'New York');
addr_table.EXTEND;
addr_table(2) := address_type('456 Elm St', 'Los Angeles');
END;
16. Query Composite Type from a Nested Table
DECLARE
addr_table address_table;
BEGIN
SELECT addr_table INTO addr_table FROM some_table;
FOR i IN 1 .. addr_table.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Street: ' || addr_table(i).street || ', City: ' || addr_table(i).city);
END LOOP;
END;
17. Nested Table of Employees with Address
CREATE TYPE employee_with_address AS OBJECT (
emp_id NUMBER,
emp_name VARCHAR2(50),
addresses address_table
);
CREATE TYPE employee_with_address_table AS TABLE OF employee_with_address;
18. Insert Employee with Address
DECLARE
emp_table employee_with_address_table;
addr_table address_table;
BEGIN
addr_table := address_table(address_type('123 Main St', 'New York'));
emp_table := employee_with_address_table();
emp_table.EXTEND;
emp_table(1) := employee_with_address(1, 'Alice', addr_table);
-- Insert into a table
INSERT INTO some_table VALUES (emp_table);
END;
19. Retrieve Employees with Address
SELECT emp_id, emp_name, a.street, a.city
FROM employees e, TABLE(e.addresses) a;
20. Modify Address in Employee Record
DECLARE
emp_table employee_with_address_table;
BEGIN
SELECT employees INTO emp_table FROM some_table WHERE emp_id = 1;
emp_table(1).addresses(1).city := 'San Francisco';
UPDATE some_table SET employees = emp_table WHERE emp_id = 1;
END;
21. Delete an Address from Employee Record
DECLARE
emp_table employee_with_address_table;
BEGIN
SELECT employees INTO emp_table FROM some_table WHERE emp_id = 1;
emp_table(1).addresses.DELETE(1); -- Deletes the first address
UPDATE some_table SET employees = emp_table WHERE emp_id = 1;
END;
22. Count Addresses for an Employee
DECLARE
emp_table employee_with_address_table;
addr_count NUMBER;
BEGIN
SELECT employees INTO emp_table FROM some_table WHERE emp_id = 1;
addr_count := emp_table(1).addresses.COUNT;
DBMS_OUTPUT.PUT_LINE('Address Count: ' || addr_count);
END;
23. Check for Empty Address List
DECLARE
emp_table employee_with_address_table;
BEGIN
SELECT employees INTO emp_table FROM some_table WHERE emp_id = 1;
IF emp_table(1).addresses.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('No Addresses for this Employee');
ELSE
DBMS_OUTPUT.PUT_LINE('Addresses exist');
END IF;
END;
24. Iterate Over Employee Addresses
DECLARE
emp_table employee_with_address_table;
BEGIN
SELECT employees INTO emp_table FROM some_table WHERE emp_id = 1;
FOR i IN 1 .. emp_table(1).addresses.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Address: ' || emp_table(1).addresses(i).street || ', ' || emp_table(1).addresses(i).city);
END LOOP;
END;
25. Create a Nested Table for Products
CREATE TYPE product_type AS OBJECT (
product_id NUMBER,
product_name VARCHAR2(50),
price NUMBER
);
CREATE TYPE product_table AS TABLE OF product_type;
26. Insert Products into Nested Table
DECLARE
product_list product_table;
BEGIN
product_list := product_table();
product_list.EXTEND(2);
product_list(1) := product_type(1, 'Laptop', 1000);
product_list(2) := product_type(2, 'Smartphone', 500);
END;
27. Query Products from Nested Table
SELECT COLUMN_VALUE AS product
FROM TABLE(product_list);
28. Sum Prices of Products
DECLARE
product_list product_table;
total_price NUMBER := 0;
BEGIN
product_list := product_table(product_type(1, 'Laptop', 1000), product_type(2, 'Smartphone', 500));
FOR i IN 1 .. product_list.COUNT LOOP
total_price := total_price + product_list(i).price;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total Price: ' || total_price);
END;
29. Update Product Price
DECLARE
product_list product_table;
BEGIN
SELECT products INTO product_list FROM product_table WHERE product_id = 1;
product_list(1).price := 900; -- Update price
UPDATE product_table SET products = product_list WHERE product_id = 1;
END;
30. Delete a Product
DECLARE
product_list product_table;
BEGIN
SELECT products INTO product_list FROM product_table WHERE product_id = 1;
product_list.DELETE(1); -- Delete the first product
UPDATE product_table SET products = product_list WHERE product_id = 1;
END;
31. Create a Nested Table for Orders
CREATE TYPE order_item AS OBJECT (
item_id NUMBER,
quantity NUMBER
);
CREATE TYPE order_item_table AS TABLE OF order_item;
32. Insert Order Items into Nested Table
DECLARE
order_items order_item_table;
BEGIN
order_items := order_item_table();
order_items.EXTEND(2);
order_items(1) := order_item(1, 5);
order_items(2) := order_item(2, 10);
END;
33. Query Order Items
SELECT COLUMN_VALUE AS order_item
FROM TABLE(order_items);
34. Calculate Total Quantity of Order Items
DECLARE
order_items order_item_table;
total_quantity NUMBER := 0;
BEGIN
order_items := order_item_table(order_item(1, 5), order_item(2, 10));
FOR i IN 1 .. order_items.COUNT LOOP
total_quantity := total_quantity + order_items(i).quantity;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total Quantity: ' || total_quantity);
END;
35. Create Nested Table for Customer Orders
CREATE TYPE customer_order AS OBJECT (
order_id NUMBER,
order_items order_item_table
);
CREATE TYPE customer_order_table AS TABLE OF customer_order;
36. Insert Customer Orders
DECLARE
cust_orders customer_order_table;
order_items order_item_table;
BEGIN
order_items := order_item_table(order_item(1, 5), order_item(2, 10));
cust_orders := customer_order_table();
cust_orders.EXTEND;
cust_orders(1) := customer_order(1, order_items);
END;
37. Query Customer Orders
SELECT o.order_id, i.item_id, i.quantity
FROM TABLE(cust_orders) o, TABLE(o.order_items) i;
38. Update Customer Order Item Quantity
DECLARE
cust_orders customer_order_table;
BEGIN
SELECT cust_orders INTO cust_orders FROM some_table WHERE order_id = 1;
cust_orders(1).order_items(1).quantity := 8; -- Update quantity
UPDATE some_table SET cust_orders = cust_orders WHERE order_id = 1;
END;
39. Delete an Order Item
DECLARE
cust_orders customer_order_table;
BEGIN
SELECT cust_orders INTO cust_orders FROM some_table WHERE order_id = 1;
cust_orders(1).order_items.DELETE(1); -- Delete the first item
UPDATE some_table SET cust_orders = cust_orders WHERE order_id = 1;
END;
40. Count Order Items in a Customer Order
DECLARE
cust_orders customer_order_table;
item_count NUMBER;
BEGIN
SELECT cust_orders INTO cust_orders FROM some_table WHERE order_id = 1;
item_count := cust_orders(1).order_items.COUNT;
DBMS_OUTPUT.PUT_LINE('Item Count: ' || item_count);
END;
41. Check for Empty Order Items
DECLARE
cust_orders customer_order_table;
BEGIN
SELECT cust_orders INTO cust_orders FROM some_table WHERE order_id = 1;
IF cust_orders(1).order_items.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('No Order Items');
ELSE
DBMS_OUTPUT.PUT_LINE('Order Items Exist');
END IF;
END;
42. Loop Through Customer Order Items
DECLARE
cust_orders customer_order_table;
BEGIN
SELECT cust_orders INTO cust_orders FROM some_table WHERE order_id = 1;
FOR i IN 1 .. cust_orders(1).order_items.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Item ID: ' || cust_orders(1).order_items(i).item_id || ', Quantity: ' || cust_orders(1).order_items(i).quantity);
END LOOP;
END;
43. Create Nested Table for Course Registrations
CREATE TYPE registration AS OBJECT (
student_id NUMBER,
course_id NUMBER
);
CREATE TYPE registration_table AS TABLE OF registration;
44. Insert Course Registrations
DECLARE
reg_table registration_table;
BEGIN
reg_table := registration_table();
reg_table.EXTEND(2);
reg_table(1) := registration(1, 101);
reg_table(2) := registration(2, 102);
END;
45. Query Course Registrations
SELECT COLUMN_VALUE AS registration
FROM TABLE(reg_table);
46. Count Total Registrations
DECLARE
reg_table registration_table;
total_regs NUMBER := 0;
BEGIN
reg_table := registration_table(registration(1, 101), registration(2, 102));
total_regs := reg_table.COUNT;
DBMS_OUTPUT.PUT_LINE('Total Registrations: ' || total_regs);
END;
47. Update Registration
DECLARE
reg_table registration_table;
BEGIN
SELECT registrations INTO reg_table FROM some_table WHERE student_id = 1;
reg_table(1).course_id := 103; -- Update course ID
UPDATE some_table SET registrations = reg_table WHERE student_id = 1;
END;
48. Delete a Registration
DECLARE
reg_table registration_table;
BEGIN
SELECT registrations INTO reg_table FROM some_table WHERE student_id = 1;
reg_table.DELETE(1); -- Delete the first registration
UPDATE some_table SET registrations = reg_table WHERE student_id = 1;
END;
49. Check for Empty Registrations
DECLARE
reg_table registration_table;
BEGIN
SELECT registrations INTO reg_table FROM some_table WHERE student_id = 1;
IF reg_table.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('No Registrations for this Student');
ELSE
DBMS_OUTPUT.PUT_LINE('Registrations Exist');
END IF;
END;
50. Loop Through Course Registrations
DECLARE
reg_table registration_table;
BEGIN
SELECT registrations INTO reg_table FROM some_table WHERE student_id = 1;
FOR i IN 1 .. reg_table.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Student ID: ' || reg_table(i).student_id || ', Course ID: ' || reg_table(i).course_id);
END LOOP;
END;
No comments:
Post a Comment