SQL>
SQL> create table company(
2 product_id number(4) not null,
3 company_id NUMBER(8) not null,
4 company_short_name varchar2(30) not null,
5 company_long_name varchar2(60)
6 );
Table created.
SQL> insert into company values(1,1001,'A Inc.','Long Name A Inc.');
1 row created.
SQL> insert into company values(1,1002,'B Inc.','Long Name B Inc.');
1 row created.
SQL> insert into company values(1,1003,'C Inc.','Long Name C Inc.');
1 row created.
SQL> insert into company values(2,1004,'D Inc.','Long Name D Inc.');
1 row created.
SQL> insert into company values(2,1005,'E Inc.','Long Name E Inc.');
1 row created.
SQL> insert into company values(2,1006,'F Inc.','Long Name F Inc.');
1 row created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE org_proc
2 (p_flag_in VARCHAR2,
3 p_product_id NUMBER,
4 p_company_id NUMBER,
5 p_company_short_name VARCHAR2,
6 p_company_long_name VARCHAR2)
7 IS
8 v_error_code NUMBER;
9 BEGIN
10 IF (p_flag_in ='I')THEN
11 BEGIN
12 INSERT INTO company VALUES(p_product_id,p_company_id,p_company_short_name,p_company_long_name);
13 EXCEPTION WHEN OTHERS THEN
14 v_error_code :=SQLCODE;
15 IF v_error_code =-1 THEN
16 RAISE_APPLICATION_ERROR(-20000,'Organization '||TO_CHAR(p_company_id)||' already exists.');
17 ELSIF v_error_code =-2291 THEN
18 RAISE_APPLICATION_ERROR(-20001,'Invalid Hierarchy Code '||TO_CHAR(p_product_id)||' specified.');
19 END IF;
20 END;
21 ELSIF (p_flag_in ='C')THEN
22 BEGIN
23 UPDATE company
24 set company_short_name =p_company_short_name,
25 company_long_name =p_company_long_name
26 WHERE product_id =p_product_id
27 AND company_id =p_company_id;
28 IF SQL%NOTFOUND THEN
29 RAISE_APPLICATION_ERROR(-20002,'Organization '||TO_CHAR(p_company_id)||' does not exist.');
30 END IF;
31 END;
32 ELSIF (p_flag_in ='D')THEN
33 BEGIN
34 DELETE company
35 WHERE product_id =p_product_id
36 AND company_id =p_company_id;
37 IF SQL%NOTFOUND THEN
38 RAISE_APPLICATION_ERROR(-20003,'Organization '||TO_CHAR(p_company_id)||' does not exist.');
39 END IF;
40 EXCEPTION WHEN OTHERS THEN
41 v_error_code :=SQLCODE;
42 IF v_error_code =-2292 THEN
43 RAISE_APPLICATION_ERROR(-20004,'Organization '||TO_CHAR(p_company_id)||' site details defined for it.');
44 END IF;
45 END;
46 END IF;
47 END;
48 /
Procedure created.
SQL>
SQL> DECLARE
2 v_product_id NUMBER := 6;
3 v_company_id NUMBER := 1010;
4 v_company_short_name VARCHAR2(30):= 'Office Inc.';
5 v_company_long_name VARCHAR2(60):= 'Office Inc.';
6 excep1 EXCEPTION;
7 PRAGMA EXCEPTION_INIT(excep1,-20000);
8 excep2 EXCEPTION;
9 PRAGMA EXCEPTION_INIT(excep2,-20001);
10 BEGIN
11 org_proc('I',v_product_id,v_company_id,v_company_short_name,v_company_long_name);
12 EXCEPTION
13 WHEN excep1 or excep2 THEN
14 DBMS_OUTPUT.PUT_LINE(SQLERRM);
15 WHEN OTHERS THEN
16 DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLCODE)||SQLERRM);
17 END;
18 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table company;
Table dropped.
SQL>