Example of some Functions

CREATE OR REPLACE FUNCTION get
    RETURN NUMBER IS
    query_str VARCHAR2(1000);
    num_of_employees NUMBER;
BEGIN
    query_str := 'SELECT COUNT(*) FROM '
        || ' employees';
    EXECUTE IMMEDIATE query_str
        INTO num_of_employees;
    RETURN num_of_employees;
END;


CREATE OR REPLACE FUNCTION get
    RETURN varchar2 IS
    query_str VARCHAR2(1000);
    num_of_employees varchar2(50) ;
BEGIN
    query_str := 'SELECT LAST_NAME FROM
        employees where employee_id=101';
    EXECUTE IMMEDIATE query_str
        INTO num_of_employees;
    RETURN num_of_employees;
END;



CREATE OR REPLACE procedure get
 IS
    query_str VARCHAR2(1000);
    num_of_employees varchar2(50) ;
BEGIN
    query_str := 'SELECT LAST_NAME FROM
        employees where employee_id=101';
    EXECUTE IMMEDIATE query_str
        INTO num_of_employees;
dbms_output.put_line(num_of_employees);
END;



create or replace function my_sign(num number) return number is
v_1 number;
begin
if num<0 then
v_1:=-1;
elsif num=0 then
v_1:=0;
elsif num>0 then
v_1:=1;
end if;
return v_1;
end;



Find the value 503,2142,2102 from  (15X4=503/16X4=2142/17X4=2102) Remark


create or replace function remark_value(dtl_no varchar2) return varchar2 as
v_1 number:=0;
v_2 number:=0;
v_3 number:=0;
v_4 number:=0;
begin
select REGEXP_COUNT(remarks, '=') into v_4 from do_finlize_v WHERE do_dtl_no=dtl_no;
for i in 1..v_4-1 loop
select substr(remarks,instr(remarks,'=',1,i)+1,
instr(substr(remarks,instr(remarks,'=',1,i)+1),'/',2)-1) into v_1 from do_finlize_v WHERE do_dtl_no=dtl_no;
v_2:=v_2+v_1;
end loop;
select substr(remarks,instr(remarks,'=',1,v_4)+1) into v_3 from do_finlize_v WHERE do_dtl_no=dtl_no;
return (v_2+v_3);
end;