Shulou(Shulou.com)06/02 Report--

This article introduces the relevant knowledge of "what is the difference between static cursors and dynamic cursors in Oracle". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Preface

Cursors are often used in the stored procedures for writing Oracle. Cursors in Oracle are divided into static cursors and dynamic cursors. Today we are going to talk about the differences.

The concept of static cursor and dynamic cursor

Static cursor

Explicit and implicit cursors are called static cursors because the definition of cursors is complete and cannot be changed before they are used.

Definition:

Cursor cursor name (parameter 1, parameter 2.) Is query statement

When called:

For variable line in cursor name loop

End loop

Dynamic cursor

The cursor is not set when declared and can be modified when it is opened.

Definition:

TYPE cursor alias IS REF CURSOR

Cursor name cursor alias

When called:

Open cursor name for dynamic SQL statement

Loop

Exit when cursor name NOTFOUND

Fetch cursor name

Into variable 1, variable 2, variable 3, variable 4

Relatively speaking, static cursors are used more in stored procedures, while dynamic cursors are relatively less. For example, if we have a split table, we will use dynamic cursors to fetch data.

Code demonstration

Use background

We enter the start and end dates in the stored procedure to query the sales between the start and end dates.

Solution idea

In this query, we can use a combination of static and dynamic cursors.

The current year and month involved is obtained through the static cursor setting start and result dates.

Generate a dynamic SQL statement to query the corresponding month partition table based on the current year and month obtained.

Traverse with dynamic cursors, and then process the data.

Code

Above are defined and static cursors and dynamic cursors, and the following static cursors are written to obtain the corresponding current year and month according to the input start and end dates

That is to say, if you enter 2018-06-01, Muhammad, Murray, 2018-08-30,

The result of the cursor display is

201806

201807

201808

According to the date entered, the corresponding dynamic SQL statement is generated through a static cursor. Vs_sSql is our dynamic string. Add the main table query first, then find the corresponding month segmentation table according to the input start and end dates, and then determine whether the table exists. If so, add the partition table to the dynamic SQL statement with union all.

Use dynamic cursors to traverse sales through dynamic sql statements to update the data.

This is the end of the content of "what is the difference between static cursors and dynamic cursors in Oracle". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.