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 front end. 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 your own stored PL/SQL program, you use one of SQL’s CREATE statements. For example, if you want to create a stored function named wordcount 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 go hereBEGIN
implement algorithm hereEND; /
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 database administrator has granted you Oracle’s CREATE PROCEDURE privilege, this statement will cause Oracle to compile and store this stored function in your schema; if your code compiles, you’ll probably see a success message such as:
If a table or stored program 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 of the reasons that Oracle provides the OR REPLACE OPTION, which you will want to use probably 99% of the time.
OR REPLACEFUNCTION wordcount (str IN VARCHAR2) RETURN PLS_INTEGER ...