Ninja Ninja - 6 months ago 14
SQL Question

Create a Simple Procedure in MySql [STUCK]

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.

Question:

I want to get the details of salary for each pilot when I write his pilot_nbr.

Pilot Table:


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',
'155000.00', '701'

'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',
'7777', 'Hawke','Mike','2ndofficer','7street','6046677326',423','EMP','139000.00','701'


Here is what I did and got stuck:

DELIMITER //
CREATE PROCEDURE pilot_Salary_Procedure( IN pilot_Number INT(20), OUT pilot_Salary DECIMAL(10,2))
BEGIN
DECLARE pilot_NummberVariable INT(20);
SELECT pilot_nbr INTO pilot_NumberVariable
FROM pilot
WHERE pilot_nbr = pilot_Number;
END //

Answer

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

select @salary;

and thus would get 145000 as result.

see also http://dev.mysql.com/doc/refman/5.7/en/call.html

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 first_name, last_name or phone, it hazardous for other's like salary or even discrete values like the identifiers (pilot_nbr or manager_nbr). Especially salary should be a DECIMAL(15,2) or similar.

Comments