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;