What are Subprograms?

1. What are Subprograms? • 1. Subprograms are named PL/SQL blocks that a. can take parameters . b. can be invoked. • 2. PL/SQL has two types of subprograms called procedures and functions. • 3. Generally, you use I. a procedure to perform an action II. a function to compute a value.

2. Functions and Procedures • Up until now, our code was in an anonymous block • It was run immediately • It is useful to put code in a function or procedure so it can be called several times • Once we create a procedure or function in a Database, it will remain until deleted (like a table).

3. Creating Procedures CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter1 [mode1] datatype1, parameter2 [mode2] datatype2, . . .)] IS|AS PL/SQL Block; • Modes: • IN: procedure must be called with a value for the parameter. Value cannot be changed • OUT: procedure must be called with a variable for the parameter. Changes to the parameter are seen by the user (i.e., call by reference) • IN OUT: value can be sent, and changes to the parameter are seen by the user • Default Mode is: IN

4. Example- what does this do? Table mylog create or replace procedure num_logged (person IN mylog.who%TYPE, num OUT mylog.logon_num%TYPE) IS BEGIN select logon_num into num from mylog where who = person; END; /

5. Calling the Procedure declare howmany mylog.logon_num%TYPE; begin num_logged(‘John',howmany); dbms_output.put_line(howmany); end; /

6. Errors in a Procedure • When creating the procedure, if there are errors in its definition, they will not be shown • To see the errors of a procedure called myProcedure, type SHOW ERRORS PROCEDURE myProcedure in the SQLPLUS prompt • For functions, type SHOW ERRORS FUNCTION myFunction

7. Creating a Function • Almost exactly like creating a procedure, but you supply a return type CREATE [OR REPLACE] FUNCTION function_name [(parameter1 [mode1] datatype1, parameter2 [mode2] datatype2, . . .)] RETURN datatype IS|AS PL/SQL Block;

8. A Function create or replace function rating_message(rating IN NUMBER) return VARCHAR2 AS BEGIN IF rating > 7 THEN return 'You are great'; ELSIF rating >= 5 THEN return 'Not bad'; ELSE return 'Pretty bad'; END IF; END; / NOTE THAT YOU DON'T SPECIFY THE SIZE

9. Calling the function declare paulRate:=9; Begin dbms_output.put_line(ratingMessage(paulRate)); end; /

10. Advantages of Procedures: • Security : • Stored procedures can help enforce data security. • You can grant users access the procedure that can query a table, but not grant them access to the table itself. • Memory Allocation : • There is reduction in memory required as stored procedures have shared memory capabilities, so only one copy of the procedure needs to be loaded for execution by multiple users. • Productivity : • There is an increase in development productivity as by writing a single procedure you can avoid redundant coding and thus increase in productivity. • If few programs needs to perform same process, that process does not have to be coded in the program itself. • It can be coded in a procedure, and the programs can then call that procedure.

11. Application Integrity : • Stored procedures can improve the integrity of an application, as a procedure needs to be tested only once to guarantee that it returns an accurate result. • Once you have a procedure that returns accurate results, all programs calling that procedure receive the same result. • So the possibility of individual programmers committing coding errors, as far as the procedure results are concerned, can be reduced. • Performance : • Stored Procedures improve database Performance in the following ways: • Amount of information sent over a network is less, as the procedure is executed on the server itself and only the result is sent to the calling program. • No compilation step is required to execute the code, since a procedure is compiled before it is stored.