Collection (Union Array (associative Arrays)) of composite data types in 11.pl_sql--pl_sql

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/49/1C/wKioL1QPA7WBp0SbAAECf0yxCvI103.jpg "style=" float: none; "title=" 1.png "alt=" Wkiol1qpa7wbp0sbaaecf0yxcvi103.jpg "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/49/1C/wKioL1QPA7WBRyTiAADlcs7ETh4851.png "style=" float: none; "title=" 2.PNG "alt=" Wkiol1qpa7wbrytiaadlcs7eth4851.png "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/49/1A/wKiom1QPA6mCa4zAAAIestWf-YI591.jpg "style=" float: none; "title=" 3.png "alt=" Wkiom1qpa6mca4zaaaiestwf-yi591.jpg "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/49/1A/wKiom1QPA6riJ39tAAFuOUDyOfI676.png "style=" float: none; "title=" 4.PNG "alt=" Wkiom1qpa6rij39taafuoudyofi676.png "/>

Sql> Edit

DECLARE

TYPE list_of_names_t is tableof employees.first_name%type

INDEX by Pls_integer;

Happyfamily list_of_names_t;

L_row Pls_integer;

BEGIN

Happyfamily (2020202020): = ' Eli ';

Happyfamily ( -15070): = ' Steven ';

Happyfamily ( -90900): = ' Chris ';

Happyfamily (in): = ' Veva ';

L_row: = happyfamily. First;

While (L_row was not NULL)

LOOP

Dbms_output. Put_Line (l_row| | '--' | | Happyfamily (L_row));

L_row: =happyfamily. NEXT (L_row);

END LOOP;

L_row: = 88;

IF happyfamily. EXISTS (L_row) Then

Dbms_output. Put_Line (' itis here!---' | | happyfamily (l_row));

ELSE

Dbms_output. Put_Line (' itis not here!--' | | happyfamily (l_row));

END IF;

END;

/

Sql> @notes/s44.sql

-90900-->chris

-15070-->steven

88-->veva

2020202020-->eli

It is here! -Veva

Pl/sqlprocedure successfully completed

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/49/1C/wKioL1QPA_eRdQ4yAAFf79gIm84163.png "style=" float: none; "title=" 5.PNG "alt=" Wkiol1qpa_erdq4yaaff79gim84163.png "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/49/1A/wKiom1QPA-uirSOiAAC5Z0tPka0132.jpg "style=" float: none; "title=" 6.png "alt=" Wkiom1qpa-uirsoiaac5z0tpka0132.jpg "/>

================example1=================

Sql> Edit

DECLARE

TYPE Dept_table_type is tableof

Departments%rowtype Indexby Pls_integer;

Dept_tabledept_table_type;

--Each element ofdept_table is a record

BEGIN

SELECT * into dept_table (1)

From departments

WHERE department_id = 10;

Dbms_output. Put_Line (dept_table (1). department_id | | ' ' | | Dept_table (1). Department_name | | ' ' | |

Dept_table (1). manager_id);

END;

/

Sql> @notes/s45.sql

Ten Administration 200

PL/SQL procedure successfully completed.

================example2=================

Sql> Edit

DECLARE

TYPE Emp_table_type is tableof

Employees%rowtype INDEX Bypls_integer;

My_emp_tableemp_table_type;

Max_count Number (3): = 104;

BEGIN

For I in 100..max_count

LOOP

SELECT * Intomy_emp_table (i)

From Employees

WHERE employee_id = i;

END LOOP;

For I inmy_emp_table. First: My_emp_table. Last

LOOP

Dbms_output. Put_Line (My_emp_table (i). last_name);

END LOOP;

END;

/

Sql> @notes/s46.sql

King

Kochhar

De Haan

Hunold

Ernst

Pl/sqlprocedure successfully completed

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/49/1C/wKioL1QPBCfQW29TAADYoUgVdSU052.jpg "title=" 7.png " alt= "Wkiol1qpbcfqw29taadyougvdsu052.jpg"/>

Sql> Edit

DECLARE

Subtype location_t ISVARCHAR2 (64);

TYPE Population_type is tableof number

INDEX by location_t;

L_country_populationpopulation_type;

L_count Pls_integer;

L_location location_t;

BEGIN

L_country_population (' greenland '): = 100000;

L_country_population (' USA '): = 300000000;

L_country_population (' Iceland '): = 750000;

L_country_population (' Australia '): = 23000000;

L_country_population (' USA '): = 400000000;

L_count: =l_country_population. COUNT;

Dbms_output. Put_Line (' COUNT = ' | | l_count);

L_location: =l_country_population. First;

Dbms_output. Put_Line (' firstrow = ' | | l_location);

Dbms_output. Put_Line (' firstvalue = ' | | l_country_population (l_location));

L_location: =l_country_population. Last;

Dbms_output. Put_Line (' lastrow = ' | | l_location);

Dbms_output. Put_Line (' Last Value = ' | | L_country_population (l_location));

END;

/

Sql> @notes/s47.sql

COUNT = 5

First Row = Australia

First value = 23000000

Last Row = USA

Last Value = 400000000

Pl/sqlprocedure successfully completed

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/49/1A/wKiom1QPBFOjdlPqAADlO-bI1jE351.jpg "style=" float: none; "title=" 8.png "alt=" Wkiom1qpbfojdlpqaadlo-bi1je351.jpg "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/49/1C/wKioL1QPBGGjCGSuAAFS8y0bAL8541.png "style=" float: none; "title=" 9.PNG "alt=" Wkiol1qpbggjcgsuaafs8y0bal8541.png "/>

Sql> Edit

DECLARE

Typepopulation_type is TABLE of number

INDEX BYVARCHAR2 (64);

City_populationpopulation_type;

I VARCHAR2 (64);

BEGIN

City_population (' smallville '): = 2000;

City_population (' Midland '): = 75000;

City_population (' megalopolis '): = 1000000;

--Change valueassocaited with key ' Samllville ':

City_population (' smallville '): = 2001;

--Print Associatearray:

I: =city_population. First;

--Get the firstelement of array

While I was notnull

LOOP

Dbms_output. Put_Line (' Popultaion of ' | | | | | ' Is ' | | City_population (i));

I: = city_population. NEXT (i);

--Get thenext element of array

END LOOP;

END;

/

Sql> @notes/s48.sql

Popultaion of Megalopolis is 1000000

Popultaion of Midland is 75000

Popultaion of Smallville is 2001

Pl/sqlprocedure successfully completed

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/49/1A/wKiom1QPBIKjtieFAAMOyg_UcNw942.png "title=" 10.PNG "alt=" Wkiom1qpbikjtiefaamoyg_ucnw942.png "/>

================example3=================

Sql> Edit

DECLARE

Typeemp_table_type is Table of

Employees%rowtype INDEX by Pls_integer;

My_emp_tableemp_table_type;

Max_countnumber (3): = 104;

BEGIN

For I IN100. Max_count

LOOP

SELECT * Intomy_emp_table (i)

From Employees

whereemployee_id = i;

END LOOP;

For I inmy_emp_table. First: My_emp_table. Last

LOOP

Dbms_output. Put_Line (My_emp_table (i). last_name);

END LOOP;

END;

/

Sql> @notes/s49.sql

King

Kochhar

De Haan

Hunold

Ernst

PL/SQL procedure successfully completed.

================example4=================

Sql> Edit

DECLARE

Subtype name_t ISVARCHAR2 (32);

TYPE lookup_t is TABLE ofname_t INDEX by Pls_integer;

Name_tab lookup_t;

Name name_t: = ' Frank ';

L_index Pls_integer;

L_found BOOLEAN: = FALSE;

BEGIN

--Populate the lookup table first

Name_tab (0): = ' Wiley ';

Name_tab (1): = ' Mickey ';

Name_tab (2): = ' Simon ';

Name_tab (3): = ' Neal ';

Name_tab (4): = ' Frank ';

Name_tab (5): = ' Darius ';

Name_tab (6): = ' Patrick ';

--Lookup The name in the lookup table

L_index: = Name_tab. First;

LOOP

IF L_index is NULL then

EXIT;

END IF;

IF name =name_tab (l_index) Then

L_found: =true;

EXIT;

END IF;

L_index: = Name_tab. NEXT (L_index);

END LOOP;

IF L_found Then

Dbms_output. Put_Line (' I found it: ' | | l_index | | '--' | | name);

ELSE

Dbms_output. Put_Line (' I cannot find it. Sorry! ');

END IF;

END;

/

Sql> @notes/s50.sql

I found It:4--and Frank

PL/SQL procedure successfully completed.

================example5=================

Sql> Edit

DECLARE

Subtype name_t ISVARCHAR2 (32);

Subtype value_t is VARCHAR2 (32);

TYPE lookup_t is TABLE ofvalue_t INDEX by name_t;

Name_tab lookup_t;

Name name_t: = ' Frank ';

BEGIN

--Populate the lookup table first

Name_tab (' Wiley '): = ' Wu ';

Name_tab (' Mickey '): = ' Wu ';

Name_tab (' Simon '): = ' beaker ';

Name_tab (' Neal '): = ' caffery ';

Name_tab (' Frank '): = ' Zhou ';

Name_tab (' Darius '): = ' Florizk ';

Name_tab (' Patrick '): = ' Jane ';

--Lookup The name in the lookup table

Ifname_tab. EXISTS (name) Then

Dbms_output. Put_Line (' I found it: ' | | name | | '--' | | Name_tab (name));

ELSE

Dbms_output. Put_Line (' I cannot find it. Sorry! ');

END IF;

END;

/

Sql> @notes/s51.sql

I found It:frank--and Zhou

Pl/sqlprocedure successfully completed

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/49/1A/wKiom1QPBWDRmtq7AACzPqU290E724.png "style=" float: none; "title=" 11.PNG "alt=" Wkiom1qpbwdrmtq7aaczpqu290e724.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/49/1C/wKioL1QPBW3CKd_MAAD_RILHEog169.png "style=" float: none; "title=" 12.PNG "alt=" Wkiol1qpbw3ckd_maad_rilheog169.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/49/1A/wKiom1QPBWHip2TkAAFfVr74-Nc561.png "style=" float: none; "title=" 13.PNG "alt=" Wkiom1qpbwhip2tkaaffvr74-nc561.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/49/1A/wKiom1QPBWKCB5jiAAG1LV3uD9c769.png "style=" float: none; "title=" 14.PNG "alt=" Wkiom1qpbwkcb5jiaag1lv3ud9c769.png "/>

This article is from the "Big sword without front of the great Qiao Not Work" blog, please make sure to keep this source http://wuyelan.blog.51cto.com/6118147/1550345

Collection (Union Array (associative Arrays)) of composite data types in 11.pl_sql--pl_sql