CREATE PROCEDURE

Examples

Creating a Procedure: Example The following statement creates the procedure remove_emp in the schema hr. The PL/SQL is shown in italics:

CREATE PROCEDURE remove_emp (employee_id NUMBER) AS
   tot_emps NUMBER;
   BEGIN
      DELETE FROM employees
      WHERE employees.employee_id = remove_emp.employee_id;
   tot_emps := tot_emps - 1;
   END;
/

==================================================================
JUSTIN EXAMPLE

https://www.youtube.com/watch?v=IJbnSUhDGcI


create table name fnama varchar2(20);

select * from name;
no rows will be there


Make sure the object is not there becauuse we use syntax create or replace
SQL>select object_name from dba_objects where object_name='JOB1' and object_type=procedure


NOW CREATE PROCEDURE

SQL>create or replace procedure job1 as
begin
insert into names values ('data1');
commit;
dbms_lock.sleep(15);
insert into names values ('data2');
commit;
end;
/
CHECK PROCEDURE BEING CREATED

SQL>select object_name from dba_objects where object_name='JOB1' and object_type=procedure

RUNNING PROCEDURE
SQL>exec job1;

CHECK
SQL>select * from name;

CHECKING TEXT OF PROCEDURE

SQL>select text from dba_source where name='JOB1';

EXCUTING PROCEDURE
SQL>exec job1;
===================================================================
Advantage of procodeure
easy Maintainence
Security
Perforamnce
No network latency



Stored procedure1) has a name,  2)take parameter and return parameter 3) can be stored in data dictionory
==========================================================
You can check error
SQL>show error
==================================================================

cd /tmp
vi helloworld.sql
CREATE
PROCEDURE HELLOWORLD AS
x VARCHAR2(20);
BEGIN
x:=' Hello World';
DBMS_OUPUT.PUT_LINE (x);
END;
/

SQL>@c;/tmp/helloworld.sql;
SQL>set serveroutput on;
SQL>exec HELLOWORLD :
=======================

REMOVE PROCEDURE
drop procedure procedurename
=============================================