Home » SQL & PL/SQL » SQL & PL/SQL » is there something wrong with my variable?(pl/sql) (oracle 9i, xp)
is there something wrong with my variable?(pl/sql) [message #314206] Wed, 16 April 2008 01:29 Go to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Hi,all.
I am trying write a simple program to calculate the area of the circle,but I met some problems.
The variable will not be changed after I pass it a value.

Here is the code:
DECLARE
radius number(6,2) := 1;
pi NUMBER(6,2) := 3.14;
result number(6,2) := PI * radius * radius;
BEGIN
DBMS_OUTPUT.PUT_LINE('Enter value for sv_radius: ' || '&radius');
DBMS_OUTPUT.PUT_LINE('SHOW radius values after gave it a value: ' || radius);
DBMS_OUTPUT.PUT_LINE('The area of the circle is ' || result );
END;
/


The output is :

输入 radius 的值: 10
Enter value for sv_radius: 10
SHOW radius values after gave it a value:1
The area of the circle is 3.14

PL/SQL 过程已成功完成。

so can somebody tell me about this ?
Thanks,
snowball
Re: is there something wrong with my variable?(pl/sql) [message #314218 is a reply to message #314206] Wed, 16 April 2008 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to study a little bit for the differences between:
- a SQL*PLus substitution variable
- a bind variable
- a PL/SQL variable

Regards
Michel
Re: is there something wrong with my variable?(pl/sql) [message #314231 is a reply to message #314218] Wed, 16 April 2008 02:24 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Michel Cadot wrote on Wed, 16 April 2008 01:48
You have to study a little bit for the differences between:
- a SQL*PLus substitution variable
- a bind variable
- a PL/SQL variable

Regards
Michel


hi,michel.
what's the differences between pl/sql variable and the other two?
It's not a faq. Can you give me a link ?

Thanks

Re: is there something wrong with my variable?(pl/sql) [message #314236 is a reply to message #314231] Wed, 16 April 2008 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL*PlusŪ User's Guide and Reference, click on Index and scroll down to "variable".
Database PL/SQL User's Guide and Reference, same thing.

Regards
Michel

[Updated on: Wed, 16 April 2008 02:38]

Report message to a moderator

Re: is there something wrong with my variable?(pl/sql) [message #314251 is a reply to message #314206] Wed, 16 April 2008 03:04 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

thank you very much!
Re: is there something wrong with my variable?(pl/sql) [message #314346 is a reply to message #314251] Wed, 16 April 2008 07:28 Go to previous messageGo to next message
spring_cool
Messages: 1
Registered: April 2008
Junior Member
Hi hope this may be the answer to your query.Try this out.

declare
v_radius number(6,2):= 1;
v_pie number(6,2):=3.14;
v_result number(6,2);

Begin
v_result := v_pie*(v_radius*v_radius);
dbms_output.put_line('Area of the circle:v_result');
End;
Re: is there something wrong with my variable?(pl/sql) [message #314348 is a reply to message #314346] Wed, 16 April 2008 07:37 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
This would give the output:
Area of the circle:v_result

Maybe you should have a look at the links Michel posted too.
Re: is there something wrong with my variable?(pl/sql) [message #314364 is a reply to message #314346] Wed, 16 April 2008 08:06 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

spring_cool wrote on Wed, 16 April 2008 07:28
Hi hope this may be the answer to your query.Try this out.

declare
v_radius number(6,2):= 1;
v_pie number(6,2):=3.14;
v_result number(6,2);

Begin
v_result := v_pie*(v_radius*v_radius);
dbms_output.put_line('Area of the circle:v_result');
End;



oh,i want a calculate a area of circle with nonfixed radius.
thanks any way,spring_cool...
Re: is there something wrong with my variable?(pl/sql) [message #314368 is a reply to message #314206] Wed, 16 April 2008 08:30 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

I worked it out,but i haven't got my answer.

i still want to ask some questions.
DECLARE
       r number(5,2);
       PI CONSTANT number(5,2) := 3.1415;

BEGIN
       DBMS_OUTPUT.put_line('Enter a radius values: ');
       DBMS_OUTPUT.put_line('The area of circle is : ' || PI *power(&r,2) );
END;


My question is :
what if I just want to calculate one by one,like this: PI*r*r,
is that possible for a variable that it can store a value until end the code?
For instance,(but this is not a good code,i only use it to express my idea.)
declare a variable a;

get a input from keyborad and store it in a;

calculate it with some expression: such as, result := PI * a * a,


sorry for my limited expression.
hope you can understand.

snowball
Re: is there something wrong with my variable?(pl/sql) [message #314373 is a reply to message #314368] Wed, 16 April 2008 08:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ACCEPT

Regards
Michel
Re: is there something wrong with my variable?(pl/sql) [message #314392 is a reply to message #314373] Wed, 16 April 2008 10:02 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

hi ,michel.
it seems that it won't work ...

Here is the code:
ACCEPT radius NUMBER FORMAT'9999' PROMPT'ENTER a radius value: '  ;
DECLARE
PI CONSTANT NUMBER(6,2);

BEGIN
DBMS_OUTPUT.PUTLINE('THE area of circle is :   ' || PI*radius*radius);
END;

when i am trying to run it,it just execute the accept statament then ended.

and also i find the variable radius will not keep that values when enter begin ... end structure.

how can i fixed it?

Thank you.


Re: is there something wrong with my variable?(pl/sql) [message #314393 is a reply to message #314392] Wed, 16 April 2008 10:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"radius" is then a SQL*Plus substitution variable and not a PL/SQL one.

Regards
Michel
Re: is there something wrong with my variable?(pl/sql) [message #314398 is a reply to message #314393] Wed, 16 April 2008 10:18 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

oh,does pl/sql have such kind of variable?
Re: is there something wrong with my variable?(pl/sql) [message #314402 is a reply to message #314398] Wed, 16 April 2008 10:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, like you declare PI.
Read the links I posted.

Regards
Michel
Re: is there something wrong with my variable?(pl/sql) [message #314484 is a reply to message #314402] Wed, 16 April 2008 21:01 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Michel Cadot wrote on Wed, 16 April 2008 10:48
Yes, like you declare PI.
Read the links I posted.

Regards
Michel



Hi,I read some related contents of your links.
And it seems that pl/sql has 3 methods to assign values to variables.But none of that will solve my problems.

And I also try to use && ,but it will not give me the prompt after an execution and that means the variable will be defined as long as it is assigned a value.

DECLARE
       radius number(5,2);
       PI CONSTANT number(5,2) := 3.1415;
       result NUMBER(5,2);
BEGIN
       DBMS_OUTPUT.put_line('Enter a radius values: ' || &radius );
       result := PI * &&radius * &&radius;
       DBMS_OUTPUT.put_line('The area of circle is : ' || result);
END;


I also read "Inputting and Outputting Data with PL/SQL",theres is a sentence:
"Although some of these APIs can accept input as well as output, there is no built-in language facility for accepting data directly from the keyboard. For that, you can use the PROMPT and
ACCEPT commands in SQL*Plus."

so is that means we can only use PROMPT and ACCEPT command or the way it mentions to use that 3 ways to assign values to variables?

Re: is there something wrong with my variable?(pl/sql) [message #314493 is a reply to message #314206] Wed, 16 April 2008 22:13 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
http://asktom.oracle.com has many, many fine coding examples
Re: is there something wrong with my variable?(pl/sql) [message #314513 is a reply to message #314484] Thu, 17 April 2008 00:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are 3 kinds of variables.
They are independant.
They don't see them.

In your exemple:
DECLARE
       radius number(5,2);
This is a PL/SQL variable.

&radius 
This is a SQL*Plus subsitution variable.
Even if they have the same name they are 2 variables in 2 different environments and they don't know each other.

Regards
Michel

[Updated on: Thu, 17 April 2008 00:41]

Report message to a moderator

Re: is there something wrong with my variable?(pl/sql) [message #314527 is a reply to message #314484] Thu, 17 April 2008 01:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Once you have accepted a value using ACCEPT and PROMPT, it will continue to use that value until you either prompt again or UNDEFINE it.

If you do not use ACCEPT and PROMPT and just use the single & before the variable name, it will provide a default prompt of 'Enter value for <variable name>: ' and prompt every time you use the single &.

You can assign values from SQL*Plus substiution variables to PL/SQL variables in an anonymous PL/SQL block. Please see the demonstration below.

SCOTT@orcl_11g> ACCEPT sub_var PROMPT 'Enter value for SQL*Plus substitution variable:	'
Enter value for SQL*Plus substitution variable:  subvar1
SCOTT@orcl_11g> DECLARE
  2    plsql_variable VARCHAR2 (30) := '&sub_var';
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE ('value for plsql_variable is :  ' || plsql_variable);
  5  END;
  6  /
old   2:   plsql_variable VARCHAR2 (30) := '&sub_var';
new   2:   plsql_variable VARCHAR2 (30) := 'subvar1';
value for plsql_variable is :  subvar1

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> ACCEPT sub_var PROMPT 'Enter value for SQL*Plus substitution variable:	'
Enter value for SQL*Plus substitution variable:  subvar2
SCOTT@orcl_11g> DECLARE
  2    plsql_variable VARCHAR2 (30) := '&sub_var';
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE ('value for plsql_variable is :  ' || plsql_variable);
  5  END;
  6  /
old   2:   plsql_variable VARCHAR2 (30) := '&sub_var';
new   2:   plsql_variable VARCHAR2 (30) := 'subvar2';
value for plsql_variable is :  subvar2

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> UNDEFINE sub_var
SCOTT@orcl_11g> DECLARE
  2    plsql_variable VARCHAR2 (30) := '&sub_var';
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE ('value for plsql_variable is :  ' || plsql_variable);
  5  END;
  6  /
Enter value for sub_var: subvar3
old   2:   plsql_variable VARCHAR2 (30) := '&sub_var';
new   2:   plsql_variable VARCHAR2 (30) := 'subvar3';
value for plsql_variable is :  subvar3

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> DECLARE
  2    plsql_variable VARCHAR2 (30) := '&sub_var';
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE ('value for plsql_variable is :  ' || plsql_variable);
  5  END;
  6  /
Enter value for sub_var: subvar4
old   2:   plsql_variable VARCHAR2 (30) := '&sub_var';
new   2:   plsql_variable VARCHAR2 (30) := 'subvar4';
value for plsql_variable is :  subvar4

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 






Re: is there something wrong with my variable?(pl/sql) [message #314563 is a reply to message #314513] Thu, 17 April 2008 03:08 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Michel Cadot wrote on Thu, 17 April 2008 00:41
There are 3 kinds of variables.
They are independant.
They don't see them.

In your exemple:
DECLARE
       radius number(5,2);
This is a PL/SQL variable.

&radius 
This is a SQL*Plus subsitution variable.
Even if they have the same name they are 2 variables in 2 different environments and they don't know each other.

Regards
Michel



hi,like what you said above,pl/sql variables and sql subsitution variable are not 'live' in the same place and they don't know each other.

if use that 3 method to assign value to pl/sql variables,they can't get a value from input and it can't interact with sql variable.
In what kind of way , we can do to let it get from input and do not prompt when unnecessary?

Thanks,


Re: is there something wrong with my variable?(pl/sql) [message #314565 is a reply to message #314527] Thu, 17 April 2008 03:12 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Barbara Boehmer wrote on Thu, 17 April 2008 01:27
Once you have accepted a value using ACCEPT and PROMPT, it will continue to use that value until you either prompt again or UNDEFINE it.

If you do not use ACCEPT and PROMPT and just use the single & before the variable name, it will provide a default prompt of 'Enter value for <variable name>: ' and prompt every time you use the single &.

You can assign values from SQL*Plus substiution variables to PL/SQL variables in an anonymous PL/SQL block. Please see the demonstration below.

SCOTT@orcl_11g> ACCEPT sub_var PROMPT 'Enter value for SQL*Plus substitution variable:	'
Enter value for SQL*Plus substitution variable:  subvar1
SCOTT@orcl_11g> DECLARE
  2    plsql_variable VARCHAR2 (30) := '&sub_var';
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE ('value for plsql_variable is :  ' || plsql_variable);
  5  END;
  6  /
old   2:   plsql_variable VARCHAR2 (30) := '&sub_var';
new   2:   plsql_variable VARCHAR2 (30) := 'subvar1';
value for plsql_variable is :  subvar1

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> ACCEPT sub_var PROMPT 'Enter value for SQL*Plus substitution variable:	'
Enter value for SQL*Plus substitution variable:  subvar2
SCOTT@orcl_11g> DECLARE
  2    plsql_variable VARCHAR2 (30) := '&sub_var';
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE ('value for plsql_variable is :  ' || plsql_variable);
  5  END;
  6  /
old   2:   plsql_variable VARCHAR2 (30) := '&sub_var';
new   2:   plsql_variable VARCHAR2 (30) := 'subvar2';
value for plsql_variable is :  subvar2

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> UNDEFINE sub_var
SCOTT@orcl_11g> DECLARE
  2    plsql_variable VARCHAR2 (30) := '&sub_var';
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE ('value for plsql_variable is :  ' || plsql_variable);
  5  END;
  6  /
Enter value for sub_var: subvar3
old   2:   plsql_variable VARCHAR2 (30) := '&sub_var';
new   2:   plsql_variable VARCHAR2 (30) := 'subvar3';
value for plsql_variable is :  subvar3

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> DECLARE
  2    plsql_variable VARCHAR2 (30) := '&sub_var';
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE ('value for plsql_variable is :  ' || plsql_variable);
  5  END;
  6  /
Enter value for sub_var: subvar4
old   2:   plsql_variable VARCHAR2 (30) := '&sub_var';
new   2:   plsql_variable VARCHAR2 (30) := 'subvar4';
value for plsql_variable is :  subvar4

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 





Very good examples,thank you,Barbara Boehmer.
Is that possible to put accept command and the pl/sql block in one script?
I can't run it correctly ,when i tried to do that.
If not , how can i undefined a variable in pl/sql block?

Thanks ,
snowball
Re: is there something wrong with my variable?(pl/sql) [message #314579 is a reply to message #314565] Thu, 17 April 2008 03:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is that possible to put accept command and the pl/sql block in one script?

In one script, yes.
In a PL/SQL block, no, as acept is a SQL*Plus command not a PL/SQL statement.

Regards
Michel
Re: is there something wrong with my variable?(pl/sql) [message #314621 is a reply to message #314579] Thu, 17 April 2008 05:37 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Michel Cadot wrote on Thu, 17 April 2008 03:54
Quote:
Is that possible to put accept command and the pl/sql block in one script?

In one script, yes.
In a PL/SQL block, no, as acept is a SQL*Plus command not a PL/SQL statement.

Regards
Michel


do you meant that we can't find a method to realize getting input in pl/sql block?
Re: is there something wrong with my variable?(pl/sql) [message #314630 is a reply to message #314621] Thu, 17 April 2008 05:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
do you meant that we can't find a method to realize getting input in pl/sql block?

Yes this is also what I meant.
PL/SQL execute inside server and does not have access to client.

Regards
Michel
Re: is there something wrong with my variable?(pl/sql) [message #314644 is a reply to message #314630] Thu, 17 April 2008 06:12 Go to previous message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Thanks for your help,michel Smile
I will keep studying pl/sql.

[Updated on: Thu, 17 April 2008 06:13]

Report message to a moderator

Previous Topic: sql query
Next Topic: to view log if reindexing done
Goto Forum:
  


Current Time: Wed Dec 07 04:57:15 CST 2016

Total time taken to generate the page: 0.08518 seconds