Distributed Database Applications

1. Distributed Database Applications COSC 5050 Week One

2. Outline • Introduction • Course overview • Oracle client environment • Data dictionary • Language fundamentals • Program control Distributed Database Applications

3. Introduction • Oracle database • PL/SQL • Accessing Oracle server • Database objects • DDL and DML statements Distributed Database Applications

4. Many Oracle applications are built using client-server architecture The Oracle database resides on the server PL/SQL is like any other programming language PL/SQL is not a stand-alone programming language PL/SQL is a part of the Oracle RDBMS PL/SQL Distributed Database Applications

5. PL/SQL • Highly structured, readable, accessible language • Standard and portable language • Embedded language • High-performance, highly integrated database language Distributed Database Applications

6. Advantages of PL/SQL Distributed Database Applications

7. The semicolon terminates CREATE, INSERT, SELECT, and DROP statements CREATE TABLE STUDENT ( FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(20) ); INSERT INTO STUDENT VALUES (‘JOHN’, ‘LUCKY’); SELECT FIRST_NAME, LAST_NAME FROM STUDENT; DROP TABLE STUDENT; SQL Example Distributed Database Applications

8. Page 7, 8, 9, 39 examples When Oracle reads a PL/SQL block, a semicolon marks the end of the individual statement within the block It is not a block terminator! The “/” executes the PL/SQL block PL/SQL Example Distributed Database Applications

9. Integration with SQL DECLARE l_book_count INTEGER; BEGIN SELECT COUNT(*) INTO l_book_count FROM books WHERE author LIKE '%Feuerstein, Steven%'; DBMS_OUTPUT.PUT_LINE( 'Steven have written (or co-written) ' || l_book_count || ' books.'); UPDATE books SET author = REPLACE (author, 'Steven', 'Stephen') WHERE author LIKE '%Feuerstein, Steven%'; END; Distributed Database Applications

10. Control and Conditional Logic CREATE OR REPLACE PROCEDURE pay_out_balance( account_id_in IN accounts.id%TYPE) IS l_balance_remaining NUMBER; BEGIN LOOP l_balance_remaining := account_balance (account_id_in); IF l_balance_remaining < 1000 THEN EXIT; ELSE apply_balance ( accont_id_in, l_balance_remaining); END IF; END LOOP; END pay_out_balance; Distributed Database Applications

11. When Things Go Wrong CREATE OR REPLACE PROCEDURE check_account(account_id_in IN accounts.id%TYPE) IS l_balance_remaining NUMBER; l_balance_below_minimum EXCEPTION; l_account_name accounts.name%TYPE; BEGIN SELECT name INTO l_account_name FROM accounts WHERE id = account_id_in; l_balance_remaining := account_balance (account_id_in); DBMS_OUTPUT.put_line ( 'Balance for ' || l_account_name || ' = ' || l_balance_remaining); IF l_balance_remaining < 1000 THEN RAISE l_balance_below_minimum; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN log_error (...); WHEN l_balance_below_minimum THEN log_error (...); RAISE; END; Distributed Database Applications

12. Creating a Stored Program CREATE OR REPLACE FUNCTION wordcount (str IN VARCHAR2) RETURN PLS_INTEGER AS words PLS_INTEGER := 0; len PLS_INTEGER := NVL(LENGTH(str),0); inside_a_word BOOLEAN; BEGIN FOR i IN 1..len + 1 LOOP IF ASCII(SUBSTR(str, i, 1)) < 33 OR i > len THEN IF inside_a_word THEN words := words + 1; inside_a_word := FALSE; END IF; ELSE inside_a_word := TRUE; END IF; END LOOP; RETURN words; END; Distributed Database Applications

13. Executing, Showing, and Dropping a Stored Program BEGIN DBMS_OUTPUT.PUT_LINE( 'There are ' || wordcount(CHR(9)) || ' words in a tab'); END; SELECT * FROM USER_OBJECTS; DESC wordcount SELECT TEXT FROM USER_SOURCE WHERE NAME = 'WORDCOUNT'; DROP FUNCTION wordcount; Distributed Database Applications

14. Access Oracle • Oracle server (service and host name) • cronus, icarus.webster.edu • Oracle client • Oracle 11g client download • SQL*Plus • SQL Developer • Application Express (APEX) Workspace • Your login and password Distributed Database Applications

15. Oracle SQL*Plus Distributed Database Applications

16. Oracle SQL Developer Distributed Database Applications

17. Oracle APEX Distributed Database Applications

18. Data Dictionary • USER_ • ALL_ • DBA_ • select view_name from all_views where view_name like 'USER%'; Distributed Database Applications

19. Language Fundamentals • PL/SQL block structure • Modularization • Scope • Anonymous blocks • Named blocks • Procedures • Functions • Scope and visibility Distributed Database Applications

20. PL/SQL Block Structure • Header • Declaration section • Execution section • Exception section PROCEDURE get_happy (ename_in IN VARCHAR2) IS hiredata DATE; BEGIN hiredate := SYSDATE – 2; INSERT INTO employee (emp_name, hiredate) VALUES (ename_in, hiredate); EXCEPTION WHEN dup_val_in_index THEN DBMS_OUTPUT.PUT_LINE (‘Cannot insert.’); END; Distributed Database Applications

21. Anonymous Blocks • No header • Begin with either DECLARE or BEGIN • Cannot be called BEGIN DBMS_OUTPUT.PUT_LINE (SYSDATE); END; DECLARE l_right_now DATE := SYSDATE; BEGIN DBMS_OUTPUT.PUT_LINE (l_right_now); END; Distributed Database Applications

22. Named Blocks • Procedures • Functions 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); end; -- Add_employee Distributed Database Applications

23. Named Blocks 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

24. Named Blocks in DB2 CREATE PROCEDURE sum( IN p_a INTEGER, IN p_b INTEGER, OUT p_s INTEGER) LANGUAGE SQL BEGIN SET p_s = p_a + p_b; END; CALL sum(100,200,?); Distributed Database Applications

25. Language Fundamentals • PL/SQL character set • Case-insensitive language • Identifiers • Up to 30 characters in length • Must start with a letter • Can include $, _, and # • Cannot contain spaces • PL/SQL keywords • Comments • Single-line comments using “--” • Multi-line comments using /* … */ Distributed Database Applications

26. PL/SQL Character Set Distributed Database Applications

27. Program Control • IF statements • CASE • LOOP • WHILE loop • Cursor loop Distributed Database Applications

28. IF Statement IF salary > 40000 THEN give_bonus (employee_id, 500); END IF; IF salary <= 40000 THEN give_bonus (employee_id, 0); ELSE give_bonus (employee_id, 500); END IF; declare salary number := 40000; begin IF salary > 40000 THEN dbms_output.put_line('Salary is greater than 40000'); ELSE dbms_output.put_line('Salary is not greater than 40000'); END IF; end; / Distributed Database Applications

29. IF Statement declare salary number := &salary; begin IF salary BETWEEN 10000 AND 20000 THEN dbms_output.put_line('Give bonus 1500.'); ELSIF salary BETWEEN 20000 AND 40000 THEN dbms_output.put_line('Give bonus 1000.'); ELSIF salary > 40000 THEN dbms_output.put_line('Give bonus 500.'); ELSE dbms_output.put_line('Give bonus 0.'); END IF; end; / Distributed Database Applications

30. CASE Statement • Simple CASE statement declare salary_level number := &salary; begin case salary_level when 1 then dbms_output.put_line('give bonus 1500.'); when 2 then dbms_output.put_line('give bonus 1000.'); when 3 then dbms_output.put_line('give bonus 500.'); else dbms_output.put_line('give bonus 0.'); end case; end; / Distributed Database Applications

31. CASE Statement • Searched CASE statement declare salary number := &salary; begin case when salary between 10000 and 20000 then dbms_output.put_line('give bonus 1500.'); when salary between 20000 and 40000 then dbms_output.put_line('give bonus 1000.'); when salary > 40000 then dbms_output.put_line('give bonus 500.'); else dbms_output.put_line('give bonus 0.'); end case; end; / Distributed Database Applications

32. CASE Statement • CASE expression declare salary number := &salary; bonus_amount number; begin bonus_amount := case when salary BETWEEN 10000 AND 20000 THEN 1500 when salary BETWEEN 20000 AND 40000 THEN 1000 when salary > 40000 THEN 500 else 0 end; dbms_output.put_line( 'Give bonus ' || bonus_amount || '.'); end; / Distributed Database Applications

33. Loop Statement • Simple loop • FOR loop • WHILE loop • Cursor FOR loop Distributed Database Applications

34. Simple Loop PROCEDURE set_all_ranks (max_rank_in IN INTEGER) IS ranking_level NUMBER (3) := 1; BEGIN LOOP EXIT WHEN ranking_level > max_rank_in; set_rank (ranking_level); ranking_level := ranking_level + 1; END LOOP; END set_all_ranks; • Use EXIT or EXIT WHEN to exit loop Distributed Database Applications

35. For Loop PROCEDURE set_all_ranks (max_rank_in IN INTEGER) IS ranking_level NUMBER (3) := 1; BEGIN FOR ranking_level IN 1 .. max_rank_in LOOP set_rank (ranking_level); END LOOP; END set_all_ranks; Reverse loop: FOR counter IN REVERSE 1 .. max LOOP … END LOOP; Distributed Database Applications

36. WHILE Loop PROCEDURE set_all_ranks (max_rank_in IN INTEGER) IS ranking_level NUMBER (3) := 1; BEGIN WHILE ranking_level <= max_rank_in LOOP set_rank (ranking_level); ranking_level := ranking_level + 1; END LOOP; END set_all_ranks; Distributed Database Applications

37. Cursor FOR Loop • Cursor with simple loop DECLARE CURSOR name_cur IS SELECT lname, fname FROM employee WHERE ssn like '8%'; name_rec name_cur%ROWTYPE; BEGIN OPEN name_cur; LOOP FETCH name_cur INTO name_rec; EXIT WHEN name_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE( name_rec.fname || ' ' || name_rec.lname); END LOOP; CLOSE name_cur; END; / Distributed Database Applications

38. Cursor FOR Loop • Cursor with simple loop DECLARE CURSOR occupancy_cur IS SELECT pet_id, room_number FROM occupancy WHERE occupied_dt = TRUNC(SYSDATE); occupancy_rec occupancy_cur%ROWTYPE; BEGIN OPEN occupancy_cur; LOOP FETCH occupancy_cur INTO occupancy_rec; EXIT WHEN occupancy_cur%NOTFOUND; update_bill (occupancy_rec.pet_id, occupancy_rec.room_number); END LOOP; CLOSE occupancy_cur; END; Distributed Database Applications

39. Cursor FOR Loop • Cursor For loop DECLARE CURSOR occupancy_cur IS SELECT pet_id, room_number FROM occupancy WHERE occupied_dt = TRUNC(SYSDATE); BEGIN FOR occupancy_rec IN occupancy_cur LOOP update_bill (occupancy_rec.pet_id, occupancy_rec.room_number); END LOOP; END; Distributed Database Applications

40. Cursor FOR Loop • Cursor FOR loop DECLARE CURSOR name_cur IS SELECT lname, fname FROM employee WHERE ssn like '8%'; BEGIN FOR name_rec IN name_cur LOOP DBMS_OUTPUT.PUT_LINE( name_rec.fname || ' ' || name_rec.lname); END LOOP; END; / Distributed Database Applications

41. Homework • Given the table layout, write the SQL to create the table • Create a script file to load database table • Create an anonymous PL/SQL block to retrieve data from above database table • Create a simple procedure in your DB2 Company schema to retrieve employee data • Project proposal Distributed Database Applications