create table emp1_tb1(last_name varchar2(20),first_name varchar2(10),salary number(10))
beginfor i in 3000..102999 loop insert into emp1_tb1(last_name,first_name,salary) values('car1'||(i-3000),'wu'||(103000-i),i);end loop;commit;end;
select count(distinct last_name) "Distinct Last Name" from emp1_tb1
declareall_rows1 number(10);temp_last_name emp1_tb1.last_name%type;begin all_rows1 := 0; temp_last_name:=' '; --cursor for distinct count for cur in(select last_name from emp1_tb1 order by last_name) loop if cur.last_name!=temp_last_name then all_rows1:=all_rows1 +1; end if; temp_last_name := cur.last_name; end loop; dbms_output.put_line('all_rows1 are'||all_rows1); end;
declareall_rows1 number(10);type last_name_tab is table of emp1_tb1.last_name%type index by binary_integer;last_name_arr last_name_tab;temp_last_name emp1_tb1.last_name%type;begin all_rows1 := 0; temp_last_name := ' '; select last_name bulk collect into last_name_arr from emp1_tb1; for i in 1..last_name_arr.count loop if temp_last_name!=last_name_arr(i) then all_rows1 := all_rows1 + 1; end if; temp_last_name := last_name_arr(i); end loop; dbms_output.put_line('all_rows are '||all_rows1); end ;
