###################################################################################
create or replace procedure procedure_1
as
begin
declare
CURSOR finding is select * from positions where P_RELASE_DATE > sysdate-360;
found positions%ROWTYPE;
begin
LOOP
FETCH finding INTO found;
EXIT WHEN finding%NOTFOUND;
DBMS_output.put_line('1 '||found.P_TITLE||'');
DBMS_output.put_line('2 '||found.P_SUB_TITLE||'');
DBMS_output.put_line('3 '||found.P_RELASE_DATE||'');
END LOOP;
CLOSE finding ;
END;
end;
/
Create or replace procedure show_games(devid number)
as
num integer;
CURSOR found IS SELECT G_ORIGINAL_TITLE,G_ORIGINAL_SUB_TITLE FROM GAMES where G_DEVELOPER_FK = devid ;
no_found EXCEPTION;
BEGIN
if found%FOUND THEN
for num IN found
LOOP
DBMS_OUTPUT.put_line('Game : ' || num.G_ORIGINAL_TITLE || ' ' || num.G_ORIGINAL_SUB_TITLE);
END LOOP;
ELSE
raise no_found;
END IF;
EXCEPTION
when no_found THEN
DBMS_OUTPUT.put_line('@@@ No games found from this developer @@@');
END;
/
CREATE OR REPLACE PROCEDURE show_positions
AS
BEGIN
declare
CURSOR TAKE_DATA IS SELECT P_TITLE, P_SUB_TITLE, P_RELASE_DATE from POSITIONS;
begin
FOR var IN TAKE_DATA
LOOP
DBMS_output.put_line('TITLE: ' ||var.P_TITLE||
' SUBTITLE: ' || var.P_SUB_TITLE||
' RELASE DATE: ' || var.P_RELASE_DATE );
END LOOP;
end;
END;
/
CREATE OR REPLACE PROCEDURE show_positions2
AS
BEGIN
declare
CURSOR jan IS SELECT P_TITLE, P_SUB_TITLE, P_RELASE_DATE from POSITIONS WHERE P_RELASE_DATE between '01-jan-06' and LAST_DAY (TO_DATE ('01','MM'));
CURSOR feb IS SELECT P_TITLE, P_SUB_TITLE, P_RELASE_DATE from POSITIONS WHERE P_RELASE_DATE between '01-feb-06' and LAST_DAY (TO_DATE ('02','MM'));
CURSOR mar IS SELECT P_TITLE, P_SUB_TITLE, P_RELASE_DATE from POSITIONS WHERE P_RELASE_DATE between '01-mar-06' and LAST_DAY (TO_DATE ('03','MM'));
begin
FOR var IN mar
LOOP
DBMS_output.put_line('TITLE: ' ||var.P_TITLE||
' SUBTITLE: ' || var.P_SUB_TITLE||
' RELASE DATE: ' || var.P_RELASE_DATE );
END LOOP;
end;
END;
/
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
procedury zwiazane z data
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
###################################################################################
ZMIANA DATY WYDANIA GRY WZGLEDEM ID GRY NA SYSTEMOWA
###################################################################################
create or replace procedure change_Date1(id number)
as
num INTEGER;
no_position EXCEPTION;
begin
SELECT count(*) INTO num FROM POSITIONS WHERE P_ID=id;
If num=0 then RAISE no_position;
Else
update POSITIONS set P_RELASE_DATE = sysdate where P_ID=id;
dbms_output.put_line('### Position updated ###');
End If;
exception
when no_position then
dbms_output.put_line('@@@ No such position !!! @@@');
end;
/
###################################################################################
ZMIANA DATY WYDANIA GRY WZGLEDEM ID GRY NA DOWOLNA
###################################################################################
create or replace procedure change_Date2(id number, data date)
as
num INTEGER;
no_position EXCEPTION;
begin
SELECT count(*) INTO num FROM POSITIONS WHERE P_ID=id;
If num=0 then RAISE no_position;
Else
update POSITIONS set P_RELASE_DATE = data where P_ID=id;
dbms_output.put_line('### Position updated ###');
ENd If;
exception
when no_position then
dbms_output.put_line('@@@ No such position in databese !!! @@@');
end;
/
###################################################################################
ZMIANA DATY WYDANIA GRY WZGLEDEM TYTULU GRY NA DOWOLNA
###################################################################################
create or replace procedure change_Date3(title char, data date)
as
num INTEGER;
no_position EXCEPTION;
begin
SELECT count(*) INTO num FROM POSITIONS WHERE P_TITLE=title;
If num=0 then RAISE no_position;
Else
update POSITIONS set P_RELASE_DATE = data where P_TITLE=title;
dbms_output.put_line('### Position updated ###');
ENd If;
exception
when no_position then
dbms_output.put_line('@@@ No such position !!! @@@');
end;
/
###################################################################################
ILE GIER ZOSTALO WYDANYCH W PRZEDZIALE data1 i data2
###################################################################################
create or replace procedure how_many(data1 date, data2 date)
as
num INTEGER;
no_position EXCEPTION;
begin
SELECT count(*) INTO num FROM POSITIONS WHERE P_RELASE_DATE between data1 and data2;
If num=0 then RAISE no_position;
Else
dbms_output.put_line('||num||');
ENd If;
exception
when no_position then
dbms_output.put_line('@@@ No such position acepting criteria @@@');
end;
/
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
procedury zwiazane z dodawaniem nowego elementu do tablicy
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
###################################################################################
DODAWANIE NOWEGO DEVELOPERA
###################################################################################
create or replace procedure add_DEVELOPER(name char, town char, country char, www char)
as
the_same INTEGER;
the_same2 INTEGER;
already_exist EXCEPTION;
already_exist2 EXCEPTION;
begin
select count(*) into the_same from DEVELOPERS where D_NAME=name and D_COUNTRY=country;
select count(*) into the_same2 from DEVELOPERS where D_NAME=name or D_COUNTRY=country;
if the_same>0 then raise already_exist;
else
insert into DEVELOPERS values(1, name, town, country, www);
DBMS_output.put_line('### Developer '||name||' '||town||' '||country||''||www||' has been added ###');
commit;
end if;
if the_same2>0 then raise already_exist2;
else
insert into DEVELOPERS values(1, name, town, country, www);
DBMS_output.put_line('### Developer '||name||' '||town||' '||country||' '||www||' has been added ###');
commit;
end if;
exception
when already_exist then
dbms_output.put_line('@@@ Cant have 2 the same developers in databese !!! @@@');
dbms_output.put_line('@@@ Identical element was found in the table @@@');
when already_exist2 then
dbms_output.put_line('@@@ Cant have 2 the same developers in databese !!! @@@');
END;
/
###################################################################################
DODAWANIE NOWEGO PUBLISHERA
###################################################################################
create or replace procedure add_PUBLISHER(name char, town char, country char, www char)
as
the_same INTEGER;
the_same2 INTEGER;
already_exist EXCEPTION;
already_exist2 EXCEPTION;
begin
select count(*) into the_same from PUBLISHERS where P_NAME=name and P_COUNTRY=country;
select count(*) into the_same2 from PUBLISHERS where P_NAME=name or P_COUNTRY=country;
if the_same>0 then raise already_exist;
else
insert into PUBLISHERS values(1, name, town, country, www);
DBMS_output.put_line('### Publisher '||name||' '||town||' '||country||''||www||' has been added ###');
commit;
end if;
if the_same2>0 then raise already_exist2;
else
insert into PUBLISHERS values(1, name, town, country, www);
DBMS_output.put_line('### Publisher '||name||' '||town||' '||country||' '||www||' has been added ###');
commit;
end if;
exception
when already_exist then
dbms_output.put_line('@@@ Cant have 2 the same developers in databese !!! @@@');
dbms_output.put_line('@@@ Identical element was found in the table @@@');
when already_exist2 then
dbms_output.put_line('@@@ Cant have 2 the same developers in databese !!! @@@');
END;
/
###################################################################################
DODAWANIE NOWEGO TYPU GRY
###################################################################################
create or replace procedure add_type(name char, abb char)
as
the_same INTEGER;
the_same2 INTEGER;
already_exist EXCEPTION;
already_exist2 EXCEPTION;
begin
select count(*) into the_same from Types where T_NAME=name and T_ABBREVIATION=abb;
select count(*) into the_same2 from Types where T_NAME=name or T_ABBREVIATION=abb;
if the_same>0 then raise already_exist;
else
insert into Types values(1, name, abb);
DBMS_output.put_line('### Type '||name||' '||abb||' has been added ###');
commit;
end if;
if the_same2>0 then raise already_exist2;
else
insert into Types values(1, name, abb);
DBMS_output.put_line('### Type '||name||' '||abb||' has been added ###');
commit;
end if;
exception
when already_exist then
dbms_output.put_line('@@@ Cant have 2 the same types of games !!! @@@');
dbms_output.put_line('@@@ Identical element was found in the table @@@');
when already_exist2 then
dbms_output.put_line('@@@ Cant have 2 the same types of games !!! @@@');
END;
/
###################################################################################
DODAWANIE NOWEGO JEZYKA GRY
###################################################################################
create or replace procedure add_LANGUAGE(name char, abb char)
as
the_same INTEGER;
the_same2 INTEGER;
already_exist EXCEPTION;
already_exist2 EXCEPTION;
begin
select count(*) into the_same from LANGUAGES where L_NAME=name and L_ABBREVIATION=abb;
select count(*) into the_same2 from LANGUAGES where L_NAME=name or L_ABBREVIATION=abb;
if the_same>0 then raise already_exist;
else
insert into LANGUAGES values(1, name, abb);
DBMS_output.put_line('### Language '||name||' '||abb||' has been added ###');
commit;
end if;
if the_same2>0 then raise already_exist2;
else
insert into LANGUAGES values(1, name, abb);
DBMS_output.put_line('### Language '||name||' '||abb||' has been added ###');
commit;
end if;
exception
when already_exist then
dbms_output.put_line('@@@ Cant have 2 the same languages in databese !!! @@@');
dbms_output.put_line('@@@ Identical element was found in the table @@@');
when already_exist2 then
dbms_output.put_line('@@@ Cant have 2 the same languages in databese !!! @@@');
dbms_output.put_line('@@@ Element with the same name OR abbreviation occured @@@');
END;
/
###################################################################################
DODAWANIE NOWEJ PLATFORMY
###################################################################################
create or replace procedure add_PLATFORM(name char, abb char)
as
the_same INTEGER;
the_same2 INTEGER;
already_exist EXCEPTION;
already_exist2 EXCEPTION;
begin
select count(*) into the_same from PLATFORMS where P_NAME=name and P_ABBREVIATION=abb;
select count(*) into the_same2 from PLATFORMS where P_NAME=name or P_ABBREVIATION=abb;
if the_same>0 then raise already_exist;
else
insert into PLATFORMS values(1, name, abb);
DBMS_output.put_line('### Platform '||name||' '||abb||' has been added ###');
commit;
end if;
if the_same2>0 then raise already_exist2;
else
insert into PLATFORMS values(1, name, abb);
DBMS_output.put_line('### Platform '||name||' '||abb||' has been added ###');
commit;
end if;
exception
when already_exist then
dbms_output.put_line('@@@ Cant have 2 the same platforms in databese !!! @@@');
dbms_output.put_line('@@@ Identical element was found in the table @@@');
when already_exist2 then
dbms_output.put_line('@@@ Cant have 2 the same platforms in databese !!! @@@');
dbms_output.put_line('@@@ Element with the same name OR abbreviation occured @@@');
END;
/
###################################################################################
DODAWANIE NOWEJ GRY
###################################################################################
create or replace procedure add_GAME(title char, subtitle char, dev number, type number, lan number, pla number)
as
the_same INTEGER;
the_same2 INTEGER;
already_exist EXCEPTION;
already_exist2 EXCEPTION;
begin
select count(*) into the_same from GAMES where G_ORIGINAL_TITLE=title and G_ORIGINAL_SUB_TITLE=subtitle;
select count(*) into the_same2 from GAMES where G_ORIGINAL_TITLE=title or G_ORIGINAL_SUB_TITLE=subtitle;
if the_same>0 then raise already_exist;
else
insert into GAMES values(1, title, subtitle, dev, type, lan, pla);
DBMS_output.put_line('### Game '||title||' '||subtitle||' '||dev||' '||type||' '||lan||' '||pla||' has been added ###');
commit;
end if;
if the_same2>0 then raise already_exist2;
else
insert into GAMES values(1, title, subtitle, dev, type, lan, pla);
DBMS_output.put_line('### Game '||title||' '||subtitle||' '||dev||' '||type||' '||lan||' '||pla||' has been added ###');
commit;
end if;
exception
when already_exist then
dbms_output.put_line('@@@ Cant have 2 the same games in databese !!! @@@');
dbms_output.put_line('@@@ Identical element was found in the table @@@');
when already_exist2 then
dbms_output.put_line('@@@ Cant have 2 the same games in databese !!! @@@');
dbms_output.put_line('@@@ Element with the same name OR abbreviation occured @@@');
END;
/
###################################################################################
DODAWANIE NOWEJ POZYCJI
###################################################################################
create or replace procedure add_POSITION(game number, lan number, pub number, data date, title char, subtitle char)
as
the_same INTEGER;
the_same2 INTEGER;
already_exist EXCEPTION;
already_exist2 EXCEPTION;
begin
select count(*) into the_same from POSITIONS where P_TITLE=title and P_SUB_TITLE=subtitle;
select count(*) into the_same2 from POSITIONS where P_TITLE=title or P_SUB_TITLE=subtitle;
if the_same>0 then raise already_exist;
else
insert into POSITIONS values(1, game, lan, pub, data, title, subtitle);
DBMS_output.put_line('### Game '||game||' '||lan||' '||pub||' '||data||' '||title||' '||subtitle||' has been added ###');
commit;
end if;
if the_same2>0 then raise already_exist2;
else
insert into POSITIONS values(1, game, lan, pub, data, title, subtitle);
DBMS_output.put_line('### Game '||game||' '||lan||' '||pub||' '||data||' '||title||' '||subtitle||' has been added ###');
commit;
end if;
exception
when already_exist then
dbms_output.put_line('@@@ Cant have 2 the same positions in databese !!! @@@');
dbms_output.put_line('@@@ Identical element was found in the table @@@');
when already_exist2 then
dbms_output.put_line('@@@ Cant have 2 the same positions in databese !!! @@@');
dbms_output.put_line('@@@ Element with the same name OR abbreviation occured @@@');
END;
/
###################################################################################
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
procedury zwiazane z usuwaniem nowego elementu do tablicy
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
create or replace procedure remove_type(Name CHAR, abb CHAR)
as
num integer;
none exception;
BEGIN
select count(*) into num from types where T_Name=Name and T_ABBREVIATION=abb;
if num = 0
then
raise none;
else
DELETE FROM types
WHERE T_Name=Name and T_ABBREVIATION=abb;
DBMS_output.put_line('Type : ' || Name || ' ' || abb ||' has been deleted!');
end if;
exception
when none then
DBMS_output.put_line('No such type exists in database !');
END;
/
execute procedure_1;
execute show_games(1);
execute show_positions;
execute show_positions2;
execute change_Date1( 1 );
execute change_Date2( 1,22-JUL-06 );
execute change_Date3('S.T.A.L.K.E.R.',22-JUL-06);
execute how_many( 05-JAN-06 , 30-MAR-06 );
execute how_many2;
execute add_type ( 'First Person Perspective', 'FPP' );
execute add_language( 'Vietnamiese', 'VIET' );
execute remove_type( 'First Person Perspective', 'FPP' );
Wyszukiwarka
Podobne podstrony:
Games Database games?tabase tablesGames Database games?tabase triggersBob Cassidy Mind GamesRead Me Before Installing 114 Reflexive Arcade Gamesgames?Backstreet Boys Quit Playing Gamesnaughty games for grown ups ebookgames hrSHSpec 032 6107C20 Games Conditionsgameshelp games 1Become a Computer Game Developer The Business of GamesSignaling Games and Gricean PragmaticsParty Games for Large Groups (more than 8 People)więcej podobnych podstron