plsql_cursor_function

-- Cursor
SET SERVEROUTPUT ON;
DECLARE
ename employee.ename%TYPE;
eid employee.eid%TYPE;
esal employee.esal%TYPE;
edept employee.edept%TYPE;

CURSOR emp_cursor IS
SELECT eid, esal, edept FROM employee;

BEGIN
OPEN emp_cursor;

LOOP
FETCH emp_cursor INTO eid, esal, edept;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.put_line(eid || ' ' || esal || ' ' || edept);
END LOOP;

CLOSE emp_cursor;

END;
/

--Cursor 1

CREATE TABLE employee(
eid VARCHAR2(5) PRIMARY KEY,
ename VARCHAR2(30),
esal NUMBER,
edept VARCHAR2(10)
);

-- Insert records
        INSERT INTO employee VALUES('E01', 'Rajat', 14000, 'Sales');
        INSERT INTO employee VALUES('E02', 'Kamal', 24000, 'IT');
        INSERT INTO employee VALUES('E03', 'Nilaj', 40000, 'Accounts');
        INSERT INTO employee VALUES('E04', 'Suman', 38000, 'HR');
        INSERT INTO employee VALUES('E05', 'Ayush', 74000, 'Sales');
DECLARE
    var_empid employee.eid%TYPE;
    var_ename employee.ename%TYPE;
    var_sal employee.esal%TYPE;

    CURSOR EMP_CURSOR IS
    SELECT eid, ename, esal FROM employee;

BEGIN
OPEN EMP_CURSOR;
LOOP
    FETCH EMP_CURSOR INTO var_empid, var_ename, var_sal;
    EXIT WHEN EMP_CURSOR%NOTFOUND;
    IF(var_sal>30000) THEN
        DBMS_OUTPUT.put_line(var_empid || ' ' || var_ename || ' ' || var_sal);
    ELSE
        DBMS_OUTPUT.put_line(var_ename || 'Salary is less than 30000' );
    END IF;
END LOOP;
CLOSE EMP_CURSOR;
DBMS_OUTPUT.put_line('Done');
END;
/

--Function

DECLARE
n INTEGER :=&n;
cnt INTEGER :=0;
i INTEGER;
flag INTEGER;
-- flag = 1 means 'n' is prime otherwise 'n' is non-prime
FUNCTION is_prime(n IN INTEGER)
RETURN INTEGER
IS
f1 INTEGER;
n1 INTEGER;
r INTEGER;
k INTEGER;

BEGIN
n1 := FLOOR(n/2);
f1 := 1; -- f1=1 means the given number is prime
FOR k IN 2..n1
LOOP
r:= MOD(n,k);
IF r=0 THEN
f1:=0;
EXIT;
END IF;
END LOOP;
RETURN(f1);
END;

-- Main program starts
BEGIN
DBMS_OUTPUT.put_line('Prime numbers<= ' || n || '==>');
FOR i IN 2..n
LOOP
    flag := is_prime(i);
        IF(flag=1) THEN
            DBMS_OUTPUT.put(i || ' ');
            cnt := cnt+1;
            END IF;
END LOOP;
DBMS_OUTPUT.put_line(' ');
DBMS_OUTPUT.put_line('Total prime numbers=' || cnt);
END;
/

-- Function 1
CREATE OR REPLACE FUNCTION find_area (Len IN NUMBER, Wid IN NUMBER)
RETURN NUMBER
AS
varea NUMBER;
BEGIN
varea:=Len*Wid;
RETURN varea;
END;
/
-- calling the function
-- a special one-row, one-column table present by default in all Oracle databases.
SELECT find_area(5,20) FROM dual;

-- Array
DECLARE
    TYPE aarrays IS VARRAY(5) OF INTEGER;
    s INTEGER :=0;
    av NUMBER (5,2);
    sdev NUMBER(5,2);
    i INTEGER;
    a aarrays;
    n INTEGER;
    temp INTEGER :=0;
    res INTEGER :=0;
BEGIN
    a:= aarrays(50,40,30,20,10);
    n:= a.COUNT();
    FOR i IN 1..5
    LOOP
    s:= s+a(i);
    END LOOP;

    av:= s/n;

    FOR i IN 1..5
    LOOP
    temp := POWER((s-av),2);
    res := res+temp;
    END LOOP;
    res := res/n;
    sdev := res;
    DBMS_OUTPUT.put_line('Sum= '|| s || 'Average= ' || av || 'Sdev= ' || sdev);

END;
/