1.使用包
set serveroutput on
begin
--使用包dbms_output
dbms_output.put_line('hello world');
end;
/

2. 变量赋值
declare
     v_ename varchar2(5); 
     v_sal number(7,2);
begin
     select ename,sal into v_ename, v_sal from emp where empno=&no; --注意赋值的顺序， 还有&表示要从控制台输入变量
     dbms_output.put_line('雇员名:‘||v_ename); --字符串拼接
end;
/
into v_name: 查出来赋予变量值
||：字符串拼接
&：表示要从控制台输入变量

3. 异常处理
declare
         v_ename varchar2(5);
         v_sal number(7,2);
begin
         select ename,sal into v_ename, v_sal from emp where empno=&no;
         dbms_output.put_line('雇员名:‘||v_ename);
exception
when no_data_found then
dbms_output.put_line('The input number is wrong');
end;
/

4. 传递参数
create procedure sp_pro3(spname varchar2, newSal number) is
begin
update emp set sal=newsal where ename=spName;
end;
/
执行：exec sp_proc3('scott', 3567);

5. java程序调用存储过程
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManage.getConnection("jdbc:oracle:thin@127.0.0.1:1521:wilson","scott","tiger");
//通过连接，取得存储过程
CallableStatement cs = conn.prepareCall("{call sp_pro3(?,?)}");
//对里面的内容赋值
cs.setString(1，"smith")；
cs.setInt(2,1000);
//执行存储过程
cs.execute();
//释放资源
cs.close();
conn.close();
 
6. 函数
create function sp_fun1(spName varchar) return is number yearSal number(7,2);
begin
select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=spName;
return yearSal;
end;
/
在sqlplus中调用函数：
var income number
call sp_fun1('scott') into income ;

7. 包
--创建包sp_package
create package sp_package is
     procedure update_sal(name varchar2, newsal number);
     function annual_income(name varchar2) return number);
end;
/

把包里面的存储过程和函数实现：
create or replace package body sp_package is
procedure update_sal(name varchar2, newsal number) is
begin
    update emp set sal=newsal where ename=name;
end;

function annual_income(name varchar2)
return number is annual_salary number;
begin
    select sal*12+nvl(comm,0) into annual_salary from emp where ename=name;
return annual_salary'
end;

end;

使用包中的函数和存储过程
exec sp_package.update_sal('scott',120);