/*===
    328lect04-2.sql - creates and tests our first PL/SQL 
                stored procedures, hello_world and goofball

    to run this script (and so also create and compile the
        stored procedures hello_world and goofball, 
        as well as test them):
    start 328lect04-2.sql
    @ 328lect04-2
    
    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;

    to then run the successfully-compiled goofball
        *** from sqlplus ***
        from then on (since it is now STORED in your
	database):
    exec goofball()
    exec goofball

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

/*===
    our first PL/SQL procedure
===*/

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

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 an 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
===*/

/*===
    procedure: goofball: void -> void
    purpose: expects nothing, returns nothing, and,
        calls hello_world FOUR times (just to show it
        can!), so that, IF serveroutput is ON, it has the
        side-effect of printing to the screen 
        a cheery greeting, the current date, and the current 
        value of local variable quantity FOUR times
===*/

create or replace procedure goofball as
begin
    hello_world;
    hello_world();
    hello_world;
    hello_world();
end;
/
show errors

/* TESTING! */

/*===
    need to set serveroutput to on to be able to see
        dbms_output.put_line results in the
        session that runs this script
===*/

set serveroutput on

prompt
prompt ========
prompt TESTING hello_world
prompt
prompt Should see a cheery greeting, the current date,
prompt and that quantity is 2
prompt ========

exec hello_world

prompt ========
prompt Should again see a cheery greeting, the current date,
prompt and that quantity is 2
prompt ========

exec hello_world()

prompt
prompt ========
prompt TESTING goofball
prompt
prompt Should see, FOUR times, a cheery greeting, the current date,
prompt and that quantity is 2
prompt ========

exec goofball

prompt ========
prompt Should again see, FOUR times, a cheery greeting, the current date,
prompt and that quantity is 2
prompt ========

exec goofball()