cursor

declare
num1 number;
begin
num1:= :Enter_any_number_andss;
dbms_output.put_line(num1);
declare
num2 number;
begin
num2:= :Enter_number2;
dbms_output.put_line(num2);
end;
end;




DECLARE var_rows number(5);
BEGIN
UPDATE employees
SET salary = salary + 1000 where EMPLOYEE_ID=102;
IF SQL%NOTFOUND THEN
dbms_output.put_line('None of the salaries where updated');
ELSIF SQL%FOUND THEN
var_rows := SQL%ROWCOUNT;
dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');
END IF;
END;
/
select * from employees;
describe employees; EMPLOYEE_ID



DECLARE
CURSOR C1 IS
SELECT FIRST_NAME,LAST_NAME FROM EMPLOYEES;
e_FIRST_NAME EMPLOYEES.FIRST_NAME%TYPE;
E_LAST_NAME EMPLOYEES.LAST_NAME%TYPE;
BEGIN
OPEN C1;
FETCH
C1 INTO e_FIRST_NAME,E_LAST_NAME;
DBMS_OUTPUT.PUT_LINE(e_FIRST_NAME || ' ' || E_LAST_NAME);
CLOSE C1;
END;





DECLARE
CURSOR JOB_CURSOR IS
SELECT * FROM HR.JOBS;
JOB_REC JOB_CURSOR%ROWTYPE;
BEGIN
IF NOT JOB_CURSOR%ISOPEN THEN
OPEN JOB_CURSOR;
END IF;
LOOP
FETCH JOB_CURSOR INTO JOB_REC;
EXIT WHEN JOB_CURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(JOB_REC.JOB_ID || ' ' || JOB_REC.JOB_TITLE || ' ' || JOB_REC.MIN_SALARY || ' ' || JOB_REC.MAX_SALARY);
END LOOP;
END;



DECLARE
CURSOR JOB_CURSOR IS
SELECT * FROM HR.JOBS;
JOB_REC JOB_CURSOR%ROWTYPE;
BEGIN

FOR JOB_REC IN JOB_CURSOR
LOOP
DBMS_OUTPUT.PUT_LINE(JOB_REC.JOB_ID || ' ' || JOB_REC.JOB_TITLE || ' ' || JOB_REC.MIN_SALARY);
END LOOP;
END;



1> DECLARE
2> CURSOR emp_cur IS
3> SELECT first_name, last_name, salary FROM emp_tbl;
4> emp_rec emp_cur%rowtype;
5> BEGIN
6> IF NOT sales_cur%ISOPEN THEN
7> OPEN sales_cur;
8> END IF;
9> FETCH sales_cur INTO sales_rec;
10> WHILE sales_cur%FOUND THEN
11> LOOP
12> dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name
13> || ' ' ||emp_cur.salary);
15> FETCH sales_cur INTO sales_rec;
16> END LOOP;
17> END;
18> /







DECLARE
R NUMBER(5,2);
AR NUMBER(7,2);
PERI NUMBER(5,2);
CH NUMBER(2);
BEGIN
R:= :ENTER_RADIUS_OF_CIRCLE;
CH:= :ENTER_CHARACTER_1_OR_2;
IF(CH=1) THEN
PERI:= 2*3.14*R;
DBMS_OUTPUT.PUT_LINE('PERIMETER IS' || ' ' || PERI);
ELSE
IF(CH=2) THEN
AR:=3.14*R*R;
DBMS_OUTPUT.PUT_LINE('AREA IS' || AR);
END IF;
END IF;
END;

SELECT * FROM DEPARTMENTS;

DECLARE
E_DEPARTMENT_ID DEPARTMENTS.DEPARTMENT_ID%TYPE;
D_DEPARTMENT_ID DEPARTMENTS.MANAGER_ID%TYPE;
BEGIN
D_DEPARTMENT_ID := :ENTER_DEPARTMENT_ID;
SELECT DEPARTMENT_ID INTO E_DEPARTMENT_ID FROM DEPARTMENTS WHERE MANAGER_ID=D_DEPARTMENT_ID;
DBMS_OUTPUT.PUT_LINE('THIS IS THE DEPARTMENT ID' || ' ' || E_DEPARTMENT_ID);
END;



CREATE OR REPLACE PROCEDURE TOTAL
( N1 IN NUMBER,N2 IN NUMBER,S OUT NUMBER)
IS
BEGIN
S:= N1+N2;
END;

DECLARE
A NUMBER(5);
B NUMBER(5);
S NUMBER(7);
BEGIN
A:= :ENTER_NUMBER_A;
B:= :ENTER_NUMBER_B;
TOTAL(A,B,S);
DBMS_OUTPUT.PUT_LINE('SUM IS' || S);
END;

CREATE OR REPLACE PROCEDURE INCR
(SAL IN OUT NUMBER)
IS
BEGIN
SAL:= SAL+2000;
END;

DECLARE
SALARY NUMBER(5);
BEGIN
SALARY:= :ENTER_SALARY;
INCR(SALARY);
DBMS_OUTPUT.PUT_LINE('THE UPDATED SALARY IS' || SALARY);
END;




CREATE OR REPLACE FUNCTION FUN1(F NUMBER)
RETURN NUMBER
IS
FAC NUMBER(7);
BEGIN
FAC:=1;
FOR I IN 1..F
LOOP
FAC:=FAC*I;
END LOOP;
END FUN1;


DECLARE
F NUMBER(6);
FA NUMBER(5);
BEGIN
N:= :ENTER_NO;
FA:= FUN(F);
DBMS_OUTPUT.PUT_LINE(FA);
END;

select * from departments;

create or replace function employer_details_func
return varchar2(20);
is
emp_name varchar2(20);
begin
select department_name into emp_name
from departments where department_id=10;
return emp_name;
end;
/



SELECT employer_details_func FROM dual;


select * from departments;
create or replace function q_kob
(p_job_id in department_id departments.department_id%type)
return varchar2
is
d_department_name departments%type;
begin
select department_id into p_job_id from departments
where department_name=d_department_name;
return (d_department_name);
end q_kob;

SELECT * FROM JOBS;

CREATE OR REPLACE FUNCTION Q_JOB
(P_JOB_ID IN JOBS.JOB_ID%TYPE)
RETURN VARCHAR2
IS
V_JOBTITLE JOBS.JOB_TITLE%TYPE;
BEGIN
SELECT JOB_TITLE INTO V_JOBTITLE FROM JOBS
WHERE JOB_ID=P_JOB_ID;
RETURN (V_JOBTITLE);
END Q_JOB;
/


VARIABLE G_TITLE VARCHAR2(30)
EXECUTE :G_TITLE :=q_kob('AD_VP')
PRINT G_TITLE

declare
g_title varchar2(30);
g_print varchar2(30);
begin
g_title:= :Enter;
g_print:=q_kob(g_title);
end;
/


create or replace function myfun return varchar2 is
begin
return 'helo bhai';
end;
/

select myfun from dual;
describe dual;

execute myfun;
select myfun('hello world') from dual;


CREATE OR REPLACE FUNCTION MYFUN2(VAR VARCHAR2) RETURN VARCHAR2 AS
BEGIN
RETURN VAR;
END;
/

SELECT * FROM DUAL;

SELECT MYFUN2('KABLU MANDAL') FROM DUAL;

CREATE OR REPLACE FUNCTION ADD1(N1 NUMBER,N2 NUMBER,S NUMBER) RETURN NUMBER AS
BEGIN
RETURN (S=N1+N2);
END;
/

SELECT losal, hisal INTO min_sal, max_sal
FROM sals
WHERE job = title;


CREATE TABLE SCHOOL
(
NAMES VARCHAR2(22),
CLASSES NUMBER(2)
)

INSERT INTO SCHOOL
VALUES(:NAMES,:CLASSES)


SELECT * FROM SCHOOL


CREATE TRIGGER SCHOOL_TRIGGER11
BEFORE INSERT ON SCHOOL
BEGIN
IF(CLASSES>8) THEN
DBMS_OUTPUT.PUT_LINE('ENTRY IS GREATER THAN 8');
ELSE
DBMS_OUTPUT.PUT_LINE('ENTRY IS LESS THAN 8');
END IF;
END;

create table bank
(
id number(5),
payment number(5)
);


insert into bank
values(:id,:payment);
create or replace
trigger tr_bank
before update
on bank
for each row
begin
dbms_output.put_line('before updating ' || (:old.name) || ’ to ’ || to_char(:new.name));
end;

update bank
set payment=payment+2000 where id=1;

select * from coll ;
SELECT * FROM USER_TRIGGERS;
Create or replace

Trigger tr5

Before

Insert

On t12

For each row

Begin

Dbms_output.put_line(’before insert of ’ || :new.name);

End;


insert into coll
values(:id,:name);
/

values (1,'sam');

SELECT * FROM T1;

select * from user_triggers;







CREATE OR REPLACE PROCEDURE ADDITION(N1 IN NUMBER,N2 IN NUMBER,S OUT NUMBER)
IS
BEGIN
S:=N1+N2;
END;

SELECT * FROM USER_PROCEDURES;

DECLARE
A NUMBER(3);
B NUMBER(3);
ADDS NUMBER(3);
BEGIN
A:= :ENTER_NUMBER_1;
B:= :ENTER_NUMBER_2;
ADDITION(A,B,ADDS);
DBMS_OUTPUT.PUT_LINE('THE SUM OF TWO NUMBER IS=' || ADDS);
END;

CREATE TABLE EMP AS SELECT * FROM EMPLOYEES;

SELECT * FROM EMP;

CREATE OR REPLACE PROCEDURE EMP_DELETE(EMP_NO IN NUMBER)
IS
BEGIN
DELETE FROM EMP WHERE EMPLOYEE_ID=EMP_NO;
END;

DECLARE
E_NO NUMBER;
BEGIN
E_NO:= :ENTER_EMP_ID_THAT_IS_TO_DELETE;
EMP_DELETE(E_NO);
DBMS_OUTPUT.PUT_LINE('EMPLOYEE_ID' || ' ' || E_NO || ' ' || 'DELETED');
END;

CREATE OR REPLACE FUNCTION FUN2(N NUMBER)
RETURN NUMBER(5)
IS
FAC NUMBER(5);
BEGIN
FAC:=1;
FOR I IN 1..N
LOOP
FAC:=FAC*I;
END LOOP;
END FUN2;

SELECT * FROM USER_FUNCTION;

DECLARE
F NUMBER(6);
FA NUMBER(6);
BEGIN
F:= :ENTER_ANY_NUMBER;
FA:=FUN2(F);
DBMS_OUTPUT.PUT_LINE(FA);
END;



SHOW ERRORS;

SELECT * FROM EMP;
SELECT * FROM EMPLOYEES;

SELECT E1.FIRST_NAME,E1.EMPLOYEE_ID FROM EMP E1,EMPLOYEES E2
WHERE E1.EMPLOYEE_ID!=E2.EMPLOYEE_ID;

set serveroutput on
declare
msal emp.salary%type;
mnno number:=102;
begin
select salary INTO msal from emp where employee_id=mnno;
if(msal>2000) then
update emp set salary+500 where employee_id=mnno;

dbms_output.put_line('---------------------------');
dbms_output.put_line('emp no :' || mnno);
dbms_output.put_line('salary : ' || msal);
dbms_output.put_line('----------------------------');
end if;
end;

CREATE TABLE EMP1 AS SELECT * FROM EMP ;

SELECT FIRST_NAME FROM EMP E MPLOYEE_ID IN(SELECT EMPLOYEE_ID FROM EMPLOYEES);


CREATE PROCEDURE LATEST_REC(E_NOS IN NUMBER,FNAME IN VARCHAR2,LNAME IN VARCHAR2,ESAL IN NUMBER)
IS
BEGIN
UPDATE NEWPERSON
SET E_ENO=E_NOS,F_NAME=FNAME,L_NAME=LNAME,E_SAL=ESAL WHERE E_ENO=E_NOS;
DBMS_OUTPUT.PUT_LINE('LATEST RECORD ADDED');
END;

SELECT * FROM NEWPERSON;


DESCRIBE NEWPERSON;
SELECT * FROM NEWPERSON;
CREATE OR REPLACE PROCEDURE INSERT_PROC1(ENO IN NUMBER,FNAME VARCHAR2,LNAME VARCHAR2,ESAL NUMBER)
IS
BEGIN
INSERT INTO NEWPERSON
VALUES(ENO,FNAME,LNAME,ESAL);
COMMIT;
DBMS_OUTPUT.PUT_LINE('VALUE IS INSERTED');
END;

SELECT * FROM USER_PROCEDURES;
DECLARE
IDS HR.NEWPERSON.E_ENO%TYPE;
F_NAMES HR.NEWPERSON.F_NAME%TYPE;
L_NAMES HR.NEWPERSON.L_NAME%TYPE;
E_SALS HR.NEWPERSON.E_SAL%TYPE;
BEGIN
IDS := :EMP_ID;
F_NAMES := :FIRST_NAME;
L_NAMES := :LAST_NAME;
E_SALS := :SALARY;
INSERT_PROC1(IDS, F_NAMES, L_NAMES, E_SALS);
DBMS_OUTPUT.PUT_LINE('INSRTED');
END;


SELECT * FROM NEWPERSON


CREATE OR REPLACE FUNCTION PAY_FUN(PAY_TYPE CHAR)
RETURN VARCHAR2 IS
BEGIN
IF(PAY_TYPE= 'H') THEN
RETURN 'HOURLY';
ELSIF(PAY_TYPE='S') THEN
RETURN 'SALARIED';
ELSIF(PAY_TYPE='E') THEN
RETURN 'EARNED';
ELSE
RETURN 'NOT IN WORK GROUP';
END IF;
END PAY_FUN;

BEGIN
DBMS_OUTPUT.PUT_LINE('EMPLOYEE HAVING' || ' ' || PAY_FUN('H'));
END;



CREATE OR REPLACE FUNCTION CALCULATOR(OPTIONS CHAR ,N1 NUMBER,N2 NUMBER)
RETURN NUMBER IS
BEGIN
IF(OPTIONS='A') THEN
RETURN (N1+N2);
ELSE
RETURN N1;
END IF;
END;

DECLARE
RESULTS NUMBER(2);
BEGIN
RESULTS:=CALCULATOR('A',12,12);
DBMS_OUTPUT.PUT_LINE('RESULT IS=' || ' ' || RESULTS);
END;





SELECT * FROM EMPLOYEES;

DECLARE
REC NUMBER(5);
EMP_REC EMPLOYEES%ROWTYPE;
CURSOR EMP_CUR IS
SELECT * FROM EMPLOYEES;
BEGIN
OPEN EMP_CUR;
LOOP
FETCH EMP_CUR INTO EMP_REC;
EXIT WHEN EMP_CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(EMP_REC.FIRST_NAME);
END LOOP;
END;