适用:oracle
用法
用法
call COM_BKY_TOOL .CRUSQL('表名','c') 生成insert语句
call COM_BKY_TOOL .CRUSQL('表名','r') 生成select语句
call COM_BKY_TOOL .CRUSQL('表名','u') 生成update语句
-----------------------------------------------------------------------包名------------------------------------------
CREATE OR REPLACE PACKAGE COM_BKY_TOOL IS
PROCEDURE CRUSQL
(
TNAME IN VARCHAR2,
CRUD_TYPE IN VARCHAR2
);
PROCEDURE CREATETYPE(TNAME IN VARCHAR2);
END COM_BKY_TOOL;
-----------------------------------------------------------------------包体------------------------------------------
CREATE OR REPLACE PACKAGE BODY COM_BKY_TOOL AS
PROCEDURE CRUSQL
(
TNAME IN VARCHAR2,
CRUD_TYPE IN VARCHAR2
) AS
CURSOR T_CURSOR IS
SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH
FROM ALL_TAB_COLUMNS
WHERE OWNER = 'SCOTT'
AND TABLE_NAME = UPPER(TNAME)
ORDER BY COLUMN_ID;
SQL_STR VARCHAR2(1000);
TYPE TB IS RECORD(
C_NAME ALL_TAB_COLUMNS.COLUMN_NAME%TYPE,
D_TYPE ALL_TAB_COLUMNS.DATA_TYPE%TYPE,
D_LEN ALL_TAB_COLUMNS.DATA_LENGTH%TYPE);
TB2 TB;
ALLCOUNT NUMBER;
COUNTNUM NUMBER := 0;
BEGIN
SELECT COUNT(*)
INTO ALLCOUNT
FROM ALL_TAB_COLUMNS
WHERE OWNER = 'SCOTT'
AND TABLE_NAME = UPPER(TNAME)
ORDER BY COLUMN_ID;
IF CRUD_TYPE = 'r' THEN
DBMS_OUTPUT.PUT('SELECT ');
OPEN T_CURSOR;
LOOP
FETCH T_CURSOR
INTO TB2;
IF T_CURSOR%FOUND THEN
COUNTNUM := COUNTNUM + 1;
DBMS_OUTPUT.PUT(UPPER(TNAME));
DBMS_OUTPUT.PUT('.');
DBMS_OUTPUT.PUT(TB2.C_NAME);
IF COUNTNUM < ALLCOUNT THEN
DBMS_OUTPUT.PUT(',');
END IF;
ELSE
EXIT;
END IF;
END LOOP;
CLOSE T_CURSOR;
DBMS_OUTPUT.PUT(' FROM ');
DBMS_OUTPUT.PUT(UPPER(TNAME));
DBMS_OUTPUT.PUT_LINE('');
ELSIF CRUD_TYPE = 'c' THEN
DBMS_OUTPUT.PUT('INSERT INTO ');
DBMS_OUTPUT.PUT(UPPER(TNAME));
DBMS_OUTPUT.PUT('(');
OPEN T_CURSOR;
LOOP
FETCH T_CURSOR
INTO TB2;
IF T_CURSOR%FOUND THEN
COUNTNUM := COUNTNUM + 1;
DBMS_OUTPUT.PUT(TB2.C_NAME);
SQL_STR := SQL_STR || '?';
IF COUNTNUM < ALLCOUNT THEN
DBMS_OUTPUT.PUT(',');
SQL_STR := SQL_STR || ',';
END IF;
ELSE
EXIT;
END IF;
END LOOP;
CLOSE T_CURSOR;
DBMS_OUTPUT.PUT(')VALUES(');
DBMS_OUTPUT.PUT(SQL_STR);
DBMS_OUTPUT.PUT(')');
DBMS_OUTPUT.PUT_LINE('');
ELSIF CRUD_TYPE = 'u' THEN
DBMS_OUTPUT.PUT('UPDATE ');
DBMS_OUTPUT.PUT(UPPER(TNAME));
DBMS_OUTPUT.PUT(' SET ');
OPEN T_CURSOR;
LOOP
FETCH T_CURSOR
INTO TB2;
IF T_CURSOR%FOUND THEN
COUNTNUM := COUNTNUM + 1;
DBMS_OUTPUT.PUT(TB2.C_NAME);
DBMS_OUTPUT.PUT('=?');
IF COUNTNUM < ALLCOUNT THEN
DBMS_OUTPUT.PUT(',');
END IF;
ELSE
EXIT;
END IF;
END LOOP;
CLOSE T_CURSOR;
DBMS_OUTPUT.PUT_LINE(' WHERE ID=?');
DBMS_OUTPUT.PUT_LINE('');
END IF;
END CRUSQL;
--
PROCEDURE CREATETYPE(TNAME IN VARCHAR2) AS
CURSOR T_CURSOR IS
SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH
FROM ALL_TAB_COLUMNS
WHERE OWNER = 'SCOTT'
AND TABLE_NAME = UPPER(TNAME)
ORDER BY COLUMN_ID;
TYPE TB IS RECORD(
C_NAME ALL_TAB_COLUMNS.COLUMN_NAME%TYPE,
D_TYPE ALL_TAB_COLUMNS.DATA_TYPE%TYPE,
D_LEN ALL_TAB_COLUMNS.DATA_LENGTH%TYPE);
TB2 TB;
ALLCOUNT NUMBER;
COUNTNUM NUMBER := 0;
BEGIN
SELECT COUNT(*)
INTO ALLCOUNT
FROM ALL_TAB_COLUMNS
WHERE OWNER = 'SCOTT'
AND TABLE_NAME = UPPER(TNAME);
OPEN T_CURSOR;
DBMS_OUTPUT.PUT('TYPE ');
DBMS_OUTPUT.PUT(UPPER(TNAME));
DBMS_OUTPUT.PUT_line(' IS RECORD(');
LOOP
FETCH T_CURSOR
INTO TB2;
IF T_CURSOR%FOUND THEN
COUNTNUM := COUNTNUM + 1;
DBMS_OUTPUT.PUT(TB2.C_NAME);
DBMS_OUTPUT.PUT(' ');
DBMS_OUTPUT.PUT(UPPER(TNAME));
DBMS_OUTPUT.PUT('.');
DBMS_OUTPUT.PUT(TB2.C_NAME);
DBMS_OUTPUT.PUT('%TYPE');
IF COUNTNUM < ALLCOUNT THEN
DBMS_OUTPUT.PUT_LINE(',');
END IF;
ELSE
EXIT;
END IF;
END LOOP;
CLOSE T_CURSOR;
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE(');');
END CREATETYPE;
END COM_BKY_TOOL;