Oracle Stored Procedures

Oracle Stored Procedures

So what's this business with Oracle allowing programmers to put programs in databases? That's right. They're called Oracle stored procedures, and they're quite useful. Mooh the Cow walks you through writing, creating, debugging, and deleting a procedure. What you will learn An Oracle stored procedure is a program stored in an Oracle database. Now this is unusual – we expect data in a database, but not programs! However, you will soon see how useful this can be. We often refer to an Oracle stored procedure as a procedure. You will learn to: write a procedure in Oracle's PL/SQL programming language using the Notepad text editor. create a procedure on an Oracle database, a process which will automatically compile and save the procedure. run a procedure stored on an Oracle database from the SQL*Plus tool supplied by Oracle. change a procedure by re-creating it on the Oracle database. debug a procedure by introducing, viewing and fixing a compilation error. drop a procedure a process which will permanently remove a procedure from an Oracle database. Next, you'll find out what you need to know before you start. Now, to get you started, let's learn how to write a procedure. Procedures are written in Oracle's PL/SQL programming language. Let’s write a harmless procedure that does nothing, so we can compile and run it with no fear of damage to your database. In Notepad, type: CREATE OR REPLACE PROCEDURE skeleton IS BEGIN NULL; END; Save your file as skeleton.sql. Let’s go through the procedure line by line. The lines are numbered for your convenience: 1 CREATE OR REPLACE PROCEDURE skeleton 2 IS 3 BEGIN 4 NULL; 5 END; Line 1: CREATE OR REPLACE PROCEDURE is an SQL statement that instructs Oracle to create a procedure called skeleton, and to overwrite it if it exists. Line 2: The IS keyword signals that a PL/SQL body will follow. Line 3: The BEGIN keyword signals the start of a PL/SQL body. Line 4: The NULL PL/SQL statement indicates that no action should be performed. We cannot just leave it out, because at least one statement is required in a PL/SQL body. Line 5: The END keyword signals the end of the PL/SQL block. Now let's move on to creating a procedure. The SQL statement CREATE OR REPLACE PROCEDURE creates, compiles and saves a procedure on an Oracle database. You need the CREATE PROCEDURE system privilege to create a procedure in your own schema. Open SQL*Plus from Windows and log on to your database. From SQL*Plus, open your skeleton.sql file. SQL*Plus loads the contents of your skeleton.sql file into the SQL*Plus buffer or memory area and presents the SQL*Plus command prompt: 1 CREATE OR REPLACE PROCEDURE skeleton 2 IS 3 BEGIN 4 NULL; 5* END; SQL> Execute the contents of the SQL*Plus buffer. Type a front slash and press like this: SQL> / SQL*Plus informs you the procedure has been created successfully and presents the SQL command prompt: Procedure created. SQL> Now your procedure is created, compiled and saved on your Oracle database. We have a procedure now, so let's run it. Run your procedure from the SQL*Plus command prompt with the EXECUTE command like this: SQL> EXECUTE skeleton; SQL*Plus assures you the procedure executed successfully: PL/SQL procedure successfully completed. The EXECUTE statement is easy and fast to type. You can also run your procedure from within an unnamed PL/SQL block. At the SQL*Plus command prompt, it looks like this: SQL> BEGIN 2 SKELETON; 3 END; 4 / PL/SQL procedure successfully completed. By calling your procedure from within an unnamed PL/SQL block, you can even call your procedure twice, like this: SQL> BEGIN 2 SKELETON; 3 SKELETON; 4 END; 5 / PL/SQL procedure successfully completed. SQL*Plus refers to the unnamed PL/SQL block when it says "PL/SQL procedure successfully completed." So even though we called the skeleton procedure twice, we only get one message back. So much for going the extra mile! Now that we've run our procedure, what do we need to do if we want to change it? Let's write a procedure that outputs the string "Hello World!". Open your skeleton.sql file in Notepad. Replace the NULL statement iswith the DBMS_OUTPUT.PUT_LINE procedure call. Your program should look like CREATE OR REPLACE PROCEDURE skeleton IS BEGIN DBMS_OUTPUT.PUT_LINE('Hello World!'); END; Save your file as skeleton.sql. From SQL*Plus, open your skeleton.sql file. SQL*Plus loads the contents of your skeleton.sql file into the SQL*Plus buffer or memory area and presents the SQL*Plus command prompt: SQL> 1 CREATE OR REPLACE PROCEDURE skeleton 2 IS 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE('Hello World!'); 5* END; SQL> Execute the contents of the SQL*Plus buffer. Type a front slash and press like this: SQL> / SQL*Plus informs you the procedure has been created successfully and presents the SQL command prompt: Procedure created. SQL> Run your procedure from the SQL*Plus command prompt with the EXECUTE command like this: SQL> EXECUTE skeleton; SQL*Plus assures you the procedure executed successfully: PL/SQL procedure successfully completed. But wait a minute! We wanted to see the string "Hello World!" Where did that go? SQL*Plus is quirky. A SET command is needed before output is shown from the DBMS_OUTPUT.PUT_LINE procedure. So let's do that. At the SQL*Plus command prompt, type: SQL> SET SERVEROUTPUT ON SQL*Plus remains secretive and provides no feedback. Execute your procedure again. From the SQL*Plus command prompt, type: SQL> EXECUTE skeleton; Now it works! SQL*Plus rewards you with: Hello World! PL/SQL procedure successfully completed. Suppose there is a problem with our procedure. Then what? We debug it, and that's what we're going to do next. Let’s introduce a compilation error into your procedure declaration. Open your skeleton.sql file in Notepad. Replace the DBMS_OUTPUT.PUT_LINE procedure call with the NULLL statement (notice the three "l"s!), an invalid PL/SQL statement. Your program should look like this: CREATE OR REPLACE PROCEDURE skeleton IS BEGIN NULLL; END; Save your file as skeleton.sql. From SQL*Plus, open your skeleton.sql file. SQL*Plus loads the contents of your skeleton.sql file into the SQL*Plus buffer or memory area and presents the SQL*Plus command prompt: SQL> 1 CREATE OR REPLACE PROCEDURE skeleton 2 IS 3 BEGIN 4 NULLL; 5* END; SQL> Execute the contents of the SQL*Plus buffer. Type a front slash and press like this: SQL> / Your procedure is compiled and saved on the database. However, SQL*Plus warns us of compilation errors: Warning: Procedure created with compilation errors. Let’s see the compilation errors. First, we need to run two SET commands to ensure the SQL*Plus buffer does not overflow. At the SQL*Plus command prompt, type: SQL> SET ARRAYSIZE 1 SQL> SET MAXDATA 60000 SQL> Again, SQL*Plus remains secretive of the result. Let's see the errors. At the SQL*Plus command prompt, type: SQL> SHOW ERRORS PROCEDURE skeleton You should see the compilation error: LINE/COL ---------------------------------------------- ERROR ---------------------------------------------- 4/3 PLS-00201: identifier 'NULLL' must be declared 4/3 PL/SQL: Statement ignored Oracle doesn't recognize the NULLL statement with the three "l"s. But Oracle won't hold it against you. Change your procedure declaration in Notepad by inserting the proper NULL statement, and follow the steps to create your procedure again on the Oracle database. What if you want to completely remove a procedure from your database? That's what we'll cover next. If you no longer want a procedure in your database, you can remove it. The SQL statement DROP PROCEDURE removes a procedure from a database. Please use caution, because the effect is permanent! DROP PROCEDURE is classified in SQL as a Data Definition Language (DDL) statement. Other examples of SQL statements in this category include CREATE, ALTER, RENAME and TRUNCATE. Oracle issues an automatic COMMIT after a DDL statement is executed. Therefore, no rollback is possible after executing a DDL statement. You need the CREATE PROCEDURE system privilege to create a procedure in your own schema. At the SQL*Plus command prompt, issue the DROP PROCEDURE SQL statement to remove your procedure called skeleton: SQL> DROP PROCEDURE skeleton; SQL*Plus assures us the procedure has been removed: Procedure dropped. Now let's sum up it all up. An Oracle stored procedure is a program stored in an Oracle database. Procedures are written in the PL/SQL programming language. You can use the Notepad text editor to write a procedure. Save each procedure with a .sql file name extension. Log on to an Oracle database with Oracle's SQL*Plus tool. It submits SQL and PL/SQL statements to the Oracle database. The SQL statement CREATE OR REPLACE PROCEDURE creates, compiles and saves a procedure on an Oracle database, regardless of whether the procedure contained compilation errors or not. Run a procedure stored on an Oracle database from the SQL*Plus tool. Use the EXECUTE statement, or an anonymous PL/SQL block to call your procedure. The clause OR REPLACE from the SQL statement CREATE OR REPLACE PROCEDURE overwrites an existing stored procedure on the Oracle database. View a procedure's compilation errors with the SQL*Plus command SHOW ERRORS. The SQL statement DROP PROCEDURE permanently removes a procedure from an Oracle database. Would you like to find out more? Keep reading. You can read more about Oracle stored procedures in the Oracle documentation. The following topics are covered: Parameters: Our basic procedure accepts no parameters. You can declare input parameters to accept data, and output parameters to return data to the calling environment. Variables: Again, our basic procedure contains no variable declarations, but you can declare any PL/SQL variable in a procedure, including characters, numbers, dates and Booleans. Extracting data: You can use the SQL statement SELECT to extract data from an Oracle database and populate variables in your procedure. Extracting lots of data: You can process batches of records in a procedure using cursors. Transaction control: Manipulate data from within a procedure. Issue SQL statements INSERT, UPDATE and DELETE, and decide if you want to commit the changes, or if they should be rolled back. cheers, Nareshallu