遍历游标的几种方式

最新推荐文章于 2023-12-21 16:03:52 发布

CD2

最新推荐文章于 2023-12-21 16:03:52 发布

阅读量2.1k

点赞数

分类专栏： Oracle 文章标签： insert c

本文链接：https://blog.csdn.net/CD2/article/details/6253661

版权

Oracle 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

-- 定义游标

CURSOR c_Student IS
SELECT ID, FIRST_NAME, LAST_NAME, MAJOR FROM T_TEST_STUDENTS;

-- 遍历游标

    OPEN c_Student;
    LOOP
      FETCH c_Student
        INTO v_NewId, v_NewFirstName, v_NewLastName, v_NewMajor;

      EXIT WHEN c_Student%NOTFOUND;
      INSERT INTO T_TEST_STUDENTS
        (ID, FIRST_NAME, LAST_NAME, MAJOR)
      VALUES
        (v_NewId, v_NewFirstName, v_NewLastName, v_NewMajor);

    END LOOP;

    CLOSE c_Student;
    COMMIT;

-- 定义游标

CURSOR c_Student IS
SELECT ID, FIRST_NAME, LAST_NAME, MAJOR FROM T_TEST_STUDENTS;

-- 遍历游标

FETCH c_Student INTO v_NewId, v_NewFirstName, v_NewLastName, v_NewMajor;

WHILE c_Student%FOUND LOOP

      INSERT INTO T_TEST_STUDENTS
        (ID, FIRST_NAME, LAST_NAME, MAJOR)
      VALUES
        (v_NewId, v_NewFirstName, v_NewLastName, v_NewMajor);

FETCH c_Student INTO v_NewId, v_NewFirstName, v_NewLastName, v_NewMajor;

END LOOP;

CLOSE c_Student;

COMMIT;

CURSOR c_Student IS
SELECT ID, FIRST_NAME, LAST_NAME, MAJOR FROM T_TEST_STUDENTS;

FOR v_Student in c_Student LOOP

      INSERT INTO T_TEST_STUDENTS
        (ID, FIRST_NAME, LAST_NAME, MAJOR)
      VALUES
        (v_Student.ID, v_Student.FIRST_NAME, v_Student.LAST_NAME, v_Student.MAJOR);

END LOOP;

COMMIT;

/* 不需要定义游标

CURSOR c_Student IS
SELECT ID, FIRST_NAME, LAST_NAME, MAJOR FROM T_TEST_STUDENTS;

FOR v_Student in (SELECT ID, FIRST_NAME, LAST_NAME, MAJOR FROM T_TEST_STUDENTS) LOOP

      INSERT INTO T_TEST_STUDENTS
        (ID, FIRST_NAME, LAST_NAME, MAJOR)
      VALUES
        (v_Student.ID, v_Student.FIRST_NAME, v_Student.LAST_NAME, v_Student.MAJOR);

END LOOP;

COMMIT;