Shulou(Shulou.com)05/31 Report--

This article focuses on "analyzing out parameters in Oracle stored procedures". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "analyzing out parameters in Oracle stored procedures".

What is a stored procedure

Oracle stored procedure can be said to be a recordset, it is a code block composed of some PL/SQL statements, these PL/SQL statements code like a method to achieve some functions (single-table or multi-table changes and queries), and then give this code block a name, when using this function to call him on the line.

Benefits of stored procedures:

Because when the database executes the action, it is compiled first and then executed. However, a stored procedure is a compiled block of code, so it executes more efficiently than PL/SQL statements.

A stored procedure can replace a large number of PL/SQL statements when programs interact in the network, so it can also reduce the traffic of the network and improve the communication rate.

Through the stored procedure, the unauthorized users can access the database indirectly under control, so as to ensure the security of the data.

Examples of stored procedures:

-- increase the salary of the designated employee by 100RMB, and print the salary create or replace procedure raiseSalary (eno in number) as before and after the increase-- define the variable, save the salary psal emp.sal%type;begin before the increase-- get the salary before the increase select sal into psal from emp where empno=eno;-- increase by 100 update emp set sal=sal+100 where empno=eno;-- do you want commit? Generally, dbms_output.put_line is not submitted and rolled back in stored procedures or functions ('before rising:' | psal | | 'after rising:' | | (psal+100)); end;/ 2. What is the out parameter?

The parameters of the output mode, used to output values, ignore the values passed in. It can be modified within the subroutine. Output: after the subroutine is executed, the final value of the out mode parameter will be assigned to the corresponding value at the time of the call. Note: out mode parameters must be called through variables.

Example of out parameters:

-- query an employee's name, monthly salary and position / * 1. Query all the information about an employee-> too many out parameters. Query all the information of all employees in a department-- > return the collection * / create or replace procedure queryempinfo (eno in number, pename out varchar2, psal out number, pjob out varchar2) asbegin select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;end At this point, I believe you have a deeper understanding of "analyzing out parameters in Oracle stored procedures". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.