1 / 24

Understanding SQL Transactions in Procedures

Learn how to control outcomes using COMMIT and ROLLBACK in SQL procedures, with examples of successful and unsuccessful runs. Explore handling exceptions for better control and feedback.

julian-anthony
Download Presentation

Understanding SQL Transactions in Procedures

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. SQL programming

  2. Transactions in Procedures • On the surface – very easy. • If everything goes well (at end): • COMMIT: • If things go badly (at end): • ROLLBACK; • Problem is controlling bad outcomes: • Handling exceptions • Giving useful feedback

  3. Simple example – with Commit SQL> CREATE OR REPLACE PROCEDURE add_patient (pat_id in char, pat_name in char, pat_address in char, pat_dobirth in date, pat_regdate in date) 2 AS 3 BEGIN 4 insert into patient(pid,pname,address,dobirth,date_reg) values(pat_id,pat_name,pat_address,pat_dobirth,pat_regdate); 5 DBMS_OUTPUT.PUT_LINE ('Insert attempted'); 6 COMMIT; 7 END; 8 / Procedure created.

  4. Review of Assignment Procedure • Asked to add a procedure to add vaccination data • Generate: • one successful run • three unsuccessful runs • Here review closely results of run

  5. ADD_VACC procedure SQL> CREATE OR REPLACE PROCEDURE add_vacc (pat_id in char, vis_vdate in date, vis_act in number, vac_vacc in char) 2 AS 3 BEGIN 4 insert into vaccinations(pid,vdate,action,vaccinated) values(pat_id,vis_vdate,vis_act,vac_vacc); 5 DBMS_OUTPUT.PUT_LINE ('Insert attempted'); 6 END; 7 / Procedure created.

  6. Successful Run 1a SQL> execute add_vacc('2','16-dec-1999',3,'cholera'); PL/SQL procedure successfully completed. SQL> select * from vaccinations 2 where pid = '2' and action = 3; PID VDATE ACTION VACCINATED ------ --------- ---------- -------------------- 2 06-AUG-91 3 polio 2 16-DEC-99 3 cholera SQL> commit; Commit complete.

  7. Successful Run 1b • No error messages • Message ‘PL/SQL procedure successfully completed’ is significant. It means: • Any exception raised during run has been properly handled • Does not necessarily mean data has been added successfully • COMMIT should have been last line in procedure • Here user has made decision to commit

  8. Unsuccessful Run 1a SQL> execute add_vacc('2','16-dec-1999',1,'cholera'); BEGIN add_vacc('2','16-dec-1999',1,'cholera'); END; * ERROR at line 1: ORA-00001: unique constraint (CGNR1.PKVAC) violated ORA-06512: at "CGNR1.ADD_VACC", line 4 ORA-06512: at line 1

  9. Unsuccessful Run 1b • Error message returned: • ORA-00001 indicates non-unique primary key value • Text message ‘unique constraint violated’ spells out nature of problem • CGNR1.PKVAC is name of constraint in CREATE TABLE definition for Vaccinations • constraint pkvac primary key (pid,vdate,action) • Note no message about successful completion. • Does not necessarily mean unsuccessful addition • Means that exception raised in INSERT operation has not been handled within the procedure

  10. Unsuccessful Run 2a SQL> execute add_vacc('2','17-dec-1999',1,'cholera'); BEGIN add_vacc('2','17-dec-1999',1,'cholera'); END; * ERROR at line 1: ORA-02291: integrity constraint (CGNR1.SYS_C0080698) violated - parent key not found ORA-06512: at "CGNR1.ADD_VACC", line 4 ORA-06512: at line 1

  11. Unsuccessful Run 2b • Error message returned: • ORA-02291 indicates foreign key entered does not match a primary key value (in visits) • Text message ‘parent key not found’ spells out nature of problem • foreign key(pid,vdate) REFERENCES visits(pid,vdate); • CGNR1.SYS_C0080698 is name of constraint • Named constraints give more information • Again no message about successful completion • As exception not handled

  12. Attempted unsuccessful run SQL> execute add_vacc('2','16-dec-1999','4','cholera'); PL/SQL procedure successfully completed. SQL> select * from vaccinations 2 where pid = '2' and action = 4; PID VDATE ACTION VACCINATED ------ --------- ---------- -------------------- • 16-DEC-99 4 cholera Worked as ‘4’ char value entered for numeric attribute action was type cast (automatically) to a number

  13. Unsuccessful Run 3a SQL> execute add_vacc('2','16-dec-1999','4',cholera); BEGIN add_vacc('2','16-dec-1999','4',cholera); END; * ERROR at line 1: ORA-06550: line 1, column 38: PLS-00201: identifier 'CHOLERA' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored

  14. Unsuccessful Run 3b • Error message returned: • ORA-06550 indicates non-declared identifier • Parameter value CHOLERA is not in quotes • Therefore taken as variable • Not declared to system • Again no message about successful completion • As exception not handled

  15. Exception Handling PL/SQL • Essential part of any program • Particularly needed for updates • open-ended nature of user inputs • But also needed for searches • e.g. may not find any matching data • An exception is raised when an operation: • fails to perform normally • A non-handled exception leads to program failure

  16. Exceptions Raised • With input particularly • Cannot specify all Oracle error codes in advance • Too many codes to specify • Some rule exceptions though can be emphasised • Need specific exceptions • And general (catch-all) exceptions

  17. Complete PL/SQL procedure • CREATE OR REPLACE PROCEDURE proc_name (parameters) AS • [DECLARE] local_vars • BEGIN • executable_code • EXCEPTION exception_code • END • /

  18. Explanation • Upper case -- literal (as is) • Lower case (to be substituted) • [DECLARE] omitted in procedures but part of full definition for PL/SQL • Executable_code • SQL commands, assignments, condition checking, text output, transactions • Exception_code: • event handling, transactions • proc_name is procedure name • local_vars are variables declared for use within procedure (standard SQL types + Boolean types)

  19. Example Procedure - part 1 CREATE OR REPLACE PROCEDURE add_patient (pat_id in char, pat_name in char, pat_address in char, pat_dobirth in date, pat_regdate in date) AS pid_too_high exception; PRAGMA EXCEPTION_INIT(pid_too_high,-20000); BEGIN insert into patient(pid,pname,address,dobirth,date_reg) values(pat_id,pat_name,pat_address,pat_dobirth,pat_regdate); DBMS_OUTPUT.PUT_LINE ('Insert attempted'); IF pat_id > '500' THEN RAISE pid_too_high; END IF; COMMIT;

  20. Example Procedure - part 2 EXCEPTION WHEN pid_too_high THEN DBMS_OUTPUT.PUT_LINE ('pid too high'); ROLLBACK; END; /

  21. Explanation 1 • pid_too_high exception; • variable pid_too_high of type exception (value true or false) • PRAGMA EXCEPTION_INIT(pid_too_high,-20000); • instruction to compiler • enables launch of further transaction to handle exception pid_too_high • IF pat_id > '500' THEN RAISE pid_too_high; END IF; • IF .. THEN … END IF construction • enforces a business rule that pid <= 500 by • raising exception pid_too_high when this state occurs

  22. Explanation 2 • EXCEPTION • opens exception handling part of procedure • WHEN … THEN …; • defines actions when a particular exception occurs

  23. Flow of Action 1 • If no exception raised • insert is performed • commit takes place • procedure terminates with ‘successful’ message • If specific exception for business rule raised • insert is performed • exception pid_too_high is raised in IF code • execution of main code immediately finishes • code in EXCEPTION section after WHEN pid_too_high is executed • including rollback • procedure terminates with ‘successful’ message

  24. Flow of Action 2 • If another exception raised (on insert e.g. primary key violation) • insert is not performed • exception is raised in procedure • execution of main code immediately finishes • As no further exception handlers are declared • procedure terminates with: • error reports • no ‘successful’ message • Need catch-all exception handlers

More Related