I am new in creating procedures in MySql and still learning it. So, I am stuck in creating procedures and need help. The question might seem basic but it will help me learn from basics.
I want to get the details of salary for each pilot when I write his pilot_nbr.
pilot_nbr, license_nbr, last_name, first_name, title, address, phone, office_nbr, contract_type, salary, manager_nbr
'701', '7111', 'Dark', 'Jack', '1st officer', '6 street',
'6042233445', '789', 'PTE', '145000.00', NULL '702', '7222', 'Mack',
'Bill', '1st officer', '7 street', '6043344556', '890', 'EMP',
'703', '7333', 'Cheung', 'Charles', '2nd officer', '8street','6044455667','503','PTE','140000.00','701'
704', '7444', 'Gordon', 'Greg', '1st officer', '9 street',
'6045566778', '123', 'EMP', '125000.00', '701' '705', '7555', 'Basso',
'Nicki', '2nd officer', '5 street', '6046677889', '223', 'EMP',
'163000.00', '701' '706', '7666', 'Vettel', 'Sebast', '1st officer',
'5 street', '6046677800', '523', 'EMP', '199000.00','701' '707',
CREATE PROCEDURE pilot_Salary_Procedure( IN pilot_Number INT(20), OUT pilot_Salary DECIMAL(10,2))
DECLARE pilot_NummberVariable INT(20);
SELECT pilot_nbr INTO pilot_NumberVariable
WHERE pilot_nbr = pilot_Number;
I would have done it that way (hoping that this is what you wanted):
DELIMITER // CREATE PROCEDURE pilot_Salary_Procedure( IN pilot_Number INT(20), OUT pilot_Salary DECIMAL(10,2)) BEGIN SELECT salary INTO pilot_Salary FROM pilot WHERE pilot_nbr = pilot_Number; END //
You are then able to execute this procedure with
call pilot_Salary_Procedure(701, @salary);
The result then is stored in the session variable
salary. You may read the value of this variable by issuing
and thus would get
145000 as result.
NB: Be careful with your datatypes: Your notation above suggests that all your values in the table are string-like (for example CHAR or VARCHAR). Whilst this is perfectly okay for attributes like
phone, it hazardous for other's like
salary or even discrete values like the identifiers (
salary should be a
DECIMAL(15,2) or similar.