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
- 2479 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
How do I run pl sql in sql developer?
What is a table partition?
how are mysql timestamps seen to a user? : Sql dba
What are sql commands?
how many triggers are allowed in mysql table? : Sql dba
What is the source code of a program?
What is difference between pl and sql?
Explain what is a subquery ?
How many tables can a sql database have?
What is sql resultset?
How do I get sql certification?
What is the current version of postgresql?
What is an ndf file?
What are the different sql languages?
What is the most restrictive isolation level? : Transact sql
