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 ASdeclare local variables go hereBEGINimplement 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:
Function created.
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.
CREATE OR REPLACE FUNCTION wordcount (str IN VARCHAR2) RETURN PLS_INTEGER ...Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Read now
Unlock full access