Interested to Buy Any Domain ? << Click Here >> for more details...
How we get all_group_function's(Sum,avg,count,max and min_value of a column(Sal) Using pl/sql anonymous block, with out using group function's.
You shouldn't use more than one select statement in entire the program.
Like cursor c is select * from <table_name>;
except this you can't use another select statement.
You can use no of variables as per requirement.
- 1 Answers
- 2401 Views
- I also Faced
- E-Mail Answers
Answer Posted / shwetha n k
DECLARE
TYPE REF_CUR_TYPE IS REF CURSOR;
CUR_VAR REF_CUR_TYPE;
V_SAL NUMBER(20);
N NUMBER(20) := 0;
V_AVG NUMBER(20);
V_MAX NUMBER(15);
V_MIN NUMBER(15);
BEGIN
OPEN CUR_VAR FOR SELECT SAL FROM EMP ORDER BY SAL DESC;
LOOP
FETCH CUR_VAR INTO V_SAL;
EXIT WHEN CUR_VAR%NOTFOUND;
N := N+V_SAL;
----DBMS_OUTPUT.PUT_LINE(V_SAL);
---- V_MAX := LEAST(V_SAL);
----V_MAX := GREATEST(V_SAL);
IF CUR_VAR%ROWCOUNT = 1 THEN
V_MAX := V_SAL;
DBMS_OUTPUT.PUT_LINE ('MAXIMUM SAL OF ALL EMPLOYEES:'||V_MAX);
END IF;
IF CUR_VAR%ROWCOUNT = 15 THEN
V_MAX := V_SAL;
DBMS_OUTPUT.PUT_LINE ('MINIMUM SAL OF ALL EMPLOYEES:'||V_MAX);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE TOTAL SALARY IS:'||N);
DBMS_OUTPUT.PUT_LINE('NO OF EMPLOYEES :' ||CUR_VAR%ROWCOUNT);
V_AVG := ROUND(N/CUR_VAR%ROWCOUNT,2);
DBMS_OUTPUT.PUT_LINE('AVERAGE SAL OF ALL EMPLOYEES:'||V_AVG);
---DBMS_OUTPUT.PUT_LINE('MINIMUM SAL OF ALL EMPLOYEES:'||V_MAX);
CLOSE CUR_VAR;
END;
| Is This Answer Correct ? | 0 Yes | 0 No |
Post New Answer View All Answers
Why is sql*loader direct path so fast?
What is trigger in pl sql?
Why do we use partitions in sql?
What is faster join or subquery?
how to check server status with 'mysqladmin'? : Sql dba
What is auto increment?
What is a join query?
Why do we use procedures in pl sql?
Is sql between inclusive?
what is commit? : Sql dba
Sql technical questions
Is sqlite free?
how to create a table index in mysql? : Sql dba
How many types of normalization are there?
How do I create a sql script?
