1
votes

Comment récupérer la valeur d'une colonne (pour une ligne particulière) lorsque le nom de la colonne correspond à une variable passée dynamiquement?

J'ai la structure de table suivante dans la base de données Oracle. Je veux savoir combien de points ABC a-t-il obtenu en mathématiques? Le nom du sujet est dynamique. Cela peut changer en fonction de l'entrée de l'utilisateur.

Table des étudiants:

Student_Id  Student_Name    Maths   English History Physics
1                ABC         93      89      90      70
2                XYZ         88      98      88      80
3                DEF         79      78      87      90

Existe-t-il un moyen de faire correspondre le nom de la colonne avec une valeur particulière extraite dynamiquement via l'entrée de l'utilisateur ?

sql oracle select procedure callable-statement

2 commentaires

Je ne sais pas si cela doit être résolu dans la requête. Je suppose que l'utilisateur accède à un site Web pour obtenir ces scores. Ensuite, codez simplement le logiciel frontal pour afficher uniquement le (s) score (s) qu'il souhaite voir.

Votre problème est la base de données. Vous feriez mieux si vous changiez votre modèle de données en trois tables: étudiants (student_id, name), topics (subject_id, name), student_subject (student_id, subject_id, grade). Dans votre application, vous proposez les sujets lus à partir du tableau des sujets et la requête utilise l'ID du sujet choisi pour obtenir la note. Très simple et facilement extensible à plus de matières (juste plus d'entrées dans les tableaux) ou peut-être des notes par an (ajoutez une colonne d'année à la table student_subject).

4 Réponses :

1
votes

Le SQL dynamique est ce dont vous avez besoin. Voici un exemple:

Exemple de données:

SQL> SELECT f_sum ('maths') FROM DUAL;

F_SUM('MATHS')
--------------
           260

SQL>

Fonction:

SQL> CREATE OR REPLACE FUNCTION f_sum (par_subject IN VARCHAR2)
  2     RETURN NUMBER
  3  IS
  4     l_str   VARCHAR2 (200);
  5     retval  NUMBER;
  6  BEGIN
  7     l_str :=
  8           'select sum('
  9        || DBMS_ASSERT.simple_sql_name (par_subject)
 10        || ') from student';
 11
 12     EXECUTE IMMEDIATE l_str INTO retval;
 13
 14     RETURN retval;
 15  END;
 16  /

Function created.

Test:

SQL> select * from student;

        ID NAM      MATHS    ENGLISH
---------- --- ---------- ----------
         1 ABC         93         89
         2 XYZ         88         98
         3 DEF         79         78

2 commentaires

Le PO a demandé "Je veux trouver combien de points ABC a-t-il obtenu en mathématiques?" Cela trouve le score total pour un sujet plutôt que le score d'un individu.

Ah oui. OK alors, vous avez montré comment faire cela, voici comment cela aurait pu être fait si la question était différente.

1
votes

Vous n'avez pas besoin de SQL dynamique ni même de PL / SQL. Vous pouvez le faire dans une requête SQL en utilisant UNPIVOT :

Oracle Setup :

| GETSCORE('ABC','MATHS') |
| ----------------------: |
|                      93 |

Requête :

SELECT getScore( 'ABC', 'MATHS' ) FROM DUAL;

CREATE FUNCTION getScore(
  i_student_name IN TEST_DATA.STUDENT_NAME%TYPE,
  i_subject      IN VARCHAR2
) RETURN NUMBER
IS
  p_score NUMBER(3,0);
BEGIN
  SELECT CASE i_subject
         WHEN 'MATHS' THEN Maths
         WHEN 'ENGLISH' THEN English
         WHEN 'HISTORY' THEN History
         WHEN 'PHYSICS' THEN Physics
         END
  INTO p_score
  FROM   test_data
  WHERE  student_name = i_student_name;

  RETURN p_score;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;
/

db fiddle ici

Si vous souhaitez utiliser une fonction PL / SQL, vous n'avez toujours pas besoin de SQL dynamique et pouvez simplement envelopper la requête ci-dessus ou utiliser une instruction CASE , comme ci-dessous: / p>

Fonction PL / SQL :

| SCORE |
| ----: |
|    93 |

Puis:

SELECT score
FROM   test_data
UNPIVOT ( score FOR subject IN ( Maths, English, History, Physics ) )
WHERE subject      = 'MATHS'
AND   student_name = 'ABC'

Sorties:

CREATE TABLE test_data ( Student_Id, Student_Name, Maths, English, History, Physics ) AS
SELECT 1, 'ABC', 93, 89, 90, 70 FROM DUAL UNION ALL
SELECT 2, 'XYZ', 88, 98, 88, 80 FROM DUAL UNION ALL
SELECT 3, 'DEF', 79, 78, 87, 90 FROM DUAL;

db fiddle ici

0 commentaires

2
votes

Vous devez savoir quelles sont les colonnes du tableau. Par conséquent, vous pouvez écrire la requête en utilisant une expression case :

select sm.marks
from studentmarks sm join
     students s
     on sm.student_id = s.student_id
where subject = :input;

En fait, cette question aide à expliquer pourquoi il est préférable de stocker ces données dans lignes plutôt que des colonnes. Si votre tableau était structuré comme suit:

Élèves:

Student_Id    Subject    Marks   
    1         Maths        93
    1         English      89
    1         History      90
    1         Physics      70
    . . .

StudentMarks

Student_Id    Student_Name
    1            ABC 
    . . .

(Notez que vous pourriez également vouloir une table de référence séparée pour les sujets.)

La requête serait triviale avec cette structure de données:

select (case when :input = 'Maths' then Maths
             when :input = 'English' then English
             when :input = 'History' then History
             when :input = 'Physics' then Physics
        end)  
from students
were Student_Name = 'ABC';

Une telle structure serait facilite également l’ajout de nouveaux sujets.

0 commentaires

0
votes

De plus, il existe une autre approche utilisant xmlquery comme suit:

SELECT      
      to_number(xmlquery('/ROWSET/ROW/C/text()'
        passing xmltype(dbms_xmlgen.getxml(
          'select '|| <subject_name> || ' as c '
          || 'from test_data WHERE student_name = ''' 
          || <student_name>  || ''''))
      returning content)) as marks
    FROM dual;

db démo violon

Bravo !!

0 commentaires