elim2.sql

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

create or replace PROCEDURE elim2(v_product_id IN number)
IS
	v_id		  number;
	v_error_code      NUMBER;
  	v_error_message   VARCHAR2(255);
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;
		v_error_code := SQLCODE;
    		v_error_message := SQLERRM;
    		dbms_output.PUT_LINE (TO_CHAR(v_error_code)||': '|| v_error_message);
		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 elim2;
/