elim_inventory.sql

学习PL/SQL时收集的一些经典的SQL源代码

create or replace PROCEDURE elim_inventory(v_product_id IN number)
IS
	v_id	number;
BEGIN
	SELECT	id
		INTO	v_id
		FROM	s_product
		WHERE	id = v_product_id;
	DELETE FROM	s_inventory
		WHERE	product_id = v_product_id;
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		ROLLBACK;
		 dbms_output.put_line(TO_CHAR(v_product_id)||' is invalid.');
	WHEN TOO_MANY_ROWS THEN
		ROLLBACK;
		 dbms_output.put_line('Data corruption in S_PRODUCT.');
	WHEN OTHERS THEN
		ROLLBACK;
		 dbms_output.put_line('Other error occurred.');
END elim_inventory;
/

create table s_product(id number(4),name varchar2(30));

insert into s_product values (1,'aaa');
insert into s_product values (2,'bbb');
insert into s_product values (3,'ccc');
commit;

create table s_inventory(id number(4),product_id number(4),loc varchar2(30));

insert into s_inventory values(1,1,'beijing');
insert into s_inventory values(2,2,'shanghai');
insert into s_inventory values(3,3,'shenzhen');
insert into s_inventory values(4,4,'guangzhou');
commit;