Procedimientos Almacenados en SQL: Gestión de Variables y Manejo de Excepciones

Yo, creo que desde la clase 28 hasta la presente ando perdido y no muy claro con lo que enseña esta profesora. en la mayoría de clases le hace falta profundizar más con ejemplos de lo que nos trata de eseñar. Lo que hace es que hace una explicación general y básica y nos pone a investigar este sancocho. Creo que algunos que empezaron el curso ya desertaron y eso que no llegaron hasta las clases 27 en adelante y los pocos estudiantes que todavían siguen es porque ya han tenido nociones de este curso.

Un montón de conceptos avanzados metidos en una sola clase, sin ejemplos, con apenas una explicación superficial. Sin recursos. Por favor revisen y mejoren este curso.

```js DELIMITER // CREATE PROCEDURE AddEmployeeAndUpdateAverageSalary( IN p_FirstName VARCHAR(50), IN p_LastName VARCHAR(50), IN p_DepartmentID INT, IN p_Salary DECIMAL(10,2), IN p_HireDate DATE, AddEmployeeWithBonues OUT p_NewAverageSalary DECIMAL(10,2) ) BEGIN DECLARE v_AvgSalary DECIMAL(10,2); DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN -- Mensaje de la excepción: Mostrar un mensaje de error SELECT 'Error: No se pudo completar la operación.' AS ErrorMessage; ROLLBACK; --Deshacer todos los cambios realizados durante la transacción END; START TRANSACTION; --Inicia una transacción, todas las operaciones siguientes se consideran una transacción -- Insertar el nuevo empleado INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary, HireDate) VALUES (p_FirstName, p_LastName, p_DepartmentID, p_Salary, p_HireDate); -- Calcular el nuevo salario promedio del departamento SELECT AVG(Salary) INTO v_AvgSalary FROM Employees WHERE DepartmentID = p_DepartmentID; --Asignar el salario promedio calculado a la variable de salida SET p_NewAverageSalary = v_AvgSalary; COMMIT; --Confirma la transacción actual gurda todos los cambios realizados END // DELIMITER; ```

Siempre se aprende algo nuevo, no sabia que SQL tenia este apartado

Nooo ya me perdí, no entendí esta cantidad de cosas y ni nos mostraron el resultado :(

es parecido al manejo de errores en python

Para los que sigan el curso desde SQL server la sintaxis cambia un poco, aqui un ejemplo con las tablas de ahce unas clases de productos: ```js create procedure insertProductAndAveragePriceProducts @PRODUCTNAME VARCHAR (100), @DESCRIPTION VARCHAR(MAX), @BRANDID INT, @PRICE FLOAT, @AVERAGEPRICE FLOAT OUTPUT AS BEGIN DECLARE @TOTALAVGPRICE FLOAT; BEGIN TRY -- Iniciar transacción BEGIN TRANSACTION; -- Insertar el nuevo producto INSERT INTO TPRODUCTS (PRODUCTNAME, DESCRIPTION,BRANDID,PRICE) VALUES (@PRODUCTNAME, @DESCRIPTION, @BRANDID, @PRICE); -- Calcular el precio promedio SELECT @TOTALAVGPRICE = AVG(PRICE) FROM TPRODUCTS; -- Asignar el precio promedio al parámetro de salida SET @AVERAGEPRICE = @TOTALAVGPRICE; -- Confirmar transacción COMMIT; END TRY BEGIN CATCH -- Manejar el error SELECT 'Error: No se puedo completar la operacion' AS ERRORMESSAGE; -- Revertir transacción ROLLBACK; END CATCH; END ```Importante recalcar algunas cosas: 1\. en SQL server a diferencia de MySql no es necesario DELIMETER. 2\. los parametros todos se inicializan con @ no con In o out, para saber si un parámetro es de salido lo hacemos con OUTPUT después de escribir el parámetro. 3\. las variables además de escribir DECLARE igual hay que poner @antes del nombre de la variable. 4\. el manejo de excepciones cambia, si alguno esta familiarizado con Javascript, Java, c#, etc, es la misma lógica que se usa para un Try/ Catch, para los que no, es fácil de entender básicamente tenemos la sección try que es donde estarán todas las acciones en caso que todo ande bien, y posteriormente va el catch que serian las acciones que pasaran si algo llega a fallar. Es decir que en ese caso el declare exit handler for sqlexception se usara en MySql, para SQL Server se usaran Begin try End try y Begin Catch End Catch.

```js CREATE OR REPLACE FUNCTION AddEmployeeAndUpdateAverageSalary( p_FirstName VARCHAR(50), p_LastName VARCHAR(50), p_DepartmentID INT, p_Salary NUMERIC(10,2), p_HireDate DATE ) RETURNS NUMERIC(10,2) LANGUAGE plpgsql AS $$ DECLARE v_AvgSalary NUMERIC(10,2); BEGIN -- Iniciar la transacción BEGIN -- Insertar el nuevo empleado INSERT INTO employees (first_name, last_name, department_id, salary, hire_date) VALUES (p_FirstName, p_LastName, p_DepartmentID, p_Salary, p_HireDate); -- Calcular el nuevo salario promedio del departamento SELECT AVG(salary) INTO v_AvgSalary FROM employees WHERE department_id = p_DepartmentID; -- Confirmar la transacción COMMIT; -- Retornar el salario promedio calculado RETURN v_AvgSalary; EXCEPTION WHEN OTHERS THEN -- Mensaje de la excepción: Mostrar un mensaje de error RAISE NOTICE 'Error: No se pudo completar la operación.'; -- Deshacer todos los cambios realizados durante la transacción ROLLBACK; RETURN NULL; END; END; $$; -- Llamar a la función SELECT AddEmployeeAndUpdateAverageSalary( 'Juan', 'Lopez', 3, 14000, '2024-03-01' ) AS NewAverageSalary; ``` ```js Data output NOTICE: Error: No se pudo completar la operación. ERROR: terminación de transacción no válida CONTEXT: función PL/pgSQL addemployeeandupdateaveragesalary(character varying,character varying,integer,numeric,date) en la línea 27 en ROLLBACK SQL state: 2D000 ```

Qué interesante no sabia como usar el ROLLBACK

y el uso XD, considero que no solo es mostrar la sintaxis sino también usarlo, ver un ejemplo practico, ver la solución de la explicación de ese procedimiento almacenado.

Para poder insertar y ver el nuevo promedio se hace con CALL AddEmployeeAndUpdateAverageSalary('David', 'Alvarez', 'IT', 5000, '2024-08-24', @new\_avg\_salary); SELECT @new\_avg\_salary AS new\_avg\_salary;

Estaría super que en la sección de recursos estuviera el código para comparar.

/\* Build a procedure that after inserting an employee, shows how many employees \-- each department has \*/ DELIMITER // CREATE PROCEDURE AddEmployeeAndUpdateCountEmployeeXDep( IN p\_FirstName VARCHAR(50), IN p\_LastName VARCHAR(50), IN p\_DepartmentID INT, IN p\_Salary DECIMAL(10,2), OUT p\_CountEmployeeXDep INT, OUT p\_DepartmentName VARCHAR(100) ) BEGIN DECLARE v\_CountEmpXDep INT; DECLARE v\_nameDep VARCHAR(100); DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SELECT 'Error: No se pudo completar la operación.' AS ErrorMessage; ROLLBACK; END; START TRANSACTION; -- Inicia una transaccion INSERT INTO employee(First\_name, department\_id, salary) VALUES (CONCAT(p\_FirstName, ' ', p\_LastName), p\_DepartmentID, p\_Salary); \-- Caluclar el numero de empleados x Dep SELECT Count(\*) INTO v\_CountEmpXDep FROM employee WHERE department\_id = p\_DepartmentID GROUP BY department\_id; \-- get name of department SELECT department\_name INTO v\_nameDep FROM department where department\_id = p\_DepartmentID; SET p\_CountEmployeeXDep = v\_CountEmpXDep; SET p\_DepartmentName = v\_nameDep; COMMIT; END // DELIMITER ;

Caro 👩‍💻 no tenia idea sobre el mensaje de error

Un procedimiento almacenado en Oracle se puede crear utilizando la siguiente sintaxis básica: ```sql CREATE OR REPLACE PROCEDURE nombre_procedimiento ( parametro1 IN tipo_dato, parametro2 OUT tipo_dato ) AS BEGIN -- Lógica del procedimiento SELECT columna INTO parametro2 FROM tabla WHERE columna = parametro1; EXCEPTION WHEN NO_DATA_FOUND THEN -- Manejo de excepciones DBMS_OUTPUT.PUT_LINE('No se encontró información.'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM); END nombre_procedimiento; ``` Este ejemplo define un procedimiento que recibe un parámetro de entrada y devuelve un parámetro de salida. Asegúrate de adaptar la lógica y los tipos de datos específicos a tus necesidades.

Esta clase si esta muy complicada jajaja

Confieso que en este punto no sé si valga la pena continuar con el curso ya que no soy experto con SQL y es la primera vez que lo estudio. Hay demasiados temas complejos que solo explican superficialmente, no muestran ninguna teoría, no hay recursos. Por favor revisen bien este curso porque falta muchisimo por mejorar, agregar y tener en cuenta a que publico va dirigido.

Siendo sincero, la clase esta buena pero el problema es que hay puntos importantes que omiten. Bueno en caso de las personas que sabemos sql esta bien porque eso nos ayuda a investigar y mejorar pero a aquellos que recien empiezan. Que mal, esto es un poco avanzado para ellos y eso que se ma o me sql. Aqui les comparto el codigo para las personas que usan la BD de las primeras clases. ```js DELIMITER // CREATE PROCEDURE SP_ADD_INSTRUCTOR_AND_UPDATE_AVERAGE_SALARY( IN I_INSTRUCTOR_NAME VARCHAR(50), IN I_INSTRUCTOR_LAST_NAME VARCHAR(50), IN I_EMAIL VARCHAR(50), IN I_SALARY DECIMAL (10, 2), OUT I_NEW_AVERAGE_SALARY DECIMAL(10, 2) ) BEGIN DECLARE V_AVG_SALARY DECIMAL(10, 2); DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN -- MANEJO DE LA EXCEPCION: MOSTRAR UN MENSAJE DE ERROR SELECT 'ERROR: NO SE PUDO COMPLETAR LA OPERACION.' AS ERROR_MESSAGE; ROLLBACK; -- DESHACER TODOS LOS CAMBIOS REALIZADOS DURANTE LA TRANSACCION END; START TRANSACTION; -- INICIA UNA TRANSACCION, TODAS LAS OPERACIONES SE CONSIDERAN EN UNA TRANSACCION -- INSERTAR EL NUEVO INSTRUCTOR INSERT INTO INSTRUCTOR (FIRSTNAME, LASTNAME, EMAIL, SALARY) VALUES (I_INSTRUCTOR_NAME, I_INSTRUCTOR_LAST_NAME, I_EMAIL, I_SALARY); -- CALCULAR EL NUEVO SALARIO PROMEDIO DEL INSTRUCTOR SELECT AVG(SALARY) INTO V_AVG_SALARY FROM INSTRUCTOR; -- ASIGNAR EL SALARIO PROMEDIO CALCULADO A LA VARIABLE SALIDA SET I_NEW_AVERAGE_SALARY = V_AVG_SALARY; COMMIT; -- CONFIRMA LA TRANSACCION ACTUAL, ES DECIR GUARDA TODOS LOS CAMBIOS END // DELIMITER ; SET @NEW_AVERAGE_SALARY = 0; CALL SP_ADD_INSTRUCTOR_AND_UPDATE_AVERAGE_SALARY('Morgan','Stark', '[email protected]', 75930.00, @NEW_AVERAGE_SALARY); SELECT @NEW_AVERAGE_SALARY; ```DELIMITER // CREATE PROCEDURE SP\_ADD\_INSTRUCTOR\_AND\_UPDATE\_AVERAGE\_SALARY( IN I\_INSTRUCTOR\_NAME VARCHAR(50), IN I\_INSTRUCTOR\_LAST\_NAME VARCHAR(50), IN I\_EMAIL VARCHAR(50), IN I\_SALARY DECIMAL (10, 2), OUT I\_NEW\_AVERAGE\_SALARY DECIMAL(10, 2) ) BEGIN DECLARE V\_AVG\_SALARY DECIMAL(10, 2); DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN \-- MANEJO DE LA EXCEPCION: MOSTRAR UN MENSAJE DE ERROR SELECT 'ERROR: NO SE PUDO COMPLETAR LA OPERACION.' AS ERROR\_MESSAGE; ROLLBACK; -- DESHACER TODOS LOS CAMBIOS REALIZADOS DURANTE LA TRANSACCION END; START TRANSACTION; -- INICIA UNA TRANSACCION, TODAS LAS OPERACIONES SE CONSIDERAN EN UNA TRANSACCION \-- INSERTAR EL NUEVO INSTRUCTOR INSERT INTO INSTRUCTOR (FIRSTNAME, LASTNAME, EMAIL, SALARY) VALUES (I\_INSTRUCTOR\_NAME, I\_INSTRUCTOR\_LAST\_NAME, I\_EMAIL, I\_SALARY); \-- CALCULAR EL NUEVO SALARIO PROMEDIO DEL INSTRUCTOR SELECT AVG(SALARY) INTO V\_AVG\_SALARY FROM INSTRUCTOR; \-- ASIGNAR EL SALARIO PROMEDIO CALCULADO A LA VARIABLE SALIDA SET I\_NEW\_AVERAGE\_SALARY = V\_AVG\_SALARY; COMMIT; -- CONFIRMA LA TRANSACCION ACTUAL, ES DECIR GUARDA TODOS LOS CAMBIOS END // DELIMITER ; SET @NEW\_AVERAGE\_SALARY = 0; CALL SP\_ADD\_INSTRUCTOR\_AND\_UPDATE\_AVERAGE\_SALARY('Morgan','Stark', 'tony\[email protected]', 75930.00, @NEW\_AVERAGE\_SALARY); SELECT @NEW\_AVERAGE\_SALARY;

Que clase tan enredada :(

Lo de Begin, Declare y compañía no es de PLSQL? ya que en mi trabajo usamos eso

✅

CREATE PROCEDURE GetAllEmployees AS BEGIN SELECT EmployeeID, FirstName, LastName, BirthDate, Position, Salary FROM Employees; END;

```js CREATE PROCEDURE Edad_Promedio ( IN FIRSTNAME VARCHAR(50), IN LASTNAME VARCHAR(50), IN AGE INT, IN EMAIL VARCHAR(100), OUT NEW_AVG_AGE DECIMAL(5,2) -- Cambiamos el tipo de dato a DECIMAL para mayor precisión ) BEGIN DECLARE AVG_AGE DECIMAL(5,2); -- Cambiamos el tipo de dato a DECIMAL para mayor precisión DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SELECT 'Error: No se pudo completar la operación' AS ErrorMessage; ROLLBACK; END; START TRANSACTION; INSERT INTO STUDENTS (FIRSTNAME, LASTNAME, AGE, EMAIL) VALUES (FIRSTNAME, LASTNAME, AGE, EMAIL); SELECT AVG(AGE) INTO AVG_AGE FROM STUDENTS; SET NEW_AVG_AGE = AVG_AGE; COMMIT; END; ```