pl/sql basic

DECLARE
names varchar2(12);
begin
names := 'kablu mandal';
DBMS_OUTPUT.PUT_LINE('the name is=' || names);
end;
/



DECLARE
char1 varchar2(1) := 'X';
char2 varchar2(1);
begin
char2 := '4';
DBMS_OUTPUT.PUT_LINE('CHARACTER=' || char1);
end;



declare
avg_days number(2) DEFAULT 21;
begin
DBMS_OUTPUT.PUT_LINE('DEFAULT DAYS' ||' ' || avg_days);
end;


declare

bonus_rate constant number := 0.05;
bonus number(8,2);
emp_id number(6) :=120;
begin
select salary*bonus_rate into bonus from employees
where employee_id=emp_id;
DBMS_OUTPUT.PUT_LINE('Employee:' || TO_CHAR(emp_id) || ' ' ||'Bonus Rate:' || TO_CHAR(bonus_rate) || 'Bonus:' || TO_CHAR(bonus));
end;




DECLARE
emp_id employees.employee_id%type;
HIR_DATE EMPLOYEES.HIRE_DATE%TYPE;
BEGIN
emp_id :=101301;
HIR_DATE :='17-JUN-87';
DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID:' || ' ' ||emp_id || ' ' ||'HIRING DATE' || HIR_DATE);
end;



DECLARE
EMP_REC EMPLOYEES%ROWTYPE;
BEGIN
SELECT * INTO EMP_REC FROM EMPLOYEES WHERE EMPLOYEE_ID=120;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME:' || EMP_REC.FIRST_NAME || ' ' || EMP_REC.LAST_NAME);
END;


DECLARE
NUM NUMBER(5);
BEGIN
NUM :=22;
IF NUM>5 THEN
DBMS_OUTPUT.PUT_LINE('NUMBER:' || TO_CHAR(NUM));
ELSIF NUM>10 THEN
DBMS_OUTPUT.PUT_LINE('NUMBER:' || TO_CHAR(NUM));
ELSE
DBMS_OUTPUT.PUT_LINE('NUMBER:' || TO_CHAR(NUM));
END IF;
END;
/




DECLARE
GRADE CHAR(1);
BEGIN
GRADE :='B';
CASE GRADE
WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('EXCELLANT');
WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('VERY GOOD');
WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('GOOD');
ELSE
DBMS_OUTPUT.PUT_LINE('such grade');
end case;
end;


BEGIN
-- use a FOR loop to process a series of numbers
FOR loop_counter IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE('Number: ' || TO_CHAR(loop_counter)
|| ' Square: ' || TO_CHAR(loop_counter**2));
END LOOP;
END;
/



BEGIN
FOR IDX IN 2..5 LOOP
DBMS_OUTPUT.PUT_LINE(IDX);

IF IDX=3 THEN
DBMS_OUTPUT.PUT_LINE('A LUCKY NUMBER='||IDX);
ELSE
DBMS_OUTPUT.PUT_LINE(IDX);
END IF;
END LOOP;
END;/



DECLARE
TOTAL NUMBER(9) :=0;
COUNTER NUMBER(6) :=0;
BEGIN
LOOP
COUNTER :=COUNTER+1;
TOTAL :=TOTAL+COUNTER*COUNTER;
EXIT WHEN TOTAL>25000;
END LOOP;
DBMS_OUTPUT.PUT_LINE('cOUNTER:' || TO_CHAR(COUNTER) || 'TOTAL:' || TO_CHAR(TOTAL));
END;
/



DECLARE
FNAME VARCHAR2(20) :='kablu';
LASTNAME VARCHAR2(25) :='mandal';
PROCEDURE UPPER_NAME(V1 IN OUT VARCHAR2,V2 IN OUT VARCHAR2) AS
BEGIN
V1:=UPPER(V1);
V2:=UPPER(V2);
END UPPER_NAME;
BEGIN
DBMS_OUTPUT.PUT_LINE(FNAME || ' ' ||LASTNAME);
UPPER_NAME(FNAME,LASTNAME);
DBMS_OUTPUT.PUT_LINE(FNAME ||' ' || LASTNAME);
END;



DECLARE
FNAME VARCHAR2(20) :='kablu';
LNAME VARCHAR2(20) :='mandal';
FUNCTION U_N(V1 IN VARCHAR2,V2 IN VARCHAR2)
RETURN VARCHAR2 AS
V3 VARCHAR2(45);
BEGIN
V3:=V1 || ' + ' || V2 || '=' || UPPER(V1) || ' ' || UPPER(V2);
RETURN V3;
END U_N;
BEGIN
DBMS_OUTPUT.PUT_LINE(U_N(FNAME,LNAME));
END;
/



DECLARE
NUM1 NUMBER :=12;
NUM2 NUMBER :=1;
FUNCTION ADD(N1 IN NUMBER,N2 IN NUMBER)
RETURN NUMBER AS
N3 NUMBER;
BEGIN
N3:=N1+N2;
RETURN N3;
END ADD;
BEGIN
DBMS_OUTPUT.PUT_LINE(ADD(NUM1,NUM2));
END;
/