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返回值类型—必须
is
--变量的声明
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 is 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
