快速生成Insert、Update、Select语句
来源:互联网 发布:网络安全法 二审 编辑:程序博客网 时间:2024/04/29 20:39
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;
- 快速生成Insert、Update、Select语句
- 使用Java生成insert,select,update语句
- Oracle-自动生成insert、update、select、javabean语句
- select,delete,update,insert语句使用方法
- SQL语句复习:insert,update,delete,select
- SQL语句复习:insert,update,delete,select
- jpa 原生insert\delete\update\select语句
- jpa 原生insert\delete\update\select语句
- SQL语句:update---select,insert---select,select---into
- 根据表名自动生成INSERT,UPDATE,DELETE,SELECT的SQL语句
- 根据表名自动生成INSERT,UPDATE,DELETE,SELECT的SQL语句
- 根据表名自动生成INSERT,UPDATE,DELETE,SELECT的SQL语句
- 根据表名自动生成INSERT,UPDATE,DELETE,SELECT的SQL语句
- oralce 生成 select 和 update 语句
- SQL基本语句(Insert,Select,Update和Delete)
- QtCreator Qt中执行SQL语句,select,update,delete,insert
- jdbc----mysql的select、insert、update、delete 基本语句
- 数据库的insert , delete , update , select 的基本语句
- Bundle传值与取值
- web编程细节
- 【算法题】题目:求1+2+…+n,要求不能使用乘除法、for、while、if、else、switch、case等关键字以及条件判断语句(A?B:C)
- 《机器学习实战》第一章
- uva1609 foul play
- 快速生成Insert、Update、Select语句
- android软键盘事件处理
- gcc编译过程 常用指令:#warning/#error/#pragma 环境变量 头文件
- Java客户端利用httpclient来同时上传文件和其他字符串参数
- 理解本真的REST架构风格
- nyoj 269 VF
- 项目管理过程 工作绩效数据,信息和报告
- 利用python库中的pdfminer解析paper pdf文档
- 160多个android开源代码汇总