Oracle Create Procedure

Oracle Create Procedure

To create a standalone stored procedure uses the CREATE PROCEDURE statement.

Create Procedure syntax

CREATE OR REPLACE PROCEDURE 
	proc_name (arg1 data_type, ...)
AS
BEGIN
....
END;
/

Create Procedure example

CREATE PROCEDURE remove_customer (p_id NUMBER)
AS
v_name varchar2(255);
BEGIN
  SELECT name INTO v_name FROM CUSTOMERS WHERE id=p_id;
  DELETE FROM customers WHERE id=p_id;
  DBMS_OUTPUT.put_line(v_name||' with id: '||p_id||' was deleted.');
END;
/

Call Procedure example

BEGIN
remove_customer(4);
END;