SHARE
TWEET

pl/sql excersises

kaiser96 Oct 14th, 2019 (edited) 28 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --301
  2. DECLARE
  3.             v_date DATE := TO_DATE('2005.01.01.', 'YYYY.MM.DD.');
  4.             FUNCTION orders(cust_id NUMBER, date_in DATE)
  5.             RETURN NUMBER IS
  6.             result NUMBER;
  7.             BEGIN
  8.             SELECT COUNT(d2.order_id) INTO result
  9.             FROM OE.customers d1
  10.             INNER JOIN OE.orders d2
  11.             ON d1.customer_id = d2.customer_id
  12.             WHERE d1.customer_id = cust_id AND order_date >= date_in;
  13.             RETURN result;
  14.             END;
  15.         BEGIN
  16.             FOR i IN
  17.                 (SELECT cust_first_name, cust_last_name, customer_id
  18.                 FROM OE.customers
  19.                 WHERE cust_first_name LIKE 'T%' OR cust_last_name LIKE 'T%')
  20.             LOOP
  21.                 DBMS_OUTPUT.put_line(i.cust_first_name || ' ' || i.cust_last_name || ' ' || orders(i.customer_id, v_date));
  22.             END LOOP;
  23.         END;
  24.  
  25. --302
  26.  
  27. DECLARE
  28. sqrroot FLOAT(5);
  29. squared NUMBER(5);
  30. absolute NUMBER(5);
  31.     PROCEDURE szamolj(n IN NUMBER, sqrt_out OUT FLOAT, squared_out OUT NUMBER, absolute_out OUT NUMBER) IS
  32.     BEGIN
  33.     IF n>0 THEN
  34.         sqrt_out:=SQRT(n);
  35.     END IF;
  36.     absolute_out:=ABS(n);
  37.     squared_out:=n*n;
  38.     END szamolj;  
  39. BEGIN
  40. FOR i IN -100..100
  41. LOOP
  42.  szamolj(i,sqrroot,squared,absolute);
  43.  DBMS_OUTPUT.PUT_LINE(i||' '||sqrroot||' '||squared||' '||absolute);
  44. END LOOP;
  45. END;
  46.  
  47.  
  48.  
  49. --303
  50. CREATE TABLE sajat_hr_employees AS SELECT * FROM HR.employees;
  51.  
  52.  
  53. CREATE OR REPLACE PROCEDURE wagechng(emp_id IN NUMBER,percentage IN NUMBER, fname OUT VARCHAR2, lname OUT VARCHAR2, new_wage OUT NUMBER)
  54. IS
  55. BEGIN
  56.     UPDATE sajat_hr_employees SET sajat_hr_employees.SALARY=sajat_hr_employees.SALARY*percentage
  57.     WHERE sajat_hr_employees.EMPLOYEE_ID=emp_id
  58.     returning sajat_hr_employees.first_name,sajat_hr_employees.last_name,sajat_hr_employees.salary INTO fname, lname,new_wage;
  59. END;
  60.  
  61. /*
  62. create or replace procedure ch_wage(azon in number, szazalek in number) is
  63.     wages number;
  64.     nameofperson varchar2(100);
  65.     begin
  66.     update sajat_hr_employees
  67.     set salary = salary + salary * (szazalek/100) where employee_id = azon;
  68.     select shre.salary into wages from sajat_hr_employees shre where shre.employee_id = azon;
  69.     select shre.first_name || ' ' || shre.last_name into nameofperson from sajat_hr_employees shre where shre.employee_id = azon;
  70.     DBMS_OUTPUT.PUT_LINE(nameofperson || ' ' || wages);
  71.     end ch_wage;
  72. begin
  73. ch_wage(120,200);
  74. end;
  75. */
  76.  
  77. --304
  78. DECLARE
  79. fname VARCHAR2(50);
  80. lname VARCHAR2(50);
  81. wage NUMBER;
  82. emp_id NUMBER(5);
  83. BEGIN
  84.     fname:='Alexander';
  85.     lname:='Hunold';
  86.     SELECT sajat_hr_employees.employee_id INTO emp_id FROM sajat_hr_employees
  87.     WHERE first_name=fname AND sajat_hr_employees.last_name=lname;
  88.     wagechng(emp_id,8/10,fname,lname,wage);
  89.     DBMS_OUTPUT.PUT_LINE (fname || ' ' || lname || ' ' || wage);
  90. END;
  91.  
  92. --308
  93. CREATE OR REPLACE PROCEDURE orderlist(fname IN VARCHAR2,lname IN VARCHAR2)
  94. IS
  95. BEGIN
  96. FOR i IN (SELECT DISTINCT  pi.product_name FROM OE.customers cus join oe.orders ord
  97. ON cus.customer_id=ord.customer_id join oe.order_items oi
  98. ON ord.order_id=oi.order_id join oe.product_information pi
  99. ON oi.product_id=pi.product_id
  100. WHERE cus.cust_first_name=fname AND cus.cust_last_name=lname
  101. )
  102. LOOP
  103. DBMS_OUTPUT.PUT_LINE(i.product_name);
  104. END LOOP;
  105. END;
  106.  
  107. --309
  108. BEGIN
  109. orderlist('Elizabeth','Brown');
  110. END;
  111.  
  112.  
  113. /*
  114. 401 15-OCT-19   Írjunk blokkot, amelynek a deklarációs részében deklarálunk egy függvényt. A függvény paraméterként kapott ügyfélnévhez megkeresi és visszaadja az ügyfél azonosítóját.  A blokkból hívjuk meg a függvényt olyan ügyfélnevekkel, amelyekre: - nincs ügyfél, - több ügyfél van. Futtassuk a blokkot. Ha kivételt kapunk, akkor kapjuk el, és írjuk ki a hiba kódját és üzenetét (minden kivétel esetén).
  115. 402 15-OCT-19   Írjunk tárolt eljárást, amely paraméterként kapott ügyfél nevéhez visszaadja az ügyfél születési dátumát és a nemét. (Itt nem kell kivételt kezelni.)
  116. 403 15-OCT-19   Írjunk blokkot, amely meghívja az előző tárolt eljárást. Hívjuk meg úgy is, hogy kivételt dob, és vizsgáljuk meg, hogy mi történik. Bővítsük a blokkunkat úgy, hogy a select into által okozott kivételeket elkapja, majd írja ki, hogy mi volt a hiba.
  117. 404 15-OCT-19   Hozzunk létre táblát csaladtagok néven. A tábla oszlopaiban tároljuk a customers tábla azonosítóját (külső kulcsként hivatkozzunk rá), és a családtag nevét. A tábla elsődleges kulcsa legyen a két oszlop együtt.
  118. 405 15-OCT-19   Írjunk tárolt függvényt, amely az előző feladat táblájába felvesz egy sort. A függvény a következő paramétereket kapja: az ügyfél azonosítója és a családtag neve.Ha a függvény rendben lefutott adja vissza a beszúrt sort. Ha kivételt kapunk amiatt, hogy egy ügyfélhez két azonos nevű családtagot veszünk fel, akkor a kapott kivételt kezeljük: írjuk ki a képernyőre, hogy melyik ügyfél (az ügyfél neve) milyen nevű családtagja már létezik. A függvény NULL értékekkel térjen vissza.
  119. 406 15-OCT-19   Írjunk tárolt eljárást, amely paraméterként kap egy ügyfélnevet és az ügyfél egy családtagjának a nevét. Az előző tárolt függvény meghívásával szúrjuk be a megfelelő sort az első feladat táblájába. Az eljárás írja képernyőre a függvény által visszaadott értékeket.
  120. 407 15-OCT-19   Írjunk blokkot, amely meghívja az előző feladat tárolt eljárását úgy, hogy az ügyfél családtagjának a neve null érték. A null érték miatt bekövetkező kivételt kezeljük, írjuk ki a képernyőre, hogy nincs megadva  a családtag neve. Ugyanebben a blokkban hívjuk meg az eljárást úgy is, hogy olyan ügyfelet adunk meg, amely nem létezik a customer táblában. Az emiatt kapott kivételt kezeljük, írjuk ki a képernyőre, hogy nincs ilyen ügyfél. Azt is próbáljuk ki, hogy olyan ügyfélnevet adjunk meg, amelyből kettő van az adatbázisban. Az ennek megfelelő kivételt hasonló módon kezeljük. A kivétek kezelése mindig csak arra az egy eljáráshiváshoz tartozzon.
  121. 408 15-OCT-19   Az előző blokk kivételei miatt alakítsuk át a 6. feladat eljárását úgy, hogy a 7. feladatban szereplő kivételeket kapja el, kezelje az ott megadottak szerint. A kivételkezelő rész csak 7. feladat kivételeivel foglalkozzon.
  122. 409 15-OCT-19   Írjunk tárolt eljárást, amely paraméterként kapott customer névhez a képernyőre listázza az ügyfelek megrendeléseinek azonosítóját (order_id), idejét (order_date) és a megrendelések végösszegét (order_total). A feladat megoldásához használjon explicit kurzort.
  123. 410 15-OCT-19   Hívjuk meg az előző feladat eljárását.
  124. */
  125.  
  126. --401
  127. CREATE TABLE sajat_oe_customers AS SELECT * FROM oe.customers;
  128.  
  129. DECLARE
  130.     person_id NUMBER;
  131. FUNCTION findID(fname IN VARCHAR2, lname IN VARCHAR2) RETURN NUMBER IS
  132.     person_id NUMBER;
  133.     BEGIN
  134.         SELECT cus.customer_id INTO person_id FROM sajat_oe_customers cus
  135.         WHERE cus.cust_first_name LIKE fname AND cus.cust_last_name LIKE lname;
  136.         RETURN person_id;
  137.     END;
  138. BEGIN
  139. person_id := findID('Elizabeth','Brown');
  140. DBMS_OUTPUT.PUT_LINE(person_id);
  141. EXCEPTION
  142.     WHEN NO_DATA_FOUND
  143.         THEN DBMS_OUTPUT.PUT_LINE(SQLCODE || '' || SQLERRM);
  144.     WHEN OTHERS
  145.         THEN DBMS_OUTPUT.PUT_LINE( 'Error code: ' || SQLCODE || '  Error message: ' || SQLERRM);
  146. END;
  147.  
  148. --402
  149. CREATE OR REPLACE PROCEDURE bd_gd(fname IN VARCHAR2, lname IN VARCHAR2, birthday OUT DATE, gender OUT CHAR)
  150. IS
  151. person oe.customers%ROWTYPE;
  152. BEGIN
  153. SELECT * INTO person FROM oe.customers cus
  154. WHERE cus.cust_first_name LIKE fname AND cus.cust_last_name LIKE lname;
  155. birthday := person.date_of_birth;
  156. gender := person.gender;
  157. END;
  158.  
  159. DECLARE
  160. dayofbirth DATE;
  161. gndr CHAR;
  162. BEGIN
  163. bd_gd('Elizabeth','Brown', dayofbirth, gndr);
  164. DBMS_OUTPUT.PUT_LINE('day of birth: ' || dayofbirth || '  gender: ' || gndr );
  165. END;
  166.  
  167. --403
  168.  
  169. CREATE OR REPLACE PROCEDURE bd_gd2(fname IN VARCHAR2, lname IN VARCHAR2, birthday OUT DATE, gender OUT CHAR)
  170. IS
  171. person oe.customers%ROWTYPE;
  172. BEGIN
  173. SELECT * INTO person FROM oe.customers cus
  174. WHERE cus.cust_first_name LIKE fname AND cus.cust_last_name LIKE lname;
  175. birthday := person.date_of_birth;
  176. gender := person.gender;
  177. END;
  178.  
  179. DECLARE
  180. dayofbirth DATE;
  181. gndr CHAR;
  182. BEGIN
  183. bd_gd2('Elizabeth','Brown', dayofbirth, gndr);
  184. DBMS_OUTPUT.PUT_LINE('day of birth: ' || dayofbirth || '  gender: ' || gndr );
  185. EXCEPTION
  186.     WHEN NO_DATA_FOUND
  187.         THEN DBMS_OUTPUT.PUT_LINE( 'Could not find name. Error code: ' || SQLCODE || '  Error message: ' || SQLERRM);
  188.     WHEN TOO_MANY_ROWS
  189.         THEN DBMS_OUTPUT.PUT_LINE( 'Error code: ' || SQLCODE || '  Error message: ' || SQLERRM);
  190. END;
  191.  
  192. --404
  193. --a sajat_oe-ben beállítottam primary key-nek a customer_id oszlopot
  194. CREATE TABLE csaladtag
  195. (   customer_id NUMBER(6,0) NOT NULL,
  196.     family_member_name VARCHAR2(100) NOT NULL,
  197.     constraint csaladtag_pk primary key (customer_id, family_member_name),
  198.     constraint fk_customer_id foreign key (customer_id) references sajat_oe_customers(CUSTOMER_ID)
  199. );
  200.  
  201. --405
  202. CREATE OR REPLACE FUNCTION felvesz(cust_id IN NUMBER, name_of_fmember IN VARCHAR2) RETURN VARCHAR2
  203. IS
  204. letezik EXCEPTION;
  205. voltmar BOOLEAN := FALSE;
  206. customer_name VARCHAR2(100);
  207. added_id NUMBER;
  208. added_fmember VARCHAR2(100);
  209. BEGIN
  210.     FOR i IN (SELECT customer_id, family_member_name FROM csaladtag
  211.     WHERE customer_id = cust_id)
  212.     LOOP
  213.     IF i.family_member_name LIKE name_of_fmember
  214.         THEN
  215.         voltmar := TRUE;
  216.     END IF;
  217.     END LOOP;
  218.     IF voltmar = TRUE
  219.         THEN RAISE letezik;
  220.     ELSE
  221.         INSERT INTO u_dgo2yb.csaladtag( CUSTOMER_ID,FAMILY_MEMBER_NAME)
  222.         VALUES(cust_id, name_of_fmember);
  223.     END IF;
  224.     FOR i IN (SELECT cst.customer_id, cst.family_member_name FROM csaladtag cst
  225.     WHERE cst.customer_id = cust_id)
  226.     LOOP
  227.     DBMS_OUTPUT.PUT_LINE('azon: ' || i.customer_id || ' csaladtag: ' || i.family_member_name);
  228.     END LOOP;
  229.     RETURN NULL;
  230.     EXCEPTION
  231.         WHEN letezik
  232.             THEN
  233.             SELECT cust_first_name || ' ' || cust_last_name INTO customer_name FROM sajat_oe_customers cus
  234.             WHERE cus.customer_id = cust_id;
  235.             DBMS_OUTPUT.PUT_LINE('This family member is already registered to this customer...
  236.            Name of customer: ' || customer_name || '. Name of family member: ' || name_of_fmember || '.');
  237.             RETURN NULL;
  238.    
  239. END;
  240.  
  241. DECLARE
  242. null_ertek VARCHAR2(1);
  243. BEGIN
  244. null_ertek:= felvesz(101, 'Pelda Peter3');
  245. END;
  246.  
  247.  
  248. --406
  249. CREATE OR REPLACE PROCEDURE recall405(cust_name IN VARCHAR2, name_of_fmember IN VARCHAR2)
  250. IS
  251. cust_id NUMBER;
  252. null_ertek VARCHAR2(1);
  253. BEGIN
  254. SELECT cus.customer_id INTO cust_id FROM sajat_oe_customers cus
  255. WHERE (cus.cust_first_name || ' ' || cus.cust_last_name) LIKE cust_name;
  256. null_ertek := felvesz(cust_id,name_of_fmember);
  257. END;
  258. /*
  259. hasznalat:
  260. declare
  261. begin
  262. recall405('Manisha Taylor','Pelda Peter');
  263. end;
  264. */
  265.  
  266.  
  267. --407
  268. /*
  269. declare
  270. null_error exception;
  271. PRAGMA EXCEPTION_INIT(null_error, -1400);
  272. begin
  273. recall405('Manisha Taylor','Pelda Peter');
  274. EXCEPTION
  275.     when no_data_found
  276.         then DBMS_OUTPUT.PUT_LINE('Error! Please input a customer''s correct name.');
  277.     when too_many_rows
  278.         then DBMS_OUTPUT.PUT_LINE('Error! This person''s name already exists in the database more than once.');
  279.     when null_error
  280.             then DBMS_OUTPUT.PUT_LINE('Error! Please input a correct family member name.');
  281. end;
  282. */
  283. DECLARE
  284. null_error EXCEPTION;
  285. PRAGMA EXCEPTION_INIT(null_error, -1400);
  286. BEGIN
  287. recall405('Manisha Taylor','Pelda Peter');
  288. EXCEPTION
  289.     WHEN NO_DATA_FOUND
  290.         THEN DBMS_OUTPUT.PUT_LINE('Error! Please input a customer''s correct name.');
  291.     WHEN TOO_MANY_ROWS
  292.         THEN DBMS_OUTPUT.PUT_LINE('Error! This person''s name already exists in the database more than once.');
  293.     WHEN null_error
  294.             THEN DBMS_OUTPUT.PUT_LINE('Error! Please input a correct family member name.');
  295. END;
  296.  
  297. --408
  298. CREATE OR REPLACE PROCEDURE recall408(cust_name IN VARCHAR2, name_of_fmember IN VARCHAR2)
  299. IS
  300. cust_id NUMBER;
  301. null_ertek VARCHAR2(1);
  302. null_error EXCEPTION;
  303. PRAGMA EXCEPTION_INIT(null_error, -1400);
  304. BEGIN
  305. SELECT cus.customer_id INTO cust_id FROM sajat_oe_customers cus
  306. WHERE (cus.cust_first_name || ' ' || cus.cust_last_name) LIKE cust_name;
  307. null_ertek := felvesz(cust_id,name_of_fmember);
  308. EXCEPTION
  309.     WHEN NO_DATA_FOUND
  310.         THEN DBMS_OUTPUT.PUT_LINE('Error! Please input a correct name.');
  311.     WHEN TOO_MANY_ROWS
  312.         THEN DBMS_OUTPUT.PUT_LINE('Error! This name already exists in the database more than once.');
  313.     WHEN null_error
  314.         THEN DBMS_OUTPUT.PUT_LINE('Error! Please input a correct family member name.');
  315. END;
  316.  
  317. --hasznalat:
  318. DECLARE
  319. BEGIN
  320. recall408('Manisha Taylor','Pelda Peter');
  321. END;
  322.  
  323. /*
  324. --kezdetleges kurzor
  325. declare
  326. cursor proba is select cus.cust_first_name || ' ' || cus.cust_last_name from oe.customers cus;
  327. ide_tedd oe.customers.cust_first_name%type;
  328.  
  329. begin
  330. open proba;
  331.  
  332. loop
  333. fetch proba into ide_tedd;
  334. if proba%notfound
  335. then
  336. exit;
  337. end if;
  338. DBMS_OUTPUT.PUT_LINE(ide_tedd);
  339. end loop;
  340. DBMS_OUTPUT.PUT_LINE(proba%rowcount);
  341. close proba;
  342. end;
  343. */
  344.  
  345. --409
  346. CREATE OR REPLACE PROCEDURE feladat409(customer_name IN VARCHAR2)
  347. IS
  348. CURSOR found_rows IS SELECT * FROM oe.orders ord WHERE ord.customer_id =
  349.     (SELECT cus.customer_id FROM oe.customers cus
  350.     WHERE (cus.cust_first_name || ' ' || cus.cust_last_name) = customer_name);
  351. fetch_into_this oe.orders%ROWTYPE;
  352. BEGIN
  353. OPEN found_rows;
  354. LOOP
  355. FETCH found_rows INTO fetch_into_this;
  356. IF found_rows%notfound
  357. THEN
  358. EXIT;
  359. END IF;
  360. DBMS_OUTPUT.PUT_LINE(fetch_into_this.order_id || '  ' || fetch_into_this.order_date || '  ' || fetch_into_this.order_total);
  361. END LOOP;
  362. CLOSE found_rows;
  363. END;
  364.  
  365. --410
  366. DECLARE
  367. BEGIN
  368. feladat409('Constantin Welles');
  369. END;
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