Oracle的基础用法详细实例（二）

Oracle的基础用法详细实例（二）

create or replace package pkg01
is
procedure sum(a number,b number,r out number);

—函数
function fun(eno number) return varchar2;

—-游标
cursor cur return emp%rowtype;

—显示游标数据的存储过程
procedure showEmp;

end pkg01;

create or replace package body pkg01
is
—指定游标结果集
cursor cur return emp%rowtype
is
select * from emp;

procedure sum(a number,b number,r out number)
is
begin
r:=a+b;
end sum;

function fun(eno number)
return varchar2
is
en emp.ename%type;
begin
select ename into en from emp
where empno=eno;
return en;
end fun;

procedure showEmp
is
begin
for e in cur
loop
dbms_output.put_line(e.ename||’,’||e.sal);
end loop;
end showEmp;

end pkg01;

按用戶的方式备份和恢复数据
exp scott/tiger@orcl file=scott_back owner=scott

imp scott/tiger@orcl file=scott_back.dmp ignore=y full=y

按表空间的方式备份和恢复数据
exp system/manage@olcp tablespaces=(users) file=tbs_users

imp
按表的方式备份数据
exp scott/tiger@orcl tables=(emp, dept) file=scott_back_tab

按表的方式导入数据
imp scott/tiger@orcl tables=(emp, dept) file=scott_back_tab

将scott用户的表导入到zzy用户
imp
userid=”\”sys/559955 as sysdba”\”
file=’f:\scott_bak_tab.dmp’
tables=(emp,dept)
fromuser=scott
touser=system
ignore=y;