Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle SQL Function: Adding local variables

In a stored PROCEDURE, I can add the v_bar local variable as follow:

CREATE OR REPLACE PROCEDURE A_PROC (
    foo        VARCHAR2
)

AS 
    v_bar  VARCHAR2(32);  

BEGIN

    SELECT FOO into v_bar WHERE ...;

END A_PROC ;

Question

How should v_bar be declared in the function below ?

CREATE OR REPLACE FUNCTION A_FUNC(
    foo        VARCHAR2
) RETURN NUMBER AS total NUMBER;

BEGIN
  -- Where to declare v_bar ?
  v_bar := 'bla';

END A_FUNC;
like image 694
Hey StackExchange Avatar asked Jul 11 '18 10:07

Hey StackExchange


People also ask

Can function have out parameter in Oracle?

A function can have OUT or IN OUT parameters, but this is bad coding practice. A function should have a return value and no out parameter.

How do you declare a local variable in PL SQL?

After the declaration, PL/SQL allocates memory for the variable's value and the storage location is identified by the variable name. Syntax for declaring variable: Following is the syntax for declaring variable: variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

Can you use variables in Oracle SQL?

There are two types of variable in SQL-plus: substitution and bind. This is substitution (substitution variables can replace SQL*Plus command options or other hard-coded text):

How can I assign multiple values to a variable in Oracle?

Actually all we need to do here is concatenate the v_out. "V_out:=v_out || item. My_name" and we are done.


2 Answers

Exactly the same; no difference. For example:

create or replace function a_func (foo in varchar2)
  return number 
as
  total number;
  -- v_bar varchar2(32);
  v_bar number;    --> should match RETURN datatype
begin
  select foo into v_bar from ...;

  total := v_bar * 1000;

  return v_bar;
end;  
like image 80
Littlefoot Avatar answered Sep 27 '22 22:09

Littlefoot


You can declare a variable after the 'as' or 'is' clause.

CREATE OR REPLACE FUNCTION A_FUNC(
foo        VARCHAR2
) RETURN NUMBER 
as
v_bar     VARCHAR2(32);  --Variable declared
v_bar_no  NUMBER(10);    --Variable declared
BEGIN
  -- Now you can use this variable
  v_bar := 'bla';

END A_FUNC;
like image 38
Ashish Mishra Avatar answered Sep 27 '22 22:09

Ashish Mishra