oracle PL/SQL程序设计
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' then 102 update testemp set sal=sal+1000 where empno=pempno; 103 elsif pjob='MANAGER' then 104 update testemp set sal=sal+800 where empno=pempno; 105 else 106 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 begin 125 open cemp(&num); 126 loop 127 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 begin 139 num:=16/0; 140 EXCEPTION 141 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 loop 154 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;
