What are subprograms
This presentation is the property of its rightful owner.
Sponsored Links
1 / 11

What are Subprograms? PowerPoint PPT Presentation


  • 63 Views
  • Uploaded on
  • Presentation posted in: General

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.

Download Presentation

What are Subprograms?

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


What are subprograms

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.


Functions and procedures

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).


Creating procedures

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


Example what does this do

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;

/


Calling the procedure

Calling the Procedure

declare

howmany mylog.logon_num%TYPE;

begin

num_logged(‘John',howmany);

dbms_output.put_line(howmany);

end;

/


Errors in a procedure

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


Creating a function

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;


A function

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


Calling the function

Calling the function

declare

paulRate:=9;

Begin dbms_output.put_line(ratingMessage(paulRate));

end;

/


Advantages of procedures

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.


What are subprograms

  • 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.


  • Login