What are the functions of oracle stored procedures

The functions of oracle stored procedures are: 1. Simplify complex operations and reduce overall development costs; 2. Increase data independence; 3. Can effectively reduce the probability of errors and improve security; 4. Improve performance .

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

Oracle stored procedures are often used in the actual database development process. As a necessary skill for a database developer, it plays an irreplaceable role in SQL statements. The so-called stored procedure is a program module that is stored in the database to perform a certain business function. It is a series of code blocks composed of one or more PL/SQL code blocks or SQL statements.

The role of stored procedures (advantages):

• Simplify complex operations and reduce overall development costs.

  The stored procedure encapsulates the actual executed business logic PL/SQL block and multiple SQL statements into the stored procedure. Other developers only need to call the written procedure to obtain the desired results without re-understanding. business. Extract the business and have it written by specialized people.
  

• Increase data independence.

  Its function is similar to that of a view. If the basic data of the table changes, we only need to modify the code in the process without modifying the calling program. This eliminates the need for user programs to write code directly facing basic data. This makes the code more cohesive and less coupled.

• Improve security.

  Using stored procedures effectively reduces the chance of errors. If you do not use stored procedures to achieve a certain operation, you may need to execute multiple separate SQL statements, and too many execution steps may cause a higher probability of errors.

• Improve performance.

  In the actual development process, the development of a business module function may require the use of multiple SQL statements and multiple PL/SQL program blocks to solve the problem. By writing it into a procedure, Oracle only needs to compile it once and it can be called at any time later. If you do not use procedures and directly write many SQL statements into the program, it will require multiple compilations and multiple connections to the database, which greatly reduces performance.

Syntax for creating Oracle stored procedures:

create [or replace] procedure 过程名
( p1 in|out datatype,
  p2 in|out datatype,
  ...
  pn in|out datatype
    
) is 
    
    ....--声明部分
    begin
    
    ....--过程体
    end;

Syntax analysis:

1. The procedure keyword is used to create stored procedures Order.

2. create [or replace]: If the stored procedure already exists, overwrite and replace the original procedure.

3. in|out: The stored procedure has two parameter options: input parameters and output parameters. in represents the input parameters, and out represents the output parameters. When using the process, the input parameters must have The corresponding variable is passed in, and the corresponding variable must be received by the outgoing parameter.

4. datatype indicates the data type corresponding to the incoming and outgoing parameter variables.

5. is is followed by the declared variables used in the process.

6. What is written in the middle of begin...end is the specific operation of the stored procedure.

Example 1. Create a stored procedure to calculate the ranking of students' grades in a certain course in the class. Use the stored procedure to calculate and return the corresponding ranking. The code is as follows:

create or replace procedure sp_score_pm(
p_in_stuid in varchar2,--学号
p_in_courseid in  varchar2, --课程ID
p_out_pm out number--排名
)
is
ls_score number:=0;
ls_pm number:=0;
begin
  --获取该学生的成绩
  select t.score into ls_score from score t
   where t.stuid = p_in_stuid
     and t.courseid = p_in_courseid;
  --获取成绩比该学生高的人数
  select count(1) into ls_pm from score t
   where t.courseid = p_in_courseid
   and  t.score>ls_score;
   --得到该学生的成绩排名
   p_out_pm:=ls_pm+1;
exception
  when no_data_found  then
     dbms_output.put_line('该学生的课程：'||p_in_courseid|| '的成绩在成绩表中找不到');
end;

Through the above Code, we can execute the compilation directly in the SQL window. After the compilation is successful, we can call the stored procedure to obtain the corresponding course grade ranking of the students. The stored procedure requires incoming and outgoing parameter assignment, so we can test it through PL/SQL statement blocks. The code is as follows:

declare
ls_pm number;--排名
begin
  --SC201801001
  sp_score_pm('SC201801001','R20180101',ls_pm);
  dbms_output.put_line('学号：SC201801001,课程号：R20180101 的成绩排名是：'||ls_pm);
  sp_score_pm('SC201801001','R20180102',ls_pm);
  dbms_output.put_line('学号：SC201801001,课程号：R20180102 的成绩排名是：'||ls_pm);
  --SC201801002
  sp_score_pm('SC201801002','R20180101',ls_pm);
  dbms_output.put_line('学号：SC201801002,课程号：R20180101 的成绩排名是：'||ls_pm);
  sp_score_pm('SC201801002','R20180102',ls_pm);
  dbms_output.put_line('学号：SC201801002,课程号：R20180102 的成绩排名是：'||ls_pm);
  
end;

The result is as follows:

Recommended tutorial: "Oracle Tutorial"

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