Oracle stored procedures rookie level knowledge __php

Oracle Stored Procedures rookie level knowledge

http://945xiexie.javaeye.com/blog/595631

I. Overview
The key points of Oracle storage process development are:
• Use the Notepad text editor to write a stored procedure in the Oracle Pl/sql programming language;
• Create a stored procedure in the Oracle database;
• Run stored procedures in the Oracle database using the Sql*plus tool;
• Modify stored procedures in the Oracle database;
• Debug stored procedures by compiling errors;
• Delete stored procedures;
Two. Environment configuration
Includes the following content:
• A text editor Notepad;
Oracle Sql*plus Tool to submit Oracle SQL and PL/SQL statements to Oracle database.
Oracle 10g Express Database, it is free to use the version;
The skills you need:
SQL basic knowledge, including inserting, modifying, deleting, etc.
• Basic techniques for using Oracle's Sql*plus tools;
• Basic skills in using Oracle's PL/SQL programming language;
Three. Write a stored procedure
Stored procedures are written in Oracle's Pl/sql programming language, let's write a stored procedure that doesn't do anything, and we can compile and run it without having to worry about any damage to the database.
In Notepad, write down:
CREATE OR REPLACE PROCEDURE skeleton
Is
BEGIN
NULL;
End;
Save the file as a skeleton.sql.
Let's walk through the stored procedure in a row:
1 CREATE OR REPLACE PROCEDURE skeleton
2 is
3 BEGIN
4 NULL;
5 end;
Line 1:
The Create OR REPLACE PROCEDURE is an SQL statement that notifies the Oracle database to create a stored procedure called skeleton, overwriting it if it exists;
Line 2:
The IS keyword indicates that the following will follow a pl/sql body.
Line 3:
The BEGIN keyword indicates the beginning of the Pl/sql body.
Line 4:
A NULL pl/sql statement indicates that nothing is done, and this cannot be deleted, because at least one sentence is needed in the pl/sql body;
Line 5:
End keyword Indicates the ending of the pl/sql body
Four. Create a stored procedure
SQL statement Create or REPLACE procedure creates, compiles, and saves a stored procedure in an Oracle database.
Open Sql*plus from window and log in to your database from Sql*plus; open skeleton.sql file.
At the sql> command prompt, enter the following command:
Sql> @skeleton
sql>/
Sql*plus loads the contents of the Skeleton.sql file into the Sql*plus buffer and executes the sql*plus statement; Sql*plus will notify you that the stored procedure has been successfully created.
Now that your stored procedure is created, compiled and saved in your Oracle database, we can run it.
Five. Run a stored procedure
Run your stored procedure from the Sql*plus command prompt using the Execute command, as follows:
sql> EXECUTE skeleton;
Sql*plus output information to ensure that the stored procedure was executed successfully: Pl/sql procedure successfully completed.
You can also run your stored procedure in a nameless pl/sql block, which, at the Sql*plus command prompt, looks like this:
Sql> BEGIN
2 skeleton;
3 END;
4/
Now that we've run our stored procedures, how do we modify it?
Six. Modify a stored procedure
Let's write an output string "Hello world!" Stored procedure, open your Skeleton.sql file with Notepad,. With Dbms_output. Put_Line The procedure call to replace the NULL statement as follows:
CREATE OR REPLACE PROCEDURE skeleton
Is
BEGIN
Dbms_output. Put_Line (' Hello world! ');
End;
Save to file Skeleton.sql.
From the Sql*plus command line, open the file skeleton.sql.
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 successfully creates and outputs a message: Procedure created.
Sql>
Run your stored procedure with the Execute command:
sql> EXECUTE skeleton;
Sql*plus shows that the stored procedure is running successfully: Pl/sql procedure successfully completed.
We want the output string "Hello world!" Did not come out, in the display of a dbms_output. Put_Line you need to run a set command before the 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.
Seven. Debug a stored procedure
When debugging a stored procedure, follow the same steps, modify the SQL file, create stored procedures, execute stored procedures, according to the compiler feedback error information to modify, this step is very tedious, need to rely on experience.
During the development and commissioning of the actual commercial storage process, due to a lot of tables, types, cursors, loops, conditions and other complex logic, and the flexible use of pl/sql statements, compile will produce a lot of error messages, programmers in accordance with these error information positioning, to modify, and then compile the correct structure finally;
Eight. Discard a stored procedure
If you do not need a stored procedure in the database you can delete it, the SQL statement drop PROCEDURE completes the deletion of a stored procedure from the database, and the drop PROCEDURE is categorized as a data definition language (DDL) class operation in SQL, and the other examples are create, ALTER, RENAME and truncate.
At the Sql*plus command prompt, use the drop PROCEDURE SQL statement to delete your stored procedure called skeleton:
Sql> DROP PROCEDURE skeleton;
Sql*plus assures us the procedure has been removed:
Procedure dropped.
Summarize
This article discusses in detail how to use Oracle tools to develop Oracle commercial stored procedures. The last use of a stored procedure may be a direct call to a program, or it may be invoked by a trigger.