Oracle stored procedure development instance

In a bank project of the author, I received the task of compiling an Oracle stored procedure. I am a programmer and have only some experience in calling a stored procedure using the CALLABLE interface.

In a bank project of the author, I received the task of compiling an Oracle stored procedure. I am a programmer and have only some experience in calling a stored procedure using the CALLABLE interface.

In a bank project of the author, I have received the task of compiling an Oracle stored procedure. I am a programmer and have only some experience in calling a stored procedure using the CALLABLE interface. I am not sure how to do this for a moment, I read some documents and found that it is not easy to write the ORACLE stored procedure through practice. Even after going on, debugging and verification are very troublesome. 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 to write a stored procedure in OraclePL/SQL programming language;

◆ Create a stored procedure in the Oracle database;

◆ Use SQL * Plus to run the stored procedure in the Oracle database;

◆ Modify the stored procedure in the Oracle database;

◆ Debug the stored procedure through compilation errors;

◆ Delete the stored procedure;

II. Environment Configuration

Includes the following:

◆ A text editor Notepad;

◆ OracleSQL * Plus tool, submit OracleSQL and PL/SQL statements to Oracledatabase.

◆ Oracle10gexpress database, which is a free version;

Required skills:

◆ Basic SQL knowledge, including insertion, modification, and deletion

◆ Basic skills for using Oracle's sSQL * Plus tool;

◆ Basic skills for using the Oracle 'spl/SQL programming language;

3. Write a stored procedure

The stored procedure is written in the Oracle 'spl/SQL programming language. This allows us to 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:

CREATEORREPLACEPROCEDUREskeleton
IS

BEGIN

NULL;

END;

Save the file as skeleton. SQL.
Let's traverse the Stored Procedure row by row:

1 CREATEORREPLACEPROCEDUREskeleton
2IS

3 BEGIN

4 NULL;

5END;

Row 1:

CREATEORREPLACEPROCEDURE is an SQL statement that notifies the Oracle database to create a stored procedure called skeleton. If it exists, it will be overwritten;

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 NULLPL/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 CREATEORREPLACEPROCEDURE 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> EXECUTEskeleton;

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

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;

3END;

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 "HelloWorld !" Use Notepad to open your skeleton. SQL file. Use DBMS_OUTPUT.PUT_LINE to replace the NULL statement, as shown below:

CREATEORREPLACEPROCEDUREskeleton
IS

BEGIN

DBMS_OUTPUT.PUT_LINE ('helloworld! ');

END;

Save to the file skeleton. SQL.

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

SQL> @ skeleton
SQL>

1 CREATEORREPLACEPROCEDUREskeleton

2IS

3 BEGIN

4DBMS_OUTPUT.PUT_LINE ('helloworld! ');

5 * END;

SQL>/

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

SQL>

Run your stored procedure with the EXECUTE command:

SQL> EXECUTEskeleton;

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

The output string "HelloWorld! "Failed. Run a SET command before displaying a DBMS_OUTPUT.PUT_LINE result. At the SQL * Plus command line prompt, type:

SQL> SETSERVEROUTPUTON

Execute your stored procedure again:

SQL> EXECUTEskeleton;

Now the result is output: HelloWorld!

PL/SQLproceduresuccessfullycompleted.

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 DROPPROCEDURE deletes a stored procedure from the database, DROPPROCEDURE 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 DROPPROCEDURESQL statement to delete your Stored Procedure named skeleton:

SQL> DROPPROCEDUREskeleton;
SQL * Plusassuresustheprocedurehasbeenremoved:

Proceduredropped.

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.