Question

Q: Write a PL/SQL after creating following tables with appropriate dataypes and constraints.
Purchase_Header (Purchase_no, Purchase_date, Vendor_id, Remarks)
Purchase_detail (Purchase_no, sr_no, item_id, qty)
Vendor_master (Vendor_id, Vendor_name)
Item_master(Item_id, Item_name, unit_rate)
 
Insert two records in Purchase_header, at least five records in purchase_detail and three records each in remaining tables.
 
The PL/SQL block to generate following output:
Purchase_no       Purcahse_date  Vendor_name     sr_no    item_name     unit_rate    Quantity    Amount
 ​

Answer 1

Answer:

CREATE TABLE employees_temp AS SELECT employee_id, first_name, last_name

FROM employees;

DECLARE

emp_id employees_temp.employee_id%TYPE;

emp_first_name employees_temp.first_name%TYPE;

emp_last_name employees_temp.last_name%TYPE;

BEGIN

INSERT INTO employees_temp VALUES(299, 'Bob', 'Henry');

UPDATE employees_temp SET first_name = 'Robert' WHERE employee_id = 299;

DELETE FROM employees_temp WHERE employee_id = 299

RETURNING first_name, last_name INTO emp_first_name, emp_last_name;

COMMIT;

DBMS_OUTPUT.PUT_LINE( emp_first_name || ' ' || emp_last_name);

END;

/

1. CREATE TABLE employees_temp AS SELECT * FROM employees;
2. BEGIN
3. UPDATE employees_temp SET salary = salary * 1.05 WHERE salary < 5000;
4. DBMS_OUTPUT.PUT_LINE('Updated ' || SQL%ROWCOUNT || ' salaries.');
5. END;
6. /
7. CREATE TABLE employees_temp AS SELECT first_name, last_name FROM employees;
8. DECLARE
9. x VARCHAR2(20) := 'my_first_name';
10. y VARCHAR2(25) := 'my_last_name';
11. BEGIN
12. INSERT INTO employees_temp VALUES(x, y);
13. UPDATE employees_temp SET last_name = x WHERE first_name = y;
14. DELETE FROM employees_temp WHERE first_name = x;
15. COMMIT;
16. END;
17. /
18. DECLARE
19. job_count NUMBER;
20. emp_count NUMBER;
21. BEGIN
22. SELECT COUNT(DISTINCT job_id) INTO job_count FROM employees;
23. SELECT COUNT(*) INTO emp_count FROM employees;
24. END;
25. /

4.

CREATE TABLE employees_temp AS SELECT employee_id, first_name, last_name

FROM employees;

CREATE TABLE employees_temp2 AS SELECT employee_id, first_name, last_name

FROM employees;

DECLARE

seq_value NUMBER;

BEGIN

-- Display initial value of NEXTVAL

-- This is invalid: seq_value := employees_seq.NEXTVAL;

SELECT employees_seq.NEXTVAL INTO seq_value FROM dual;

DBMS_OUTPUT.PUT_LINE ('Initial sequence value: ' || TO_CHAR(seq_value));

-- The NEXTVAL value is the same no matter what table you select from

-- You usually use NEXTVAL to create unique numbers when inserting data.

INSERT INTO employees_temp VALUES (employees_seq.NEXTVAL, 'Lynette', 'Smith');

-- If you need to store the same value somewhere else, you use CURRVAL

INSERT INTO employees_temp2 VALUES (employees_seq.CURRVAL, 'Morgan', 'Smith');

-- Because NEXTVAL values might be referenced by different users and

-- applications, and some NEXTVAL values might not be stored in the

-- database, there might be gaps in the sequence

-- The following uses the stored value of the CURRVAL in seq_value to specify

-- the record to delete because CURRVAL (or NEXTVAL) cannot used in a WHERE clause

-- This is invalid: WHERE employee_id = employees_seq.CURRVAL;

SELECT employees_seq.CURRVAL INTO seq_value FROM dual;

DELETE FROM employees_temp2 WHERE employee_id = seq_value;

-- The following udpates the employee_id with NEXTVAL for the specified record

UPDATE employees_temp SET employee_id = employees_seq.NEXTVAL

WHERE first_name = 'Lynette' AND last_name = 'Smith';

-- Display end value of CURRVAL

SELECT employees_seq.CURRVAL INTO seq_value FROM dual;

DBMS_OUTPUT.PUT_LINE ('Ending sequence value: ' || TO_CHAR(seq_value));

END;

/