PL/SQL

1. PL/SQL Chapter 8

2. Outline • Procedures • Cursors • Triggers

3. Procedural SQL • SQL does not support the conditional execution of procedures as well as looping operations. • To overcome the lack of procedural functionality in SQL and to provide standardization, SQL-99 standard defines the use of persistent stored modules (PSM).

4. What is PSM? • A PSM is a block of code containing standard SQL statements and procedural extensions that is stored and executed at the DBMS server. • The PSM represents business logic that can be encapsulated, stored and shared among multiple database users. • Oracle implements PSMs through its procedural SQL language.

5. What is PL/SQL and where can PL/SQL be used? • Procedural SQL (PL/SQL) is a language that makes it possible to use and store procedural code and SQL statements within the database and merge SQL and traditional programming constructs. The procedural code is executed as a unit by the DBMS when it is invoked b the end user. • PL/SQL is used to create • Anonymous PL/SQL blocks (without name) • Stored Procedures • PL/SQL functions • Triggers

6. PL/SQL block • The PL/SQL block starts with the DECLARE section in which the variables, the data types or the initial values are declared. • The PL/SQL block ends with the word END • The PL/SQL block can be executed by typing / and press enter key. • Each statement inside the PL/SQL code must end with a semicolon “;”. • To display the output use the command dbms_output.put_line(‘any string’);. • Conditional statement or Looping statements can be used after declare statements; • The SELECT statement uses the INTO keyword inside the PL/SQL block of code to assign the output of the query to a PL/SQL variable. If the SELECT statement returns more than one value, you will get an error.

7. PL/SQL Program Blocks

8. Commands included in PL/SQL • Comments: PL/SQL program includes commands such as Comments statements that are lines of text that explain or document a program step or series of steps. • Not executed by interpreter • To create a block of comments enclosed between /* and */ • To create one line comment type two hyphens -- at the beginning of the line. • PL/SQL programs have • Commands to perform arithmetic operations • Assignment statements that assign values to variables • Conditional structures to perform decisions • Looping structures to repeat instructions multiple times • SQL commands that retrieve data.

9. Procedural Statements • Sequences Statement 1; Statement 2;… • Control Statements IF <condition> THEN statement1; ELSE statement2; ENDID; • Looping Structures DO WHILE LOOP…. Statement 1; Statement 2;… ENDLOOP

10. Assignment Statements • Assigns a value to a variable • An assignment statement has the following syntax variable_name := value; • Value can be a literal: current_s_first_name := 'John'; • Value can be another variable: current_s_first_name := s_first_name;

11. Displaying PL/SQL Program Output in SQL*Plus • PL/SQL output buffer • Memory area on database server • Stores program’s output values before they are displayed to user • Should increase the size of the buffer as • SET SERVEROUTPUT ON SIZE buffer_size • Default buffer size • 2000 bytes • To display more than just a few lines of output, it is better to make the output buffer larger than the default size. • Maximum is 1000000 bytes

12. Displaying PL/SQL Program Output in SQL*Plus (continued) • Display program output • DBMS_OUTPUT.PUT_LINE('display_text'); • The display_text value can • contain literal character strings such as ‘ My name is Roohi’ or • variable names such as current_s_first. • Display maximum of 255 characters of text data

13. Executing a PL/SQL Program in SQL*Plus • Create program in text editor • Copy and paste program commands into SQL*Plus window • Press Enter after last program command • Type front slash ( / ) • Then press Enter again

14. Executing a PL/SQL Program in SQL*Plus Write a PL/SQL program to display the current date as shown below Today’s date is 27-Feb-2012 DECLARE todays_date DATE; X number; BEGIN todays_date := SYSDATE; X:= 1; DBMS_OUTPUT.PUT_LINE('Today''s date is '); DBMS_OUTPUT.PUT_LINE(todays_date); DBMS_OUTPUT.PUT_LINE(x); END;

15. PL/SQL Data Conversion Functions • Sometimes the PL/SQL interpreter is unable to implicitly convert value and an error occurs. • Other times, PL/SQL interpreter performs an implicit conversion and produce unexpected/invalid data • Explicit data conversions • Convert variables to different data types using data conversion functions that are built into PL/SQL. A Guide to Oracle10g

16. Display today’s date using concatenated character string • To concatenate two strings in PL/SQL, you use the double bar (||) operator: new_string := string1 || string2; SQL> DECLARE 2 todays_date DATE; 3 BEGIN 4 todays_date :=SYSDATE; 5 DBMS_OUTPUT.PUT_LINE('Today''s date is ' || TO_CHAR(todays_date)); 6 END; 7 / PL/SQL procedure successfully completed.

17. PL/SQL Decision Control Structures • Use IF/THEN/ELSE to execute code if condition is true or FALSE • IFconditionTHEN commands that execute if condition is TRUE; ELSE commands that execute if condition is FALSE; END IF; • Evaluates ELSE commands if condition is FALSE

18. PL/SQL Decision Control Structures • Use IF/ELSIF decision control structure to test for many different conditions.: • IF condition1THEN commands that execute if condition1 is TRUE; ELSIF condition2THEN commands that execute if condition2 is TRUE; ELSIF condition3THEN commands that execute if condition3 is TRUE; ... ELSE commands that execute if none of the conditions are TRUE; END IF;

19. Loops • PL/SQL has 5 loop structures • LOOP…EXIT WHEN • WHILE …..LOOP • Numeric FOR loops

20. The LOOP...EXIT WHEN Loop The LOOP…EXITWHEN loop can also be either a pretest or a post test loop. LOOP program statements EXIT WHENcondition; END LOOP;

21. The WHILE...LOOP The WHILE….LOOP is a pretest loop that evaluates the exit condition before it executes any program statement. WHILEcondition LOOP program statements END LOOP;

22. The Numeric FOR Loop • Does not require explicit counter increment • The loop counter variable and it start and end values are declared in the loop’s FOR statement. • Automatically increments counter variable until it reaches the end value. FOR counter_variableIN start_value .. end_value LOOP program statements ENDLOOP;

23. Using SQL Queries in PL/SQL Programs • Action queries can be used as in SQL*Plus • May use variables in action queries • DDL commands may not be used in PL/SQL

24. Using SQL Commands in PL/SQL Programs

25. PL/SQL block to insert a new row in the VENDOR table SET SERVEROUTPUT ON BEGIN INSERT INTO VENDOR VALUES (25772,’Clue Store’, ‘Issac Hayes’, ‘456’, ‘323-2009’, ‘VA’, ‘N’); DBMS.OUTPUT.PUT_LINE(‘New Vendor Added’); END; / -- to execute An anonymous PL/SQL Program

26. PL/SQL block to count the number of products having different price ranges DECLARE W_P1 NUMBER(3) := 0; W_p2 number(3) := 10; W_P1 NUMBER(2) := 0; BEGIN WHILE W_P2 <300 LOOP SELECT COUNT(P_CODE) INTO W_NUM FROM PRODUCT WHERE P_PRICE BETWEEN W_P1 AND W_P2; DBMS_OUTPUT.PUT_LINE (‘The number of products between’ ||W_P1||’and’ || W_P2 || ‘is ’ || W_NUM); W_P1 := W_P2+1; W_P2 := W_P2+ 50 ; END LOOP; END; /

27. STORED PROCEDURES • A stored procedures is a named collection of procedural and SQL statements. • Stored procedures are stored in the database. • They can be used to encapsulate and represent business transactions. • Syntax: CREATE OR REPLACE PROCEDURE procedure_name [(argument [IN/OUT] data-type,….. )] [IS/AS] [variable_namedata_type[:=initial_value] ] BEGIN PL/SQL or SQL statements …. END; Refer to the handouts for more explanation on IN/OUT arguments

28. STORED PROCEDURES • Advantages: • Stored procedures substantially reduce traffic as there is no transmission of individual SQL statements over the network. • Stored procedures increase performance because all the transactions are executed locally on the RDBMS.

29. Create a procedure to assign an additional 5percent discount for all products when the quantity on Hand is more than or equal to twice the minimum quantity. CREATE OR REPLACE PROCEDURE PRC_PROD_DISCOUNT AS BEGIN UPDATE PRODUCT SET P_DISCOUNT = P_DISCOUNT + .05 WHERE P_QOH >= P_MIN*2; DBMS_OUTPUT.PUT_LINE ('* * Update finished * *'); END;

30. STORED PROCEDURES • To execute the stored procedure, use the followijg syntax: • EXEC procedure_name[(paramenter_list)]

31. Create a procedure to assign an additional percent discount (an input variable) for all products when the quantity on Hand is more than or equal to twice the minimum quantity. CREATE OR REPLACE PROCEDURE PRC_PROD_DISCOUNT(WPI IN NUMBER) AS BEGIN IF ((WPI <= 0) OR (WPI >= 1)) THEN -- validate WPI parameter DBMS_OUTPUT.PUT_LINE('Error: Value must be greater than 0 and less than 1'); ELSE -- if value is greater than 0 and less than 1 UPDATE PRODUCT SET P_DISCOUNT = P_DISCOUNT + WPI WHERE P_QOH >= P_MIN*2; DBMS_OUTPUT.PUT_LINE ('* * Update finished * *'); END IF; END;

32. Run the Procedure EXEC PRC_PROD_DISCOUNT (1.5); What is the outcome? EXEC PRC_PROD_DISCOUNT (.05); What is the outcome?

33. Oracle Sequences • MS Access AutoNumber data type fills a column with unique numeric values • Oracle sequences • Independent object in the database • Named, used anywhere a value expected • Not tied to a table or column • Generate numeric values that can be assigned to any column in any table • Created and deleted at any time

35. Create a procedure which adds a new invoice. CREATE OR REPLACE PROCEDURE PRC_INV_ADD (W_CUS_CODE IN VARCHAR2, W_DATE IN DATE) AS BEGIN INSERT INTO INVOICE VALUES(INV_NUMBER_SEQ.NEXTVAL, W_CUS_CODE, W_DATE); DBMS_OUTPUT.PUT_LINE('Invoice added'); END;

36. Create a procedure to add a new customer CREATE OR REPLACE PROCEDURE PRC_CUS_ADD (W_LN IN VARCHAR, W_FN IN VARCHAR, W_INIT IN VARCHAR, W_AC IN VARCHAR, W_PH IN VARCHAR) AS BEGIN -- note that the procedure uses the CUS_CODE_SEQ sequence created earlier. Attribute names are required when not giving values for all table attributes INSERT INTO CUSTOMER(CUS_CODE,CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE) VALUES (CUS_CODE_SEQ.NEXTVAL, W_LN, W_FN, W_INIT, W_AC, W_PH); DBMS_OUTPUT.PUT_LINE ('Customer ' || W_LN || ', ' || W_FN || ' added.'); END; /

37. EXEC PRC_CUS_ADD(‘Walker’, ‘James’,NULL, ‘615’, ‘84-HORSE’); The result is Customer Walker, James added. PL/SQL procedure successfully completed.

38. Create a procedure which adds a new product line row for a given invoice. CREATE OR REPLACE PROCEDURE PRC_LINE_ADD (W_LN IN NUMBER, W_P_CODE IN VARCHAR2, W_LU NUMBER) AS W_LP NUMBER := 0.00; BEGIN -- GET THE PRODUCT PRICE SELECT P_PRICE INTO W_LP FROM PRODUCT WHERE P_CODE = W_P_CODE; -- ADDS THE NEW LINE ROW INSERT INTO LINE VALUES(INV_NUMBER_SEQ.CURRVAL, W_LN, W_P_CODE, W_LU, W_LP,null); DBMS_OUTPUT.PUT_LINE('Invoice line ' || W_LN || ' added'); END; In order to insert a new record in the LINE table, we need to know the Invoice Number, Product Code, Line Units (the values are obtained from the input parameters), Line Price ( the value is obtained from the Product Price i.e. p_price) in a PRODUCT Table

39. Cursor • A cursor is a special construct in procedural SQL to hold the data rows returned by an SQL query. • A cursor is a reserved area of memory in which the output of the query is stored like an array holding columns and rows. • Cursors are held in a reserved memory area in the DBMS server, not in the client computer.

40. Cursors • A cursor is a pointer to a memory location on database server that the DBMS uses to process a SQL query • Cursors are used to retrieve and manipulate database data in PL/SQL programs • Types: • Implicit • Explicit

41. Implicit Cursor

42. Implicit Cursors • Context area • A memory location on the database server • Contains information about query e.g. no. of rows that the query processes, and a machine language representation of the query etc. • Created by INSERT, UPDATE, DELETE, or SELECT • Active set • For SELECT queries, the context area also stores the active set, which is the set of data rows that query retrieves • Implicit cursor • is a pointer to the context area • For Implicit cursor, there is no need to write commands explicitly to create it or retrieve its value.

43. Implicit Cursors • Executing a SELECT query creates an implicit cursor i.e.Implicit cursor can be used to assign the output of a SELECT query to PL/SQL program variables when we are sure that the query will return one and only one record. • If the query returns more than one record, or does not return any record, an error occurs.

44. Using an Implicit Cursor • To retrieve data values from a query’s implicit cursor into PL/SQL program variables add INTO clause : SELECTfield1, field2, ... INTOvariable1, variable2, ... FROMtable1, table2, ... WHEREjoin_ conditions ANDsearch_condition_to_retrieve_1_record; • The variables must have been declared in the program’s declaration section • The variables must have the same data types as the associated database fields.

45. Reference Variables • Reference variables directly reference specific database field or record and assume data type of the associated field or record • %TYPE: is a reference data type which assumes the data type of a database field • data declaration syntax: variable_nametablename.fieldname%TYPE; e.g. current_c_lastCustomer.Cus_Lname%TYPE; The current_c_lastvariable would assume a data type of VARCHAR2(15), because this is the data type of Cus_Lname field in the CUSTOMER table

46. Implicit Cursors (continued) • Useful to use %TYPE reference data type to declare variables used with implicit cursors • variable_nametablename.fieldname%TYPE • e.g. current_c_lastcustomer.cus_lname%TYPE Variable name Table name Field name in the table

47. Reference Variables • %ROWTYPE: is a reference data type which assumes the data type of a database record • data declaration syntax: row_variable_nametablename%ROWTYPE; e.g. customer_rowCustomer%ROWTYPE; The CUSTOMER_ROW references all of the fields in the CUSTOMER TABLE, and each field has the same data type as its associated database fields.

48. Reference Variables • How do you refer to individual data field within a %ROWTYPE variable? • Use the syntax row_variable_name.fieldname • If u have to refer to f_first name, you can write customer_row.cus_fname or the balance, you can write customer_row.cus_balance

49. Reference Data Type used with Cursors • %TYPE: is a reference data type which assumes the data type of a database field • data declaration syntax: variable_nameCursor_Name.fieldname%TYPE; e.g. current_c_lastcus_cursor.cus_fname%TYPE; %ROWTYPE:is a reference data type which assumes the data type of a database record • data declaration syntax: variable_nameCursor_Name%ROWTYPE; e.g. customer_rowcus_cursor%ROWTYPE;

50. PL/SQL program that displays last name and first name from the CUSTROMER table where cus_code=10010 using an implicit cursor SQL> DECLARE 2 current_c_lastcustomer.cus_lname%TYPE; 3 current_c_firstcustomer.cus_fname%TYPE; 4 BEGIN 5 SELECT cus_lname, cus_fname 6 INTO current_C_last, current_C_first 7 FROM customer 8 WHERE cus_code = 10010; 9 DBMS_OUTPUT.PUT_LINE(' The customer ''s name is ' || current_f_first || ' ' || current_f_last); 10 END; 11 / The customer's name is Ramas Alfred PL/SQL procedure successfully completed.