Oracle PL/SQL Programming, 5th Edition

Performing Essential PL/SQL Tasks

Let’s turn to the highlights of creating, running, deleting, and otherwise managing PL/SQL programs, using SQL*Plus as the frontend. Don’t expect to be overwhelmed with detail here; treat this section as a glimpse of topics that will be covered in much greater detail in the chapters ahead.

Creating a Stored Program

To build a new stored PL/SQL program, you use one of SQL’s CREATE statements. For example, if you want to create a stored function that counts words in a string, you can do so using a CREATE FUNCTION statement:

CREATE FUNCTION wordcount (str IN VARCHAR2)
   RETURN PLS_INTEGER
AS
   declare local variables here
BEGIN
   implement algorithm here
END;
/

As with the simple BEGIN-END blocks shown earlier, running this statement from SQL*Plus requires a trailing slash on a line by itself.

Assuming that the DBA has granted you Oracle’s CREATE PROCEDURE privilege (which also gives you the privilege of creating functions), this statement causes Oracle to compile and store this stored function in your schema; if your code compiles, you’ll probably see a success message such as:

Function created.

If another database object, such as a table or package, named wordcount already exists in your Oracle schema, CREATE FUNCTION will fail with the error message ORA-00955: name is already used by an existing object. That is one reason that Oracle provides the OR REPLACE option, which you will want to use probably 99% of the time.

CREATE OR REPLACE FUNCTION wordcount (str ...