selectinto動態sql

『壹』 sql如何把查詢出來的多個表創建成一個臨時表

SELECT * INTO #TEMPTABLENAME

FROM

(

SELECT xxxxxx //你的查詢語句

)AS table_source //這個別名是必須的

WHERE xxxxxxxx //你需要的where判斷;

COMMIT或ROLLBACK後可自動刪除該臨時表

1、sql server使用select into會自動生成臨時表，不需要事先創建。

select * into #temp from sysobjects

2、sql要把多個表合並成一個要用到union或union all的關鍵字。

3、union或union all的區別是：union會自動壓縮多個結果集合中的重復結果，而union all則將所有的結果全部顯示出來。

(1)selectinto動態sql擴展閱讀

sql語言特點如下：

1、一體化：SQL集數據定義DDL、數據操縱DML和數據控制DCL於一體，可以完成資料庫中的全部工作。

2、使用方式靈活：它具有兩種使用方式，即可以直接以命令方式交互使用；也可以嵌入使用，嵌入到C、C++、FORTRAN、COBOL、JAVA等主語言中使用。

3、語言簡潔，語法簡單，好學好用：在ANSI標准中，只包含了94個英文單詞，核心功能只用6個動詞，語法接近英語口語。

『貳』 oracle筆記-動態SQL

孫告第 章 動態SQL

為何使用動態SQL

實現動態SQL有兩種方式 DBMS_SQL和本地動態SQL（EXECUTE IMMEIDATE）

主要從以下方面考慮使用哪種方式

是否知道涉及的列數和類型

DBMS_SQL包括了一個可以 描述 結果集的存儲過程（DBMS_SQL DESCRIBE_COLUMNS） 而本地動態SQL沒有

是否知道可能涉及的綁定變數數和類型

DBMS_SQL允許過程化的綁定語句的輸入 而本地動態SQL需要在編譯時確定

是否使用 數組化 操作（Array Processing）

DBMS_SQL允許 而本地動態SQL基本不可以 但可以用其他方式實現（對查詢可用FETCH BULK COLLECT INTO 對INSERT等 可用一個BEGIN … END塊中加循環實現）

是否在同一個會話中多次執行同一語句

DBMS_SQL可以分析一次執行多次 而本地動態SQL會在每次執行時進行軟分析

是否需要用REF CURSOR返回結果集

僅本地動態SQL可用REF CURSOR返回結果集

如何使用動態SQL

DBMS_SQL

調用OPEN_CURSOR獲得一個游標句柄

調用PARSE分析語句 一個游標句柄可以用於多條不同的已分析語句 但一個時間點僅一條有效

調用BIND_VARIABLE或BIND_ARRAY來提供語句的任何輸入

若是一個查詢（SELECT語句） 調用DIFINE_COLUMN或DEFINE_ARRAY來告知賣凱掘Oracle如何返回結果

調用EXECUTE執行語句

若是一個查中核詢 調用FETCH_ROWS來讀取數據 可以使用COLUMN_VALUE從SELECT列表根據位置獲得這些值

否則 若是一個PL/SQL塊或帶有RETURN子句的DML語句 可以調用VARIABLE_VALUE從塊中根據變數名獲得OUT值

調用CLOSE_CURSOR

注意這里對任何異常都應該處理 以關閉游標 防止泄露資源

本地動態SQL

EXECUTE IMMEDIATE 語句

[INTO {變數 變數 … 變數N | 記錄體}]

[USING [IN | OUT | IN OUT] 綁定變數 … 綁定變數N]

[{RETURNING | RETURN} INTO 輸出 [ … 輸出N]…]

注意本地動態SQL僅支持弱類型REF CURSOR 即對於REF CURSOR 不支持BULK COLLECT

最後說明

『叄』 怎麼在存儲過程中動態加入索引

create or replace procere sp_crt_stg_index( p_src_tbl_name varchar2,p_tgt_tbl_name varchar2,p_tx_date varchar2)
is
v_crt_ind_sql varchar2(4000) := '';
v_drop_ind_sql varchar2(4000) := '';
v_ind_name varchar2(100) := '';
v_tgt_ind_name varchar2(100) := '';
v_col_list varchar2(1000) := '';
v_col_name varchar2(100) := '';
i_ind_col_cnt number(2,0) := 0;
i_tbl_cnt number(2,0) := 0;
i_ind_cnt number(2,0) := 0;
v_step_no varchar2(3) :='';
begin
--step 1 檢查索引定義是否存在
v_step_no :='1';
select count(1) into i_ind_col_cnt from all_ind_columns
where table_name = p_src_tbl_name;
if(i_ind_col_cnt = 0 ) then
sp_log('sp_crt_stg_index',v_step_no,'3',p_tx_date,p_src_tbl_name||'不存在索引定義');
return;
end if;
--step 2 檢查目標表是否存在
v_step_no :='2';
select count(1) into i_tbl_cnt from tabs
where table_name = p_tgt_tbl_name;
if(i_tbl_cnt = 0 ) then
sp_log('sp_crt_stg_index',v_step_no,'3',p_tx_date,p_src_tbl_name||'不存在');
return;
end if;
--step 3 創建索引
v_step_no :='3';
for cur_ind in (select index_name from all_indexes where table_name = p_src_tbl_name) loop
v_ind_name := cur_ind.index_name;
v_col_name := '';
v_col_list := '(';
for cur_ind_col in (select column_name from all_ind_columns where index_name = v_ind_name order by column_position) loop
v_col_name := cur_ind_col.column_name;
v_col_list := v_col_list||v_col_name||',';
end loop;
v_col_list := v_col_list||')';
v_col_list := replace(v_col_list,',)',')');
v_tgt_ind_name :=replace(v_ind_name,p_src_tbl_name,p_tgt_tbl_name);
v_tgt_ind_name := v_tgt_ind_name||substr(p_tx_date,7,21);
select count(1) into i_ind_cnt from all_indexes where index_name = v_tgt_ind_name;
if(i_ind_cnt > 0) then
v_drop_ind_sql := 'drop index '||v_tgt_ind_name;
execute immediate v_drop_ind_sql;
end if;
sp_log('sp_crt_stg_index',v_step_no,'1',p_tx_date,v_tgt_ind_name||'創建開始');
v_crt_ind_sql := 'create index '||v_tgt_ind_name||' on '||p_tgt_tbl_name||v_col_list||' nologging';
if(v_crt_ind_sql is not null) then
execute immediate v_crt_ind_sql;
end if;
sp_log('sp_crt_stg_index',v_step_no,'1',p_tx_date,v_tgt_ind_name||'創建結束');
end loop;
exception
when others then
sp_log('sp_crt_stg_index',v_step_no,'3',p_tx_date,v_tgt_ind_name||'創建異常:'||SQLERRM);
end;

#########################

---- 日期類型轉換
to_char(sysdate,'YYYY-MM-DD HH24:MI:SS');
----Oracle檢查分區
select count(1)
from ALL_TAB_PARTITIONS
where table_name = p_tgt_tbl_name
and PARTITION_NAME = v_prt_name;
----碼表
Select * From ict_s_dic Where opttype = 'ID_TYPE';
decode 內部欄位拼接
decode(Date_Nextvisit,'','下次跟進時間：['||Date_Nextvisit||']') RMK1,

----拼接欄位,刪除表
SELECT 'DROP TABLE '||TABLE_NAME||';' FROM TABS WHERE TABLE_NAME LIKE 'M_ICT%';
Select * From tabs ;
----交換分區，分區交換後數據是否交換？
'ALTER TABLE '|| p_tgt_tbl_name ||' EXCHANGE PARTITION '||v_prt_name||' WITH TABLE '||p_src_tbl_name;
----查詢某表是否存在
select count(1) from tabs
where table_name = p_src_tbl_name;
----重建索引
ALTER index ind_id_idx rebuild
----創建索引nologging
create index I_ICT_CUST_INFO__0 on ICT_CUST_INFO_20141222 (CUST_NO)
nologging;
----查詢分區內數據
select count(1) from stg_ict_trade_info partition(ICT_PRT_2014005);
Select * From User_Ind_Partitions;
Select * From User_Part_Indexes;

-----查詢索引
select index_name from ALL_INDEXES WHERE TABLE_NAME=p_src_tbl_name;
-----查詢分區
select * from ALL_TAB_PARTITIONS
where table_name = p_tgt_tbl_name and PARTITION_NAME = v_prt_name;
-----查詢表名
select * from tabs where table_name = p_tgt_tbl_name;
-----清空表分區數據
'ALTER TABLE '||p_tgt_tbl_name||' TRUNCATE PARTITION ' || v_prt_name;
-----增加表分區
'ALTER TABLE '||p_tgt_tbl_name||' ADD PARTITION ' || v_prt_name||' VALUES LESS THAN (''' ||v_monthend||''') TABLESPACE ICLIENT_O_DATA01 ';
-----重建索引
'ALTER INDEX '||cur_ind.index_name|| ' REBUILD PARALLEL 128 COMPUTE STATISTICS NOLOGGING';
-----交換分區
'ALTER TABLE '|| p_tgt_tbl_name ||' EXCHANGE PARTITION '||v_prt_name||' WITH TABLE '||p_src_tbl_name||' INCLUDING INDEXES';

-----oralce創建同義詞------------

create or replace public SYNONYM ICT_ORG_BPH for iclientodata.ICT_ORG_BPH;

-----oracle 賦許可權
grant select, insert, update, delete on ICT_ORG_BPH to ICLIENTOOPR; --賦許可權

---------------DBA查看錶空間------------
select a.tablespace_name,
a.bytes / 1024 / 1024 / 1024 "Sum G",
(a.bytes - b.bytes) / 1024 / 1024 / 1024 "used G",
b.bytes / 1024 / 1024 / 1024 "free G",
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used"
from (select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes, max(bytes) largest
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by ((a.bytes - b.bytes) / a.bytes) desc

-------------------------------------------------------------------------------------
--oracle 生成刪表語句，
SELECT 'DROP TABLE '||TABLE_NAME||';' FROM TABS WHERE TABLE_NAME LIKE 'M_ICT%'
--查詢oracle 中ICT開頭的
SELECT * FROM tabs WHERE TABLE_NAME LIKE 'ICT%'

----oracle 表分析語句
'ANALYZE TABLE ' || v_ana_tbl_name || ' estimate system statistics';

例如：ANALYZE TABLE ICT_SUM_AST_DBT_CUST ESTIMATE SYSTEM STATISTICS;
-----------查看鎖定對象及會話
SELECT OBJECT_NAME,MACHINE,S.SID,S.SERIAL#
FROM GV$LOCKED_OBJECT I,DBA_OBJECTS O,GV$SESSION S
WHERE I.OBJECT_ID=O.OBJECT_ID AND I.SESSION_ID=S.SID;
----------oralce解鎖
ALTER SYSTEM KILL SESSION '280,219';

授權腳本生成方法：
select 'grant select on table dmccrm.'||tbl_name||' to public,ex_sdbods; ' from t_ict_tbl_type where tbl_type in('ALL','GP') and ETL_DIR IN( 'GP->ORACLE','無需同步');

-----------oracle批量生成同義詞
SELECT 'create or replace public synonym '||table_name ||' for iclientodata.'||table_name FROM tabs WHERE table_name like 'ICT_%'

-----------oracle批量生成修改表欄位的長度
select 'ALTER TABLE '||TABLE_NAME||' MODIFY '||COLUMN_NAME||' NUMBER(30,8);' from cols t
where t.DATA_TYPE = 'NUMBER'
AND T.DATA_SCALE >0 AND SUBSTR(TABLE_NAME,-8,8)<>'20140531'

---------oracle 批量創建同義詞
select 'create or replace public synonym '||table_name||' for iclientodata.'||table_name||';' from user_tables WHERE table_name LIKE 'ICT_%' AND table_name NOT LIKE '%20140531';

-----------oracle 交換分區語句
ALTER TABLE ICT_CUST_LEVEL_HIS ADD PARTITION ICT_PRT_20140531 VALUES LESS THAN ('2014-06-01')
--------MERGE使用方法
MERGE INTO ICT_CUST_INFO_ALL a
USING
(
SELECT t.CUST_MNG_UM_NO ,t.CUST_NO
FROM ict_cust_mnger_rel t
WHERE t.CUST_NO IN
('600021562650', '600037441214', '600036874754', '600038507516',
'600020226746', '600038089420', '600041030403', '600038952992',
'600039468303')
)b
ON( a.cust_no= b.cust_no)
WHEN MATCHED THEN
UPDATE SET a.MAX_ASSET_INTRO_NO=b.CUST_MNG_UM_NO;

-------------oracle 樹形查詢，查詢機構編號為『9902』的和其下級子機構
select org_id,org_name,org_level from (
SELECT rownum rn, ioi.org_id , ioi.org_name,ioi.org_level
FROM ict_org_info ioi
START WITH ioi.org_id ='9902'
CONNECT BY PRIOR ioi.org_id = ioi.parent_org_id_b
order by ioi.org_level desc
) where rn=1

-------------oracle 樹形查詢，查詢機構編號為『9902』的和上級機構

select org_id,org_name,org_level from (
SELECT rownum rn, ioi.org_id , ioi.org_name,ioi.org_level
FROM ict_org_info ioi
START WITH ioi.org_id ='9902'
CONNECT BY ioi.org_id = PRIOR ioi.parent_org_id_b
order by ioi.org_level desc
) where rn=1

-------------oracle 樹形查詢，查詢機構編號為『9902』的上級機構

select org_id,org_name,org_level from (
SELECT rownum rn, ioi.org_id , ioi.org_name,ioi.org_level
FROM ict_org_info ioi
START WITH ioi.org_id ='9902'
CONNECT BY ioi.org_id = PRIOR ioi.parent_org_id_b
order by ioi.org_level desc
) where rn=1

----------查看錶名與表空間
Select * From user_tables Where table_name = 'ICT_RMT_APPO';
Select * From User_Tablespaces;

----------查詢SQL 預估時間
SELECT SE.SID,
OPNAME,
TRUNC(SOFAR / TOTALWORK * 100, 2) || '%' AS PCT_WORK,
ELAPSED_SECONDS ELAPSED,
ROUND(ELAPSED_SECONDS * (TOTALWORK - SOFAR) / SOFAR) REMAIN_TIME,
SQL_TEXT
FROM V$SESSION_LONGOPS SL, V$SQLAREA SA, V$SESSION SE
WHERE SL.SQL_HASH_VALUE = SA.HASH_VALUE
AND SL.SID = SE.SID
AND SOFAR != TOTALWORK
ORDER BY START_TIME;
------TYPE opty_cur IS REF CURSOR;
整體的意思是「創建一個類型變數cur，它引用游標」，除了cur外，其餘全是關鍵字。
TYPE cur：定義類型變數 ,is ref cursor：相當於數據類型，不過是引用游標的數據類型。
這種變數通常用於存儲過程和函數返回結果集時使用，
因為PL/SQL不允許存儲過程或函數直接返回結果集，
但可以返回類型變數，於是引用游標的類型變數作為輸出參數或返回值就應運而生了。
----查殺進程
SELECT dob.OBJECT_NAME Table_Name,
lo.LOCKED_MODE,
lo.SESSION_ID,
vss.SERIAL#,
vps.spid,
vss.action Action,
vss.osuser OSUSER,
vss.process AP_PID,
VPS.SPID DB_PID,
'alter system kill session ' || '''' || lo.SESSION_ID || ',' ||
vss.SERIAL# || ''';' kill_command
from v$locked_object lo, dba_objects dob, v$session vss, V$PROCESS VPS
where lo.OBJECT_ID = dob.OBJECT_ID
and lo.SESSION_ID = vss.SID
AND VSS.paddr = VPS.addr
order by 2, 3, DOB.object_name;

『肆』 執行動態SQL語句的函數怎麼寫

執行動態SQL語句的函數怎麼寫
其實你這你都知道用函數實現不了的,因為你的輸入參數@SQL是動態的,那必須用exec執行,而函數里不能用exec。建議你用存儲過程實現，示例如下：

創建存儲過程：
create procere TEST
(
@SQL NVARCHAR(200),
@RE INT output
)
AS
BEGIN
set nocount on
if exists (select * from tempdb.dbo.sysobjects where xtype='U' and id=object_id('#test'))
drop table #test

create table #test(total int)
insert into #test
exec (@SQL)
select @RE=isnull(total,0) from #test
set nocount off
END

調用示例：
declare @RE int
exec TEST 'select 1+1',@RE output
PRINT @RE

結果：
2