时间戳

首页 > 解决方案 > PL/SQL 过程使用游标在循环中获取元组

plsql - PL/SQL 过程使用游标在循环中获取元组

问题描述

我正在尝试创建一个从 TableA 读取 100 个元组的过程。对于从 TableA 检索到的每个元组，它还将使用 student_ID 索引从 TableB 检索相关元组，并将所需的属性添加到内存/记录中。具有新属性的元组将被加载到数据仓库中。在加载之前，它需要检查维度表是否已经包含信息。如果是，那么它只会更新事实表，或者更新两个表。重复所有步骤，直到所有数据都加载到数据仓库中。

以下是我正在构建的代码。

            CREATE OR REPLACE PROCEDURE PROJ AS
                v_tab_rec varchar2 (100);
                v_counter number (5);
                v_student_id TableB.student_id%type;
                v_studname TableB.studname%type;
                v_address TableB.address%type; 
                v_coursename TableB.coursename%type;
            DECLARE
                CURSOR c_TableA IS
                SELECT * FROM TableA;
                v_tab_rec c_TableA%ROWTYPE;
            BEGIN
                OPEN c_TableA;
                v_counter :=1;
                WHILE (v_counter <= 500)   
                    LOOP
                        FETCH c_TableA INTO v_tab_rec;
                        EXIT WHEN c_TableA%NOTFOUND;
                        
                        SELECT * FROM v_tab_rec WHERE v_tab_rec.student_id = v_TableB.student_id;
                        
                        IF v_tab_rec.student_id NOT IN (SELECT * FROM Student) THEN
                            INSERT INTO Student VALUES (v_tab_rec.student_ID, v_studname, v_address);
                        ELSIF v_tab_rec.course_id NOT IN (SELECT * FROM Course) THEN
                            INSERT INTO Course VALUES (v_tab_rec.course_id, v_coursename);
                        ELSIF v_tab_rec.subject_id NOT IN (SELECT * FROM Subject) THEN
                            INSERT INTO Subject VALUES (v_tab_rec.subject_id, v_tab_rec.subject_name);
                        END IF;
                     END LOOP;
                 END LOOP;
                    IF c_tableA%ISOPEN THEN
                        CLOSE c_tableA;
                    END IF;
                COMMIT;
                v_counter := v_counter +100;
            END PROJ;
            /

标签： plsqltuplescursorfetchprocedure

解决方案

您的程序充满了结构（编译时）和逻辑（运行时）的错误。此外，您的代码似乎与描述不符；这本身就是令人困惑的。在下文中，我试图指出您程序中的结构性错误和一些评论。见标签---<<<。

CREATE OR REPLACE PROCEDURE PROJ AS
                v_tab_rec varchar2 (100);                 
                v_counter number (5);
                v_student_id TableB.student_id%type;
                v_studname TableB.studname%type;
                v_address TableB.address%type; 
                v_coursename TableB.coursename%type;
            DECLARE  
            ---<<< Declare. Not necessary, or you are sstarting a nested block
            ---<<< This is not an Error, but does have ramafacations later which 
            ---<<< would generate an error.
                CURSOR c_TableA IS
                SELECT * FROM TableA;
                v_tab_rec c_TableA%ROWTYPE;     
                ---<<< v_ab_rec already defined as scalar variable
            BEGIN
                OPEN c_TableA;
                v_counter :=1;
                WHILE (v_counter <= 500) 
                ---<<< Login: Above essentially createa an infinate loop
                ---<<< as v_counter only incremented AFTER loop is complete
                ---<<< so above condition will always remain true. 
                    LOOP
                        FETCH c_TableA INTO v_tab_rec;
                        ---<<< v_tab_rec duplicate definition as Cursor Row type AND Scalar 
                        EXIT WHEN c_TableA%NOTFOUND;
                        
                        SELECT * FROM v_tab_rec WHERE v_tab_rec.student_id = v_TableB.student_id;
                        ---<<< v_tab_rec is NOT a table or view so cannot select from it 
                        ---<<< even if you could INTO clause missing from select. Required in PL/SQL.
                        ---<<< v_TableB is undefined 
                        
                        IF v_tab_rec.student_id NOT IN (SELECT * FROM Student) THEN
                        ---<<< The "Select *: is invalid as used but even if it were allowed
                        ---<<< it retrieves all columns from student. You cannot compare 
                        ---<<< the scalar v_tab_rec.student_id to the row returned by the select. 
                            INSERT INTO Student VALUES (v_tab_rec.student_ID, v_studname, v_address);
                        ---<<< nor would the above select (if valid) populate the "v_" variables.  
                       
                        ELSIF v_tab_rec.course_id NOT IN (SELECT * FROM Course) THEN
                        ---<<< Same problems as Student
                            INSERT INTO Course VALUES (v_tab_rec.course_id, v_coursename);
                        
                        ELSIF v_tab_rec.subject_id NOT IN (SELECT * FROM Subject) THEN
                         ---<<< Same problems as Student
                            INSERT INTO Subject VALUES (v_tab_rec.subject_id, v_tab_rec.subject_name);
                        END IF;
                     END LOOP;
                 END LOOP;
                 ---<<< End second loop, however there is only 1 loop
                    IF c_tableA%ISOPEN THEN
                        CLOSE c_tableA;
                    END IF;
                COMMIT;
                v_counter := v_counter +100;
                ---<<< Why increment after the loop is complete (not an error,
                ---<<< but serves no purpose 
                
            END PROJ;

---<<< 逻辑问题：
“If...Elsif...Elsif...End if”允许 3 个表中的任何一个不包含必要的 id。但是当 Student、Course 和 Subject 都缺少必要的 id 时会发生什么。

描述令人困惑。“从 TableA 读取 100 个元组”然后在处理 500 行后尝试退出的目的是什么。进一步“仅更新事实表，或以其他方式更新两个表”尚不清楚。什么是事实表（Student、Course 和 Subject？），但“两个表”中的另一个表是什么？TableB 的用途是什么？

推荐阅读