Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to find the average value of database nested tables". In the daily operation, I believe that many people have doubts about how to find the average value of database nested tables. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful for everyone to answer the doubt of "how to find the average value of database nested tables". Next, please follow the editor to study!

I created a SCHEMA-level number-type nested table:

CREATE OR REPLACE TYPE plch_numbers_t IS TABLE OF NUMBER/

Which options implement a function named plch_avg that averages all the elements in the above nested table so that the following blocks of code will show "3.5" after execution?

DECLARE l_numbers plch_numbers_t: = plch_numbers_t (1,2,3,4,5,6); BEGIN DBMS_OUTPUT.put_line (plch_avg (l_numbers)); END;/

(A)

CREATE OR REPLACE FUNCTION plch_avg (numbers_in IN plch_numbers_t) RETURN NUMBERIS l_index PLS_INTEGER: = numbers_in.FIRST; l_sum NUMBER: = 0 * * begin WHILE l_index IS NOT NULL LOOP l_sum: = l_sum + numbers_in (l_index); l_index: = numbers_in.NEXT (l_index); END LOOP; RETURN l_sum / numbers_in.COUNT;END;/

(B)

CREATE OR REPLACE FUNCTION plch_avg (numbers_in IN plch_numbers_t) RETURN NUMBERIS l_average NUMBER;BEGIN SELECT AVG (COLUMN_VALUE) INTO l_average FROM TABLE (numbers_in)

(C)

CREATE OR REPLACE FUNCTION plch_avg (numbers_in IN plch_numbers_t) RETURN NUMBERISBEGIN RETURN AVG (numbers_in); END;/

(D)

CREATE OR REPLACE FUNCTION plch_avg (numbers_in IN plch_numbers_t) RETURN NUMBERIS l_index PLS_INTEGER: = numbers_in.FIRST; l_average NUMBER: = 0 numbers_in.NEXT begin WHILE l_index IS NOT NULL LOOP l_average: = l_average + numbers_in (l_index) / numbers_in.count; l_index: = numbers_in.NEXT (l_index)

(E)

CREATE OR REPLACE FUNCTION plch_avg (numbers_in IN plch_numbers_t) RETURN NUMBERIS l_average NUMBER;BEGIN SELECT AVG (numbers_in (ROWNUM)) INTO l_average FROM numbers_in; RETURN lumped averageism

Measured answer ABC

C error PLS-00204: function or pseudo column 'AVG' can only be used in SQL statements

E error PL/SQL: ORA-00942: table or view does not exist

Answer ABD, B is the best answer, note that D may have errors in some cases.

At this point, the study on "how to find the average value of database nested tables" is over. I hope to be able to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.