How to pass the value of a variable defined inside a stored procedure to a SQL statement?

raelyn

A variable is defined inside the stored procedure, and its value is used in the SQL statement, which is written as follows:
CREATE OR REPLACE PROCEDURE "GETMAXFLUXINWITHHYID" (
 startTime in date, endTime in date
)
as
minHyId number;
begin
WHILE (minHyId <= 20) LOOP
         SELECT * FROM TABLE1 WHERE HY_ID = minHyId AND PROBE_TIME BETWEEN startTime AND endTime;
         minHyId: = minHyId + 1
END LOOP;
end;

Compilable always prompts errors. How should I write it correctly?
I don't know what kinds of writing methods can be used for variable transfer in stored procedures?

我是杀人狂

declare @str varchar (1000)
set @ str = 'select * from tablename'
exec (@str)

whbilly

You can set the parameters to be returned to out type parameters, or directly change to function

iamglf

CREATE OR REPLACE PROCEDURE "GETMAXFLUXINWITHHYID" (
 startTime in date, endTime in date
)
as
minHyId number;
inum number;
begin
WHILE (minHyId <= 20) LOOP
         SELECT count (*) into inum FROM TABLE1 WHERE HY_ID = minHyId AND PROBE_TIME BETWEEN startTime AND endTime;-The query result is not saved in the variable
         minHyId: = minHyId + 1; --Less semicolon
END LOOP;
end;

iamglf

Compile error, use show error to view detailed information

idpublic

我是杀人狂
 
 
   declare @str varchar (1000)
set @ str = 'select * from tablename'
exec (@str)
  
 
-
Caixia, this is sqlserver syntax


WHILE (minHyId <= 20) LOOP
         SELECT * FROM TABLE1 WHERE HY_ID = minHyId AND PROBE_TIME BETWEEN startTime AND endTime;
         minHyId: = minHyId + 1
END LOOP;
---
There is no int in front of select or behind
Definitely wrong

SELECT * FROM TABLE1 WHERE HY_ID = minHyId AND PROBE_TIME BETWEEN startTime AND endTime;
Input variables are used in this way