How to call a function in store procedure Oracle PL/SQL

create or replace function final_sal(v_empno number)
return number
is
v_newsal number;
v_sal number;
v_comm number;
begin
select sal,comm into v_sal,v_comm
    from emp
      where empno=v_empno;
if v_comm is null then
v_newsal :=nvl(v_comm,2000);
V_newsal := (v_newsal+v_sal)*12;
return v_newsal;
else
v_newsal := (v_sal+v_comm)*12;
return v_newsal;
end if;
end final_sal;


create or replace procedure emp_details(v_empno number)
 is
 v_ename varchar2(20);
 v_job varchar2(20);
 v_mgr varchar2(20);
 v_sal number;
 begin
 select ename,job,mgr into v_ename,v_job,v_mgr
      from emp
        where empno=v_empno;
 v_sal := final_sal(v_empno);  -- here i call that function 
dbms_output.put_line('Emp Name:' || v_ename);
dbms_output.put_line('Emp Job :' || v_job);
dbms_output.put_line('Emp Mgr:' || v_mgr);
dbms_output.put_line('Emp Total Sal:' || v_sal);
end emp_details;

Read More

Oracle PL/SQL Procedure for user login check

create or replace procedure login_check(v_username varchar2,v_password varchar2)
 as
uname varchar2(20);
pass varchar2(20);
ctime varchar2(40);
ltime timestamp :=systimestamp;
begin
  select to_char(systimestamp,'hh24')  into ctime from dual;
  select username,password into uname,pass from LOGIN where LOWER(username)=LOWER(v_username) and LOWER(password)=LOWER(v_password);
if (lower(uname)=lower(v_username) and lower(pass)=lower(v_password))
  then
if ctime between 1 and 12 then
 dbms_output.put_line(' Good Morning ' ||  v_username ||' : '|| to_char(systimestamp,'hh:mi'));
elsif ctime between '12' and '17' then
 dbms_output.put_line(' Good AfterNoon '|| v_username ||' : '|| to_char(systimestamp,'hh:mi'));
else
dbms_output.put_line(' Good evening '|| v_username ||' : '|| to_char(systimestamp,'hh:mi'));
end if;
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
 dbms_output.put_line('invalid username/password');
end login_check;
/

Read More

Call A Sequence To Generate Unique Id With In A Procedure

First Create A Sequence:-

CREATE SEQUENCE seq
    INCREMENT BY 10
    START WITH 1
    MAXVALUE 10000
    NOCACHE
    NOCYCLE;

Now Create A procedure And Use This Sequence:-

create or replace procedure ST_INSERT(
                                       ST_NAME VARCHAR,
                                       ST_BRANCH VARCHAR )
 as
begin
insert into STUD (STUDENT_ID,NAME,BRANCH)
          values ('CDS'||SEQ.NEXTVAL,ST_NAME,ST_BRANCH );
     end ST_INSERT;
     /

Read More