Functions are usually instrumental, and stored procedures are usually complex operations of DML

Function has return type return

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

set serveroutput on;
begin
  dbms_output.put_line('surface t_book Yes'|| getBookCount() ||'Bar data');
end;

Functions with parameters

execute execution string

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


begin
  dbms_output.put_line('Table has'|| getTableCount('t_bookType') ||'Bar data');
end;

stored procedure

In doesn't come in

Out, out, out

in out

Judge the operation, if the book name exists, do not insert data into the t ﹣ book

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);
    commit;
  end;
end addBook;

execute addBook('java',1);


create procedure addBook2(bN in varchar2,typeId in number) as
begin
  declare maxId number;
  n number;
  begin
    select count(*) into n from t_book where bookName=bN;
    if(n>0) then
     return;
    end if;
    select max(id) into maxId from t_book;
    insert into t_book values(maxId+1,bN,typeId);
    commit;
  end;
end addBook2;

execute addBook2('java3',1);

out will return to where it was called

Number of records before and after execution

create procedure addBook3(bN 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=bN;
    if(n>0) then
     return;
    end if;
    select max(id) into maxId from t_book;
    insert into t_book values(maxId+1,bN,typeId);
    select count(*) into n2 from t_book;
    commit;
  end;
end addBook3;

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

Packages, effective management functions and stored procedures

The project is divided into several modules, which manage the functions and stored procedures of each module, and call the management stored procedures and functions through packages

Variables can be defined in the package. Variables are shared

create package pkg_book as
   function getbookcount return number;
   function getTableCount(table_name varchar2) return number;
   procedure addBook(bookName in varchar2,typeId in number);
end pkg_book;

create package body pkg_book as

       function getBookCount return number as
          begin
            declare book_count number;
            begin
              select count(*) into book_count from t_book;
              return book_count;
            end;
        end getBookCount;
        
        
        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 getTableCount;
          
          
          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);
                commit;
              end;
            end addBook;


end pkg_book;

set serveroutput on;
begin
  dbms_output.put_line('surface t_book Yes'|| pkg_book.getBookCount() ||'Bar data');
end;