PL/SQL:对SQL语言的扩展,可以对SQL进行编程
单行注释以--
多行注释 /* */
,可以跨多行
declare --声明变量、常量、光标、例外 begin --程序 exception --例外 end;
DECLARE 声明部分。
这是为游标,数据类型定义,变量,嵌入函数和过程分配内存的位置。
BEGIN 可执行程序部分 以关键字BEGIN开头 并且必须至少有一行可执行代码,即使它是关键字NULL,这意味着没有操作。
EXCEPTION 异常处理部分 以关键字EXCEPTION开头 捕获任何数据库或PL / SQL错误的地方。
END :每个PL / SQL块以关键字END结束。
DECLARE 声明部分 和 EXCEPTION 异常处理部分 可以省略,省略之后叫 匿名块,匿名blick不保存在数据库中。适合创建测试单元。
例子1:hello world
desc dbms_output; -- 用来打印的程序包 begin dbms_output.put_line('hello world'); end;
输出 : PL/SQL 过程已成功完成 , 未打印 'hello world'
因为 打印服务默认是关闭的,需要打开 set serveroutput on ;
输出 :
PL/SQL 过程已成功完成。
hello world
psex char := '男'; pname varchar2(10);
变量名 为了 与其他 关键字重名,前面加个p (pl/sql简称p)
set serveroutput on ; declare psex char(3) := '男'; -- char默认1个字符 ,但汉字 占3个字节(不同字符编码字节数不同) pname varchar2(10); begin select ename into pname from emp where empno = 7499; dbms_output.put_line('hello world' || psex || '--------' || pname); end;
这里 pname 我们其实是要 对应 emp表中的 ename,所以二者类型最好保持一致 (长度),所以不能写死
pname emp.ename%type; -- 引用表中字段的类型,而且也推荐这么干
可以用于同时保存多个变量值
Person per new Person(1,'zs',28,11...,...); emp_info emp%rowtype; -- 将表映射成对象,而且 表中的所有字段全部自动映射到对象的属性中
调用 直接通过 ' . ' 调用 和java调用属性 一摸一样
declare emp_info emp%rowtype; begin select * into emp_info from emp where empno = 7788; dbms_output.put_line(emp_info.empno || '----------' || emp_info.ename || '----------' || emp_info.job); end;
①if if 条件 then ... end if ; ②if..else if 条件 then ...; else ... end if ; ③if .. elsif..else... if 条件 then ...; elsif 条件 then... else... end if ;
示例:
declare num number :=1 ; begin if num=1 then dbms_output.put_line('输入了1'); elsif num=2 then dbms_output.put_line('输入了2'); else dbms_output.put_line('输入了其他'); end if ; end;
① 相当于java的while循环 while 条件 loop … end loop; ②相当于java的do..while循环 loop … exit when 条件; end loop; ③相当于java的for循环 for i in 1 .. 3 loop … end loop;
示例:
set serveroutput on; declare begin for x in 1..5 loop dbms_output.put_line(x); end loop; end;
示例:求1到5之和
java代码 int sum = 0; int i=1; do{ sum +=i; i++; }while(i<=5), pl/sql set serveroutput on ; declare pnum number := 1; psum number :=0; begin loop exit when pnum >5; psum := psum + pnum; -- sum+=i; pnum := pnum +1; end loop ; dbms_output.put_line (psum); end;
前面都是 存储一行数据 或 存储多行数据,那存储多行数据怎么办
pename varchar2(20) ; select ename into pename from emp ; --错误
存储多行数据-->光标
语法:
cursor 光标名 (参数列表) is select …
示例:
用光标存储集合
cursor mycursor is select ename from emp ;
使用:
打开光标 open mycurosr
光标使用前必须打开
获取一行光标的值 fetch mycursor into pename ;
-- (其中pename类型是emp.ename%type)
关闭光标 close mycursor ;
光标属性
%isopen -- 判断光标是否打开
%rowcount -- 已从光标中读取的记录数
%found -- 判断这行是否有数据
%notfound -- 判断这行是否没有数据
示例:查询并打印员工姓名、薪水
set serveroutput on; declare --定义光标 cursor cemp is select ename,sal from emp; pename emp.ename%type; psal emp.sal%type; begin open cemp; loop fetch cemp into pename,psal; -- 获取当前指向的记录,并将指针下移 dbms_output.put_line(pename || '的薪水是' || psal); --退出条件 --exit when 没有数据; exit when cemp%notfound; end loop; close cemp; end;
示例:涨工资 job 为PRESIDENT涨1000 job为MANAGER涨800 其他400
set serveroutput on declare cursor cemp is select empno, job from emp; pempno emp.empno%type; pjob emp.job %type; begin open cemp; loop --取一条记录 fetch cemp into pempno,pjob; --判断职位 if pjob = 'PRESIDENT' then update emp set sal=sal+1000 where empno=pempno; elsif pjob = 'MANAGER' then update emp set sal=sal+800 where empno=pempno; else update emp set sal=sal+400 where empno=pempno; end if; exit when cemp%notfound; end loop; close cemp; commit; -- oracle需要手动提交 --> ACID(Oracle默认级别是 Read Commit,一边不提交、另一边读不到) dbms_output.put_line('完成'); end;
示例:带参数的光标 查询某个部门的员工姓名
set serveroutput on declare --定义光标保存某个部门的员工姓名 cursor cemp(dno number) is select ename from emp where deptno=dno; pename emp.ename%type; begin open cemp(20); loop fetch cemp into pename; dbms_output.put_line(pename); exit when cemp%notfound; end loop; close cemp; end;
No_data_found:没有找到数据
Too_many_rows:数据太多了,保存不下 select..into 匹配多个行,如 select ename into pename from emp;
Zero_Divide:被零除
Value_error:算术或转换错误
Timeout_on_resource:等待资源超时 (分布式数据库)
示例:被0除
set serveroutput on declare pnum number; begin pnum := 1/0; exception when zero_divide then dbms_output.put_line('1:0不能做被除数'); dbms_output.put_line('2:0不能做被除数'); when value_error then dbms_output.put_line('算术或者转换错误'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('其他例外'); end;
b.自定义例外
declare 例外名 exception ; -- 定义异常 begin if … then raise 例外名 ; -- 抛出异常 end if; exception when 例外名 then DBMS_OUTPUT.PUT_LINE('…'); -- 捕获异常 WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('其他例外'); end;
declare myexc exception; -- 定义异常 pnum number := 1; begin if pnum = 1 then raise myexc; -- 抛出异常 end if; exception when myexc then dbms_output.put_line('自定义例外...'); -- 捕获异常 end;
示例:是否存在50号部门的员工?如果不存在,抛出一个例外。
set serveroutput on; declare cursor cemp is select ename from emp where deptno=50; pename emp.ename%type; no_emp_found exception; --自定义例外 begin open cemp; fetch cemp into pename; --从光标取一条记录 if cemp%notfound then raise no_emp_found; --抛出例外 end if; close cemp; exception when no_emp_found then dbms_output.put_line('没有找到员工'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('其他例外'); end;