Oracle Stored Procedures

Establish stored procedures on ORACLE server that can be called by multiple applications, can pass parameters to stored procedures, and can be passed back to stored procedures

To create a stored procedure syntax:
CREATE [OR REPLACE] PROCEDURE procedure_name
[(argment [{in | Out | In Out}] Type,
argment [{in | Out | In Out}] Type]
{is | As}
< type. Description of the variable >
BEGIN
< Executive section >
EXCEPTION
< optional exception error handlers >
END;

Cases:

1 CREATE OR REPLACE PROCEDUREdelemp2(V_empnoinchEmp.empno%TYPE)3  as4  　　 No_result EXCEPTION;5 BEGIN6  　　 DELETE  fromEmpWHEREEmpno=V_empno;7  　　 IFSql%NOTFOUND Then8  　　　　 RAISE No_result;9 END IF;TenDbms_output. Put_Line ('encoded as'||V_empno||''s staff has been removed!'); One EXCEPTION A  　　  whenNo_result Then  -Dbms_output. Put_Line ('The data you need doesn't exist!'); - 　　  whenOTHERS Then theDbms_output. Put_Line (SQLCODE||'---'||sqlerrm); - ENDDelemp;

Call a stored procedure
Once the stored procedure is established, the user can invoke the run in Sqlplus, Oracle development tools, or third-party development tools as long as it is authorized.
ORACLE uses the EXECUTE statement to implement a call to a stored procedure:
Exec[ute] Procedure_name (Parameter1, parameter2 ...)

Cases:

CREATE OR REPLACE PROCEDUREProc_demo (Dept_no Number DEFAULT Ten, sal_sum out Number, Emp_count out Number) isBEGIN 　　 SELECT SUM(SAL),COUNT(*) intoSal_sum, Emp_count fromEmpWHEREDeptno=Dept_no; EXCEPTION whenNo_data_found ThenDbms_output. Put_Line ('The data you need doesn't exist!');  whenOTHERS ThenDbms_output. Put_Line (SQLCODE||'---'||sqlerrm);ENDProc_demo;

Call Method:

DECLAREV_num Number; V_sum Number(8,2);BEGINProc_demo ( -, V_sum, v_num); Dbms_output. Put_Line ('Total salary of unit 30th:'||V_sum||', number of persons:'||v_num); Proc_demo (Sal_sum=V_sum, Emp_count=v_num); Dbms_output. Put_Line ('Total salary of unit 10th:'||V_sum||', number of persons:'||v_num);END;

Delete procedure
You can use the drop statement to delete a procedure:
DROP PROCEDURE Proceduer_name;

Oracle Stored Procedures