Introduction to Oracle stored procedure development

The following articles describe how to develop an Oracle stored procedure, including how to run a stored procedure and how to run the relevant code in a stored procedure, the following is a detailed introduction of the article. I hope you will gain some benefits after browsing the following content.

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 Oracle stored procedure, how can we modify it?

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> 
  
  
CREATE OR REPLACE PROCEDURE skeleton  
  
  
IS  
  
  
BEGIN  
  
  
DBMS_OUTPUT.PUT_LINE('Hello World!');  
  
  
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 displays that the Oracle 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!

SQL> EXECUTE skeleton;

 
 

  
  
SQL> EXECUTE skeleton; 
 
 

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;

Discard a stored procedure

If you do not need an Oracle 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 Oracle commercial Oracle stored procedures. Finally, the stored procedure may be directly called by a program or trigger.