学习笔记
1.1 流程控制语句
%type定义一个变量，其数据类型和该表某个字段类型一致
%rowtype返回一个记录类型，其数据类型和表数据结构一致
|
set SCHEMA SCOTT; declare v_empno emp.empno%type :=7369; rec emp%ROWTYPE; begin select * into rec from emp where empno=v_empno; print (rec.ename||'的职业是：'||rec.job||'，工资是:'||rec.sal); end; |
if语句
|
declare v_empno emp.empno%type:=7369; v_sal emp.sal%type; v_com varchar(50); begin select sal into v_sal from emp where empno=v_empno; if v_sal<3000 then v_com:='需要加工资'; else v_com:='工资不变'; end if; print (v_com); end; |
Case语句
|
select ename,deptno,case when deptno=10 then sal*1.2 when deptno=20 then sal*1.5 when deptno=30 then sal*2 else sal end case as tol from emp; --等价与下面 select ename ,deptno ,case deptno when 10 then sal*1.2 when 20 then sal*1.5 when 30 then sal*2 else sal end case as tol from emp; |
循环语句-loop
|
declare v1 int:=0; begin loop v1:=v1+2; print ('int的当前值为:'||v1); exit when v1=12; --一定要有退出条件 end loop; end; |
循环语句-while
|
declare v1 int:=1; v2 int:=0; begin while v1<=100 loop v2:=v2+v1; v1:=v1+1; end loop; print ('从1加到100的和是:'||v2); end; |
循环语句-for
|
declare v1 int:=0; begin for i in 1..10 loop v1:=v1+i; end loop; print ('1加到10的和是:'||v1); end; |
1.2 游标
|
对于处理多行记录的事务经常使用游标实现，游标是一个指向上下文的句柄和指针 游标分为显性游标和隐式游标 显示游标：1定义游标 2 打开游标 3 抓取数据 4 关闭游标 显性游标属性
隐性游标属性
|
显性游标
|
--工资排行第五到第十的员工信息 declare v_ename emp.ename%type; v_sal emp.sal%type; v_num int; cursor c_cursor is select a.r, a.ename,a.sal from (select rownum r,e.* from (select * from emp order by sal desc)e where rownum<=10)a where a.r>=5; --定义游标 begin open c_cursor; --打开游标 fetch c_cursor into v_num,v_ename,v_sal; --获取数据 while c_cursor %found loop --判断游标属性 print('工资排行第'||v_num||'的员工是'||v_ename||',工资是:'||v_sal); fetch c_cursor into v_num,v_ename,v_sal; end loop; close c_cursor; --关闭游标 end;
--每个部门工资最高工资员工信息 declare v_ename emp.ename%type; v_sal emp.sal%type; v_dname dept.dname%type; cursor c_cursor is select d.dname,e.ename, e.sal from ( select * from ( select deptno,ename,sal,row_number()over(partition by deptno order by sal desc)rn from emp) where rn=1)e,dept d where e.deptno=d.deptno; --定义游标 begin open c_cursor; --打开游标 fetch c_cursor into v_dname,v_ename,v_sal; --获取数据 while c_cursor %found loop --判断游标属性 print('部门'||v_dname||'工资最高的员工是'||v_ename||',工资是:'||v_sal); fetch c_cursor into v_dname,v_ename,v_sal; end loop; close c_cursor; --关闭游标 end; |
处理隐性游标
|
--删除emp部门为30的所有员工，如部门没员工删除，则删除该部门 declare v_deptno emp.deptno%type:=30; begin delete from emp where deptno=v_deptno; if sql%NOTFOUND then delete from dept where deptno=30; end if; end; |
游标检索循环
|
declare v_empno emp.empno%type; v_sal emp.sal%type; cursor c_cursor is select empno ,sal from emp; begin open c_cursor; loop fetch c_cursor into v_empno ,v_sal; exit when c_cursor %NOTFOUND; if v_sal<3000 then update emp set sal=sal*2 where empno=v_empno; print ('编号为'||v_empno||'工资已更新'); end if; print ('记录数：'||c_cursor %ROWCOUNT); end loop; close c_cursor; end; |
游标for循环
|
declare cursor c_sal is select empno ,ename,sal from emp; begin for v_sal in c_sal loop --隐含执行一个fetch语句 --隐含监控c_sal%notfound print(to_char(v_sal.empno)||'--'||v_sal.ename||'--'||to_char(v_sal.sal)); end loop; end; |
not_data_found与%notfound
|
类型 |
应用场景 |
|
|
no_data_found |
select ..into |
|
|
%notfound |
当一个显式游标的where字句未找到时 |
|
|
sql%notfound |
当update或delete语句where未找到时 |
|
|
%notfond或%found |
确定循环退出条件 |
|
1.3 异常处理
--预定义异常
|
--NO_DATA_FOUND declare v_empno emp.empno%type:=7300; v_sal emp.sal%type; begin select sal into v_sal from emp where empno=v_empno; if v_sal<=8000 then update emp set sal=sal+100 where empno=v_empno; print('编码为'||v_empno||'员工工资已更新'); else print ('编码为'||v_empno||'员工工资已超过规定值'); end if; exception when NO_DATA_FOUND then print ('编码为'||v_empno||'员工不存在'); end; --TOO_MANY_ROWS declare v_empno emp.empno%type:=7300; v_sal emp.sal%type; begin select sal into v_sal from emp ; if v_sal<=8550 then update emp set sal=sal+100 where empno=v_empno; print('编码为'||v_empno||'员工工资已更新'); else print ('编码为'||v_empno||'员工工资已超过规定值'); end if; exception when TOO_MANY_ROWS then print ('返回结果过多'); end; |
异常处理--自定义异常
|
declare v_empno emp.empno%type:=7169; e_result exception; begin update emp set sal=sal+1000 where empno=v_empno; if sql%NOTFOUND then raise e_result; end if; exception when e_result then print ('无此员工编号，更新失败'); print(SQLCODE); --错误代码 print(SQLERRM); --错误信息 when OTHERS then null; end; |
1.4 触发器
|
--dml触发器 create table tt(name varchar(20),time1 DATE); create or replace trigger trg1 after delete --操作及时间 on test --对象 for each statement --语句级 begin insert into tt values (user,sysdate); --触发操作 end; delete from test where rowid<=1; select * from tt; --记录员工工资变更情况 create table tt2(name varchar(20),old_sal number(10,2),new_sal number(10,2),time1 datetime(6)); create or replace trigger trg2 after update --操作及时间 on test --对象 for each row --行级 begin insert into tt2 values(user,:old.sal,:new.sal,sysdate); end; --:old 新值 :new 新值 update test set sal=sal+1000 where sal<3000;
--替代触发器（只支持视图，不建议对复杂视图做dml操作） create view emp_view as select deptno,count(*) total_emp,sum(sal) total_sal from scott.emp group by deptno; create or replace trigger trg3 instead of delete --操作及时间 on emp_view --对象 for each row --行级 begin delete from scott.emp where deptno=:old.deptno; end; delete from emp_view where deptno=10;
|
1.5 存储过程
|
create or replace procedure delemp (v_empno emp.empno%type) as e_result EXCEPTION ;--定义异常 begin delete from emp where empno=v_empno; if sql%NOTFOUND then raise e_result; end if; print ('编号为'||v_empno||'员工已删除'); exception when e_result then print('编号不存在'); end; --调用存储过程 call delemp(7369); --根据员工号获取员工信息 create or replace procedure p_emp (v_empno emp.empno%type) as v_ename emp.ename%type; v_sal emp.sal%type; v_deptno emp.deptno%type; e_result EXCEPTION ;--定义异常 cursor c_emp is select ename,sal,deptno from emp where empno=v_empno; begin open c_emp; fetch c_emp into v_ename,v_sal,v_deptno; if c_emp%NOTFOUND then raise e_result; end if; close c_emp; print (v_ename||'所在的部门是：'||v_deptno||'，工资是：'||v_sal); exception when e_result then print ('员工非本公司成员'); end; --调用存储过程 call p_emp(7499); |
使用匿名块调用存储过程
|
create or replace procedure p_demo (v_deptno emp.deptno%type,v_sal out emp.sal%type ,v_count out int) as begin select sum(sal),count(*) into v_sal ,v_count from emp where deptno=v_deptno; exception when NO_DATA_FOUND then print('数据不存在'); when OTHERS then null; end; --调用存储过程 declare v_num int; v_sum int; begin p_demo(10,v_sum,v_num); print('10号部门总工资是'||v_sum||',人数是'||v_num); end; --重编译存储过程 alter procedure p_demo compile; --查看存储过程的定义的两种方法 select * from dba_source where name='P_DEMO'; select DBMS_METADATA.GET_DDL('PROCEDURE','P_DEMO','SCOTT'); |
1.6 函数
|
create or replace function get_sal (v_deptno emp.deptno%type,v_count out int) return int as v_sum int; begin select sum(sal),count(*) into v_sum ,v_count from emp where deptno=v_deptno; return v_sum; exception when NO_DATA_FOUND then print('数据不存在'); when OTHERS then null; end; --函数调用方式（1） declare v_num int; v_sum int; begin v_sum:=get_sal(20,v_num); print('20号部门总工资是'||v_sum||',人数是'||v_num); end; --函数调用方式（2） declare v_num int; v_sum int; begin v_sum:=get_sal(v_deptno=>20,v_count=>v_num); print('20号部门总工资是'||v_sum||',人数是'||v_num); end; |
1.7 包
|
--创建测试表 create table t_emp as select empno,ename,sal,deptno from emp ; alter table t_emp add constraint primary key(empno); --创建包头 create or replace package emp_package is --创建包头 g_deptno number(3):=30; --定义公共变量 procedure add_emp(eno number,name varchar,sal number,dno number default g_deptno); procedure fire_emp(eno number); function get_sal(eno number)return number; end ; --创建包体 create or replace package body emp_package is procedure add_emp(eno number,name varchar,sal number,dno number default g_deptno)is begin insert into t_emp(empno,ename,sal,deptno)values(eno,name,sal,dno); exception when DUP_VAL_ON_INDEX then RAISE_APPLICATION_ERROR(-20012,'改雇员已存在'); end; procedure fire_emp(eno number) is begin delete from t_emp where empno=eno; if sql%NOTFOUND then RAISE_APPLICATION_ERROR(-20012,'改雇员不存在'); end if; end; function get_sal(eno number)return number is v_sal t_emp.sal%type; begin select sal into v_sal from t_emp where empno=eno; print(v_sal); return v_sal; exception when NO_DATA_FOUND then RAISE_APPLICATION_ERROR(-20012,'改雇员不存在'); end; end ; --调用包 emp_package.add_emp(2000,'cbc',19000); emp_package.fire_emp(1000); emp_package.get_sal(2000); |
