i need to create variables for my query
i have a inner join query Es
select * from ...
inner join
etc ..
where username = 'jack'
and value 1 = 'work'
i'd like to create on top variables for easy change value in where condition.
I have used Declare and set but show me the error PLS-00103 for group having intersect minus order start union where connect
how can i do for create variables ? thanks a lot for help, sorry for my English is not my first languages.
You can use Anonymus PL/SQL block for your above question. But if you are using select inside block, you should use INTO clause along with it.
E.g.--
CREATE TABLE T1 (AA VARCHAR2(20));
insert into t1 values('YYY');
commit; -- table created with sample value.
Now, use Anonymus pl-sql block to call select -
DECLARE
USERNAME VARCHAR2(20);
VALUE1 VARCHAR(20);
BEGIN
USERNAME:='YYY';
SELECT aa into VALUE1 FROM T1 WHERE AA=USERNAME;
dbms_output.put_line(VALUE1);
end;
Now, in your case since you are using select * you can use ROWTYPE attribute to select the row from your table.
Check ROWTYPE example here. http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/fundamentals.htm#BEIBGEFH
You have to embeded statements between begin and end.
Declare varchar2(30) vUserName := 'jack';
Declare ...
Begin
select * from ...
inner join
etc ..
where username = vUserName
and value 1 = vValue1
End
vusername varchar2(30) := 'jack';