oracle stored procedure return value

Oracle stored procedures are precompiled, reusable blocks of code because they allow developers to create, test, and execute code in the database without requiring a connection to an external application. One of the main advantages of Oracle stored procedures is improved database performance and security, but they require special handling when they need to return values.

The return value characteristics of stored procedures are based on actual business needs, because some stored procedures may only be used to trigger some operations without returning any values, such as some notification operations in update operations. . However, in other cases, the stored procedure may need to return a simple value, such as a number, string, or Boolean value, or under other conditions a collection or data table.

First, let's look at an example of how a stored procedure returns a single value:

CREATE OR REPLACE PROCEDURE get_employee_count(p_department varchar2, p_employee_count out number) 
IS 
BEGIN 
    SELECT COUNT(*) INTO p_employee_count FROM employees WHERE department = p_department; 
END;

In this example, the stored procedure receives a department name parameter and returns the total number of employees in that department. In the procedure definition, we use an OUT parameter to return the result. In the main part, we execute a SELECT query and store the results in the p_employee_count parameter.

To call this stored procedure and retrieve the return value, we can use the following code:

DECLARE 
    v_employee_count NUMBER; 
BEGIN 
    get_employee_count('IT', v_employee_count); 
    DBMS_OUTPUT.PUT_LINE('Total Employees in IT department:'||v_employee_count); 
END; 

In this example, we define a variable v_employee_count using the DECLARE block and pass it to the stored procedure . We then call the stored procedure in the body section and display the results in the output.

Regarding using stored procedures to return result sets, we can also use separate table/cursor technology to handle it.

CREATE OR REPLACE TYPE Employee AS OBJECT 
( 
    employee_id NUMBER(6), 
    first_name VARCHAR2(20), 
    salary NUMBER(8,2) 
); 

CREATE OR REPLACE TYPE EmployeeList AS TABLE OF Employee; 

CREATE OR REPLACE PROCEDURE get_employees(p_department VARCHAR2, p_employee_list OUT EmployeeList) 
IS 
BEGIN 
    SELECT Employee(EMPLOYEE_ID, FIRST_NAME, SALARY) BULK COLLECT INTO p_employee_list FROM employees WHERE department = p_department; 
END; 

DECLARE 
    v_employee_list EmployeeList; 
BEGIN 
    get_employees('IT', v_employee_list); 
    FOR i IN v_employee_list.first .. v_employee_list.LAST LOOP 
        DBMS_OUTPUT.PUT_LINE(v_employee_list(i).employee_id||' - '||v_employee_list(i).first_name); 
    END LOOP; 
END; 

In this example, we define an object type named Employee, which contains employee_id, first_name and salary attributes. We also define an EmployeeList type, which is a collection of Employee objects. Finally, we created a stored procedure get_employees that receives the department name and returns a list of employees for that department.

In the body of the stored procedure, we execute a SELECT query and use the BULK COLLECT INTO statement to convert the result set into an EmployeeList object.

To call this stored procedure, and retrieve the result set, we can use the following code:

DECLARE 
    v_employee_list EmployeeList; 
BEGIN 
    get_employees('IT', v_employee_list); 
    FOR i IN v_employee_list.FIRST .. v_employee_list.LAST LOOP 
        DBMS_OUTPUT.PUT_LINE(v_employee_list(i).employee_id||' - '||v_employee_list(i).first_name||' - '||v_employee_list(i).salary); 
    END LOOP; 
END; 

In this example, we define a variable v_employee_list of type EmployeeList and pass it to Stored procedure get_employees. We then use a FOR loop to iterate through the collection and display the properties of each Employee object in the output.

In this article, we looked at the different ways of returning values ​​in Oracle stored procedures, including methods of returning individual values ​​and result sets. No matter what your application needs are, stored procedures are a powerful tool that can improve database performance and security while providing reusable blocks of code.

The above is the detailed content of oracle stored procedure return value. For more information, please follow other related articles on the PHP Chinese website!