/*===
    hello.sql - creates and tests our first PL/SQL subroutine,
                the stored procedure hello_world

    to run this script (and so also create and compile the
        stored procedure hello_world, as well as test it):
    start hello.sql
    @ hello
    
    to then run the successfully-compiled hello_world
        *** from sqlplus ***
        from then on (since it is now STORED in your
	database):
    exec hello_world()
    exec hello_world

    to then run the successfully-compiled hello_world
        *** from another PL/SQL subroutiune ***
	from then on (since it is now STORED in your
	database):
    hello_world();
    hello_world;
 
    by: Sharon Tuttle
    last modified: 2025-02-13
===*/

/*===
    procedure: hello_world: void -> void
    purpose: expects nothing, returns nothing, and has the
        side-effect of (IF serveroutput is on) printing to
        the screen a cheery greeting, the current date,
	and the current value of local variable quantity

    examples:
        if it is currently February 12, 2025, and in SQL/Plus you run:
        
        exec hello_world()

        and serveroutput is on, you should see printed to the screen:
Hello, world! on 25-FEB-12
quantity is: 2

        if you run:
   
        exec hello_world

        and serveroutput is on, you should see printed to the screen:
Hello, world! on 25-FEB-12
quantity is: 2

===*/

create or replace procedure hello_world as
    /*===
        for a PL/SQL procedure, the declaration part is between its
        header and the BEGIN starting its body
    ===*/

    curr_date date;
    quantity integer := 1;
    
begin
    /*===
        use INTO clause to set local variable curr_date
	to the single date that this SELECT projects
    ===*/

    select sysdate
    into curr_date
    from dual;

    /*===
        note PL/SQL's assignment operator!
    ===*/

    quantity := quantity + 1;

    /*===
        examples of printing to the screen
	*   BUT will ONLY be seen if have done:
	    set serveroutput on
	    ...in your current sqlplus session
	    
	*   note that || works fine with non-string values,
	    hooray!
    ===*/

    dbms_output.put_line('Hello, world! on ' || curr_date);
    dbms_output.put_line('quantity is: ' || quantity);
end;
/
show errors

/*===
    SEE the / above? that's needed so your stored procedure,
        once created, will also be COMPILED!
    *   fun fact: do not put a COMMENT on the same line as the /,
        either!  ( 8-/ guess how I know this... )

    SEE the show errors above? That will display the error
        messages from the latest thing compiled, if any
===*/

/*===
    say that I want to see dbms_output.put_line results in the
        session that runs this script
===*/

set serveroutput on

/*=== TESTING PL/SQL procedure hello_world ===*/

prompt
prompt ============
prompt TESTING hello_world, should see Hello, world! on the current date
prompt and then a quantity of 2:
prompt (using: exec hello_world() )
prompt

/*===
    use SQL*Plus exec command to execute a successfully-compiled
        stored procedure or function
        *** from sqlplus ***
===*/

exec hello_world()

prompt ============
prompt TESTING hello_world, should see Hello, world! on the current date
prompt and then a quantity of 2:
prompt (using: exec hello_world )
prompt

exec hello_world()