{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Procedimientos PL/PgSQL: Introducción\n",
    "\n",
    "## Bloques en Postgres\n",
    "```\n",
    "do $$ \n",
    "<<mi_bloque>>\n",
    "declare\n",
    "  film_count integer := 0;\n",
    "begin\n",
    "   -- Obtener el número de películas\n",
    "   select count(*) \n",
    "   into film_count\n",
    "   from film;\n",
    "   -- Mostrar un Mensaje\n",
    "   raise notice 'El número de películas es: %', film_count;\n",
    "end mi_bloque $$;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Condicionales\n",
    "\n",
    "Los condicionales permiten ejecutar instrucciones en función del resultado de una expresión booleana.\n",
    "\n",
    "* found es una variable global que está disponible una vez ejecutado un select.\n",
    "\n",
    "```\n",
    "do $$\n",
    "declare\n",
    "  selected_film film%rowtype;\n",
    "  input_film_id film.film_id%type := 93;\n",
    "begin  \n",
    "\n",
    "  select * from film\n",
    "  into selected_film\n",
    "  where film_id = input_film_id;\n",
    "  \n",
    "  if not found then\n",
    "     raise notice 'La película % no ha sido encontrada', input_film_id;\n",
    "  else\n",
    "     raise notice 'El título de la película es %', selected_film.title;\n",
    "  end if;\n",
    "end $$\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Declaraciones if-then-elsif\n",
    "\n",
    "Las declaraciones if y if-else evalúan una condición. Sin embargo, la if-then-elsif declaración evalúa múltiples condiciones.\n",
    "\n",
    "\n",
    "```\n",
    "do $$\n",
    "declare\n",
    "   v_film film%rowtype;\n",
    "   len_description varchar(92);\n",
    "begin  \n",
    "\n",
    "  select * from film\n",
    "  into v_film\n",
    "  where film_id = 100;\n",
    "  \n",
    "  if not found then\n",
    "     raise notice 'Película no encontrada';\n",
    "  else\n",
    "      if v_film.length >0 and v_film.length <= 50 then\n",
    "\t\t len_description := 'Corta';\n",
    "\t  elsif v_film.length > 50 and v_film.length < 120 then\n",
    "\t\t len_description := 'Normal';\n",
    "\t  elsif v_film.length > 120 then\n",
    "\t\t len_description := 'larga';\n",
    "\t  else \n",
    "\t\t len_description := 'No Disponible';\n",
    "\t  end if;\n",
    "    \n",
    "\t  raise notice 'La película % es %.',\n",
    "\t     v_film.title,  \n",
    "\t     len_description;\n",
    "  end if;\n",
    "end $$\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Declaraciones CASE\n",
    "\n",
    "Además de la declaración if, se proporciona declaraciones CASE que le permiten ejecutar un bloque de código basado en una condición.\n",
    "\n",
    "### Declaración de CASE simple\n",
    "\n",
    "```\n",
    "do $$\n",
    "declare \n",
    "\trate   film.rental_rate%type;\n",
    "\tprice_segment varchar(50);\n",
    "begin\n",
    "    -- obtener la tasa de alquiler\n",
    "    select rental_rate into rate \n",
    "    from film \n",
    "    where film_id = 100;\n",
    "\t\n",
    "\t-- asignar la clasificacion del segmento\n",
    "\tif found then\n",
    "\t\tcase rate\n",
    "\t\t   when 0.99 then\n",
    "              price_segment = 'Econo';\n",
    "\t\t   when 2.99 then\n",
    "              price_segment = 'Regular';\n",
    "\t\t   when 4.99 then\n",
    "              price_segment = 'VIP';\n",
    "\t\t   else\n",
    "\t    \t  price_segment = 'Sin Especificar';\n",
    "\t\t   end case;\n",
    "\t\traise notice 'El segmento de la pelicula es: %', price_segment;\n",
    "    end if;\n",
    "end; $$\n",
    "```\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}