1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
|
/*
Test script for the Query Browser script editor.
*/
drop table if exists test.widget;
CREATE TABLE test.widget (
widget_id int(11),
widget_price decimal(6,2));
INSERT INTO WIDGET VALUES (1, 253.00);
INSERT INTO WIDGET VALUES (2, 202.00);
INSERT INTO WIDGET VALUES (2, 734.40);
INSERT INTO WIDGET VALUES (4, 234.00);
DELIMITER %%
DROP PROCEDURE IF EXISTS test.setpartprice%%
CREATE PROCEDURE test.setpartprice (partid INT ,
Quantity INT,
price DECIMAL(6,2)
)
BEGIN
DECLARE discount_percent DECIMAL(6,2);
DECLARE discounted_price DECIMAL(6,2);
DECLARE test INT;
SET discount_percent = 15;
SET discounted_price = price - (discount_percent / 100 * price);
CALL test.widgetcount(test);
IF quantity > 2 AND test > 3 THEN
SET discounted_price = discounted_price - 2.00;
END IF;
UPDATE WIDGET SET widget_price = discounted_price WHERE widget_id = partid;
-- Select * from widget;
END%%
# call test.setpartprice (1, 15, 200);
DROP PROCEDURE IF EXISTS test.widgetcount%%
CREATE PROCEDURE test.widgetcount (OUT num INT)
BEGIN
SELECT COUNT(*) INTO num FROM widget;
END%%
DROP PROCEDURE IF EXISTS simpleproc%%
CREATE PROCEDURE simpleproc ()
BEGIN
DECLARE Inttest int; -- just a declaration
Set Inttest = 3; -- and give the declaration an integer
SELECT 'TEST' AS Col1, IntTest; -- select just string value
END%%
DROP PROCEDURE IF EXISTS test.simpleproc%%
CREATE PROCEDURE test.simpleproc ()
BEGIN
SELECT 'TEST' AS Res1;
SELECT 'TEST2' AS Res2;
SELECT test.hello ('Alfredo') as Res3;
END%%
CREATE FUNCTION test.hello (s CHAR(20)) RETURNS CHAR(50)
RETURN CONCAT('Hello, ',s,'!')%%
DELIMITER ;
|
