Select Variable in Oracle

Question

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?

In the T-SQL version, should set @init_cnt = @init_cnt+1; be inside the loop? — William Robertson
– William Robertson, Commented Feb 12, 2018 at 18:39
There is no set in PL/SQL: set x = y; becomes x := y; This can be part of declaration, e.g. v_init_cnt integer := 1;. You also don't surround every SQL statement with begin/end. — William Robertson
– William Robertson, Commented Feb 12, 2018 at 18:42

Kaushik Nayak · Accepted Answer · 2018-02-12 18:32:28Z

1

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;

answered Feb 12, 2018 at 18:32

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select Variable in Oracle

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related