Oracle PL/SQL Programming

1. Oracle PL/SQL Programming • Review of Oracle data types and sqlplus commands • Creating PL/SQL Blocks • Declare, Execution, Exception Sections • Implicit and Explicit Cursors • Creating PL/SQL Procedures • Creating PL/SQL Functions • DBMS_OUTPUT Package and Debugging • PL/SQL Built-in Functions • The DECODE function • Creating Triggers

2. Data Types • VARCHAR2(size) - variable length character string having a maximum of size bytes (up to 4000) • NUMBER - numeric column with space for 40 digits, plus space for a decimal point and sign. • NUMBER(size) - numeric column of specified size • NUMBER(size, d) - d digits after the decimal point

3. More Data Types • CHAR(size) - fixed length character data. Maximum size is 2000. Default size is 1. • DATE - valid dates range from 1/1/4712 BC to 12/31/4712 AD • DECIMAL - same as NUMBER, does not accept arguments such as size or d • FLOAT - same as NUMBER • INTEGER - same as NUMBER, does not accept d as argument

4. More Data Types • LONG - character data of variable size up to 2GB, only one LONG column can be defined per table. LONG columns cannot be used in WHERE clauses or indexes. Other restrictions also apply. • LONG RAW - raw binary data, otherwise same as LONG • LONG VARCHAR - same as LONG • BLOB - binary large object, up to 4GB • CLOB - character large object, up to 4GB • …..

5. Oracle sqlplus Basics SQL*Plus commands allow a user to manipulate and submit SQL statements. Specifically, they enable a user to: • Enter, edit, store, retrieve and run SQL statements • Enter, edit, store, retrieve and run PL/SQL blocks • List the column definitions for any table • Format, perform calculations on, store, and print query results in the form of reports • Set different options

6. Oracle sqlplus Commands • / - Execute the current SQL statement in the buffer - same as RUN • ACCEPT - Accept a value from the user and place it into a variable ACCEPT cname PROMPT ‘Enter Company Name: ’ SELECT … WHERE Company = ‘&cname’; • AUTOTRACE - Trace the execution plan of the SQL statement and gather statistics • BREAK - Set the formatting behavior for the output of SQL statements

7. Oracle sqlplus Commands • BTITLE - Place a title on the bottom of each page in the printout from a SQL statement • COLUMN - Change the appearance of an output column from a query COLUMN sid HEADING “Student ID” FORMAT 9999 COLUMN price FORMAT $9,999.99 • COMPUTE - Does calculations on rows returned from a SQL statement

8. Oracle sqlplus Commands • CONNECT - Connect to another Oracle database or to the same Oracle database under a different user name • COPY - Copy data from one table to another in the same or different databases • DESCRIBE - List the columns with data types of a table • EDIT - Edit the current SQL statement in the buffer using an external editor such as vi or emacs

9. Oracle sqlplus Commands • EXIT - Exit the SQL*Plus program • HELP - Obtain help for a SQL*Plus command (In some installations) • HOST - Drop to the operating system shell

10. Oracle sqlplus Commands • QUIT - Exit the SQL*Plus program • REMARK - Place a comment following the REMARK keyword • RUN - Execute the current SQL statement in the buffer • SET - Set a variable to a new value SET LINESIZE 80 SET PAGESIZE 25 ...

11. Oracle sqlplus Commands • SHOW - Show the current value of a variable • SPOOL - Send the output from a SQL statement to a file • START - Load a SQL statement located in a script file and then run that SQL statement • TIMING - Used to time the execution of SQL statements for performance analysis • TTITLE - Place a title on the top of each page in the printout from a SQL statement • UNDEFINE - Delete a user defined variable

12. PL/SQL Overview • PL/SQL is Oracle’s Procedural Language superset of SQL. It can be used to: • Codify business rules through the creation of stored procedures. • Trigger database events to occur • Add programming logic to the execution of SQL commands. • SQL is considered as a 4GL which specifies only the WHAT but not the HOW. PL/SQL is a 3GL that adds procedural constructs to SQL processing.

13. PL/SQL Blocks • PL/SQL code is grouped into blocks where each block has: • a DECLARE section • an EXECUTABLE section • an EXCEPTION section • A block can be an anonymous block, a function block or a procedure block. • Single line comments: Any characters followed by two dashes (--) in a line. • Multiline comments: /* … */

14. A Sample UNIVERSITY database • A sample database containing the following relations to illustrate PL/SQL programming: • STUDENTS(id, fname, lname, major, credits) • ROOMS(rid, building, roomno, seats, desc) • CLASSES(dept, course, desc, maxenroll, curenroll, credits, rid) • REGISTERED(sid, dept, course, grade) (Assume a sequence STU_SEQ has been created)

15. Creating a Sequence • CRETE SEQUENCE stu_seq increment by 1 start with 1000; • A sequence guarantees a unique id making it easier to define a table in the second normal form. • You can use the NEXTVAL and CURVAL of a sequence to access the next value or the current value in a sequence.

16. declare v_major varchar2(10) := ‘History’; v_fname varchar2(10) := ‘Scott’; v_lname varchar2(10) := ‘Mason’; begin Update STUDENTS set major = v_newmajor where fname = v_fname and lname = v_lname; if SQL%NOTFOUND then INSERT INTO students (id, fname, lname, major) VALUES (stu_seq.nextval, v_fname, v_lname, v_major); end if; end; . /

17. The Declare Section • The declaration section begins a PL/SQL block. It begins with the “declare” keyword, followed by a list of variable and cursor definitions. • Declaration syntax for variables: <var_name> [constant] <data_type> [:= <initial_val>]; • Declaration syntax for anchored variables: <var_name> <object>%type [:= <initial_val>]; <var_name> <object>%rowtype [:= <initial_val>]; where object can be a database object.

18. Declaration Examples declare amount integer(10); name varchar2(25); hiredate date; commission real(5,2) := 12.5; pi constant number(9,7) := 3.1415926; cursor emp_cursor is select * from employee; emp emp_cursor%ROWTYPE; ename employee.name%type;

19. Cursor Variable • A cursor is used to process multiple rows retrieved from the database. Using a cursor, your program can step through the set of rows returned one at a time, processing each one.

20. Variable Declaration • PL/SQL variables can be of the same type as database columns • Varchar2(20), date, number(5), … • Additional PL/SQL specific types: • Binary_integer (used to store signed integer values) • Boolean • User defined types (objects)

21. The Executable Section • The executable section begins with the keyword “begin”. Executable statements include: • Assignments • Programming constructs such as IF … THEN … ELSE … END IF, WHILE, FOR, GOTO … • SQL data manipulation statements • OPEN and CLOSE cursor statements • variable := expression; (an assignment statement)

22. Conditional Control • if (…) then … end if; • if (…) then … else … end if; • if (…) then … elsif (…) then … ... else … end if; • For example: if SQL%NOTFOUND then INSERT INTO STUDENTS (id, fname, lname, major) VALUES (stu_seq.nextval, v_fname, v_lname, v_major); end if;

23. Cursor Attributes • A cursor has 4 attributes each returning a value that can be used in expressions. • %FOUND: a boolean attribute, TRUE if the previous FETCH returned a row, FALSE otherwise. • %NOTFOUND: opposite of %FOUND. • %ISOPEN: TRUE if the associated cursor is open, FALSE otherwise. • %ROWCOUNT: the number of rows fetched by the cursor so far.

24. Implicit Cursor vs Explicit Cursor • In general, %FOUND is appended to the name of an explicitly defined cursor to reference the value. • These explicit cursors are used to process SELECT statements that return more than one row. • INSERT, UPDATE, DELETE and single row SELECT … INTO statements use implicit cursors for processing. In this case, %FOUND is appended to SQL to access the value of the attribute.

25. Loop Control • loop …... end loop; • Make sure you have an “exit when …” statement in the loop. • for count in 1..7 loop …… end loop; • count is declared as an variable, does not need to be initialized or incremented. • for rad_val in rad_cursor loop …… end loop; • rad_cursor must be declared as a cursor. rad_val must be declared as an anchored variable. Do not need to open or close the cursor. Do not need to fetch explicitly. • while (…) loop …… end loop;

26. Using SQL in PL/SQL • The only SQL statements allowed in a PL/SQL program are DML and transaction control statements. • DML: SELECT, INSERT, UPDATE, DELETE • Transaction Control: COMMIT, ROLLBACK, SET TRANSACTION, SAVEPOINT

27. Transaction Control COMMIT; SET TRANSACTION; Update ACCOUNTS set blnce = blnce – t_amt where actno = from_acct; Update ACCOUNTS set blnce = blnce + t_amt where actno = to_acct; Savepoint A; Insert into …..; if (condition) then rollback to savepoint A; Savepoint B; more SQL commands … if (condition2) then rollback to savepoint B; COMMIT;

28. The Exception Handling Section • When user-defined or system-related exceptions occur, the control of the PL/SQL block is transferred to the exception handling section which always begin with the “exception” keyword. The exception handling section must precede the “end” keyword that terminates the executable section.

29. Sample Exceptions • CURSOR_ALREADY_OPEN • DUP_VAL_ON_INDEX (during insert) • INVALID_CURSOR (cursor not open) • INVALID_NUMBER (e.g. ’34+some’) • NO_DATA_FOUND (select .. Into returns no rows) • TOO_MANY_ROWS (select returns more than 1 row) • VALUE_ERROR (assignment or select into) • ZERO_DIVIDE • … • OTHERS

30. declare x number := 5; y number; z number; begin z := sqrt(x); y := 1 / z; z := x + y; Exception when others then handle_error(…); end; . /

31. A PL/SQL Block • declare • <declaration section> • begin • <executable section> • exception • <exception handling section> • end

32. Different Kinds of Blocks • Anonymous block: constructed dynamically and executed only once. • Named block: anonymous blocks with a label that gives the block a name. • Subprograms: procedures and functions that are stored in database. Typically executed many times explicitly via a call. • Triggers: named blocks stored in the database. Executed implicitly when the triggering event occurs.

33. Assume These Database Tables in the Programming Examples • customers (cno, cname, street, zip, phone) • zipcodes (zip, city) • radius_vals (radius) • areas (radius, area) • worker (name, age, lodging)

34. PL/SQL Programming - Anonymous Block declare pi constant NUMBER(9,7) := 3.1415926; radius INTEGER(5); area NUMBER(14,2); begin radius := 3; area := pi * power(radius, 2); INSERT INTO areas VALUES (radius, area); end; . /

35. <<ins_one_rad_area>> declare pi constant NUMBER(9,7) := 3.1415926; area NUMBER(14,2); cursor rad_cursor is SELECT * FROM radius_vals; rad_val rad_cursor%ROWTYPE; begin open rad_cursor; fetch rad_cursor INTO rad_val; area := pi * power(rad_val.radius, 2); INSERT INTO areas VALUES (rad_val.radius,area); close rad_cursor; end; . /

36. <<Ins_Many_Rad_Area>> declare pi constant NUMBER(9,7) := 3.1415926; area NUMBER(14,2); cursor rad_cursor is SELECT * FROM radius_vals; rad_val rad_cursor%ROWTYPE; begin for rad_val in rad_cursor loop area := pi * power(rad_val.radius, 2); INSERT INTO areas VALUES (rad_val.radius,area); end loop; end; . /

37. Cursors and Bind Variables Declare v_sid students.id%type; v_fname students.fname%type; v_lname students.lname%type; v_major students.major%type := ‘History’; cursor studentCur is select id, fname, lname from students where major = v_major; Begin open studentCur; loop fetch studentCur into v_sid, v_fname, v_lname; exit when studentCur%NOTFOUND; end loop; close studentCur; End;

38. Parameterized Cursors Declare cursor studentCur(p_major students.major%type) is select id, fname, lname from students where major = p_major; Begin open studentCur(‘Computer Science’); loop fetch studentCur into v_sid, v_fname, v_lname; exit when studentCur%NOTFOUND; end loop; close studentCur; End;

39. Creating a PL/SQL Procedure CREATE PROCEDURE new_worker (pname IN VARCHAR2) AS (declaration if any) BEGIN INSERT INTO worker (name, age, lodging) VALUES (pname, null, null); END; / EXECUTE new_worker(‘John Smith’);

40. Create procedure insert_area (rad_val in varchar2, area_val in varchar2) as begin insert into areas values (to_number(rad_val),to_numer(area_val)); exception when INVALID_NUMBER then dbms_output.put_line(‘numeric error’); dbms_output.put_line( sqlcode || ‘--’ || sqlerrm); when OTHERS then dbms_output.put_line(‘other error’); dbms_output.put_line( sqlcode || ‘--’ || sqlerrm); end; /

41. Formal Parameters • Oracle PL/SQL program parameters have modes: • IN: input parameter (default) • OUT: output parameter • INOUT: input/output parameter • You can also specify a default value for an input parameter

42. Create or replace procedure AddNewStudent( p_fname students.fname%type; p_lname students.lname%type; p_major students.major%type defualt ‘Economics’) as begin INSERT INTO students (id, fname, lname, major) VALUES (stu_seq.nextval,p_fname,p_lname,p_major); commit; end; /

43. Actual Parameters Using positional notation (only the last parameters can have default values): Begin AddNewStudents(‘Beth’, ‘Woods’); End Using named notation (any parameter can adopt the default value(s)): execute AddNewStudents(p_fname => ‘Beth’, p_lname => ‘Woods’);

44. Creating a PL/SQL Function CREATE OR REPLACE FUNCTION get_city (cnum IN customers.cno%TYPE) RETURN zipcodes.city%TYPE AS rcity zipcodes.city%TYPE; BEGIN SELECT city INTO rcity FROM customers, zipcodes WHERE cno = cnum and customers.zip = zipcodes.zip; RETURN (rcity); END; /

45. Create function almostfull ( p_dept classes.dept%type, p_course classes.course%type) Return boolean is v_ccount number, v_mcount number, v_return boolean, v_almostfull constant number := 95; begin select curenroll, maxenroll into v_ccount, v_mcount from classes where dept = p_dept and course = p_course; if (v_ccount / v_mcount * 100) > v_almostfull then v_return := TRUE; else v_return := FALSE; end if; return v_return; End;

46. declare cursor classCur is select dept, course from classes; classRec classCur%rowtype; begin for classRec in classCur loop if almostfull(classRec.dept, classRec.course) then dbms_output.put_line( classRec.dept || ‘ ‘ || classRec.course || ‘ ‘ || ‘is 95% full.’); end if; end loop; End;

47. PL/SQL Programming - Debugging • Sqlplus command: show errors Will display the line and column numbers of each error as well as the text of each error message While debugging, it is best to deal with one function/procedure at a time • The USER_ERRORS table SELECT line, position, text FROM USER_ERRORS WHERE name = ‘funname’ AND type = ‘FUNCTION’;

48. PL/SQL Programming - Debugging • The USER_SOURCE table SELECT text FROM USER_SOURCE WHERE name = ‘new_worker’ AND type = ‘PROCEDURE’ ORDER BY line;

49. Using the DBMS_OUTPUT package • The dbms_output package itself does not contain any mechanism for printing. It simply implements a first in, first out data structure. set serveroutput on size 100000 • Try these dbms_output procedures dbms_output.new_line; dbms_output.put(emp_rec.eno); (you can put any varchar2, number or date argument) dbms_output.put_line(‘Total sales for employee ’ || ename || ‘ is ’ || total ); • DBMS_OUTPUT is not designed for reporting.

50. Useful String Functions Set (e.g. ‘.+*’) represents a set of characters • rpad(string, length[, set]): right pad a string using a given set of characters. Set defaults to the blank character. • lpad(…) left pad a string • rtrim(string[, set]): trim off the given set of characters from the right. Set defaults to the blank character. • ltrim(…) trim off from the left • length(string): returns the length of the string