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)
set serveroutput on size 30000; // declare once DECLARE message varchar2(20):='Hi Uzzal'; BEGIN dbms_output.put_line(message); END; /
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; /
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; /
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; /
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; /
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; /
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; /
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; /
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; /
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; /
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; /
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; /
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; /
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 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 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;
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; /
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; /
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 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
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';
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; /
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 -- 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 -- 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 -- 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; /
Total : 27273
Today :9
Today Visit Country :