已经解决了先定义一个变量 select count(*) into 定义的变量 from 所查询的表
nCount int;select count(*) into nCount from Tablename;
---定义变量 declare t number(5,2) ---赋值给t select count(*) into t from a
SQL> CREATE OR REPLACE PROCEDURE p_disp_col(v_table_name VARCHAR2) IS 2 TYPE array_type IS TABLE OF VARCHAR2(30) INDEX BY PLS_INTEGER; 3 v_cols array_type; 4 BEGIN 5 SELECT t.COLUMN_NAME BULK COLLECT 6 INTO v_cols 7 FROM user_tab_columns t 8 WHERE t.TABLE_NAME = upper(v_table_name); 9 FOR i IN 1 .. v_cols.count LOOP 10 dbms_output.put_line(v_cols(i)); 11 END LOOP; 12 END; 13 /过程已创建。SQL> set serveroutput on;SQL> exec p_disp_col('emp'); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNOPL/SQL 过程已成功完成。SQL>
给注释吧,从 SQL> set serveroutput on;这段开始plsql用plsql运行不了。
结贴。用了这段,很简明好用。 select column_name,data_type,data_length,data_precision,data_scale from user_tab_columns where table_name=upper('emp');
--其实还可以用游标做,但是无非是从user_tab_columns中获取特定字段 select column_name,data_type,data_length,data_precision,data_scale from user_tab_columns where table_name=upper('emp');
select count(*) into 定义的变量 from 所查询的表
declare t number(5,2)
---赋值给t
select count(*) into t from a
2 TYPE array_type IS TABLE OF VARCHAR2(30) INDEX BY PLS_INTEGER;
3 v_cols array_type;
4 BEGIN
5 SELECT t.COLUMN_NAME BULK COLLECT
6 INTO v_cols
7 FROM user_tab_columns t
8 WHERE t.TABLE_NAME = upper(v_table_name);
9 FOR i IN 1 .. v_cols.count LOOP
10 dbms_output.put_line(v_cols(i));
11 END LOOP;
12 END;
13 /过程已创建。SQL> set serveroutput on;SQL> exec p_disp_col('emp');
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNOPL/SQL 过程已成功完成。SQL>
给注释吧,从
SQL> set serveroutput on;这段开始plsql用plsql运行不了。
oracle 用过程获得表的字段
结贴。用了这段,很简明好用。
select column_name,data_type,data_length,data_precision,data_scale
from user_tab_columns
where table_name=upper('emp');
--其实还可以用游标做,但是无非是从user_tab_columns中获取特定字段
select column_name,data_type,data_length,data_precision,data_scale
from user_tab_columns
where table_name=upper('emp');