| Creating a function in oracle database [message #8329] |
Sat, 09 August 2003 17:32  |
Nancy
Messages: 19
Registered: March 2002
|
Junior Member |
|
|
A. Using the Oracle database, I am creating a function based on a student table. This function will determine the highest grade of all the students in my student table. I have to include a minimum of five grades in my student table.
B. I am creating a procedure that will call the function created and show as output, the highest determined by the function.
I will show the code for the student table created. Using my own column names, types and values.
I will show the code for the function created.
I will show the code for the procedure that calls the function and all outputs from sql*plus.
I created a function HighGrade that returns the highest grade for the above.
if I use the following:
variable v_goodgrade number;
call HighGrade() into :v_goodgrade;
print v_goodgrade;
I get the expected output, that is, my highest grade in the table.
If I use the following unnamed procedure:
DECLARE
v_goodgrade number;
BEGIN
call HighGrade() into :v_goodgrade;
print v_goodgrade;
END;
I get:
SP2-0552: Bind variable "V_GOODGRADE" not declared.
What am I doing wrong?
Thanks In Advance
|
|
|
|
| Re: Creating a function in Oracle database [message #8330 is a reply to message #8329] |
Sun, 10 August 2003 06:21  |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
> DECLARE
> v_goodgrade number;
> BEGIN
> call HighGrade() into :v_goodgrade;
> print v_goodgrade;
> END;
> SP2-0552: Bind variable "V_GOODGRADE" not declared.
The clue is it's a SQL*Plus error ("SP2-nnnn") and it's referring to a "bind variable". v_goodgrade that you declared at the top is different to :v_goodgrade the bind variable.
"call" and "print" are also SQL*Plus commands, not PL/SQL. You need something like
v_goodgrade := HIGHGRADE;
|
|
|
|
| Re: Creating a function in Oracle database [message #8333 is a reply to message #8330] |
Sun, 10 August 2003 08:34 |
Nancy
Messages: 19
Registered: March 2002
|
Junior Member |
|
|
Hi, Sorry to bother you, I'm not sure if you can help, I am really getting frustrated with this problem.
The whole problem is this: Using the Oracle database, create a function based on a student table that you will create. This function will calculate the average grade for one of the students in your student table. Include a minimum of two grades in your student table. Use a procedure to call the function created and display the lastname, firstname and average grade of one of the students in the table created. Show the code for the student table created. Use your own column names, types and values. Show the code for the function created. Show the code for the procedure to call the function and its output. I 'm having some problem with the function and calling the function from a procedure. Can you help, or direct me to someone who could? Thanks in advance!
Nancy
|
|
|
|
| Re: Creating a function in Oracle database [message #8334 is a reply to message #8330] |
Sun, 10 August 2003 10:31 |
Nancy
Messages: 19
Registered: March 2002
|
Junior Member |
|
|
Is this the change you are referring to,I am so lost.
if I use the following:
variable v_goodgrade number;
call HighGrade() into :v_goodgrade;
print v_goodgrade;
I get the expected output, that is, my highest grade in the table.
If I use the following unnamed procedure:
DECLARE
v_goodgrade := HIGHGRADE;
BEGIN
call HighGrade() into :v_goodgrade;
print v_goodgrade;
END;
Thanks
Nancy
|
|
|
|
| Re: Creating a function in Oracle database [message #8335 is a reply to message #8334] |
Sun, 10 August 2003 16:59 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
> DECLARE
> v_goodgrade := HIGHGRADE;
> BEGIN
> call HighGrade() into :v_goodgrade;
> print v_goodgrade;
> END;
Needs to be something like
<pre style="color: navy; font-size: 9pt;">DECLARE
v_goodgrade NUMBER := HIGHGRADE;
BEGIN
DBMS_OUTPUT.PUT_LINE('Highest grade is: ' || v_goodgrade);
END;
/</pre>...or even...
<pre style="color: navy; font-size: 9pt;">BEGIN
DBMS_OUTPUT.PUT_LINE('Highest grade is: ' || HIGHGRADE);
END;
/</pre>
I'm not sure an anonymous block like this meets the requirement for a "procedure" though. If not, you will need "CREATE OR REPLACE PROCEDURE xxx AS ..." - or better still, put both the procedure and the function into one package.
Also the requirement asks for "lastname, firstname and average grade of one of the students in the table created" - not highest grade. Presumably you'll need to pass a parameter to your AVERAGE_GRADE function to specify a particular student.
Hope that helps...
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
