Summary About Oracle PL/SQL (Part 1)

  • Basic syntax:
    DECLARE
    <declaration section>
    BEGIN
    <executable section>
    EXCEPTION
    <exception section>
    END;
    
  • To declare a variable : *name_of_variable* *datatype* := *defaultvalue*;
  • %type : used for declare variables with relation to the data type of a column in an existing table.
  • %rowtype : used in cursors to declare a single variable which contain a single record from resultset of a cursor or table without needing to specify individual variable.
  • Constant : in variable declaration its mean that the variable does not allowed to be changed.
  • Condition syntax:
    IF (thecondition) THEN
    <the process>
    END IF;
    

    with else condition

    IF (thecondition) THEN
    <the process>
    ELSE
    <the else process>
    END IF;
    

    nested else if

    IF (thecondition) THEN
    <the process>
    ELSE IF (the next condition) THEN
    <the process>
    ELSE
    <the else process>
    END IF;
    

    case statement

    CASE variable_name
    WHEN 'thevalueofvariable1' THEN <statement1>
    WHEN 'thevalueofvariable2' THEN <statement2>
    ELSE <statement3> --this one is default statement
    
  • Looping syntax:
    LOOP
    <the process>
    <condition to exit from loop>
    <the process>
    END LOOP;
    

    using while statement

    WHILE (condition) LOOP
    <the process>
    END LOOP;
    

    using for statement

    FOR variable_name IN firstValue .. lastValue LOOP
    <the process>
    END LOOP;
    

Hope this article can help. See you on the next part.. 🙂

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