解决方案 »
- 两种sql写法,哪一种效率更高?
- oracle数据库同步问题
- ORA-00600: internal error code, arguments: [kpofdr-long]
- 求一个SQL语句
- 如何使用pl/sql直接对oracle的表进行操作?
- oracle中怎样让一个字段自动crement(不用sequence)
- 请各位高手大侠帮我这个新手解决这道难题...十分感谢的说!!!还有是全英文的..麻烦各位帮帮忙哦..很重要的啊!!
- 怎样知道数据高速缓冲区、字典高速缓冲区、共享sql池、重演日志缓冲区的大小?
- 数据迁移:从SQL Server到Oracle
- 如何保持Oracle中两个结构一样的表的数据同步或一致
- 请好心人解决一下Oracle中的关系代数问题!!!
- 数据库高速缓存命中百分比只有百分之60.32怎么办?
select column_name,data_type ,data_length,data_precision,data_scale from user_tab_columns [where table_name='my_tab'];如果是向表中插入数据,不用那么麻烦做判断,直接insert就行
insert into my_tmp(tname,cname)
select 'my_tab',column_name
from user_tab_columns
where table_name='my_tab' and column_name like 'SQ%';
顶,说的不错!用sp或者function吧!
我就用存储过程弄出来了啊!!!!研究下还不会很难, 现贴出代码出来!!!!
CREATE OR REPLACE PROCEDURE GET_ALL_TABLE_SQID IS CURSOR mOutCur IS SELECT TABLE_NAME AS TNAME FROM ALL_TABLES WHERE OWNER='LZSQ' AND TABLE_NAME NOT LIKE 'CODE_%%' AND TABLE_NAME NOT LIKE 'WB%%' AND TABLE_NAME NOT LIKE 'GNET_CODE_%%'; TYPE DYNCURSOR IS REF CURSOR; mDynCur DYNCURSOR; sTableName VARCHAR2(100); sColumnName VARCHAR2(100); sSQCnt VARCHAR2(10); sSQLBuff VARCHAR2(100);BEGIN sSQCnt:='0'; OPEN mOutCur; FETCH mOutCur INTO sTableName; WHILE mOutCur%FOUND LOOP OPEN mDynCur FOR 'SELECT COLUMN_NAME FROM ALL_TAB_COLS WHERE OWNER=''LZSQ'' AND DATA_TYPE=''VARCHAR2'' AND TABLE_NAME='''||sTableName||''''; FETCH mDynCur INTO sColumnName; WHILE mDynCur%FOUND LOOP sSQLBuff := 'SELECT COUNT(*) INTO :x FROM '||sTableName||' WHERE "'||sColumnName||'" LIKE ''SQ%'''; DBMS_OUTPUT.PUT_LINE('['||sTableName||']-['||sColumnName||']......OK!'); EXECUTE IMMEDIATE sSQLBuff INTO sSQCnt; IF TO_NUMBER(sSQCnt)>0 THEN INSERT INTO MY_SQID_TAB(TABNAME,COLNAME) VALUES(sTableName,sColumnName); COMMIT; END IF; FETCH mDynCur INTO sColumnName; END LOOP; CLOSE mDynCur; FETCH mOutCur INTO sTableName; END LOOP; COMMIT; CLOSE mOutCur;END GET_ALL_TABLE_SQID;