Function VS Procedure:

Procedure:

procedure is a group of PL/SQL statements that can be called by name. The call specification (sometimes called call spec) specifies a java method or a third-generation language routine so that it can be called from SQL and PL/SQL.

The procedure contains a header and a body.

• Header: The header contains the name of the procedure and the parameters or variables passed to the procedure.
• Body: The body contains a declaration section, execution section and exception section similar to a general PL/SQL block.

How to pass parameters in procedure:

When you want to create a procedure or function, you have to define parameters .There is three ways to pass parameters in procedure:

• IN: It is a default parameter. It passes the value to the subprogram.
• OUT: It must be specified. It returns a value to the caller.
• IN OUT: It must be specified. It passes an initial value to the subprogram and returns an updated value to the caller.

Function:

The PL/SQL stored procedure or simply a procedure is a PL/SQL block which performs one or more specific tasks.

create or replace function UKACCOUNT.adder(n1 in number, n2 in number)    
return number    
is     
n3 number(8);    
begin    
n3 :=n1+n2;    
return n3;    
end;    
/ 


// cal function 


DECLARE    
   n3 number(2);    
BEGIN    
   n3 := UKACCOUNT.adder(11,22);    
   dbms_output.put_line('Addition is: ' || n3);    
END;    
/ 


// declare and call in one block --problem

DECLARE
   a number;  
   b number;  
   c number;  
create or replace FUNCTION ukaccount.findMax2(x IN number, y IN number)   
RETURN number  
IS  
    z number;  
BEGIN  
   IF x > y THEN  
      z:= x;  
   ELSE  
      Z:= y;  
   END IF;  
  
   RETURN z;  
END;   
BEGIN  
   a:= 23;  
   b:= 45;  
  
   c := ukaccount.findMax2(a, b);  
   dbms_output.put_line(' Maximum of (23,45): ' || c);  
END;  
/ 

CREATE OR REPLACE function DALERP.get_care_language_translation (p_care_label_id number,P_language_code varchar2) return NVARCHAR2 is 
		cursor all_fabrication is
		select a1.sl_no, a1.ENGLISH,a1.FABRIC_PERCNT
		from
		(
		select a.sl_no,A.LANGUAGE ENGLISH,to_char(a.FABRIC_PERCNT) FABRIC_PERCNT
		From CARE_LABEL_FABRICATION a
		where A.MASTER_ID=p_care_label_id
		and LANGUAGE is not null
		union all
		select 99 sl_no,english,'' FABRIC_PERCNT from CARE_LANGUAGES where id=(select decode(IS_PART_OF_SETS,460,460,null) from CARE_LABEL_MASTER where ID=P_Care_Label_Id)
		union all
		select 100 sl_no,english,'' FABRIC_PERCNT from CARE_LANGUAGES where id=(select decode(EXCLUSIVE_TRIM,418,418,null) from CARE_LABEL_MASTER where ID=P_Care_Label_Id)
		)a1
		order by sl_no asc;

		cursor all_wash is
		select sl_no,ENGLISH
		from
		(
		select a.sl_no,(A.INSTRUCTION) ENGLISH 
		From CARE_LABEL_WASH_INSTRUCTION a
		where A.MASTER_ID=p_care_label_id
		and A.INSTRUCTION is not null
		)
		order by sl_no asc;



		v_fabrication NVARCHAR2(1000);
		v_translation1 NVARCHAR2(1000);

		v_1st_time number;

		v_wash NVARCHAR2(1000);
		v_translation2 NVARCHAR2(1000);

		v_final_translation NVARCHAR2(1000);

		begin


		v_translation1:='';
		v_1st_time:=0;


		for j in all_fabrication loop

				
        BEGIN
		   
		select decode(j.FABRIC_PERCNT,null,upper(substr(max(nvl(TRANSLATIONS,'.....')),1,1)) || substr(max(nvl(TRANSLATIONS,'.....')),2,9999),j.FABRIC_PERCNT||'% ' || upper(substr(max(nvl(TRANSLATIONS,'.....')),1,1)) || substr(max(nvl(TRANSLATIONS,'.....')),2,9999))  
		into v_fabrication
		from TESCO_CARE_LANGUAGES_V a  where a.ENGLISH=j.english and a.language_code=P_language_code;
		  
		  EXCEPTION
				 WHEN OTHERS THEN
				    v_fabrication :='.....';
		END; 
		   
		   
		   if v_1st_time=0 then
			v_translation1:=v_fabrication;
			v_1st_time:=1;
			else
			v_translation1:=concat(concat(v_translation1,' / '),v_fabrication);
			end if;
					 
		end loop;

		for i in all_wash loop

		BEGIN
		
		   select nvl(TRANSLATIONS,'.....') into v_wash from TESCO_CARE_LANGUAGES_V a  where trim(upper(a.english))=trim(upper(i.english)) and language_code=P_language_code;
		   
		   EXCEPTION
				 WHEN OTHERS THEN
				    v_wash :='.....';
		END; 
		   
		   if v_1st_time=0 then
			v_translation2:=v_wash;
			v_1st_time:=1;
			else
			v_translation2:=concat(concat(v_translation2,' / '),v_wash);
			end if;
					 
		end loop;


		v_final_translation:=v_translation1|| v_translation2;

		return trim(v_final_translation);
end;
/