PL/SQL - 使用立即执行基于游标创建表

Question

我编写了以下代码，用于选择一些学生测试数据并使用光标将其插入表中。id希望能够做的是为每个学生创建一个表并插入他们的相对数据。这可以是一行或多行。

SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE run_student_scores 
  IS
  CURSOR c_pass_fail_cursor
    IS
  SELECT students.firstname,
      test_history.score,
      test_id.test_name,
      test_id.passing_grade
  FROM students
  INNER JOIN test_history
    ON students.student_id = test_history.student_id
  INNER JOIN test_id
    ON test_id.test_id = test_history.test_id
    WHERE test_history.start_time BETWEEN to_timestamp(sysdate) + INTERVAL '8' HOUR 
      AND to_timestamp(sysdate) + INTERVAL '21' HOUR;
v_name    students.firstname%TYPE;
v_score   test_history.score%TYPE;
v_test    varchar2(40);
v_passing test_id.passing_grade%TYPE;
v_result  varchar2(4);
BEGIN
  EXECUTE IMMEDIATE ('create table student_tests_' || (to_char(sysdate, 'yyyymmdd')) ||     '(student_name VARCHAR2(20), test_name varchar2(40), test_score NUMBER(3), pass_rate     NUMBER(3), pass_fail VARCHAR2(4))');
OPEN c_pass_fail_cursor;
 LOOP
FETCH c_pass_fail_cursor INTO v_name, v_score, v_test, v_passing;
EXIT WHEN c_pass_fail_cursor%NOTFOUND;
If v_score < v_passing 
  THEN v_result := 'Fail';
    DBMS_OUTPUT.PUT_LINE(v_name || ' ' || v_score || ' ' || v_test || ' ' || V_passing || ' ' || 'Result =' || v_result);
ELSE
  v_result := 'Pass';
  DBMS_OUTPUT.PUT_LINE(v_name || ' ' || v_score || ' ' || v_test || ' ' || V_passing || ' ' || 'Result =' || v_result);
END IF;
EXECUTE IMMEDIATE 'INSERT INTO student_tests_' || (to_char(sysdate, 'yyyymmdd')) || ' ' || ' values(:1, :2, :3, :4, :5)' using v_name, v_test, v_score, v_passing, v_result;
END LOOP;
CLOSE c_pass_fail_cursor; 
END;
/ 

在过去的几天里，我一直在玩它，但无法让它工作。我能得到的最接近的是创建表并仅插入第一行，当循环尝试创建已经存在的表时生成错误。

任何帮助都会很棒

谢谢大家

本

Answer 1

结果：每个学生 1 张桌子（按名称唯一）。

create table ...现在位于由异常块包围的循环内。如果该表已存在，则处理异常 （ORA-00955）。

SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE run_student_scores 
IS
  CURSOR c_pass_fail_cursor
    IS
  SELECT students.firstname,
      test_history.score,
      test_id.test_name,
      test_id.passing_grade
  FROM students
  INNER JOIN test_history
    ON students.student_id = test_history.student_id
  INNER JOIN test_id
    ON test_id.test_id = test_history.test_id
    WHERE test_history.start_time BETWEEN to_timestamp(sysdate) + INTERVAL '8' HOUR 
      AND to_timestamp(sysdate) + INTERVAL '21' HOUR;
v_name    students.firstname%TYPE;
v_score   test_history.score%TYPE;
v_test    varchar2(40);
v_passing test_id.passing_grade%TYPE;
v_result  varchar2(4);
    --New variables
    table_already_exists EXCEPTION;
    PRAGMA EXCEPTION_INIT(table_already_exists, -955);
BEGIN
OPEN c_pass_fail_cursor;
 LOOP
FETCH c_pass_fail_cursor INTO v_name, v_score, v_test, v_passing;
EXIT WHEN c_pass_fail_cursor%NOTFOUND;
    begin
        EXECUTE IMMEDIATE ('create table student_tests_' || v_name  || ' (student_name VARCHAR2(20), test_name varchar2(40), test_score NUMBER(3), pass_rate     NUMBER(3), pass_fail VARCHAR2(4))');
    exception when table_already_exists then
        null;
    end;
If v_score < v_passing 
  THEN v_result := 'Fail';
    DBMS_OUTPUT.PUT_LINE(v_name || ' ' || v_score || ' ' || v_test || ' ' || V_passing || ' ' || 'Result =' || v_result);
ELSE
  v_result := 'Pass';
  DBMS_OUTPUT.PUT_LINE(v_name || ' ' || v_score || ' ' || v_test || ' ' || V_passing || ' ' || 'Result =' || v_result);
END IF;
EXECUTE IMMEDIATE 'INSERT INTO student_tests_' || v_name || ' ' || ' values(:1, :2, :3, :4, :5)' using v_name, v_test, v_score, v_passing, v_result;
END LOOP;
CLOSE c_pass_fail_cursor; 
END;
/ 

Answer 2

Grafros 的答案看起来不错，但我强烈建议DBMS_ASSERT一起看看DBMS_ASSERT。ENQUOTE_NAME或DBMS_ASSERT。ENQUOTE_LITERAL，只要你拿了 abtrary 字符串并在执行中使用它。