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
declare all_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;
declare all_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 ;
bulk collect 用法
最新推荐文章于 2021-04-12 08:23:52 发布