Oracle for Loop loops and cursor loops

Source: Internet
Author: User

1. In loop form

DECLARE
CURSOR C_sal is SELECT employee_id, first_name | | Last_Name ename, Salary
From employees;
BEGIN
--Implicitly open cursors
For v_sal in C_sal LOOP
--implicitly executes a FETCH statement
Dbms_output. Put_Line (To_char (v_sal.employee_id) | | ---' | | v_sal.ename| | ' ---' | | To_char (v_sal.salary));
--Implicit monitoring c_sal%notfound
END LOOP;
--Implicitly closing cursors
END;

2. Normal cursor Loops

  declare    --定义游标并且赋值(is 不能和cursor分开使用)    cursor stus_cur is select * from students;    --定义rowtype    cur_stu students%rowtype;    /*开始执行*/    begin    --开启游标    open stus_cur;     --loop循环     loop     --循环条件     exit when stus_cur%notfound;     --游标值赋值到rowtype     fetch stus_cur into cur_stu;     --输出     dbms_output.put_line(cur_stu. name );     --结束循环     end loop;    --关闭游标    close stus_cur;    /*结束执行*/   end ;3.Efficient cursor Looping   declare   cursor myemp_cur   is select * from myemp;   type myemp_tab is table of myemp%rowtype;   myemp_rd myemp_tab;   begin     open myemp_cur;     loop     fetch myemp_cur bulk collect into myemp_rd limit 20;     for i in 1..myemp_rd. count loop      dbms_output.put_line( ‘姓名:‘ ||myemp_rd(i).ename);     end loop;     exit when myemp_cur%notfound;     end loop;   end ;BULK COLLECTThe clause retrieves the result in bulk, binding the result set to a collection variable at a time, and sending it from the SQL engine to the PL/SQL engine. You can usually select INTO,
The bulk COLLECT is used in the FETCH into and returning into clauses. Limitations of BULK Collect
1. You cannot use the bulk COLLECT clause on an associative array that uses a string type as a key.
2, only in the server-side program to use bulk COLLECT, if used on the client, it will produce an error that does not support this feature.
3. The target object of BULK COLLECT into must be a collection type.
4. Composite targets (such as object types) cannot be used in the returning into clause.
5, if there are multiple implicit data type conversions, multiple composite targets cannot be used in the bulk COLLECT into clause.
6. If there is an implicit data type conversion, a collection of compound targets, such as a collection of object types, cannot be used in the bulk collectinto clause.

Oracle for Loop loops and cursor loops

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.