Faltu Funda

Stored procedures and functions

1. Stored procedure list can be viewed by
   
   
   
   1. Show procedure status
   
   
   
   


2. Stored procedures are case insensitive


3. Maximum length for a stored procedure and stored function is 64 chars


4. Stored procedures can not be nested. That is one stored procedure can not declare any other stored procedure in it’s body.


5. Valid parameter type for stored procedures are
   
   
   
   1. IN – this is the default parameter type
   
   
   2. OUT
   
   
   3. INOUT
   
   
   
   


6. session variables in mysql starts with @ and we don’t need to declare them before setting values to  them.


7. Variable declaration in an SP
   
   
   
   1. DECLARE var int DEFAULT 5;
   
   
   2. DECLARE var int(20)
   
   
   
   


8. Loops in Stored procedures
   
   
   
   1. IF   THEN  ELSE  END IF
   
   
   2. WHILE   END WHILE
   
   
   3. LOOP    END LOOP
   
   
   4. REPEAT  UNTIL      END REPEAT
   
   
   
   


9. default value for an un initialized variable is NULL and NULL + 1 = NULL


10. Calling a stored procedure from command prompt
    
    
    
    1. Call stpName(param);
    
    
    
    

11. stored procedure that will be called from command prompt can return any no. of values but stored procedure that will be called from some scripting language like php

can return only on value. In such stored procedures “select” query must have in INTO statement that can assign value to the variable passed as OUT variable for example 

/*

create procedure temp(OUT var varchar(255))

begin

select user from users where id=1 limit 1 into var;

end

now to use it from php

 

$q = “call temp(@tt)”;

mysql_query($q);

$q = “select @tt”;

$r = mysql_query($q);

$row = mysql_fetch_array($r)

$row[@tt] = ‘name’;