Advertisement
Maks140888

Untitled

May 26th, 2022
610
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. create or replace function count_langs_employee(worker_name varchar)
  2. return number
  3. is
  4.     counter number;
  5. begin
  6.     select count(language)
  7.     into counter
  8.     from table_A
  9.     where ename = worker_name;
  10.    
  11.     return counter;
  12. end;
  13.  
  14. CREATE or replace type native_speaker as object(
  15.     i VARCHAR(50)
  16. );
  17.  
  18. create or replace type native_speakers as TABLE of native_speaker;
  19.  
  20. create or replace function language_speakers(lang VARCHAR)
  21. return native_speakers as all_speakers  native_speakers;
  22. begin
  23.     select native_speaker(ename)
  24.     bulk collect into all_speakers
  25.     from table_A
  26.     where language = lang;
  27.  
  28.     return all_speakers;
  29. end;
  30.  
  31. create or replace function best_speaker(lang varchar)
  32. return varchar as best_speaker varchar(50);
  33. begin
  34.     select listagg(i, ' ')
  35.     into best_speaker
  36.     from TABLE(language_speakers(lang))
  37.     where count_langs_employee(i) = (select max(count_langs_employee(i)) from table(language_speakers(lang)));  
  38.  
  39.     return best_speaker;
  40. end;
  41.  
  42. select language, best_speaker(language) from table_A
  43. group by language
  44. order by length(best_speaker(language))
  45.  
Advertisement
RAW Paste Data Copied
Advertisement