There are several columns, named from 1 to 12, they contain different values, they are not important, their number is important.

Example:

1 2 3 4 ...(столбцы) * * * * * * (значения) * * *

I am writing a procedure that should read the sum of the numbers of column values ​​to a given one. Those. taking the input parameter value, for example, 3, the procedure should calculate the sum of the quantities of values ​​in the columns to the third inclusive.

 1 2 3 2+ 2+ 2 = 6

It is logical that the loop should be used, however, due to the weak knowledge in plsql and in programming in general, it stalled almost at the very beginning. Help to implement a cycle to perform this operation.

  • You don't have to do a loop. Count the number of values ​​for each column COUNT (1), COUNT(2) ... and add their values. - vikolyada
  • @vikolyada Thanks for the tip! But nevertheless - you need to know the amount to a certain, and not all. How can this be beautifully described? - n0n4m3
  • one
    For example, you can make a CASE 'входной параметр с числом столбцов' WHEN 1 THEN COUNT(1) WHEN 2 THEN COUNT(1) + COUNT(2) ... END . Maybe not the best option, but in theory it should work. - vikolyada
  • @vikolyada, CASE WHEN 'input parameter with the number of columns'> = 1 THEN COUNT (1) ELSE 0 END + CASE WHEN 'input parameter with the number of columns'> = 2 THEN COUNT (2) ELSE 0 END + ... - German Borisov
  • one
    You can use UNPIVOT - pegoopik

1 answer 1

did not understand, explain with an example:

 CREATE TABLE tmp_data ( c1 NUMBER, c2 NUMBER, c3 NUMBER, c4 NUMBER, c5 NUMBER, c6 NUMBER, c7 NUMBER, c8 NUMBER, c9 NUMBER, c10 NUMBER, c11 NUMBER, c12 NUMBER ); / INSERT INTO tmp_data (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12) VALUES ( 1, 1, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); / INSERT INTO tmp_data (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12) VALUES ( NULL, 1, 1, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); / INSERT INTO tmp_data (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12) VALUES ( 1, NULL, 1, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ); / CREATE OR REPLACE FUNCTION F_COUNT_RS( A_START IN NUMBER DEFAULT 1, A_END IN NUMBER) RETURN NUMBER IS TYPE l_col_sum_typ IS TABLE OF NUMBER INDEX BY PLS_INTEGER; l_c_sum l_col_sum_typ ; l_end NUMBER := A_END; l_result NUMBER :=0; BEGIN SELECT COUNT(t.c1), COUNT(t.c2), COUNT(t.c3), COUNT(t.c4), COUNT(t.c5), COUNT(t.c6), COUNT(t.c7), COUNT(t.c8), COUNT(t.c9), COUNT(t.c10), COUNT(t.c11), COUNT(t.c12) INTO l_c_sum(1), l_c_sum(2), l_c_sum(3), l_c_sum(4), l_c_sum(5), l_c_sum(6), l_c_sum(7), l_c_sum(8), l_c_sum(9), l_c_sum(10), l_c_sum(11), l_c_sum(12) FROM tmp_data t; IF l_end IS NULL OR l_end > l_c_sum.count THEN l_end := l_c_sum.count; END IF; FOR idx IN A_START..l_end LOOP l_result := l_result+l_c_sum(idx); dbms_output.put_line(idx|| l_c_sum(idx)); END LOOP; RETURN l_result; END F_COUNT_RS; / SELECT F_COUNT_RS(1,3) FROM dual;
  • Can it be easier then through dynamic SQL to do than so hardcore? ))) - Anatoly Ernst