oracle stored procedures

Question

how can I make oracle procedure with oracle xe, how can I check if input is valid or not? ex: if my input is number and I type in char that procedure prints out something in that case, I've dealt with SQL but not with these kind of procedures? any help is appreciated

UPDATE

This was a dummy example .. what I meant is to start from the most simple thing then move on up to the more complicated examples, what I actually need is check if a field book in table books equals 0 then stop borrowing query to insert else insert.

Answer 1

Here is an example of the sort of process I think you want. I have had to make a couple of (I hope educated) guesses to fill in the gaps in your example.

create or replace procedure borrow_book
    ( p_book in books.id%type
      , p_borrower in library_member.id%type ) 
as
    cursor cur_book is
        select out_flag
        from books
        where id = p_book
        for update of out_flag;
    rec_book cur_book%rowtype;
begin
    open cur_book;
    fetch cur_book into rec_book;

    if rec_book.out_flag = 0
    then
        raise_application_error(-20000, 'Book is already out on loan.');
    else    
        insert into loans (book_id, borrower_id, issue_date)
        values (p_book, p_borrower, sysdate);
        update books
        set out_flag = 0
        where current of cur_books;
    end if;

    close cur_book;
end borrow_book;
/