CREATE PROCEDURE [DATABSE_NAME].[PROCEDURE Name]
PROCEDURE [DATABSE_NAME].[PROCEDURE Name] ( IN {Parameter_name} {DATA TYPE OF parameter }, OUT [output paramaeter name ]{DataType OF parameter name))
BEGIN
[ DECLARE a INTEGER; ---Decalring variable name
{ SELECT |UPDATE|DELETE|INSERT} -----SQL Statements
END;
EXAMPLE:
CREATE PROCEDURE EMPLOYEE_db.EMP_PROC
( IN p_empno INTEGER , OUT p_empname VARCHAR(20),OUT p_sal DECIMAL(5,0))
BEGIN
SELECT EMPNAME ,SAL INTO :p_empname,p_sal FROM EMPLOYEE_db.EMP
WHERE EMPNO=:p_empno;
END;
Executing a procedure:
CALL B16909_db.EMP_PROC(1002,p_empname,p_sal);
---Output:
p_empname p_sal
RAMU 10790
----------------------The same Procedure we can write in Oracle like following this-------
CREATE OR REPLACE PROCEDURE V_RETRIEVE(V_EMPNO IN NUMBER,
OR REPLACE PROCEDURE V_RETRIEVE(V_EMPNO IN NUMBER, V_SAL OUT NUMBER(5,0),
OUT NUMBER(5,0), V_NAME OUT VARCHAR2)
OUT VARCHAR2) IS
BEGIN
SELECT ENAME, JOB INTO V_NAME, V_JOB
FROM EMP WHERE EMPNO=V_EMPNO;
END;
; DESC PROCEDURE V_RETRIEVE;
PROCEDURE V_RETRIEVE;
