NobrIntegrált információs rendszerek labor br Adatbázisok szerver oldali programozása/nobr forrásának megtekintése

{{GlobalTemplate|Infoszak|IIRLaborDBServerProg}}


2007. 11. 23.

__TOC__

==Kapcsolódó tárgyak==

* [[AdatbServerProg|Adatbázisok szerver oldali programozása]]

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

<pre>
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;
</pre>



==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===

<pre>
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
);
</pre>

===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

<pre>
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
</pre>

===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

<pre>
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);
</pre>


===Előadások listázása===

Kimenet:
* Előadás címe
* Előadók
* Cikk címe

<pre>
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;
</pre>

===Futtatás===

<pre>
	 exec article_reg 'article_title1', 'A', 'G', 'R';
	 exec presentation_reg 'prez_title1', 1, 'G', 'R', 'A';
	 exec list;
</pre>


-- [[KarakoMiklos|palacsint]] - 2007.11.23.


[[Category:Infoszak]]