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

You stored procedures parameters are already strongly typed. If you have an "int" parameter someone types in "ABC" for the value, Oracle will through it out. You won't have to/need to.

Answer 2

Something like this?

create or replace PROCEDURE BOOK() AS
BEGIN
    declare cursor cur_b is 
        select * from books;
    BEGIN
        FOR book_row IN cur_b LOOP
            IF book_row.book=0 THEN
                INSERT INTO ...
            END IF;
        end loop;
    end;
END BOOK;

Answer 3

Your problem does not sound as if you would need PL/SQL.

A single SQL-insert should do (if I understand your question right):

INSERT INTO new_table
SELECT id, val FROM books WHERE book = 0;

If you still need a procedure, put that into a procedure:

CREATE OR REPLACE PROCEDURE my_proc AS
BEGIN
  INSERT INTO new_table
  SELECT id, val FROM books WHERE book = 0;
END my_proc;

Try to avoid looping over a cursor in PL/SQL and inserting values, when it could be done in a single SQL.

Answer 4

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;
/