Essayer d'écrire une procédure qui ne prend aucune valeur, ajoute une colonne de prix de vente à ma table de produits existante, puis effectue une boucle pour calculer un prix de vente et l'insérer dans la nouvelle colonne.

Je n'ai rien réussi à faire fonctionner, je pense que c'est dû au fait qu'Oracle n'aime pas que ALTER TABLE soit exécuté depuis l'intérieur d'une procédure, mais je ne sais pas, et je n'en sais pas assez pour diriger mes tentatives n'importe où ailleurs.

C'est ma tentative

CREATE or REPLACE PROCEDURE ProductLineSale as
BEGIN
    DECLARE
       NewSalePrice NUMBER(6,2):=0; 
    EXECUTE IMMEDIATE 'alter table ' || Product || 'add or replace column' || 'SalePrice NUMBER(6,2);'
    FOR p in (SELECT ProductStandardPrice FROM Product
            group by ProductStandardPrice)
        LOOP
            CASE WHEN p.ProductStandardPrice>=400 THEN NewSalePrice:=.9*price
                 WHEN p.ProductStandardPrice<400 THEN NewSalePrice:=.85*price
            INSERT INTO Product(SalePrice)
            VALUES(NewSalePrice)
        END LOOP;
END ProductLineSale

Product est le nom littéral de la table Product dans ma base de données. SalePrice est ce que je voudrais que la nouvelle colonne soit nommée.

SQLDeveloper ne compilera pas la procédure. L'erreur que j'obtiens est également assez cryptique:

Erreur (2,10) : PLS-00103 : A rencontré le symbole « =" en s'attendant à l'un des éléments suivants : table d'exception constante longue double référence char heure horodatage intervalle date caractère national binaire nchar.

0
whgski 8 nov. 2020 à 01:23

1 réponse

Meilleure réponse

Il y a une foule d'erreurs... Celles qui me sautent aux yeux lors d'un premier passage.

  • L'exigence n'a pas de sens. L'ajout d'une colonne dans une procédure n'a pas de sens. Vous créez une procédure parce que vous voulez que le code soit réutilisable. L'ajout d'une colonne ne peut se faire qu'une seule fois, elle n'est donc par définition pas réutilisable.
  • Une procédure doit être compilée avant de pouvoir être exécutée. S'il existe une référence à une colonne qui n'existe pas, la procédure échouera à compiler. Ainsi, si vous souhaitez ajouter une colonne à la table à l'aide du SQL dynamique, toutes les références ultérieures à la colonne (c'est-à-dire votre instruction insert) devront également utiliser le SQL dynamique.
  • Votre déclaration DDL est incorrecte. Il n'y a pas de clause add or replace, c'est alter table product add SalePrice NUMBER(6,2). Notez que lorsque vous construisez votre chaîne, vous devez également vous assurer qu'il y a un espace entre la clause add et le nom de la colonne SalesPrice - l'une des deux chaînes que vous concaténez ensemble serait besoin de cela.
  • Cela n'a pas de sens d'avoir un declare là où vous en avez. Vous pouvez déclarer des variables entre le as et le begin une ligne au-dessus. Vous êtes autorisé à y créer un bloc PL/SQL imbriqué avec le declare, mais vous auriez alors besoin d'un begin et d'un end correspondants que vous n'avez pas.
  • Si vous comptez utiliser une instruction case en PL/SQL, vous aurez besoin d'un end case. Vous auriez également besoin d'un point-virgule ; après chaque expression.
  • Il manque également un point-virgule dans votre instruction insert.
  • Logiquement, j'ai du mal à imaginer que vous vouliez vraiment avoir un insert ici. Cela n'a pas de sens logique de créer un tas de nouvelles lignes dans le tableau lorsque vous ajoutez une nouvelle colonne. Je suppose que vous voulez update la valeur de la nouvelle colonne dans les lignes existantes. Ce qui, vraisemblablement, nécessite que votre curseur sélectionne la ou les colonnes de clé primaire et modifie potentiellement si et par quoi vous vous regroupez.
  • Product et price sont utilisés comme variables locales dans l'instruction execute immediate et dans l'instruction case mais ne sont pas définis. Je suppose que vous voulez juste coder en dur le nom de la table que vous modifiez et que price est censé faire référence au nom d'une colonne de la table que vous devez sélectionner dans votre curseur mais je ' je ne suis pas sûr.

Cette instruction case est syntaxiquement valide (ou le serait si price se résout en quelque chose de valide). La plupart des autres corrections sont moins évidentes pour les raisons que j'ai détaillées ci-dessus.

case when p.ProductStandardPrice>=400 
     then NewSalePrice:=.9*price;
     when p.ProductStandardPrice<400 
     THEN NewSalePrice:=.85*price;
  end case;

Si je devais spéculer sur ce que vous voulez réellement (étant donné qu'il s'agit d'un devoir avec des exigences qui n'ont aucun sens), je suppose que quelque chose comme

CREATE or REPLACE PROCEDURE ProductLineSale 
as
begin
  execute immediate 'alter table Product add SalePrice NUMBER(6,2)';
  execute immediate 'update product ' ||
                    '   set SalePrice = (case when ProductStandardPrice >= 400 ' ||
                    '                         then 0.9 * Price ' ||
                    '                         else 0.85 * Price ' ||
                    '                      end) ';
end ProductLineSale;

Si vous envisagez d'utiliser du SQL dynamique, il est presque toujours judicieux de déclarer une variable locale, de créer l'instruction SQL dans cette variable, puis de l'exécuter afin de pouvoir déboguer les choses en imprimant l'instruction que vous avez créée pour déboguer ce.

1
Justin Cave 8 nov. 2020 à 09:41