Distributed Database Applications

1. Distributed Database Applications COSC 5050 Week Three

2. Outline • Cursors and data retrieval • Procedures • Functions • Parameters • Sequences Distributed Database Applications

3. Cursors and Data Retrieval • PL/SQL integrates with the Oracle database • System Global Area (SGA) • Private working area for SQL statement • Cursor • To query data from the database and make that data available within PL/SQL programs • Implicit cursor • Explicit cursor • Cursor variable Distributed Database Applications

4. Cursor • Cursor is a pointer into a virtual table in the database CURSOR employee_cur IS SELECT fname, lname FROM employee; OPEN employee_cur; FETCH employee_cur INTO employee_rec; CLOSE employee_cur; Distributed Database Applications

5. Cursor Fetch Operation Distributed Database Applications

6. Cursor create or replace procedure cursor_employee is CURSOR employee_cur IS SELECT fname, lname FROM employee WHERE ssn = '600000001'; employee_rec employee_cur%rowtype; begin OPEN employee_cur; FETCH employee_cur INTO employee_rec; dbms_output.put_line( employee_rec.fname || ' ' ||employee_rec.lname); CLOSE employee_cur; end; / Distributed Database Applications

7. Cursor Attributes • Page 468 • %FOUND • Status of fetch operation • True if fetch succeeded • %NOTFOUND • Opposite of %FOUND • %ROWCOUNT • Number of records fetched • %ISOPEN • True if the cursor is open cursor_name%attribute_name Distributed Database Applications

8. Referencing Variables in Cursor • Identifier precedence in a cursor • Qualifying variable with proc name • Using standard naming conventions create or replace PROCEDURE adjust IS l_code NUMBER := 1000; CURSOR double_code_cur IS SELECT code + l_code as adjusted FROM employee WHERE dept_num = 2; employee_rec double_code_cur%rowtype; begin OPEN double_code_cur; FETCH double_code_cur INTO employee_rec; dbms_output.put_line(employee_rec.adjusted); CLOSE double_code_cur; END; Distributed Database Applications

9. Implicit Cursor • Use SELECT INTO DECLARE l_department department.dept_name%TYPE; BEGIN SELECT dept_name INTO l_department FROM department WHERE dept_num = 1; DBMS_OUTPUT.PUT_LINE(l_department); END; DECLARE l_department department%ROWTYPE; BEGIN SELECT * INTO l_department FROM department WHERE dept_num = 1; DBMS_OUTPUT.PUT_LINE(l_department.dept_name); END; Distributed Database Applications

10. Implicit Cursor • Error handling • With exception Implicit SQL cursor attributes SQL%FOUND SQL%NOTFOUND SQL%ROWCOUNT SQL%ISOPEN DECLARE l_department department%ROWTYPE; BEGIN SELECT * INTO l_department FROM department WHERE dept_num = 3; DBMS_OUTPUT.PUT_LINE(l_department.dept_name); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No data!'); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('Too many rows'); END; / Distributed Database Applications

11. Explicit Cursor • Explicitly defined in declaration section • Complete control over open, fetch, close CURSOR company_cur IS SELECT company_id FROM company; CURSOR name_cur (company_id_in IN NUMBER) IS SELECT name FROM company WHERE company_id = company_id_in; CURSOR emp_cur RETURN employee%ROWTYPE IS SELECT * FROM employee WHERE department_id = 10; Distributed Database Applications

12. Explicit Cursor • Open • Fetch • Close • Explicit cursor attributes • cursor%FOUND • cursor%NOTFOUND • cursor%ROWCOUNT • cursor%ISOPEN Distributed Database Applications

13. Explicit Cursor DECLARE CURSOR c1 IS SELECT fname, lname, code, code*1.1 new_code FROM employee; rec c1%ROWTYPE; BEGIN OPEN c1; LOOP FETCH c1 INTO rec; EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE( rec.fname || ' ' || rec.lname || ' ' || rec.code || ' ' || rec.new_code); END LOOP; CLOSE c1; END; / Distributed Database Applications

14. Explicit Cursor DECLARE l_dept_id NUMBER := 1; CURSOR name_cur (dept_id_in IN NUMBER) IS SELECT dept_name, lname FROM department inner join employee on department.dept_mgr_ssn = employee.ssn WHERE department.dept_num = dept_id_in; name_rec name_cur%ROWTYPE; BEGIN OPEN name_cur(l_dept_id); LOOP FETCH name_cur INTO name_rec; EXIT WHEN name_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE( name_rec.dept_name || ' ' || name_rec.lname); END LOOP; CLOSE name_cur; END; / Distributed Database Applications

15. Explicit Cursor DECLARE CURSOR emp_cur RETURN employee%rowtype IS SELECT * FROM employee WHERE dept_num = 15; emp_rec emp_cur%ROWTYPE; BEGIN OPEN emp_cur; LOOP FETCH emp_cur INTO emp_rec; EXIT WHEN emp_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(emp_cur%rowcount || ': ' || emp_rec.fname || ' ' || emp_rec.lname); END LOOP; CLOSE emp_cur; END; / Distributed Database Applications

16. Cursor Variable • Cursor variable points to or references an underlying cursor DECLARE TYPE employee_curtype IS REF CURSOR RETURN employee%ROWTYPE; employee_curvar employee_curtype; employee_rec employee%ROWTYPE; BEGIN OPEN employee_curvar FOR SELECT * FROM employee; FETCH employee_curvar INTO employee_rec; CLOSE employee_curvar; dbms_output.put_line(employee_rec.fname); END; / Distributed Database Applications

17. Cursor Loop • Cursor with simple loop DECLARE CURSOR name_cur IS SELECT lname, fname FROM employee WHERE ssn like '8%'; name_rec name_cur%ROWTYPE; BEGIN OPEN name_cur; LOOP FETCH name_cur INTO name_rec; EXIT WHEN name_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE( name_rec.fname || ' ' || name_rec.lname); END LOOP; CLOSE name_cur; END; / Distributed Database Applications

18. Cursor Loop • Cursor with simple loop DECLARE CURSOR occupancy_cur IS SELECT pet_id, room_number FROM occupancy WHERE occupied_dt = TRUNC(SYSDATE); occupancy_rec occupancy_cur%ROWTYPE; BEGIN OPEN occupancy_cur; LOOP FETCH occupancy_cur INTO occupancy_rec; EXIT WHEN occupancy_cur%NOTFOUND; update_bill (occupancy_rec.pet_id, occupancy_rec.room_number); END LOOP; CLOSE occupancy_cur; END; Distributed Database Applications

19. Cursor FOR Loop • Cursor For loop DECLARE CURSOR occupancy_cur IS SELECT pet_id, room_number FROM occupancy WHERE occupied_dt = TRUNC(SYSDATE); BEGIN FOR occupancy_rec IN occupancy_cur LOOP update_bill (occupancy_rec.pet_id, occupancy_rec.room_number); END LOOP; END; Distributed Database Applications

20. Cursor FOR Loop • Cursor FOR loop DECLARE CURSOR name_cur IS SELECT lname, fname FROM employee WHERE ssn like '8%'; BEGIN FOR name_rec IN name_cur LOOP DBMS_OUTPUT.PUT_LINE( name_rec.fname || ' ' || name_rec.lname); END LOOP; END; / Distributed Database Applications

21. Modular Code • PL/SQL offers the following structures that modularize your code in different ways • Procedure • Function • Trigger • Package Distributed Database Applications

22. Procedure • Procedure is a module that performs one or more actions • Can be a standalone call, executable statement Distributed Database Applications

23. CREATE OR REPLACE PROCEDURE get_happy (ename_in IN VARCHAR2)  Header Procedure IS hiredate DATE;  Declarations BEGIN hiredate:= SYSDATE – 2; INSERT INTO employee (emp_name, hiredate) VALUES (ename_in, hiredate);  Code Body EXCEPTION * optional * WHEN DUP_VAL_IN_INDEX THEN DBMS_OUTPUT.PUT_LINE (‘Cannot insert.’);  Exception block * optional * END get_happy; Distributed Database Applications

24. Procedure Distributed Database Applications

25. Procedure procedure Add_employee (ssn in varchar2 , fname in varchar2 , lname in varchar2 , dept_num in number , code in number , sup_ssn in varchar2) is begin insert into employee values (ssn, fname, lname, dept_num, code, sup_ssn); end; Distributed Database Applications

26. Procedure procedure delete_employee (tgt_ssn in varchar2) is cursor dept_mgr_cur (tst_ssn varchar2) is select dept_mgr_ssn from department where dept_mgr_ssn = tst_ssn; dept_mgr_cur_ssn varchar2(9); begin open dept_mgr_cur(tgt_ssn); fetch dept_mgr_cur into dept_mgr_cur_ssn; if (dept_mgr_cur%found) then update department set dept_mgr_ssn = NULL where dept_mgr_ssn = tgt_ssn; end if; delete from employee where ssn = tgt_ssn; close dept_mgr_cur; end; Distributed Database Applications

27. Procedure • Calling a procedure • Procedure is called as an executable PL/SQL statement • Called in a block • Must end with (;) begin apply_discount( new_company_id, 0.15 ); display_store_summary; display_store_summary(); end; Distributed Database Applications

28. Function • Function is a module that returns a value • Function can exist only as part of an executable statement • Function has a datatype Distributed Database Applications

29. Function Distributed Database Applications

30. Function Distributed Database Applications

31. Function • Defining function create or replace function favorite_nickname( name_in in varchar2) return varchar2 is begin End; • Calling function declare nickname varchar2(100) := favorite_nickname(‘Nic’); Distributed Database Applications

32. Function create or replace function get_department( ssn_in in employee.ssn%type) return department.dept_name%type is l_dept_name department.dept_name%type; begin select dept_name into l_dept_name from department inner join employee on employee.dept_num = department.dept_num where ssn = ssn_in; dbms_output.put_line('department name: ' || l_dept_name); return l_dept_name; exception when no_data_found then dbms_output.put_line( 'no such employee or not in any department!'); return null; end; / Distributed Database Applications

33. Parameter • Be used to pass information back and forth • Parameter declaration must be unconstrained DECLARE company_name varchar2(60); PROCEDURE display_company( company_name IN varchar2) IS … Distributed Database Applications

34. Parameter Mode • IN mode: Read-only • Is the default mode • Value cannot be changed • Can have default value • Passed by reference • OUT mode: Write-only • Has no value until the end • No default value • Actual parameter must be a variable • Passed by value • IN OUT mode: Read-write • Cannot have default value • Must be a variable • Passed by value Distributed Database Applications

35. Parameter create or replace procedure delete_employee ( tgt_ssn_in in varchar2, removed_name_out out varchar2) is cursor dept_mgr_cur (tst_ssn varchar2) is select dept_mgr_ssn from department where dept_mgr_ssn = tst_ssn; dept_mgr_cur_ssn varchar2(9); begin open dept_mgr_cur(tgt_ssn_in); fetch dept_mgr_cur into dept_mgr_cur_ssn; if (dept_mgr_cur%found) then update department set dept_mgr_ssn = NULL where dept_mgr_ssn = tgt_ssn_in; end if; select lname into removed_name_out from employee where ssn = tgt_ssn_in; delete from employee where ssn = tgt_ssn_in; close dept_mgr_cur; end; / Distributed Database Applications

36. Parameter create or replace PROCEDURE combine_and_format_names (first_name_inout IN OUT VARCHAR2, last_name_inout IN OUT VARCHAR2, full_name_out OUT VARCHAR2, name_format_in IN VARCHAR2 := 'LAST, FIRST') IS BEGIN first_name_inout := UPPER (first_name_inout); last_name_inout := UPPER (last_name_inout); IF name_format_in = 'LAST, FIRST' THEN full_name_out := last_name_inout || ', ' || first_name_inout; ELSIF name_format_in = 'FIRST LAST' THEN full_name_out := first_name_inout || ' ' || last_name_inout; END IF; END; / Distributed Database Applications

37. Oracle Sequences • Independent object in the database • Not a data type • Not tied to a table or a column • Can be used anywhere a value is expected • Generate a numeric value that can be assigned to any column in any table • The table attribute to which you assigned a value based on a sequence can be edited and modified • Can be created and deleted anytime Distributed Database Applications

38. Oracle Sequences • CREATE SEQUENCE NAME [START WITH N] [INCREMENT BY N] [CACHE | NOCACHE] • CREATE SEQUENCE CUS_CODE_SEQ START WITH 20010 NOCACHE; • CREATE SEQUENCE INV_NUMBER_SEQ START WITH 4010 NOCACHE; • SELECT * FROM USER_SEQUENCES; • DROP SEQUENCE CUS_CODE_SEQ; • DROP SEQUENCE INV_NUMBER_SEQ; Distributed Database Applications

39. Oracle Sequences • INSERT INTO CUSTOMER VALUES (CUS_CODE_SEQ.NEXTVAL, ‘CONNERY’, ‘SEAN’, NULL, ‘615’, ‘898-2007’, 0.00); • INSERT INTO INVOICE VALUES (INV_NUMBER_SEQ.NEXTVAL, 20010, SYSDATE); • INSERT INTO LINE VALUES (INV_NUMBER_SEQ.CURRVAL, 1, ‘13-Q2/P2’, 1, 14.99); • INSERT INTO LINE VALUES (INV_NUMBER_SEQ.CURRVAL, 2, ‘23109-HB’, 1, 9.95); Distributed Database Applications

40. Oracle Sequences • Once a sequence value is used (through NEXTVAL), it cannot be used again • If SQL statement rolls back, the sequence value does not roll back • A sequence is not associated with a table • A sequence can be used to generate unique values for more than one tables • Dropping a sequence does not delete the values generated • A sequence has one purpose: assign unique numbers to stuff • Do not expect a sequence to return gap free values Distributed Database Applications

41. Homework • Create a function that returns the next available customer ID. • Create a stored procedure that accepts customer data as input arguments, adds a new customer record to the customer table. • Create a stored procedure that accepts a numeric amount as an input argument that will be used as a filter threshold and prints a listing of customer information. • Lab activities Distributed Database Applications