Note: When you login Oracle, execute SET SERVEROUTPUT ON before you start work. Don't forget */ in the end of every bloc

[answerhappygod]

Note When You Login Oracle Execute Set Serveroutput On Before You Start Work Don T Forget In The End Of Every Bloc 1 (28.39 KiB) Viewed 78 times

Note When You Login Oracle Execute Set Serveroutput On Before You Start Work Don T Forget In The End Of Every Bloc 2 (41.73 KiB) Viewed 78 times

Note When You Login Oracle Execute Set Serveroutput On Before You Start Work Don T Forget In The End Of Every Bloc 3 (19.1 KiB) Viewed 78 times

Note: When you login Oracle, execute SET SERVEROUTPUT ON before you start work. Don't forget */ in the end of every block. EXAMPLE 1: A simple function without parameters CREATE OR REPLACE FUNCTION dcount RETURN number AS V_count NUMBER; BEGIN SELECT count(*) INTO V count FROM dept; RETURN v_count; END dcount; Calling a function from an anonymous block: DECLARE v_counter NUMBER; BEGIN v_counter := dcount; DBMS_OUTPUT.PUT LINE (v_counter); END; EXAMPLE 2: A simple function with one IN parameter CREATE OR REPLACE FUNCTIONdcount (p_deptno In number) RETURN number AS V_count NUMBER;

BEGIN SELECT count(*) INTO V count FROM dept wheredeptno<p_deptno; RETURN v_count: END dcount: Calling a function from an anonymous block: DECLARE v_counter NUMBER; BEGIN v_counter := dcount (50); DBMS_OUTPUT.PUT_LINE (v_counter); END; Calling a function from an SQL statement: SELECT dcount(50) FROM dual; Remember, if your procedure or function does not compile or reports as having an error, you can execute the command SHOW ERRORS to display recent error details. Before you begin, create the ENEW and DNEW tables using the following statement: CREATE OR REPLACE TABLE ENEW AS SELECT * FROM EMP; CREATE OR REPLACE TABLE DNEW AS SELECT * FROM DEPT; TASK 1 Write a function that counts the number of rows in the ENEW table where the employee earns more than $1200. Run the function from an anonymous block and display the result. TASK 2 Write a function that will model salary increases for the coming year (Use the ENEW table). The percent increase value should be passed into the function along with the employee number. The function should calculate the new salary after the increase has

been applied for each employee. Finally, call the function from an SQL statement using a 6 percent increase in salary. TASK 3 Write a function and pass a department number to it. If the DNEW table does not contain that department number, return a FALSE value, otherwise return a TRUE value. Print the appropriate message in the calling program based on the result.