Oracle Stored Procedure syntax and compilation process explanation, oracle Stored Procedure

Oracle Stored Procedure syntax and compilation process explanation, oracle Stored Procedure
Syntax

CREATE [ORReplace] PROCEDURE [schema.] procedure_name [(argument [{IN | OUT | INOUT}] datatype ,... argument [{IN | OUT | INOUT}] datatype)] {IS | AS} [descriptionpart description part] in sqlstatement statement sequence [EXCEPTION Handling] END [procedureName process name];

Syntax analysis

OR REPLACE

Is an optional keyword. We recommend that you use this keyword. If the process already exists, this keyword will re-create the process, so that you do not have to delete or recreate the process.

Either the keyword IS or

They are no different. IS followed by a complete PL/SQL block, which can define local variables, but cannot start with DECLARE. Local variables are stored in the process.

Format parameters can have three modes: IN, OUT, And INOUT. If the mode is not specified for the form parameter, the default mode is IN.

IN indicates input parameters.

OUT indicates the output parameter.

HelloWorld!

Create the first Stored ProcedureHelloWorld

SQL>create or replace procedure helloworldasbegindbms_output.put_line('helloworld');end;

/

Note: you must press enter after the input is complete in the stored procedure and enter "/" in the next line to create the bucket.

When Procedure created is displayed, the stored Procedure is successfully created.

Call HelloWorld!

Run

　　SQL>set serveroutput on;　　SQL>exec[ute] helloworld;

The following result is displayed:

　　helloworld　　PL/SQL procedure successfully completed

Compilation process

Note:

No matter whether the creation is successful or not, the creation process/FUNCTION Command create procedure or create function automatically saves its source code to the database, and the compiled code can only be saved to the database after compilation.

Only the stored procedures and functions in which the compiled code is stored in the database can be called.

That is to say, if the statement for creating a stored procedure is incorrect, the source code of the stored procedure will also be put into the database, but it will be displayed as an error.

To view the error, use the USER_ERRORS data dictionary or the show errors command to query the error in the current system.