Oracle stored procedures with input and output parameters

(i) using input parameters
Requirements: Add a record in Emp_copy, empno the maximum value for the existing empno +1,ename cannot be empty and must be greater than 0,deptno to 60.
To create a stored procedure:

Create or Replace procedureInsert_emp (emp_nameinch varchar2, Dept_noinch  Number) asbegin  DeclareMax_empno Number; begin    if(Emp_name is NULL orLength (Emp_name)= 0) Then      return; End if; if(Dept_no!=  -) Then      return; End if; Select Max(empno) intoMax_empno fromemp_copy; Insert  intoEmp_copy (empno, ename, Deptno)Values(Max_empno+ 1, Emp_name, dept_no); End;Endinsert_emp;/

Call the stored procedure and verify that:
(1)

Sql>ExecuteInsert_emp ('Li Si', -);P L/Sqlproceduresuccessfully completed. SQL>Col empno format99999; Col ename format a15;col deptno format99999;SelectEmpno, ename, Deptno fromEmp_copywhereDeptno=  -; EMPNO ename DEPTNO------ --------------- ------  7981Li Si -

(2)

SQL>execute insert_emp ('6);P l/procedure  Successfully completed. SQL>Selectfromwhere=6; SQL>

(ii) Use of output parameters
Requirement: On the basis of the previous requirement, the number of records before and after inserting the data should be emp_copy separately.
To create a stored procedure:

Create or Replace procedureInsert_emp (emp_nameinch varchar2, Dept_noinch  Number, Original_count out Number, Current_count out Number) asbegin  DeclareMax_empno Number; begin    if(Emp_name is NULL orLength (Emp_name)= 0) Then      return; End if; if(Dept_no!=  -) Then      return; End if; Select Count(1) intoOriginal_count fromemp_copy; Select Max(empno) intoMax_empno fromemp_copy; Insert  intoEmp_copy (empno, ename, Deptno)Values(Max_empno+ 1, Emp_name, dept_no); Select Count(1) intoCurrent_count fromemp_copy; End;Endinsert_emp;/

To call a stored procedure:

DeclareCount1 Number; Count2 Number;beginInsert_emp ('Wang Wu', -, Count1, Count2); Dbms_output.put_line ('Original Count of table Emp_copy is' ||count1); Dbms_output.put_line ('Current count of table Emp_copy is' ||count2);End;/OriginalCount  of TableEmp_copy is  the Current Count  of TableEmp_copy is  -PL/SqlprocedureSuccessfully completed.

(iii) Use of input/output parameters
The in-out parameter combines the above two parameter types, passing the value to the process body and also being assigned to the process body. In-out parameters can be used as input or as output.
Requirements: Achieve two-digit switching.
To create a stored procedure:

Create or Replace procedureSwap (value1inchOut Number, value2inchOut Number) asbeginvalue1:=Value1+value2; value2:=Value1-value2; Value1:=Value1-value2;Endswap;/

To call a stored procedure:

DeclareA Number:=  A; b Number:=  -;beginDbms_output.put_line ('before swap:a =' ||A|| ', B =' ||b);  Swap (A, b); Dbms_output.put_line ('After swap:a =' ||A|| ', B =' ||b);End;/before Swap:a=  AB=  -After swap:a=  -B=  APL/SqlprocedureSuccessfully completed.

Oracle stored procedures with input and output parameters