Oracle's Loops and Corsor

   这两天啊有一个心的业务，是需要如果我批量修改数据的话，那么另一张表的数据也需要修改，也是多条的修改，发现这个问题的时候，自然而然的想到了触发器，但是以前都是简单的单条语句的跟新，没有过整个表的去做一个触发器，今天学习了一下数据库中的循环，这里写一下。    在写触发器的时候用到了一个循环和一个Cursor，触发器如下：

create  OR  replace  trigger  trg_test before   update  on  cm_bogie_inandoutreport declare //define cursors, Find values for each column of the table edge cursor  c_bogie is  select  s_bureaunumno C_b_bureaunumno, S_serialnum c_b_serialnum, S_trainsetid c_b_t Rainsetid from  cm_bogie_inandoutreport;    BEGIN //Loop assignment for  V_record in  C_bogie loop updat E TEST_MHB t set T.s_id=v_record.c_b_bureaunumno where t.s_sex = v_ Record.c_b_bureaunumno;Dbms_output.put_line (C_B_BUREAUNUMNO); end loop;   END trg_test;

Cycle
The general loop is roughly divided into three types: the basic loop (loop), while loop, the For loop

1. Basic cycle

LOOP          statement1;          ......           EXIT [WHENENDLOOP

When a basic loop is used, the statement is executed at least once, regardless of whether the condition is met, and when condition is true, the loop exits and the corresponding action after the end loop is executed. When writing a basic loop, be sure to include the exit statement, or you will get into a dead loop. You should also define a loop control variable and modify the value of the loop control variable in the loop body. Example:

declare    i int:=1;    begin    loop    into testloop values(i);    exitwhen i=10;    i:=i+1;    endloop;    end;

2.WHILE Cycle

The basic loop executes at least one statement in the body of the loop, whereas for a while loop, the statement in the loop body executes only if the condition is true. While loop with while ... Loop starts and ends with end loop.

WHILELOOP            statement1;            statement2;            ENDLOOP

When condition is true, the statement in the loop body is executed, and when condition is false or null, the loop exits and the statement after the end loop is executed. When using the while loop, you should define a loop control variable and change the value of the loop control variable in the loop body. Example:

SQL> declare    i int:=1;    begin    whilei<=10 loop    insert into testloop values(i);    i:=i+1;    end loop;    end

3.FOR Cycle

When using a basic loop or while loop, you need to define a loop control variable, and the loop control variable can use not only the number type, but also other data types. When using a For loop, Oracle implicitly defines the loop control variable.

FORin [REVERSELOOP          statement1;          statement2;          ENDLOOP

When we query, we use select and into to put the contents of the select into inside, and the cursor gives us a convenience, so the question
What is a cursor?
① retrieves the result set from the table, and the mechanism from which each point points to a record for interaction.

The operations in the ② relational database are performed on the complete rowset.
The rowset returned by the SELECT statement includes all rows that satisfy the conditions listed in the WHERE clause of the statement. The complete rowset returned by the statement is called the result set.
Applications, especially interactive and online applications, treat a complete set of results as a unit that is not always valid.
These applications require a mechanism to process a row or rows at a time. The cursor is an extension of the result set that provides this mechanism.

 游标是通过游标库来实现的。游标库是常常作为数据库系统或数据访问 API 的一部分而得以实现的软件， 用来管理从数据源返回的数据的属性（结果集）。这些属性包括并发管理、在结果集中的位置、返回的行数， 以及是否能够在结果集中向前和/或向后移动（可滚动性）。 游标跟踪结果集中的位置，并允许对结果集逐行执行多个操作，在这个过程中可能返回至原始表，也可能不返回至原始表。 换句话说，游标从概念上讲基于数据库的表返回结果集。 由于它指示结果集中的当前位置 ，就像计算机屏幕上的光标指示当前位置一样，“游标”由此得名。

2, what is the function of the cursor?
① Specifies the location of a particular row in the result set.
② retrieves a row or successive rows based on the current result set location.
③ modifies the data in the row at the current position of the result set.
④ defines different levels of sensitivity for data changes made by other users.
⑤ can access the database programmatically.
3, how to use the cursor

Example1:/* Conn Scott/tiger */DeclareCursor Mycur is SelectEmpno,ename,sal fromEmp VNA VARCHAR2 (Ten); VNO Number (4); Vsal Number (7,2);     Begin Open mycur; Fetch Mycur intoVno,vna,vsal; Dbms_output.put_line (vno| |"| |    vna| | ' ' | | Vsal);Close mycur;End; /Example2: UsingLoopTraverse the cursor. /* Conn Scott/tiger */DeclareCursor Mycur is SelectEname,job,sal,empno fromEmp  Vare Mycur%rowtype; BeginifMycur%isopen =false  ThenOpen mycur; Dbms_output.put_line (' Opening ... ');     End if;LoopFetch Mycur intoVare;Exit  whenMycur%notfound; Dbms_output.put_line (mycur%rowcount| |"| |    vare.empno| | ' ' | |    vare.ename| | ' ' | | Vare.sal);     End Loop;ifMycur%isopen ThenClose mycur; Dbms_output.put_line (' Closing ... ');     End if;End;

   其实游标还是挺好用的，就和Select 。。。into...一样，游标大家把他当作是一个表就可以，只不过这个游标还是指向咱们查询出来表的第一行。

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Oracle's Loops and Corsor