更新表pl/sql [英] update table pl/sql
问题描述
我想使用pl/sql函数更新下表，问题是我设法编写了触发代码，但我想改用'function'来重写它. 我希望客户5将他们的订单从30个增加到200个，并允许用户输入:
I would like to update the following table using pl/sql function, the thing is I managed to write a trigger code but I want to re-write it using 'function' instead. I would like for Customer 5 to increase their order from 30 to 200. and Enable the user to type in:
1)customer_ID的数字5和 2)200为更新数量. 并在更新前后为客户5打印总数量.
1) The number 5 for customer_ID and 2) 200 for the updated quantity. and print out the total quantity for customer 5 before and after the update.
Create table sales (customer_ID number(10), product_ID number(10), quantity number(10));
INSERT INTO sales (customer_ID, product_ID, quantity) Values(3,1,23);
INSERT INTO sales (customer_ID, product_ID, quantity) Values(1,2,34);
INSERT INTO sales (customer_ID, product_ID, quantity) Values(1,3,654);
INSERT INTO sales (customer_ID, product_ID, quantity) Values(3,7,32);
INSERT INTO sales (customer_ID, product_ID, quantity) Values(4,3,23);
INSERT INTO sales (customer_ID, product_ID, quantity) Values(3,3,111);
INSERT INTO sales (customer_ID, product_ID, quantity) Values(5,4,6);
我编写的触发代码:
create or replace trigger quantity_change
before insert or update of quantity on sales
for each row
WHEN (NEW.customer_id > 0)
DECLARE
qua number;
BEGIN
qua := :NEW.quantity - :OLD.quantity;
dbms_output.put_line('Old quangtity: ' || :OLD.quantity);
dbms_output.put_line('New quantity: ' || :NEW.quantity);
dbms_output.put_line('diiference quangtity: ' || qua);
END;
UPDATE sales
SET quantity = 200 WHERE customer_id = 5;
我设法编写了此程序，但仍然陷于困境，不知道如何启用使用
I managed to write this procedure but still stuck, dont know how to enable use
CREATE or replace PROCEDURE Updatesales
(
customer_ID number,
product_ID number,
quantity number)
AS
BEGIN
UPDATE sales
SET quantity= 100
WHERE customer_id= 4;
END;
我想使用一个函数来解决问题，一个函数会像这样
I want to use a function to solve the issue , a function would be somthing like this
CREATE [OR REPLACE] FUNCTION function_name [(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype {IS | AS} BEGIN < function_body > END [function_name];
请咨询
推荐答案
您的过程未使用您声明的参数；身体应该更像:
Your procedure isn't using the parameters you have declared; the body should be more like:
UPDATE sales
SET quantity= quantity
WHERE customer_id= customer_id;
...但是那样做不会如您期望的那样，因为您对参数和列使用了相同的名称(并且根本没有引用产品ID)，因此表中的每一行都会更新其当前值.为了避免混淆，通常在形式参数名称中使用前缀，尽管在引用它们时也可以显式使用过程名称.
... but that won't do what you expect because you've used the same names for the parameters and columns (and haven't referenced the product ID at all), so every row in the table will be updated with its current value. It's common to use a prefix for your formal parameter names to avoid that confusion, though you can also use the procedure name explicitly when you refer to them.
您说过您想要一个功能，但不清楚原因.通常在过程中而不是在函数中修改数据，并且如果函数执行任何DML，则不能从查询中调用它，而必须在PL/SQL上下文中调用它.因此，我将从一个过程开始.
You said you want a function but it isn't clear why. It's conventional to modify data in procedures and not in functions, and if a function does do any DML then it can't be called from a query and would have to be called in a PL/SQL context. So I'll start with a procedure.
您说过要在更新前后打印"数量.该过程不应该那样做；您不应假定用户或客户端可以处理dbms_output或将其启用.您可以使用OUT参数将更新前的值返回给调用方:
You said you wanted to 'print out' the quantity before and after the update. The procedure shouldn't do that; you should not assume that the user or client can handle dbms_output or will have it enabled. You could use an OUT parameter to return the pre-update value to the caller though:
CREATE OR REPLACE PROCEDURE update_sales
(
p_customer_id IN sales.customer_id%type,
p_product_id IN sales.product_id%type,
p_new_quantity IN sales.quantity%type,
p_old_quantity OUT sales.quantity%type
) AS
BEGIN
SELECT quantity
INTO p_old_quantity
FROM sales
WHERE customer_id = p_customer_id
AND product_id = p_product_id
FOR UPDATE;
UPDATE sales
SET quantity = p_new_quantity
WHERE customer_id = p_customer_id
AND product_id = p_product_id;
END;
/
这会将数量的当前值存储到OUT变量中，并且还使用for update锁定记录以在您处理该值时停止更改值(在这里可能过大了，但是您想学习... )
This gets the current value of the quantity into an OUT variable, and also locks the record with for update to stop the value changing while you're working on it (probably overkill here, but you want to learn...)
然后使用传入的新值更新同一行.这就是使用客户ID和产品ID再次查找该行，并且如果要进行实验，可以以不同的方式进行操作-将rowid放入另一个局部变量从您的第一个查询中获取并用于更新，或使用光标等.
It then updates the same row with the new value that was passed in. That is finding the row again using the customer and product IDs, and you could do that differently if you want to experiment - get the rowid into another local variable from your first query and use that for the update, or use a cursor, etc.
您可以从一个匿名块中调用它作为测试，并使用dbms_output显示旧值和新值.再次，不要在生产代码中使用dbms_output，仅用于调试:
You could call it from an anonymous block as a test, and use dbms_output to show the old and new values; again, don't use dbms_output in production code, only for debugging:
SET serveroutput ON
DECLARE
l_customer_id sales.customer_id%type;
l_product_id sales.product_id%type;
l_new_quantity sales.quantity%type;
l_old_quantity sales.quantity%type;
BEGIN
l_customer_id := 5;
l_product_id := 4;
l_new_quantity := 200;
update_sales(l_customer_id, l_product_id, l_new_quantity, l_old_quantity);
dbms_output.put_line('Quantity changed from ' || l_old_quantity
|| ' to ' || l_new_quantity
|| ' (' || to_char(l_new_quantity - l_old_quantity, 'FMS999') || ')');
END;
/
PL/SQL procedure successfully completed.
Quantity changed from 6 to 200 (+194)
您可以以类似的方式使用绑定变量从应用程序中调用此方法，并让应用程序显示值.
You can call this from an application, using bind variables, in a similar way, and have the application display the values.
请注意，我尚未提交或回滚所做的更改，并且另一个尝试以相同值调用该过程的会话将阻塞，直到我这样做为止；否则，请执行以下步骤.但在运行时会看到新值(200).我也没有在该过程中进行任何验证或异常处理，因此调用者需要同时进行这两个操作.
Note that I haven't committed or rolled back the changes, and another session trying to call the procedure with the same values will block until I do; but will then see the new value (200) when it does run. I also haven't done any validation or exception handling in the procedure, so the caller needs to do both.
您可以使它成为一个返回旧值而不使用OUT参数的函数，但是您需要以类似的方式调用它，并且通常人们不希望函数能够更改任何内容-仅返回当前状态.但是，如果确实要这样做，则需要修改声明以具有返回类型和局部变量.选择旧值到该局部变量中；然后也将其返回:
You could make this a function that returns the old value instead of using an OUT parameter, but you'd need to call it in a similar way, and generally people don't expect functions to change anything - just to return the current state. But if this is really how you want to do it, you need to modify the declaration to have a return type and local variable; select the old value into that local variable; and then return that too:
CREATE OR REPLACE FUNCTION update_sales
(
p_customer_id IN sales.customer_id%type,
p_product_id IN sales.product_id%type,
p_new_quantity IN sales.quantity%type
)
RETURN sales.quantity%type
AS
l_old_quantity sales.quantity%type;
BEGIN
SELECT quantity
INTO l_old_quantity
FROM sales
WHERE customer_id = p_customer_id
AND product_id = p_product_id;
UPDATE sales
SET quantity = p_new_quantity
WHERE customer_id = p_customer_id
AND product_id = p_product_id;
RETURN l_old_quantity;
END;
/
您仍然必须从PL/SQL上下文(或类似JDBC可调用语句)中调用它:
You still have to call it from a PL/SQL context (or something like a JDBC callable statement):
DECLARE
l_old_quantity sales.quantity%type;
BEGIN
l_old_quantity := update_sales(5, 4, 200);
dbms_output.put_line('Quantity was ' || l_old_quantity);
END;
/
PL/SQL procedure successfully completed.
Quantity was 6
您无法从普通SQL调用它，因为它正在执行DML操作:
You can't call it from plain SQL because it is doing a DML operation:
select update_sales(5, 4, 200) from dual;
Error report -
SQL Error: ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "MY_SCHEMA.UPDATE_SALES", line 17
14551. 00000 - "cannot perform a DML operation inside a query "
*Cause: DML operation like insert, update, delete or select-for-update
cannot be performed inside a query or under a PDML slave.
*Action: Ensure that the offending DML operation is not performed or
use an autonomous transaction to perform the DML operation within
the query or PDML slave.
这篇关于更新表pl/sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
