學習來源: CodeData - MySQL 超新手入門（12）Prepared Statement

Prepared Statements的應用

1. 以查詢敘述來說，有一些敘述可能大部份的內容都是一樣的，只有在條件的設定上會不一樣

   SELECT Code, Name, GNP
   FROM   country
   WHERE  Code = 'USA'
   
   SELECT Code, Name, GNP
   FROM   country
   WHERE  Code = 'JPN'
   

2. 如果有「許多要執行的敘述，可是內容卻相似」的情況，可以使用「Prepared statements」改善資料庫的效率
   1. 把這種敘述先準備好

      PREPARE my_country 
      FROM
      'SELECT Code, Name, GNP FROM country 
      WHERE Code = ?'
      

   2. 因為prepared statement的內容中有一個問號，所以你要先設定好一個資料，然後再請伺服器執行指定的prepared statement，伺服器就會傳回執行後的結果了

      SET @my_code = 'USA'
      EXECUTE my_country USING @my_code
      
      SET @my_code = 'JPN'
      EXECUTE my_country USING @my_code
      

使用者變數

使用者變數儲存一些簡單的資料，例如數字或字串，它們可以在後續的操作中使用。語法:
sql SET @變數名稱1 { = | := } 值 [, @變數名稱2 { = | := } 值, ...]

1. 「SELECT」敘述也可以設定需要的使用者變數，不過要特別注意指定的符號只能使用「:=」
2. 使用「SELECT」敘述設定使用者變數的方式，也可以直接把查詢敘述傳回的資料儲存起來

   SELECT @max_gnp := MAX(GNP),
          @max_population := MAX(Population)
   FROM   country
   

   • 設定的使用者變數，可以使用在後續的敘述中，也可以拿使用者變數來執行需要的運算

建立、執行與移除Prepared Statements

1. 有「許多要執行的敘述，可是內容卻相似」的情形，可以考慮請伺服器把這種敘述建立為 prepared statement，建立語法:

   PREPARE my_country 
   FROM
   'SELECT Code, Name, GNP FROM country 
   WHERE Code = ?';
   

   1. 敘述中的問號是「參數標記、parameter marker」
2. 執行一個 prepared statement 並不一定需要傳送資料給它，要依據 prepared statement 包含的敘述中有沒有問號來決定。執行語法:

   SET @my_code = 'JPN'
   EXECUTE my_country USING @my_code;
   

3. 一個問號就需要先設定好一個使用者變數，然後再使用「USING」傳送資料給 prepared statement 使用。刪除語法:

   {DEALLOCATE | DROP } PREPARE 名稱
   

Prepared Statements的參數

1. 在建立prepared statement時，你會依照敘述的需求設定參數標記，這些參數標記也決定執行prepared statement時，須要傳多少參數資料給它才可以正確的執行，範例:

   PREPARED new_dept FROM
   'INSERT INTO cmdev.dept VALUES(?,?,?)';
   EXECUTE new_dept USING 參數一, 參數二, 參數三;
   

2. 傳送的參數數量不對的話，就會產生錯誤訊息
3. 傳送的使用者變數不存在的話，會自動使用「NULL」值代替

有效範

1. 所有使用者變數與 prepared statements 都是某一個用戶端專屬的
2. 如果用戶端離線以後，他所設定的使用者變數與 prepared statements 都會被清除
3. 所以建立 prepared statements 時，不可以指定它是屬於哪一個資料庫，否則會有錯誤訊息。
   • 錯誤的範例:

     PREPARED world.my_city FROM 'SELECT * FROM city';