NobrIntegrált információs rendszerek labor br Adatbázisok szerver oldali programozása/nobr

Ez az oldal a korábbi SCH wikiről lett áthozva.

Ha úgy érzed, hogy bármilyen formázási vagy tartalmi probléma van vele, akkor, kérlek, javíts rajta egy rövid szerkesztéssel!

Ha nem tudod, hogyan indulj el, olvasd el a migrálási útmutatót.

2007. 11. 23.

Kapcsolódó tárgyak

• Adatbázisok szerver oldali programozása

Közösen megnézett példák

CREATE TABLE oktatok (
	oktato_id int primary key,
	oktato_name nvarchar(150)
)

insert into Oktatok values (4, 'A');
insert into Oktatok values (2, 'B');
insert into Oktatok values (3, 'C');

select * from oktatok;

declare @name nvarchar(150);
set @name = 'Hello bello';

print @name;


declare @name nvarchar(150);
select @name = oktato_name from oktatok where oktato_id = 2;

print @name + '!';


-- ciklusok

declare @i int;
set @i = 5;

while (select count(*) from oktatok) < 10
begin
	insert into oktatok values (@i, 'oktato_' + convert(nvarchar, @i));
	set @i = @i + 1;
end

select * from oktatok;


--  kurzorok

declare @id int, @name nvarchar(150);

-- definiálás:
declare c cursor  for select * from oktatok;

-- megnyitás:
open c;

-- feldolgozás - a változok sorrendje fontos
fetch c into @id, @name;

-- az egyszeres egyenlőségjel itt összehasonlítás
while @@fetch_status = 0
begin
	print 'Oktató neve: ' + @name + ', id: ' + convert(nvarchar, @id);
	fetch c into @id, @name;
end

-- kurzor bezárása (ajánlott, mert van maximális számuk, illetve
-- később ilyen néven nem lehet majd kurzort létrehozni (?)).
close c;
deallocate c;


-- tárolt eljárások


-- nem tér vissza semmivel, nem kötelező (nincs return benne)
-- első futásra csak létrehozza

create proc eljaras
	@id int = 4
as
	declare  @count int;
	select @count = count(*) from oktatok where oktato_id > @id;
	print @count;


exec eljaras;
exec eljaras 5;


-- drop-pal lehet torolni, vagy alter
alter proc eljaras
	@id int = 4,
	@name nvarchar(150) OUTPUT
as
	select @name = oktato_name from oktatok where oktato_id = @id


-- hasznalat

declare @temp nvarchar(150);
exec eljaras 1, @temp OUTPUT;
print @temp;

Adatbázis séma

Főtáblák

• articles: article_id, article_name (cikk címe)
• authors: author_id, author_name
• presentations: presentation_id, presentation_name (előadás címe)

Kapcsolótáblák

• article_author article_id, author_id
• article_presentation: article_id, presentation_id
• author_presentation: author_id, presentation_id

Feladatok

Mivel függvényekről és szekvenciáról nem volt szó a laboron, így a kód kissé redundáns.

Táblák létrehozása

create table authors (
		  author_id int primary key,
		  author_name nvarchar(150)
);

create table articles (
		  article_id int primary key,
		  article_name nvarchar(150)
);

create table presentations (
		  presentation_id int primary key,
		  presentation_name nvarchar(150)
);


create table article_author (
		  article_id int,
		  author_id int
);

create table article_presentation (
		  article_id int,
		  presentation_id int
);

create table author_presentation (
		  author_id int,
		  presentation_id int
);

Tárolt eljárás cikk felvételéhez

Bemenő paraméterek:

• Cikk címe
• Maximum három szerző neve

Tennivalók:

• Cikk felvétele a táblába
• Ha a szerző nem létezik, akkor őket is vegye fel
• Szerzők és cikk összerendelése

CREATE PROC article_reg
	@title	 nvarchar(150),
	@author1 nvarchar(150),
	@author2 nvarchar(150) = NULL,
	@author3 nvarchar(150) = NULL
AS
	declare @article_id int;

	print 'Cikk regisztrálása';
	SELECT @article_id = MAX(article_id) FROM articles;
	IF @article_id IS NULL
	BEGIN
		set @article_id = 1;
	END
	print 'Last article_id: ' + convert(nvarchar, @article_id);
	set @article_id = @article_id + 1;
	INSERT INTO articles values(@article_id, @title);
	print 'Uj cikk felveve: ' + @title + ' (id: ' + convert(nvarchar, @article_id) + ')';

	declare @newid int;


	SELECT @newid = MAX(author_id) FROM authors;
	IF @newid IS NULL
	BEGIN
		set @newid = 1;
	END
	print 'Last author_id: ' + convert(nvarchar, @newid);

	declare @author_id int;
	declare @exists int;

	IF @author1 IS NOT NULL
	BEGIN
		set @newid = @newid + 1;

		select @exists = count(*) FROM authors WHERE author_name = @author1;
		
		IF @exists = 0
			BEGIN
				INSERT INTO authors values(@newid, @author1);
				print 'Uj szerzo felveve: ' + @author1 + ' (id: ' + convert(nvarchar, @newid) + ')';
				set @author_id = @newid;
			END
		ELSE
			BEGIN
				SELECT @author_id = author_id FROM authors WHERE author_name = @author1;
				print 'Szerzo-id: ' + @author1 + ' - ' + convert(nvarchar, @author_id);
			END 

		INSERT INTO article_author values(@article_id, @author_id);
		print 'Uj article_author felveve: ' + 
			convert(nvarchar, @article_id) + ' - ' + convert(nvarchar, @author_id);
	END


	set @author1 = @author2;
	IF @author1 IS NOT NULL
	BEGIN
		set @newid = @newid + 1;
		select @exists = count(*) FROM authors WHERE author_name = @author1;
		
		IF @exists = 0
			BEGIN
				INSERT INTO authors values(@newid, @author1);
				print 'Uj szerzo felveve: ' + @author1 + ' (id: ' + convert(nvarchar, @newid) + ')';
				set @author_id = @newid;
			END
		ELSE
			BEGIN
				SELECT @author_id = author_id FROM authors WHERE author_name = @author1;
				print 'Szerzo-id: ' + @author1 + ' - ' + convert(nvarchar, @author_id);
			END 

		INSERT INTO article_author values(@article_id, @author_id);
		print 'Uj article_author felveve: ' + 
			convert(nvarchar, @article_id) + ' - ' + convert(nvarchar, @author_id);
	END

	set @author1 = @author3;
	IF @author1 IS NOT NULL
	BEGIN
		
		set @newid = @newid + 1;
		select @exists = count(*) FROM authors WHERE author_name = @author1;
		
		IF @exists = 0
			BEGIN
				INSERT INTO authors values(@newid, @author1);
				print 'Uj szerzo felveve: ' + @author1 + ' (id: ' + convert(nvarchar, @newid) + ')';
				set @author_id = @newid;
			END
		ELSE
			BEGIN
				SELECT @author_id = author_id FROM authors WHERE author_name = @author1;
				print 'Szerzo-id: ' + @author1 + ' - ' + convert(nvarchar, @author_id);
			END 

		INSERT INTO article_author values(@article_id, @author_id);
		print 'Uj article_author felveve: ' + 
			convert(nvarchar, @article_id) + ' - ' + convert(nvarchar, @author_id);
	END

Tárolt eljárás előadás felvételéhez

Bemenő paraméterek:

• Előadás címe
• Előadók nevei
• Cikk azonosítója

Ellenőrzések:

• A cikknek léteznie kell
• Az előadóknak léteznie kell (az authors táblában)
• Egy cikkhez csak egy előadás tartozhat

Tennivalók:

• Előadás felvétele a táblába
• Előadás és előadók, valamint cikk és előadás összerendelése

CREATE PROC presentation_reg
	@title	 nvarchar(150),
	@article_id	int,
	@author1 nvarchar(150),
	@author2 nvarchar(150) = NULL,
	@author3 nvarchar(150) = NULL
as
	declare @author nvarchar(150);
	declare @count int;
	
	declare @author_id int;
	declare @author1_id int;
	declare @author2_id int;
	declare @author3_id int;


	select @count = count(*) FROM presentations WHERE presentation_name = @title;
	
	IF @count < 0
	BEGIN
		print 'Ilyen című eloadas mar letezik';
		return;
	END

	-- cikknek leteznie kell
	select @count = count(*) FROM articles WHERE article_id = @article_id;
	IF @count != 1
	BEGIN
		print 'Nincs ilyen cikk!';
		return;
	END

	-- egy cikkhez csak egy eloadas
	select @count = count(*) FROM article_presentation WHERE article_id = @article_id;
	IF @count = 1
	BEGIN
		print 'Mar van ehhez a cikkhez eloadas!';
		return;
	END

	set @author = @author1;
	IF @author IS NOT NULL
	BEGIN
		set @author_id = NULL;
		select @author_id = author_id FROM authors WHERE author_name = @author;
		IF @author_id IS NULL
		BEGIN
			print 'A kovetkezo szerzo nem letezik: ' + @author;
			return;
		END
	END	
	set @author1_id = @author_id;

	set @author = @author2;
	IF @author IS NOT NULL
	BEGIN
		set @author_id = NULL;
		select @author_id = author_id FROM authors WHERE author_name = @author;
		IF @author_id IS NULL
		BEGIN
			print 'A kovetkezo szerzo nem letezik: ' + @author;
			return;
		END
	END	
	set @author2_id = @author_id;

	set @author = @author3;
	IF @author IS NOT NULL
	BEGIN
		set @author_id = NULL;
		select @author_id = author_id FROM authors WHERE author_name = @author;
		IF @author_id IS NULL
		BEGIN
			print 'A kovetkezo szerzo nem letezik: ' + @author;
			return;
		END
	END	
	set @author3_id = @author_id;

	print 'Ellenorzesek rendben';

	-- prezi letrehozasa
	declare @prezi_id int;
	SELECT @prezi_id = MAX(presentation_id) FROM presentations;
	IF @prezi_id IS NULL
	BEGIN
		set @prezi_id = 0;
	END
	print 'Last presentation_id: ' + convert(nvarchar, @prezi_id);
	set @prezi_id = @prezi_id + 1;


	INSERT INTO presentations values (@prezi_id, @title);
	INSERT INTO author_presentation values (@author1_id, @prezi_id);	
	INSERT INTO author_presentation values (@author2_id, @prezi_id);
	INSERT INTO author_presentation values (@author3_id, @prezi_id);

	INSERT INTO article_presentation values (@article_id, @prezi_id);

Előadások listázása

Kimenet:

• Előadás címe
• Előadók
• Cikk címe

CREATE PROC list
AS

declare @prez_id int;
declare @prez_name nvarchar(150);

declare @auth_id int;
declare @auth_name nvarchar(150);
declare @article_name nvarchar(150);

declare prez cursor for select * from presentations;
open prez;

fetch prez into @prez_id, @prez_name;

while @@fetch_status = 0
begin
	print '------------------------------------------';
	print 'Prezenteacio cime: ' + @prez_name + ', id: ' + convert(nvarchar, @prez_id);
	fetch prez into @prez_id, @prez_name;

	-- eloadok nevei
	declare auth cursor for 
		select a.author_name name  
		from author_presentation AS ap,
				authors AS a
		WHERE ap.presentation_id = @prez_id
		AND ap.author_id = a.author_id;
	open auth;

	fetch auth into @auth_name;
	while @@fetch_status = 0
	begin
		print 'Eloado: ' + @auth_name;
		fetch auth into @auth_name;
	end;

	close auth;
	deallocate auth;

	-- cikk címe
	set @article_name = '';
	SELECT @article_name = a.article_name 
		FROM articles AS a, 
			article_presentation AS ap
		WHERE ap.presentation_id = @prez_id 
		AND ap.article_id = a.article_id;
	
	
	print 'Cikk cime: ' + @article_name;

	fetch prez into @prez_id, @prez_name;
end

close prez;
deallocate prez;

Futtatás

	 exec article_reg 'article_title1', 'A', 'G', 'R';
	 exec presentation_reg 'prez_title1', 1, 'G', 'R', 'A';
	 exec list;

-- palacsint - 2007.11.23.