1 / 41

Distributed Database Applications

Distributed Database Applications. COSC 5050 Week Three. Outline. Cursors and data retrieval Procedures Functions Parameters Sequences. Cursors and Data Retrieval. PL/SQL integrates with the Oracle database System Global Area (SGA) Private working area for SQL statement Cursor

cleave
Download Presentation

Distributed Database Applications

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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 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 code NUMBER := 1000; CURSOR double_code_cur IS SELECT code + 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 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%RWOTYPE 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 := &department_number; 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 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

More Related