PLSQL_OverView_2
Objectives: PL/SQL Blocks:
  • There are three types of blocks , anonymous, stored programs and trigger
  • Stored Program:
    • Must have an unique name
    • Can be called by the uses.
    • There are two types of stored programs
      • Function and Procedure: These are the routines, which must be compiled within a PL/SQL block.
      • Function:
        • A function must return a value of a data type to the caller.
        • A function can accept many data-type but returns value of one data type only.
        • A DML (insert, update) SQL statement can be used in a function. but user can't call this function using a SQL statement. If a function is called by a SQL statement performing DML function, the query fails.
        • The functions are to retrieve value where as the procedures are used to manipulate values.
      • Procedure:
        • A procedure may not return a value to the caller.
        • But may be constructed with parameters to return some values to the caller.
        • A procedure can't use DML statement
  •  
Get help with the functions:

 
PLSQL : Procedure, Functions and Packages
A stored Procedure or Function is a precompiled PL/SQL block, saved at database with a name which can be used by a client routine.

Advantages of having stored procedures:

  • Better performance as it reduced net work traffic.
  • Scalability: Uses shared memory, further enhanced with the use of Cursor (a Pointer to a row), and trigger ( which responds to an event).
  • Better Security, and data collection.
  • This block can have OWNER'S OR INVOKER'S PRIVILEGES  set up options.
  • ERROR HANDLING : On error quit the program with grace
  • User defined variable: Allow user defined variables
     
  • Portable: Code is portable to any computer and system
  • OOP and abstract data type: Support object oriented programming
  • Basic Format of a Procedure or function:

    CREATE [OR REPLACE] PROCEDURE name [(parameter[,parameter, ...])] {IS
    |AS}
    [local declarations]
    BEGIN
    executable statements
    [EXCEPTION
    exception handlers]
    END [name];
     
  • Execute a
    EXECUTE command
    A procedure can be executed using EXECUTE command. To execute a procedure either you must be the owner of the procedure or you must have EXECUTE privilege on the procedure.

    SQL> execute procedure_name(param1, param2);
  • A Stored function Must Return a value to the caller routines.

begin
dbms_output.put_line(getdueamt(10));
end;
An anonymous block: you can only create one anonymous block per session.

In the example below I used two independent nested blocks. In the nested block all the local variable are hidden from each other, where as global variables are not. The values of the global variables can be overwritten in the last statements. 

Procedural block: Below is an example of creating a procedural block, you may call this block with Execute SQL statement or from another PL/SQL block

--define_proc1.sql
create or replace procedure define_proc1
IS
BEGIN
DBMS_OUTPUT.PUT_LINE( 'Hello World');
END;
/
EXEC define_proc1;
/
begin
define_proc1;
end;
/
 
Book says use AS when you are creating standalone function or procedure

--define_proc2.sql
create or replace procedure define_proc2
AS
-- use as for a stand alone procedure
BEGIN
DBMS_OUTPUT.PUT_LINE( 'Hello World');
END;
/
EXEC define_proc1;
/
begin
define_proc1;
end;
/