Cours.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "# Cours de SQL\r\n",
                "\r\n",
                "Ce cours est présenté sous forme de Notebook, je le mettrai à jour après chaque séance. Il vous suffit de sélectionner une base de donnée et de jouer l'ensemble du notebook pour être prêt à aborder une nouvelle séance.\r\n",
                "\r\n",
                "## Rappels\r\n",
                "\r\n",
                "Nous travaillons avec SQL Server 2019, qui utilise la syntaxe Transact-SQL. En cas de problème/blocage, référez-vous à la documentation de Transact-SQL de préférence. D'autres documentations (MySQL, Postgres, Oracle, PL/SQL) pourraient vous aider, mais il existe des différences entre ces différents dialectes.\r\n",
                "\r\n",
                "## Contexte\r\n",
                "\r\n",
                "Nous allons prendre pour contexte une base de donnée de site de e-commerce assez sommaire. Seule une partie de la base va être modélisée, le but étant de présenter uniquement des notions de base de donnée et non de faire un projet complet.\r\n",
                "\r\n",
                "## Révisions\r\n",
                "\r\n",
                "Pour commencer, nous allons créer une base de données et nous positionner dessus pour travailler."
            ],
            "metadata": {
                "azdata_cell_guid": "76c5597e-b0d6-4801-b2ab-fa6491570956"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "-- On teste l'existence de la base pour éviter de\r\n",
                "-- crasher le notebook si elle existe déjà\r\n",
                "IF(DB_ID(N'eCommerce') IS NULL)\r\n",
                "    CREATE DATABASE [eCommerce];\r\n",
                "GO\r\n",
                "USE [eCommerce];\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "c17d6fd7-676a-4331-8d20-ddc7e73bbdbf",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.012"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 2
        },
        {
            "cell_type": "markdown",
            "source": [
                "Maintenant que nous avons une base de données, nous allons pouvoir créer une table. Nous allons commencer par la table des produits. Pour le moment, nous produits auront un `Id` ainsi qu'un `Nom`.\r\n",
                "\r\n",
                "Afin de faire des colones avec un auto-incrément, nous allons utiliser la propriété `Identity`. Nous allons également positionner une clé primaire sur notre colone `Id`."
            ],
            "metadata": {
                "azdata_cell_guid": "fb6ac25b-6380-4a2c-b1c8-d680b7f18633"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "IF NOT EXISTS (\r\n",
                "    SELECT  [name]\r\n",
                "    FROM    [sysobjects]\r\n",
                "    WHERE   [name]='Produit_PRD'\r\n",
                "            AND [xtype]='U')\r\n",
                "    CREATE TABLE [dbo].[Produit_PRD] (\r\n",
                "        [Id] BIGINT IDENTITY(1, 1) PRIMARY KEY,\r\n",
                "        [Nom] NVARCHAR(255) NOT NULL,\r\n",
                "    )\r\n",
                "    GO"
            ],
            "metadata": {
                "azdata_cell_guid": "14a9910d-e200-4d4f-9d84-ba9e2a100859",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.012"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 3
        },
        {
            "cell_type": "markdown",
            "source": [
                "Nous allons également ajouter une table qui contiendra des revues sur les différents articles proposés sur le site. Les revues auront une `Id`, clé primaire identité, une clé étrangère vers la table des produits, un titre et un contenu."
            ],
            "metadata": {
                "azdata_cell_guid": "bb08cc4f-07de-4b21-abee-25b8f8ec80c4"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "IF NOT EXISTS (\r\n",
                "    SELECT  [name]\r\n",
                "    FROM    [sysobjects]\r\n",
                "    WHERE   [name]='Revue_REV'\r\n",
                "            AND [xtype]='U')\r\n",
                "    CREATE TABLE [dbo].[Revue_REV] (\r\n",
                "        [Id] BIGINT IDENTITY(1, 1) PRIMARY KEY,\r\n",
                "        [PRD_Id] BIGINT NOT NULL,\r\n",
                "        [Titre] NVARCHAR(255) NOT NULL,\r\n",
                "        [Contenu] NVARCHAR(MAX) NOT NULL,\r\n",
                "        CONSTRAINT fk_PRD_Id_REV\r\n",
                "            FOREIGN KEY ([PRD_Id]) REFERENCES [Produit_PRD]([Id])\r\n",
                "    )\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "e8074b6c-7e08-4ca5-8799-d0ad6628ac1d",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.013"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 4
        },
        {
            "cell_type": "markdown",
            "source": [
                "### Opérations de base\r\n",
                "\r\n",
                "Maintenant que nous avons des tables, nous allons pouvoir y mettre du contenu, le récupérer, le modifier, le supprimer.\r\n",
                "\r\n",
                "Pour l'insertion, nous ne devons pas saisir de valeur pour les colonnes identités. Ainsi, si je souhaite insérer une ligne dans ma table de produit, je pourrais faire la requête suivante :"
            ],
            "metadata": {
                "azdata_cell_guid": "072fa0b9-e4c1-465a-bd01-ebca5e256f89"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "/*\r\n",
                "INSERT INTO [dbo].[Produit_PRD] ([Nom])\r\n",
                "VALUES (N'iPone 11'), (N'One Plus 7')\r\n",
                "GO\r\n",
                "*/"
            ],
            "metadata": {
                "azdata_cell_guid": "569cab0c-b777-4828-9568-a21ddf4b04d7",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.005"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 5
        },
        {
            "cell_type": "markdown",
            "source": [
                "On peut récupérer les données avec l'instruction `SELECT`"
            ],
            "metadata": {
                "azdata_cell_guid": "371c36ae-00db-435e-9018-fb30203d29de"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT\r\n",
                "        [Id],\r\n",
                "        [Nom]\r\n",
                "FROM    [dbo].[Produit_PRD]\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "3cf87aaa-9e4c-4d3c-8b3c-7624cf16b347",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.118"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 6,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "Id"
                                    },
                                    {
                                        "name": "Nom"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "1",
                                    "1": "iPhone 11"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>Id</th><th>Nom</th></tr><tr><td>1</td><td>iPhone 11</td></tr></table>"
                    }
                }
            ],
            "execution_count": 6
        },
        {
            "cell_type": "markdown",
            "source": [
                "On va pouvoir faire des mises à jour à l'aide de la clause `UPDATE`"
            ],
            "metadata": {
                "azdata_cell_guid": "2cbdd7af-1227-47c0-be6d-30251e4bf1b6"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "/*\r\n",
                "UPDATE [dbo].[Produit_PRD]\r\n",
                "SET [Nom] = N'iPhone 11'\r\n",
                "WHERE [Id] = 1\r\n",
                "GO\r\n",
                "*/"
            ],
            "metadata": {
                "azdata_cell_guid": "cfb887e6-1f49-4728-8f40-aef231eb46b2",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.005"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 7
        },
        {
            "cell_type": "markdown",
            "source": [
                "On peut également supprimer des lignes avec l'instruction `DELETE`"
            ],
            "metadata": {
                "azdata_cell_guid": "bae22e9e-dd7c-442d-a5d1-f1d4771095a0"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "/*\r\n",
                "DELETE FROM [dbo].[Produit_PRD] WHERE [Id] = 2\r\n",
                "GO\r\n",
                "*/"
            ],
            "metadata": {
                "azdata_cell_guid": "80a83375-5aa0-4b31-b089-81f572896729",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.005"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 8
        },
        {
            "cell_type": "markdown",
            "source": [
                "Nous pouvons modifier la structure des table à postériori, à l'aide de l'instruction `ALTER TABLE`. Nous allons modifier notre table de produits pour ajouter une colone avec le prix, un descriptif et un contenu."
            ],
            "metadata": {
                "azdata_cell_guid": "2ccf7c08-47d6-48f0-91a7-0f6e161b356d"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "IF NOT EXISTS (\r\n",
                "    SELECT [name] \r\n",
                "    FROM   [sys].[columns]\r\n",
                "    WHERE  [object_id] = OBJECT_ID(N'[dbo].[Produit_PRD]') \r\n",
                "            AND [name] = 'Prix'\r\n",
                ")\r\n",
                "    ALTER TABLE [dbo].[Produit_PRD]\r\n",
                "    ADD [Prix] DECIMAL(5, 2) NOT NULL,\r\n",
                "        [Descriptif] NVARCHAR(255) NULL,\r\n",
                "        [Contenu] NVARCHAR(MAX) NULL,\r\n",
                "        DEFAULT 0 FOR [Prix]\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "efb921d6-4e70-488c-8202-a96b8aefa634",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.030"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 9
        },
        {
            "cell_type": "markdown",
            "source": [
                "### Jointures\r\n",
                "\r\n",
                "Nous pouvons faire des jointures entre le contenu de différentes tables. Il existe différents types de jointures :\r\n",
                "\r\n",
                "![Jointures SQL](https://i.pinimg.com/originals/78/06/27/78062746cee62bdc112aad0582ae49c4.jpg)\r\n",
                "\r\n",
                "Ceci va nous permettre de créer des requêtes complexes."
            ],
            "metadata": {
                "azdata_cell_guid": "3cff37e0-cdab-4c4e-ada0-a1ade656639a"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT\r\n",
                "    [PRD].[Id] AS [ProduitId],\r\n",
                "    [PRD].[Nom] AS [ProduitNom],\r\n",
                "    [REV].[Titre] AS [RevueTitre]\r\n",
                "FROM [dbo].[Produit_PRD] AS [PRD]\r\n",
                "INNER JOIN [dbo].[Revue_REV] AS [REV]\r\n",
                "    ON [REV].[PRD_Id] = [PRD].[Id]\r\n",
                "ORDER BY [PRD].[Id] ASC\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "7141c1ae-fa5d-4c40-877f-66fb2f05e962",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(0 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.924"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 10,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "ProduitId"
                                    },
                                    {
                                        "name": "ProduitNom"
                                    },
                                    {
                                        "name": "RevueTitre"
                                    }
                                ]
                            },
                            "data": []
                        },
                        "text/html": "<table><tr><th>ProduitId</th><th>ProduitNom</th><th>RevueTitre</th></tr></table>"
                    }
                }
            ],
            "execution_count": 10
        },
        {
            "cell_type": "markdown",
            "source": [
                "### Vues\r\n",
                "\r\n",
                "Nous pouvons utiliser des vues pour avoir une vision de plusieurs tables aglomérées et mise en cache par le SGBD."
            ],
            "metadata": {
                "azdata_cell_guid": "20e7741f-acf4-48df-84e1-50c9e10a9f82"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "CREATE OR ALTER VIEW [dbo].[VueRevues]\r\n",
                "AS\r\n",
                "    SELECT\r\n",
                "        [PRD].[Id] AS [ProduitId],\r\n",
                "        [PRD].[Nom] AS [ProduitNom],\r\n",
                "        [REV].[Titre] AS [RevueTitre]\r\n",
                "    FROM [dbo].[Produit_PRD] AS [PRD] (NOLOCK)\r\n",
                "    INNER JOIN [dbo].[Revue_REV] AS [REV] (NOLOCK)\r\n",
                "        ON [REV].[PRD_Id] = [PRD].[Id]\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "ec83d5dd-5b49-4359-bcd3-1e69da31960b",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.013"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 11
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT [ProduitId], [ProduitNom], [RevueTitre]\r\n",
                "FROM [dbo].[VueRevues]\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "2587ddf6-eddc-4f77-b057-d8f6c7ded8fa",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(0 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.005"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 12,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "ProduitId"
                                    },
                                    {
                                        "name": "ProduitNom"
                                    },
                                    {
                                        "name": "RevueTitre"
                                    }
                                ]
                            },
                            "data": []
                        },
                        "text/html": "<table><tr><th>ProduitId</th><th>ProduitNom</th><th>RevueTitre</th></tr></table>"
                    }
                }
            ],
            "execution_count": 12
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Fonctions\r\n",
                "\r\n",
                "En SQL, nous pouvons créer différents types de fonctions. Nous en étudierons deux :\r\n",
                "- Les fonctions table\r\n",
                "- Les fonctions scalaires\r\n",
                "\r\n",
                "Les fonctions tables sont des fonctions qui vont nous revoyer des données structurées sous forme d'une table."
            ],
            "metadata": {
                "azdata_cell_guid": "5c68532a-5c4d-46af-9c93-29e811e650bf"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "CREATE OR ALTER FUNCTION [dbo].[GetRevues](@PrdId BIGINT)\r\n",
                "RETURNS TABLE\r\n",
                "AS\r\n",
                "RETURN\r\n",
                "(\r\n",
                "    SELECT [Titre], [Contenu]\r\n",
                "    FROM [dbo].[Revue_REV] (NOLOCK)\r\n",
                "    WHERE [PRD_Id] = @PrdId\r\n",
                ")\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "ff5b6071-8425-4e36-bf71-8c343b02144e"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.006"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 13
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT [Titre], [Contenu] FROM [dbo].[GetRevues](1)\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "e6e81e65-6c7a-4268-8614-456157eddfc0"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(0 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.004"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 14,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "Titre"
                                    },
                                    {
                                        "name": "Contenu"
                                    }
                                ]
                            },
                            "data": []
                        },
                        "text/html": "<table><tr><th>Titre</th><th>Contenu</th></tr></table>"
                    }
                }
            ],
            "execution_count": 14
        },
        {
            "cell_type": "markdown",
            "source": [
                "Nous avons également les fonctions scalaires, qui vont retourner une valeur. SQL Server en propose plusieurs en natif, voici quelques exemples :"
            ],
            "metadata": {
                "azdata_cell_guid": "32113083-9541-4739-93af-aeab817e307c"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT GETDATE() AS [Date], [Titre] FROM [Revue_REV]\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "d6708583-7537-497a-b303-e9b3cc031e5b"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(0 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.005"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 15,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "Date"
                                    },
                                    {
                                        "name": "Titre"
                                    }
                                ]
                            },
                            "data": []
                        },
                        "text/html": "<table><tr><th>Date</th><th>Titre</th></tr></table>"
                    }
                }
            ],
            "execution_count": 15
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT\r\n",
                "    COALESCE([Descriptif], [Contenu]) AS [Resultat],\r\n",
                "    [Descriptif],\r\n",
                "    [Contenu]\r\n",
                "FROM [dbo].[Produit_PRD]\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "e9b130b2-a30e-4e74-9ad0-1ebbd990cbcf"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.006"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 16,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "Resultat"
                                    },
                                    {
                                        "name": "Descriptif"
                                    },
                                    {
                                        "name": "Contenu"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "NULL",
                                    "1": "NULL",
                                    "2": "NULL"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>Resultat</th><th>Descriptif</th><th>Contenu</th></tr><tr><td>NULL</td><td>NULL</td><td>NULL</td></tr></table>"
                    }
                }
            ],
            "execution_count": 16
        },
        {
            "cell_type": "markdown",
            "source": [
                "On va également pouvoir définir nos propres fonctions"
            ],
            "metadata": {
                "azdata_cell_guid": "5b76620e-38a8-4088-b0f3-2a379ec3016f"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "CREATE OR ALTER FUNCTION [dbo].[CalculPromo](@Prix DECIMAL(5, 2))\r\n",
                "RETURNS DECIMAL(5, 2)\r\n",
                "AS\r\n",
                "BEGIN\r\n",
                "    DECLARE @Taux DECIMAL(5, 2);\r\n",
                "\r\n",
                "    IF (@Prix > 50)\r\n",
                "        SET @Taux = 0.65;\r\n",
                "    ELSE\r\n",
                "        SET @Taux = 0.75;\r\n",
                "\r\n",
                "    RETURN(@Prix * @Taux)\r\n",
                "END\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "452d299d-1932-47af-9110-17fcc38621ab"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.006"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 17
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT [Prix], [dbo].[CalculPromo]([Prix]) AS [Promo] FROM [Produit_PRD]\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "e7e0a445-2fe1-4f41-8e41-ce5ee6b496dd"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.012"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 18,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "Prix"
                                    },
                                    {
                                        "name": "Promo"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "0,00",
                                    "1": "0,00"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>Prix</th><th>Promo</th></tr><tr><td>0,00</td><td>0,00</td></tr></table>"
                    }
                }
            ],
            "execution_count": 18
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Triggers et curseurs\r\n",
                "\r\n",
                "Avant d'entrer dans le vif du sujet, nous allons modifier notre table produits pour y ajouter deux colonnes :\r\n",
                "- Une pour la date d'ajout de produit\r\n",
                "- Une pour la date de mise à jour du produit"
            ],
            "metadata": {
                "azdata_cell_guid": "2b891758-2ee6-4cb2-a4a8-40e70ad29a09"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "IF NOT EXISTS (\r\n",
                "    SELECT [name] \r\n",
                "    FROM   [sys].[columns]\r\n",
                "    WHERE  [object_id] = OBJECT_ID(N'[dbo].[Produit_PRD]') \r\n",
                "            AND [name] = 'Ajout'\r\n",
                ")\r\n",
                "    ALTER TABLE [dbo].[Produit_PRD]\r\n",
                "    ADD [Ajout] DATETIME2 NOT NULL,\r\n",
                "        [Modification] DATETIME2 NULL,\r\n",
                "        DEFAULT GETDATE() FOR [Ajout]\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "4d237e63-63b4-45c8-b648-741c413557c3"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.008"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 19
        },
        {
            "cell_type": "markdown",
            "source": [
                "Notre table de produit comporte désormais deux champs supplémentaire, une date avec une valeur par défaut (date d'ajout) et une date sans valeur par défaut (date de dernière modification).\r\n",
                "\r\n",
                "Si j'ai envie de maintenir à jour la date de dernière modification de ma table sans devoir veiller à mettre la valeur correctement à chaque fois, je peux définir un trigger lors de l'update de ma (ou mes) ligne(s).\r\n",
                "\r\n",
                "Pour ce faire, nous allons également utiliser un curseur pour itérer sur la (ou les) valeur(s) modifiée(s)."
            ],
            "metadata": {
                "azdata_cell_guid": "4bdfa4c0-fed0-40e3-a753-ed4719dc3301"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "CREATE OR ALTER TRIGGER [dbo].[trig_UpdatePrdModification]\r\n",
                "   ON  [dbo].[Produit_PRD] \r\n",
                "   AFTER UPDATE\r\n",
                "AS \r\n",
                "BEGIN\r\n",
                "\tSET NOCOUNT ON;\r\n",
                "\r\n",
                "    DECLARE @num INT\r\n",
                "\tDECLARE @PrdId INT\r\n",
                "\r\n",
                "\tSELECT @num = COUNT(*) from INSERTED\r\n",
                "\r\n",
                "\tIF @num > 1\r\n",
                "\tBEGIN\r\n",
                "\t\tDECLARE curs CURSOR FOR SELECT [Id] from INSERTED\r\n",
                "\t\tOPEN curs\t\t\r\n",
                "\t\tFETCH NEXT FROM curs into @PrdId; \r\n",
                "\t\tWHILE (@@FETCH_STATUS = 0)\r\n",
                "\t\tBEGIN\r\n",
                "\t\t\tUPDATE\t[dbo].[Produit_PRD]\r\n",
                "\t\t\tSET\t\t[Modification] = GETDATE()\r\n",
                "\t\t\tWHERE\t[Id] = @PrdId\r\n",
                "\r\n",
                "\t\t\tFETCH NEXT FROM curs into @PrdId; \r\n",
                "\t\tEND\r\n",
                "\r\n",
                "\t\tCLOSE curs\r\n",
                "\t\tDEALLOCATE curs\r\n",
                "\tEND\r\n",
                "\tELSE\r\n",
                "\tBEGIN\r\n",
                "\t\tSELECT TOP 1 @PrdId = [Id] from INSERTED\r\n",
                "\r\n",
                "\t\tUPDATE  [dbo].[Produit_PRD]\r\n",
                "        SET\t\t[Modification] = GETDATE()\r\n",
                "        WHERE\t[Id] = @PrdId\r\n",
                "\tEND\r\n",
                "END"
            ],
            "metadata": {
                "azdata_cell_guid": "ae4151b9-fbc8-4194-a626-95713ee2d7fc"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.014"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 20
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Procédures stockées\r\n",
                "\r\n",
                "Les procédures stockées vont nous permettre de faire des suites d'actions sur notre base, avec un faible coût en temps.\r\n",
                "\r\n",
                "Afin d'illustrer un cas d'utilisation, nous allons créer deux tables, une pour nos commandes, et une pour les produits qu'elles contiennent."
            ],
            "metadata": {
                "azdata_cell_guid": "97405ced-2e97-46ba-b836-b5d12ede4225"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "IF NOT EXISTS (\r\n",
                "    SELECT  [name]\r\n",
                "    FROM    [sysobjects]\r\n",
                "    WHERE   [name]='Commande_CMD'\r\n",
                "            AND [xtype]='U')\r\n",
                "    CREATE TABLE [dbo].[Commande_CMD] (\r\n",
                "        [Id] UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),\r\n",
                "        [Date] DATETIME NOT NULL DEFAULT GETDATE()\r\n",
                "    )\r\n",
                "    GO"
            ],
            "metadata": {
                "azdata_cell_guid": "fcbd0620-e960-4271-977c-7ee49278d229"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.018"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 21
        },
        {
            "cell_type": "code",
            "source": [
                "IF NOT EXISTS (\r\n",
                "    SELECT  [name]\r\n",
                "    FROM    [sysobjects]\r\n",
                "    WHERE   [name]='ProduitDeCommande_PDC'\r\n",
                "            AND [xtype]='U')\r\n",
                "    CREATE TABLE [dbo].[ProduitDeCommande_PDC] (\r\n",
                "        [Id] UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),\r\n",
                "        [CMD_Id] UNIQUEIDENTIFIER,\r\n",
                "        [Nom] NVARCHAR(255) NOT NULL,\r\n",
                "        [Prix] DECIMAL(5, 2) NOT NULL,\r\n",
                "        [Descriptif] NVARCHAR(MAX),\r\n",
                "        [Quantite] SMALLINT NOT NULL \r\n",
                "        CONSTRAINT fk_CMD_Id_PDC\r\n",
                "            FOREIGN KEY ([CMD_Id]) REFERENCES [Commande_CMD]([Id])\r\n",
                "    )\r\n",
                "    GO"
            ],
            "metadata": {
                "azdata_cell_guid": "59d4cf07-8445-426c-abd7-fbd26e7cfc4f"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.007"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 22
        },
        {
            "cell_type": "markdown",
            "source": [
                "Nous voulons faire la création de nos commande d'une traite. Dans un premier temps, nous allons faire des commandes avec un seul produit.\r\n",
                "\r\n",
                "Notre procédure va ainsi créer notre nouvelle ligne de commande, puis attacher notre produit à cette commande grâce à l'ID de la commande, et ce, sans repasser par un client (.NET, Java, etc), entièrement côté SQL."
            ],
            "metadata": {
                "azdata_cell_guid": "bd433d40-70ee-48c1-bddd-de17362589e2"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "CREATE OR ALTER PROCEDURE [dbo].[prc_CreationCommande]\r\n",
                "    @IdProduit BIGINT\r\n",
                "AS\r\n",
                "BEGIN\r\n",
                "    SET NOCOUNT ON;\r\n",
                "\r\n",
                "    DECLARE @CmdId UNIQUEIDENTIFIER = NEWID();\r\n",
                "\r\n",
                "    INSERT INTO [dbo].[Commande_CMD] ([Id])\r\n",
                "    VALUES (@CmdId)\r\n",
                "\r\n",
                "    INSERT INTO [dbo].[ProduitDeCommande_PDC]\r\n",
                "    ([CMD_Id], [Nom], [Prix], [Descriptif], [Quantite])\r\n",
                "    SELECT @CmdId, [Nom], [Prix], [Descriptif], 1\r\n",
                "    FROM [dbo].[Produit_PRD] AS [PRD] (NOLOCK)\r\n",
                "    WHERE [PRD].[Id] = @IdProduit\r\n",
                "END;"
            ],
            "metadata": {
                "azdata_cell_guid": "9b1fca2c-df80-4d9d-8e96-7810d5f5f59e",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.008"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 23
        },
        {
            "cell_type": "markdown",
            "source": [
                "On peut appeler les procédures à l'aide du mot-clé `EXEC` directement dans une fenêtre de requête, ou dans une autre procédure.\r\n",
                "\r\n",
                "```sql\r\n",
                "EXEC [dbo].[prc_CreationCommande] @IdProduit = 1\r\n",
                "```"
            ],
            "metadata": {
                "azdata_cell_guid": "06a87b9a-af79-415e-a17f-d12541ee9abd"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT * FROM [ProduitDeCommande_PDC]"
            ],
            "metadata": {
                "azdata_cell_guid": "5e6e82bb-ec29-4338-8d96-0731ce76317c"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(4 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.010"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 24,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "Id"
                                    },
                                    {
                                        "name": "CMD_Id"
                                    },
                                    {
                                        "name": "Nom"
                                    },
                                    {
                                        "name": "Prix"
                                    },
                                    {
                                        "name": "Descriptif"
                                    },
                                    {
                                        "name": "Quantite"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "7e83249d-4ff0-4535-96a9-2bf63b64a5b0",
                                    "1": "a216e8f3-af85-485f-88c5-c7f15ba8dc56",
                                    "2": "iPhone 11",
                                    "3": "0,00",
                                    "4": "NULL",
                                    "5": "2"
                                },
                                {
                                    "0": "2699d254-babc-42d6-8bb8-746278753f11",
                                    "1": "ef7c7e3b-8790-45bd-a21e-3a85945a63d8",
                                    "2": "iPhone 11",
                                    "3": "0,00",
                                    "4": "NULL",
                                    "5": "2"
                                },
                                {
                                    "0": "cead23b7-a2d9-4bfd-a56a-a383a3a91c6c",
                                    "1": "353234a8-1cc0-4105-8e35-bb0790e9bc2d",
                                    "2": "iPhone 11",
                                    "3": "0,00",
                                    "4": "NULL",
                                    "5": "1"
                                },
                                {
                                    "0": "71fe8b22-bec0-4f31-8952-d9a8f06ac2fe",
                                    "1": "38ca3649-53ea-43d8-903b-c41f7cf312a5",
                                    "2": "iPhone 11",
                                    "3": "0,00",
                                    "4": "NULL",
                                    "5": "22"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>Id</th><th>CMD_Id</th><th>Nom</th><th>Prix</th><th>Descriptif</th><th>Quantite</th></tr><tr><td>7e83249d-4ff0-4535-96a9-2bf63b64a5b0</td><td>a216e8f3-af85-485f-88c5-c7f15ba8dc56</td><td>iPhone 11</td><td>0,00</td><td>NULL</td><td>2</td></tr><tr><td>2699d254-babc-42d6-8bb8-746278753f11</td><td>ef7c7e3b-8790-45bd-a21e-3a85945a63d8</td><td>iPhone 11</td><td>0,00</td><td>NULL</td><td>2</td></tr><tr><td>cead23b7-a2d9-4bfd-a56a-a383a3a91c6c</td><td>353234a8-1cc0-4105-8e35-bb0790e9bc2d</td><td>iPhone 11</td><td>0,00</td><td>NULL</td><td>1</td></tr><tr><td>71fe8b22-bec0-4f31-8952-d9a8f06ac2fe</td><td>38ca3649-53ea-43d8-903b-c41f7cf312a5</td><td>iPhone 11</td><td>0,00</td><td>NULL</td><td>22</td></tr></table>"
                    }
                }
            ],
            "execution_count": 24
        },
        {
            "cell_type": "markdown",
            "source": [
                "Si on veut passer plusieurs paramètre du même type, nous aurons tendance à utiliser des chaînes de caractères avec des sépérateurs."
            ],
            "metadata": {
                "azdata_cell_guid": "8a986d4a-30ca-4c63-9594-8d989c6cca64"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "CREATE OR ALTER PROCEDURE [dbo].[prc_CreationCommande]\r\n",
                "    @IdsProduits NVARCHAR(MAX)\r\n",
                "AS\r\n",
                "BEGIN\r\n",
                "    SET NOCOUNT ON;\r\n",
                "\r\n",
                "    DECLARE @CmdId UNIQUEIDENTIFIER = NEWID();\r\n",
                "\r\n",
                "    INSERT INTO [dbo].[Commande_CMD] ([Id])\r\n",
                "    VALUES (@CmdId)\r\n",
                "\r\n",
                "    INSERT INTO [dbo].[ProduitDeCommande_PDC]\r\n",
                "    ([CMD_Id], [Nom], [Prix], [Descriptif], [Quantite])\r\n",
                "    SELECT @CmdId, [Nom], [Prix], [Descriptif], 1\r\n",
                "    FROM [dbo].[Produit_PRD] AS [PRD] (NOLOCK)\r\n",
                "    WHERE [PRD].[Id] IN (\r\n",
                "        SELECT CAST(value AS BIGINT)\r\n",
                "        FROM STRING_SPLIT(@IdsProduits, '|')\r\n",
                "    )\r\n",
                "END;"
            ],
            "metadata": {
                "azdata_cell_guid": "4f6651fb-7000-4483-a993-e2bd695b5d0e"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.006"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 25
        },
        {
            "cell_type": "markdown",
            "source": [
                "L'appel se ferait de la manière suivante :\r\n",
                "\r\n",
                "```sql\r\n",
                "EXEC [dbo].[prc_CreationCommande] @IdProduit = N'1|2|3|4'\r\n",
                "```\r\n",
                "\r\n",
                "__Attention !__\r\n",
                "\r\n",
                "La méthodologie avec `NEWID()` ne fonctionne qu'avec le type `UNIQUEIDENTIFIER`. Si vos clé primaires sont des entier (`INT`, `BIGINT`, ...), vous devrez faire de la façon suivante :\r\n",
                "\r\n",
                "```sql\r\n",
                "DECLARE @NewId BIGINT\r\n",
                "\r\n",
                "INSERT INTO [dbo].[MaTable] ([Champ1], [Champ2])\r\n",
                "VALUES ('val1', 'val2')\r\n",
                "\r\n",
                "SET @NewId = @@IDENTITY -- Contient la lé nouvellement générée\r\n",
                "```\r\n",
                "\r\n",
                "## WITH common_table_expression\r\n",
                "\r\n",
                "Afin de passer plusieurs paramètres de façon simple à notre procédure, nous allons séparés nos champs de le façon suivante :\r\n",
                "\r\n",
                "```\r\n",
                "Id;Quentite|Id;Quantite\r\n",
                "```\r\n",
                "\r\n",
                "Pour pouvoir manipuler plus simplement nos données, nous allons pouvoir utiliser une expression `WITH common_table_expression` (ou `WITH CTE` pour faire plus court).\r\n",
                "\r\n",
                "Ces epressions doivent forcément être précédées par un point-virgule (`;WITH ...`) afin de fonctionner. Elles expirent également immédiatement à la suite de la première instruction recontrée qui n'est pas une `WITH common_table_expression`."
            ],
            "metadata": {
                "azdata_cell_guid": "0ff6a5de-3abf-4366-a0f9-a855c392df6d"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "CREATE OR ALTER PROCEDURE [dbo].[prc_CreationCommande]\r\n",
                "    @InfosProduits NVARCHAR(MAX)\r\n",
                "AS\r\n",
                "BEGIN\r\n",
                "    SET NOCOUNT ON;\r\n",
                "     \r\n",
                "    DECLARE @CmdId UNIQUEIDENTIFIER = NEWID()\r\n",
                "\r\n",
                "    INSERT INTO [dbo].[Commande_CMD] ([Id])\r\n",
                "    VALUES (@CmdId)\r\n",
                "\r\n",
                "    ;WITH InfosProduits AS (\r\n",
                "        SELECT  CAST(PARSENAME(REPLACE([value],';','.'),2) AS BIGINT) as [Id],\r\n",
                "                CAST(PARSENAME(REPLACE([value],';','.'),1) AS SMALLINT) as [Quantite]\r\n",
                "        FROM STRING_SPLIT(@InfosProduits, '|')\r\n",
                "    )\r\n",
                "\r\n",
                "    INSERT INTO [dbo].[ProduitDeCommande_PDC]\r\n",
                "    ([CMD_Id], [Nom], [Prix], [Descriptif], [Quantite])\r\n",
                "    SELECT @CmdId, [Nom], [Prix], [Descriptif], [IFP].[Quantite]\r\n",
                "    FROM [dbo].[Produit_PRD] AS [PRD] (NOLOCK)\r\n",
                "    INNER JOIN InfosProduits AS [IFP] ON [IFP].[Id] = [PRD].[Id];\r\n",
                "END;"
            ],
            "metadata": {
                "azdata_cell_guid": "44ba5444-a5b2-462c-8425-2b9c652e9a03"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.008"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 26
        },
        {
            "cell_type": "markdown",
            "source": [
                "Nous pouvons maintenant utiliser notre procédure de la manière suivante :\r\n",
                "\r\n",
                "```sql\r\n",
                "EXEC [dbo].[prc_CreationCommande] @InfosProduits = N'1;2|2;5'\r\n",
                "```\r\n",
                "\r\n",
                "Nous aurions également pu utiliser une variable de type `TABLE`, comme dans l'exemple ci-dessous :"
            ],
            "metadata": {
                "azdata_cell_guid": "a9c6fd9d-2182-476d-af67-398584ddb884"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "CREATE OR ALTER PROCEDURE [dbo].[prc_CreationCommande]\r\n",
                "    @InfosProduits NVARCHAR(MAX)\r\n",
                "AS\r\n",
                "BEGIN\r\n",
                "    SET NOCOUNT ON;\r\n",
                "     \r\n",
                "    DECLARE @CmdId UNIQUEIDENTIFIER = NEWID()\r\n",
                "    DECLARE @Table AS TABLE (\r\n",
                "            [Id] BIGINT,\r\n",
                "            [Quantite] SMALLINT\r\n",
                "    )\r\n",
                "\r\n",
                "    INSERT INTO @Table([Id], [Quantite])\r\n",
                "    SELECT  CAST(PARSENAME(REPLACE([value],';','.'),2) AS BIGINT) as [Id],\r\n",
                "                CAST(PARSENAME(REPLACE([value],';','.'),1) AS SMALLINT) as [Quantite]\r\n",
                "        FROM STRING_SPLIT(@InfosProduits, '|')\r\n",
                "\r\n",
                "    INSERT INTO [dbo].[Commande_CMD] ([Id])\r\n",
                "    VALUES (@CmdId)\r\n",
                "\r\n",
                "    INSERT INTO [dbo].[ProduitDeCommande_PDC]\r\n",
                "    ([CMD_Id], [Nom], [Prix], [Descriptif], [Quantite])\r\n",
                "    SELECT @CmdId, [Nom], [Prix], [Descriptif], [IFP].[Quantite]\r\n",
                "    FROM [dbo].[Produit_PRD] AS [PRD] (NOLOCK)\r\n",
                "    INNER JOIN @Table AS [IFP] ON [IFP].[Id] = [PRD].[Id];\r\n",
                "END;"
            ],
            "metadata": {
                "azdata_cell_guid": "0959cd61-833e-4c0e-bee6-9627ac4b696e"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.010"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 27
        },
        {
            "cell_type": "markdown",
            "source": [
                "Ces dernières ont quelques propriétés particulières :\r\n",
                "\r\n",
                "- Elles sont créées en mémoire, contrairement aux tables temporaires\r\n",
                "- Elles ne peuvent pas être utilisées dans des transactions, mais sont plus rapide que des tables temporaires\r\n",
                "- Elles ne peuvent pas avoir d'index particuliers (seulement clé primaire et unique), contrairement aux tables temporaires\r\n",
                "- Elles peuvent servir de paramètres aux procédures et aux fonctions, contrairement aux tables temporaires\r\n",
                "- On ne peut pas modifier leur schéma, contrairement aux tables temporaires\r\n",
                "- Elles ne peuvent être utilisées qu'entre deux routines (`GO`), contrairement aux tables temporaires"
            ],
            "metadata": {
                "azdata_cell_guid": "b9e72db6-c6b7-41fd-b891-35bed138df7e"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Transactions"
            ],
            "metadata": {
                "azdata_cell_guid": "87c84a7f-7d03-4ebb-84b7-810d996d5578"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "CREATE OR ALTER PROCEDURE [dbo].[prc_CreationCommande]\r\n",
                "    @InfosProduits NVARCHAR(MAX)\r\n",
                "AS\r\n",
                "BEGIN\r\n",
                "    SET NOCOUNT ON;\r\n",
                "     \r\n",
                "    BEGIN TRANSACTION TRANSAC_CREATION_COMMANDE;\r\n",
                "    BEGIN TRY\r\n",
                "        DECLARE @CmdId UNIQUEIDENTIFIER = NEWID()\r\n",
                "\r\n",
                "        INSERT INTO [dbo].[Commande_CMD] ([Id])\r\n",
                "        VALUES (@CmdId)\r\n",
                "\r\n",
                "        ;WITH InfosProduits AS (\r\n",
                "            SELECT  CAST(PARSENAME(REPLACE([value],';','.'),2) AS BIGINT) as [Id],\r\n",
                "                    CAST(PARSENAME(REPLACE([value],';','.'),1) AS SMALLINT) as [Quantite]\r\n",
                "            FROM STRING_SPLIT(@InfosProduits, '|')\r\n",
                "        )\r\n",
                "\r\n",
                "        INSERT INTO [dbo].[ProduitDeCommande_PDC]\r\n",
                "        ([CMD_Id], [Nom], [Prix], [Descriptif], [Quantite])\r\n",
                "        SELECT @CmdId, [Nom], [Prix], [Descriptif], [IFP].[Quantite]\r\n",
                "        FROM [dbo].[Produit_PRD] AS [PRD] (NOLOCK)\r\n",
                "        INNER JOIN InfosProduits AS [IFP] ON [IFP].[Id] = [PRD].[Id];\r\n",
                "\r\n",
                "        COMMIT TRANSACTION TRANSAC_CREATION_COMMANDE;\r\n",
                "\r\n",
                "        SELECT 0 AS ErrorNumber, N'' AS ErrorMessage;\r\n",
                "    END TRY\r\n",
                "    BEGIN CATCH\r\n",
                "        ROLLBACK TRANSACTION TRANSAC_CREATION_COMMANDE;\r\n",
                "        SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;\r\n",
                "    END CATCH\r\n",
                "END;"
            ],
            "metadata": {
                "azdata_cell_guid": "dfe5b756-95b9-409b-a509-31440f08d9f6"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.009"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 35
        },
        {
            "cell_type": "code",
            "source": [
                "DECLARE @Table TABLE (\r\n",
                "    [ErrorNumber] INT,\r\n",
                "    [ErrorMessage] NVARCHAR(MAX)\r\n",
                ")\r\n",
                "\r\n",
                "INSERT INTO @Table\r\n",
                "EXEC [dbo].[prc_CreationCommande] @InfosProduits = N'1;2|2;5'\r\n",
                "\r\n",
                "SELECT * FROM @Table"
            ],
            "metadata": {
                "azdata_cell_guid": "6ae038ed-d47e-4f68-8b17-b6716f839d45"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.017"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {},
                    "execution_count": 36,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "ErrorNumber"
                                    },
                                    {
                                        "name": "ErrorMessage"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "0",
                                    "1": ""
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>ErrorNumber</th><th>ErrorMessage</th></tr><tr><td>0</td><td></td></tr></table>"
                    }
                }
            ],
            "execution_count": 36
        }
    ]
}