Procedura zwracająca osoby których średnie zarobki są większe od podanych w parametrze.
create proc procedura
@wej_srednia money
as begin
select nazwisko, avg(brutto) srednia
from pracownicy p join zarobki z on z.pracID=p.pracID
group by p.nazwisko
having avg(brutto)=
(select max(wynik.srednia) from
(select nazwisko, avg(brutto) srednia
from pracownicy p join zarobki z on z.pracID=p.pracID
group by nazwisko)wynik)
union
select nazwisko,avg(brutto) srednia
from pracownicy p join zarobki z on z.pracID=p.pracID
group by nazwisko
having avg(brutto)=@wej_srednia;
end
procedura 5600
Funkcja przyjmująca tekst jako parametr, odwracająca i powtarzająca tyle razy ile podano w drugim parametrze
create function rev (@par1 varchar(10), @par2 integer)
returns varchar(50)
as
begin
declare @i as integer
declare @wynik as varchar(50)
set @i = 0
set @wynik = ''
while @i < @par2
begin
set @i = @i + 1
if ((@i = 1) or (@i = 3) or (@i = 5))
begin
set @wynik = @wynik + REVERSE(SUBSTRING(@par1,1,LEN(@par1))) + '_'
end
else
begin
set @wynik = @wynik + SUBSTRING(@par1,1,LEN(@par1)) + '_'
end
end
return @wynik
end
select dbo.rev('XYZ', 5)
Zapytanie zwracające nazwę dnia tygodnia z aktualnego dnia + ile dni
alter function fun_kol(@a date, @b integer)returns varchar(30)
as begin
declare @day integer
if @b is null set @b=10
set @day=@b+DATEPART(day, @a)
declare @z varchar(20)
if DATEPART(weekday, @day)=1 set @z='Poniedzialek'
if DATEPART(weekday, @day)=2 set @z='Wtorek'
if DATEPART(weekday, @day)=3 set @z='Środa'
if DATEPART(weekday, @day)=4 set @z='Czwartek'
if DATEPART(weekday, @day)=5 set @z='Piątek'
if DATEPART(weekday, @day)=6 set @z='Sobota'
if DATEPART(weekday, @day)=7 set @z='Niedziela'
return @z
end
print datepart(day, '04/07/2010')
Trigger który przy dodaniu i modyfikacji tabeli pracownicy tworzy email w tabeli pracownicy 3 liter imienia kropki 5 liter nazwiska i @p.lodz.pl
create table pracownicy add email varchar(100)
alter trigger em
on pracownicy
for insert, update
as begin
update pracownicy
set email=SUBSTRING(Imię,1,3)+'.'+SUBSTRING(nazwisko,1,5)+
'@p.lodz.pl'
end
insert into pracownicy(IDpracownika, Imię, nazwisko) values('10', 'Jan', 'Zasada')
select * from pracownicy
Procedura zwracająca „Wiadomość” osoby które maja <parametr1> do <parametr2> lat
create procedure kolokwium2
@par1 int,
@par2 int
as
begin
declare @imie varchar(50)
declare @nazwisko varchar(50)
declare @wiek int
declare @tmp varchar(50)
if @par1 = ''
begin
set @par1 = 18
end
if @par2 = ''
begin
set @par2 = 30
end
declare kurs1 scroll cursor
for select imie, nazwisko, wiek from pracownicy
open kurs1
fetch last from kurs1 into @imie, @nazwisko, @wiek
while @@FETCH_STATUS=0
begin
if ((@par1 < @wiek) and (@par2 > @wiek))
begin
print 'Osoba ' + @imie + ' '+ @nazwisko + ' ' + cast(@wiek as varchar) + 'lat'
end
fetch relative -2 from kurs1 into @imie, @nazwisko, @wiek
end
close kurs1
deallocate kurs1
end
kolokwium2 36, 200
Funkcja która miesza znaki z <prametr1> i <parametr2> od tylu
alter FUNCTION znaki(@a varchar(50),@b varchar(50))
returns varchar(100)as begin
if LEN(@a) !=LEN(@b) return 'Blad dlugosci znakow'
+ cast(len(@a) as varchar) + '<>' + cast(len(@b) as varchar)
declare @licznik integer;
declare @wynik varchar(100);
set @licznik=0;
set @wynik='';
while @licznik<=LEN(@a)
begin
set @licznik=@licznik+1
set @wynik=@wynik +SUBSTRING(@a,@licznik-1,1)
set @wynik=@wynik +SUBSTRING(@b,@licznik-1,1)
end
return reverse(@wynik)
end
select dbo.znaki('12345', 'adams')
Funkcja zwracająca osoby których nazwisko zaczyna się na literę podana w parametrze
create function rebel(@filtr varchar(3))
returns table
as
return (select imie, nazwisko from pracownicy where
nazwisko like @filtr) -- w tym miejscu nie ma begina i enda
go
select * from dbo.rebel('n%')
Sterowanie przepływem- pętle
alter proc procedura
@ilosc integer
as
begin
declare @nazw varchar(50) --ustawienie na dana wartosc
set @nazw='puste'
print @nazw
--ostatni rekord jest podstawiany
select @nazw = nazwisko from pracownicy order by nazwisko;
print @nazw
if len(@nazw)>10
print 'wieksza'
else print 'mniejsza'
declare @i integer
set @i = 0
while @i < @ilosc
begin
print 'Przebieg nr ' + convert(varchar,@i)
set @i = @i+1
end
end
exec procedura 5;
Procedura dzieląca adres na poszczególne fragmenty
create procedure adres @adres_caly varchar(100) as
begin
declare @dl_ul integer
declare @pozycja integer
declare @reszta varchar(100)
set @reszta = @adres_caly
set @pozycja = charindex(' ',@reszta)
print substring(@reszta, 0, @pozycja+1)
while (len(@reszta)>2)
begin
set @reszta=substring(@reszta, @pozycja+1, len(@reszta))
set @pozycja = charindex(' ',@reszta)
print substring(@reszta,0,@pozycja+1)
end
end
exec adres 'Piotrkowska 123/23 m.30 90-123 Łódź '
Procedura zwracająca w ‘wiadomości’ co drugiego pracownika którego imie zaczyna się na <parametr>
create procedure zadanie4
@param varchar(1)
as
begin
declare @nazw varchar(50)
declare @imie varchar(50)
declare @wiek int
declare @licz int
set @licz=1
if @param is null
begin
declare cr_pracownicy cursor scroll
for select imie, nazwisko, wiek from pracownicy
open cr_pracownicy
fetch last from cr_pracownicy into @imie, @nazw, @wiek
while @@Fetch_status = 0
begin
print @imie + ' ' + @nazw + ' ' + cast (@wiek as varchar)
+ 'lat Pracownik nr: ' + cast(@licz as varchar)
fetch prior from cr_pracownicy into @imie, @nazw, @wiek
fetch prior from cr_pracownicy into @imie, @nazw, @wiek
set @licz=@licz+2
end
close cr_pracownicy
deallocate cr_pracownicy
end
else
begin
declare cr_pracownicy cursor scroll
for select imie, nazwisko, wiek from pracownicy
where upper(nazwisko) like @param+'%'
open cr_pracownicy
fetch last from cr_pracownicy into @imie, @nazw, @wiek
while @@Fetch_status = 0
begin
print @imie + ' ' + @nazw + ' ' + cast (@wiek as varchar)
+ 'lat Pracownik nr: ' + cast(@licz as varchar)
fetch next from cr_pracownicy into @imie, @nazw, @wiek
fetch next from cr_pracownicy into @imie, @nazw, @wiek
set @licz=@licz+2
end
close cr_pracownicy
deallocate cr_pracownicy
end
end
go
exec zadanie4 'k';