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
- 1995 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
Is record in oracle pl sql?
What are functions in sql?
What is procedure explain with program?
give the syntax of grant and revoke commands? : Sql dba
How do I run a pl sql program?
How long will it take to learn pl sql?
What is your daily office routine?
How global cursor can be declare with dynamic trigger ?
What is sql resultset?
What is pl sql block in dbms?
difference between anonymous blocks and sub-programs.
What is dba in sql? : SQL DBA
write an sql query to find names of employee start with 'a'? : Sql dba
How do I remove duplicates in two columns?
What are the types pl/sql code blocks?
