Snowflake Scripting 的常见用例示例¶
您可以编写使用 Snowflake Scripting 语言元素、数据类型和变量的匿名块和存储过程,以提供可解决常见用例的解决方案。本主题包括一些常见用例的 Snowflake Scripting 代码示例。
使用用户输入更新表数据¶
以下示例创建了一个使用用户输入更新表数据的存储过程。它使用一个 FOR循环 遍历表的 RESULTSET 中的行。该 FOR 循环包含 条件逻辑。基于用户输入的 绑定变量 决定存储过程执行的确切更新。
该示例使用以下数据:
CREATE OR REPLACE TABLE bonuses (
emp_id INT,
performance_rating INT,
salary NUMBER(12, 2),
bonus NUMBER(12, 2)
);
INSERT INTO bonuses (emp_id, performance_rating, salary, bonus) VALUES
(1001, 3, 100000, NULL),
(1002, 1, 50000, NULL),
(1003, 4, 75000, NULL),
(1004, 4, 80000, NULL),
(1005, 5, 120000, NULL),
(1006, 2, 60000, NULL),
(1007, 5, 40000, NULL),
(1008, 3, 140000, NULL),
(1009, 1, 95000, NULL);
SELECT * FROM bonuses;
+--------+--------------------+-----------+-------+
| EMP_ID | PERFORMANCE_RATING | SALARY | BONUS |
|--------+--------------------+-----------+-------|
| 1001 | 3 | 100000.00 | NULL |
| 1002 | 1 | 50000.00 | NULL |
| 1003 | 4 | 75000.00 | NULL |
| 1004 | 4 | 80000.00 | NULL |
| 1005 | 5 | 120000.00 | NULL |
| 1006 | 2 | 60000.00 | NULL |
| 1007 | 5 | 40000.00 | NULL |
| 1008 | 3 | 140000.00 | NULL |
| 1009 | 1 | 95000.00 | NULL |
+--------+--------------------+-----------+-------+
以下存储过程使用 FOR 循环遍历 bonuses
表的 RESULTSET 中的行。系统根据每位员工(具有指定绩效等级)工资的指定百分比发放奖金。存储过程使用条件逻辑将奖金仅应用于具有指定绩效等级的员工。它还使用输入(bonus_percentage
和 performance_value
)作为绑定变量。
CREATE OR REPLACE PROCEDURE apply_bonus(bonus_percentage INT, performance_value INT)
RETURNS TEXT
LANGUAGE SQL
AS
DECLARE
-- Use input to calculate the bonus percentage
updated_bonus_percentage NUMBER(2,2) DEFAULT (:bonus_percentage/100);
-- Declare a result set
rs RESULTSET;
BEGIN
-- Assign a query to the result set and execute the query
rs := (SELECT * FROM bonuses);
-- Use a FOR loop to iterate over the records in the result set
FOR record IN rs DO
-- Assign variable values using values in the current record
LET emp_id_value INT := record.emp_id;
LET performance_rating_value INT := record.performance_rating;
LET salary_value NUMBER(12, 2) := record.salary;
-- Determine whether the performance rating in the record matches the user input
IF (performance_rating_value = :performance_value) THEN
-- If the condition is met, update the bonuses table using the calculated bonus percentage
UPDATE bonuses SET bonus = ( :salary_value * :updated_bonus_percentage )
WHERE emp_id = :emp_id_value;
END IF;
END FOR;
-- Return text when the stored procedure completes
RETURN 'Update applied';
END;
注意:如果您在 Python Connector 代码中使用 SnowSQL、Classic Console 或者 execute_stream
或 execute_string
方法,请改用本示例(请参阅 在 SnowSQL、Classic Console 和 Python Connector 中使用 Snowflake Scripting):
CREATE OR REPLACE PROCEDURE apply_bonus(bonus_percentage INT, performance_value INT)
RETURNS TEXT
LANGUAGE SQL
AS
$$
DECLARE
-- Use input to calculate the bonus percentage
updated_bonus_percentage NUMBER(2,2) DEFAULT (:bonus_percentage/100);
-- Declare a result set
rs RESULTSET;
BEGIN
-- Assign a query to the result set and execute the query
rs := (SELECT * FROM bonuses);
-- Use a FOR loop to iterate over the records in the result set
FOR record IN rs DO
-- Assign variable values using values in the current record
LET emp_id_value INT := record.emp_id;
LET performance_rating_value INT := record.performance_rating;
LET salary_value NUMBER(12, 2) := record.salary;
-- Determine whether the performance rating in the record matches the user input
IF (performance_rating_value = :performance_value) THEN
-- If the condition is met, update the bonuses table using the calculated bonus percentage
UPDATE bonuses SET bonus = ( :salary_value * :updated_bonus_percentage )
WHERE emp_id = :emp_id_value;
END IF;
END FOR;
-- Return text when the stored procedure completes
RETURN 'Update applied';
END;
$$
;
要运行存储过程,请指定奖金百分比和绩效等级。例如,调用存储过程,为绩效等级为 5 的员工发放 3% 的奖金:
CALL apply_bonus(3, 5);
运行查询以显示结果:
SELECT * FROM bonuses;
+--------+--------------------+-----------+---------+
| EMP_ID | PERFORMANCE_RATING | SALARY | BONUS |
|--------+--------------------+-----------+---------|
| 1001 | 3 | 100000.00 | NULL |
| 1002 | 1 | 50000.00 | NULL |
| 1003 | 4 | 75000.00 | NULL |
| 1004 | 4 | 80000.00 | NULL |
| 1005 | 5 | 120000.00 | 3600.00 |
| 1006 | 2 | 60000.00 | NULL |
| 1007 | 5 | 40000.00 | 1200.00 |
| 1008 | 3 | 140000.00 | NULL |
| 1009 | 1 | 95000.00 | NULL |
+--------+--------------------+-----------+---------+
筛选和收集数据¶
以下示例创建了一个存储过程,用于筛选和收集表中的数据。该过程使用收集到的数据将行插入另一个表中,以跟踪历史趋势。
该示例使用以下数据来跟踪虚拟机的所有权和设置 (VMs):
CREATE OR REPLACE TABLE vm_ownership (
emp_id INT,
vm_id VARCHAR
);
INSERT INTO vm_ownership (emp_id, vm_id) VALUES
(1001, 1),
(1001, 5),
(1002, 3),
(1003, 4),
(1003, 6),
(1003, 2);
CREATE OR REPLACE TABLE vm_settings (
vm_id INT,
vm_setting VARCHAR,
value NUMBER
);
INSERT INTO vm_settings (vm_id, vm_setting, value) VALUES
(1, 's1', 5),
(1, 's2', 500),
(2, 's1', 10),
(2, 's2', 600),
(3, 's1', 3),
(3, 's2', 400),
(4, 's1', 8),
(4, 's2', 700),
(5, 's1', 1),
(5, 's2', 300),
(6, 's1', 7),
(6, 's2', 800);
CREATE OR REPLACE TABLE vm_settings_history (
vm_id INT,
vm_setting VARCHAR,
value NUMBER,
owner INT,
date DATE
);
假设一家公司希望在设置值超过特定阈值时跟踪此表中的数据。以下存储过程收集和筛选 vm_settings
表中的数据,然后在满足以下条件时向 vm_settings_history
表中插入行:
将值为
s1
的vm_setting
设置为小于5
。将值为
s2
的vm_setting
设置为大于500
。
插入到 vm_settings_history
表中的行包括 vm_settings
表中的所有列值,以及拥有该 VM 的员工的 emp_id
和当前日期。
CREATE OR REPLACE PROCEDURE vm_user_settings()
RETURNS VARCHAR
LANGUAGE SQL
AS
DECLARE
-- Declare a cursor and a variable
c1 CURSOR FOR SELECT * FROM vm_settings;
current_owner NUMBER;
BEGIN
-- Open the cursor to execute the query and retrieve the rows into the cursor
OPEN c1;
-- Use a FOR loop to iterate over the records in the result set
FOR record IN c1 DO
-- Assign variable values using values in the current record
LET current_vm_id NUMBER := record.vm_id;
LET current_vm_setting VARCHAR := record.vm_setting;
LET current_value NUMBER := record.value;
-- Assign a value to the current_owner variable by querying the vm_ownership table
SELECT emp_id INTO :current_owner
FROM vm_ownership
WHERE vm_id = :current_vm_id;
-- If the record has a vm_setting equal to 's1', determine whether its value is less than 5
IF (current_vm_setting = 's1' AND current_value < 5) THEN
-- If the condition is met, insert a row into the vm_settings_history table
INSERT INTO vm_settings_history VALUES (
:current_vm_id,
:current_vm_setting,
:current_value,
:current_owner,
SYSDATE());
-- If the record has a vm_setting equal to 's2', determine whether its value is greater than 500
ELSEIF (current_vm_setting = 's2' AND current_value > 500) THEN
-- If the condition is met, insert a row into the vm_settings_history table
INSERT INTO vm_settings_history VALUES (
:current_vm_id,
:current_vm_setting,
:current_value,
:current_owner,
SYSDATE());
END IF;
END FOR;
-- Close the cursor
CLOSE c1;
-- Return text when the stored procedure completes
RETURN 'Success';
END;
注意:如果您在 Python Connector 代码中使用 SnowSQL、Classic Console 或者 execute_stream
或 execute_string
方法,请改用本示例(请参阅 在 SnowSQL、Classic Console 和 Python Connector 中使用 Snowflake Scripting):
CREATE OR REPLACE PROCEDURE vm_user_settings()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
-- Declare a cursor and a variable
c1 CURSOR FOR SELECT * FROM vm_settings;
current_owner NUMBER;
BEGIN
-- Open the cursor to execute the query and retrieve the rows into the cursor
OPEN c1;
-- Use a FOR loop to iterate over the records in the result set
FOR record IN c1 DO
-- Assign variable values using values in the current record
LET current_vm_id NUMBER := record.vm_id;
LET current_vm_setting VARCHAR := record.vm_setting;
LET current_value NUMBER := record.value;
-- Assign a value to the current_owner variable by querying the vm_ownership table
SELECT emp_id INTO :current_owner
FROM vm_ownership
WHERE vm_id = :current_vm_id;
-- If the record has a vm_setting equal to 's1', determine whether its value is less than 5
IF (current_vm_setting = 's1' AND current_value < 5) THEN
-- If the condition is met, insert a row into the vm_settings_history table
INSERT INTO vm_settings_history VALUES (
:current_vm_id,
:current_vm_setting,
:current_value,
:current_owner,
SYSDATE());
-- If the record has a vm_setting equal to 's2', determine whether its value is greater than 500
ELSEIF (current_vm_setting = 's2' AND current_value > 500) THEN
-- If the condition is met, insert a row into the vm_settings_history table
INSERT INTO vm_settings_history VALUES (
:current_vm_id,
:current_vm_setting,
:current_value,
:current_owner,
SYSDATE());
END IF;
END FOR;
-- Close the cursor
CLOSE c1;
-- Return text when the stored procedure completes
RETURN 'Success';
END;
$$;
运行存储过程:
CALL vm_user_settings();
通过运行以下查询,可查看该过程插入到 vm_settings_history
表中的数据:
SELECT * FROM vm_settings_history ORDER BY vm_id;
+-------+------------+-------+-------+------------+
| VM_ID | VM_SETTING | VALUE | OWNER | DATE |
|-------+------------+-------+-------+------------|
| 2 | s2 | 600 | 1003 | 2024-04-01 |
| 3 | s1 | 3 | 1002 | 2024-04-01 |
| 4 | s2 | 700 | 1003 | 2024-04-01 |
| 5 | s1 | 1 | 1001 | 2024-04-01 |
| 6 | s2 | 800 | 1003 | 2024-04-01 |
+-------+------------+-------+-------+------------+