自动生成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 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;