oracle 如何调试 带输入参数,输出结果集的存储过程
create or replace procedure P_BOOKSEARCH
(
v_BookName in books.bookname%type,
v_BookAuthor in books.bookauthor%type,
v_BookPub in books.bookpub%type,
v_BookType in books.booktype%type,
results out sys_refcursor
)
as
begin
if v_BookType='0000' then
open results for select bks.bookid,bks.bookname,bktp.typename,bks.bookauthor,to_char(bks.bookpubdate,'yyyy-mm-dd'),bks.bookpub
from
books bks,booktype bktp
where
bks.booktype=bktp.booktypeno
and bks.bookauthor like '% '||v_BookAuthor||'% '
and bks.bookname like '% '||v_BookName||'% '
and bks.bookpub like '% '||v_BookPub||'% ';
else
open results for select bks.bookid,bks.bookname,bktp.typename,bks.bookauthor,to_char(bks.bookpubdate,'yyyy-mm-dd'),bks.bookpub
from
books bks,booktype bktp
where
bks.booktype=bktp.booktypeno
and bks.bookauthor like '% '||v_BookAuthor||'% '
and bks.bookname like '% '||v_BookName||'% '
and bks.bookpub like '% '||v_BookPub||'% '
and bks.booktype=v_BookType;
end if;
end;
存储过程可以正确编译,但是调用的时候只有表头,没有数据,单单没有输入内容的时候sql查出来都有值的