why do we write create/replace for creating a procedure in PL/SQL

Can anyone explain why do we write CREATE OR REPLACE for 开发者_如何学Pythoncreating a stored procedure in PL/SQL?

OR REPLACE allows you to replace a procedure which already exists, in other words, you dont need to drop the procedure and recreate it each time you want to recreate it

REPLACE KEYWORD Allows you to Modify Data Base Objects Already exist.

consider below examples then you understand clearly.

CREATE PROCEDURE pr_greetings
IS 
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello');
END;

OUTPUT:

PROCEDURE PR_GREETINGS compiled

If we try to modify without REPLACE KEYWORD we get error, SO i should drop it and Re-create it. see below.

CREATE PROCEDURE pr_greetings              ``
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello World');
END;

OUTPUT:
ORA-00955: name is already used by an existing object

Now with REPLACE KEYWORD we can modify that

CREATE or REPLACE PROCEDURE pr_greetings
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello World');
END;

OUTPUT:
PROCEDURE PR_GREETINGS compiled.

I Hope You Understand Clearly, Thank you.

1 To modify the procedure without 'create or replace' you have to drop and recreate the object itself in two steps.

2 The main reason is to preserve the object grants:

SQL> connect to hr
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 
Connected as hr

SQL> 
SQL> create procedure dummy
  2  as
  3  begin
  4      null;
  5  end dummy;
  6  /
Procedure created


SQL> grant execute on dummy to bps;

Grant succeeded

SQL> connect bps
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 
Connected as bps

SQL> select * from user_tab_privs_recd where table_name ='DUMMY';

OWNER                          TABLE_NAME                     GRANTOR                        PRIVILEGE                                GRANTABLE HIERARCHY
------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --------- ---------
HR                             DUMMY                          HR                             EXECUTE                                  NO        NO
SQL> exec hr.dummy;

PL/SQL procedure successfully completed

SQL> connect hr
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 
Connected as hr

SQL> create or replace procedure dummy
  2  as
  3  begin
  4      null;
  5      dbms_output.put_line('dummy');
  6  end;
  7  /

Procedure created

SQL> select * from user_tab_privs_made where table_name ='DUMMY';

GRANTEE                        TABLE_NAME                     GRANTOR                        PRIVILEGE                                GRANTABLE HIERARCHY
------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --------- ---------
BPS                            DUMMY                          HR                             EXECUTE                                  NO        NO

SQL> connect bps
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 
Connected as bps

SQL> 
SQL> select * from user_tab_privs_recd where table_name ='DUMMY';

OWNER                          TABLE_NAME                     GRANTOR                        PRIVILEGE                                GRANTABLE HIERARCHY
------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --------- ---------
HR                             DUMMY                          HR                             EXECUTE                                  NO        NO


SQL> set serveroutput on
SQL> exec hr.dummy;

dummy

PL/SQL procedure successfully completed

SQL> connect hr
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 
Connected as hr
 SQL> drop procedure dummy;

Procedure dropped
SQL> create  procedure dummy
  2  as
  3  begin
  4      null;
  5      dbms_output.put_line('dummy');
  6  end;
  7  /

Procedure created
SQL> -- as you can see priviliges previously made are gone
SQL> select * from user_tab_privs_made where table_name ='DUMMY';

GRANTEE                        TABLE_NAME                     GRANTOR                        PRIVILEGE                                GRANTABLE HIERARCHY
------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --------- ---------

SQL> connect bps
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 
Connected as bps
SQL> select * from user_tab_privs_recd where table_name ='DUMMY';

OWNER                          TABLE_NAME                   GRANTOR                        
------------------------------ ---------------------------------------- 
SQL> exec hr.dummy;

begin hr.dummy; end;

ORA-06550: line 2, column 7:
PLS-00201: identifier 'HR.DUMMY' must be declared
ORA-06550: line 2, column 7:
PL/SQL: Statement ignored

SQL>