Step by Step Learn Oracle Stored Procedure

Hello everyone, how about your day, in this chapter we will discuss about how to make stored procedure in oracle.First open your IDE for oracle in this case i will use PL/SQL and please following step at bellow:(Log in with system).
1. Create user :
Create user [USER_NAME] identified by [PASSWORD];
grant connect, resources to [USER_NAME] (give permission to access database).

Example:
Create user dbDataService identified by data12345;
grant connect, resource to dbDataService;

Note:
Press F8 to execute query
2. Log in with dbDataService (at this case)-> Create table.
Create table[TABLE_NAME]([PROPERTY_NAME][VARIABEL_TYPE])

Example:
Create table Student (NO varchar2(100) primary key not null, Name varchar2(100));

3. Fill data at Student table
Insert into Student values('1','Hendry');
Insert into Student values('2','Marco');

4. Open SQL WINDOW. After that please copy following code at bellow to make stored procedure.(at this case i will use curosr to read all row).
CREATE OR REPLACE PROCEDURE[STOREDPROCEDURE_NAME]
AS
BEGIN
END;

Example:
CREATE OR REPLACE PROCEDURE SP_SHOWSTUDENT
AS
CUROSR STUDENTCURSOR IS
SELECT NO,NAMA FROM STUDENT;
BEGIN
FOR D IN STUDENTCURSOR LOOP
DBMS_OUTPUT.PUT_LINE('NO'||D.NO);
DBMS_OUTPUT.PUT_LINE('NO'||D.NAMA);
END LOOP;
COMMIT;
END;

5.Execute stored procedure.
BEGIN
[STOREDPROCEDURE_NAME];
END;

Example:
BEGIN
SP_SHOWSTUDENT;
END;

Okay that's is it and finnaly congratulation you have made STORED PROCEDURE in oracle. Thank you for see this tutorial i hope this tutorial can help you..Have a nice day..^^