1 / 47

Distributed Database Applications

Distributed Database Applications. COSC 5050 Week Five. Outline. Managing PL/SQL Code Exceptions and Error Handling Transaction Management Packages. Data Dictionary. USER_ USER_DEPENDENCIES USER_ERRORS USER_OBJECTS USER_SOURCE USER_TRIGGERS USER_ARGUMENTS. Data Dictionary.

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 Five

  2. Outline • Managing PL/SQL Code • Exceptions and Error Handling • Transaction Management • Packages Distributed Database Applications

  3. Data Dictionary • USER_ • USER_DEPENDENCIES • USER_ERRORS • USER_OBJECTS • USER_SOURCE • USER_TRIGGERS • USER_ARGUMENTS Distributed Database Applications

  4. Data Dictionary SELECT object_type, object_name, status FROM user_objects WHERE object_type IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE', 'TRIGGER' ) ORDER BY object_type, status, object_name; DESC ADD_EMPLOYEE SELECT TEXT FROM USER_SOURCE WHERE NAME = 'ADD_EMPLOYEE'; Distributed Database Applications

  5. Debug PL/SQL Programs • Recompile invalid code • Source code debugger • Analyzing • Unit test • show err Distributed Database Applications

  6. Exception Handler • System exception • An exception that is defined by Oracle and is usually raised by the PL/SQL runtime engine • With name or number • NO_DATA_FOUND • User defined exceptions • An exception that is defined by the programmer and is therefore specific to the application Distributed Database Applications

  7. Exception 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

  8. Exception 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

  9. Exception procedure delete_department (department_id_in innumber) is begin delete from department where dept_num = department_id_in; end; procedure delete_department (department_id_in in number) is begin delete from department where dept_num = department_id_in; exception when others then if sqlcode = -2292 then dbms_output.put_line ('Please delete employees for the department first.'); end if; end; SQLCODE is a built-in function that returns the number of the last error raised. Distributed Database Applications

  10. Declare Exception • Declare an exception by listing the name of the exception followed by the keyword EXCEPTION • invalid_company_id EXCEPTION; • Be referenced in two ways • Raise the exception: • RAISE invalid_company_id; • Handle the raised exception: • WHEN invalid_company_id THEN Distributed Database Applications

  11. Declare Exception procedure calc_annual_sales (company_id_in in company.company_id%type) is invalid_company_id exception; negative_balance exception; duplicate_company boolean; begin … body of executable statements … raise invalid_company_id; exception … when no_data_found –- system exception then … when invalid_company_id then … when negative_balance then … end; Distributed Database Applications

  12. Declare Exception procedure delete_department(department_id_in in number) is l_count number; still_have_employees exception; begin select count(*) into l_count from employee where dept_num = department_id_in; if l_count > 0 then raise still_have_employees; end if; delete from department where dept_num = department_id_in; exception when still_have_employees then dbms_output.put_line ('Please delete employees for the department first.'); end; Distributed Database Applications

  13. Associate Exception Name procedure delete_department(department_id_in in number) is still_have_employees exception; pragma exception_init (still_have_employees, -2292); begin delete from department where dept_num = department_id_in; exception when still_have_employees then dbms_output.put_line ('Please delete employees for the department first.'); end; ORA-2292 violated integrity constraining (OWNER.CONSTRAINT) – child record found. The PRAGMA keyword is used to signify that the remainder of the PL/SQL statement is a pragma, or directive, or pseudoinstruction to the compiler. Distributed Database Applications

  14. Associate Exception Name • EXCEPTION_INIT • Compile-time command or pragma • Shot for pragmatic information • Instructs the compiler to associate an identifier, declared as an EXCEPTION, with a specific error number • The error number cannot be: • -1403 (error code for NO_DATA_FOUND), use 100 instead • 0 or any positive number besides 100 • A negative number less than -1000000 Distributed Database Applications

  15. Associate Exception Name • Centralize EXCEPTION_INIT into packages CREATE OR REPLACE PACKAGE dynsql IS invalid_table_name EXCEPTION; PRAGMA EXCEPTION_INIT (invalid_table_name, -903); invalid_column_name EXCEPTION; PRAGMA EXCEPTION_INIT (invalid_column_name, -904); Distributed Database Applications

  16. Raise Exception • Oracle might raise the exception when it detects an error • Programmer might raise an exception with the RAISE statement • RAISE exception_name; • RAISE package_name.exception_name; • RAISE; -- propagate out Distributed Database Applications

  17. Raise Exception DECLARE invalid_id EXCEPTION; id_value VARCHAR2(30); BEGIN id_value := id_for ('SMITH'); IF SUBSTR (id_value, 1, 1) != 'X' THEN RAISE invalid_id; END IF; ... END; IF days_overdue (isbn_in, borrower_in) > 365 THEN RAISE overdue_pkg.book_is_lost; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN -- record all the "context" information errlog.putline (company_id_in); -- pass NO_DATA_FOUND unhandled RAISE; Distributed Database Applications

  18. Handling Exception • Exception handlers are structured much like a conditional CASE statement Distributed Database Applications

  19. Handling Exception 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); EXCEPTION WHEN OTHERS THEN DECLARE l_errcode PLS_INTEGER := SQLCODE; BEGIN IF l_errcode = -1 THEN DBMS_OUTPUT.put_line ('Employee ID already in use.'); RAISE; ELSIF l_errcode = -2291 THEN DBMS_OUTPUT.put_line ('Invalid department ID: '); RAISE; ELSE null; RAISE; END IF; END; END add_employee; Distributed Database Applications

  20. Combing Multiple Exceptions 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 OR TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('Wrong department number!'); END; / Distributed Database Applications

  21. Named System Exception • P. 133 • CURSOR_ALREADY_OPEN (ORA-06511) • DUP_VAL_ON_INDEX (ORA-00001) • INVALID_CURSOR (ORA-01001) • INVALID_NUMBER (ORA-01722) • NO_DATA_FOUND (ORA-01403) • TOO_MANY_ROWS (ORA-01422) • VALUE_ERROR (ORA-06502) • ZERO_DIVIDE (ORA-01476) Distributed Database Applications

  22. SQLCODE and SQLERRM • Built-in Error Functions • SQLCODE • Return the oracle error number • SQLERRM • Return the oracle message text associated with the error begin insert into employee values ('666666666', 'Dave', 'Letterman', NULL, 4581.12, NULL); exception when others then dbms_output.put_line('code : ' || sqlcode); dbms_output.put_line('message : ' || sqlerrm); if sqlcode = -1 then dbms_output.put_line ('Employee number exists!'); end if; end; Distributed Database Applications

  23. SQLCODE and SQLERRM PROCEDURE DELETE_COMPANY (COMPANY_ID IN NUMBER) IS BEGIN DELETE FROM COMPANY WHERE COMPANY_ID = COMPANY_ID_IN; EXCEPTION WHEN OTHERS THEN DECLARE ERROR_CODE NUMBER := SQLCODE; ERROR_MSG VARCHAR2(512) := SQLERRM; BEGIN IF ERROR_CODE = -2292 /*CHILD RECORDS FOUND*/ THEN DELETE FROM EMPLOYEE WHERE COMPANY_ID = COMPANY_ID_IN; DELETE FROM COMPANY WHERE COMPANY_ID = COMPANY_ID_IN; ELSIF ERROR_CODE = -2291 /*PARENT KEY NOT FOUND*/ THEN DBMS_OUTPUT.PUT_LINE(‘INVALID COMPANY ID: ’||TO_CHAR(COMPANY_ID_IN)); ELSE DBMS_OUTPUT.PUT_LINE(‘ERROR DELETING COMPANY, ERROR: ’||ERROR_MSG); END IF; END; END DELETE_COMPANY; Distributed Database Applications

  24. DML in PL/SQL • INSERT • INSERT INTO table VALUES (val1, …); • INSERT INTO table AS SELECT …; • UPDATE • UPDATE table SET col1 = val1 WHERE … • DELETE • DELETE FROM table WHERE … • Cursor attributes for DML operations • SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN Distributed Database Applications

  25. DML in PL/SQL • Use SQL%FOUND to determine if your DML statement affected any rows CREATE OR REPLACE PROCEDURE change_author_name ( old_name_in IN books.author%TYPE, new_name_in IN books.author%TYPE, changes_made_out OUT BOOLEAN) IS BEGIN UPDATE books SET author = new_name_in WHERE author = old_name_in; changes_made_out := SQL%FOUND; END; Distributed Database Applications

  26. DML in PL/SQL • Use SQL%ROWCOUNT when you need to know exactly how many rows were affected by your DML statement CREATE OR REPLACE PROCEDURE change_author_name ( old_name_in IN books.author%TYPE, new_name_in IN books.author%TYPE, rename_count_out OUT PLS_INTEGER) IS BEGIN UPDATE books SET author = new_name_in WHERE author = old_name_in; rename_count_out := SQL%ROWCOUNT; END; Distributed Database Applications

  27. RETURNING Clause • Use returning clause with DML and retrieve information directly into variables Declare l_name employee.lname%type; l_code employee.code%type; Begin for rec in (select * from employee) loop update employee set code = rec.code*1.10 where ssn = rec.ssn returning code, lname into l_code, l_name; dbms_output.put_line ('New code for ' || l_name || ' = ' || l_code); end loop; End; / Distributed Database Applications

  28. Use Records in Insert and Update procedure add_employee(emp_in in employee%rowtype) is begin insert into employee values emp_in; end; declare new_employee employee%rowtype; begin new_employee.ssn := '123123124'; new_employee.fname := 'Steve'; new_employee.lname := 'Hack'; new_employee.dept_num := '1'; new_employee.code := 123.45; new_employee.sup_ssn := '600000001'; add_employee(new_employee); new_employee.code := 333.09; update employee set row = new_employee where ssn = new_employee.ssn; end; Distributed Database Applications

  29. Use Records in Insert and Update • Using records with the RETURNING clause DECLARE my_book_new_info books%ROWTYPE; my_book_return_info books%ROWTYPE; BEGIN my_book_new_info.isbn := '1-56592-335-9'; my_book_new_info.title := 'ORACLE PL/SQL PROGRAMMING'; my_book_new_info.summary := 'General user guide and reference'; my_book_new_info.author := 'FEUERSTEIN, STEVEN AND BILL PRIBYL'; my_book_new_info.page_count := 980; UPDATE books SET ROW = my_book_new_info WHERE isbn = my_book_new_info.isbn RETURNING isbn, title, summary, author, page_count INTO my_book_return_info; END; Distributed Database Applications

  30. Transaction Management • Commit – save all outstanding changes • Rollback – erase all outstanding changes • Savepoint • SAVEPOINT savepoint_name; • Rollback to savepoint • ROLLBACK TO savepoint_name; • Lock table • LOCK TABLE emp, dept IN SHARE MODE • LOCK TABLE emp IN ROW EXCLUSIVE MODE Distributed Database Applications

  31. Autonomous Transaction procedure add_emp(…) is begin update …; update …; savepoint start_add; insert …; commit; exception when others then rollback to start_add; write_log (sqlcode, sqlerrm); end; MT begins procedure write_log(…) is pragma autonomous_transaction; begin insert into log values ( code, text, user, sysdate); commit; end; AT begins MT suspends AT ends MT resumes MT ends Distributed Database Applications

  32. Autonomous Transaction • Define a PL/SQL block as autonomous transaction • anonymous block, procedure, function, packaged procedure, packaged function, database trigger • Isolate the DML in that block from the caller's transaction context • The block becomes an independent transaction Distributed Database Applications

  33. Package • A package is a grouping or packaging of PL/SQL code elements • Package specification • Definition or specification of all the publicly available elements in the package • Package body • Code required to implement elements defined in the package specification Distributed Database Applications

  34. Favorites_pkg Specification • Favorites_pkg (p. 625) CREATE OR REPLACE PACKAGE favorites_pkg AUTHID CURRENT_USER IS c_chocolate CONSTANT PLS_INTEGER := 16; c_strawberry CONSTANT PLS_INTEGER := 29; TYPE codes_nt IS TABLE OF INTEGER; my_favorites codes_nt; TYPE fav_info_rct IS REF CURSOR RETURN favorites%ROWTYPE; PROCEDURE show_favorites (list_in IN codes_nt); FUNCTION most_popular RETURN fav_info_rct; END favorites_pkg; / Distributed Database Applications

  35. Favorites_pkg Body CREATE OR REPLACE PACKAGE BODY favorites_pkg IS g_most_popular PLS_INTEGER := c_strawberry; PROCEDURE show_favorites (list_in IN codes_nt) IS BEGIN FOR indx IN list_in.FIRST .. list_in.LAST LOOP DBMS_OUTPUT.put_line (list_in (indx)); END LOOP; END show_favorites; FUNCTION most_popular RETURN fav_info_rct IS retval fav_info_rct; null_cv fav_info_rct; BEGIN OPEN retval FOR SELECT * FROM favorites WHERE code = g_most_popular; RETURN retval; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN null_cv; END most_popular; END favorites_pkg; • Body (p.627) Distributed Database Applications

  36. Package Specification CREATE OR REPLACE PACKAGE EMPLOYEE_PKG AS SUBTYPE FULLNAME_T IS VARCHAR2 (200); FUNCTION FULLNAME ( LAST_IN EMPLOYEE.LNAME%TYPE, FIRST_IN EMPLOYEE.FNAME%TYPE) RETURN FULLNAME_T; FUNCTION FULLNAME ( EMPLOYEE_SSN_IN IN EMPLOYEE.SSN%TYPE) RETURN FULLNAME_T; END EMPLOYEE_PKG; Distributed Database Applications

  37. Package Body CREATE OR REPLACE PACKAGE BODY EMPLOYEE_PKG AS FUNCTION FULLNAME ( LAST_IN EMPLOYEE.LNAME%TYPE, FIRST_IN EMPLOYEE.FNAME%TYPE) RETURN FULLNAME_T IS BEGIN RETURN LAST_IN || ', ' || FIRST_IN; END; FUNCTION FULLNAME (EMPLOYEE_SSN_IN IN EMPLOYEE.SSN%TYPE) RETURN FULLNAME_T IS RETVAL FULLNAME_T; BEGIN SELECT FULLNAME (LNAME, FNAME) INTO RETVAL FROM EMPLOYEE WHERE SSN = EMPLOYEE_SSN_IN; RETURN RETVAL; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; WHEN TOO_MANY_ROWS THEN RETURN NULL; END; END EMPLOYEE_PKG; Distributed Database Applications

  38. Calling Packaged Elements • Package is a container for code elements • Run or reference elements in a package • From outside of that package, use dot notation • package_name.element_name • Within the package (specification or body), no need to include the name of the package Distributed Database Applications

  39. Pets_inc Package CREATE OR REPLACE PACKAGE pets_inc IS max_pets_in_facility CONSTANT INTEGER := 120; pet_is_sick EXCEPTION; CURSOR pet_cur (pet_id_in in pet.id%TYPE) RETURN pet%ROWTYPE; FUNCTION next_pet_shots (pet_id_in IN pet.id%TYPE) RETURN DATE; PROCEDURE set_schedule (pet_id_in IN pet.id%TYPE); END pets_inc; Distributed Database Applications

  40. Pets_inc Package CREATE OR REPLACE PACKAGE BODY pets_inc IS CURSOR pet_cur (pet_id_in IN pet.id%TYPE) RETURN pet%ROWTYPE IS SELECT * FROM pet p WHERE p.id = pet_id_in; FUNCTION next_pet_shots (pet_id_in IN pet.id%TYPE) RETURN DATE /* This Implementation is Just a Stub, Presumably to be Replaced by Something that Does Something Meaningful Later On */ IS BEGIN RETURN SYSDATE; END next_pet_shots; PROCEDURE set_schedule (pet_id_in IN pet.id%TYPE) /* This Implementation is Just a Stub, Presumably to be Replaced by */ /* Something that Does Something Meaningful Later On */ IS BEGIN /* Null is a No-Op. */ /* The Statement is Included to Satisfy the Compile-Time Syntax-Checker */ NULL; END; END pets_inc; / Distributed Database Applications

  41. Pets_inc Package declare c_pet constant pet.id%type:=1099; v_net_appointment date; begin if pets_inc.max_pets_in_facility > 100 then open pets_inc.pet_cur(c_pet); else v_next_appointment:=pets_inc.next_pet_shots(c_pet); end if; exception when pets_inc.pet_is_sick then pets_inc.set_scheducle(c_pet); end; Distributed Database Applications

  42. Package Data • Package level data • Persist for the session • Public and private data • State of the packaged cursors persists for the session • Open/fetch/close it in different program • Close packaged cursor promptly Distributed Database Applications

  43. Packaged Cursors CREATE OR REPLACE PACKAGE book_info IS CURSOR byauthor_cur ( author_in IN books.author%TYPE ) IS SELECT * FROM books WHERE author = author_in; CURSOR bytitle_cur ( title_filter_in IN books.title%TYPE ) RETURN books%ROWTYPE; TYPE author_summary_rt IS RECORD ( author books.author%TYPE, total_page_count PLS_INTEGER, total_book_count PLS_INTEGER); CURSOR summary_cur ( author_in IN books.author%TYPE ) RETURN author_summary_rt; END book_info; Distributed Database Applications

  44. Packaged Cursors CREATE OR REPLACE PACKAGE BODY book_info IS CURSOR bytitle_cur ( title_filter_in IN books.title%TYPE ) RETURN books%ROWTYPE IS SELECT * FROM books WHERE title LIKE UPPER (title_filter_in); CURSOR summary_cur ( author_in IN books.author%TYPE ) RETURN author_summary_rt IS SELECT author, SUM (page_count), COUNT (*) FROM books WHERE author = author_in; END book_info; Distributed Database Applications

  45. Packaged Cursors DECLARE onebook book_info.bytitle_cur%ROWTYPE; BEGIN OPEN book_info.bytitle_cur ('%PL/SQL%'); LOOP EXIT WHEN book_info.bytitle_cur%NOTFOUND; FETCH book_info.bytitle_cur INTO onebook; book_info.display (onebook); END LOOP; CLOSE book_info.bytitle_cur; END; Distributed Database Applications

  46. Use of Packages • Encapsulating data manipulation • Avoiding the hardcoding of literals • Grouping together logically related functionality • Caching session-static data to improve application performance Distributed Database Applications

  47. Homework • Given the package code and the database table. • List the sequence of procedure and function calls initiated for the PL/SQL command. • What is the output for the PL/SQL command? • Add exception handling in function. • Lab activities Distributed Database Applications

More Related