PL/SQL help creating a stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

PL/SQL help creating a stored procedure

Author

Topic

thomassale88
Starting Member

2 Posts

Posted - 2010-12-09 : 08:48:42

Thank you all for your prompt responses.
My operating system is Windows 7
Code: [Select all] [Show/ hide]

SELECT * from v$version

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod 
PL/SQL Release 10.2.0.1.0 - Production                           
CORE	10.2.0.1.0	Production                                         
TNS for Linux: Version 10.2.0.1.0 - Production                   
NLSRTL Version 10.2.0.1.0 - Production

The first table is Team


create table team (
team_id  varchar2(8),
team_name  varchar2(20),
entry_fee  varchar2(10),
points     number(10))

Insert statement for Team_tbl
Code: [Select all] [Show/ hide]

insert into team values(
'T01','London','lo1','paid','0')

The second table is Fixture_team
Code: [Select all] [Show/ hide]

create table fixture_team(
team_id varchar2(8),
fixture_id varchar2(8),
goals number(10),
players fixture_player_nest)
nested table players store as fixture_player_nest_tbl

This table has a nested table which is made up of:

Type:
Code: [Select all] [Show/ hide]

create or replace
type fixt_player as object(
player_id varchar2(8),
position varchar2(8))

Varray:
Code: [Select all] [Show/ hide]

create or replace
type match_players as varray(7) of fixt_player;

Nested:
Code: [Select all] [Show/ hide]

create type fixture_player_nest as table of match_players

The third and final table is Fixture_tbl
Code: [Select all] [Show/ hide]

create table Fixture_tbl(
fixt_id varchar2(8),
fixt_date date,
fixt_time varchar2(10),
fixt_location location_t,
umpire_id varchar2(8),
player_of_match player_of_match,
home_team varchar2(20),
away_team varchar2(20))

I have only ever used simple procedures to print a line such as a persons date of birth but never any thing as complicated as computing columns and updating a separate table.

P.S I haven't used this forum before so i would like to appologise in advance for any posting errors. thank you

thomassale88
Starting Member

2 Posts

Posted - 2010-12-09 : 08:51:52

So far i have this:

create or replace procedure fixture_update_points(p_fixt_id in varchar2) is
update table_team t
begin
   set points = points + (select case when t.team_id = team_id_h
                                      then points_h
                                      when t.team_id = team_id_a
                                      then points_a
                                      else 0
                                 end points
                            from (select h.team_id team_id_h,
                                         case when h.goals_number > a.goals_number
                                              then 6
                                              when h.goals_number = a.goals_number
                                              then 4
                                              else 0
                                         end points_h,
                                         a.team_id team_id_a,
                                         case when a.goals_number > h.goals_number
                                              then 6
                                              when a.goals_number = h.goals_number
                                              then 4
                                              else 0
                                         end points_h,
                                    from (select m.team_id,f.goals_number
                                            from fixture_tbl m,table_team t,fixture_team f
                                           where m.fixt_id = :p_fixt_id
                                             and m.home_team = t.team_name
                                             and t.team_id = f.team_id
                                         ) h,
                                         (select m.team_id,f.goals_number
                                            from fixture_tbl m,table_team t,fixture_team f
                                           where m.fixt_id = :p_fixt_id
                                             and m.away_team = t.team_name
                                             and t.team_id = f.team_id
                                         ) a
                                 )
                         )
 where team_name in (select home_team,away_team
                       from fixture_tbl
                      where fixt_id = :p_fixt_id
                    )

But I keep getting this error:

PROCEDURE FIXTURE_UPDATE_POINTS compiled
Errors: check compiler log
2/1            PLS-00103: Encountered the symbol "UPDATE" when expecting one of the following:

   begin function package pragma procedure subtype type use
   <an identifier> <a double-quoted delimited-identifier> form
   current cursor external language

PROCEDURE FIXTURE_UPDATE_POINTS compiled
Errors: check compiler log

Kristen
Test

22859 Posts

Posted - 2010-12-09 : 08:59:17

Sorry, this is a Microsoft SQL Server forum - therefore there may not be any/many folk with the knowledge to help you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-09 : 11:53:19

try your luck at www.orafaq.com

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources