Oracle PL/SQL Oracle stored Procedures

A stored procedure is a named PL/SQL program block that can be given parameters, stored in a database, and can be called by a user. Since the stored procedure is compiled code, it is not necessary to compile again at the time of the call, which improves the running efficiency of the program. In addition, the use of stored procedures can implement modular design of the program

Syntax for stored procedures:

create [or Replace] procedure procedure_name (parameter [{in| out}] data_type,

parameter [{in| out}] data_type ...)

{Is|as}

Begin

Executable section;

exception

Exception handlers;

End

Program Demo:

Write a stored procedure to insert a piece of data into the EMP table

Create of Replace procedure My_procedure3 is
Begin
INSERT into EMP (empno,ename) VALUES (9527, ' Star Master's Pak Fu ');
End

The console calls the stored procedure:

exec process name (parameter);

or write a PLSQL statement block

Begin

Process name (parameter);

End

Parameters:

Oracle has three parameter modes in and out in represents the user's input parameters Program Demo:

Create or Replace procedure my_procedure (In_no in Number,in_name on VARCHAR2) is
Begin
INSERT into EMP (empno,ename) values (in_no,in_name);
End

The out parameter represents the user's output

Program Demo:

Create or Replace procedure My_pro (In_no in Number,out_name out varchar2,out_sal off number) is
Begin
Select Ename,sal to Out_name,out_sal from EMP where empno=in_no;
End

in Sqlplus, you want the value after the process with the output has finished executing Method 1: Write anonymous blocks

Declare
V_name Emp.ename%type;
V_sal Emp.sal%type;
Begin
My_pro (7369,v_name,v_sal);
Dbms_output.put_line (V_name);
Dbms_output.put_line (v_sal);
End

Attention:

In and out features with both in and out, type parameters can be read and written in the process

Oracle PL/SQL Oracle stored Procedures