distributed database applications
Post on 25-Feb-2016
31 Views
Preview:
DESCRIPTION
TRANSCRIPT
Distributed Database Applications
COSC 5050Week Five
Jiangping WangWebster University Distributed Database Applications
OutlineManaging PL/SQL CodeExceptions and Error HandlingTransaction ManagementPackages
Jiangping WangWebster University Distributed Database Applications
Data DictionaryUSER_
USER_DEPENDENCIESUSER_ERRORSUSER_OBJECTSUSER_SOURCEUSER_TRIGGERSUSER_ARGUMENTS
Jiangping WangWebster University Distributed Database Applications
Data DictionarySELECT object_type, object_name, statusFROM user_objectsWHERE object_type IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE', 'TRIGGER' )ORDER BY object_type, status, object_name;
SELECT TEXT FROM USER_SOURCE WHERE NAME = 'ADD_EMPLOYEE';
DESC ADD_EMPLOYEE
Jiangping WangWebster University Distributed Database Applications
Debug PL/SQL ProgramsRecompile invalid codeSource code debuggerAnalyzingUnit testshow err
Jiangping WangWebster University Distributed Database Applications
Exception HandlerSystem exception
An exception that is defined by Oracle and is usually raised by the PL/SQL runtime engineWith name or numberNO_DATA_FOUND
User defined exceptionsAn exception that is defined by the programmer and is therefore specific to the application
Jiangping WangWebster University Distributed Database Applications
ExceptionDECLARE 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;/
Jiangping WangWebster University Distributed Database Applications
Exceptioncreate or replace function get_department( ssn_in in employee.ssn%type) return department.dept_name%typeis 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;/
Jiangping WangWebster University Distributed Database Applications
Exception
procedure delete_department (department_id_in in number)isbegin 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;
procedure delete_department (department_id_in in number) is begin delete from department where dept_num = department_id_in;end;
SQLCODE is a built-in function that returns the number of the last error raised.
Jiangping Wang
Declare ExceptionDeclare 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
Webster University Distributed Database Applications
Jiangping WangWebster University Distributed Database Applications
Declare Exceptionprocedure 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 exceptionthen
…when invalid_company_idthen
…when negative_balancethen
…
end;
Jiangping WangWebster University Distributed Database Applications
Declare Exceptionprocedure 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;
Jiangping WangWebster University Distributed Database Applications
Associate Exception Nameprocedure delete_department(department_id_in in number)is
still_have_employees exception;pragma exception_init (still_have_employees, -2292);
begindelete 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.
Jiangping Wang
Associate Exception NameEXCEPTION_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 instead0 or any positive number besides 100A negative number less than -1000000
Webster University Distributed Database Applications
Jiangping Wang
Associate Exception NameCentralize EXCEPTION_INIT into packages
Webster University Distributed Database Applications
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);
Jiangping WangWebster University Distributed Database Applications
Raise ExceptionOracle might raise the exception when it detects an errorProgrammer might raise an exception with the RAISE statement
RAISE exception_name;RAISE package_name.exception_name;RAISE; -- propagate out
Jiangping Wang
Raise Exception
Webster University Distributed Database Applications
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;
Jiangping Wang
Handling ExceptionException handlers are structured much like a conditional CASE statement
Webster University Distributed Database Applications
Property DescriptionEXCEPTION WHEN NO_DATA_FOUND THEN executable_statements1;
If the NO_DATA_FOUND exception is raised, then execute the first set of statements.
WHEN payment_overdue THEN executable_statements2;
If the payment is overdue, then execute the second set of statements
WHEN OTHERS THEN executable_statements3; END;
If any other exception is encountered, then execute the third set of statements.
Jiangping WangWebster University Distributed Database Applications
PROCEDURE add_employee ( ssn in varchar2, fname in varchar2, lname in varchar2, dept_num in number, code in number, sup_ssn in varchar2)ISBEGIN 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;
Handling Exception
Jiangping WangWebster University Distributed Database Applications
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;/
Jiangping WangWebster University Distributed Database Applications
Named System ExceptionP. 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)
Jiangping WangWebster University Distributed Database Applications
SQLCODE and SQLERRMBuilt-in Error FunctionsSQLCODE
Return the oracle error numberSQLERRM
Return the oracle message text associated with the errorbegin
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;
Jiangping WangWebster University Distributed Database Applications
SQLCODE and SQLERRMPROCEDURE DELETE_COMPANY (COMPANY_ID IN NUMBER)ISBEGIN 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;
Jiangping WangWebster University Distributed Database Applications
DML in PL/SQLINSERT
INSERT INTO table VALUES (val1, …);INSERT INTO table AS SELECT …;
UPDATEUPDATE table SET col1 = val1 WHERE …
DELETEDELETE FROM table WHERE …
Cursor attributes for DML operationsSQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN
Jiangping Wang
Use SQL%FOUND to determine if your DML statement affected any rows
DML in PL/SQL
Webster University Distributed Database Applications
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;
Jiangping Wang
Use SQL%ROWCOUNT when you need to know exactly how many rows were affected by your DML statement
DML in PL/SQL
Webster University Distributed Database Applications
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;
Jiangping WangWebster University Distributed Database Applications
RETURNING ClauseUse returning clause with DML and retrieve information directly into variables
Declare l_name employee.lname%type;l_code employee.code%type;
Beginfor rec in (select * from employee)loop
update employeeset code = rec.code*1.10where ssn = rec.ssn
returning code, lnameinto l_code, l_name;
dbms_output.put_line('New code for ' || l_name || ' = ' || l_code);
end loop;End;/
Jiangping WangWebster University Distributed Database Applications
Use Records in Insert and Update
procedure add_employee(emp_in in employee%rowtype)isbegin 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;
Jiangping Wang
Using records with the RETURNING clause
Use Records in Insert and Update
Webster University Distributed Database Applications
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;
Jiangping WangWebster University Distributed Database Applications
Transaction ManagementCommit – save all outstanding changesRollback – erase all outstanding changesSavepoint
SAVEPOINT savepoint_name;Rollback to savepoint
ROLLBACK TO savepoint_name;Lock table
LOCK TABLE emp, dept IN SHARE MODELOCK TABLE emp IN ROW EXCLUSIVE MODE
Jiangping WangWebster University Distributed Database Applications
Autonomous Transactionprocedure add_emp(…)is begin
update …;update …;savepoint start_add;insert …;commit;
exceptionwhen othersthen rollback to start_add; write_log
(sqlcode, sqlerrm);end;
procedure write_log(…)is
pragma autonomous_transaction;begin
insert into log values (code, text, user, sysdate);
commit;end;
MT begins
MT ends
MT suspends AT begins
AT ends
MT resumes
Jiangping Wang
Autonomous TransactionDefine 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 contextThe block becomes an independent transaction
Webster University Distributed Database Applications
Jiangping WangWebster University Distributed Database Applications
PackageA package is a grouping or packaging of PL/SQL code elementsPackage specification
Definition or specification of all the publicly available elements in the package
Package bodyCode required to implement elements defined in the package specification
Jiangping WangWebster University Distributed Database Applications
Favorites_pkg Specification
Favorites_pkg (p. 625)CREATE OR REPLACE PACKAGE favorites_pkg AUTHID CURRENT_USERIS 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;/
Jiangping WangWebster University Distributed Database Applications
Favorites_pkg Body
Body (p.627)CREATE OR REPLACE PACKAGE BODY favorites_pkgIS 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;
Jiangping WangWebster University Distributed Database Applications
Package SpecificationCREATE OR REPLACE PACKAGE EMPLOYEE_PKGAS 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;
Jiangping WangWebster University Distributed Database Applications
Package BodyCREATE OR REPLACE PACKAGE BODY EMPLOYEE_PKGAS 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;
Jiangping Wang
Calling Packaged ElementsPackage is a container for code elementsRun or reference elements in a package
From outside of that package, use dot notation
package_name.element_nameWithin the package (specification or body), no need to include the name of the package
Webster University Distributed Database Applications
Jiangping WangWebster University Distributed Database Applications
Pets_inc PackageCREATE OR REPLACE PACKAGE pets_incIS 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;
Jiangping WangWebster University Distributed Database Applications
Pets_inc PackageCREATE 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;/
Jiangping WangWebster University Distributed Database Applications
Pets_inc Packagedeclare
c_pet constant pet.id%type:=1099;v_net_appointment date;
beginif pets_inc.max_pets_in_facility > 100then
open pets_inc.pet_cur(c_pet);else
v_next_appointment:=pets_inc.next_pet_shots(c_pet);end if;
exceptionwhen pets_inc.pet_is_sickthen
pets_inc.set_scheducle(c_pet);end;
Jiangping WangWebster University Distributed Database Applications
Package DataPackage level dataPersist for the session
Public and private dataState of the packaged cursors persists for the session
Open/fetch/close it in different programClose packaged cursor promptly
Jiangping Wang
Packaged Cursors
Webster University Distributed Database Applications
CREATE OR REPLACE PACKAGE book_infoIS 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;
Jiangping Wang
Packaged Cursors
Webster University Distributed Database Applications
CREATE OR REPLACE PACKAGE BODY book_infoIS 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;
Jiangping Wang
Packaged Cursors
Webster University Distributed Database Applications
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;
Jiangping WangWebster University Distributed Database Applications
Use of PackagesEncapsulating data manipulationAvoiding the hardcoding of literalsGrouping together logically related functionalityCaching session-static data to improve application performance
Jiangping WangWebster University Distributed Database Applications
HomeworkGiven 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
top related