【IT168 服务器学院】Oracle数据库数据对象中最基本的是表和视图，其他还有约束、序列、函数、存储过程、包、触发器等。对数据库的操作可以基本归结为对数据对象的操作,理解和掌握Oracle数据库对象是学习Oracle的捷径。
way 1:
通过spool输出到文件:
|
set serveroutput on
spool ''c:\log.txt''
DECLARE
v_text VARCHAR2(1000);
--i number(3):=0;
CURSOR cur IS
SELECT text
FROM sys.DBA_SOURCE
WHERE OWNER = ''SCOTT''
AND NAME = ''TESTCCB'';
BEGIN
OPEN cur;
LOOP
FETCH cur
INTO v_text;
--DBMS_OUTPUT.PUT_LINE('' cityname = '' ||v_name||'' count=''|| i);
DBMS_OUTPUT.put_line(v_text);
--i := i+ 1;
EXIT WHEN cur%NOTFOUND;
END LOOP;
CLOSE cur;
END;
/
spool off |
way2:
利用 utl_file 包直接写出到文件
要求oracle用户对os文件系统有操作权限
|
DECLARE
v_text VARCHAR2(1000);
v_dir VARCHAR2(256);
v_owner VARCHAR2(128);
v_obj VARCHAR2(128);
l_output utl_file.file_type;
CURSOR cur IS
SELECT text
FROM dba_source
WHERE OWNER = v_owner
AND NAME = v_obj;
BEGIN
v_owner := ''SCOTT'';
v_obj := ''EMP'';
SELECT t.directory_path INTO v_dir FROM all_directories t;
l_output := utl_file.fopen(v_dir, ''tab.txt'', ''w'');
utl_file.new_line(l_output);
utl_file.put_line(l_output,
''-- output owner :'' || v_owner || '' object: '' ||
v_obj);
OPEN cur;
LOOP
FETCH cur
INTO v_text;
EXIT WHEN cur%NOTFOUND;
utl_file.new_line(l_output);
utl_file.put_line(l_output, v_text);
END LOOP;
utl_file.new_line(l_output);
utl_file.put_line(l_output, ''-- output finished! '');
utl_file.fclose(l_output);
CLOSE cur;
END;
/
|
