Oracle PL/SQLOverview of PL/SQLPL/SQL BlockSample Program OneSample Program One (cont.)Execute PL/SQL ProgramsDeclarationPL/SQL Data TypesRecord Type & Row TypeSimple StatementsConditions Involving NullFor LoopSample Program TwoSample Program Two (cont.)ExceptionsSlide 17User-Defined ExceptionsUser-Defined Exceptions (cont.)ProcedureProcedure (cont.)Slide 22FunctionFunction (cont.)Slide 25Stored Procedures and FunctionsA Sample Stored ProcedureCompilation & ExecutionOracle PL/SQLJohn OrtizLecture 13 Oracle PL/SQL (1) 2Overview of PL/SQLOracle's Procedural Language extension to SQL.Support many programming language features. If-then-else, loops, subroutines. Program units written in PL/SQL can be compiled and stored in Oracle DB. Compiled subroutines can be used in SQL.PL/SQL code is portable across all operating systems that support Oracle.PL/SQL does not support DDL.Lecture 13 Oracle PL/SQL (1) 3PL/SQL Block A PL/SQL block contains logically related SQL and PL/SQL statements. Three sections in a typical PL/SQL block: declare type, variable, function, procedure, …begin SQL & PL/SQL statementsexception exception handlingend/ /* program end */Lecture 13 Oracle PL/SQL (1) 4Sample Program OnePrint a message indicating if student 1234 is a CS major.declare student_name Students.Name%TYPE; student_major Students.Major%TYPE;begin select Name, Major into student_name, student_major from Students where SID = `1234';Lecture 13 Oracle PL/SQL (1) 5Sample Program One (cont.) if (student_major = ‘CS’) then dbms_output.put_line(‘A CS student.’); else dbms_output.put_line(‘Not a CS student.’); end if; end;/ /* end each PL/SQL program with / */Lecture 13 Oracle PL/SQL (1) 6Execute PL/SQL ProgramsSave the program in a file: sample1.sqlExecute the program in SQL*Plus SQL> start sample1Enable output to the screen: SQL> set serveroutput on or place “set serveroutput on” at the beginning of the PL/SQL program.Lecture 13 Oracle PL/SQL (1) 7Declaration declare average_GPA number(3,2); no_of_depts constant number(2) := 23; no_of_students number(5) not null := 12000; employee_name varchar2(30); state_code char(2); done boolean default true;declare one variable at a time.Lecture 13 Oracle PL/SQL (1) 8PL/SQL Data Types Built-in Simple Types:binary_integer: -231-1 to 231-1natural: 0 to 231positive: 1 to 231long: character string up to 32,760 bytesboolean: boolean type (true, false, null) number(n,m), char(n), varchar2(n) , date : same as their counterparts in SQL%type: using an existing column type. v_student_gpa Students.gpa%typeLecture 13 Oracle PL/SQL (1) 9Record Type & Row TypeDefine a new record type. type course_record_type is record (course_id Courses.cid%type; title Courses.title%type; credit_hours number); course_record course_record_type; %rowtype: use an existing tuple type. one_student Students%rowtype;Use “.” to reference record fields course_record.title = ‘Database I’;Lecture 13 Oracle PL/SQL (1) 10Simple Statements Null Statement: null;Assignment Statement: i := i + 1; name := ‘Smith’; Conditional Statement: if condition1 then statement1; elsif condition2 then statement2; else statement3; end if;Both elsif and else are optional.Lecture 13 Oracle PL/SQL (1) 11Conditions Involving NullTesting for null or not null as in SQL if (course_title is null) then …Expressions involving null will result a null. (s1.gpa > s2.gpa) results a null if s1.gpa is nullIf a condition results false or null, the corresponding statement will not be evaluated.Truth values involve null:A and B: null if either A or B is nullA or B: null only if both A and B are nullNot A: null if A is nullLecture 13 Oracle PL/SQL (1) 13For Loop Syntax: for variable in [reverse] low..high loop statements; end loop; for x in -10..10 loop … end loop; for x in reverse -10..10 loop … end loop; No need to declare loop variable explicitly.Loop variable is not accessible outside loop.Modifying loop variable will cause an error.Lecture 13 Oracle PL/SQL (1) 14Sample Program TwoAs long as the total company payroll is less than $5 million, increase employee’s salary by 2%. declare company_payroll number;begin select sum(salary) into company_payroll from Employees;Lecture 13 Oracle PL/SQL (1) 15Sample Program Two (cont.) while company_payroll < 5000000 loop update Employees set salary = salary * 1.02; select sum(salary) into company_payroll from Employees; end loop;end;/Lecture 13 Oracle PL/SQL (1) 16ExceptionsAn exception is any error that occurs during program execution. exception /* exception section */ when dup_val_on_index then dbms_output.put_line(sqlcode || ‘--’ || sqlerrm); end;Output message if the exception occurs: -1--ORA-00001: unique constraint violatedLecture 13 Oracle PL/SQL (1) 17ExceptionsSyntax: exception: when exception_name then error-handling-code; … when others then error-handling-code;Pre-defined exceptions: invalid_cursor, too_many_rows, dup_val_on_index, no_data_found, etc.Lecture 13 Oracle PL/SQL (1) 18User-Defined Exceptionsdeclare /* declare section */ /* Must be explicitly declared */ invalid_gpa exception; begin /* execution section */ if (gpa < 0 or gpa > 4.0) then /* Must be raised by user */ raise invalid_gpa; end if;Lecture 13 Oracle PL/SQL (1) 19User-Defined Exceptions (cont.)exception /* exception section */ when invalid_gpa then dbms_output.put_line(‘GPA value is invalid.’);end;/It is a good practice to handle all exceptions explicitly in the exception section.Can improve reliability and readabilityLecture 13 Oracle PL/SQL (1) 20Procedure Assume a Customer relation. Write a PL/SQL program to retrieve a given customer. Report an exception if not found. set serveroutput ondeclare v_cid customers.cid%type; v_cname customers.cname%type; v_city customers.city%type; status boolean;Lecture 13 Oracle PL/SQL (1) 21Procedure (cont.) procedure get_customer( cust_id in customers.cid%type, cust_name out customers.cname%type, cust_city out customers.city%type, status out boolean) is begin select cname, city into cust_name, cust_city from customers where cid = cust_id; status := true; exception /* optional */ when no_data_found then status := false; end; /* procedure */Lecture 13 Oracle PL/SQL