What does the basic structure of Oracle stored procedures include?

The structure of Oracle stored procedure contains three parts: 1. Process declaration; 2. Execution process part; 3. Stored procedure exception. This part can be omitted if you want to enhance the fault tolerance of the script and the convenience of debugging. Just write exception handling.

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

What is a stored procedure?

Stored procedure: Baidu Encyclopedia explains this, stored procedure (Stored Procedure) is used in large database systems , a set of SQL statements to complete specific functions is stored in the database. After the first compilation, it does not need to be compiled again when called again. The user specifies the name of the stored procedure and gives the parameters (if the stored procedure has parameters) ) to call the stored procedure.

To put it simply, it is a SQL statement that does one thing specifically.

It can be called by the database itself or by a java program.

The stored procedure in the oracle database is a procedure.

Stored procedure structure

(1) Basic structure

Oracle stored procedure contains three parts : Process declaration, execution process part, stored procedure exception (can be written or not, to enhance the fault tolerance of the script and the convenience of debugging, write exception handling)

(2) No-parameter storage Procedure

/**
name_procedure就是自己自定义的存储过程名
*/
create or replace procedure name_procedure AS/IS
	变量名1  数据类型;
	变量名2  数据类型;
BEGIN
	--要处理的业务逻辑
	EXCEPTION    --存储过程异常
END name_procedure;

(3). Parameter stored procedure

Ordinary parameter stored procedure

/*
age 类型为number 初始化为20;
*/
CREATE OR REPLACE PROCEDURE name_procedure (param1 TYPE)
AS/IS
name varchar(20);
age number :=20;
BEGIN
  --业务处理.....
END ;

Stored procedure with parameters and assignment

CREATE OR REPLACE PROCEDURE 存储过程名称(
       s_no in varchar,
       s_name out varchar,
       s_age number) AS
total NUMBER := 0;
BEGIN
  SELECT COUNT(1) INTO total FROM student s WHERE s.age=s_age;
  dbms_output.put_line('符合该年龄的学生有'||total||'人');
  EXCEPTION
    WHEN too_many_rows THEN 
    DBMS_OUTPUT.PUT_LINE('返回值多于1行'); 
END

The parameter IN represents the input parameter and is the default mode of the parameter.

• OUT represents the return value parameter, and the type can use any legal type in Oracle.

• The parameters defined in the OUT mode can only be assigned within the process body, which means that the parameter can pass a certain value back to the process that called it

• IN OUT means that the parameter can pass a value to the process, or a certain value can be passed out

Line 7: Query statement, using the parameter s_age as a filter condition, INTO key word, assign the found result to the total variable.

Line 8: Output query results, "||" is used to connect strings in the database

Lines 9-11: Do exception handling

Stored procedure syntax

(1)Operator

(2) SELECT INTO STATEMENT statement

• Assign the query results to one variable or multiple variables

Requirement: The result is 100 points The student’s name, age, place of origin

CREATE OR REPLACE PROCEDURE DEMO_CDD1 IS
s_name VARCHAR2;   --学生名称
s_age NUMBER;      --学生年龄
s_address VARCHAR2; --学生籍贯
BEGIN
  --给单个变量赋值
  SELECT student_address INTO s_address
  FROM student where student_grade=100;
   --给多个变量赋值
  SELECT student_name,student_age INTO s_name,s_age
  FROM student where student_grade=100;
  --输出成绩为100分的那个学生信息
  dbms_output.put_line('姓名：'||s_name||',年龄：'||s_age||',籍贯：'||s_address);
END

(3) Select statement

//if 后面一定要添加THEN 相当于Java中的 大括号
IF s_sex=1 THEN
  dbms_output.put_line('这个学生是男生');
ELSE IF THEN
  dbms_output.put_line('这个学生是女生');
ELSE
  dbms_output.put_line('这个学生性别错误');
END IF

(4) Loop statement

a.Basic loop

LOOP
  IF 表达式 THEN
    EXIT;
  END IF
END LOOP;

b.while loop

WHILE 表达式 LOOP
  dbms_output.put_line('haha');
END LOOP;

c.for loop

//a 1 到 20 范围
FOR a in 10 .. 20 LOOP
  dbms_output.put_line('value of a: ' || a);
END LOOP;

Recommended tutorial: "Oracle Tutorial"

The above is the detailed content of What does the basic structure of Oracle stored procedures include?. For more information, please follow other related articles on the PHP Chinese website!