Introduction to PL/SQL

1. Introduction to PL/SQL N. Dimililer

2. About PL/SQL • 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. • PL/SQL offers modern software engineering features such as data encapsulation, exception handling, information hiding, and object orientation.

3. PL/SQL Blocks Anonymous Block : Unnamed block of PL/SQL. These do not accept any parameters. • Procedure : A series of statements accepting and/or returning zero or more variables through parameters. • Function: A series of statements accepting zero or more variables that returns one value using an explicit RETURN statement. • Package : A collection of procedures and functions that has two parts, a specification listing available procedures and functions and their parameters, and a body that contains the actual code for the procedures and functions. • Trigger : A series of PL/SQL statements attached to a database table that execute whenever a triggering event (select, update, insert, delete) occurs.

4. Benefits of PL/SQL • Integration • Improved Performance • Modularize Program Development • Portability • Identifiers can be used • Procedural Language Control Structures • Exception/Error handling

5. PL/SQL improves performance Other DBMSs SQL Application SQL SQL SQL SQL IF...THEN SQL ELSE SQL END IF; SQL Oracle with PL/SQL Application

6. PL/SQL Block Structure DECLARE BEGIN EXCEPTION END;

7. PL/SQL Block Structure DECLARE --optional Variables, cursors, user-defined exceptions BEGIN --mandatory • SQL statements • PL/SQL statements EXCEPTION --optional Actions to perform whenerrors occur END;

8. Executing Statements and PL/SQL Blocks from SQL*Plus • Place a semicolon (;) at the end of a SQL statement or PL/SQL control statement. • Use a slash (/) to run the anonymous PL/SQL block in the SQL*Plus buffer. When the block is executed successfully, without unhandled errors or compile errors, the message output should be as follows: PL/SQL procedure successfully completed. • Place a period (.) to close a SQL*Plus buffer. A PL/SQL block is treated as one continuous statement in the buffer, and the semicolons within the block do not close or run the buffer.

9. PL/SQL statements and semicolon • In PL/SQL, an error is called an exception. • Section keywords DECLARE, BEGIN, and EXCEPTION are not followed by semicolons. • END and all other PL/SQL statements require a semicolon to terminate the statement. • You can string statements together on the same line, but this method is not recommended for clarity or editing.

10. Use of Variables • With PL/SQL you can declare variables and then use them in SQL and procedural statements anywhere an expression can be used. • Temporary storage of data • Manipulation of stored values • Reusability. • Ease of maintenance

11. 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.(will be covered later on when we talk about procedures and functions) • View results through output variables. • Remember: Uninitialized variables contain “NULL” value

12. identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr]; You cannot assign NULL to v_deptno. Must be initialized Declare v_hiredate DATE; v_deptno NUMBER(2) NOT NULL := 10; v_gender VARCHAR2(6) := 'FEMALE'; v_city VARCHAR2(10); c_comm CONSTANT NUMBER := 1400; The value of c_comm cannot be changed. Must be initialized at declaration Declaration of PL/SQL variables Syntax of declaration Example declarations

13. Assigning values to variables Syntax of assignement identifier := expr; Set v_hiredate to a date v_hiredate := '31-DEC-2012'; Set the employee name to “Ali” v_ename := ‘Ali';

14. DBMS_OUTPUT.PUT_LINE • Put_line is a procedure included in the Oracle-supplied package DBMS_OUTPUT • Used for displaying data from a PL/SQL block on the screen • Must be enabled in SQL*Plus with SET SERVEROUTPUT ON

15. Example Anonymous Blocks Simply prints “Hello world” on screen BEGIN DBMS_OUTPUT.PUT_LINE(‘Hello world’); END; BEGIN DBMS_OUTPUT.PUT_LINE(‘Today is’ || SYSDATE ); END; Put_line accepts a single string as parameter. Use concatenation operator if you have more than one string to display

16. Variable declaration Variable Initialization Variable declared and initialized at declaration Example Anonymous Blocks DECLARE v_name varchar2(20); BEGIN v_name := ‘Ali’; DBMS_OUTPUT.PUT_LINE(Hello || v_name); END; Task: Write an anonymous block to print “Hello Ali” on screen. Declare name as a variable DECLARE v_name varchar2(20) := ‘Ali’; BEGIN DBMS_OUTPUT.PUT_LINE(Hello || v_name); END; v_name varchar2(20) DEFAULT ‘Ali’;

17. Substitution Variable: The value you enter from keyboard replaces the substitution variable Keyboard entry Output Enter value for your_name: ‘Veli’ Old 4: v_name := &your_name New 4: v_name := ‘Veli’ Hello Veli Example Anonymous Blocks Read the name of user from keyboard and print “Hello <name>” on screen. DECLARE v_name varchar2(20); BEGIN v_name := &your_name; DBMS_OUTPUT.PUT_LINE(‘Hello ’ || v_name); END;

18. Controlling Flow of Events • You can change the logical flow of statements using conditional IF statements and loop control structures. • Conditional IFstatements: • IF-THEN-END IF • IF-THEN-ELSE-END IF • IF-THEN-ELSIF-ELSE-END IF

19. IF STATEMENT Syntax Example: If v_dept_name is ‘ITEC’ change v_dept_no to 35. IF condition THEN statements; [ELSIF condition THEN statements;] [ELSE statements;] END IF; IF v_dept_name = ‘ITEC' THEN v_dept_no := 35; END IF;

20. Example Anonymous Block Task: Write an anonymous block that will print ‘weekend’ if the system date is ‘Sunday’ or ‘Saturday’. DECLARE v_current_date date := SYSDATE; BEGIN IF to_char(v_current_date, ‘d’) in (1,7) THEN dbms_output.put_line(‘weekend’); END IF; END;

21. Example Anonymous Block Task: Write an anonymous block that will print ‘weekend’ if the system date is ‘Sunday’ or ‘Saturday’ and ‘weekday’ otherwise. BEGIN IF to_char(sysdate, ‘d’) in (1,7) THEN dbms_output.put_line(‘weekend’); ELSE dbms_output.put_line(‘weekday’); END IF; END;

22. Example Anonymous Block Task: Write an anonymous block that will print weekend if the system date is ‘Sunday’ or ‘Saturday’, we have class if it is Tuesday or Thursday and no class otherwise. BEGIN IF to_char(sysdate, ‘d’) in (1,7) THEN dbms_output.put_line(‘week end’); ELSIF to_char(sysdate,’d’) in (3,5) THEN dbms_output.put_line(‘we have class’); ELSE dbms_output.put_line(‘no class’); END IF; END;

23. Summary • We learned the overall format of a PL/SQL anonymous block. • If statement is covered. • Next lesson we will learn about loops.