What are the out parameters of stored procedures in oracle

In Oracle, the out parameter in the stored procedure is the parameter of the output mode. It is used to output the value. The incoming value will be ignored. It can be modified inside the subroutine. After the subroutine is executed, The final value of the out mode parameter will be assigned to the corresponding actual parameter variable during the call. The out mode parameter must be called through the variable.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

What are the out parameters of the stored procedure in Oracle

1. What is the stored procedure

Oracle stored procedure can be said to be a record set, which is composed of some PL/ A code block composed of SQL statements. These PL/SQL statement codes implement some functions like a method (add, delete, modify and query a single table or multiple tables), and then give this code block a name. When this function is used Just call him.

Benefits of stored procedures:

When the database performs actions, it is compiled first and then executed. However, a stored procedure is a compiled code block, so its execution efficiency is higher than that of PL/SQL statements.

A stored procedure can replace a large number of PL/SQL statements when interacting in programs and networks, so it can also reduce network communication volume and increase communication speed.

Through stored procedures, users without permissions can indirectly access the database under control, thereby ensuring data security.

Example of stored procedure:

--给指定的员工涨100块钱的工资，并且打印涨前和涨后的薪水
create or replace procedure raiseSalary(eno in number)
as
  --定义变量，保存涨前的薪水
  psal emp.sal%type;
begin
  --得到涨前的薪水
  select sal into psal from emp where empno=eno;
  --涨100
  update emp set sal=sal+100 where empno=eno;
  
  --要不要commit？
  --一般，不在存储过程或者存储函数中提交和回滚
  
  dbms_output.put_line('涨前:'||psal||'   涨后:'||(psal+100));
end;
/

2. What is the out parameter?

The parameters of the output mode are used to output values, and the incoming values ​​will be ignored. It can be modified inside the subroutine. Output: After the subroutine is executed, the final value of the out mode parameter will be assigned to the corresponding when called. Note: The out mode parameter must be called through a variable.

out parameter example:

--查询某个员工的姓名 月薪和职位
/*
1. 查询某个员工的所有信息 ---> out参数太多
2. 查询某个部门中所有员工的所有信息 --> 返回集合
*/
create or replace procedure queryempinfo(eno in number,
                                         pename out varchar2,
                                         psal   out number,
                                         pjob   out varchar2)
as
begin
  select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;
end;
/

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of What are the out parameters of stored procedures in oracle. For more information, please follow other related articles on the PHP Chinese website!