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