MySQL variable vs variable What s the difference

Question

In another question I posted someone told me that there is a difference between    variable   and   variable   in MySQL  He also mentioned how MSSQL has batch scope and MySQL has session scope  Can someone elaborate on this for me

User · Answer

variable is very useful if calling stored procedures from an application written in Java   Python etc  There are ocassions where variable values are created in the first call and needed in functions of subsequent calls  Side-note on PL SQL  Oracle  The advantage can be seen in Oracle PL SQL where these variables have 3 different scopes   Function variable for which the scope ends when function exits  Package body variables defined at the top of package and outside all functions whose scope is the session and visibility is package  Package variable whose variable is session and visibility is global   My Experience in PL SQL I have developed an architecture in which the complete code is written in PL SQL  These are called from a middle-ware written in Java  There are two types of middle-ware  One to cater calls from a client which is also written in Java  The other other one to cater for calls from a browser  The client facility is implemented 100 percent in JavaScript  A command set is used instead of HTML and JavaScript for writing application in PL SQL  I have been looking for the same facility to port the codes written in PL SQL to another database  The nearest one I have found is Postgres  But all the variables have function scope  Opinion towards   in MySQL I am happy to see that at least this   facility is there in MySQL  I don t think Oracle will build same facility available in PL SQL to MySQL stored procedures since it may affect the sales of Oracle database

User · Answer

In MySQL   variable indicates a user-defined variable  You can define your own   SET  a    test   SELECT  a    Outside of stored programs  a variable  without    is a system variable  which you cannot define yourself   The scope of this variable is the entire session  That means that while your connection with the database exists  the variable can still be used   This is in contrast with MSSQL  where the variable will only be available in the current batch of queries  stored procedure  script  or otherwise   It will not be available in a different batch in the same session

User · Answer

In principle  I use UserDefinedVariables  prepended with    within Stored Procedures  This makes life easier  especially when I need these variables in two or more Stored Procedures  Just when I need a variable only within ONE Stored Procedure  than I use a System Variable  without prepended        Xybo   I don t understand why using  variables in StoredProcedures should be risky  Could you please explain  scope  and  boundaries  a little bit easier  for me as a newbe

User · Answer

MySQL has a concept of user-defined variables  They are loosely typed variables that may be initialized somewhere in a session and keep their value until the session ends  They are prepended with an   sign  like this   var You can initialize this variable with a SET statement or inside a query  SET  var   1  SELECT  var2    2  When you develop a stored procedure in MySQL  you can pass the input parameters and declare the local variables  DELIMITER     CREATE PROCEDURE prc test  var INT  BEGIN     DECLARE  var2 INT      SET var2   1      SELECT  var2  END      DELIMITER    These variables are not prepended with any prefixes  The difference between a procedure variable and a session-specific user-defined variable is that a procedure variable is reinitialized to NULL each time the procedure is called  while the session-specific variable is not  CREATE PROCEDURE prc test    BEGIN     DECLARE var2 INT DEFAULT 1      SET var2   var2   1      SET  var2    var2   1      SELECT  var2   var2  END   SET  var2   1   CALL prc test     var2   var2 ---   --- 2     2   CALL prc test     var2   var2 ---   --- 2     3   CALL prc test     var2   var2 ---   --- 2     4  As you can see  var2  procedure variable  is reinitialized each time the procedure is called  while  var2  session-specific variable  is not   In addition to user-defined variables  MySQL also has some predefined  quot system variables quot   which may be  quot global variables quot  such as   global port or  quot session variables quot  such as   session sql mode  these  quot session variables quot  are unrelated to session-specific user-defined variables

User · Answer

MSSQL requires that variables within procedures be DECLAREd and folks use the  Variable syntax  DECLARE  TEXT VARCHAR 25     text    Also  MS allows for declares within any block in the procedure  unlike mySQL which requires all the DECLAREs at the top     While good on the command line  I feel using the  set    variable  within stored procedures in mySQL is risky  There is no scope and variables live across scope boundaries  This is similar to variables in JavaScript being declared without the  var  prefix  which are then the global namespace and create unexpected collisions and overwrites    I am hoping that the good folks at mySQL will allow DECLARE  Variable at various block levels within a stored procedure  Notice the    at sign   The   sign prefix helps to separate variable names from table column names - as they are often the same  Of course  one can always add  an  v  or  l   prefix  but the   sign is a handy and succinct way to have the variable name match the column you might be extracting the data from without clobbering it   MySQL is new to stored procedures and they have done a good job for their first version  It will be a pleaure to see where they take it form here and to watch the server side aspects of the language mature

[mysql] MySQL: @variable vs. variable. What's the difference?

Side-note on PL/SQL (Oracle)

My Experience in PL/SQL

Opinion towards `@` in MySQL

Examples related to mysql

Examples related to sql

Examples related to variables