用于在Oracle DBMS中删除记录的存储过程 [英] Stored procedure for deleting records in Oracle DBMS
问题描述
试图弄清楚如何创建一个存储过程,该存储过程将从客户表,customer_order表和line_item表中删除一个客户.这是表格.
Trying to figure out how to create a stored procedure that will delete a customer from the customer table, customer_order table and line_item table. Here are the tables.
CREATE TABLE customer(
customer_ID DECIMAL(10) NOT NULL,
customer_first VARCHAR(30),
customer_last VARCHAR(40),
customer_total DECIMAL(12, 2),
PRIMARY KEY (customer_ID));
CREATE TABLE customer_order (
order_id DECIMAL(10) NOT NULL,
customer_id DECIMAL(10) NOT NULL,
order_total DECIMAL(12,2),
order_date DATE,
PRIMARY KEY (ORDER_ID),
FOREIGN KEY (CUSTOMER_ID) REFERENCES customer);
CREATE TABLE line_item(
order_id DECIMAL(10) NOT NULL,
item_id DECIMAL(10) NOT NULL,
item_quantity DECIMAL(10) NOT NULL,
line_price DECIMAL(12,2),
PRIMARY KEY (ORDER_ID, ITEM_ID),
FOREIGN KEY (ORDER_ID) REFERENCES customer_order,
FOREIGN KEY (ITEM_ID) REFERENCES item);
到目前为止,我拥有的SP
Here what I have so far for the SP
CREATE OR REPLACE PROCEDURE DELETE_CUSTOMER(
customer_id_arg IN DECIMAL,
first_name_arg IN VARCHAR,
last_name_arg IN VARCHAR
) IS
BEGIN
DELETE FROM CUSTOMER
WHERE customer_id IN (SELECT customer_last
FROM Customer
WHERE customer_id = customer_id_arg);
END;
当我尝试运行它时,它不会删除记录,而我对于该怎么办感到困惑.
When I try to run it, it doesn't delete a record and I am confused as for what to do.
推荐答案
下面是一个示例代码,用于使用database_dictionary从父级表级联到所有子级表中删除数据,我写它很有趣;-) 请注意从人的最后一行删除,其中pk ='38B567E2909447868ABDDF500B78F2A3';可以很容易地概括.要补充的一点是,如果您的主要约束是两列或更多列,则此脚本不起作用...
Here a sample code to delete data from a parent table cascading to all child tables using the database_dictionary, I had lots of fun writing it ;-) Note the last line delete from person where pk = '38B567E2909447868ABDDF500B78F2A3'; can easily be generalised. Another thing to add, this script doesn't work if you have primary constraints of two columns or more...
declare
TYPE cur_typ IS REF CURSOR;
procedure delete_from_sub_table_first(p_current_table_name varchar2, l_parent_key_value varchar2) is
c cur_typ;
child_table_pm_key_value varchar2(255);
begin
for childConsRecord in (select ac.table_name child_table, acc.column_name Child_column, rac.table_name, racc.COLUMN_NAME, (select column_name from ALL_CONS_COLUMNS accpm, all_constraints acpm where accpm.constraint_name = acpm.constraint_name and acpm.CONSTRAINT_TYPE = 'P' and ac.TABLE_NAME = acpm.TABLE_NAME) child_table_pm_key
from ALL_CONS_COLUMNS acc, all_constraints ac, ALL_CONS_COLUMNS racc, all_constraints rac
where acc.CONSTRAINT_NAME = ac.CONSTRAINT_NAME
and ac.CONSTRAINT_TYPE = 'R'
and racc.CONSTRAINT_NAME = rac.CONSTRAINT_NAME
and rac.constraint_name = ac.R_CONSTRAINT_NAME
and rac.table_name = p_current_table_name) loop
OPEN c FOR 'select ' || childConsRecord.child_table_pm_key || ' child_value FROM ' || childConsRecord.child_table || ' where ' || childConsRecord.Child_column || ' = :1' using l_parent_key_value;
LOOP
FETCH c INTO child_table_pm_key_value;
EXIT WHEN c%NOTFOUND;
-- process row here
delete_from_sub_table_first(childConsRecord.child_table, child_table_pm_key_value);
END LOOP;
close c;
EXECUTE IMMEDIATE 'DELETE FROM ' || childConsRecord.child_table || ' where ' || childConsRecord.Child_column || ' = :1' using l_parent_key_value;
end loop;
end;
begin
delete_from_sub_table_first('PERSON', '38B567E2909447868ABDDF500B78F2A3');
delete from person where pk = '38B567E2909447868ABDDF500B78F2A3';
end;
/
这篇关于用于在Oracle DBMS中删除记录的存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!