oracle

case insensitive search
to retrieve records, search records case insensitive use upper function

select * from User

where upper(name) like 'JOHN%'

select current date/execute function
select sysdate
from dual;

executing a function
select func(x)
from dual;

select number of records in table
select count(*)
from table

executing a procedure which returns a reference cursor

DECLARE
TYPE CurTyp IS REF CURSOR;
cur CurTyp;
BEGIN
DBMS_OUTPUT.ENABLE(1000000); -- enable output
cur := schema.pkg.proc_name(input);
END;

executing a procedure which returns a number

DECLARE
x number;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
x := schema.pkg.proc_name(input);
END;

executing a procedure which returns a varchar
DECLARE
str varchar(3000);
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
str := schema.pkg.proc_name(input);
END;

printing separator

declare
str varchar(3000);
begin
dbms_output.put_line('------------------');
str := 'Hello';
dbms_output.put_line(str);
end;

string literal is enclosed in single quote
example 'hello'

assignment operator
:=

equality operator
=

not equal to operator
<>

line separator
;

For loop

DECLARE
ii number;
BEGIN
ii := 4;
FOR ii IN -2..20 LOOP
dbms_output.put_line(ii);
END LOOP;
END;

--fetching data from cursors to record

declare
type curtype is ref cursor;
cur curtyp;

type rectype is record
(
col1 number,
col2 varchar(30)
);
rec rectype;

begin
cur := proc(); --execute proc

loop
fetch cur into rec
exit when cur%notfound;

dbms_output.put_line(rec.col1 || ' ' || rec.col2);
end loop

close cur;
end;