Home » SQL & PL/SQL » SQL & PL/SQL » help in PROCEDURE
help in PROCEDURE [message #438814] Tue, 12 January 2010 06:50 Go to next message
breakid
Messages: 6
Registered: January 2010
Location: IL
Junior Member
Hi,
I try to create this
CREATE OR REPLACE PROCEDURE big_CAPACITY
(
cap airport.CAPACITY%type,
wei airport.WEIGHT%type
)
AS
BEGIN
select model,modelnum into model1,modelnum1 from airport where CAPACITY>cap and WEIGHT>wei;
end big_CAPACITY;


but all I get is the error -
ERROR at line 8: PL/SQL: SQL Statement ignored6. AS
7. BEGIN
8. select model,modelnum into model1,modelnum1 from airport where CAPACITY>cap and WEIGHT>wei;
9. end big_CAPACITY;


I worn to create a procedure that I enter 2 numbers and it giving me all the model,moelnum that is bigger from this numbers.

Re: help in PROCEDURE [message #438815 is a reply to message #438814] Tue, 12 January 2010 06:52 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
You have to declare model1 and modelnum1 as variables.
Re: help in PROCEDURE [message #438818 is a reply to message #438815] Tue, 12 January 2010 07:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
... and loop to fetch all values returned without forgetting to display or return them in any way.

Regards
Michel

[Updated on: Tue, 12 January 2010 07:07]

Report message to a moderator

Re: help in PROCEDURE [message #438833 is a reply to message #438814] Tue, 12 January 2010 08:55 Go to previous messageGo to next message
breakid
Messages: 6
Registered: January 2010
Location: IL
Junior Member
I don't get it,
I try this one still not good -
CREATE OR REPLACE PROCEDURE big_CAPACITY
(
cap airport.CAPACITY%type,
wei airport.WEIGHT%type,
model1 airport.model%type,
modelnum1 airport.modelnum%type
)
AS
BEGIN
select model,modelnum from into model1,modelnum1 airport where CAPACITY>cap and WEIGHT>wei;
end big_CAPACITY;
Re: help in PROCEDURE [message #438834 is a reply to message #438833] Tue, 12 January 2010 09:00 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
create or replace procedure test ( empid IN number)
as
lname varchar2(20);
begin
---The below sql is going to fetch one row if you are going to ---fetch multiple records use cursor and loop 
select last_name into lname from employees where employee_id=empid;
end;
/
Re: help in PROCEDURE [message #438841 is a reply to message #438833] Tue, 12 January 2010 09:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This is NOT MS-SQL Server, this is Oracle.
In Oracle, a procedure does NOT by default return the resultset of the query it contains; read up on REF CURSORs
Re: help in PROCEDURE [message #438846 is a reply to message #438833] Tue, 12 January 2010 10:19 Go to previous messageGo to next message
cookiemonster
Messages: 12420
Registered: September 2008
Location: Rainy Manchester
Senior Member
This doesn't look remotely right either:
select model,modelnum from into model1,modelnum1 airport where CAPACITY>cap and WEIGHT>wei;

The from is in the wrong place
Re: help in PROCEDURE [message #438848 is a reply to message #438814] Tue, 12 January 2010 10:29 Go to previous messageGo to next message
breakid
Messages: 6
Registered: January 2010
Location: IL
Junior Member
ok, I fix it bu still no good,

CREATE OR REPLACE PROCEDURE big_CAPACITY
(
cap airport.CAPACITY%type,
wei airport.WEIGHT%type,
model1 airport.model%type,
modelnum1 airport.modelnum%type
)
AS
BEGIN
select model,modelnum into model1,modelnum1 from airport where CAPACITY>cap and WEIGHT>wei;
end big_CAPACITY;


I got error -
Quote:

expression 'MODEL1' cannot be used as an INTO-target of a SELECT/FETCH statement

[Updated on: Tue, 12 January 2010 10:29]

Report message to a moderator

Re: help in PROCEDURE [message #438849 is a reply to message #438848] Tue, 12 January 2010 10:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session.

Good readings before coding in PL/SQL:
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Without the basics no one can help you without posting the whole solution (that you will not understand).

Regards
Michel
Re: help in PROCEDURE [message #438851 is a reply to message #438848] Tue, 12 January 2010 10:40 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
As well as Michel's advice, re-read its_me_ved's post. It gives the basic structure of what you are trying to do.
Re: help in PROCEDURE [message #438856 is a reply to message #438814] Tue, 12 January 2010 11:35 Go to previous messageGo to next message
breakid
Messages: 6
Registered: January 2010
Location: IL
Junior Member
thanks I fix it.
Re: help in PROCEDURE [message #438874 is a reply to message #438856] Tue, 12 January 2010 16:07 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
So show us what you did (it may help others following the thread ata a later date.)
Previous Topic: Select Random Rows
Next Topic: how to read the next record
Goto Forum:
  


Current Time: Thu Dec 08 06:26:02 CST 2016

Total time taken to generate the page: 0.08587 seconds