Stored Procedures

DELIMITER // CREATE PROCEDURE GetAllProducts() BEGIN SELECT * FROM products; END // DELIMITER ;	สร้าง PROCEDURE GetAllProducts() ค้นหาข้อมูล products ทั้งหมด
CALL STORED_PROCEDURE_NAME() เช่น CALL GetAllProducts();	Calling stored procedures เรียก
SHOW CREATE PROCEDURE GetAllProducts	พิมพลง phpmyadmin ดูรายละเอียดการค้นหา storde
DECLARE loop_finish INTEGER DEFAULT 0; DECLARE ytax_id VARCHAR(25); DECLARE ref_id_ytax VARCHAR(25); DECLARE ybranch_ytax VARCHAR(25); DECLARE day_ytax VARCHAR(25); DECLARE id_ytax VARCHAR(15); DECLARE queue_ytax VARCHAR(25); DECLARE mile_ytax VARCHAR(25); DECLARE mileout_ytax VARCHAR(25); DECLARE technician_name_ytax VARCHAR(250); DECLARE sa_id_ytax VARCHAR(15); DECLARE emp_id_ytax VARCHAR(15);	Declaring variables ตัวแปร
DECLARE x, y INT DEFAULT 0	เราประกาศสองINTตัวแปร xและ และและการตั้งค่าเริ่มต้นของพวกเขาให้เป็นศูนย์
DECLARE total_count INT DEFAULT 0 SET total_count = 10;	การกำหนดตัวแปร
DECLARE total_products INT DEFAULT 0 SELECT COUNT(*) INTO total_products FROM products	การกำหนดตัวแปร โดยใช้ INTO
DELIMITER // CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255)) BEGIN SELECT * FROM offices WHERE country = countryName; END // DELIMITER ;	การรับparamiter ของ products แบบ IN
DELIMITER $$ CREATE PROCEDURE products_out( IN idx VARCHAR(25), OUT total INT) BEGIN SELECT sum(UnitsInStock) INTO total FROM products WHERE ProductID = idx; END$$ DELIMITER ;	การรับparamiter ของ products แบบ OUT CALL products_out('1',@total);// ส่งรหัสไป 1 @total คือ paramiter ที่ส่งออกมา SELECT @total AS total_data; // ทำการเลือกค่าที่ส่งออกมาแสดงผล
DELIMITER $$ CREATE PROCEDURE set_counter(INOUT count INT(4),IN inc INT(4)) BEGIN SET count = count + inc; END$$ DELIMITER ;	INOUT parameter example เป้น procedure บวกเลข ที่้ส่งเข้าไป โดยใช้พารามิเตอร์ INOUT SET @counter = 1; CALL set_counter(@counter,1); -- 2 CALL set_counter(@counter,1); -- 3 CALL set_counter(@counter,5); -- 8 SELECT @counter; -- 8
DELIMITER $$ CREATE PROCEDURE get_order_by_cust(IN cust_no INT, OUT shipped INT, OUT canceled INT, OUT resolved INT, OUT disputed INT) BEGIN -- shipped SELECT count(*) INTO shipped FROM orders WHERE customerNumber = cust_no AND status = 'Shipped'; -- canceled SELECT count(*) INTO canceled FROM orders WHERE customerNumber = cust_no AND status = 'Canceled'; -- resolved SELECT count(*) INTO resolved FROM orders WHERE customerNumber = cust_no AND status = 'Resolved'; -- disputed SELECT count(*) INTO disputed FROM orders WHERE customerNumber = cust_no AND status = 'Disputed'; END	Stored procedures return multiple values example ส่งค่าออกหลายค่า CALL get_order_by_cust(141,@shipped,@canceled,@resolved,@disputed); SELECT @shipped,@canceled,@resolved,@disputed; ที่ลองทำ BEGIN -- shipped SELECT ProductID INTO shipped FROM products WHERE SupplierID = cust_no AND ProductName = 'Chai'; -- canceled SELECT UnitPrice INTO canceled FROM products WHERE SupplierID = cust_no AND ProductName = 'Chang'; END ========================== CALL get_order_by_cust(1,@shipped,@canceled); SELECT @shipped,@canceled;
BEGIN DECLARE creditlim DOUBLE; SELECT UnitPrice INTO creditlim FROM products WHERE ProductID = idxxx ; IF creditlim >=18 THEN SET dataxx = 'good'; ELSE SET dataxx = 'xxxx'; END IF; END	MySQL IF statement examples ============================= CALL GetCustomerLevel_if_else(3,@dataxx); SELECT @dataxx;
DELIMITER $$ CREATE PROCEDURE GetCustomerLevel( in p_customerNumber int(11), out p_customerLevel varchar(10)) BEGIN DECLARE creditlim double; SELECT creditlimit INTO creditlim FROM customers WHERE customerNumber = p_customerNumber; IF creditlim > 50000 THEN SET p_customerLevel = 'PLATINUM'; ELSEIF (creditlim <= 50000 AND creditlim >= 10000) THEN SET p_customerLevel = 'GOLD'; ELSEIF creditlim < 10000 THEN SET p_customerLevel = 'SILVER'; END IF; END$$	if() then else if ()
CASE case_expression WHEN when_expression_1 THEN commands WHEN when_expression_2 THEN commands ... ELSE commands END CASE; ตัวอย่าง ที่ลองทำ BEGIN DECLARE data_case VARCHAR(50); SELECT ProductName INTO data_case FROM products WHERE ProductID = p; CASE data_case WHEN 'chai' THEN SET result_data = '2-day Shipping'; WHEN 'Chang' THEN SET result_data = '3-day Shipping'; ELSE SET result_data = '5-day Shipping'; END CASE; END	case สวิตเคส CALL case_swi(1,@result_data); SELECT @result_data;	CREATE DEFINER=`root`@`localhost` PROCEDURE `case_swi`(IN p INT(11),OUT result_data varchar(50)) BEGIN DECLARE data_case VARCHAR(50); SELECT ProductName INTO data_case FROM products WHERE ProductID = p; CASE data_case WHEN 'chai' THEN SET result_data = '2-day Shipping'; WHEN 'Chang' THEN SET result_data = '3-day Shipping'; ELSE SET result_data = '5-day Shipping'; END CASE; END
BEGIN DECLARE x INT; // กำหนดตัวแปร เป้น int DECLARE str VARCHAR(255); // ประกาศตัวแปรไว้ SET x = 1; SET str = ''; WHILE x <= 5 DO SET str = CONCAT(str,x,','); // ต่อคำ SET x = x + 1; END WHILE; SELECT str; //แสดง END	loop while วนเอาค่ามาต่อกัน 1,2,3,4,5,
CURSOR	MySQL cursor -อ่านอย่างเดียวคุณไม่สามารถปรับปรุงข้อมูลในตารางต้นแบบผ่านเคอร์เซอร์ -คุณสามารถเรียกแถวในการสั่งซื้อที่กำหนดโดยคำสั่ง SELECT คุณไม่สามารถเรียกแถวในลำดับที่ตรงกันข้าม นอกจากนี้คุณไม่สามารถข้ามแถวหรือข้ามไปยังแถวที่ระบุในชุดผลลัพธ์ -	ถ้า cursor มา 3 ฟิว ก็ต้อง fetch 3 ฟิว
DECLARE cursor_name CURSOR FOR SELECT_statement;	ขั้นแรกคุณต้องประกาศเคอร์เซอร์โดยใช้คำสั่งประกาศ:
OPEN cursor_name;	ถัดไปคุณเปิดเคอร์เซอร์โดยใช้คำสั่งเปิด
FETCH cursor_name INTO variables list;	จากนั้นคุณใช้คำสั่งเรียกเพื่อดึงแถวถัดไปชี้โดยเคอร์เซอร์และเลื่อนเคอร์เซอร์ไปที่แถวถัดไปในชุดผลลัพธ์
CLOSE cursor_name;	สุดท้ายคุณเรียกคำสั่งปิดเพื่อยกเลิกเคอร์เซอร์และปล่อยหน่วยความจำที่เกี่ยวข้องกับมันดังต่อไปนี้:
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;	ในการประกาศจัดการไม่พบว่ามีค่าแล้ว คุณใช้ไวยากรณ์ต่อไปนี้:
BEGIN DECLARE v_finished INTEGER DEFAULT 0; DECLARE v_email varchar(100) DEFAULT ""; DECLARE email_list varchar(100) ; DEClARE email_cursor CURSOR FOR SELECT ProductID FROM products; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1; OPEN email_cursor; get_pro:LOOP FETCH email_cursor INTO v_email; IF v_finished =1 THEN LEAVE get_pro; END IF; #SET email_list = CONCAT(v_email,";",email_list); SELECT * FROM products WHERE ProductID = v_email; END LOOP get_pro; CLOSE email_cursor; END	ทดลองดึงข้อมูล products result จะออกมาหลายอันตามคำสั่ง	CREATE DEFINER=`root`@`localhost` PROCEDURE `build_email_list`() BEGIN DECLARE v_finished INTEGER DEFAULT 0; DECLARE v_email varchar(100) DEFAULT ""; DECLARE email_list varchar(100) ; DEClARE email_cursor CURSOR FOR SELECT ProductID FROM products; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1; OPEN email_cursor; get_pro:LOOP FETCH email_cursor INTO v_email; IF v_finished =1 THEN LEAVE get_pro; END IF; #SET email_list = CONCAT(v_email,";",email_list); SELECT * FROM products WHERE ProductID = v_email; END LOOP get_pro; CLOSE email_cursor; END
DELIMITER $$ CREATE FUNCTION CustomerLevel(p_creditLimit double) RETURNS VARCHAR(10) DETERMINISTIC BEGIN DECLARE lvl varchar(10); IF p_creditLimit > 50000 THEN SET lvl = 'PLATINUM'; ELSEIF (p_creditLimit <= 50000 AND p_creditLimit >= 10000) THEN SET lvl = 'GOLD'; ELSEIF p_creditLimit < 10000 THEN SET lvl = 'SILVER'; END IF; RETURN (lvl); END	MySQL stored function example ====== วิธีใช้ SELECT customerName, CustomerLevel(creditLimit) FROM customers;	กรณีสร้าง ใน navicat ต้องใส่ DETERMINISTIC ก่อน BEGIN
SELECT `ahistory_id` , count( id ) FROM `cvhc_main` GROUP BY `ahistory_id` HAVING ( count( id ) >2 )	การใช้ having ใน stored
Deterministic Function ฟังก์ชันแบบคืนค่าตายตัว Non-Deterministic Function ฟังก์ชันแบบคืนค่าไม่ตายตัว
CREATE TABLE 1_nongarticle_tags( article_id INT, tag_id INT, PRIMARY KEY(article_id,tag_id) );	สร้างเพื่อ ้handel
DELIMITER $$ CREATE PROCEDURE handler(IN article_id INT, IN tag_id INT) BEGIN DECLARE CONTINUE HANDLER FOR 1062 SELECT CONCAT('duplicate keys (',article_id,',',tag_id,') found') AS msg; -- insert a new record into article_tags INSERT INTO article_tags(article_id,tag_id) VALUES(article_id,tag_id); -- return tag count for the article SELECT COUNT(*) FROM article_tags; END	CALL handler(1,1); CALL handler(1,2); CALL handler(1,3); --- CALL handler(1,3);