英文:
Failure to Use a Stored Procedure in GridDB
问题
由于我之前曾与关系型数据库一起工作，使用存储过程是一种常见的做法，但相当长一段时间以来，我已经转向使用GridDB，一个NoSQL数据库。我最近遇到的问题是使用存储过程时出现了以下错误：
[错误代码]: 151001[描述]（语法错误）
我认为在GridDB中存储过程是不支持的操作。是否有一种方法可以在GridDB中创建用户定义的函数（UDF），有人可以帮助提供下面我的代码示例的编辑，以在查询期间执行自定义数据处理操作吗？
import com.toshiba.mwcloud.gs.Collection;import com.toshiba.mwcloud.gs.GSException;import com.toshiba.mwcloud.gs.GridStore;import com.toshiba.mwcloud.gs.GridStoreFactory;import com.toshiba.mwcloud.gs.Query;import com.toshiba.mwcloud.gs.RowKey;import com.toshiba.mwcloud.gs.RowSet;static class Order{int order_id;int total_amount;}Properties props = new Properties();props.setProperty("notificationAddress", "239.0.0.1");props.setProperty("notificationPort", "31999");props.setProperty("clusterName", "defaultCluster");props.setProperty("user", "admin");props.setProperty("password", "admin");GridStore store = GridStoreFactory.getInstance().getGridStore(props);CREATE PROCEDURE CalculateOrderTotal(IN orderId INT, OUT totalAmount DECIMAL(10, 2))BEGINDECLARE itemPrice DECIMAL(10, 2);SET totalAmount = 0;DECLARE done INT DEFAULT FALSE;SELECT price FROM OrderItems WHERE order_id = orderId;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;ENDquery = f"SELECT order_id, total_amount {CalculateOrderTotal}(quantity, price) AS total_price FROM {myCluster}"results = container.query(query)
英文:
Since I have previously worked with relational databases, using stored procedures is a common practice, but for quite a long time, I have shifted to using GridDB, a NoSQL database. The problem that I recently faced was the use of a stored procedure which gave me the following error:
[Error code]: 151001[Description] (Syntax error)
I think the stored procedure is an unsupported operation in GridDB. Is there a way of creating a user-defined function (UDF) in GridDB, and can anyone help provide edits in my code example below on how to use it to perform a custom data processing operation during the query?
import com.toshiba.mwcloud.gs.Collection;import com.toshiba.mwcloud.gs.GSException;import com.toshiba.mwcloud.gs.GridStore;import com.toshiba.mwcloud.gs.GridStoreFactory;import com.toshiba.mwcloud.gs.Query;import com.toshiba.mwcloud.gs.RowKey;import com.toshiba.mwcloud.gs.RowSet;static class Order{int order_id;int total_amount;}Properties props = new Properties();props.setProperty("notificationAddress", "239.0.0.1");props.setProperty("notificationPort", "31999");props.setProperty("clusterName", "defaultCluster");props.setProperty("user", "admin");props.setProperty("password", "admin");GridStore store = GridStoreFactory.getInstance().getGridStore(props);CREATE PROCEDURE CalculateOrderTotal(IN orderId INT, OUT totalAmount DECIMAL(10, 2))BEGINDECLARE itemPrice DECIMAL(10, 2);SET totalAmount = 0;DECLARE done INT DEFAULT FALSE;SELECT price FROM OrderItems WHERE order_id = orderId;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;ENDquery = f"SELECT order_id, total_amount {CalculateOrderTotal}(quantity, price) AS total_price FROM {myCluster}"results = container.query(query)
答案1
得分: 1
我将为您创建一个UDF，该UDF根据订单中每个商品的数量和价格计算总价格：
import com.toshiba.mwcloud.gs.GridStore;import com.toshiba.mwcloud.gs.GridStoreFactory;import com.toshiba.mwcloud.gs.GridStoreFactoryFactory;import com.toshiba.mwcloud.gs.Row;import com.toshiba.mwcloud.gs.RowSet;import com.toshiba.mwcloud.gs.TimeSeries;String totalPriceFunction = "function calculateTotalPrice(quantity, price) { return quantity * price; }";GridStore store = GridStoreFactory.getInstance().getGridStore(props);store.putFunction("calculateTotalPrice", totalPriceFunction, null);String containerName = "OrderItems";TimeSeries<Row> container = store.putTimeSeries(containerName, Row.class);
然后，在您的查询中调用它以计算总价格：
import com.toshiba.mwcloud.gs.Query;import com.toshiba.mwcloud.gs.RowKey;import com.toshiba.mwcloud.gs.RowSet;String queryStr = String.format("SELECT order_id, calculateTotalPrice(quantity, price) AS total_price FROM %s", containerName);Query<Row> query = store.query(queryStr, Row.class);RowSet<Row> rs = query.fetch();while (rs.hasNext()) {Row row = rs.next();int orderId = row.getInteger("order_id");double totalPrice = row.getDouble("total_price");// 使用 orderId 和 totalPrice 进行其他操作}
请注意，上述代码是用Java编写的，并且在查询中使用了创建的UDF函数来计算总价格。
英文:
I would create a udf that calculates the total price based on the quantity and price of each item in the order:
import com.toshiba.mwcloud.gs.GridStore;import com.toshiba.mwcloud.gs.GridStoreFactory;import com.toshiba.mwcloud.gs.GridStoreFactoryFactory;import com.toshiba.mwcloud.gs.Row;import com.toshiba.mwcloud.gs.RowSet;import com.toshiba.mwcloud.gs.TimeSeries;String totalPriceFunction = "function calculateTotalPrice(quantity, price) { return quantity * price; }";GridStore store = GridStoreFactory.getInstance().getGridStore(props);store.putFunction("calculateTotalPrice", totalPriceFunction, null);String containerName = "OrderItems";TimeSeries<Row> container = store.putTimeSeries(containerName, Row.class);
then call it in your query to calculate total price:
import com.toshiba.mwcloud.gs.Query;import com.toshiba.mwcloud.gs.RowKey;import com.toshiba.mwcloud.gs.RowSet;String queryStr = String.format("SELECT order_id, calculateTotalPrice(quantity, price) AS total_price FROM %s", containerName);Query<Row> query = store.query(queryStr, Row.class);RowSet<Row> rs = query.fetch();while (rs.hasNext()) {Row row = rs.next();int orderId = row.getInteger("order_id");double totalPrice = row.getDouble("total_price");// do something with orderId and totalPrice}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论