------------ 
-- sp-from-java-setup.sql
-- 
-- contains a stored procedure sp_from_java, whose
--    whole purpose in life is to serve as an
--    example stored procedure to be called from
--    a Java JDBC application (or servlet...)
-- 
-- original version by: Ann Burroughs 
-- modified by: Sharon Tuttle 
-- last modified: 3-13-13
------------ 

drop table table_from_java2;

create table table_from_java2
(field1 integer,
 field2 varchar2(20),
 field3 number,
 field4 date,
 primary key (field1));

create or replace procedure sp_from_java
                            (first out number, second out char,
                             third in number,  fourth in varchar2,
                             fifth out number) as
    v_count integer;
begin

    fifth := 0;

    select count(*) 
    into first
    from table_from_java2;

    select count(*)
    into v_count
    from table_from_java2
    where field1 = third
    and field2 = fourth;

    if v_count = 0 then
        second := 'N';
    else
        second := 'Y';
    end if;

exception
    when others then
        fifth := -1;
end;
/        

insert into table_from_java2
values
(10, 'blue', 34.56, sysdate-1);

insert into table_from_java2
values
(20, 'red', 12.34, sysdate-1);

insert into table_from_java2
values
(30, 'green', 56.78, sysdate-1);

insert into table_from_java2
values
(40, 'yellow', 45.67, sysdate-1);

insert into table_from_java2
values
(50, 'purple', 78.9, sysdate-1);