/* =============================================================== * GET_PK:取得主键定义 说明: 如果返回NULL,表示这个表没有定义主键 * =============================================================== */ FUNCTION GET_PK(p_table CHAR) RETURN CHAR IS CURSOR c_col(p_constraint CHAR) IS SELECT COLUMN_NAME FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME = p_constraint ORDER BY POSITION;
v_constraint VARCHAR2(30); v_pk VARCHAR2(60); --主键定义,字段间以逗号分割 BEGIN /* 取主键名称 */ BEGIN SELECT CONSTRAINT_NAME INTO v_constraint FROM USER_CONSTRAINTS WHERE TABLE_NAME = UPPER(p_table) AND CONSTRAINT_TYPE = 'P'; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END; /* 取出主键字段 */ v_pk := ''; FOR rec IN c_col(v_constraint) LOOP v_pk := v_pk || rec.column_name || ','; END LOOP; v_pk := SUBSTR(v_pk,1,LENGTH(v_pk)-1); RETURN v_pk; END GET_PK;
select TABLE_NAME,CONSTRAINT_NAME,SEARCH_CONDITION,STATUS from user_constraints WHERE TABLE_name=upper('&TABLE_Name') and constraint_type='P';
to snowmagic(sl):你的不符合我的要求啊,^_^ to gechangwei(葛长伟):我在toad里编译出错……
select * from user_constraints WHERE TABLE_NAME=upper('表名') and constraint_type='P';
to gechangwei(葛长伟):我在toad里编译出错……我要的是查出来哪些字段是主键!!!
也就是说我要的是column这项,而非Constraint这项。
自己动手,丰衣足食: select uc.TABLE_NAME,ucc.COLUMN_NAME from user_constraints uc,user_cons_columns ucc WHERE uc.CONSTRAINT_NAME = ucc.CONSTRAINT_NAME and uc.TABLE_NAME=upper('station') and constraint_type='P'各位看看有没有问题?
GET_PK:取得主键定义
说明:
如果返回NULL,表示这个表没有定义主键
* =============================================================== */
FUNCTION GET_PK(p_table CHAR) RETURN CHAR IS
CURSOR c_col(p_constraint CHAR) IS
SELECT COLUMN_NAME
FROM USER_CONS_COLUMNS
WHERE CONSTRAINT_NAME = p_constraint
ORDER BY POSITION;
v_constraint VARCHAR2(30);
v_pk VARCHAR2(60); --主键定义,字段间以逗号分割
BEGIN
/* 取主键名称 */
BEGIN
SELECT CONSTRAINT_NAME
INTO v_constraint
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = UPPER(p_table) AND
CONSTRAINT_TYPE = 'P';
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END; /* 取出主键字段 */
v_pk := '';
FOR rec IN c_col(v_constraint) LOOP
v_pk := v_pk || rec.column_name || ',';
END LOOP;
v_pk := SUBSTR(v_pk,1,LENGTH(v_pk)-1); RETURN v_pk;
END GET_PK;
from user_constraints WHERE TABLE_name=upper('&TABLE_Name')
and constraint_type='P';
to gechangwei(葛长伟):我在toad里编译出错……
from user_constraints WHERE TABLE_NAME=upper('表名')
and constraint_type='P';
select uc.TABLE_NAME,ucc.COLUMN_NAME
from user_constraints uc,user_cons_columns ucc WHERE uc.CONSTRAINT_NAME = ucc.CONSTRAINT_NAME and uc.TABLE_NAME=upper('station')
and constraint_type='P'各位看看有没有问题?