Oracle 笔记（四）

PLSQL编程【语法、plsql控制语句、异常、游标、触发器、存储过程】

1、 PLSQL的语法–块编程

a) 概念：procedural language struct query language

b) PLSQL书写语法

declare

--声明块

begin

--执行块

exception—异常块

when XXX异常 then—捕获异常的语法

--异常处理

end;

c) 变量的定义

变量名[constant]数据类型 := 初始值；

d) 数据类型

Oracle数据类型:integer,number ,varchar2,char……

--声明一个变量，在变量中存储一个姓名“王健”，并打印“欢迎王健同学来上课”

declare

v_name varchar2(10);

begin

v_name :='王健';

dbms_output.put_line('欢迎'||v_name||'同学来上课！');

end;

会话输出打印开关

set serveroutput on;

--声明一个变量，在变量中存储一个姓名“王健”，并打印“欢迎王健同学来上课”

declare

v_name varchar2(10):='王健' ;

v_age number(3):=18;

begin

--给变量v_name赋值

v_name := '&学生姓名';

v_age := &年龄;

dbms_output.put_line('欢迎'||v_name||v_age||'同学来上课！');

end;

e) 伪类型

1) 列伪类型emp.ename%type

2) 行伪类型emp%rowtype

--编写一段plsql块，完成功能把scott用户的姓名存储到v_ename变量中，工资存储到v_sal变量中并打印变量存储的值

declare

v_ename emp.ename%type; --列伪类型

v_sal emp.sal%type;

begin

select ename,sal into v_ename,v_sal from emp where ename='SCOTT';

dbms_output.put('员工的姓名：'||v_ename);

dbms_output.put_line(',员工的工资：'||v_sal);

end;

--编写一段plsql块，完成功能把scott用户的所有信息存储并打印出来

declare

v_emprow emp%rowtype;--行伪类型

begin

select*into v_emprow from emp where ename ='SCOTT'; dbms_output.put_line(v_emprow.empno||v_emprow.ename||v_emprow.job);

end;

f) 变量赋值有几种方式？

1.:=[初始化、语句赋值]

2.select 字段名 into 变量名 from 表

3.替代变量&变量描述

2、 PLSQL编程控制语句语法–编程思想

a) if条件分支

if 条件 then

--操作语句

elsif条件 then

--操作语句

else

--操作语句

end if;

b) case多分支

case 比较变量

when 比较值 then

--操作语句

when 比较值 then

--操作语句

when 比较值 then

--操作语句

when 比较值 then

--操作语句

……

else

--缺省值

end case；

c) 循环之while循环

While 循环条件

Loop

--执行语句

end loop;

d) 循环之dowhile循环 loop循环

loop

--执行语句

Exit when 退出条件

end loop；

e) 循环之for循环

For 循环变量 in 开始值..结束值

Loop

end loop;

f) Goto :问题-多重循环跳出问题【循环不能超过三层，垃圾代码】

一、条件分支语句

a) 简单的条件判断 if- then

--编写一个块，可以输入一个雇员名，如果该雇员的工资低于2000，就给该雇员工资增加10%

declare

v_ename emp.ename%type;

v_sal emp.sal%type;

begin

v_ename :='&雇员姓名';

select sal into v_sal from emp_bak where ename=v_ename;

if v_sal<2000then

update emp_bak set sal= sal*1.1where ename=v_ename;

endif;

end;

a) 二重条件分支 if then else

--编写一个块，可以输入一个雇员名，如果该雇员的补助不是0就在原来的基础上增加100；如果补助为0就把补助设为200

--编写一个块，可以输入一个雇员名，如果该雇员的补助不是0就在原来的基础上增加100；

--如果补助为0就把补助设为200

declare

v_ename emp.ename%type;

v_comm emp.comm%type;

declare

v_ename emp.ename%type;

v_comm emp.comm%type;

begin

v_ename :='ALLEN';

select nvl(comm,0)into v_comm from emp_bak where ename=v_ename;

if v_comm<>0then

update emp_bak set comm = comm+100where ename = v_ename;

else

update emp_bak set comm =200where ename = v_ename;

endif;

end;

b) 多重条件分支 if-then elsif – else[CASE 实现]

--编写一个块，可以输入一个雇员编号，如果该雇员的职位是PRESIDENT就给他的工资增加1000，如果该雇员的职位是MANAGER就给他的工资增加500，其它职位的雇员工资增加200

二、循环语句– loop

编写一个过程，可输入用户名，并循环添加10个用户到users表中，用户编号从1开始

--编写一个过程，可输入用户名，并循环添加10个用户到users表中，用户编号从1开始

3、异常【系统异常、通用异常、自定义异常、应用程序异常】

a) 系统异常

--使用替代变量输入数据，实现向emp表插入数据，并在插入前检查是不是违反主键约束，

declare

v_empno emp.empno%type;

v_ename emp.ename%type;

v_sal emp.sal%type;

begin

v_empno :=&empno;

v_ename :='&ename';

v_sal :=&sal;

insertinto emp_bak(empno,ename,sal)values(v_empno,v_ename,v_sal);

exception

when Dup_val_on_index then

dbms_output.put_line('员工编号已经存在！');

end;

b) 通用异常【others】SQLCode异常编码 sqlerrm异常错误消息

--查询?部门的所有员工打印结果

declare

v_emprow emp%rowtype;

v_deptno emp.deptno%type;

begin

v_deptno :=&deptno;

select*into v_emprow from emp where deptno = v_deptno;

exception

when No_data_found then

dbms_output.put_line('输入的部门不存在！');

when Too_many_rows then

dbms_output.put_line('返回的行数太多！');

whenothersthen

dbms_output.put_line('错误编号：'||SQLCode||'，错误信息：'||SQLErrM);

end;

c) 自定义异常

--编写PLSQL块，完成限制输入的数字范围1到10，并正确处理异常

declare

v_num number(2);

--1、定义一个异常

MY_ERR exception;

begin

v_num :=0;

--2、经过判断后抛出异常.

if v_num <1or v_num >10then

raise MY_ERR;

endif;

dbms_output.put_line(v_num);

--3、捕获异常

exception

when MY_ERR then

dbms_output.put_line('输入的数字必须在1~10之间');

end;

d) 应用程序异常反馈

pragma exception_init(异常名称,sqlcode);

raise_application_error(sqlcode,sqlerrm) sqlcode错误编码-20000 ~ -20999

declare

v_num number(2);

--1、定义一个异常

MY_ERR exception;

pragmaexception_init(MY_ERR,-20001);

begin

v_num :=0;

--2、经过判断后抛出异常.

if v_num <1or v_num >10then

raise_application_error(-20001,'输入的数字必须在1~10之间');

endif;

dbms_output.put_line(v_num);

--3、捕获异常

exception

whenothersthen

dbms_output.put_line('错误编号：'||SQLCode||',错误信息：'||SQLErrM);

end;

4、游标cursor-处理多行数据

a) 隐式游标sql

b) 显式游标

c) 动态游标（REF游标）

d) 公有属性

%found 影响一行或多行数据的时候此属性为true

%notfound 没有影响行数此属性为true

%rowcount 行数

%isopen 游标的打开

e) 案例一：隐式游标-修改scott的工资为2000块

--隐式游标-查询SCOTT的工资并进行打印

declare

v_sal emp.sal%type;

begin

select sal into v_sal from emp where ename ='SCOTT';

ifsql%foundthen

dbms_output.put_line(sql%rowcount);

endif;

end;

--隐式游标-修改30号部门的工资为2000块

Begin

update emp_bak set sal =2000where deptno =30;

ifsql%foundthen

dbms_output.put_line(sql%rowcount);

else

dbms_output.put_line(‘输入的部门编号不存在！’);

endif;

end;

f) 案例二：显式游标-查询30号所有员工信息

--查询30部门所有员工信息

declare

v_emprow emp%rowtype;

--定义游标

cursor cur_erow isselect*from emp where deptno =30;

begin

--获取游标

for v_emprow in cur_erow

loop

dbms_output.put_line('雇员姓名：'||v_emprow.ename);

endloop;

end;

案例三：带参显式游标 -显式游标-查询？号所有员工信息

--查询30部门所有员工信息

declare

v_emprow emp%rowtype;

--定义游标

cursor cur_erow(v_deptno emp.deptno%type)isselect*from emp where deptno = v_deptno;

begin

--获取游标

for v_emprow in cur_erow(&deptno)

loop

dbms_output.put_line('雇员姓名：'||v_emprow.ename);

endloop;

end;

练习：将score_bak表中的每个学生的成绩改成这门课的平均成绩

declare

v_vwrow vw_avg_scr%rowtype;

cursor cur_avg isselect*from vw_avg_scr;

begin

for v_vwrow in cur_avg

loop

update score_bak set scr = v_vwrow.avg_scr where cno = v_vwrow.cno;

endloop;

end;

g) 案例四：loop循环游标

--查询30号部门所有员工信息

declare

v_erow emp%rowtype;

--定义游标

cursor cur_erow isselect*from emp where deptno =30;

begin

--打开游标

open cur_erow;

--获取游标

loop

fetch cur_erow into v_erow;

dbms_output.put_line('员工姓名：'||v_erow.ename);

exitwhen cur_erow%notfound;

endloop;

--关闭游标

if cur_erow%isopenthen

close cur_erow;

endif;

end;

h) 案例五:动态游标/ref游标

--使用动态游标打印员工信息和部门信息

declare

v_erow emp%rowtype;

v_drow dept%rowtype;

--1、定义一个动态游标类型

--弱类型的动态游标

type cur1 isrefcursor;

--强类型的动态游标

type cur2 isrefcursorreturn emp%rowtype;

--2、使用动态游标类型定义一个动态游标

cur_row cur1;

begin

--3、打开游标并给游标赋值

open cur_row forselect*from emp;

--4、使用loop循环获取游标

dbms_output.put_line('员工信息：');

loop

fetch cur_row into v_erow;

dbms_output.put_line(v_erow.ename);

exitwhen cur_row%notfound;

endloop;

--5、关闭游标

if cur_row%isopenthen

close cur_row;

endif;

--6、重新打开游标并重新给游标赋值

open cur_row forselect*from dept;

--7、使用loop循环获取游标

dbms_output.put_line('部门信息：');

loop

fetch cur_row into v_drow;

dbms_output.put_line(v_drow.dname);

exitwhen cur_row%notfound;

endloop;

--8、关闭游标

if cur_row%isopenthen

close cur_row;

endif;

end;

1、 Oracle的自定义函数

2、 Oracle的触发器

3、 Oracle的存储过程

知识点一：自定义函数

语法：create [or replace] function 函数名（参数）

return返回值类型—必须

--变量的声明

begin

--执行语句

--return 返回变量

--exception 异常块（return 异常编号）

end；

Java：

int add(int no1,int no2)

{

int sum = no1+no2;

return sum;

}

Oracle

--使用PLSQL developer创建自定义函数，实现两个数字相加

createorreplacefunction fun_add(num1 number,num2 number)

returnnumber

v_result number(2);

begin

v_result := num1+num2;

return v_result;

exception

whenothersthen

dbms_output.put_line('输入错误！');

return-1;

end;

调用函数

方式一：

select fun_add(50,50) from dual;

方式二：

declare

v_num1 number(2);

v_num2 number(2);

v_result number(2);

begin

v_num1 :=&加数1;

v_num2 :=&加数2;

v_result := fun_add(v_num1,v_num2);

dbms_output.put_line(v_result);

exception

whenothersthen

dbms_output.put_line('输入的数据有误！！');

end;

2017-10-31 18:36:17