Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SET SERVEROUTPUT ON;
- DECLARE
- najwieksza INTEGER;najmniejsza INTEGER;
- BEGIN
- SELECT MAX(sal) INTO najwieksza FROM emp;
- SELECT MIN(sal) INTO najmniejsza FROM emp;
- DBMS_OUTPUT.put_line('min:'||najmniejsza);
- DBMS_OUTPUT.put_line('max:'||najwieksza);
- END;
- DECLARE
- zmienna INTEGER;
- BEGIN
- SELECT COUNT(*) INTO zmienna FROM emp WHERE mgr IS NULL;
- DBMS_OUTPUT.put_line(zmienna);
- END;
- SELECT * FROM emp;
- CREATE OR REPLACE PROCEDURE proc1(nazwa VARCHAR,lokalizacja VARCHAR) AS
- BEGIN
- INSERT INTO DEPT VALUES((SELECT MAX(deptno)+10 FROM dept),nazwa,lokalizacja);
- END;
- --execute proc1('testDzial','Warszawa')
- --select * from dept
- CREATE OR REPLACE TRIGGER tri1
- BEFORE INSERT OR UPDATE
- ON EMP
- FOR EACH ROW
- BEGIN
- IF :NEW.sal IS NULL THEN
- raise_application_error(-20111, 'pensja jest nieznana');
- END IF;
- END;
- --insert into emp values(699,'tak','prawnik','7902',current_date,890,0,20);
- --select * from emp;
- CREATE OR REPLACE PROCEDURE proc2(nazwisko VARCHAR,pensja INT) AS
- zmienna int;
- BEGIN
- SELECT COUNT(*)INTO zmienna FROM emp WHERE ename=nazwisko;
- IF zmienna=0 THEN
- DBMS_OUTPUT.put_line('nie istnieje pracownik o podanym nazwisku');
- ELSE
- SELECT sal INTO zmienna FROM emp WHERE ename=nazwisko;
- IF zmienna<pensja THEN
- UPDATE emp SET sal=pensja WHERE ename=nazwisko;
- END IF;
- END IF;
- END;
- --select * from emp;
- --execute proc2('ALLEN',2000);
- CREATE OR REPLACE TRIGGER tri2
- BEFORE INSERT
- ON EMP
- FOR EACH ROW
- BEGIN
- IF :NEW.mgr IS NULL THEN
- SELECT empno INTO :NEW.mgr FROM emp WHERE hiredate=(SELECT MIN(hiredate) FROM emp);
- END IF;
- END;
- INSERT INTO emp VALUES(997,'Michxs','CLERK',NULL,CURRENT_DATE,900,0,20);
- SELECT * FROM emp;
- CREATE OR REPLACE PROCEDURE proc3(liczba int)AS
- SREDNIA int;
- CURSOR kursor IS SELECT ename, sal FROM emp;
- wiersz kursor%ROWTYPE;
- BEGIN
- SELECT AVG(sal) INTO srednia FROM emp;
- OPEN kursor;
- LOOP
- FETCH kursor INTO wiersz;
- EXIT WHEN kursor%NOTFOUND;
- IF wiersz.sal>srednia THEN
- UPDATE emp SET sal=sal-liczba WHERE ename=wiersz.ename;
- DBMS_OUTPUT.put_line('zmniejszono pensje '||wiersz.ename);
- ELSE
- UPDATE emp SET sal=sal+liczba WHERE ename=wiersz.ename;
- DBMS_OUTPUT.put_line('zwiekszono pensje '||wiersz.ename);
- END IF;
- END LOOP;
- CLOSE kursor;
- END;
- --execute proc3(1);
- --select * from emp;
- CREATE OR REPLACE TRIGGER tri3
- BEFORE INSERT OR DELETE OR UPDATE
- ON EMP
- FOR EACH ROW
- DECLARE tmp INT;
- BEGIN
- IF inserting THEN
- SELECT MIN(sal)INTO tmp FROM emp;
- IF :NEW.sal<tmp THEN
- raise_application_error(-20111, 'za mala pensja');
- END IF;
- SELECT MAX(sal)INTO tmp FROM emp;
- IF :NEW.sal>tmp THEN
- raise_application_error(-20111, 'za duza pensja');
- END IF;
- ELSIF DELETING THEN
- IF :old.deptno IS NOT NULL THEN
- raise_application_error(-20111, 'nie mozna usunac pracownika');
- END IF;
- ELSE
- IF :old.hiredate!=:NEW.hiredate THEN
- raise_application_error(-20111,'data ulegla zmianie');
- END IF;
- END IF;
- END;
- DELETE FROM emp WHERE ename='FERRARI'
- INSERT INTO emp VALUES('433','FERRARI','ANALYST',7902,CURRENT_DATE,2000,0,0);
- UPDATE EMP SET hiredate=CURRENT_DATE WHERE ename='JAMES'
- SELECT * FROM emp;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement