--包含定义,执行和异常处理的pl/sql部分 declare v_ename emp.ename%type; 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; / --pl/sql块分类 1:匿名块 declare v_avgsal emp.sal%type; begin select avg(sal) into v_avgsal from emp where deptno=&deptno; dbms_output.put_line('平均工资:'||v_avgsal); end; 2:命名块 <<outer1>> declare v_deptno emp.deptno%type; v_job emp.job%type; begin <<inner1>> begin select deptno into v_deptno from emp where lower(ename) = lower('&name'); end;-- <<inner1> select job into v_job from emp where deptno=v_deptno; dbms_output.put_line('岗位:'||v_job); end;-- <<outer1>> select * from emp --procudure 用于执行特定的操作 create or replace procedure update_sal(name varchar2, newsal number) is begin update emp set sal=newsal where lower(ename)=lower(name); dbms_output.put_line('用户名'||name); dbms_output.put_line('薪水'||newsal); end update_sal; / begin update_sal('scott','2000'); end; --function 用于返回特定的数据 create or replace function annual_income ( name varchar2 ) return number is annual_salary integer; begin select sal*12+nvl(common,0) into annual_salary from emp where lower(ename) = lower(name); return annual_salary; DBMS_OUTPUT.put_line('年收入'||annual_salary); end; select annual_income('bob') 年收入 from dual; --package --package normal create package emp_pkg is procedure update_sal(name varchar2,newsal number); function annual_income(name varchar2) return number; end emp_pkg; / --package body create or replace package body emp_pkg is procedure update_sal(name varchar2,newsal number) is begin update emp set sal=newsal where lower(ename) = lower(name); dbms_output.put_line('用户名'||name); dbms_output.put_line('薪水'||newsal); end; function annual_income(name varchar2) return number is annual_salary int; begin select sal*12+nvl(common,0) into annual_salary from emp where lower(ename) = lower(name); return annual_salary; end; end; / exec emp_pkg.update_sal('scott',1200) select emp_pkg.annual_income('scott') 年收入 from dual;