Agenda

1. Agenda • Introduction • Expectation from the class • Course Coverage for next three days • PL/SQL overview • Structure of PL/SQL • Conditional Statements and Loops • Exceptions • Cursors • Stored procedures • Functions • Packages • Triggers

2. PL SQL Course • What is PL/SQL? • Why PL/SQL? • Structure of PL/SQL (Blocks) • Variables in PL/SQL

3. What is PL/SQL? • PL/SQL is the programming language with the power of SQL. • PL/SQL is the database programming language which is used in Oracle database. • Its used to create programs which will implement the business processes. • SQL is an interactive language • PL/SQL is a non interactive language, using PL/SQL we can declare variables, assign values, we have conditional statements, handle errors, have looping mechanism etc etc. • PL/SQL language is used in various tools of Oracle like Forms, Report developer etc.

4. Why PL/SQL • Business runs based on lots of processes. • Consider an example of a bank where we do deposits, with drawls, account opening, account closing, calculating interest for a certain period based on the account type etc • So, in banks withdrawal is a process where we check if the available balance is greater than minimum balance required + the withdrawal amount, then only we can disburse the amount. In this scenario, you cannot just write one SQL to finish the whole process, we have to write set of DML operations to finish the job, in this case we write PL/SQL block. • So, if business defines 100 different process, then as programmers we implement those process in terms of PL/SQL blocks in the database world. • SQL answers the questions • PL/SQL solves the business process (one or more than one SQL will exists in one business process).

5. Structure of PL/SQL (Block) DECLARE ------- ------- BEGIN --------- --------- --------- --------- EXCEPTION ---------- ---------- ---------- END; Is used to declare variables used in the program Is the actual logic you write to solve the problem Error handling section in the program

6. Variables in PL/SQL • Following variables are used in PL/SQL • Number – variable is used to store number values. Per example if you want to store the salary of particular employee then you select the sal from emp and store that value in to that variable with in the block • Date – used to store the date values. Say in an application where we place an order for any product, the org says with in 3 days we deliver the product, in this case have a date variable to calculate the delivery date so that you can inform your operations team to deliver the same with in that date. • Char, Varchar – used to store the string values. For example we want to split the first name and last name from an employee name then we may use varchar(30) as the variable to store the same.

7. Simple PL/SQL with variable declaration This program is used to get two values from end user and add those numbers and display the result back. In this example we did not use and SQL with in the program. In the next example we will see how to use SQL with in PL/SQL block. DECLARE var_number1 number(4); --declaration of number variable var_number2 number(4); --declaration of number variable var_result number(6); --declaration of number variable BEGIN var_number1 := &number1; --getting a value from end user through prompt var_number2 := &number2; -- := is the assingment operator in PL/SQL var_result := var_number1 + var_number2; DBMS_OUTPUT.PUT_LINE(var_result); -- to display the value on the screen END;

8. DBMS_OUTPUT • In the above program we used DBMS_OUTPUT.PUT_LINE • DBMS_OUTPUT.PUT_LINE is the DBMS package through which you can print the variable value in a PL/SQL block. • To enable this use SET SERVEROUTPUT ON in the SQL session. (Use SQL Plus and in sql prompt type SET SERVEROUTPUT ON) • We use this variable mostly to debug the problem in the real world.

9. Embedded SQL • When we write a SQL with in a PL/SQL block, we call that as embedded sql. • Syntax for embedded sql is • Select col_name into var_namefrom tablewhere col_name > ‘xyz’ • We use into clause to store the values what SQL returns • Embedded SQL can return only one row back • If SQL returns more than one row then those values cannot be stored in one variable, so oracle raises an error called TOO_MANY_ROWS with in the program • If SQL does not return any rows ie (no result set), then oracle raises an error called NO_DATA_FOUND

10. Example of simple PL/SQL block • This pl/sql block is used to display emp name by taking empno as the input DECLARE v_empno number(4); --this is how you declare a variable v_empname varchar(30); -- this variable is used to store emp name BEGIN v_empno := &empno – this is the way we read data for a variable from end user Select ename into v_empnamefrom empwhere empno = v_empno; DBMS_OUTPUT.PUT_LINE (v_empname); END;

11. Error handling • Run the above PL/SQL block with the valid employee id, you will get the employee name. • Run the same PL/SQL block with the invalid employee id (empid which does not exists in the emp table), now check the results. It will raise an error called NO_DATA_FOUND. • We can handle this scenario in the exception area of a PL/SQL block. • In the next slide we will handle the exception area for the same block. In this, if employee id does not exists then we will display ‘Employee does not exists’

12. Example of simple PL/SQL block with exception • This pl/sql block is used to display emp name by taking empno as the input, if empno does not exists in employee table then display Employee does not exists DECLARE v_empno number(4); --this is how you declare a variable v_empname varchar(30); -- this variable is used to store emp name BEGIN v_empno := &empno – this is the way we read data for a variable from end user Select ename into v_empnamefrom empwhere empno = v_empno; DBMS_OUTPUT.PUT_LINE (v_empname); EXCEPTION When NO_DATA_FOUND then DBMS_OUTPUT.PUT_LINE(‘Employee does not exists’); END;

13. Exercise • Read Deptno from end user • Display dname if that deptno exists in the table • If that deptno does not exists then insert a record into dept table with ‘UNKNOWN’ as values for dname and loc • Read empno and percentage of salary hike. • Change the salary of employee with the respective salary (update) • If you did not find that employee display ‘Employee number not found’ message

14. Using Update statement in PL/SQL • Consider following example, read empno, salary to increment • If user enters 8956 as the employee number and 300 as the increment then your program should increase the salary by Rs 300. • If you did not find the user then display ‘No such employee exists in the database’ • If you updated the salary display ‘Salary updated’

15. PL/SQL – Using Update • Declare • v_empno number(4); • V_increment_sal number(7,2); • Begin • UPDATE emp set sal = sal+v_increment where empno = v_empno If SQL%ROWCOUNT = 0 then DBMS_OUTPUT.PUT_LINE(‘No such emp’); Else DBMS_OUTPUT.PUT_LINE(‘Salary update’); End If; • END; • In the above example you see we did not use the exception called NO_DATA_FOUND. Only Select statement raises an exception called NO_DATA_FOUND, other DML operations like update, delete and insert wont raise this exception. In those scenarios, you a global variable called SQL%ROWCOUNT in the program. • The rowcount variable gets updated after every DML operation you issue, so the value what you have in that variable is, the number of rows which got affected in the prior DML operation in that session.

16. Consider the following tables Customer Bnk_txns Accounts Customer_id Cust_name Address city Account_id Cust_id Account_open_dt Account_balance Txn_id Act_id Txn_type Txn_date Amount datetime Write a PL/SQL block to automate the account transfer process. You can transfer the money from one account to the other. We have two accounts (200, 210). You have to transfer the money from account 200 to 210, the amount is Rs 2000. Analyze the problem and come up with how many DML statements you have to write in the whole program.

17. Variables specific to PL/SQL • Boolean – used to store the boolean values like TRUE or FALSE. In SQL we don’t have this data type. If you want to store this kind of data in the database usually we use a flag column which stores either 0 or 1 where we represent 0 for false and 1 for true or vice versa. • Exception – Is used to handle any user defined errors. Say the business rule is we cannot raise the salary of any employee by 100% at one instance. When user is updating the salary of an employee with more than 100% we can use the user defined exception to handle this error. • Cursor – is a variable which holds the result set of an SQL so that you can fetch one record at a time. • PL/SQL tables – is the data type where we can store the multiple values in one variable. You can compare this with the array data type of any programming language. • Record Data type – to store the heterogenious data type values in one variable. You can compare this with the structures of any programming language etc

18. Exception • Exception in PL/SQL block is used to handle the errors raised by oracle or by business process. • So exceptions are used to handle the errors in the PL/SQL program • Say you wrote a SQL as part of the PL/SQL, that SQL did not return any row, then oracle raises an exception called no_data_found

19. System Defined exceptions • NO_DATA_FOUND • TOO_MANY_ROWS • DUP_VAL_ON_INDEX • INVALID_NUMBER • ZERO_DIVIDE • CURSOR_ALREADY_OPEN • INVALID_CURSOR • OTHERS • These exceptions get raised by system when an exception / error occurs, so we call it as system defined exceptions.

20. Control Statements • As in any other programming language we have If then else clause in PL/SQL. We can control the flow of your program based on certain statements with in the block. • If <condition> then Logic…..End If; • If <condition> then Logic…...Else Logic……End If; • If <condition> then Logic…..Elsif Logic…..Elsif Logic…..Else Logic…..End If;

21. Using If then Else clause example • Problem Statement • Get the customer_name from end user. Display the grade of the customer based on the following rules. • If the total sales made by that customer is greater than 15000 then call them as ‘Grade A’, if sales is between 10000 and 15000 then call them as ‘Grade B’, if sales is between 5000 and 10000 then call them as ‘Grade C’ and < 5000 as ‘Grade D’ • Consider you have two tables called customer and sales table. Customer table has cust_id, cust_name, contact_nm, phone and sales table has sales_id, sales_date, qty, price_per_qty and cust_id as the columns. • Result (In steps) • In the above requirement, we will have to receive the customer name as the argument. • We have to join the sales table and customer table to find the sales for that particular customer we are looking for • Check the sales against the business rules using If then Else clause and display the correct Grade. • In the next slide, we will write the PL/SQL program to solve the same.

22. PL/SQL code for Grading a customer • DECLARE v_cust_name varchar(30); v_total_sales number(8,2); v_grade char(1);BEGIN v_cust_name := &customer_name; Select sum(qty*price_per_qty) into v_total_sales from sales, customer where sales.cust_id = customer.cust_id and customer.cust_name = v_cust_name; If v_total_sales > 15000 then v_grade := ‘A’; Elsif v_total_Sales between 10000 and 15000 then v_grade := ‘B’; Elsif v_total_Sales between 5000 and 10000 then v_grade := ‘C’; Else v_grade : =‘D’; End If; DBMS_OUTPUT.PUT_LINE(‘Customer is Grade : ‘ || v_grade);END;

23. Testing the code which you wrote • Create the data in such a way that you have customer who has sales more than 15000, 15000, 10000, 7000, 3000 • Pass the correct customer name which exists in the database and make sure the code works. • Now pass the customer name which does not exists in the database and see what you get. • Problem: • If you provide the customer name which does not exists we want to display ‘No such customer exists in the database’ • If you provide the customer name which exists then display the grade of that customer • Go ahead and change the program which will work based on the above requirement

24. Exercise • Consider customer and location table. As an org, we are dealing with certain number of locations. • Read location and customer name, we have to validate location as well as customer name. If that customer is operating out of that location, then display information is right otherwise display ‘Information is not right’ • To solve the problem, you may be writing 3 different SQL statements, first to check whether location exists, next to check the ename and the last one to check whether the combination exists in the database • But from a block, you have only one exception area. Any one of the above sql may trigger the no_data_found? How do you decide which sql caused the no_data_found error?

25. Loops • Loops come in programming language where we execute the same piece of code many times based on the requirement. • We want apply the grading process to all the customers based on the sales they made, in this scenario we use looping concept to deal with all customers. • There are two types of loops in PL/SQL, Unconditional and Conditional Loops. • Unconditional loops, does not have any entry criteria, the control goes into the loop, checks the condition etc based on a condition it decides to execute or to quit the loop. • Conditional Loops in other hand, first check the condition then enters in to the loop. • Difference between Conditional and Unconditional loop is, unconditional loop executes at least once where as conditional loops may or may not execute at least once.

26. Unconditional Loop • Following is the syntax of Uncoditional Loop. • DECLARE ……BEGIN …… Loop ….. ….. Exit when <condition>End Loop;……END;

27. Conditional Loop • If you don’t know how many number of times you execute, but you know the condition to check, then use Do While loop. • while <condition> Loop…………End Loop; • If you know the number of times to execute the same code, then use For Loop. • For I in 1..100 Loop…….…….End Loop;

28. Exercise • Business - Floral website • We need to take order from customer, we have to deliver the order with in 10 hours. • Table structure is order_id, desc, ord_date,del_date,status • Use ord_seq sequence to get the next value which goes in as value in order_id • Read desc from the user • Insert a record into orders table with the current date time as the value for order_date, del_date should be order date + 10 hrs and status should be ‘NEW’

29. Cursor • Is a pl/sql variable through which we can handle multiple rows with in pl/sql block • When a select statement with in a pl/sql returns more than one row, oracle raises too_many_rows exception. In this scenario, we can use cursor to handle one record at a time. • Scenario where you cursor • If your corporation decides to give some discounts for your customers, based on how loyal he/she is, how much business we do with that customer etc etc, now we need to check some of the parameters before giving discounts, so we need to check one by one row from customer table and make a decision based on the rules. So here we cannot use a single update statement to the related tables. Now Cursor comes into picture. A cursor is nothing but a result set through which you can fetch one by one row.

30. Types of Cursor • They are two different types of cursors • Explicit Cursors • Implicit Cursors. • Implicit cursors is nothing but if you issue a select statement the server executes the query and stores the rows in a memory area in the server and returns the rows in network packets to the client, here you do not have the control in the result set of rows. So, any sql you write internally treats as implicit cursor • Explicit cursors is the sql statements where you have the control over the result set from a program where you can fetch one by one row from the result set. In this case, server holds the result set, and client fetches one record at a time.

31. Declaring and working with cursor • This program / block is used to display all the customer names. DECLARE        var_custname customer.cust_name%type;        CURSOR getcustnames IS SELECT cust_name FROM customer;BEGIN        OPEN getcustnames; --opening a cursor, actually execute the sql                                         --and places all the rows in server memory area         LOOP           FETCH getcustnames into var_custname; -- Fetching the current record                              Exit When getcustnames%NOTFOUND --If all the rows got over then                                                                          -- %NOTFOUND cursor attribute will be true.                 dbms_output.put_line(var_custname); -- Display the customer name       END LOOP;       Close getcustnames; -- Close the cursor so that server releases memory.END;

32. Visualization of Cursor

33. Steps to remember while using cursor • Declare a cursor • Open a cursor • Fetch data into variables from the cursor • Close the cursor • Example of a cursor which displays all the department names using cursor • Declare cursor dept_cur is select dname from dept; v_dname dept.dname%type;begin open dept_cur; Loop Fetch dept_cur into v_dname; Exit when dept_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_dname); End Loop; Close dept_cur;End;

34. Points to remember • Remember while working with cursors we must provide same number of variables as the number of columns we selected in the select statement of the cursor.You cannot fetch once you closed the cursor, if you do it will raise an exception called invalid_cursor. You can do fetch only after opening the cursor. Another exception it may raise is CURSOR_ALREADY_OPEN, if you are trying to open the cursor which is already open. • Cursor Attributes%ISOPEN returns TRUE if already the cursor is open. returns FALSE if its not opened.%NOTFOUND returns TRUE if the last fetch statement does not return a row.%FOUND returns TRUE if the last fetch statement return a row.%ROWCOUNT total number of rows returned so far.

35. For Update Cursor • DECLARE       v_deptno number(3);       CURSOR c1 IS select empno, deptno from emp FOR UPDATE;BEGIN        For c1_record IN c1 Loop            If deptno = 40 then               DELETE from emp WHERE CURRENT OF c1;            End If;        End Loop;        COMMIT WORK;END;

36. Procedure • Is a pl/sql block where we can store the code in the database. • We call PL/SQL block as unnamed block where as procedure is named block. • We can pass / receive arguments from procedures. • It’s a power full mechanism through which we implement the business / data processing of any organization • The other big difference is when you store the procedure it also stores the execution plan in a binary format in the database. • When you run a PL/SQL, every SQL statement has to go through four steps of SQL execution (check the syntax, parsing, execution plan and execute accordingly). When we write a SQL with in a stored procedure then only executes the sql based on the execution plan. Thus its much faster than the stored procedure

37. Syntax • CREATE OR REPLACE PROCEDURE procedure_name( argument1 in/out data type, argument2 in/out data type....)ASPL/SQL BlockEnd Procedure_name • IN argumentOUT argumentIN OUT argument • IN - pass the value from calling environment into the procedure.OUT - return a value from the procedure to the calling program.IN OUT - pass the value from calling program and the called program passes some other calculated value through the same variable to the calling program.

38. Sample Stored Procedure • Write a stored procedure when you sell a product, check if the qty_on_hand is equal to reorder level or less than reorder level, if so insert a row into the orders table. If you already placed the order with in last 2 days then do not place an order on that product. • CREATE OR REPLACE PROCEDUREcheck_update_reorder ( prod_id in number, curr_qty in number ) is v_reorder_level product.reorder_level%type; begin select reorder_level into v_reorder_level from products where product_id = prod_id; If curr_qty <= v_reorder_level then begin select 1 into v_check from orders where product_id = prod_id and order_date between trunc(sysdate) - 2 and trunc(sysdate); exception when no_data_found then insert into orders ( order_id, product_id, order_date ) values ( order_seq.nextval, prod_id, sysdate ); end; End If;end check_update_level;

39. Exercise (Stored procedure) We are creating this stored procedure to increment the salaries based on the following business rules. We get two arguments from end user one is deptno and the other is the budget of increment allocated to this department. We have increase the salary by 5% if the employee salary is > 50000 Rs, increase the salary by 7% if the employee salary is > 30K and < 50K, increase the salary by 8% if the salary is > 15K and <30K else increase the salary by 10% The total increments we gave to the employees should not exceed the budget value. If the exceeds the budget value then raise the error (‘Not enough budget and rollback if you updated any employees salary, otherwise commit the changes what you made to the salaries of each employee. (Use user defined exception to raise the error from the program) You can use the existing emp table.

40. Exercise • Web_cust (cust_id, cust_name,email_id, phone, status) • Email is a must • Email must have @ • Status <> ‘Bounced’ • Call center (cust_id, cust_name, city, phone, email, rep_name) • Phone number is a must • If no sales rep name reject those records • Agent_cust (cust_id, cust_name, city, org_name, phone) • Organization is a must • Priority of selection (1.Agent, 2.Call center 3.Web) • The Unique key to identify the duplicate is cust_name and city. • Move all the good records to target, all bad records to a table called rejected_records (record, reason)

41. Target definition • Cust_id • Cust_nm • City • Email_id • Phone • Org_nm • Call_center_rep • Source (valid values are ‘WEB’,’AGENT’,’CALL CENTER’)

42. Function - Syntax • CREATE OR REPLACE FUNCTION function_name(argument1 in/out data type, argument2 in/out data type....)RETURN data typeASPL/SQL blockEnd function_nameWhile writing a function we should have a return statement with in the PL/SQL block.You cannot execute a function as same as stored procedure. You should call a function from a PL/SQL block or from a sql statement or from another stored procedure or stored function, the reason being the value is returned by the function and that value should be in a variable.

43. Sample Function • Write a function to get the customer name from customer table by passing the customer number. • CREATE OR REPLACE FUNCTION get_custname ( var_custno CUSTOMER.CUST_NO%TYPE) return char AS    var_custnmhold CUSTOMER.CUST_NAME%TYPE;BEGIN    SELECT cust_name into var_custnmhold    FROM customer    WHERE cust_no = var_custno;    Return var_custnmhold;EXCEPTION    WHEN no_data_found then     Return  ' ';END get_custname;

44. Sample function • Write a Function to update the customer name by passing the customer number and the new name. If you find the row and updated then return1 else -1. • CREATE OR REPLACE FUNCTION func_upt_custname ( var_custno customer.cust_no%TYPE, var_custname customer.cust_name%TYPE ) return number ISBEGIN   Update customer set cust_name = var_custname   Where cust_no = var_custno;   IF SQL%FOUND then        Return 1   ELSIF SQL%NOTFOUND then        Return -1   END IFEXCEPTIONWHEN Others THEN     Return -1END func_upt_custname;

45. More functions • Pass cust_id and get the cust_grade based on the following business rule • Check the sales for the cust, if sales > 30000 then assign Grade ‘A’, if sales >20000 and < 30000 the Grade ‘B’, else grade ‘C’ • Call the function from an SQL as a column. • Select cust_name, fn_get_cust_grade(cust_id)from customer • Tip: Don’t call a function as part of select statement if that function does any other DML operation other than Select

46. Problem statement • We have the following business rule on employee entity. • As we recruit / promote employees to different grades. Based on the grade bracket is maintained. • If Grade ‘A’ the sal >=40K, if grade ‘B’ the sal should be between 30 and 40K, if grade ‘C’ then the sal should be between 15 and 30 other wise the sal < 15K • How do we make sure the data we have in the database follows this rules? • What are the ways you know in which we can implement the business rules?

47. Trigger • A trigger is a stored program which will get executed when an event occurs on a table which is nothing but an insert or update or delete statement. • You cannot call a trigger like a stored procedure or a function. • We cannot pass / receive any arguments to triggers. • We cannot have transactional statements like COMMIT or ROLLBACK • Following are different types of triggers on a table. • Insert Trigger   (Before statement, Before Row, After Row, After Statement) • Update Trigger (Before statement, Before Row, After Row, After Statement) • Delete Trigger   (Before statement, Before Row, After Row, After Statement)

48. Usage of Triggers • In following business scenarious we can use triggers. • To implement complex business rules. Some of the simple business rules like PK and FK can be implement through constraints. When constraints cannot take care of the business rules we may dependent on application layer / triggers to do the same. • To automate certain process we will depend on triggers. Per example, when the qty in hand goes less than the re order level, we need to place an order automatically. • We create audit columns like crt_date, crt_by, mod_date and mod_by in some of the data sensitive tables. • We create this because when some one changes the data, we need to know who did it and what time they did for audit purpose. • When DW wants only the changed / new data from operational resources, we may write triggers to update some flags and giving these records to DW.

49. Trigger Syntax • create or replace trigger • <trigger name> <event> • on <table_name> for <statement / row level> • begin • Logic • end;

50. Triggers (inserting, updating, deleting) • create or replace trigger tr_dept • before insert or update or delete • on dept for each row • begin • If INSERTING then • dbms_output.put_line ('Before trigger - INSERT'); • elsif UPDATING then • dbms_output.put_line ('Before trigger - UPDATE'); • else • dbms_output.put_line ('Before trigger - DELETE'); • end if; • end;