How Oracle calls stored procedures

Oracle is an efficient relational database management system. In enterprise-level application development, stored procedures are a very important part. In Oracle, a stored procedure is a program unit that can be run on the database server. It can be written through PL/SQL, supports a large number of logical processing and transaction control, and can combine multiple SQL statements into a set.

In actual development and operation and maintenance, how to call stored procedures in Oracle is crucial. This article will introduce in detail how Oracle calls stored procedures.

1. Create stored procedures

There are many ways to create stored procedures in Oracle. The two most common ways are to use Oracle SQL Developer tools or use SQL*Plus commands. line tools.

The steps to create a stored procedure using the Oracle SQL Developer tool are as follows:

1) Open SQL Developer and connect to the Oracle database server.

2) Enter the SQL statement of the stored procedure in the SQL Worksheet window. For example:

CREATE OR REPLACE PROCEDURE show_emp_info
IS
BEGIN
SELECT * FROM emp;
END;

3) Press the Ctrl Enter key to execute the SQL statement to create a stored procedure named show_emp_info.

If you use the SQL*Plus command line tool to create a stored procedure, you can use the following command:

CREATE OR REPLACE PROCEDURE show_emp_info
IS
BEGIN
SELECT * FROM emp ;
END;
/

Note that when using SQL*Plus to create a stored procedure, you need to add a "/" symbol at the end of the statement to indicate the end of the statement.

2. Calling stored procedures

There are many ways to call stored procedures in Oracle. The two most common methods are to use Oracle SQL Developer tools or to use PL/SQL blocks. .

The method of calling a stored procedure using the Oracle SQL Developer tool is as follows:

1) Select the required database connection and open the SQL Worksheet window.

2) Enter the following SQL statement in the SQL Worksheet window:

BEGIN
show_emp_info;
END;

3) Press the Ctrl Enter key to execute the SQL statements can call stored procedures.

If you use a PL/SQL block to call a stored procedure, you can use the following syntax:

BEGIN
show_emp_info;
END;
/

Similarly You need to add a "/" symbol at the end of the statement to indicate the end of the statement.

It should be noted that when the stored procedure needs to pass in parameters, IN and OUT parameters can be used instead of the formal parameters of the function. The IN parameters represent the parameters passed into the stored procedure, while the OUT parameters represent the results returned by the stored procedure.

3. Parameter passing

In Oracle, stored procedures pass parameters through IN and OUT parameters. The IN parameter is used to receive external incoming data, and the OUT parameter is used to return the result.

The syntax for using IN parameters in stored procedures is as follows:

CREATE OR REPLACE PROCEDURE show_emp_info(
deptno IN NUMBER
)
IS
BEGIN
SELECT * FROM emp WHERE deptno = deptno;
END;

The syntax for using OUT parameters in a stored procedure is as follows:

CREATE OR REPLACE PROCEDURE show_emp_info(
deptno IN NUMBER,
emp_count OUT NUMBER
)
IS
BEGIN
SELECT COUNT(*) INTO emp_count FROM emp WHERE deptno = deptno;
END;

It should be noted that , when using the OUT parameter, the stored procedure needs to return the value of the parameter at the end, as follows:

CREATE OR REPLACE PROCEDURE show_emp_info(
deptno IN NUMBER,
emp_count OUT NUMBER
)
IS
BEGIN
SELECT COUNT(*) INTO emp_count FROM emp WHERE deptno = deptno;
RETURN emp_count;
END;

4. Conclusion

Calling stored procedures in Oracle is a very important and basic operation. This article introduces in detail the method of creating and calling stored procedures in Oracle, and explains in detail the passing of parameters in stored procedures. Hopefully this article can provide developers with practical guidance.

The above is the detailed content of How Oracle calls stored procedures. For more information, please follow other related articles on the PHP Chinese website!