Understanding pass by value and pass by reference is essential for effective programming. These concepts determine how data is passed to functions and procedures

SQL Function and Procedures

1. Chapter 8: Functions and Procedures Fernando Hernandez Amilcar Hernandez David Donovan Luis Cornejo

2. Creation Option 2 Deterministic Clause & Parallel_enable clause - Guarantees that a function works the same way with any inputs. This requires that a function does not read or write data from external sources, like packages or database tables.

3. Creation Option Cont’d 3 PIPELINED Clause - provides improved performance when functions return collections, like varray , table collections, reference cursors.

4. Understand positional, named, and mixed call notation. Calling notation is a way of providing values to the parameters of a subroutine such as PL/SQL function or a stored procedure. 4 Types of Calling Notations Positional Notation Named Notation and Mixed calling notation Positional Calling Notations Is the most common calling notation Specify the value for each formal parameter in a sequential manner Named Calling Notations Pass values to the formal parameters using their names Useful when you have a subroutine with parameters where some of those parameters are mandatory Mixed Calling Notation Call subroutines using the combination of named as well as positional Helpful where the parameter list is defined with all mandatory parameters first and optional parameters next

5. 5 Example of Named Calling Notation for calling a PL/SQL Subroutines It has 3 parameters. Among these 3 parameters 2 are mandatory and 1 is optional with a default value 0 DECLARE   var_result  NUMBER; BEGIN   var_result := add_num(var_1 => 10, 30 ,var_3 =>19);   DBMS_OUTPUT.put_line('Result ->' || var_result); END; CREATE OR REPLACE PROCEDURE emp_sal (dep_id NUMBER, sal_raise NUMBER) IS BEGIN     UPDATE employees SET salary = salary * sal_raise WHERE department_id = dep_id;   DBMS_OUTPUT.PUT_LINE ('salary updated successfully'); END;  / Stored Procedure call using positional notation in Oracle Database Example of Mixed calling notation for calling PL/SQL subroutines CREATE OR REPLACE FUNCTION add_num (var_1 NUMBER, var_2 NUMBER DEFAULT 0, var_3 NUMBER ) RETURN NUMBER IS BEGIN   RETURN var_1 + var_2 + var_3; END; / Example: Positional Notation for calling PL/SQL Subroutines

6. Pass-by-value functions They receive a copy of the values and not the real variable. They return a value but the Original values do not change. The return value must be assign to a variable in a PL/SQL block or be returned as an expression in a SQL query. 6

7. Creating a pass-by-value function SQL> CREATE OR REPLACE FUNCTION my_function_name 2 (parameter_name IN VARCHAR2) RETURN VARCHAR2 IS 7 Function parameters names, if many they are separated by commas. Datatype for each parameter input and output The IN keyword shows that the parameter is read-only meaning that the function will use a copy of the value or variable. This keyword can be omitted because read-only is the default behavior of PL/SQL.

8. Creating a pass-by-value function 8 SQL> CREATE OR REPLACE FUNCTION my_function_name 2 (parameter_name IN VARCHAR2) RETURN VARCHAR2 IS 3 lv_name VARCHAR2(10) := ‘World’; 4 BEGIN 5 IF parameter_name IS NOT NULL THEN 6 lv_name := parameter_name; 7 END IF; 8 RETURN ‘Hello ‘ ||lv_name|| ‘!’; 9 END; 10 / The parameter cannot be use as a left-side operator because is read-only but can be use for operations or assign its value to another variable inside the function.

9. Understand pass-by-reference functions You use pass-by-reference functions when you want to perform an operation, return a value from the function, and alter one or more actual parameters. These functions can only act inside the scope of another program or environment. 9 IN mode . An IN mode variable is really a copy of the variable, but you can ask to pass a reference. PL/SQL typically obliges when using the IN mode of operation. An IN mode variable is really a copy of th It can assign a literal number or string as the actual parameter because the IN mode only requires a value because it discard the variable reference and value when it completes.

10. Understand pass-by-reference functions 10 IN OUT mode: An IN OUT mode variable is typically a reference to a copy of the actual variable for a couple reasons. If the function or procedure fails the original values are unchanged (this is a departure from the behavior of collections passed as actual parameters in Java). You can assign values to the formal parameter at runtime when using an IN OUT mode variable An IN mode variable is really a copy of th The external value is changed inside the function and at completion of the function the external variable passed as an actual parameter is changed OUT mode: An OUT mode variable is very much like an IN OUT mode variable with one exception. There is no initial value in it. You must assign a value to an OUT mode variable because it has no value otherwise. If the function or procedure fails, the external variable is unchanged A n OUT mode variable is always null until it is assigned a value in the function

11. 11 Thanks! For Watching!