declare
说明部分 （变量说明，光标申明，例外说明 〕
begin
语句序列 （DML语句〕…
exception
例外处理语句
End;
/
if语句
循环语句
光标(Cursor)==ResultSet
l说明光标语法：
CURSOR 光标名 [ (参数名 数据类型[,参数名 数据类型]...)]
IS SELECT 语句;
l用于存储一个查询返回的多行数据
l打开光标： open c1; (打开光标执行查询)
l取一行光标的值：fetch c1 into pjob; (取一行到变量中)
l关闭光标： close c1;(关闭游标释放资源)
系统定义例外
•No_data_found (没有找到数据)
•Too_many_rows (select …into语句匹配多个行)
•Zero_Divide ( 被零除)
•Value_error (算术或转换错误)
•Timeout_on_resource (在等待资源时发生超时)
1 select * from emp; 2 3 select * from tab; 4 5 set serveroutput on 6 declare 7 begin 8 SYS.DBMS_OUTPUT.PUT_LINE('Hello world'); 9 end; 10 / 11 12 select * from emp; 13 14 15 16 set serveroutput on; 17 declare 18 pename EMP.ENAME%type; 19 psal EMP.SAL%type; 20 begin 21 select ename,sal into pename,psal from emp where EMPNO=7369; 22 DBMS_OUTPUT.put_line(pename||'***'||psal); 23 end; 24 25 26 set serveroutput on; 27 declare 28 emp_rec emp%rowtype; 29 begin 30 select * into emp_rec from emp where empno=7369; 31 SYS.DBMS_OUTPUT.PUT_LINE(emp_rec.ename || '的薪水'||emp_rec.sal); 32 end; 33 / 34 35 36 37 38 39 set serveroutput on 40 accept num prompt '请输入一个数字'; 41 declare 42 pnum number := # 43 begin 44 if pnum = 0 then 45 dbms_output.put_line('输入的数字是'||pnum); 46 elsif pnum = 1 then 47 dbms_output.put_line('输入的数字是'|| pnum); 48 else 49 dbms_output.put_line('输入的是其他数字'); 50 end if; 51 end; 52 / 53 54 set serveroutput on 55 accept num prompt '请输入数字'; 56 declare 57 num number :=1; 58 begin 59 loop 60 EXIT WHEN num># 61 DBMS_OUTPUT.PUT_LINE(num); 62 num :=num+1; 63 end loop; 64 end; 65 66 67 68 set serveroutput on; 69 declare 70 CURSOR cemp is select ename,sal from emp; 71 pename EMP.ENAME%type; 72 psal EMP.SAL%type; 73 begin 74 open cemp; 75 loop 76 FETCH cemp into pename,psal; 77 exit when cemp%notfound; 78 DBMS_OUTPUT.PUT_LINE(pename||'的工资是'||psal); 79 end loop; 80 close cemp; 81 end; 82 83 84 create table testemp as 85 select * from emp; 86 select * from testemp; 87 88 89 90 91 set serveroutput on; 92 declare 93 cursor cemp is select empno,tjob from testemp; 94 pempno testemp.EMPNO%type; 95 pjob testemp.tjob%type; 96 begin 97 open cemp; 98 loop 99 fetch cemp into pempno,pjob;100 exit when cemp%notfound;101 if pjob='PRESIDENT' then102 update testemp set sal=sal+1000 where empno=pempno;103 elsif pjob='MANAGER' then104 update testemp set sal=sal+800 where empno=pempno;105 else106 update testemp set sal=sal+400 where empno=pempno;107 end if;108 end loop;109 close cemp;110 end;111 112 select * from TESTEMP;113 114 rollback;115 116 117 118 set serveroutput on;119 accept num prompt '输入部门号';120 declare 121 cursor cemp(dtno testemp.deptno%type) is select ename,deptno from testemp where deptno=dtno;122 pename testemp.ename%type;123 pdeptno testemp.deptno%type;124 begin125 open cemp(&num);126 loop127 fetch cemp into pename,pdeptno;128 exit when cemp%notfound;129 DBMS_OUTPUT.PUT_LINE(pename || '******' || pdeptno);130 end loop;131 close cemp;132 DBMS_OUTPUT.put_line('success');133 end;134 135 set serveroutput on;136 declare 137 num number;138 begin139 num:=16/0;140 EXCEPTION141 when Zero_Divide then DBMS_OUTPUT.PUT_LINE('除数不能为0');142 when others then 143 DBMS_OUTPUT.PUT_LINE('其他例外');144 end; 145 146 set serveroutput on;147 declare 148 cursor cemp is select ename from emp where deptno=60;149 pename emp.ename%type;150 no_dept_exception exception;151 begin 152 open cemp;153 loop154 fetch cemp into pename;155 if cemp%notfound then 156 raise no_dept_exception;157 end if;158 end loop;159 exception 160 when no_dept_exception then 161 DBMS_OUTPUT.PUT_LINE('没有相关部门');162 when others then 163 DBMS_OUTPUT.PUT_LINE('其他例外');164 close cemp;165 end;