PL/SQL块

1.plsql块格式
declare
---(定义部分)
begin
---（执行部分是必须的）
exception
--(处理错误部分)
end;
2.简单的程序块
begin
dbms_output.put_line('we are students!');
end;
3.输入变量的程序块
declare
v_ename varchar2(10);
begin
select ename into v_ename from emp
where empno=&no;
dbms_output.put_line('雇员名:'||v_ename);
exception
when no_data_found then
dbms_output.put_line('请输入正确的员工号');
end;
4.匿名块:没有名称的程序块就叫匿名块
declare
v_avgsal number(6,2);
begin
select avg(sal) into v_avgsal from emp 
where deptno=&v_deptno;
dbms_output.put_line(v_avgsal);
end;
5.命名块
<<outer>>
declare
v_deptno number(2);
v_dname varchar2(10);
begin
<<inter>>
begin
select deptno into v_deptno from emp where lower(ename)=lower('&name');
end;
--<<inter>>
select dname into v_dname from dept where deptno=v_deptno;
dbms_output.put_line(' 部门名:'||v_dname);
end;
--<<outer>>
6.子程序(包括过程，函数和包)
(1)函数
create or replace function v_sal(name varchar2)
return number 
is
v_sal number(7,2);
begin
select sal*12+nvl(comm,0) into v_sal 
from test where lower(ename)=lower(name);
return v_sal;
end;
调用函数:
SQL> var income number
SQL> call v_sal('JONES') into :income;