Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE PROCEDURE vypis_studentov ( rocnik IN student.rocnik%TYPE )
- AS
- CURSOR cur1 ( p_rocnik student.rocnik%TYPE )
- IS
- SELECT
- st.os_cislo,
- ou.meno,
- ou.priezvisko,
- st.st_skupina
- FROM
- priklad_db2.student st
- JOIN priklad_db2.os_udaje ou USING ( rod_cislo )
- WHERE
- st.rocnik = p_rocnik
- ORDER BY
- st.st_skupina,
- ou.priezvisko,
- ou.meno;
- TYPE t_st_rec IS RECORD (
- oc student.os_cislo%TYPE,
- meno os_udaje.meno%TYPE,
- priezv os_udaje.priezvisko%TYPE,
- skupina student.st_skupina%TYPE );
- -- st_rec t_st_rec;
- st_rec cur1%ROWTYPE;
- BEGIN
- OPEN cur1(rocnik);
- LOOP FETCH cur1 INTO st_rec;
- IF cur1%notfound THEN EXIT;
- ELSE DBMS_OUTPUT.put( st_rec.os_cislo/*oc*/ || ' ');
- DBMS_OUTPUT.put(st_rec.meno|| ' ' || st_rec.priezvisko/*priezv*/ || ' ' );
- DBMS_OUTPUT.put_line(st_rec.st_skupina/*skupina*/);
- END IF;
- END LOOP;
- CLOSE cur1;
- END;
- /
- SET serveroutput ON;
- exec vypis_studentov(2);
- SELECT table_name FROM tabs;
- SELECT 'drop table ' || table_name || ';' FROM tabs WHERE table_name LIKE 'P\_%' escape '\';
- drop table P_OSOBA;
- drop table P_ZTP;
- drop table P_TYP_POSTIHNUTIA;
- drop table P_ZAMESTNANEC;
- drop table P_ZAMESTNAVATEL;
- drop table P_MESTO;
- drop table P_OKRES;
- drop table P_KRAJ;
- drop table P_KRAJINA;
- drop table P_ODVOD_PLATBA;
- drop table P_PRISPEVKY;
- drop table P_POBERATEL;
- drop table P_POISTENIE;
- drop table P_HISTORIA;
- drop table P_TYP_PRISPEVKU;
- drop table P_PLATITEL;
- drop type t_adresa;
- CREATE or replace TYPE t_adresa
- is object
- (
- mesto VARCHAR2(30),
- ulica VARCHAR2(50),
- psc CHAR(5)
- );
- /
- select ulica from p_osoba;
- desc p_osoba;
- drop type t_osoba;
- CREATE or replace TYPE t_osoba
- is object
- (
- meno varchar2(20),
- priezvisko varchar2(30),
- rod_cislo char(11),
- adresa t_adresa
- );
- /
- create or replace procedure vypis ( p_adresa in t_adresa )
- as
- begin
- dbms_output.put_line(p_adresa.mesto || ' ' || p_adresa.ulica || ' ' || p_adresa.psc);
- end;
- /
- set serveroutput on;
- declare
- adresa t_adresa;
- begin
- adresa := t_adresa('Nededza', 'Stredna', 01302);
- vypis(adresa);
- end;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement