Oracle stored procedures and examples (stored procedures for several parameter scenarios)

Stored Procedures
Stored procedures can have multiple or 0 input and output parameters, but usually do not return a value. Stored procedures are already compiled code, so the execution is highly efficient
Stored Procedure creation statement

create [or Replace] procedure stored procedure name [(Input, output parameters, In/out mode parameters)]
is/as
begin
sentences;
[Exception
 sentences;]
End stored procedure name;

Example of a stored procedure with no parameters

Create or Replace procedure Pro_no_par is
begin
update employees set salary=salary+100 where Employee_id=100;
  commit;
Dbms_output.put_line (' Wages have been adjusted. ');
End Pro_no_par;
-Execute, using execute execute
pro_no_par;

Results:

Anonymous block has been completed the
salary has been adjusted.
Execute using anonymous block
begin
Pro_no_par;
End;
Anonymous block has been completed the
salary has been adjusted.

Example of a stored procedure with in-mode parameters
There are three types of arguments: specifying name passing; passing by position; mixed-mode delivery
Note Mixed-mode delivery begins at the location that is passed with the specified name, and the following argument must use the "Specify name Pass"

Create or Replace procedure Pro_in_par
(var_1 in Nvarchar2,
  var_2 into number)   is
begin
Update Employees set salary=salary+var_2 where first_name=var_1;
commit;
Dbms_output.put_line (var_1| | ' The wages have been increased ' | | var_2| | '! ');
End Pro_in_par;
Execute
Execute pro_in_par (' Steven ', 100);

Results:

Anonymous block has been completed
Steven's salary has been increased 100!
Execute Pro_in_par (var_1=> ' Steven ', var_2=>100);
Anonymous block has been completed
Steven's salary has been increased 100!
Execute Pro_in_par (' Steven ', var_2=>100);
Anonymous block has been completed
Steven's salary has increased by 100!

--for example, mixed parameter input, the following parameter does not use "Specify name delivery"
Execute Pro_in_par (var_1=> ' Steven ');
Error starting executing command on row 42:
Execute Pro_in_par (var_1=> ' Steven ')
error Report:
ORA-06550: Line 1th, column 34th: 
PLS-00312: A location-related parameter does not indicate its relevance
ORA-06550: Line 1th, column 7th: 
pl/sql:statement ignored
06550.00000-  "lines%s , column%s:\n%s "
*cause:    usually a pl/sql compilation error.
*action:

Example of a stored procedure with out mode and in out mode parameter

CREATE PROCEDURE Pro_out_par
(var_1 in Out number,
  var_2 out Employees.first_name%type,
  var_3 out employees . Phone_number%type) is
begin
Select First_name,phone_number to var_2,var_3 from employees where employee_id= var_1;
End Pro_out_par;
-Implementation of
declare 
ex_var_1 number;
Ex_var_2  Employees.first_name%type;
Ex_var_3  Employees.phone_number%type;
Begin
ex_var_1:=102;
Pro_out_par (ex_var_1,ex_var_2,ex_var_3);
Dbms_output.put_line (' Employee Number ' | | ex_var_1| | ' The employee's name is; ' | | ex_var_2| | ', contact tel is: ' | | ex_var_3);
End

Results

Anonymous block has completed employee
number 102 employee name is; Lex, the contact number is: 515.123.4569

Only effort will not betray
Blog for reference only, welcome to visit. If there are mistakes, I hope to criticize them.