Home >> FAQs/Tutorials >> Oracle DBA FAQ >> Index

Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions

By: FYIcenter.com

Part:   1  2   3  4  5  6  7 

(Continued from previous part...)

How To Create a Stored Procedure?

A stored procedure is a procedure with a specified name and stored into the current database. If you want to create a stored procedure, you can use the CREATE PROCEDURE statement. The example script below creates a stored procedure:

SQL> CREATE PROCEDURE Greeting AS
  2  BEGIN
  3    DBMS_OUTPUT.PUT_LINE('Welcome to FYICenter!');
  4  END;
  5  /
Procedure created.

How To Execute a Stored Procedure?

If you want to execute a stored procedure, you can use the EXECUTE statement. The example script below shows how to executes a stored procedure:

SQL> set serveroutput on;

SQL> CREATE PROCEDURE Greeting AS
  2  BEGIN
  3    DBMS_OUTPUT.PUT_LINE('Welcome to FYICenter!');
  4  END;
  5  /
Procedure created.

SQL> EXECUTE Greeting;
Welcome to FYICenter!

How To Drop a Stored Procedure?

If there is an existing stored procedure and you don't want it any more, you can remove it from the database by using the DROP PROCEDURE statement as shown in the following script example:

SQL> CREATE PROCEDURE Greeting AS
  2  BEGIN
  3    DBMS_OUTPUT.PUT_LINE('Welcome to FYICenter!');
  4  END;
  5  /
Procedure created.

SQL> DROP PROCEDURE Greeting;
Procedure dropped.

How To Pass Parameters to Procedures?

Store procedures or functions can take parameters. You need to define parameters while defining the procedure, and providing values to parameters while calling the procedure. The script below shows you how to do this:

SQL> CREATE OR REPLACE PROCEDURE DBA_TASK (day VARCHAR2) AS 
  2  BEGIN     
  3    IF day = 'MONDAY' THEN 
  4      DBMS_OUTPUT.PUT_LINE('Checking log files.');       
  5    ELSIF day = 'FRIDAY' THEN             
  6      DBMS_OUTPUT.PUT_LINE('Rebuild indexes.');          
  7    ELSE    
  8      DBMS_OUTPUT.PUT_LINE('Reading some papers.');      
  9    END IF; 
 10  END;      
 11  /

SQL> EXECUTE DBA_TASK('MONDAY');
Checking log files.

SQL> EXECUTE DBA_TASK('SUNDAY');
Reading some papers.

As you can see, procedures with parameters can make procedures more flexible.

How To Create a Stored Function?

A stored function is a function with a specified name and stored into the current database. If you want to create a stored function, you can use the CREATE FUNCTION statement. The example script below creates a stored procedure:

SQL> CREATE OR REPLACE FUNCTION GET_SITE
  2    RETURN VARCHAR2 AS
  3  BEGIN
  4    RETURN 'FYICentere.com';
  5  END;
  6  /
Function created.

(Continued on next part...)

Part:   1  2   3  4  5  6  7