what are subprograms n.
Download
Skip this Video
Download Presentation
What are Subprograms?

Loading in 2 Seconds...

play fullscreen
1 / 11

What are Subprograms? - PowerPoint PPT Presentation


  • 96 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'What are Subprograms?' - olinda


Download Now 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.
slide11

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.