自动生成sql
适用：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 ISPROCEDURE 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 ASPROCEDURE CRUSQL(TNAME IN VARCHAR2,CRUD_TYPE IN VARCHAR2) ASCURSOR T_CURSOR ISSELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTHFROM 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_LENALL_TAB_COLUMNS.DATA_LENGTH%TYPE);TB2 TB;ALLCOUNT NUMBER;COUNTNUM NUMBER := 0;BEGINSELECT COUNT(*)INTO ALLCOUNTFROM ALL_TAB_COLUMNS WHERE OWNER = 'SCOTT' AND TABLE_NAME = UPPER(TNAME) ORDER BY COLUMN_ID;IF CRUD_TYPE = 'r' THENDBMS_OUTPUT.PUT('SELECT ');OPEN T_CURSOR;LOOPFETCH T_CURSORINTO TB2;IF T_CURSOR%FOUND THENCOUNTNUM := COUNTNUM + 1;DBMS_OUTPUT.PUT(UPPER(TNAME));DBMS_OUTPUT.PUT('.');DBMS_OUTPUT.PUT(TB2.C_NAME);IF COUNTNUM < ALLCOUNT THENDBMS_OUTPUT.PUT(',');END IF;ELSEEXIT;END IF;END LOOP;CLOSE T_CURSOR;DBMS_OUTPUT.PUT(' FROM ');DBMS_OUTPUT.PUT(UPPER(TNAME));DBMS_OUTPUT.PUT_LINE('');ELSIF CRUD_TYPE = 'c' THENDBMS_OUTPUT.PUT('INSERT INTO ');DBMS_OUTPUT.PUT(UPPER(TNAME));DBMS_OUTPUT.PUT('(');OPEN T_CURSOR;LOOPFETCH T_CURSORINTO TB2;IF T_CURSOR%FOUND THENCOUNTNUM := COUNTNUM + 1;DBMS_OUTPUT.PUT(TB2.C_NAME);SQL_STR := SQL_STR || '?';IF COUNTNUM < ALLCOUNT THENDBMS_OUTPUT.PUT(',');SQL_STR := SQL_STR || ',';END IF;ELSEEXIT;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' THENDBMS_OUTPUT.PUT('UPDATE ');DBMS_OUTPUT.PUT(UPPER(TNAME));DBMS_OUTPUT.PUT(' SET ');OPEN T_CURSOR;LOOPFETCH T_CURSORINTO TB2;IF T_CURSOR%FOUND THENCOUNTNUM := COUNTNUM + 1;DBMS_OUTPUT.PUT(TB2.C_NAME);DBMS_OUTPUT.PUT('=?');IF COUNTNUM < ALLCOUNT THENDBMS_OUTPUT.PUT(',');END IF;ELSEEXIT;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) ASCURSOR T_CURSOR ISSELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTHFROM 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_LENALL_TAB_COLUMNS.DATA_LENGTH%TYPE);TB2 TB;ALLCOUNT NUMBER;COUNTNUM NUMBER := 0;BEGINSELECT COUNT(*)INTO ALLCOUNTFROM 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(');LOOPFETCH T_CURSORINTO TB2;IF T_CURSOR%FOUND THENCOUNTNUM := 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 THENDBMS_OUTPUT.PUT_LINE(',');END IF;ELSEEXIT;END IF;END LOOP;CLOSE T_CURSOR;DBMS_OUTPUT.PUT_LINE('');DBMS_OUTPUT.PUT_LINE(');');END CREATETYPE;END COM_BKY_TOOL;
页:
[1]