Summary About Oracle PL/SQL (Part 2)

  • A function must return value.
  • A procedure mainly used to an action. It allowed to not return value.
  • Basic syntax to create procedure
    CREATE [OR REPLACE] PROCEDURE procedurename
    [(parametername [in|out|in out] type, [...])]
    [IS|AS]
    BEGIN
       <procedure body>
    END;
    

    Note:
    [xxx] => optional
    OR REPLACE => used to modify existing procedure.
    IN => value from outside
    OUT => return value
    IN OUT => return value and the value from outside allocated on same parameter
    AS => used for standalone procedure

  • Basic syntax to create function
    CREATE [OR REPLACE] FUNCTION functionname
    [(parametername [in|out|in out] type, [...])]
    RETURN returndatatype
    [IS|AS]
    BEGIN
       <function body>
    END [functionname];
    
  • To execute standalone procedure we can:
    1. EXECUTE procedurename
    2. BEGIN
      procedurename
      END;
  • Cursor. There are 2 types of cursor:
    1. Implisit Cursor. Automatically created when sql statement is executed.
    2. Explisit cursor. Created manually by user.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s