SHARE
TWEET

Untitled

a guest Oct 15th, 2019 99 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE PROCEDURE vypis_studentov ( rocnik   IN student.rocnik%TYPE )
  2. AS
  3.     CURSOR cur1 ( p_rocnik  student.rocnik%TYPE )
  4.     IS
  5.         SELECT
  6.              st.os_cislo,
  7.              ou.meno,
  8.              ou.priezvisko,
  9.              st.st_skupina
  10.          FROM
  11.              priklad_db2.student st
  12.              JOIN priklad_db2.os_udaje ou USING ( rod_cislo )
  13.          WHERE
  14.              st.rocnik = p_rocnik
  15.          ORDER BY
  16.              st.st_skupina,
  17.              ou.priezvisko,
  18.              ou.meno;
  19.  
  20.     TYPE t_st_rec IS RECORD (
  21.         oc         student.os_cislo%TYPE,
  22.         meno       os_udaje.meno%TYPE,
  23.         priezv     os_udaje.priezvisko%TYPE,
  24.         skupina    student.st_skupina%TYPE );
  25. --  st_rec  t_st_rec;
  26.     st_rec  cur1%ROWTYPE;
  27. BEGIN
  28.     OPEN cur1(rocnik);
  29.         LOOP FETCH cur1 INTO st_rec;
  30.             IF cur1%notfound THEN EXIT;
  31.             ELSE DBMS_OUTPUT.put( st_rec.os_cislo/*oc*/ || ' ');
  32.                 DBMS_OUTPUT.put(st_rec.meno|| ' ' || st_rec.priezvisko/*priezv*/ || ' ' );
  33.                 DBMS_OUTPUT.put_line(st_rec.st_skupina/*skupina*/);
  34.             END IF;
  35.         END LOOP;
  36.     CLOSE cur1;
  37. END;
  38. /
  39.  
  40. SET serveroutput ON;
  41.  
  42. exec vypis_studentov(2);
  43.  
  44. SELECT table_name FROM tabs;
  45.  
  46. SELECT 'drop table ' || table_name || ';' FROM tabs WHERE table_name LIKE 'P\_%' escape '\';
  47.  
  48.  
  49.  
  50.  
  51.  
  52.  
  53.  
  54. drop table P_OSOBA;
  55. drop table P_ZTP;
  56. drop table P_TYP_POSTIHNUTIA;
  57. drop table P_ZAMESTNANEC;
  58. drop table P_ZAMESTNAVATEL;
  59. drop table P_MESTO;
  60. drop table P_OKRES;
  61. drop table P_KRAJ;
  62. drop table P_KRAJINA;
  63. drop table P_ODVOD_PLATBA;
  64. drop table P_PRISPEVKY;
  65. drop table P_POBERATEL;
  66. drop table P_POISTENIE;
  67. drop table P_HISTORIA;
  68. drop table P_TYP_PRISPEVKU;
  69. drop table P_PLATITEL;
  70.  
  71.  
  72.  
  73. drop type t_adresa;
  74.  
  75. CREATE or replace TYPE t_adresa
  76. is object
  77. (
  78.    mesto VARCHAR2(30),
  79.    ulica VARCHAR2(50),
  80.    psc CHAR(5)
  81. );
  82. /
  83.  
  84.  
  85. select ulica from p_osoba;
  86. desc p_osoba;
  87.  
  88.  
  89. drop type t_osoba;
  90. CREATE or replace TYPE t_osoba
  91. is object
  92. (
  93.    meno varchar2(20),
  94.    priezvisko varchar2(30),
  95.    rod_cislo char(11),
  96.    adresa t_adresa
  97. );
  98. /
  99.  
  100. create or replace procedure vypis ( p_adresa in t_adresa )
  101. as
  102. begin    
  103.    dbms_output.put_line(p_adresa.mesto || ' ' || p_adresa.ulica || ' ' || p_adresa.psc);
  104. end;
  105. /
  106.  
  107. set serveroutput on;
  108.  
  109. declare
  110. adresa t_adresa;
  111. begin
  112.    adresa := t_adresa('Nededza', 'Stredna', 01302);
  113.    vypis(adresa);
  114. end;
  115. /
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top