快速生成insert update select 语句
declare
sText VARCHAR2(3000);
sTable varchar2(30);
begin
sTable := '&Tablename';
select get_sql_insert(sTable) INTO sText from dual;
DBMS_OUTPUT.put_line(sText);
DBMS_OUTPUT.put_line('');
DBMS_OUTPUT.put_line('');
select get_sql_Update(sTable) INTO sText from dual;
DBMS_OUTPUT.put_line(sText);
DBMS_OUTPUT.put_line('');
DBMS_OUTPUT.put_line('');
select get_sql_select(sTable) INTO sText from dual;
DBMS_OUTPUT.put_line(sText);
end;
/
CREATE OR REPLACE FUNCTION GET_SQL_INSERT(STABLENAME IN VARCHAR2) RETURN VARCHAR2 IS
/*
-- 用途 : 获取表全部字段的插入（INSERT）语句
*/
RESULT VARCHAR2(3000);
I INTEGER;
BEGIN
I:= 0;
RESULT := 'INSERT INTO '|| UPPER(STABLENAME) ||'(' ;
FOR CUR IN (SELECT COLUMN_NAME FROM USER_TAB_COLS WHERE UPPER(TABLE_NAME) = UPPER(STABLENAME) ORDER BY COLUMN_ID ) LOOP
IF I = 0 THEN
RESULT := RESULT || CUR.COLUMN_NAME;
ELSE
RESULT := RESULT ||',' ||CUR.COLUMN_NAME;
END IF;
I:= I+1;
END LOOP;
RESULT := RESULT || ' ) VALUES ( ';
I:= 0;
FOR CUR IN (SELECT COLUMN_NAME FROM USER_TAB_COLS WHERE UPPER(TABLE_NAME) = UPPER(STABLENAME) ORDER BY COLUMN_ID ) LOOP
IF I = 0 THEN
RESULT := RESULT ||':'||CUR.COLUMN_NAME;
ELSE
RESULT := RESULT ||',:' ||CUR.COLUMN_NAME;
END IF;
I:= I+1;
END LOOP;
RESULT := RESULT || ' ) ';
RETURN(RESULT);
END GET_SQL_INSERT;
/
CREATE OR REPLACE FUNCTION GET_SQL_UPDATE(STABLENAME IN VARCHAR2)
RETURN VARCHAR2 IS
/*
-- 用途 : 获取表全部字段的更新（UPDATE）语句
*/
RESULT VARCHAR2(3000);
PK_COL VARCHAR2(30);
I INTEGER;
BEGIN
I := 0;
RESULT := 'UPDATE ' || UPPER(STABLENAME) || ' SET ';
FOR CUR IN (SELECT COLUMN_NAME
FROM USER_TAB_COLS
WHERE UPPER(TABLE_NAME) = UPPER(STABLENAME)
ORDER BY COLUMN_ID) LOOP
IF I = 0 THEN
RESULT := RESULT || CUR.COLUMN_NAME || '= :' || CUR.COLUMN_NAME;
ELSE
RESULT := RESULT || ',' || CUR.COLUMN_NAME || '= :' ||
CUR.COLUMN_NAME;
END IF;
I := I + 1;
END LOOP;
RESULT := RESULT || ' WHERE ';
BEGIN
SELECT UL.COLUMN_NAME
INTO PK_COL
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UL
WHERE UC.CONSTRAINT_NAME = UL.CONSTRAINT_NAME
AND UC.TABLE_NAME = UPPER(STABLENAME)
AND CONSTRAINT_TYPE = 'P';
EXCEPTION
WHEN OTHERS THEN
PK_COL := '';
END;
IF PK_COL IS NOT NULL THEN
RESULT := RESULT || PK_COL || '1 = :' || PK_COL || '1';
END IF;
RETURN(RESULT);
END GET_SQL_UPDATE;
/
CREATE OR REPLACE FUNCTION GET_SQL_SELECT(STABLENAME VARCHAR2,
OtherName VARCHAR2 default '') RETURN VARCHAR2 IS
/*
-- 用途 : 获取表全部字段的查询（SELECT）语句
*/
RESULT VARCHAR2(3000);
I INTEGER;
PreOtherName VARCHAR2(31);
BEGIN
I:= 0;
IF nvl(TRIM(otherName),' ') = ' ' THEN
PreOtherName := '';
ELSE
PreOtherName := TRIM(otherName) ||'.';
END IF;
RESULT := 'SELECT ';
FOR CUR IN (SELECT COLUMN_NAME FROM USER_TAB_COLS WHERE UPPER(TABLE_NAME) = UPPER(STABLENAME) ORDER BY COLUMN_ID ) LOOP
IF I = 0 THEN
RESULT := RESULT || PreOtherName||CUR.COLUMN_NAME;
ELSE
RESULT := RESULT ||',' ||PreOtherName||CUR.COLUMN_NAME;
END IF;
I:= I+1;
END LOOP;
RESULT := RESULT || ' FROM '|| UPPER(STABLENAME)||' '||TRIM(otherName);
RETURN(RESULT);
END GET_SQL_SELECT;