PLSQL Stored Procedure

6. Procedure Stored Procedure
Syntax:
Create or replace procedure procedure_name
[(Argument_name [{In | Out | in out}] type,
...
Argument_name [{In | Out | in out}] type)]
{Is |}
Begin
Statements
End;

A stored procedure is also called a nameblock. It can be stored in a database and called wherever needed. In addition, it can contain function, package, and trigger.

If there is a nameed block, of course, there is a week without a name: anonymous Block

Anonymous block:
It cannot be stored in the database and parsed every time it is used. It cannot be called in other blocks.
Syntax:
Declare
// Declare and retrieve
Begin
// Block
End

Go back to procedure and check out a simple demo:

Create or replace procedure pro_hello is
// The above header is equivalent to an anonymous block: declare
Begin
Dbms_output.put_line ('hello, World ');
// Output A Hello, world string on the screen. Each time you enter sqlplus, the set serveroutput on function is enabled to view the output.
End;

You can call the preceding stored procedure in an anonymous block,
Begin
Pro_hello;
// Name of the stored procedure
End;

The above is a simple example! There is no practical value. Generally, databases involve adding, deleting, modifying, and querying data:
Ultra-simple query:
Create or replace procedure pro_lab (
P_id s_emp.id % Type // p_id is the form parameter of pro_lab.
) Is
V_fname s_emp.first_name % type;
Begin
Select first_name
Into v_fname
// Into assigns the query result to the variable v_fname.
From s_emp
Where id = p_id;

Dbms_output.put_line ('hello, '| v_fname );
End;

// Call procedure
File-Name: lab1. SQL
Begin
Pro_lab (1); // 1: real parameters of pro_lab
End;

Procedure Parameter mode:
In:
The default mode. When procedure is called, the value of procedure's real parameter is passed to procedure. Inside procedure, the form parameter of procedure is read-only:
Out:
When procedure is called, any real parameters of procedure will be ignored. In procedure, the parameters can only be written:
In out:
The combination of in and out. when procedure is called, the value of the real parameter can be passed to the procedure; inside the parameter, it can also be read or written; when the procedure ends, the control will return to the control environment, and the content of the form parameter will be assigned to the real parameter during the call.

Create or replace procedure pro_param (
P_in number, // The parameter type cannot contain the precision or scale, or the week cannot contain: (3)
P_out number // The parameter of procedure
) Is
Begin
Dbms_output.put_line ('in Param: '| p_in );
P_in: = 100; // The in mode parameter is readable only.

Dbms_output.put_line ('out Param: '| p_out );
P_out: = 200; // The out mode can only contain values from procedure to external.
End;

// Call procedure
File-Name: lab3. SQL
Declare
V_out number: = 100; // This real parameter cannot be passed into procedure.
Begin
Pro_param (2, v_out); // 2: real parameter v_out: Out in Mode
End;

Exercise:
Query the name of a leader
Create or replace procedure p_lab (
P_id in s_emp.id % type,
P_fname out s_emp.first_name % type) is
V_fname s_emp.first_name % type;
V_mid s_emp.id % type;
Begin
Select decode (A. first_name, null, B. first_name, A. first_name) Name
Into v_fname
From s_emp A, s_emp B
Where B. manager_id = A. ID (+) and B. ID = p_id;

P_fname: = v_fname;
/*
Select first_name, manager_id
Into v_fname, v_mid
From s_emp
Where id = p_id

If v_mid is null then
P_fname: = v_fname;
Else
Select first_name
Into p_fname
From s_emp
Where id = v_mid
End if
*/
End;

File-Name: lab6. SQL
Declare
V_name s_emp.first_name % type;
Begin
P_lab (3, v_name );
Dbms_output.put_line ('manager is: '| vname );
End;

Note that it is recommended by the teacher.