|
SQL>
SQL>
SQL>
SQL> create table product(
2 product_id number(4) not null,
3 product_description varchar2(20) not null
4 );
Table created.
SQL>
SQL> insert into product values (1,'Java');
1 row created.
SQL> insert into product values (2,'Oracle');
1 row created.
SQL> insert into product values (3,'C#');
1 row created.
SQL> insert into product values (4,'Javascript');
1 row created.
SQL> insert into product values (5,'Python');
1 row created.
SQL>
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>
SQL> create table org_company_site(
2 company_id number(8) not null,
3 site_no number(4) not null
4 );
Table created.
SQL> insert into org_company_site values (1001,1);
1 row created.
SQL> insert into org_company_site values (1002,2);
1 row created.
SQL> insert into org_company_site values (1003,3);
1 row created.
SQL> insert into org_company_site values (1004,1);
1 row created.
SQL> insert into org_company_site values (1004,2);
1 row created.
SQL> insert into org_company_site values (1004,3);
1 row created.
SQL> insert into org_company_site values (1005,1);
1 row created.
SQL> insert into org_company_site values (1005,4);
1 row created.
SQL> insert into org_company_site values (1005,5);
1 row created.
SQL> insert into org_company_site values (1006,1);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE p_print_report(p_report_no NUMBER,p_title VARCHAR2)
2 IS
3 TYPE rc IS REF CURSOR;
4 refCursorValue rc;
5 v_product_description VARCHAR2(20);
6 v_company_short_name VARCHAR2(30);
7 BEGIN
8 IF (p_report_no =1)THEN
9 OPEN refCursorValue FOR SELECT h.product_description,o.company_short_name
10 FROM company o,product h
11 WHERE o.product_id =h.product_id
12 AND 1 < (SELECT count(os.site_no)
13 FROM org_company_site os
14 WHERE os.company_id =o.company_id);
15 ELSIF (p_report_no =2)THEN
16 OPEN refCursorValue FOR SELECT h.product_description,o.company_short_name
17 FROM company o,product h
18 WHERE o.product_id =h.product_id
19 AND NOT EXISTS
20 (SELECT *
21 FROM company o1
22 WHERE o1.company_id =o.company_id
23 AND o1.product_id =2 );
24 END IF;
25 dbms_output.put_line(p_title);
26 dbms_output.put_line(rpad('-',length(p_title),'-'));
27 dbms_output.put_line(rpad('Hierarchy',20,' ')||' '||rpad('Description',30,' '));
28 dbms_output.put_line(rpad('-',20,'-')||' '||rpad('-',30,'-'));
29 LOOP
30 FETCH refCursorValue INTO v_product_description,v_company_short_name;
31 EXIT WHEN refCursorValue%NOTFOUND;
32 dbms_output.put_line(rpad(v_product_description,20,' ')||' '||
33 rpad(v_company_short_name,30,' '));
34 END LOOP;
35 CLOSE refCursorValue;
36 END p_print_report;
37 /
Procedure created.
SQL>
SQL>
SQL> drop table org_company_site;
Table dropped.
SQL>
SQL> drop table company;
Table dropped.
SQL>
SQL> drop table product;
Table dropped.
SQL>
SQL>
|