2018年法网总奖金额上涨近9%，单打冠军奖金再增10万欧元

The basic syntax for creating Oracle stored procedures is to use CREATE OR REPLACE PROCEDURE, followed by the process name, optional variable declaration, BEGIN logic code and END; end; 2. For example, the process to create the output "Hello, World!" must use DBMS_OUTPUT.PUT_LINE and execute it in /exec; 3. When executing stored procedures, you can use BEGIN hello_world; END; or abbreviated EXEC hello_world; but you need to run SET SERVEROUTPUT ON first to see the output; 4. You can implement the input function by adding IN parameters (such as p_name IN VARCHAR2), making the process more practical; 5. If you need to return data, you can use OUT parameters (such as p_greeting OUT VARCHAR2) to declare the variable reception value when called and output the result in the block; 6. The stored procedure does not directly return the value, and is suitable for performing operations rather than calculating the results. Advanced applications can combine table operations, cursors and exception handling.

Writing a simple stored procedure in Oracle is straightforward once you understand the basic structure. A stored procedure is a named PL/SQL block that performs a specific task and can be stored in the database for repeated use.

Here's how to create a basic stored procedure in Oracle:

? Basic Syntax of a Stored Procedure

 CREATE OR REPLACE PROCEDURE procedure_name 
IS
    -- Declare variables (optional)
BEGIN
    -- Your PL/SQL code
    NULL; -- Placeholder if no logic yet
END;
/

You run this in an Oracle environment like SQL*Plus, SQL Developer, or any tool that supports PL/SQL.

?? Example: Simple Procedure That Prints a Message

Let's create a procedure that outputs "Hello, World!" using DBMS_OUTPUT.PUT_LINE :

 CREATE OR REPLACE PROCEDURE hello_world
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello, World!');
END;
/

? The / at the end tells SQL*Plus or similar tools to execute the PL/SQL block.

?? How to Execute the Procedure

After creating it, you can run the procedure using:

 BEGIN
    hello_world;
END;
/

Or, more simply (in SQL Developer or SQL*Plus):

 EXEC hello_world;

? Make sure SET SERVEROUTPUT ON; is enabled to see the output:

 SET SERVEROUTPUT ON

? Adding Parameters (Input Example)

You can make the procedure more useful by adding parameters. Here's one that takes a name and prints a personalized greeting:

 CREATE OR REPLACE PROCEDURE greet_person (p_name IN VARCHAR2)
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
END;
/

Call it like this:

 EXEC greet_person('Alice');
-- Output: Hello, Alice!

? Key Points to Remember

• Use CREATE OR REPLACE to create or update the procedure.
• IN parameters are for input (default direction).
• Always end the procedure with END; and include / in tools like SQL*Plus.
• Use DBMS_OUTPUT.PUT_LINE for debugging or output (requires SET SERVEROUTPUT ON ).
• Procedures don't return values directly (use functions for that), but can use OUT parameters to return data.

? Optional: Using OUT Parameters

Example with an OUT parameter:

 CREATE OR REPLACE PROCEDURE get_message (p_name IN VARCHAR2, p_greeting OUT VARCHAR2)
IS
BEGIN
    p_greeting := 'Hello, ' || p_name || '!';
END;
/

To call it:

 DECLARE
    v_msg VARCHAR2(100);
BEGIN
    get_message('Bob', v_msg);
    DBMS_OUTPUT.PUT_LINE(v_msg);
END;
/

That's it — you've created a simple stored procedure in Oracle. Start with basic ones like these, then build up to working with tables, cursors, and error handling.

The above is the detailed content of How to write a simple stored procedure in Oracle?. For more information, please follow other related articles on the PHP Chinese website!