SQL语句

基于ibatis的sql语句，包含创建表，插入数据，查询数据，删除表等。

示例：

   
   



   
   
	
    
    
	
    
    
	
    
    		
	
	
    
    
     
     
		CREATE TABLE R_HSR_Index_Trend
		(
			VENDOR           NUMBER(1),
			RAT              NUMBER(1),
			TIMEVALUE        NUMBER(20),
			KPI3        	 NUMBER(17,2),
			KPI80      		 NUMBER(17,2),
			KPI45    		 NUMBER(17,2)
		)
	
    
    
	
	
    
    
     
     
		CREATE TABLE R_HSR_Index_Geo
		(
			VENDOR           NUMBER(1),
			RAT              NUMBER(1),
			LINEID		     NUMBER(3),
			LINE		     Varchar2(256),
			ROADSECTIONID    NUMBER(3),
			ROADSECTION   	 varchar2(256),
			KPI3        	 NUMBER(17,2),
			KPI80      		 NUMBER(17,2),
			KPI45    		 NUMBER(17,2)
		)
	
    
    
	
	
    
    
     
     
		CREATE TABLE R_HSR_Index_Rank
		(
			VENDOR           NUMBER(1),
			RAT              NUMBER(1),
			PREFECTUREID	 NUMBER(3),
			PREFECTURE		 Varchar2(256),
			KPI3        	 NUMBER(17,2),
			KPI80      		 NUMBER(17,2),
			KPI45    		 NUMBER(17,2)
		)
	
    
    

	
    
    
     
     
		INSERT INTO R_HSR_Index_Trend
		(
			VENDOR,
			RAT,
			TIMEVALUE,
			KPI3,
			KPI80,
			KPI45
		)
		VALUES  
		(
			^vendor^,
			^rat^,
			^timeValue^,
			^KPI3^,
			^KPI80^,
			^KPI45^
		)
	
    
    
		
	
    
    
     
     
		INSERT INTO R_HSR_Index_Geo
		(
			VENDOR,
			RAT,
			LINEID,
			LINE,
			ROADSECTIONID,
			ROADSECTION,
			KPI3,
			KPI80,
			KPI45
		)
		VALUES  
		(
			^vendor^,
			^rat^,
			^lineId^,
			#line#,
			^roadSectionId^,
			#roadSection#,
			^KPI3^,
			^KPI80^,
			^KPI45^
		)     
	
    
    
	
	
    
    
     
     
		INSERT INTO R_HSR_Index_Rank
		(
			VENDOR,
			RAT,
			PREFECTUREID,
			PREFECTURE,
			KPI3,
			KPI80,
			KPI45
		)
		VALUES  
		(
			^vendor^,
			^rat^,
			^cityId^,
			#city#,
			^KPI3^,
			^KPI80^,
			^KPI45^
		)
	
    
    		
	
	
    
    

		SELECT
			   TIMEVALUE   as TIMEVALUE,
			   ^Kpi^        as KPI
		FROM   naspa.R_HSR_Index_Trend
		ORDER BY TIMEVALUE ASC
	
	
	
    
    

		SELECT
		 	   LINE        		as LINE,
			   LINEID      		as LINEID,
			   ROADSECTION      as ROADSECTION,
			   ROADSECTIONID    as ROADSECTIONID,
			   ^Kpi^            as KPI		   
		FROM   naspa.R_HSR_Index_Geo
		ORDER BY LINEID,ROADSECTIONID ASC
	
	
	
    
    

		SELECT
			   PREFECTURE       as CITY,
			   PREFECTUREID     as CITYID,
			   ^Kpi^        as KPI
		FROM   naspa.R_HSR_Index_Rank
		ORDER BY KPI ASC
	
	
	
    
    
     
     
		declare
			cursor delete_table
			is
			select table_name from user_tables 
				where upper(table_name)='R_HSR_INDEX_TREND' or upper(table_name)='R_HSR_INDEX_GEO' or upper(table_name)='R_HSR_INDEX_RANK';
			c_row delete_table%rowtype;
			
		begin
			for c_row in delete_table loop
				execute immediate 'drop table '||c_row.table_name||' purge';
			end loop;
		end;
	
    
    

   
   
empty

oracle拼接：java.sql.BatchUpdateException: ORA-00984: column not allowed here + pl/sql 进行插入操作时，报错：ora-00984: 列在此处不允许

 
 注意：^ 是整形    # 替换字符串

java.sql.SQLException: ORA-00917: missing comma

java.sql.SQLException: ORA-01747: user.table.column, table.column 或列说明 + ORA-01747: invalid user.table.column, table.column, or column specification

java.sql.BatchUpdateException: ORA-01438: value larger than specified precision allowed for this column + java.sql.BatchUpdateException: ORA-01438: 值大于为此列指定的允许精度