Stored Procedures:Advanced Concepts

Use your normal text editor to write the procedure. At the beginning of the procedure, place the command
CREATE PROCEDURE procedure_name AS   ...
For example, to use the example , you can create a text (source) file called get_emp.sql containing the following code:
CREATE PROCEDURE procedure_name AS ...
For example, to use the example , you can create a text (source) file called get_emp.sql containing the following code:

CREATE PROCEDURE get_emp_rec (emp_number IN emp.empno%TYPE,
emp_ret OUT emp%ROWTYPE) AS
BEGIN
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
INTO emp_ret
FROM emp
WHERE empno = emp_number;
END;

Then, using an interactive too such as SQL*Plus, load the text file containing the procedure by entering the command
SQLPLUS> @get_emp
to load the procedure into the current schema. (.SQL is the default file extension.) Note the slash (/) at the end of the code. This is not part of the code; it just activates the loading of the procedure.

When developing a new procedure, it is usually much more convenient to use the CREATE OR REPLACE . . . PROCEDURE command. This replaces any previous version of that procedure in the same schema with the newer version. This is done with no warning.

You can use either the keyword IS or AS after the procedure parameter list.

Privileges Required to Create Procedures
To create a stand-alone procedure or function, or package specification or body, you must meet the following prerequisites:

• You must have the CREATE PROCEDURE system privilege to create a procedure or package in your schema, or the CREATE ANY PROCEDURE system privilege to create a procedure or package in another user’s schema.

Attention: To create without errors, that is, to compile the procedure or package successfully, requires the following additional privileges:

>> The owner of the procedure or package must have been explicitly granted the necessary object privileges for all objects referenced within the body of the code;

>>The owner cannot have obtained required privileges through roles.

>> If the privileges of a procedure’s or package’s owner change, the procedure must be reauthenticated before it is executed.

>>If a necessary privilege to a referenced object is revoked from the owner of the procedure (or package), the procedure cannot be executed.

>>The EXECUTE privilege on a procedure gives a user the right to execute a procedure owned by another user.

>>Privileged users execute the procedure under the security domain of the procedure’s owner.

>>Therefore, users never have to be granted the privileges to the objects referenced by a procedure.

>>This allows for more disciplined and efficient security strategies with database applications and their users.

>>Furthermore, all procedures and packages are stored in the data dictionary

>>No quota controls the amount of space available to a user who creates procedures and packages.

Altering Stored Procedures

To alter a stored procedure , you must:-

>>use the CREATE OR REPLACE PROCEDURE command, which first drops the procedure or function if it exists, then recreates it as specified.

>> or first DROP it, using the DROP PROCEDURE  command.

>> then recreate it using the CREATE PROCEDURE command.

>>  The procedure  is dropped with no warning.

---