Oracle uses a cursor to query a string that is a combination of all the field names of a specified data table

Application: Refer to all the field name codes of the data table on the web, use cursors to generate all field names of the specified single table followed by commas for the list of field names separated by the select comma from the string and other occasions.

The query result output is as follows:

The field list string for the current data table Tb_ud_user is
Ah,birthplace,bm,cellphone,cjgzrq,department2,djjid,gzcx,gzkh,gzsfzh,hjdz,hyzk,id,jhrq,jtzz,lafx_ld,ljdz,lly, Lxdh,name,nxdh,policenumber,reserve1,reserve10,reserve9,sccp,sex,sfjh,sfqby,sfz,spjb,yl_22,zj,zw,zzmm
The current data table Tb_ud_user queries all record statements as
Select Ah,birthplace,bm,cellphone,cjgzrq,department2,djjid,gzcx,gzkh,gzsfzh,hjdz,hyzk,id,jhrq,jtzz,lafx_ld,ljdz , lly,lxdh,name,nxdh,policenumber,reserve1,reserve10,reserve9,sccp,sex,sfjh,sfqby,sfz,spjb,yl_22,zj,zw,zzmm from Tb_ud_user

The detailed script code is as follows:

--oracle A string that uses a cursor to query all the field names of a specified data table

Declare
Mytablename NVARCHAR2 (+): = ' tb_ud_user '; --Define the data table name variable to query
MyString NVARCHAR2 (1000): = "; --Define a list of field names to output string variables
Selstring VARCHAR2 (2000): = "; --Define the query statement string variables to be output

Cursor MyCursor is--Define cursors   

SELECT DISTINCT table_column.*,table_nallable. Data_type,table_nallable. nullable  from (SELECT DISTINCT utc.table_name  table_name, utc.comments    table_comments, Ucc.column_name 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 don't like '%1% ') table_column,       (select DISTINCT table_name, COLUMN_NAME, nullable, data_type from user_tab_cols  where table_name don't like '%_b '   and T Able_name not like '%_z '   and table_name don't like '%1% ') table_nallable  where table_column.column_name = Table_ nallable.column_name   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; --Define Cursor record type
Counter int: = 0;
Begin
Open mycursor; --Open cursor
If Mycursor%isopen then--Judging open success
Loop-Loop Get recordset
Fetch mycursor into Myrecord; --Get the record in the cursor

If Mycursor%found then--the found property of the cursor determines if there is a record
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| | ' The field list string is ');
Dbms_output.put_line (mystring);

selstring:= ' SELECT ' | | mystring| | ' From ' | | Mytablename;

Dbms_output.put_line (' Current data table ' | | mytablename| | ' Query all record statements as ');
Dbms_output.put_line (selstring);

Exit

End

End If;

End Loop;
Else
Dbms_output.put_line (' Cursor not open ');
End If;

Close MyCursor;

End

Oracle uses a cursor to query a string that is a combination of all the field names of a specified data table