Procedures Oracle My SQL Prof Arfaoui COM 390

Procedures Oracle & My. SQL Prof. Arfaoui. COM 390 Chapter 5 Oracle 11 g: PL/SQL Programming

Chapter Objectives After completing this lesson, you should be able to understand: Named program units Creating a procedure Calling a procedure from another procedure Using the DESCRIBE command with procedures Debugging procedures using DBMS_OUTPUT Using subprograms The scope of variables, exception handling and transaction control Removing procedures 2 Oracle 11 g: PL/SQL Programming

Brewbean’s Challenge • Develop programming modules for specific tasks such as calculating taxes or updating inventory 3 Oracle 11 g: PL/SQL Programming

Named Program Units PL/SQL blocks executed thus far have been anonymous blocks Now we will assign a name to the block and save it in the database as a stored program unit This makes program units reusable 4 Oracle 11 g: PL/SQL Programming

Stored Routines Procedures, Functions, Triggers, Packages Fast - A stored routine is held on the database server. Reusability - A stored routine is created once but used many times. Code efficiency - Stored routines also reduce code duplication. Debugging and testing an application also becomes easier. Application security - Application only sees the data it needs. 5 Oracle 11 g: PL/SQL Programming

Create Procedure Statement Syntax 6 Oracle 11 g: PL/SQL Programming

Parameters – Make Program Units Reusable Mechanisms used to send values in and out of program units IN – is the default mode. When you define an IN parameter in a stored procedure, the calling program has to pass an argument to the stored procedure. In addition, the value of an IN parameter is protected. OUT – the value of an OUT parameter can be changed inside the stored procedure and its new value is passed back to the calling program. IN OUT – an IN OUT parameter is a combination of IN and OUT parameters. The calling program may pass the argument. The stored procedure can modify the IN OUT parameter. Pass the new value back to the calling program. 7 Oracle 11 g: PL/SQL Programming

Create Procedure - Oracle • Procedure to determine shipping cost Oracle DBMS CREATE OR REPLACE PROCEDURE SHIP_COST_SP( p_qty IN Numeric, p_ship OUT Numeric) AS BEGIN IF p_qty > 10 THEN p_ship : = 11. 00; ELSIF p_qty > 5 THEN p_ship : = 8. 00; ELSE p_ship : = 5. 00; END IF; 8 END; Oracle 11 g: PL/SQL Programming

Create Procedure - My. SQL DBMS DELIMITER // CREATE PROCEDURE SHIP_COST_SP(IN p_qty BEGIN IF p_qty > 10 THEN SET p_ship = 11. 00; ELSEIF p_qty > 5 THEN SET p_ship = 8. 00; ELSE SET p_ship = 5. 00; END IF; END// DELIMITER; Numeric, OUT p_ship Numeric)

Execute Procedure - Oracle DBMS Declare a variable to hold value from OUT parameter DECLARE Call procedure addressing both lv_ship_num NUMERIC(6, 2); parameters BEGIN SHIP_COST_SP(7 , lv_ship_num); Display value returned to verify DBMS_OUTPUT. PUT_LINE('Ship Cost = ' || lv_ship_num); END; 10 Note: Parameter arguments are passed positionally by default Oracle 11 g: PL/SQL Programming

Execute Procedure - My. SQL DBMS CALL SHIP_COST_SP(7 , @lv_ship_num); SELECT CONCAT('Ship Cost = ' , @lv_ship_num ) AS "OUTPUT";

IN OUT mode - Oracle Send value in and out via the same parameter Oracle DBMS CREATE OR REPLACE PROCEDURE phone_fmt_sp(p_phone IN OUT VARCHAR 2) AS BEGIN p_phone : = '(' || SUBSTR(p_phone, 1, 3) || ')' || SUBSTR(p_phone, 4, 3) || '-' || SUBSTR(p_phone, 7, 4); END; 12 Oracle 11 g: PL/SQL Programming

IN OUT mode - My. SQL DBMS DELIMITER // CREATE PROCEDURE phone_fmt_sp (INOUT p_phone VARCHAR(25)) BEGIN SET p_phone = CONCAT('(' , SUBSTR(p_phone, 1, 3) , ')' , SUBSTR(p_phone, 4, 3) , '-' , SUBSTR(p_phone, 7, 4)); END//

Execute Procedure - Oracle DBMS Declare fmt_Phone varchar(200) : ='7181234567'; begin phone_fmt_sp(fmt_Phone); DBMS_OUTPUT. PUT_LINE(fmt_Phone); end;

Execute Procedure - My. SQL DBMS set @fmt_Phone = '7181234567'; CALL phone_fmt_sp(@fmt_Phone); select @fmt_Phone AS "OUTPUT";

Calling a Procedure from another procedure Oracle DBMS CREATE OR REPLACE PROCEDURE ORDER_TOTAL_SP (p_bsktid IN number, p_cnt out number, p_sub out number, p_ship out number, p_total out number) AS BEGIN DBMS_OUTPUT. PUT_LINE('order total proc called'); SELECT SUM(quantity), SUM(quantity * price) into p_cnt, p_sub FROM bb_basketitem WHERE idbasket = p_bsktid; Calling procedure ship_cost_sp(p_cnt, p_ship); ship_cost_sp p_total : = NVL(p_sub, 0) + NVL(p_ship, 0); DBMS_OUTPUT. PUT_LINE('order total proc ended'); Oracle 11 g: PL/SQL END; 16 Programming

Remove a Procedure DROP PROCEDURE procedure_name; 17 Oracle 11 g: PL/SQL Programming

TO DO Execute ORDER_TOTAL_SP in Oracle Create the equivalent My. SQL Stored procedure to ORDER_TOTAL_SP Call ORDER_TOTAL_SP in My. SQL

DESCRIBE Command | Oracle & My. SQL Lists the parameters of a program unit Oracle: DESCRIBE ORDER_TOTAL_SP; My. SQL: SHOW CREATE PROCEDURE phone_fmt_sp; 19 Oracle 11 g: PL/SQL Programming

Debugging with DBMS_OUTPUT 20 Oracle 11 g: PL/SQL Programming

Debugging with DBMS_OUTPUT 21 Oracle 11 g: PL/SQL Programming

Subprograms A program unit defined within another program unit: Must be declared in the DECLARE section of the containing program unit Can only be referenced by the containing program unit 22 Oracle 11 g: PL/SQL Programming

Variable Scope When nesting blocks, are variables shared? Inner blocks can use variables from outer blocks 23 Oracle 11 g: PL/SQL Programming

Variable Scope (continued) 24 Oracle 11 g: PL/SQL Programming

Exception-Handling. Flow 25 Oracle 11 g: PL/SQL Programming

Transaction Control Scope The scope refers to the group of DML statements that are affected by a particular transaction control statement By default, a session has a single DML queue and a transaction control statement would affect all DML in the queue regardless of which program unit initiated the statement DML statements of a program unit can be treated separately or as an autonomous transaction 26 Oracle 11 g: PL/SQL Programming

Autonomous Transaction The pragma instructs the PL/SQL compiler to establish a PL/SQL block as autonomous or independent. 27 Oracle 11 g: PL/SQL Programming

Summary Named program unit assigns a name to a program unit so it can be reused Parameters are used to pass values in and out of program units Stored program units are saved in the database Parameter modes include: IN, OUT, and IN OUT Use DBMS_OUTPUT. PUT_LINE statement to debug 28 Oracle 11 g: PL/SQL Programming

Summary (continued) A subprogam is a procedure declared within another procedure Variable scope must be considered with nested blocks Autonomous transactions must be explicitly created Remove a procedure with the DROP PROCEDURE command 29 Oracle 11 g: PL/SQL Programming