CODEWITHSUNDEEP
oracle-databaseplsql
Tpk43
Tpk43

Reputation: 331

Select Variable in Oracle

Here is what I will do in TSQL

declare @Init_Cnt int =1,@Tot_cnt int

set @Tot_cnt = (select count(distinct column_name) from mytable)

while (@init_cnt <= @tot_cnt)
begin
  //Insert statement

end
set @init_cnt = @init_cnt+1;

This is what I tried in Oracle

declare
    v_Init_cnt INT;
    v_Tot_cnt int;

    set v_Init_cnt := 1;

begin
   select count(distinct column_name) v_Tot_cnt into  from mytable
end

begin
    while v_Init_cnt 
    loop
        dbms_output.put_line ('COUNT': || v_Init_cnt );
        v_Init_cnt 
        exit when v_Init_cnt <= v_Tot_cnt ;
    end loop;
end;

How will I achieve my Tsql version in Oracle? Am I doing it right? If I wanna select only my variable say

select v_Tot_cnt from dual; is not working how can I do that?

Upvotes: 0

Views: 84

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

It would look something like this in Oracle.

DECLARE
        v_Init_Cnt number(10) :=1;
        v_Tot_cnt  number(10);
BEGIN
        select count(distinct column_name) INTO v_Tot_cnt from mytable;

        WHILE (v_Init_Cnt <= v_Tot_cnt)
        LOOP
                --Insert statement
                v_Init_Cnt := v_Init_Cnt+1;
        END LOOP;
END;

Upvotes: 1

Related Questions