CODEWITHSUNDEEP
sqloracle-database
Sheen
Sheen

Reputation: 3441

In Oracle procedure, how to define local variable and use it after DECLARE block?

I want to create a stored proc like below

create or replace PROCEDURE AAA
(
  cv_1 IN OUT SYS_REFCURSOR
)
as
BEGIN

  DECLARE ord_t VARCHAR2(20);
  begin
  SELECT order_type into ord_t from BT;
  dbms_output.Put_line(ord_t);
  end;

  if ord_t = 'xxx' THEN
      open cv_1 for
      select * from BA;
  else
      open cv_1 for
      select * from BB;
  END if;

END;

But there is error saying ord_t must be declared. How do I write this correctly? The purpose of the proc is to return results from either BA or BB based on the value of ord_t.

Upvotes: 0

Views: 816

Answers (1)

Chrᴉz remembers Monica
Chrᴉz remembers Monica

Reputation: 1904

You need to declare the variable before the BEGIN-tag in which it should be used

create or replace PROCEDURE AAA(cv_1 IN OUT SYS_REFCURSOR)
as
  ord_t VARCHAR2(20);
BEGIN
  SELECT order_type into ord_t from BT;
  dbms_output.Put_line(ord_t);

  if ord_t = 'xxx' THEN
      open cv_1 for
      select * from BA;
  else
      open cv_1 for
      select * from BB;
  END if;

END;

In procedures, the CREATE OR REPLACE PROCEDURE name(params) AS replaces the DECLARE of a block. Therefor, you don't need it here.

The definition of a block is 

DECLARE or CREATE FUNCTION/PROCEDURE
  (variables in block)
BEGIN
  (code using variables)
END

Examples:

declare
  myvar number(1);
begin
  --begin scope of myvar--
  select 1 into myvar from dual;
  --end scope of myvar--
end;

create procedure myproc (var1 in number)
as
  myvarouter number(1);
begin
  --begin scope myvarouter--
  declare
    myvarinner number(1);
  begin
    --begin scope myvarinner--
    select 1 into myvarinner from dual;
    select 1 into myvarouter from dual;
    --end scope myvarinner--
  end;
  select 1 into myvarouter from dual;
  --end scope myvarouter--
end;

Another question with the same content can also be found here

Upvotes: 3

Related Questions