Examples / Common Use cases
This topic contains some examples of script code that is useful for some common use cases.
Update column in a table
In this Example, we use:
FORloop- Conditional logic
- Variablebinding
- RESULTSET
The following data is used in this example:
sql
CREATE TABLE IF NOT EXISTS my_folder.CAR_DEALERS_BP (
DEALER_ID INTEGER,
CUSTOMER_RATING INTEGER,
CARS_SOLD INTEGER,
BONUS_POINTS DECIMAL
);
INSERT INTO my_folder.CAR_DEALERS_BP (DEALER_ID, CUSTOMER_RATING, CARS_SOLD, BONUS_POINTS) VALUES
(43010, 5, 300, NULL),
(43020, 2, 50, NULL),
(47030, 1, 45, NULL),
(47040, 3, 840, NULL),
(39050, 4, 1010, NULL),
(39060, 5, 330, NULL),
(22070, 2, 120, NULL),
(22080, 1, 440, NULL),
(12070, 4, 200, NULL),
(12080, 3, 700, NULL),
(12090, 5, 940, NULL);
SELECT * FROM my_folder.CAR_DEALERS_BP;txt
+-----------+-----------------+-----------+--------------+
| DEALER_ID | CUSTOMER_RATING | CARS_SOLD | BONUS_POINTS |
+-----------+-----------------+-----------+--------------+
| 43010 | 5 | 300 | NULL |
| 43020 | 2 | 50 | NULL |
| 47030 | 1 | 45 | NULL |
| 47040 | 3 | 840 | NULL |
| 39050 | 4 | 1010 | NULL |
| 39060 | 5 | 330 | NULL |
| 22070 | 2 | 120 | NULL |
| 22080 | 1 | 440 | NULL |
| 12070 | 4 | 200 | NULL |
| 12080 | 3 | 700 | NULL |
| 12090 | 5 | 940 | NULL |
+-----------+-----------------+-----------+--------------+This stored procedure is an example of how a column values in a table can be updated with newly calculated values based on conditional logic and user input, using a record ID.
sql
CREATE OR REPLACE PROCEDURE my_folder.CALCLULATE_BONUS_POINTS
(CUSTOMER_RATING_WEIGHT DECIMAL, CUSTOMER_RATING_THRESHOLD INTEGER)
RETURNS STRING
AS
DECLARE
my_rs RESULTSET; -- declare a result set
BEGIN
my_rs := (SELECT * FROM my_folder.CAR_DEALERS_BP); -- assign to the result set and execute the query
FOR record IN my_rs DO -- iterate over the result set
LET ID INTEGER := record.DEALER_ID; -- assign variable values using values in the current row
LET CUSTOMER_RATING INTEGER := record.CUSTOMER_RATING;
LET CARS_SOLD DECIMAL := record.CARS_SOLD;
IF (CUSTOMER_RATING >= CUSTOMER_RATING_THRESHOLD) THEN -- if CUSTOMER_RATING > threshold
-- Weght bonuspoints
UPDATE my_folder.CAR_DEALERS_BP SET
BONUS_POINTS = (:CARS_SOLD / 100 * (1 + (CUSTOMER_RATING * :CUSTOMER_RATING_WEIGHT)))
WHERE DEALER_ID = :ID;
ELSE -- calculate bonus points withoud additionl bonus
UPDATE my_folder.CAR_DEALERS_BP SET BONUS_POINTS = (:CARS_SOLD / 100)
WHERE DEALER_ID = :ID;
END IF;
END FOR;
-- Return text when the stored procedure completes
RETURN 'Bonus points calculated';
END;
CALL my_folder.CALCLULATE_BONUS_POINTS(0.1,3);txt
+-------------------------+
| CALCLULATE_BONUS_POINTS |
+-------------------------+
| Bonus points calculated |
+-------------------------+sql
SELECT * FROM my_folder.CAR_DEALERS_BP ORDER BY BONUS_POINTS DESC;txt
+-----------+-----------------+-----------+--------------+
| DEALER_ID | CUSTOMER_RATING | CARS_SOLD | BONUS_POINTS |
+-----------+-----------------+-----------+--------------+
| 39050 | 4 | 1010 | 14.140 |
| 12090 | 5 | 940 | 14.100 |
| 47040 | 3 | 840 | 10.920 |
| 12080 | 3 | 700 | 9.100 |
| 39060 | 5 | 330 | 4.950 |
| 43010 | 5 | 300 | 4.500 |
| 22080 | 1 | 440 | 4.400 |
| 12070 | 4 | 200 | 2.800 |
| 22070 | 2 | 120 | 1.200 |
| 43020 | 2 | 50 | 0.500 |
| 47030 | 1 | 45 | 0.450 |
+-----------+-----------------+-----------+--------------+Update data and create history of rows
We use the table "CAR_DEALERS_BP" from the first example and define the additional bonus depending on "CUSTOMER_RATING"-column.
Create and fill table for additional bonus weight depending on "CUSTOMER_RATING":
sql
CREATE TABLE IF NOT EXISTS my_folder.ADD_BONUS_WEIGHT (
CUSTOMER_RATING INTEGER,
CUSTOMER_RATING_WEIGHT DECIMAL
);
INSERT INTO my_folder.ADD_BONUS_WEIGHT (CUSTOMER_RATING, CUSTOMER_RATING_WEIGHT) VALUES
(1, 0),
(2, 0),
(3, 0.05),
(4, 0.075),
(5, 0.1);
SELECT * FROM my_folder.ADD_BONUS_WEIGHT;txt
+-----------------+------------------------+
| CUSTOMER_RATING | CUSTOMER_RATING_WEIGHT |
+-----------------+------------------------+
| 1 | 0.000 |
| 2 | 0.000 |
| 3 | 0.050 |
| 4 | 0.075 |
| 5 | 0.100 |
+-----------------+------------------------+We assume that the sales data changes and we need to update the bonus amount at an assumed interval. In addition, the additional bonus should be calculated depending on the "CUSTOMER_RATING" value using a unique weight per "CUSTOMER_RATING" level.
We also store the complete historical status in the table "CAR_DEALERS_BP_HISTORY" with the timestamp of the change every time, we run the procedure.
Create history table:
sql
CREATE TABLE IF NOT EXISTS my_folder.CAR_DEALERS_BP_HISTORY (
DEALER_ID INTEGER,
CUSTOMER_RATING INTEGER,
CARS_SOLD INTEGER,
BONUS_POINTS DECIMAL,
CHANGE_TIME_STAMP TIMESTAMP
);Create stored procedure:
sql
CREATE OR REPLACE PROCEDURE my_folder.CALCLULATE_BONUS_POINTS_WITH_HISTORY()
RETURNS STRING
AS
DECLARE
my_rs RESULTSET; -- declare a result set
new_CUSTOMER_RATING_WEIGHT DECIMAL; -- declare variable for new bonus points calculation
BEGIN
my_rs := (SELECT * FROM my_folder.CAR_DEALERS_BP); -- assign query to resultset
FOR record IN my_rs DO -- iterate over the result set
LET cur_DEALER_ID INTEGER := record.DEALER_ID; -- assign variable values from the current row
LET cur_CUSTOMER_RATING INTEGER := record.CUSTOMER_RATING; -- assign variable from the current row
LET cur_CARS_SOLD INTEGER := record.CARS_SOLD; -- assign variable values from the current row
LET cur_CURRENT_BONUS_POINTS DECIMAL := record.BONUS_POINTS; -- assign variable from the current row
SELECT CUSTOMER_RATING_WEIGHT INTO :new_CUSTOMER_RATING_WEIGHT
FROM my_folder.ADD_BONUS_WEIGHT
WHERE CUSTOMER_RATING = :cur_CUSTOMER_RATING; -- get weight für new bonus points calculation
LET new_BONUS_POINTS DECIMAL := :cur_CARS_SOLD/100 * (1.000 + (:cur_CUSTOMER_RATING * :new_CUSTOMER_RATING_WEIGHT)); -- calculate new bonus points
INSERT INTO my_folder.CAR_DEALERS_BP_HISTORY VALUES ( -- fill history table
:cur_DEALER_ID,
:cur_CUSTOMER_RATING,
:cur_CARS_SOLD,
:cur_CURRENT_BONUS_POINTS,
CURRENT_TIMESTAMP);
UPDATE my_folder.CAR_DEALERS_BP SET -- update bonus points in the table
BONUS_POINTS = :new_BONUS_POINTS
WHERE DEALER_ID = :cur_DEALER_ID;
END FOR;
RETURN 'Successful';
END;Run procedure:
sql
CALL my_folder.CALCLULATE_BONUS_POINTS_WITH_HISTORY();View the results:
sql
SELECT * FROM my_folder.CAR_DEALERS_BP ORDER BY BONUS_POINTS DESC;txt
+-----------+-----------------+-----------+--------------+
| DEALER_ID | CUSTOMER_RATING | CARS_SOLD | BONUS_POINTS |
+-----------+-----------------+-----------+--------------+
| 12090 | 5 | 940 | 14.100 |
| 39050 | 4 | 1010 | 13.130 |
| 47040 | 3 | 840 | 9.660 |
| 12080 | 3 | 700 | 8.050 |
| 39060 | 5 | 330 | 4.950 |
| 43010 | 5 | 300 | 4.500 |
| 22080 | 1 | 440 | 4.400 |
| 12070 | 4 | 200 | 2.600 |
| 22070 | 2 | 120 | 1.200 |
| 43020 | 2 | 50 | 0.500 |
| 47030 | 1 | 45 | 0.450 |
+-----------+-----------------+-----------+--------------+Values in BONUS_POINTS column has changed compared to the previous example because we applied weights from the ADD_BONUS_WEIGHT table.
sql
SELECT * FROM my_folder.CAR_DEALERS_BP_HISTORY ORDER BY CHANGE_TIME_STAMP DESC;txt
+-----------+-----------------+-----------+--------------+--------------------------+
| DEALER_ID | CUSTOMER_RATING | CARS_SOLD | BONUS_POINTS | CHANGE_TIME_STAMP |
+-----------+-----------------+-----------+--------------+--------------------------+
| 39050 | 4 | 1010 | 14.140 | 2025-02-05 12:20:03.867 |
| 12090 | 5 | 940 | 14.100 | 2025-02-05 12:20:07.260 |
| 47040 | 3 | 840 | 10.920 | 2025-02-05 12:20:03.313 |
| 12080 | 3 | 700 | 9.100 | 2025-02-05 12:20:06.680 |
| 39060 | 5 | 330 | 4.950 | 2025-02-05 12:20:04.427 |
| 43010 | 5 | 300 | 4.500 | 2025-02-05 12:20:01.630 |
| 22080 | 1 | 440 | 4.400 | 2025-02-05 12:20:05.545 |
| 12070 | 4 | 200 | 2.800 | 2025-02-05 12:20:06.113 |
| 22070 | 2 | 120 | 1.200 | 2025-02-05 12:20:04.980 |
| 43020 | 2 | 50 | 0.500 | 2025-02-05 12:20:02.190 |
| 47030 | 1 | 45 | 0.450 | 2025-02-05 12:20:02.759 |
+-----------+-----------------+-----------+--------------+--------------------------+