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.
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 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 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:
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.
OR REPLACEFUNCTION wordcount (str ...