MySQL
संग्रहीत दिनचर्या (प्रक्रिया और कार्य)

पैरामीटर

टिप्पणियों

एक फंक्शन बनाएं

निम्नलिखित (तुच्छ) उदाहरण फ़ंक्शन केवल स्थिर INT मान 12 ।

DELIMITER ||
CREATE FUNCTION functionname()
RETURNS INT
BEGIN
    RETURN 12;
END;
||
DELIMITER ;

पहली पंक्ति परिभाषित करती है कि सीमांकक चरित्र ( DELIMITER || ) को किस रूप में बदलना है, इसे फ़ंक्शन बनाने से पहले सेट करने की आवश्यकता है अन्यथा यदि इसे इसके डिफ़ॉल्ट पर छोड़ दिया जाता है ; फिर पहला ; यह पाया जाता है कि फ़ंक्शन बॉडी में CREATE स्टेटमेंट के अंत के रूप में लिया जाएगा, जो आमतौर पर वांछित नहीं है।

CREATE FUNCTION के चलने के बाद आपको सीमांकक को इसके डिफ़ॉल्ट पर वापस सेट करना चाहिए ; जैसा कि उपरोक्त उदाहरण में फ़ंक्शन कोड के बाद देखा गया है ( DELIMITER ; )।

कार्य इस प्रकार है:

SELECT functionname();
+----------------+
| functionname() |
+----------------+
|             12 |
+----------------+

थोड़ा और अधिक जटिल (लेकिन अभी भी तुच्छ) उदाहरण एक पैरामीटर लेता है और इसमें एक निरंतरता जोड़ता है:

DELIMITER $$
CREATE FUNCTION add_2 ( my_arg INT )
  RETURNS INT
BEGIN
  RETURN (my_arg + 2);
END;
$$
DELIMITER ;

SELECT add_2(12);
+-----------+
| add_2(12) |
+-----------+
|        14 |
+-----------+

DELIMITER निर्देश पर एक अलग तर्क के उपयोग पर ध्यान दें। आप वास्तव में किसी भी चरित्र अनुक्रम कि में प्रकट नहीं होता उपयोग कर सकते हैं CREATE बयान शरीर है, लेकिन हमेशा की तरह व्यवहार एक जैसे दोगुनी गैर अक्षरांकीय वर्ण का उपयोग करने के लिए है \\ , || या $$ ।

किसी फ़ंक्शन, प्रक्रिया या निर्माण या ट्रिगर को अपडेट करने से पहले कुछ GUI के रूप में पैरामीटर को बदलने के लिए अच्छा अभ्यास है क्योंकि कमांड लाइन के माध्यम से चलने वाले प्रश्नों को बदलने में हमेशा सीमांकक की आवश्यकता नहीं होती है।

एक तैयार तैयारी के साथ प्रक्रिया बनाएँ

DROP PROCEDURE if exists displayNext100WithName;
DELIMITER $$
CREATE PROCEDURE displayNext100WithName
(    nStart int,
    tblName varchar(100)
)
BEGIN
    DECLARE thesql varchar(500); -- holds the constructed sql string to execute

    -- expands the sizing of the output buffer to accomodate the output (Max value is at least 4GB)
    SET session group_concat_max_len = 4096; -- prevents group_concat from barfing with error 1160 or whatever it is

    SET @thesql=CONCAT("select group_concat(qid order by qid SEPARATOR '%3B') as nums ","from (    select qid from ");
    SET @thesql=CONCAT(@thesql,tblName," where qid>? order by qid limit 100 )xDerived");
    PREPARE stmt1 FROM @thesql; -- create a statement object from the construct sql string to execute
    SET @p1 = nStart; -- transfers parameter passed into a User Variable compatible with the below EXECUTE
    EXECUTE stmt1 USING @p1;
     
    DEALLOCATE PREPARE stmt1; -- deallocate the statement object when finished
END$$
DELIMITER ;

संग्रहीत कार्यविधि का निर्माण कई क्लाइंट टूल में आवश्यक DELIMITER के साथ रैपिंग दिखाता है।

कॉलिंग उदाहरण:

call displayNext100WithName(1,"questions_mysql");

%3B (सेमी-कोलोन) विभाजक के साथ नमूना आउटपुट:

मापदंडों के बारे में, बाहर, के साथ संग्रहित प्रक्रिया

DELIMITER $$
 
DROP PROCEDURE IF EXISTS sp_nested_loop$$
CREATE PROCEDURE sp_nested_loop(IN i INT, IN j INT, OUT x INT, OUT y INT, INOUT z INT)
BEGIN
    DECLARE a INTEGER DEFAULT 0;
    DECLARE b INTEGER DEFAULT 0;
    DECLARE c INTEGER DEFAULT 0;
    WHILE a < i DO
        WHILE b < j DO
            SET c = c + 1;
            SET b = b + 1;
        END WHILE;    
        SET a = a + 1;
        SET b = 0;        
    END WHILE;
    SET x = a, y = c;
    SET z = x + y + z;
END $$
DELIMITER ;

इनवॉइस ( कॉल ) संग्रहीत कार्यविधि:

SET @z = 30;
call sp_nested_loop(10, 20, @x, @y, @z);
SELECT @x, @y, @z;

परिणाम:

+------+------+------+
|  @x  |  @y  |  @z  |
+------+------+------+
|  10  |  200 |  240 |
+------+------+------+

एक IN पैरामीटर एक प्रक्रिया में कोई मान से गुजरता है। प्रक्रिया मान को संशोधित कर सकती है, लेकिन प्रक्रिया वापस आने पर संशोधन कॉलर को दिखाई नहीं देता है।

OUT पैरामीटर वापस कॉलर के लिए प्रक्रिया से एक मान गुजरता है। इसका प्रारंभिक मान प्रक्रिया के भीतर NULL है, और प्रक्रिया वापस आने पर इसका मान कॉलर को दिखाई देता है।

एक INOUT पैरामीटर को कॉलर द्वारा आरंभीकृत किया जाता है, इसे प्रक्रिया द्वारा संशोधित किया जा सकता है, और प्रक्रिया द्वारा किए गए किसी भी परिवर्तन को कॉलर को दिखाई देता है जब प्रक्रिया वापस आती है।

Ref: http://dev.mysql.com/doc/refman/5.7/en/create-procedure.html

कर्सर

कर्सर आपको पंक्ति के आधार पर क्वेरी के परिणामों को व्यवस्थित करने में सक्षम करते हैं। DECLARE कमांड का उपयोग init कर्सर के लिए किया जाता है और इसे एक विशिष्ट SQL क्वेरी के साथ जोड़ा जाता है:

DECLARE student CURSOR FOR SELECT name FROM studend;

मान लीजिए कि हम कुछ प्रकार के उत्पाद बेचते हैं। हम गिनना चाहते हैं कि प्रत्येक प्रकार के कितने उत्पाद मौजूद हैं।

हमारा डेटा:

CREATE TABLE product
(
  id   INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  type VARCHAR(50)      NOT NULL,
  name VARCHAR(255)     NOT NULL

);
CREATE TABLE product_type
(
  name VARCHAR(50) NOT NULL PRIMARY KEY
);
CREATE TABLE product_type_count
(
  type  VARCHAR(50)      NOT NULL PRIMARY KEY,
  count INT(10) UNSIGNED NOT NULL DEFAULT 0
);

INSERT INTO product_type (name) VALUES
  ('dress'),
  ('food');

INSERT INTO product (type, name) VALUES
  ('dress', 'T-shirt'),
  ('dress', 'Trousers'),
  ('food', 'Apple'),
  ('food', 'Tomatoes'),
  ('food', 'Meat');

हम कर्सर का उपयोग करके संग्रहीत प्रक्रिया का उपयोग करके लक्ष्य प्राप्त कर सकते हैं:

DELIMITER //
DROP PROCEDURE IF EXISTS product_count;
CREATE PROCEDURE product_count()
  BEGIN
    DECLARE p_type VARCHAR(255);
    DECLARE p_count INT(10) UNSIGNED;
    DECLARE done INT DEFAULT 0;
    DECLARE product CURSOR FOR
      SELECT
        type,
        COUNT(*)
      FROM product
      GROUP BY type;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

    TRUNCATE product_type;

    OPEN product;
    
    REPEAT
      FETCH product
      INTO p_type, p_count;
      IF NOT done
      THEN
        INSERT INTO product_type_count
        SET
          type  = p_type,
          count = p_count;
      END IF;
    UNTIL done
    END REPEAT;
    
    CLOSE product;
  END //
DELIMITER ;

जब आप प्रक्रिया को कॉल कर सकते हैं:

CALL product_count();

परिणाम product_type_count तालिका में होगा:

type   | count
----------------
dress  |   2
food   |   3

हालांकि यह CURSOR का एक अच्छा उदाहरण है, ध्यान दें कि प्रक्रिया के पूरे शरीर को कैसे बदला जा सकता है

INSERT INTO product_type_count
        (type, count)
    SELECT type, COUNT(*)
        FROM product
        GROUP BY type;

यह बहुत तेज चलेगा।

एकाधिक परिणाम

एक SELECT कथन के विपरीत, एक Stored Procedure कई परिणाम सेट लौटाती है। पर्ल, पीएचपी, आदि में एक CALL के परिणामों को इकट्ठा करने के लिए उपयोग किए जाने वाले विभिन्न कोड की आवश्यकता होती है।

(यहां या कहीं और विशिष्ट कोड की आवश्यकता है!)

एक फंक्शन बनाएं

DELIMITER $$
CREATE
    DEFINER=`db_username`@`hostname_or_IP`
    FUNCTION `function_name`(optional_param data_type(length_if_applicable))
    RETURNS data_type
BEGIN
    /*
    SQL Statements goes here
    */
END$$
DELIMITER ;

RETURNS डेटा_टाइप किसी भी MySQL डेटाटाइप है।