ORACLE: à l'Aide d'expressions de table communes (Common Table Expressions) avec PL/SQL

Tout d'abord, mon parcours est dans SQL Server. À l'aide d'expressions de table communes (Common Table Expressions) est un jeu d'enfant et de le convertir à une procédure stockée avec des variables ne nécessite pas de modifications de la structure du SQL d'autres que de remplacer les valeurs saisies avec les noms de variable.

En Oracle PL/SQL cependant, il est d'une nature différente. Mes expressions de table communes beau travail en tant que droit SQL, mais une fois que j'ai essayer d'envelopper comme PL/SQL que je tombe sur une foule de questions. De ma compréhension, SÉLECTIONNEZ maintenant besoin d'une DANS laquelle seules les résultats d'un seul enregistrement. Cependant, je suis désireux de l'ensemble du jeu d'enregistrements de plusieurs valeurs.

Mes excuses si je suis absent de l'évidence ici. Je pense que 99% de mon problème est le changement de paradigme que je dois faire.

Donné l'exemple suivant:

NOTE: je me suis grandement sur la simplification du SQL ici. Je sais, l'exemple ci-dessous peut être réalisé en une seule instruction SQL. Le SQL est beaucoup plus complexe. C'est les bases, je suis à la recherche d'ici.

WITH A as (SELECT * FROM EMPLOYEES WHERE DEPARTMENT = 200),

B as (SELECT * FROM A WHERE EMPLOYEE_START_DATE > date '2014-02-01'),

C as (SELECT * FROM B WHERE EMPLOYEE_TYPE = 'SALARY')

SELECT 'COUNTS' as Total,
(SELECT COUNT(*) FROM A) as 'DEPT_TOTAL',
(SELECT COUNT(*) FROM B) as 'NEW_EMPLOYEES',
(SELECT COUNT(*) FROM C) as 'NEW_SALARIED'
FROM A
WHERE rowcount = 1;

Maintenant, si je veux faire cela en PL/SQL avec des variables qui sont passés dans ou prédéfinies sur le dessus, ce n'est pas une simple question de déclarer les variables, popping valeurs en eux, et à changer mes valeurs codées en dur dans des variables et de l'exécuter. NOTE: je sais que je peux simplement changer les valeurs codées en dur à des variables comme :Département :date de début, et le Type, mais encore une fois, je suis trop simplifier l'exemple.

Il y a trois questions, je suis confronté ici que je suis en train d'envelopper ma tête autour de:

1) Quelle serait la meilleure façon de réécrire ce à l'aide de PL/SQL avec les variables déclarées? Les expressions de table communes ont maintenant pour aller DANS quelque chose. Mais ensuite, j'ai fais affaire avec une ligne à la fois par opposition à l'ensemble de la table. Donc, CTE de 'A' est une seule ligne à la fois, et CTE B ne verrez que la ligne unique, par opposition à tous les résultats des données d'Un, etc. Je sais que je vais probablement avoir à utiliser les CURSEURS pour parcourir les dossiers, ce qui semble en quelque sorte plus compliquer ce.

2) La sortie a maintenant utiliser DBMS_OUTPUT. Pour plusieurs enregistrements, je vais devoir utiliser un CURSEUR avec FETCH (ou POUR...la BOUCLE). Oui?

3) Est ce qu'il y a un gros problème de performance avec ce vs droite SQL en ce qui concerne la vitesse et les ressources utilisées?

Merci d'avance et encore une fois, toutes mes excuses si je suis absent quelque chose de vraiment évident ici!

S'il vous plaît consulter par exemple pour stackoverflow.com/tags/plsql/info et de commencer la lecture de Steven Feuerstein du PL/SQL 101 de la série (vous trouverez les liens dans la balise wiki).

InformationsquelleAutor VermontCoder | 2014-11-12

1

Tout d'abord, cela n'a rien à voir avec les expressions de table communes. Ce problème serait le même avec un simple select * from table requête. La différence est que, avec T-SQL, la requête va dans un curseur implicite qui est retournée à l'appelant. Lors de l'exécution de la SP de la Gestion de Studio, c'est pratique. Le résultat s'affiche dans la fenêtre de données, comme si l'on avait exécuté la requête directement. Mais c'est en fait non-standard de comportement. Oracle a plus de norme de comportement qui peuvent être définis comme "l'ensemble de résultats de la requête qui n'est pas dirigé dans un curseur doit être adressée à des variables." Lorsqu'elle est dirigée dans des variables, puis la requête doit retourner une seule ligne.

De reproduire le comportement de T-SQL, il vous suffit de déclarer explicitement et retour du curseur. Ensuite, le code appelant extrait du curseur de l'ensemble de résultats, mais une ligne à la fois. Vous n'avez pas profiter de la commodité de Sql ou Développeur PL/SQL Developer en détournant l'ensemble des résultats à l'affichage des données de la fenêtre, mais vous ne pouvez pas tout avoir.

Cependant, comme nous n'avons généralement pas d'écrire SPs juste pour être appelée à partir de l'IDE, il est plus facile de travailler avec Oracle explicite de curseurs que SQL Server est implicitement. Juste google "oracle retour curseur ref de l'appelant, l'" pour obtenir un ensemble beaucoup de bon matériel.

InformationsquelleAutor TommCatt
1

Façon la plus simple est l'envelopper dans un implicite pour la boucle
```
begin
   for i in (select object_id, object_name
               from user_objects
              where rownum = 1) loop
      -- Do something with the resultset
      dbms_output.put_line (i.object_id || ' ' || i.object_name);
   end loop;
end;
```
Seule ligne de la requête, sans la nécessité de saisir les variables.
- Juste que d'un côté-comment - à partir d'Oracle 10 implicite POUR les boucles, comme indiqué par @OlafurTryggvason utiliser automatiquement en VRAC RECUEILLIR sémantique interne. Ainsi, dans mon expérience il y a peu d'avantages à déclarer les curseurs à l'aide d'une déclaration de CURSEUR plus. Vous devez uniquement utiliser un SELECT...INTO... si vous êtes absolument certain que la SÉLECTIONNER en question ne retourner qu'une seule ligne. TSQL et PL/SQL sont différents des bêtes à l'aide de différents modèles de programmation, mais rester avec elle et après une petite quantité de douleur mentale, vous allez en prendre l'habitude (PL/SQL, je veux dire, pas la peine :-). Le meilleur de la chance.
- Alors pourquoi ne pas utiliser explicitement en VRAC RECUEILLIR et POURTOUT de la déclaration 😉 je souhaite affiches mentionnent leur Oracle version spécifiquement jusqu'à quatre décimales, pour éviter toute confusion, le cas échéant.
- Je ne l'utilise explicite en vrac recueillir à la Limite de la subdivision.
- Bob - Merci pour l'encouragement, la douleur est à droite! Je continue de penser que je suis faire cela d'autant plus difficile qu'elle ne l'est en réalité. En VRAC RECUEILLIR ressemble à un pas dans la bonne direction. Vais faire quelques expériences avec elle. Lalit - Oracle 11.2.0.4
- Petite mise à jour ici. En VRAC RECUEILLIR est certainement le chemin à parcourir et réalise ce que je dois faire. Je n'ai couru dans un problème avec l'interrogation de la collection créée avec l'essentiel de RECUEILLIR. Je n'ai utiliser le tableau([NAME_OF_BULK_COLLECT_COLLECTION]), j'ai eu un peu par la "collection locale types pas autorisés dans les instructions SQL" erreur. J'ai ensuite utilisé le créer ou de remplacer le type en haut de la procédure, mais alors aucun résultat. Il ressemble à l'administrateur de ne pas me donner accès à la création d'objets dans la base de données. Devra claquer le fouet sur son demain. 😉
InformationsquelleAutor Olafur Tryggvason

Vous devez vous connecter pour publier un commentaire.