3.2.4.9. SQLite
3.2.4.9.1. 插入
3.2.4.9.1.1. 单表插入
单表数据插入，语法支持范围
insert xxx into table_name values xxx returning/return xxx into yyy; 其中returning/return ... into ... 语句一般用于plsql中-- 转换前Oracle SQL： INSERT INTO students VALUES ('John Doe', 20) RETURNING employee_id, salary INTO @v_employee_ids, @v_salaries; INSERT INTO students (name, age) VALUES ('John Doe', 20) RETURNING employee_id, salary INTO v_employee_ids, v_salaries; INSERT INTO students (name, age) VALUES ('John Doe', 20) RETURN employee_id, salary INTO @v_employee_ids, @v_salaries; INSERT INTO students (name, age) VALUES ('John Doe', 20) RETURN employee_id, salary INTO v_employee_ids, v_salaries; -- 转换后SQLite： INSERT INTO `students` VALUES ('John Doe',20) RETURNING `employee_id` AS `v_employee_ids`,`salary` AS `v_salaries`; INSERT INTO `students` (`name`,`age`) VALUES ('John Doe',20) RETURNING `employee_id` AS `v_employee_ids`,`salary` AS `v_salaries`; INSERT INTO `students` (`name`,`age`) VALUES ('John Doe',20) RETURNING `employee_id` AS `v_employee_ids`,`salary` AS `v_salaries`; INSERT INTO `students` (`name`,`age`) VALUES ('John Doe',20) RETURNING `employee_id` AS `v_employee_ids`,`salary` AS `v_salaries`;
警告
注意：对于return/returning后面的参数个数与into后面的参数个数数量保证一致，否则转换出错。
3.2.4.9.2. 更新
3.2.4.9.2.1. 单表更新
单表数据更新，语法支持范围
update table_name ... returning/return xxx into yyy; 其中returning/return ... into ... 语句一般用于plsql中
-- 转换前Oracle SQL：
UPDATE employees SET salary = v_new_salary WHERE employee_id = 101 RETURNING salary, eeee INTO @qw, :er;
UPDATE employees SET salary = v_new_salary WHERE employee_id = 101 RETURNING salary, eeee INTO qw, er;
UPDATE employees SET salary = v_new_salary WHERE employee_id = 101 RETURN salary, eeee INTO @qw, @er;
UPDATE employees SET salary = v_new_salary WHERE employee_id = 101 RETURN salary, eeee INTO qw, er;
-- 转换后SQLite：
UPDATE `employees` SET `salary`=`v_new_salary` WHERE `employee_id`=101 RETURNING `salary` AS `qw`,`eeee` AS `er`;
UPDATE `employees` SET `salary`=`v_new_salary` WHERE `employee_id`=101 RETURNING `salary` AS `qw`,`eeee` AS `er`;
UPDATE `employees` SET `salary`=`v_new_salary` WHERE `employee_id`=101 RETURNING `salary` AS `qw`,`eeee` AS `er`;
UPDATE `employees` SET `salary`=`v_new_salary` WHERE `employee_id`=101 RETURNING `salary` AS `qw`,`eeee` AS `er`;
警告
注意：对于return/returning后面的参数个数与into后面的参数个数数量保证一致，否则转换出错。
3.2.4.9.3. 删除
3.2.4.9.3.1. 单表删除
单表数据删除，语法支持范围
delete from table_name ... returning/return xxx into yyy;其中returning/return ... into ... 语句一般用于plsql中
-- 转换前Oracle SQL：
DELETE FROM employees WHERE salary < 3000 RETURNING employee_id, salary INTO @v_employee_ids, @v_salaries;
DELETE FROM employees WHERE salary < 3000 RETURNING employee_id, salary INTO v_employee_ids, v_salaries;
DELETE FROM employees WHERE salary < 3000 RETURN employee_id, salary INTO @v_employee_ids, @v_salaries;
DELETE FROM employees WHERE salary < 3000 RETURN employee_id, salary INTO v_employee_ids, v_salaries;
-- 转换后SQLite：
DELETE FROM `employees` WHERE `salary`<3000 RETURNING `employee_id` AS `v_employee_ids`,`salary` AS `v_salaries`;
DELETE FROM `employees` WHERE `salary`<3000 RETURNING `employee_id` AS `v_employee_ids`,`salary` AS `v_salaries`;
DELETE FROM `employees` WHERE `salary`<3000 RETURNING `employee_id` AS `v_employee_ids`,`salary` AS `v_salaries`;
DELETE FROM `employees` WHERE `salary`<3000 RETURNING `employee_id` AS `v_employee_ids`,`salary` AS `v_salaries`;
警告
注意：对于return/returning后面的参数个数与into后面的参数个数数量保证一致，否则转换出错。