PL/SQL Code Example

By ukmodak | June 6th 2022 12:23:03 PM | viewed 189 times

How to run PL/SQL

1 Install database

2 go to oracle home directory and create a file with .sql extension and write pl/sql code on it

3 cmd>sqlplus

4. enter sys as sysdba and enter sys password

3 cmd>alter session set container=apexukpdb;

grant connect to ukaccount;
grant all privileges to ukaccount;

sql>@directory\filename.sql enter (best) // or copy and pase code on it and enter(may be face problem)

pl/sql basic syntax


set serveroutput on size 30000;   // declare once

DECLARE
message varchar2(20):='Hi Uzzal';
BEGIN
dbms_output.put_line(message);
END;
/

create table using pl/sql


DECLARE
    ddl_qry     VARCHAR2 (150);
BEGIN
    ddl_qry := 'CREATE TABLE apex.uktable(
                id     NUMBER(3),
                tut_name    VARCHAR2(50),
				primary key(id)
                )';
    EXECUTE IMMEDIATE ddl_qry;
END;
/

user defined data type in pl/sql


DECLARE
SUBTYPE name IS CHAR(20);
SUBTYPE message IS varchar2(100);
sultan name;
ukmessage message;
BEGIN
   sultan:='Reader';
   ukmessage:='Welcome to plsql';
   dbms_output.put_line('Hello' ||' '|| sultan || ukmessage);
END;
/

variable initialize in pl/sql



DECLARE
a integer:=10;
b integer:=20;
c integer;
d real;
BEGIN
    c:=(a+b);
	d:=(c *.5);
	dbms_output.put_line('The value of c: ' || c);  
	dbms_output.put_line('The value of d: ' || d);
	/* must write with single quatation */
END;
/

nested if-then-else in pl/sql


DECLARE 
a number(3) := 100; 
b number(3) := 200; 
BEGIN 
	-- check the boolean condition  
	IF( a = 100 ) THEN 
	-- if condition is true then check the following  
		IF( b = 200 ) THEN 
		-- if condition is true then print the following  
		dbms_output.put_line('Value of a is 100 and b is 200' ); 
		END IF; 
	END IF; 
	dbms_output.put_line('Exact value of a is : ' || a ); 
	dbms_output.put_line('Exact value of b is : ' || b ); 
END; 
/ 

labeling in loop in pl/sql


DECLARE 
		i number(1); 
		j number(1); 
		BEGIN 
			<< outer_loop >> 
			FOR i IN 1..3 LOOP 
				<< inner_loop >> 
				FOR j IN 1..3 LOOP 
				dbms_output.put_line('i is: '|| i || ' and j is: ' || j); 
				END loop inner_loop; 
			END loop outer_loop; 
END; 
/


array in pl/sql


DECLARE 
   type namesarray IS VARRAY(5) OF VARCHAR2(10); 
   type grades IS VARRAY(5) OF INTEGER; 
   names namesarray; 
   marks grades; 
   total integer; 
BEGIN 
   names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz'); 
   marks:= grades(98, 97, 78, 87, 92); 
   total := names.count; 
   dbms_output.put_line('Total '|| total || ' Students'); 
   FOR i in 1 .. total LOOP 
      dbms_output.put_line('Student: ' || names(i) || ' 
      Marks: ' || marks(i)); 
   END LOOP; 
END; 
/

exception handing in pl/sql


DECLARE 
   c_id ukaccount.ukcourse.id%type := &cc_id; 
   c_name ukaccount.ukcourse.Name%type; 
   ex_invalid_id  EXCEPTION; 
BEGIN 
   IF c_id <= 0 THEN 
      RAISE ex_invalid_id; 
   ELSE 
      SELECT  name INTO  c_name
      FROM ukaccount.ukcourse 
      WHERE id = c_id;
      DBMS_OUTPUT.PUT_LINE ('Name: '||  c_name);  
   END IF; 

EXCEPTION 
   WHEN ex_invalid_id THEN 
      dbms_output.put_line('ID must be greater than zero!'); 
   WHEN no_data_found THEN 
      dbms_output.put_line('No such customer!'); 
   WHEN others THEN 
      dbms_output.put_line('Error!');  
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;
/

implicit cursor in pl/sql

DECLARE  
   total_rows number(2); 
BEGIN 
   UPDATE ukaccount.ukcourse 
   SET name ='bangladesh' WHERE ID =1; 
   IF sql%notfound THEN 
      dbms_output.put_line('no ukcourse selected'); 
   ELSIF sql%found THEN 
      total_rows := sql%rowcount;
      dbms_output.put_line( total_rows || ' ukcourse selected '); 
   END IF;
    COMMIT;   
END; 
/ 

explicit cursor in pl/sql


DECLARE
   CURSOR cur_ukcourse is 
      SELECT id, name FROM ukaccount.ukcourse; 
	  
   c_id ukaccount.ukcourse.id%type; 
   c_name ukaccount.ukcourse.name%type; 
BEGIN 
   OPEN cur_ukcourse; 
   LOOP 
   FETCH cur_ukcourse into c_id, c_name; 
      EXIT WHEN cur_ukcourse%notfound; 
      dbms_output.put_line(c_id || ' ' || c_name); 
   END LOOP; 
   CLOSE cur_ukcourse; 
END; 
/

table base record in pl/sql


DECLARE 
   course_table_recrord ukaccount.ukcourse%rowtype; 
BEGIN 
   SELECT * into course_table_recrord 
   FROM ukaccount.ukcourse 
   WHERE id = 2;  
   dbms_output.put_line('Course ID: ' || course_table_recrord.id); 
   dbms_output.put_line('Course Name: ' || course_table_recrord.name); 
END; 
/

cursor base table record in pl/sql


DECLARE 
   CURSOR course_cur is 
      SELECT id, name 
      FROM ukaccount.ukcourse;
   course_record course_cur%rowtype;	     
BEGIN 
   OPEN course_cur; 
   LOOP 
      FETCH course_cur into course_record; 
      EXIT WHEN course_cur%notfound; 
      DBMS_OUTPUT.put_line(course_record.id || ' ' || course_record.name); 
   END LOOP; 
END; 
/

user defined record in pl/sql


DECLARE 
   type ukcourse is record 
      (
	  id number(3), 
      name varchar(50)
	  ); 
   ukcourse1 ukcourse; 
   ukcourse2 ukcourse; 
BEGIN 
   -- ukcourse1 specification 
   
   ukcourse1.id  := 3; 
   ukcourse1.name := 'India'; 

 -- ukcourse2 specification 
 
   ukcourse2.id  := 4; 
   ukcourse2.name := 'Pakistan';   
   
  
  -- Print ukcourse1  record 
  
   dbms_output.put_line('id : ' || ukcourse1.id); 
   dbms_output.put_line('name: '|| ukcourse1.name); 

   
  -- Print ukcourse2 2 record 
  
   dbms_output.put_line('id : ' || ukcourse2.id); 
   dbms_output.put_line('name: '|| ukcourse2.name); 
 
END; 
/

declare and call function in pl/sql


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;    
/ 

// call function UKACCOUNT.adder  -- done


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

declare and call function in one block in pl/sql

// 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;
/

create procedure to create table in pl/sql



create or replace procedure UKACCOUNT.ddlUktable is
    ddl_qry VARCHAR2 (150);
begin
   ddl_qry := 'CREATE TABLE UKACCOUNT.uktable(
                id     NUMBER(3),
                tut_name    VARCHAR2(50),
				primary key(id)
                )';
    EXECUTE IMMEDIATE ddl_qry;
end ddlUktable;
/


// call procedure apex.ddlUktable   // done;

BEGIN
UKACCOUNT.ddlUktable;
END;
/

or
EXEC apex.ddlUktable;

create procedure to update another table data using cursor in pl/sql


set serveroutput on size 30000;
CREATE OR REPLACE PROCEDURE apex.updatetable IS
 error_number number;
 cursor ci IS
 SELECT * FROM uktable;  
 all_data ci%ROWTYPE; 
BEGIN
      OPEN ci;
		  LOOP
		  fetch ci into all_data;
			  IF ci%NOTFOUND THEN
			  error_number :=999;
			  END IF;
			  /* or
				 EXIT WHEN ci%NOTFOUND;
			  */
		  INSERT INTO UKCOURSE(NAME) VALUES(all_data.course_name);
		  END LOOP;
	  
	  CLOSE ci;
	  
    COMMIT;
	  
EXCEPTION
  WHEN OTHERS THEN
    raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);	  
END;
/

create procedure to update another table data using loop in pl/sql

set serveroutput on size 30000;

CREATE OR REPLACE PROCEDURE UKACCOUNT.updatetable IS
 error_number number;  
BEGIN

	  for r IN (SELECT * FROM UKACCOUNT.uktable)
	    LOOP 
		   INSERT INTO UKACCOUNT.UKCOURSE(ID,NAME) VALUES(r.id,r.name);
		END LOOP;
    COMMIT;
	  
EXCEPTION
  WHEN OTHERS THEN
    raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);	  
END;
/

// call procedure UKACCOUNT.updatetable   // done;

BEGIN
UKACCOUNT.updatetable;
END;
/

trigger in pl/sql


CREATE OR REPLACE TRIGGER display_course_id 
BEFORE DELETE OR INSERT OR UPDATE ON ukaccount.ukcourse 
FOR EACH ROW 
WHEN (NEW.ID > 0) 
DECLARE 
   id_diff number; 
BEGIN 
   id_diff := :NEW.id  - :OLD.id; 
   dbms_output.put_line('Old id: ' || :OLD.id); 
   dbms_output.put_line('New id: ' || :NEW.id); 
   dbms_output.put_line('ID difference: ' || id_diff); 
END; 
/ 

//note: where inseret id in the table then auto call trigger

package in pl/sql


// package specification   -- done by grant connect to ukaccount

CREATE OR REPLACE PACKAGE ukaccount.uk_course AS 
    PROCEDURE addUkcourse(c_id ukaccount.ukcourse.id%type,c_name ukaccount.ukcourse.name%type);  
	PROCEDURE delUkcourse(c_id ukaccount.ukcourse.id%type);
	PROCEDURE listUkcourse;   
END uk_course; 
/

// package body   -- done by grant connect to ukaccount


CREATE OR REPLACE PACKAGE BODY ukaccount.uk_course AS 
   PROCEDURE addUkcourse(
      c_id  ukaccount.ukcourse.id%type, 
      c_name ukaccount.ukcourse.Name%type
	  ) 
   IS 
   BEGIN 
      INSERT INTO ukaccount.ukcourse (id,name) 
         VALUES(c_id, c_name); 
   END addUkcourse; 
   
   PROCEDURE delUkcourse(c_id ukaccount.ukcourse.id%type) IS 
   BEGIN 
      DELETE FROM ukaccount.ukcourse 
      WHERE id = c_id; 
   END delUkcourse;  
   
   PROCEDURE listUkcourse IS 
	   CURSOR course_cur is 
		  SELECT id, name 
		  FROM ukaccount.ukcourse;
	   course_record course_cur%rowtype;	     
		BEGIN 
		   OPEN course_cur; 
		   LOOP 
			  FETCH course_cur into course_record; 
			  EXIT WHEN course_cur%notfound; 
			  DBMS_OUTPUT.put_line(course_record.id || ' ' || course_record.name); 
		   END LOOP; 
		END listUkcourse; 
   
END uk_course; 
/

// calling package   


set serveroutput on size 30000; 
BEGIN 
   ukaccount.uk_course.addUkcourse(5, 'Potitish'); 
   ukaccount.uk_course.addUkcourse(6, 'krishna'); 
   commit;
   ukaccount.uk_course.listUkcourse;
END; 
/


// note: if any code of any block occuur error then package will stop

view in pl/sql

CREATE OR REPLACE FORCE VIEW DALERP.VW_DELIVERY_ITEM
(
   ITEM_PID,
   ITEM_CODE,
   ITEM_NAME,
   ITEM_GROUP_PID,
   GROUP_CODE,
   GROUP_NAME,
   MU_PID,
   NET_WEIGHT,
   MARKET_UNIT_PID,
   SHORT_NAME,
   UNIT_PID,
   SKU,
   CTN_UNIT_PID,
   CTN,
   CONVERSION_VALUE,
   RATE,
   SKU_RATE,
   ISSUE_QTY,
   ISSUE_QTY_CTN,
   ISSUE_VALUE,
   ISSUE_PID,
   ISSUE_NO,
   ISSUE_DATE,
   ISSUE_FROM,
   ISSUE_FROM_NAME,
   ISSUE_TO,
   ISSUE_TO_NAME,
   STORE_TYPE_PID,
   TYPE_NAME,
   DELIVERED_QTY,
   DELIVERY_QTY_CTN,
   DELIVERED_VALUE,
   DELIVERY_PID,
   USER_DELIVERY_NO,
   DELIVERY_DATE,
   DELIVERY_FROM,
   DELIVERY_FROM_NAME,
   DELIVERY_TO,
   DELIVERY_TO_NAME,
   ADDRESS,
   LOCATION,
   CONTACT_NO,
   APPROVE_BY,
   APPROVE_DT,
   DELIVERY_ENT_DATE,
   RECOMMEND_BY,
   RECOMMEND_DT,
   TRANS_TYPE,
   USER_ID,
   DELIVERY_ENT_BY,
   DELIVERY_ENT_BY_NAME,
   CUM_DEL_QTY,
   PENDING_QTY,
   PENDING_QTY_CTN,
   PENDING_VALUE,
   STATUS,
   STATUS_DESC
)
AS
   SELECT iss.item_pid,
          iss.item_code,
          iss.item_name,
          iss.item_group_pid,
          ig.group_code,
          ig.group_name,
          iss.mu_pid,
          iss.net_weight,
          iss.market_unit_pid,
          u.short_name,
          iss.unit_pid,
          u1.short_name sku,
          iss.ctn_unit_pid,
          u2.short_name ctn,
          iss.conversion_value,
          iss.rate,
          iss.sku_rate,
          iss.issue_qty,
          iss.issue_qty_ctn,
          iss.issue_value,
          iss.issue_pid,
          iss.issue_no,
          iss.issue_date,
          iss.issue_from,
          su1.store_unit_name issue_from_name,
          iss.issue_to,
          su.store_unit_name issue_to_name,
          iss.store_type_pid,
          st.type_name,
          dli.delivery_qty delivered_qty,
          dli.delivery_qty_ctn,
          dli.delivered_value,
          dli.delivery_pid,
          dli.user_delivery_no,
          dli.delivery_date,
          dli.delivery_from,
          su3.store_unit_name delivery_from_name,
          dli.delivery_to,
          su2.store_unit_name delivery_to_name,
          su2.address,
          su2.location,
          su2.contact_no,
          dli.approve_by,
          dli.approve_dt,
          dli.ent_date delivery_ent_date,
          dli.recommend_by,
          dli.recommend_dt,
          dli.trans_type,
          dli.user_id,
          dli.user_id delivery_ent_by,
          dut.user_name delivery_ent_by_name,
          dli.cum_del_qty,
          iss.issue_qty - NVL (dli.cum_del_qty, 0) pending_qty,
            iss.issue_qty_ctn
          - DECODE (iss.market_unit_pid,
                    dli.market_unit_pid, NVL (dli.cum_del_qty, 0),
                    NVL (dli.cum_del_qty, 0) / iss.conversion_value)
             pending_qty_ctn,
          (iss.issue_qty - NVL (dli.cum_del_qty, 0)) * dli.rt pending_value,
          iss.status,
          sas.status_desc
     FROM (SELECT si.item_pid,
                  i.item_group_pid,
                  i.item_code,
                  i.item_name,
                  si.mu_pid,
                  si.net_weight,
                  si.market_unit_pid,
                  si.conversion_value,
                  i.unit_pid,
                  i.market_unit_pid ctn_unit_pid,
                  si.rate,
                  si.sku_rate,
                  si.issue_qty,
                  DECODE (si.market_unit_pid,
                          i.market_unit_pid, si.issue_qty,
                          si.issue_qty / si.conversion_value)
                     issue_qty_ctn,
                    si.issue_qty
                  * DECODE (si.market_unit_pid,
                            i.market_unit_pid, si.rate,
                            si.sku_rate)
                     issue_value,
                  si.issue_pid,
                  sm.user_issue_no issue_no,
                  sm.issue_date,
                  sm.issue_from,
                  sm.issue_to,
                  sm.store_type_pid,
                  sm.status
             FROM store_issuechd si, store_issuemst sm, item i
            WHERE si.issue_pid = sm.pid AND si.item_pid = i.pid) iss,
          (SELECT sdgg.pid,
                  sdgg.issue_pid,
                  sdgg.item_pid,
                  sdgg.rate,
                  sdgg.sku_rate,
                  sdgg.delivery_qty,
                  DECODE (sdgg.market_unit_pid,
                          i.market_unit_pid, sdgg.delivery_qty,
                          sdgg.delivery_qty / sdgg.conversion_value)
                     delivery_qty_ctn,
                    sdgg.delivery_qty
                  * DECODE (sdgg.market_unit_pid,
                            i.market_unit_pid, sdgg.rate,
                            sdgg.sku_rate)
                     delivered_value,
                  sdgg.delivery_pid,
                  sd.user_delivery_no,
                  sd.delivery_date,
                  sd.delivery_from,
                  sd.delivery_to,
                  sd.approve_by,
                  sd.approve_dt,
                  sd.ent_date,
                  sd.recommend_by,
                  sd.recommend_dt,
                  sd.trans_type,
                  sd.user_id,
                  (SUM (
                      sdgg.delivery_qty)
                   OVER (
                      PARTITION BY sdgg.issue_pid, sdgg.item_pid, sdgg.rate
                      ORDER BY sd.delivery_date, sd.user_delivery_no))
                     cum_del_qty,
                  DECODE (sdgg.market_unit_pid,
                          i.market_unit_pid, sdgg.rate,
                          sdgg.sku_rate)
                     rt,
                  i.market_unit_pid
             FROM store_deliverygrandgrandchd sdgg,
                  store_deliverymst sd,
                  item i
            WHERE sdgg.delivery_pid = sd.pid AND sdgg.item_pid = i.pid) dli,
          item_group ig,
          unit u,
          unit u1,
          unit u2,
          store_unit su,
          store_unit su1,
          store_unit su2,
          store_unit su3,
          store_type st,
          user_tbl dut,
          store_action_status sas
    WHERE     iss.issue_pid = dli.issue_pid(+)
          AND iss.item_pid = dli.item_pid(+)
          AND iss.rate = dli.rate(+)
          AND iss.item_group_pid = ig.pid
          AND iss.store_type_pid = st.pid
          AND dli.user_id = dut.user_id(+)
          AND iss.market_unit_pid = u.pid
          AND iss.unit_pid = u1.pid
          AND iss.ctn_unit_pid = u2.pid
          AND iss.issue_to = su.pid
          AND iss.issue_from = su1.pid
          AND dli.delivery_to = su2.pid(+)
          AND dli.delivery_from = su3.pid(+)
          AND iss.status = sas.id(+);


CREATE OR REPLACE FORCE VIEW DALPDFPRINT.ALL_PO_PDF_V (PO_NO)
AS
  SELECT DISTINCT PO_NO
   from(
           SELECT distinct a.PO_NO
           FROM DALPDFPRINT.FULL_PO a 
              WHERE SUBSTR(TRIM(A.PO_NO),0,3) ='460'
              OR SUBSTR(TRIM(A.PO_NO),0,3) ='461'
      UNION ALL
           SELECT distinct a.PO_NO
           FROM DALPDFPRINT.FULL_PO a,DALPDFPRINT.REST_PO B,DALPDFPRINT.PACK P,DALPDFPRINT.FULL_PO_PACKS PP
              WHERE A.ID = PP.FULLPO_ID
              AND P.ID = PP.PACKS_ID
              AND SUBSTR(TRIM(A.PO_NO),0,3) !='460'
              AND SUBSTR(TRIM(A.PO_NO),0,3) !='461'
              AND SUBSTR(TRIM(A.PO_NO),-3) = SUBSTR(TRIM(B.RETEK_ORDER_NO),-3)
      UNION ALL
          SELECT distinct a.PO_NO PO_NO
          FROM DALPDFPRINT.PARTIAL_PO A,DALPDFPRINT.REST_PO B,DALPDFPRINT.PACK P,DALPDFPRINT.PARTIAL_PO_PACKS PP
              WHERE A.ID = PP.PARTIALPO_ID
              AND P.ID = PP.PACKS_ID
             AND SUBSTR(TRIM(A.PO_NO),-3) = SUBSTR(TRIM(B.RETEK_ORDER_NO),-3)
     ) 
   order by PO_NO asc;
   
  

To call a view run the following code

select PO_NO from ALL_PO_PDF_V where a.PO_NO ='990-01674';

varray in pl/sql

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  TYPE table_type IS VARRAY(5) OF NUMBER(10);
  v_tab  table_type;
  v_idx  NUMBER;
BEGIN
  -- Initialise the collection with two values.
  v_tab := table_type(1, 2);

  -- Extend the collection with extra values.
  << load_loop >>
  FOR i IN 3 .. 5 LOOP
    v_tab.extend;
    v_tab(v_tab.last) := i;
  END LOOP load_loop;
  
  -- Can't delete from a VARRAY.
  -- v_tab.DELETE(3);

  -- Traverse collection
  v_idx := v_tab.FIRST;
  << display_loop >>
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
    v_idx := v_tab.NEXT(v_idx);
  END LOOP display_loop;
END;
/

index by table or associative array Collection in pl/sql


SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  TYPE table_type IS TABLE OF NUMBER(10)
    INDEX BY BINARY_INTEGER;
  
  v_tab  table_type;
  v_idx  NUMBER;
BEGIN
  -- Initialise the collection.
  << load_loop >>
  FOR i IN 1 .. 5 LOOP
    v_tab(i) := i;
  END LOOP load_loop;
  
  -- Delete the third item of the collection.
  v_tab.DELETE(3);
  
  -- Traverse sparse collection
  v_idx := v_tab.FIRST;
  << display_loop >>
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
    v_idx := v_tab.NEXT(v_idx);
  END LOOP display_loop;
END;
/

nested table collection in pl/sql

// nested table collection -- done

SET SERVEROUTPUT ON SIZE 1000000

DECLARE
  TYPE table_type IS TABLE OF NUMBER(10);
  v_tab  table_type;
  v_idx  NUMBER;
BEGIN

  -- Initialise the collection with two values.
  v_tab := table_type(1, 2);

  -- Extend the collection with extra values.
  << load_loop >>
  FOR i IN 3 .. 5 LOOP
    v_tab.extend;
    v_tab(v_tab.last) := i;
  END LOOP load_loop;
  
  -- Delete the third item of the collection.
  v_tab.DELETE(3);
  
  -- Traverse sparse collection
  v_idx := v_tab.FIRST;
  << display_loop >>
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
    v_idx := v_tab.NEXT(v_idx);
  END LOOP display_loop;
END;
/

assignment of same type nested table collection in pl/sql

// assignment of same type nested table  collection -- done

DECLARE
  TYPE table_type IS TABLE OF NUMBER(10);
  v_tab_1  table_type;
  v_tab_2  table_type;
BEGIN
  -- Initialise the collection with two values.
  v_tab_1 := table_type(1, 2);

  -- Assignment works.
  v_tab_2 := v_tab_1;
END;
/


// or 


SET SERVEROUTPUT ON
DECLARE
  TYPE table_type IS TABLE OF NUMBER(10);
  v_tab_1  table_type;
  v_tab_2  table_type;
BEGIN
  -- Initialise the collection with two values.
  v_tab_1 := table_type(1, 2);
  v_tab_2 := v_tab_1;

  IF v_tab_1 = v_tab_2 THEN
    DBMS_OUTPUT.put_line('1: v_tab_1 = v_tab_2');
  END IF;

  v_tab_1 := table_type(1, 2, 3);

  IF v_tab_1 != v_tab_2 THEN
    DBMS_OUTPUT.put_line('2: v_tab_1 != v_tab_2');
  END IF;
END;
/

assignment of two type nested table collection in pl/sql


// assignment of two type nested table  collection -- done

DECLARE
  TYPE table_type_1 IS TABLE OF NUMBER(10);
  TYPE table_type_2 IS TABLE OF NUMBER(10);
  v_tab_1  table_type_1;
  v_tab_2  table_type_2;
BEGIN
  -- Initialise the collection with two values.
  v_tab_1 := table_type_1(1, 2);

  -- Assignment causes compilation error.
  v_tab_2 := v_tab_1;
END;
/

-- output: problem


// method on collection example -- done

DECLARE
     TYPE emp_arr_typ IS TABLE OF VARCHAR2(10);
     emp_arr emp_arr_typ;
	 i  INTEGER := 0;
	 
	 CURSOR emp_cur IS SELECT name FROM ukaccount.ukcourse
     WHERE ROWNUM <= 10;
    
BEGIN
     emp_arr := emp_arr_typ();
     FOR r_emp IN emp_cur LOOP
        emp_arr.EXTEND;
        emp_arr(emp_arr.LAST) := r_emp.name;	
     END LOOP;
	 
	 -- Traverse sparse collection
	 
	  i := emp_arr.FIRST;
	  << display_loop >>
	  WHILE i IS NOT NULL LOOP
		DBMS_OUTPUT.PUT_LINE('The Name is ' || emp_arr(i));
		i := emp_arr.NEXT(i);
	  END LOOP display_loop;   
END;
/

bONEandALL
Visitor

Total : 19502

Today :37

Today Visit Country :

  • United States
  • Germany
  • United Kingdom
  • India
  • Switzerland
  • Singapore
  • The Netherlands
  • Russia