Stored procedures are used to perform specific operations and complete certain functions. They can have input parameters in and output parameters out. Store functions are used to return specific results. Use the return clause at the head of the function, specify the type of return, and use the return clause in the execution part to return results. Package is used to organize stored procedures and functions. The package is divided into two parts: package description and package body.

1. System, software and constraints

• win10
• oracle11g
• sql developer

2. Stored procedure operation

• 1 write a stored procedure to add data to the table

# Create stored procedure, no parameters
create or replace procedure pro_demo1 
is
begin
  insert into emp(empno,ename,sal) values (88,'wanhe',2000);
end;
# Call stored procedure
exec pro_demo1();  --exec yes sqlplus command
//perhaps
call  pro_demo1(); --call yes sql command

• 2 write a stored procedure to update employee salary information by entering employee name and salary

#Create a stored procedure with two input parameters
create or replace procedure pro_demo2 (name varchar2,newSalary number)
as
begin
  update emp set sal=newSalary where ename=name;
end;
# Call stored procedure
exec pro_demo2('SCOTT',9000);

• 3. Write a stored procedure to return the employee name to the outgoing parameter through the employee number of the incoming parameter

# There are input and output parameters
create or replace procedure pro_demo3 (no in emp.empno%type,username out emp.ename%type)
is 
begin
  select ename into username from emp where empno=no;
end;
# Invoking such stored procedures in blocks
declare
  v_no emp.empno%type;
  v_ename emp.ename%type;
begin
  v_no:=&empno;
  pro_demo3(v_no,v_ename);
  dbms_output.put_line('Employee name:'||v_ename);
end;

3. Storage function operation

• 1 create a storage function to input 10 and return the result

# Enter 10 and return the result
create or replace function fun_demo1(num number) return number
is
  v_num number(10);
begin
  v_num:=num+10;
  return v_num;
end;  
# Calling storage function in block
declare
  v_num number(10);
begin
  v_num:=fun_demo1(25);
  dbms_output.put_line('The return value of the function is:'||v_num);
end;

4. Package operation

# Create a package, only declaration is not implemented
create or replace package pac_demo1
is
  procedure pro_demo8(name emp.ename%type,newSal in number);
  function fun_demo8(name varchar2) return number;
end; 
# Create the package and complete the implementation
create or replace package body pac_demo1
is
  procedure pro_demo8 (name emp.ename%type,newSal in number)
  is
  begin
    update emp set sal=newSal where ename=name;
  end;
  function fun_demo8 (name varchar2) return number
  is
    v_sal emp.sal%type;
  begin
    select sal into v_sal from emp where ename=name;
    return v_sal;
  end;
end;
# call
exec pac_demo1.pro_demo8('SCOTT',1111);
select pac_demo1.fun_demo8('SCOTT') from dual;

These are the stored procedures, stored functions and packages in oracle.