Create a Stored Procedure in Oracle

Please comment if you like the video

PL/SQL : Simple Procedure Example

CREATE OR REPLACE PROCEDURE EXAMPLE_PROC
IS
CURSOR EMP_CUR IS
SELECT *
FROM EMP;

EMP_REC EMP_CUR%ROWTYPE;

BEGIN

OPEN EMP_CUR;

LOOP

FETCH EMP_CUR INTO EMP_REC;
EXIT WHEN EMP_CUR%NOTFOUND ;
DBMS_OUTPUT.PUT_LINE(EMP_REC.ENAME);
END LOOP;

CLOSE EMP_CUR;
END;



COMMAND USED TO EXECUTE PROCEDURE :
EXEC EXAMPLE_PROC;

OUTPUT :

JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

PL/SQL : Simple Explicit Cursor Example

DECLARE

CURSOR EMP_CUR IS
SELECT *
FROM EMP;

EMP_REC EMP_CUR%ROWTYPE;

BEGIN

OPEN EMP_CUR;

LOOP

FETCH EMP_CUR INTO EMP_REC;
EXIT WHEN EMP_CUR%NOTFOUND ;
DBMS_OUTPUT.PUT_LINE(EMP_REC.ENAME);
END LOOP;

CLOSE EMP_CUR;
END;


INPUT :
NA

OUTPUT :

JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

PL/SQL : Simple Query with two sections

DECLARE                     -- Declaration section
v_ename emp.ename%type ;

BEGIN                        -- Execution section
Select ename into v_ename
from emp
where empno = '7219';

DBMS_OUTPUT.PUT_LINE(v_ename);

END;


INPUT : NA
OUTPUT : John (the corresponding ename for empno given will be displayed)