 Variable passing please explain difference between T-SQL and PL/SQL [message #208850] |
Tue, 12 December 2006 06:21  |
tdudley
Messages: 6
Registered: December 2006
Location: Barrie, Ontario
|
Junior Member |
|
|
I'm trying to run a query where I just do a simple variable replacement. I use this to test logic of code against different values if I must manually replace the value in many places. For simplicity I've only used the variable in one place below.
In Transact SQL, used by MS SQL Server, I would just do a declare then a set command then use the variable.
As a simplified example this works in T-SQL:
declare @freq int;
set @freq=5000;
select (round((@freq+2.15774) / 4.3125,0)-6)*2+1;
This returns 2309.
In PL/SQL:
select ((ROUND( (5000+2.15774)/4.3125 )-6)*2)+1 as Zippy from DUAL;
This also returns a value of 2309. Note 5000 used instead of a variable.
All I wish to do is pass the number in a similar fashion to TSQL but it fails.
I get the message PLS-00428: an INTO clause is expected in this SELECT statement.
declare freq int :=5000;
begin
select ((ROUND( (freq+2.15774)/4.3125 )-6)*2)+1 as Zippy from DUAL; end;
I do not have the permissions to create functions so how can accomplish passing a variable in PL/SQL?
Thanks in advance for your help.
|
|
|
|
| Re: Variable passing please explain difference between T-SQL and PL/SQL [message #208881 is a reply to message #208850] |
Tue, 12 December 2006 07:19  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
In pl/sql you are not required to do calculations as Select statements - you can (and absolutely should - it's much quicker) just execute the required statement:declare
freq int :=5000;
begin
freq := ((ROUND( (freq+2.15774)/4.3125 )-6)*2)+1;
end;
If you absolutely insist on using a SELECT statment, you need to select it INTO a value in pl/sql - this is to give you control over where and into what data types the data returned from a query goes:declare
freq int :=5000;
begin
select ((ROUND( (freq+2.15774)/4.3125 )-6)*2)+1 as Zippy
INTO freq
from DUAL;
end; |
|
|
|
|
|
| Re: Variable passing please explain difference between T-SQL and PL/SQL [message #208889 is a reply to message #208886] |
Tue, 12 December 2006 07:50 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Pl/Sql does not (without making it) return anything to the user - this is because it is generally called from other pieces of pl/sql or some form of GUI, and you wouldn't want it outputing lots of irrelevant details.
There are several ways of getting the result of your calculation back (create the whole thing as a function and return the value, use DBMS_OUTPUT to display it in SQL*Plus, update some part of the databse with it, raise it as the Error message of a use defined exception) - if you can give us some clue what you're trying to do, we can help more.
|
|
|
|
| Re: Variable passing please explain difference between T-SQL and PL/SQL [message #208891 is a reply to message #208889] |
Tue, 12 December 2006 07:59 |
tdudley
Messages: 6
Registered: December 2006
Location: Barrie, Ontario
|
Junior Member |
|
|
In general I'm trying to run a SQL command to return values. As I'm very limited in permissions being able to pass a variable to code is similar to being able to create a function. That is what I was trying to accomplish. Simply to pass freq as a variable to obtain a value. This functionality allows me to test the logic of the select statement. It is useless to not get output. I tried using AND exec dbms_output.enable(10000); |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
