Oracle uses a cursor to query a string that combines the names of all fields in a specified data table.

Application Scenario: refer to the Code for querying all field names of a data table on the Internet, and use a cursor to generate a field name consisting of all field names of a specified table and commas (,) that are used to separate select commas (,).

Application Scenario: refer to the Code for querying all field names of a data table on the Internet, and use a cursor to generate a field name consisting of all field names of a specified table and commas (,) that are used to separate select commas (,).

Application Scenario: refer to the Code for querying all field names of a data table on the Internet. Use a cursor to generate a list of field names separated by a comma (,) and a list of field names from strings.

The query result is output as follows:

The field list string of the current data table TB_UD_USER is
AH, BIRTHPLACE, BM, CELLPHONE, example, example, DJJID, GZCX, GZKH, GZSFZH, HJDZ, HYZK, ID, JHRQ, JTZZ, LAFX_LD, LJDZ, LLY, LXDH, NAME, NXDH, POLICENUMBER, RESERVE1, RESERVE10, RESERVE9, SCCP, SEX, sf1_, SFQBY, SFZ, SPJB, YL_22, ZJ, ZW, ZZMM
The statement used to query all records in the current data table TB_UD_USER is
Select AH, BIRTHPLACE, BM, CELLPHONE, numbers, numbers, DJJID, GZCX, GZKH, GZSFZH, HJDZ, HYZK, ID, JHRQ, JTZZ, LAFX_LD, LJDZ, LLY, LXDH, NAME, NXDH, effecenumber, RESERVE1, RESERVE10, RESERVE9, SCCP, SEX, sf1_, SFQBY, SFZ, SPJB, YL_22, ZJ, ZW, ZZMM from TB_UD_USER

The detailed script code is as follows:

-- Oracle uses a cursor to query a string that combines the names of all fields in a specified data table

Declare
Mytablename NVARCHAR2 (200): = 'tb _ UD_USER '; -- defines the name variable of the data table to be queried.
Mystring NVARCHAR2 (1000): = ''; -- defines the string variable of the field name list to be output.
Selstring VARCHAR2 (2000): = ''; -- defines the string variable of the query statement to be output.

Cursor mycursor is -- defines the cursor

Select distinct TABLE_COLUMN. *, TABLE_NALLABLE.DATA_TYPE, TABLE_NALLABLE.NULLABLE from (select distinct utc. table_name, utc. comments table_comments, ucc. column_name, ucc. comments column_comments from user_tab_comments utc, user_col_comments ucc where utc. table_name = ucc. table_name and utc. table_name not like '% _ B' and utc. table_name not like '% _ Z' and utc. table_name not like '% 1%') TABLE_COLUMN, (select distinct table_name, column_name, nullable, DATA_TYPE from user_tab_cols where table_name not like '% _ B' and table_name not like '% _ Z' and table_name not like' % 100 ') TABLE_NALLABLE where TABLE_COLUMN.column_name = tables and TABLE_COLUMN.TABLE_NAME = TABLE_NALLABLE.table_name and TABLE_COLUMN.TABLE_NAME = mytablename order by TABLE_COLUMN.TABLE_NAME, TABLE_COLUMN.column_name;

Myrecord mycursor % rowtype; -- defines the cursor record type
Counter int: = 0;
Begin
Open mycursor; -- open the cursor
If mycursor % isopen then -- determines whether the open is successful
Loop -- Obtain Record Sets cyclically
Fetch mycursor into myrecord; -- get records in the cursor

If mycursor % found then -- the cursor's found attribute determines whether a record exists
Begin
-- If it is the first field
If (mystring = '') then
Mystring: = myrecord. column_name;
Else
Mystring: = mystring | ',' | myrecord. column_name;
End if;

End;

Else

Begin

Dbms_output.put_line ('current data table '| mytablename |''s field list string is ');
Dbms_output.put_line (mystring );

Selstring: = 'select' | mystring | 'from' | mytablename;

Dbms_output.put_line ('current data table '| mytablename | 'Statement for querying all records is ');
Dbms_output.put_line (selstring );

Exit;

End;

End if;

End loop;
Else
Dbms_output.put_line ('cursor not opened ');
End if;

Close mycursor;

End;