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
- 2596 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
Can we join 3 tables in sql?
explain the advantages and disadvantages of stored procedure? : Sql dba
What are local and global Indexes and where they are useful.
What is sql constant?
What is union and union all keyword in sql and what are their differences?
what happens when the column is set to auto increment and you reach the maximum value for that table? : Sql dba
What can sql server reporting services do?
When to use inner join and left join?
Which is faster truncate or drop?
Is pl sql better than sql?
Is primary key clustered or nonclustered?
What is cte?
what are the differences between procedure-oriented languages and object-oriented languages? : Sql dba
How do I save a stored procedure?
what are date and time data types in mysql? : Sql dba
