JumpStart PL/SQL

1. JumpStart PL/SQL Wang Hao

2. Outline • PL/SQL Programming • SQL*Plus and SQL commands

3. PL/SQL Programming • PL/SQL is an extension to SQL with design features of programming languages. • Data manipulation and query statements of SQL are included within procedural units of code.

4. Benefits of PL/SQL • Integration of applications, shared library and Oracle server. • Improve performance. application SQL Other DBMSs IF … THEN SQL ELSE SQL END IF; Oracle with PL/SQL application

5. PL/SQL Block Structure • DECLARE –Optional • Variables, cursors, uer-define exceptions • BEGIN – Mandatory • SQL statements • PL/SQL statements • EXCEPTION – Optional • Actions to perform when errors occur • END; - Mandatory

6. PL/SQL Block Structure(1) DECLARE v_variable VARCHAR2(5); BEGIN SELECT column_name INTO v_variable FROM table_name EXCEPTION … WHEN exception_name THEN END;

7. Block Types [DECLARE] BEGIN -- statements [EXCETPION] END; Function FUNCTION name RETURN datatype IS BEGIN -- statements RETURN value; [EXCETPION] END; Procedure PROCEDURE name IS BEGIN -- statements [EXCETPION] END;

8. Program Constructs • Anonymous Block • Unnamed PL/SQL block that is embedded within an application or is issued interactively • Stored procedure or function Named PL/SQL block stored in Oracle Server that can accept parameters and be invoked repeatedly by name. • Package Named PL/SQL module that groups related procedures, functions and identifiers. Stored in Oracle server or Oracle Developer components – for example, Forms.

9. Handling Variables in PL/SQL • Declare and initialize variables in the declaration section. • Assign new values to variables in the executable section. • Pass values into PL/SQL blocks through parameters. • View results through output variables.

10. Types of PL/SQL Variables • Scalar • BOOLEAN, DATE, NUMBER, VARCHAR2 • Composite • Such as records. • Reference • Pointers • LOB (large objects) • Used to hold large objects such as images. • Non-PL/SQL variables: bind and host variables.

11. Declaring PL/SQL Variables • Examples: Declare v_hiredate DATE v_deptno NUMBER(2) NOT NULL :=10; v_location VARCHAR(13) := ‘New York’; v_tax_rate NUMBER(3,2) := 3.0; c_comm CONSTANT NUMBER := 1400; • Guidelines: • Follow naming conventions. • Initialize variables designated as NOT NULL. • Declare at most one identifier per line.

12. Base Scalar Datatypes • VARCHAR2(maximum_length) • No default size; max = 32767 bytes • NUMBER[(precision, scale)] • For fixed and floating numbers. • CHAR[(maximum_length)] • For fixed-length character string. Default to 1. • BOOLEAN • TURE, FALSE, or NULL • BINARY_INTEGER • Base type of integers between -2,147,483,647 and 2,147,483,647. • PLS_INTEGER • Same range as BINARY_INTEGER but requires less storage and are faster than NUMBER and BINARY_INTEGER values.

13. Declaring Variables with the % TYPE attribute • Declare a variable according to • A database column definition • Another previously declared variables. • Prefix % TYPE • Examples: v_ename emp.ename%TYPE; v_balance NUMBER(7,2); v_min_balance v_balance%TYPE := 10;

14. PL/SQL Block Syntax and Guidelines • Statements can continue over several lines. • Identifiers • Can contain up to 30 characters. • Must begin with alphabetic character. • Literals • Enclosed in single quotation marks. • A PL/SQL block is terminated by a slash / on a line by itself. • Commenting code • Single-line comments with two dashes (--). • Place multi-line comments between /* and */

15. Operators • Arithmetic + , -, *, / , **, • Comparison =, <, >, >=, <=, !=, IS NULL, LIKE, BETWEEN, IN • Assignment := • Statement terminator ; • String concatenation || • Logical NOTG, AND , OR

16. SQL Statements in PL/SQL • Extract a row of data from the database by using the SELECT command. Only a single set of values can be returned. • Make changes to rows in the database by using DML commands. (INSERT, UPDATE, DELETE). • Control a transaction with the COMMIT, ROLLBACK or SAVEPOINT command. • Determine DML outcome with implicit cursors. • Does not support DDL (CREATE TABLE, ALTER TABLE, or DROP TABLE) and DCL (GRANT, REVOKE)

17. SELECT Statements in PL/SQL • Retrieve data from the database with SELECT. • Example: DECLARE v_dept_no NUMBER(2); v_loc VARCHAR2(15); BEGIN SELECT deptno, loc INTO v_deptno, v_loc FROM dept WHERE dname=‘SALES’; END;

18. Manipulating Data Using PL/SQL • INSERT BEGIN INSERT INTO emp(empno, ename, job, deptno) VALUES (empno_sequence.NEXTVAL, ‘Jack’, ‘CTO’, 10); END; • UPDATE BEGIN UPDATE emp SET sal = sal + 10; WHERE job = ‘ANALYST’; END;

19. Manipulating Data Using PL/SQL • DELETE BEGIN DELETE FROM emp WHERE name = ‘JACK’; END;

20. Writing Control Structures • Conditional IF statements. IF v_dept_no = 10 THEN v_bonus := 5000; ELSIF v_dept_no=20 THEN v_bonus := 7500; ELSE v_bonus := 2000; END IF;

21. Writing Control Structures • Iterative control: loop statements • Basic loop • FOR loop • WHIE loop • Basic loop BEGIN LOOP INSERT INTO item(ordid, itemid) VALUES (610, v_counter); v_counter := counter + 1; EXIT WHEN v_counter > 10; END LOOP; COMMIT; END;

22. Writing Control Structures • FOR loop BEGIN FOR i IN 1..100 LOOP …… END LOOP; END; • WHILE loop BEGIN WHILE i <= 100 LOOP …… END LOOP; END; • Nested loop is allowed.

23. SQL Cursor • A cursor is a private SQL work area. Each SQL statement executed by the Oracle Server has an individual cursor associated with it. • There are two types of cursors: • Implicit cursors • Explicit cursors • The Oracle Server uses implicit cursors to parse and execute your SQL statements. • Explicit cursors are explicitly declared by the programmer.

24. SQL Cursor Attributes • Using SQL cursor attributes, you can test the outcome of your SQL statements.

25. Writing Explicit Cursors • Use explicit cursors to individually process each row returned by a multi-row SELECT statement. Active Set Cursor Current Row

26. Writing Explicit Cursors: Example DECLARE v_empno emp.empno%TYPE; v_ename emp.ename%TYPE; CURSOR emp_cursor IS SELECT empno, ename FROM emp; BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO v_empno, v_ename; EXIT WHEN emp_cursor%NOTFOUND; …… END LOOP; CLOSE emp_cursor;

27. Cursors with Parameters • Example: pass the department number and job title to the WHERE clause. DECALRE CURSOR emp_cursor (p_deptno NUMBER, p_job VARCHAR2) IS SELECT empno, ename FROM emp WHERE deptno = v_deptno AND job = v_job; BEGIN OPEN emp_cursor(10, ‘CLERK’); ……

28. Cursors with Subqueries DECALRE CURSOR my_cursor IS SELECT t1.deptno, t1.dname, t2.STAFF FROM dept t1, (SELECT deptno, count(*) STAFF FROM emp GROUP BY deptno) t2 WHERE t1.deptno = t2.deptno AND t2.STAFF >= 5;

29. Using Arrays • Item type: VARRAY (short for Variable-size arrays). • A varray can have only one dimension and must be indexed by integers. • A varray has a maximum size. • Example: DECALRE TYPE list IS VARRAY(25) of NUMBER(3); v1 v_list; BEGIN FOR i IN 1..25 v1(i) := 1; END LOOP; END;

30. Subprograms • Subprograms are named PL/SQL blocks that can take parameters and be invoked. PL/SQL has two types of subprograms called procedures and functions. • Example: a procedure named debit_account, which debits a bank account:

31. PROCEDURE debit_account (acct_id INTEGER, amount REAL)IS old_balance REAL; new_balance REAL; overdrawn EXCEPTION; BEGIN SELECT bal INTO old_balance FROM accts WHERE acct_no = acct_id; new_balance := old_balance - amount; IF new_balance < 0 THEN RAISE overdrawn; ELSE UPDATE accts SET bal = new_balance WHERE acct_no = acct_id; END IF; EXCEPTION WHEN overdrawn THEN ... END debit_account;

32. Writing Functions • Consider the function sal_ok, which determines if a salary is out of range: FUNCTION sal_ok (salary REAL, title VARCHAR2) RETURN BOOLEAN IS min_sal REAL; max_sal REAL; BEGIN SELECT losal, hisal INTO min_sal, max_sal FROM sals WHERE job = title; RETURN (salary >= min_sal) AND (salary <= max_sal); END sal_ok;

33. Parameter Modes • IN mode (the default) • passes values to a subprogram. • actual parameter is passed by reference (a pointer to the value is passed in) • OUT mode (must be specified) • returns values to the caller. • actual parameter is passed by value (a copy of the value is passed out) unless NOCOPY is specified • IN OUT mode (must be specified) • passes initial values to a subprogram and returns updated values to the caller. • actual parameter is passed by value.

34. Declaring Subprograms • You can declare subprograms in any PL/SQL block, subprogram, or package. But, you must declare subprograms at the end of a declarative section after all other program items. • PL/SQL requires that you declare an identifier before using it. Therefore, you must declare a subprogram before calling it.

35. Packaging Subprograms • You can group logically related subprograms in a packages, which is stored in the database. • Packaged subprograms can be shared by many applications, while hiding implementation details. • Package spec: declares the subprograms, and • Package body: implements the subprograms. • Example:

36. Creating Package Spec: CREATEOR REPLACEPACKAGE emp_actions ASPROCEDURE hire_employee (emp_id INTEGER, name VARCHAR2, ...); PROCEDURE fire_employee (emp_id INTEGER); PROCEDURE raise_salary (emp_id INTEGER, amount REAL); ... END emp_actions; • Creating Package Body:

37. CREATE OR REPALCE PACKAGE BODY emp_actions ASPROCEDURE hire_employee (emp_id INTEGER, name VARCHAR2, ...) IS BEGIN ... INSERT INTO emp VALUES (emp_id, name, ...); END hire_employee; PROCEDURE fire_employee (emp_id INTEGER) IS BEGIN DELETE FROM emp WHERE empno = emp_id; END fire_employee; PROCEDURE raise_salary (emp_id INTEGER, amount REAL) IS BEGIN UPDATE emp SET sal = sal + amount WHERE empno = emp_id; END raise_salary; ... END emp_actions;

38. Packaging Subprograms • You can define subprograms in a package body without declaring their specs in the package spec. However, such subprograms can be called only from inside the package. • Guidelines: • Put package spec and body in separate files. • Compile package spec first before compiling package body.

39. Referencing Package Contents • To reference the types, items, subprograms, and call specs declared within a package spec, use dot notation, as follows: • package_name.type_name • package_name.item_name • package_name.subprogram_name • package_name.call_spec_name • Example: SQL> CALL emp_actions.hire_employee('TATE', 'CLERK', ...);

40. Oracle Packages: DBMS_OUTPUT • Package DBMS_OUTPUT enables you to display output from PL/SQL blocks and subprograms, which makes it easier to test and debug them. • You display the information by calling the procedure get_line or by setting SERVEROUTPUT ON in SQL*Plus. • For example, suppose you create the following stored procedure:

41. CREATE PROCEDURE calc_payroll (payroll OUT NUMBER) AS CURSOR c1 IS SELECT sal, comm FROM emp; BEGIN payroll := 0; FOR c1rec IN c1 LOOP c1rec.comm := NVL(c1rec.comm, 0); payroll := payroll + c1rec.sal + c1rec.comm; END LOOP; /* Display debug info. */ DBMS_OUTPUT.PUT_LINE('Value of payroll: ' || TO_CHAR(payroll)); END; In SQL*Plus SQL> SET SERVEROUTPUT ON SQL> VARIABLE num NUMBER SQL> CALL calc_payroll(:num); Value of payroll: 31225

42. Oracle Packages: UTL_FILE • Package UTL_FILE allows your PL/SQL programs to read and write operating system (OS) text files. • It provides a restricted version of standard OS stream file I/O, including open, put, get, and close operations.

43. UTL_FILE Functions • UTL_FILE.FOPEN ( location IN VARCHAR2, filename IN VARCHAR2, open_mode IN VARCHAR2) RETURN UTL_FILE.FILE_TYPE; • Open_mode: 'r' , 'w‘, 'a‘ • The maximum line size is the default to 1023. • To read a line of max. length > 1023, use • UTL_FILE.FOPEN ( location IN VARCHAR2, filename IN VARCHAR2, open_mode IN VARCHAR2, max_linesize IN BINARY_INTEGER) RETURN file_type;

44. UTL_FILE Functions • UTL_FILE.IS_OPEN (file IN FILE_TYPE) RETURN BOOLEAN; • UTL_FILE.FCLOSE (file IN OUT FILE_TYPE); • UTL_FILE.GET_LINE ( file IN FILE_TYPE, buffer OUT VARCHAR2); • Text is read up to but not including the line terminator, or up to the end of the file. • If the line does not fit in the buffer, then a VALUE_ERROR exception is raised. If no text was read due to "end of file," then the NO_DATA_FOUND exception is raised. • The maximum size of an input record is 1023 bytes.

45. BEGIN v_fid := utl_file.fopen('e:\log','h.log','r'); BEGIN LOOP utl_file.get_line(v_fid,in_row); IF substr(in_row,1,6)='Table' AND substr(in_row,-1,1) = ':' THEN h_table_name := rtrim(substr(in_row,7,30),':'); ELSEIF substr(in_row,-7,7) = 'loaded.' THEN h_no_rows := to_number(substr(in_row,3,instr (in_row,'R')-4)); ENDIF; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; utl_file.fclose(v_fid); END;

46. Useful Links • http://oraweb.comp.nus.edu.sg/RDBMS/appdev.816/a77069/toc.htm PL/SQL User's Guide and Reference • http://www.elementkjournals.com/dbm/0005/dbm0053.htmUsing the UTL_FILE package

47. Using SQL*Plus • A client for users to interactively execute commands on Oracle Server. • Useful commands • Describe a table structure: desc <table name> • List all tables under you: select table_name from user_tables. • Run packages: @<package name>

48. SQL*Plus Commands • Show errors: show error; • Edit the previously executed command(s): Edit • Enable output on SQL*PLUS: SET SERVEROUTPUT ON • Solve array overflow problem: SET ARRAYSIZE 1

49. SQL Commands • DROP TABLE employee • CREATE TABLE employee ( empno INTEGER NOT NULL, name VARCHAR2(50) NOT NULL, sal REAL NOT NULL, primary key (empno)); • INSERT INTO employee VALUES (1, 'Jack', 6000) • UPDATE employee SET sal=500 WHERE name=‘Jack’ • DELETE FROM employee WHERE name=‘Jack’; • CREATE INDEX test_index on employee(sal) • DROP INDEX test_index