用儲存過程把表裡的資料導成insert語句
SQL> show parameters utl_file_dir;
可以看到該引數的當前設定。
如果沒有值,必須修改資料庫的initsid.ora檔案,將utl_file_dir 指向一個你想用PL/SQL file I/O 的路徑。
重新啟動資料庫。此引數才生效。
呼叫它,可以把表裡的資料生成(insert into 表名 ....)OS下檔案的過程genins_file方法:
SQL>exec genins_file('emp','/oracle/logs','insert_emp.sql');
| | |
表名,可變 | 生成OS下檔名,可變
|
utl_file_dir路徑名,不變(我設定的是/oracle/logs)
儲存過程原碼
CREATE OR REPLACE PROCEDURE genins_file(
p_table IN varchar2,
p_output_folder IN VARCHAR2,
p_output_file IN VARCHAR2)
IS
--
l_column_list VARCHAR2(32767);
l_value_list VARCHAR2(32767);
l_query VARCHAR2(32767);
l_cursor NUMBER;
ignore NUMBER;
l_insertline1 varchar2(32767);
l_insertline2 varchar2(32767);
cmn_file_handle UTL_FILE.file_type;
--
FUNCTION get_cols(p_table VARCHAR2)
RETURN VARCHAR2
IS
l_cols VARCHAR2(32767);
CURSOR l_col_cur(c_table VARCHAR2) IS
SELECT column_name
FROM user_tab_columns
WHERE table_name = upper(c_table)
ORDER BY column_id;
BEGIN
l_cols := null;
FOR rec IN l_col_cur(p_table)
LOOP
l_cols := l_cols || rec.column_name || ',';
END LOOP;
RETURN substr(l_cols,1,length(l_cols)-1);
END;
--
FUNCTION get_query(p_table IN VARCHAR2)
RETURN VARCHAR2
IS
l_query VARCHAR2(32767);
CURSOR l_query_cur(c_table VARCHAR2) IS
SELECT 'decode('||column_name||',null,''null'','||
decode(data_type,'VARCHAR2','''''''''||'||column_name ||'||'''''''''
,'DATE','''''''''||to_char('||column_name||',''YYYY-MM-DD HH24:MI:SS'')||'''''''''
,column_name
) || ')' column_query
FROM user_tab_columns
WHERE table_name = upper(c_table)
ORDER BY column_id;
BEGIN
l_query := 'SELECT ';
FOR rec IN l_query_cur(p_table)
LOOP
l_query := l_query || rec.column_query || '||'',''||';
END LOOP;
l_query := substr(l_query,1,length(l_query)-7);
RETURN l_query || ' FROM ' || p_table;
END;
--
BEGIN
l_column_list := get_cols(p_table);
l_query := get_query(p_table);
l_cursor := dbms_sql.open_cursor;
DBMS_SQL.PARSE(l_cursor, l_query, DBMS_SQL.native);
DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, l_value_list, 32767);
ignore := DBMS_SQL.EXECUTE(l_cursor);
--
IF NOT UTL_FILE.IS_OPEN(cmn_file_handle) THEN
cmn_file_handle := UTL_FILE.FOPEN (p_output_folder, p_output_file, 'a',32767);
END IF;
LOOP
IF DBMS_SQL.FETCH_ROWS(l_cursor)>0 THEN
DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_value_list);
l_insertline1:='INSERT INTO '||p_table||' ('||l_column_list||')';
l_insertline2:=' VALUES ('||l_value_list||');';
UTL_FILE.put_line (cmn_file_handle, l_insertline1);
UTL_FILE.put_line (cmn_file_handle, l_insertline2);
ELSE
EXIT;
END IF;
END LOOP;
IF NOT UTL_FILE.IS_OPEN(cmn_file_handle) THEN
UTL_FILE.FCLOSE (cmn_file_handle);
END IF;
END;
/
可以看到該引數的當前設定。
如果沒有值,必須修改資料庫的initsid.ora檔案,將utl_file_dir 指向一個你想用PL/SQL file I/O 的路徑。
重新啟動資料庫。此引數才生效。
呼叫它,可以把表裡的資料生成(insert into 表名 ....)OS下檔案的過程genins_file方法:
SQL>exec genins_file('emp','/oracle/logs','insert_emp.sql');
| | |
表名,可變 | 生成OS下檔名,可變
|
utl_file_dir路徑名,不變(我設定的是/oracle/logs)
儲存過程原碼
CREATE OR REPLACE PROCEDURE genins_file(
p_table IN varchar2,
p_output_folder IN VARCHAR2,
p_output_file IN VARCHAR2)
IS
--
l_column_list VARCHAR2(32767);
l_value_list VARCHAR2(32767);
l_query VARCHAR2(32767);
l_cursor NUMBER;
ignore NUMBER;
l_insertline1 varchar2(32767);
l_insertline2 varchar2(32767);
cmn_file_handle UTL_FILE.file_type;
--
FUNCTION get_cols(p_table VARCHAR2)
RETURN VARCHAR2
IS
l_cols VARCHAR2(32767);
CURSOR l_col_cur(c_table VARCHAR2) IS
SELECT column_name
FROM user_tab_columns
WHERE table_name = upper(c_table)
ORDER BY column_id;
BEGIN
l_cols := null;
FOR rec IN l_col_cur(p_table)
LOOP
l_cols := l_cols || rec.column_name || ',';
END LOOP;
RETURN substr(l_cols,1,length(l_cols)-1);
END;
--
FUNCTION get_query(p_table IN VARCHAR2)
RETURN VARCHAR2
IS
l_query VARCHAR2(32767);
CURSOR l_query_cur(c_table VARCHAR2) IS
SELECT 'decode('||column_name||',null,''null'','||
decode(data_type,'VARCHAR2','''''''''||'||column_name ||'||'''''''''
,'DATE','''''''''||to_char('||column_name||',''YYYY-MM-DD HH24:MI:SS'')||'''''''''
,column_name
) || ')' column_query
FROM user_tab_columns
WHERE table_name = upper(c_table)
ORDER BY column_id;
BEGIN
l_query := 'SELECT ';
FOR rec IN l_query_cur(p_table)
LOOP
l_query := l_query || rec.column_query || '||'',''||';
END LOOP;
l_query := substr(l_query,1,length(l_query)-7);
RETURN l_query || ' FROM ' || p_table;
END;
--
BEGIN
l_column_list := get_cols(p_table);
l_query := get_query(p_table);
l_cursor := dbms_sql.open_cursor;
DBMS_SQL.PARSE(l_cursor, l_query, DBMS_SQL.native);
DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, l_value_list, 32767);
ignore := DBMS_SQL.EXECUTE(l_cursor);
--
IF NOT UTL_FILE.IS_OPEN(cmn_file_handle) THEN
cmn_file_handle := UTL_FILE.FOPEN (p_output_folder, p_output_file, 'a',32767);
END IF;
LOOP
IF DBMS_SQL.FETCH_ROWS(l_cursor)>0 THEN
DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_value_list);
l_insertline1:='INSERT INTO '||p_table||' ('||l_column_list||')';
l_insertline2:=' VALUES ('||l_value_list||');';
UTL_FILE.put_line (cmn_file_handle, l_insertline1);
UTL_FILE.put_line (cmn_file_handle, l_insertline2);
ELSE
EXIT;
END IF;
END LOOP;
IF NOT UTL_FILE.IS_OPEN(cmn_file_handle) THEN
UTL_FILE.FCLOSE (cmn_file_handle);
END IF;
END;
/
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/7199859/viewspace-343414/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- PostgreSQL獲取建表語句儲存過程SQL儲存過程
- Oracle 儲存過程中的DDL語句Oracle儲存過程
- MySQL儲存過程語句及呼叫MySql儲存過程
- 在ORACLE裡用儲存過程定期分割表(轉)Oracle儲存過程
- 金倉資料庫KingbaseES儲存過程 RETURN語句資料庫儲存過程
- oracle 儲存過程以及plsql語句塊Oracle儲存過程SQL
- Sql Server系列:SQL語句查詢資料庫中表、檢視、儲存過程等組成SQLServer資料庫儲存過程
- EF中使用SQL語句或儲存過程SQL儲存過程
- 刪除資料庫中所有儲存過程和函式的sql語句資料庫儲存過程函式SQL
- 通過SQL語句提取儲存過程中的內容SQL儲存過程
- 利用dbms_profile定位儲存過程或者package裡低效率語句儲存過程Package
- SQL 儲存過程裡呼叫另一個儲存過程SQL儲存過程
- Mysql 資料庫水平分表 儲存過程MySql資料庫儲存過程
- 把一個資料表中的資料匯入另一個表,觸發器和儲存過程的觸發器儲存過程
- PostgreSQL的insert語句執行過程分析SQL
- 把自編儲存過程設定為系統儲存過程儲存過程
- 用儲存過程動態建立表儲存過程
- 直接insert與儲存過程insert效能區別儲存過程
- 儲存過程 與 SQL Server語句大比拼儲存過程SQLServer
- 在.NET中用儲存過程執行SQL語句儲存過程SQL
- Oracle儲存過程乾貨(二):PLSQL控制語句Oracle儲存過程SQL
- mySQL語法中的儲存過程及if語句的使用簡例MySql儲存過程
- 儲存過程語法儲存過程
- asp.net分頁的SQL語句及儲存過程ASP.NETSQL儲存過程
- 批量插入資料的儲存過程儲存過程
- 儲存過程if,for,while語句,陣列和遊標使用儲存過程While陣列
- Oracle儲存過程乾貨(三):PLSQL迴圈語句Oracle儲存過程SQL
- 【SqlServer】清除過期資料的儲存過程SQLServer儲存過程
- MySql資料庫——儲存過程MySql資料庫儲存過程
- 儲存過程返回資料集儲存過程
- MySql 儲存過程 臨時表 無法插入資料MySql儲存過程
- 儲存過程呼叫不同資料庫的資料儲存過程資料庫
- 儲存過程基本語法儲存過程
- MySQL的寫入資料儲存過程MySql儲存過程
- Mysql儲存過程 變數,條件,迴圈語句用法MySql儲存過程變數
- 將表資料生成SQL指令碼的儲存過程和工具SQL指令碼儲存過程
- 【資料庫】資料庫儲存過程(一)資料庫儲存過程
- SQL Server資料庫遠端更新目標表資料的儲存過程SQLServer資料庫儲存過程