PL/SQL

1 / 8

# PL/SQL - PowerPoint PPT Presentation

PL/SQL. vytvor tabuÄ¾ku t1 s atribÃºtmi e,f vytvor dve premenne a,b naÄÃ­taj z tabuÄ¾ky hodnoty e,f a v prÃ­pade ze e&gt;1 vloÅ¾ hodnoty opaÄnÃ© f,e. CREATE TABLE T1( e INTEGER, f INTEGER ); DELETE FROM T1; INSERT INTO T1 VALUES(1, 3); INSERT INTO T1 VALUES(2, 4); DECLARE a NUMBER;

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about 'PL/SQL' - tucker

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

### PL/SQL

vytvor tabuľku t1 s atribútmi e,f

vytvor dve premenne a,b

načítaj z tabuľky hodnoty e,f a v prípade ze e>1

vlož hodnoty opačné f,e

CREATE TABLE T1(

e INTEGER,

f INTEGER

);

DELETE FROM T1;

INSERT INTO T1 VALUES(1, 3);

INSERT INTO T1 VALUES(2, 4);

DECLARE

a NUMBER;

b NUMBER;

BEGIN

SELECT e,f INTO a,b FROM T1 WHERE e>1;

INSERT INTO T1 VALUES(b,a);

END;

.

run;

Práca s PL/SQL

E F

--------- ----------

1 3

2 4

4 2

vytvor dve premenne

pre hodnoty e väčšie ako 2

ak druha hodnota je rovná 1 napíš opačné hodnoty

v opačnom prípade pridaj ku nim hodnotu 10

E F

------- ----------

1 3

2 4

4 2

12 14

DECLARE

a NUMBER;

b NUMBER;

BEGIN

SELECT e,f INTO a,b FROM T1

WHERE e>2;

IF b=1 THEN

INSERT INTO T1 VALUES(b,a);

ELSE

INSERT INTO T1 VALUES(b+10,a+10);

END IF;

END;

.

run;

Práca s PL/SQL
do tabulky t1 vlozte hodoty premennej x a jej y=x*x

pre x y intervalu 5 - 20

DECLARE

x NUMBER := 5;

BEGIN

LOOP

INSERT INTO T1 VALUES(x,x*x);

x := x+1;

EXIT WHEN x>20;

END LOOP;

END;

.

run;

Práca s PL/SQL

E F

---------- ----------

1 3

2 4

4 2

12 14

5 25

6 36

7 49

8 64

9 81

1. vytvorte

tabulku t1 s atributami a, b,c,d, kde

a - cele cislo

b - cele cislo

c - znakova premenna pozostavajuca zo 6 znakov

d - datova premenna

a- primarny kluc

b cudzi kluc z tabulky t2

hodnota a 10-20

tabulku t2 s atributami

b - cele cislo

f - text

g - desatinne cislo s intervalu 0.01-0.05

b - primarny kluc

zadajte do tabulky t1 take hodnoty, aby v premennej c bolo aspon 2x hodnota zzzz

CREATE table t2(

b number(2),

f VARCHAR(5),

g Number(4,2)

check (g between 0.01 and 0.50),

PRIMARY KEY(b));

insert into t2 values(1, 'asdf', 0.5);

insert into t2 values(11, 'asdf', 0.4);

CREATE table t1 (

a number(2),

b number(2),

c VARCHAR(5),

d date,

PRIMARY KEY(a),

FOREIGN KEY (b) REFERENCES t2(b),

check(a between 10 and 20)

);

insert into t1 values(11,1, 'zzz',TO_DATE('10-oct-1960'));

insert into t1 values(12,11, 'zzz',TO_DATE('01-jan-2008'));

Využitie jazyka PL/SQL
2. zmena hodnoty

v tabulke t1 zmente hodnotu c z hodnoty zzz na asdf ak b > 10

update t1 set c = 'asdf'

where b>10;

3. pridajte do tabulky t2 stlpce k, l celych cisel

alter table t2 add k number(2);

alter table t2 add l number(4);

4. pridavanie viac udajov

pridajte desat hodnot pre b = 2,4,6,... hodnoty k= 1...10, l = 1.4,9,16

declare

i number:=1;

j number;

begin

LOOP

j :=i*i;

insert into t2 (b,k,l) values (2*i, i, j);

i:=i+1;

exit when i>10;

END LOOP;

end;

Využitie jazyka PL/SQL
pridajte desat hodnot

pre b = 2,4,6,... hodnoty

k= 1...10,

l = 1.4,9,16

x1 f(x1)

-------- ----------

1 1

2 4

3 9

4 16

5 25

6 36

7 49

8 64

9 81

10 100

declare

i number:=1;

j number;

begin

for i in 1..10 LOOP

j :=i*i;

insert into t2 (b, k,l) values (2*i, i, j);

END LOOP;

end;

select k"x1",l"f(x1)"

from t2

where k>0;

Využitie jazyka PL/SQL
Vypíš hodnoty funkcie

F(x)=x*x

Pre xo= (i..kk)

Kde- i počiatočná hodnota x

Kk - konečná hodnota x

x1 f(x1)

---- ----------

4 16

5 25

6 36

7 49

8 64

declare

i number:=&i;

kk number:=&kk;

j number;

begin

LOOP

j :=i*i;

insert into t2 (b,k,l) values (2*i, i, j);

i:=i+1;

exit when i>kk;

END LOOP;

end;.

run

select k"x1",l"f(x1)"

from t2

where k>0;

Využitie jazyka PL/SQL