011_PROCEDURES.sql

-- PROCEDURES IN SQL
  -- PROCEDURES ARE INTENDED TO PERFROM SOM OPERATIONS IN THE SQL, BUT THEY DON'T RETURN A VALUE
  -- THESE ARE LIKE VOID FUNCTIONS
  -- PROCEDURES CAN'T BE SUED IN SQL QUERIES DIRECTLY
  -- THEY MUST BE CALLED IN ANOTHER PL/SQL BLOCK OR MUST BE CALLED WITH EXEC COMMAND
  -- PROCEDURES TAKES 2 TYPES OF PARAMETERS 
  -- 1. IN -> PARAMETERS (INPUTS)
  -- 2. OUT -> PARAMETERS (OUTPUTS)
  -- YOU CAN USE OUT PARAMETERS TO STORE THE RESULTS

-- PRINTING NUMBERS FROM 1 TO A USING PROCEDURES
CREATE OR REPLACE PROCEDURE first_procedure(a IN NUMBER)
IS
BEGIN
    FOR i IN 1..a LOOP
        DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END;
/

-- WAY :- 1
EXEC first_procedure(10);

-- WAY :- 2
-- ANOTHER PL/SQL BLOCK
BEGIN
    first_procedure(100);
END;
/

-- STORING THE RESULT IN OUTPUT VARIABLE (LIKE POINTERS IN OUR PROGRAMMING)
CREATE OR REPLACE PROCEDURE xyz(a IN NUMBER, b OUT NUMBER)
IS
BEGIN
    b := a * a;
END;
/
  
DECLARE
    input NUMBER;
    output NUMBER;
BEGIN
    input := 10;
    xyz(input, output);
    DBMS_OUTPUT.PUT_LINE(output);
END;
/

-- IN PROCEDURES YOU CAN EXECUTE DML STATEMENTS
SELECT * FROM employee;

-- EXAMPLE
-- STORE THE QUERIES IN THE FORM OF PROCEDURES,
-- UPDATE THE SALARY OF EMPLOYEE

CREATE OR REPLACE PROCEDURE 
updSalary(emp_id IN NUMBER, new_salary IN NUMBER)
IS
BEGIN
    UPDATE employee SET salary = new_salary
    WHERE id = emp_id;
END;
/

SELECT * from EMPLOYEE;
EXEC updSalary(113, 65000);
EXEC updSalary(114, 50000);


-- DISPLAY THE HIGHEST AND LOWEST SALARIES OF PARTICULAR DEPARTMENT EMPLOYEES
-- HR higest: 80000
-- HR lowest: 35000
CREATE OR REPLACE PROCEDURE 
getMinMaxSalaries(dept_name IN VARCHAR2)
IS
    min_salary NUMBER;
    max_salary NUMBER;
BEGIN
    -- SELECT INTO
    SELECT MIN(salary), MAX(salary) INTO
    min_salary, max_salary
    FROM employee WHERE department = dept_name;
    DBMS_OUTPUT.PUT_LINE('Highest ' || dept_name || ': ' || max_salary);
    DBMS_OUTPUT.PUT_LINE('Lowest ' || dept_name || ': ' || min_salary);
END;
/
EXEC getMinMaxSalaries('HR');
EXEC getMinMaxSalaries('Testing');