How to Develop an oracle Stored Procedure

In my previous banking project, I received the task of writing an oracle stored procedure. I amProgramEmployee, his head only has some experience in calling stored procedures using the callable interface. I have read some documents for some time, it is not easy to write the Oracle stored procedure through practice. debugging and verification are troublesome even after the process is launched. Simply put, an oracle stored procedure is a program stored in an Oracle database.

I. Overview

The key points of Oracle stored procedure development are:

? Use the notepad text editor and Oracle PL/SQLProgramming LanguageWrite a stored procedure;

? Create a stored procedure in the Oracle database;

? Use SQL * Plus to run stored procedures in Oracle databases;

? Modify the stored procedure in the Oracle database;

? Debug the stored procedure through compilation errors;

? Delete a stored procedure;

II. Environment Configuration

Includes the following:

? A text editor notepad;

? The Oracle SQL * Plus tool submits Oracle SQL and PL/SQL statements to Oracle database.

? Oracle 10g express database, which is a free version;

Required skills:

? Basic SQL knowledge, including insert, modify, and delete

? Basic skills for using Oracle's SQL * Plus tool;

? Basic skills for using Oracle's PL/SQL programming language;

3. Write a stored procedure

The stored procedure is written in Oracle's PL/SQL programming language. Let's write a stored procedure that does nothing, we can compile and run it without worrying about any damage to the database.

In notepad, write down:

Create or replace procedure skeleton

Is

Begin

NULL;

End;

Save the file as skeleton. SQL.

Let's traverse the Stored Procedure row by row:

1 create or replace procedure skeleton

2 is

3 begin

4 null;

5 end;

Row 1:

Create or replace procedure is an SQL statement that notifies the Oracle database to create a stored procedure called skeleton, and overwrites it if it exists;

Row 2:

The is keyword indicates that a PL/SQL body will be followed.

Row 3:

The begin keyword indicates the start of the PL/SQL body.

Row 4:

The null PL/SQL statement indicates that nothing is done. This statement cannot be deleted because at least one sentence is required in the PL/SQL body;

Row 5:

The end keyword indicates the end of the PL/SQL body.

4. Create a stored procedure

The SQL statement create or replace procedure creates, compiles, and saves a stored procedure in the Oracle database.

Open SQL * Plus from window and log on to your database from SQL * Plus; open the skeleton. SQL file.

Enter the following command at the SQL> Command Prompt:

SQL> @ skeleton

SQL>/

SQL * Plus loads the content of the skeleton. SQL file to the SQL * Plus buffer and executes the SQL * Plus statement. SQL * Plus notifies you That the stored procedure has been created successfully.

Now your stored procedure is created, compiled, and saved to your Oracle database. We can run it.

5. Run a stored procedure

Run the Execute Command from the SQL * Plus command line prompt:

SQL> execute skeleton;

SQL * Plus outputs the following information to ensure that the stored procedure is successfully executed: PL/SQL procedure successfully completed.

You can also run your stored procedure in an unnamed PL/SQL block. At the SQL * Plus command prompt, it looks like:

SQL> begin

2 skeleton;

3 end;

4/

Now that we have run our stored procedure, how can we modify it?

6. modify a stored procedure

Let's write an output string "Hello World !" Use NotePad to open your skeleton. SQL file. Use dbms_output.put_line to replace the null statement, as shown below:

Create or replace procedure skeleton

Is

Begin

Dbms_output.put_line ('Hello world! ');

End;

Save to the file skeleton. SQL.

Open the file skeleton. SQL from the SQL * Plus command line.

SQL> @ skeleton

SQL>

1 create or replace procedure skeleton

2 is

3 begin

4 dbms_output.put_line ('Hello world! ');

5 * end;

SQL>/

SQL * Plus notifies you That the stored procedure is successfully created and the prompt message is displayed: procedure created.

SQL>

Run your stored procedure with the execute command:

SQL> execute skeleton;

SQL * Plus shows that the stored procedure runs successfully: PL/SQL procedure successfully completed.

The output string "Hello world! "Failed. Run a set command before displaying a dbms_output.put_line result. At the SQL * Plus command line prompt, type:

SQL> set serveroutput on

Execute your stored procedure again:

SQL> execute skeleton;

Now the result is output: Hello world!

PL/SQL procedure successfully completed.

7. debug a stored procedure

When debugging a stored procedure, follow the same steps to modify the SQL file, create a stored procedure, execute the stored procedure, and modify the error information according to the compiler feedback. This step is very tedious, it depends on experience.

In the development and debugging process of the actual commercial storage process, complex logics such as tables, types, cursors, loops, and conditions are involved, and flexible use of PL/SQL statements, during compilation, a lot of error messages will be generated. The programmer can locate and correct the errors Based on the errors, and then compile the statements to get the correct structure;

8. discard a stored procedure

If you do not need a stored procedure in the database, you can delete it. The SQL statement drop procedure deletes a stored procedure from the database, drop procedure is classified as a Data Definition Language (DDL) operation in SQL. Other examples include create, alter, rename, and truncate ..

At the SQL * Plus command prompt, use the drop Procedure SQL statement to delete your Stored Procedure named SKELETON:

SQL> drop procedure skeleton;

SQL * Plus assures us the procedure has been removed:

Procedure dropped.

Summary

This article discusses in detail how to use oracle tools to develop the Oracle commercial storage process. Finally, the stored procedure may be directly called by a program or trigger.