oracle如何导存储_ORACLE逻辑存储结构

如何导出ORACLE指定存储过程

①.、要想连接到一个数据库,先得配置一个tnsnames.ora文件.路径位于 【XX】/NETWORK/ADMIN/tnsnames.ora.【XX】视具体情况而定.

截图中红框标记的地方需要改成你需要连接的数据库的信息.

SET echo off ;

SET heading ? off ;

SET feedback ? off ;

SPOOL 'C:/EXP/DDL/PRC_JG_JGTZ.SQL' replace

SELECT CASE

? ? ?WHEN LINE = 1 THEN

? ? ? 'CREATE OR REPLACE ' || TEXT

? ? ?WHEN LINE = MAX_LINE THEN

? ? ? TEXT || CHR(10 ) || '/'

? ? ?ELSE

? ? ? TEXT

? ?END

FROM USER_SOURCE A

LEFT JOIN (SELECT A.NAME,A.TYPE, MAX(LINE) MAX_LINE

? ? ? ? ? ?FROM USER_SOURCE A

? ? ? ? ? WHERE TYPE in ( 'PROCEDURE', 'PACKAGE' ,'PACKAGE BODY' )

? ? ? ? ? GROUP BY A.NAME,A.TYPE ) B ON A.NAME||A.TYPE = B.NAME||B.TYPE

WHERE A.TYPE in ('PROCEDURE' , 'PACKAGE', 'PACKAGE BODY' ) AND A.NAME IN

(

'ETL_FACT_JG_SAEINDEX_SCALE'

,'ETL_FACT_JG_SAEINDEX_SCALE_OLD'

,'ETL_DM_JG_CIRC'

)

ORDER BY a.NAME||a.TYPE , LINE;

SPOOL OFF

连接数据库后,输入：@E:\export.sql

如果遇到了截图中出现的问题,请参照文章结尾的【注意事项】.

连接数据库后输入：@C:\exp\ddl\PRC_JG_JGTZ.SQL

注意事项

若提示无法创建spool文件,请确保目录C:/EXP/DDL存在,或者指定新的路径.

Oracle批量导出存储过程（保持每个存储过程独立）

略微复杂,需要存储过程实现.

①.、创建输出路径,比如你要在d盘test目录下输出,你就先在d盘根目录下建立一个test的目录.

create?or?replace?directory?TMP?as?'d:\test';

grant?read,write?on?directory?TMP?to?scott;?--比如我用的scott用户

alter?system?set?utl_file_dir='d:\test'?scope=spfile;

CREATE?OR?REPLACE?PROCEDURE?SP_OUTPUT_PROCEDURE?is

file_handle??utl_file.file_type;

cursor?cur_procedure_name?is

select?distinct?name?from?user_source?where?type?=?'PROCEDURE';

cursor?cur_sp_out?is

select?t.text

from?(select?0?line,?'CREATE?OR?REPLACE?'?text

from?dual

union

select?line,?text

from?user_source

where?type?=?'PROCEDURE'

and?name?=?v_name)?t

order?by?line;

begin

open?cur_procedure_name;

loop

fetch?cur_procedure_name

into?v_name;

exit?when?cur_procedure_name%notfound;

write_file_name?:=?v_name?||?'.txt';

open?cur_sp_out;

fetch?cur_sp_out

into?v_text;

exit?when?cur_sp_out%notfound;

file_handle?:=?utl_file.fopen('TMP',?write_file_name,?'a');

write_content?:=?v_text;

--write?file

IF?utl_file.is_open(file_handle)?THEN

utl_file.put_line(file_handle,?write_content);

END?IF;

--close?file

utl_file.fclose(file_handle);

end?loop;

close?cur_sp_out;

close?cur_procedure_name;

end;

oracle怎样导入数据库

以上就是土嘎嘎小编为大家整理的oracle如何导存储相关主题介绍,如果您觉得小编更新的文章只要能对粉丝们有用,就是我们最大的鼓励和动力,不要忘记讲本站分享给您身边的朋友哦！！