4、存储过程(procedure)

create function getBookCount return number as
begin
   declare book_count number;
   begin
        select count(*) into book_count from t_book;
        return book_count;
   end;
end;


set serverout on;
begin
    dbms_output.put_line(getBookCount());
end;




create function getTableCount(table_name varchar2) return number as
begin
       declare recore_count number;
       query_sql varchar2(300);
       begin
               query_sql:='select count(*) from ' || table_name;
               execute immediate query_sql into recore_count;
               return recore_count; 
       end;
end;


set serverout on;
begin
    dbms_output.put_line(getTableCount('t_book'));
end;




create procedure addBook(bookName in varchar2,typeId in number) as
begin
       declare maxId number;
       begin
              select max(id) into maxId from t_book;
              insert into t_book values (maxId+1,bookName,typeId);
       end;
       
end;


execute addBook('java好东西111',1);


create or replace procedure addBook2(book_Name in varchar2,typeId in number) as
begin
       declare maxId number;
       n number;
       begin
         select count(*) into n from t_book where bookname=book_Name;
         if(n>0) then
            dbms_output.put_line('已存在');
            return;
          end if;
          select max(id) into maxId from t_book;
          insert into t_book values (maxId+1,book_Name,typeId);
          dbms_output.put_line('插入了');
       end;
end;


execute addBook2('java好东西222',1);


create or replace procedure addBook3(book_Name in varchar2,typeId in number,n1 out number,n2 out number) as
begin
       declare maxId number;
               n number;
       begin
               select count(*) into n1 from t_book;
               select count(*) into n from t_book where bookname=book_Name;
               if(n>0) then
                  dbms_output.put_line('已存在');
                  return;
               end if;   
               select max(id) into maxId from t_book;
               insert into t_book values (maxId+1,book_Name,typeId);
               commit;
               select count(*) into n2 from t_book;
       end;
end;


declare n1 number;
        n2 number;
begin
        execute addBook3('aaa',2,n1,n2);
        dbms_output.put_line(n1 || '.........' || n2);
end;