9.pl/sql 子程序
pl/sql 可以分为命名块和匿名块。下面将介绍pl/sql 命名块，命名块没有匿名块的限制，他们可以存储到数据库中，可以被其他的块调用，不需要在每次执行时都重复编译。
1.创建过程的示例 create or replace procedure newdept( p_deptno dept.deptno%type; -- 部门编号 p_name dept.dname%type; -- 部门位置 p_loc dept.loc%type ) as v_deptcount number; -- 保存是否存在员工编号 begin select count(*) into v_deptcount from dept where deptno=p_deptno; -- 查询在deptno 表中是否存在部门编号 if v_deptcount>0 -- 如果存在相同的员工信息 then raise_application_error(-2002,'出现了相同的员工信息'); end if; insert into dept(deptno,dname,loc) values(p_deptno,p_dname,p_loc); --插入记录 commit; --提交事务 end;
2.调用1 过程示例 begin newdept(10,'成本科','深圳'); exception when others then dbms_output.put_line('产生了错误：'||sqlerrm); end;
3.创建newdept 过程 create or replace procedure newdept( p_deptno in number, -- 部门编号 p_dname in varchar2, -- 部门名称 p_loc in varchar2 -- 位置 ) as v_deptcount number(4); -- 保存是否存在员工编号 e_duplication_dept exception; begin select count(*) into v_deptcount from dept where deptno=p_deptno; --查询在dept 表中是否存在的部门编号 if v_deptcount >0 -- 如果存在相同的员工记录 then -- 抛出异常 raise e_duplication_dept; end if; insert into dept(deptno,dname,loc) values(p_deptno,p_name,p_loc); -- 插入记录 commit; -- 提交事务 exception when e_duplication_dept then rollback; raise_application error(-20002,'出现了相同的员工信息'); end;
4.getraisedsalary 函数示例 create or replace function getraisedsalary(p_empno emp.empno%type) return number is v_job emp.job%type; -- 职位变量 v_sal emp.sal%type; -- 薪资变量 v_salaryratio number(10.2);-- 调薪比率 begin --获取员工表中的薪资信息 select job,sal into b_job,v_sal from emp where empno=v_empno; case v_job when '职员' then v_salaryratio :=1.09; when '销售人员' then v_salaryratio :=1.11; when '经理' then v-salaryratio :=1.18; else v_salaryratio :=1; end case; if v_salaryratio <>1 -- 如果有调薪的可能 then return round(v_sal *v_salaryratio,2); -- 返回调薪后的薪资 else return v_sal; -- 否则不返回薪资 end if; exception when no_data_found then return 0; -- 如果没有找到员工共记录，则返回0 -- 如果没有找到员工记录，则返回0 end getraisedsalary;
5.调用4函数 declare v_raisedsal number(10,2); -- 定义保存调薪记录的临时文件 begin -- 调用函数获取调薪后的记录 dbms_output.put_line('7369员工调薪记录：'getraisedsalary(7369)); v_raisedsal:=getraisedsalary(7521); dbms_output.pUt_line('7521员工调薪记录：'||getraisedsalary(7521)); end;
6.在过程中使用return语句示例 create or replace procedure raisesalary( p_)rmpno emp.mempno%type -- 员工编号参数 as v_job emp.job%type; -- 局部的职位变量 v_sal emp.sal%type; -- 局部的薪资变量 begin -- 查询员工信息 select job,sal into v_job,v_sal from emp where empno=p_empno; if v_job<>'职员' then -- 仅为职员加薪 return; -- 如果不是职员，则退出 elsif v_sal >3000 then -- 如果职员薪资大于3000 则退出 return; else -- 否则更新薪资记录 update emp set sal= round(sal*1.12,2) where empno=p_empno; end if; exception when no_data_found then -- 异常处理 dbms_output.put_line('没有找到员工记录'); end raisesalary;
7.insertdept 过程示例 create or replace procedure insertdept( p_deptno number, --定义形参 p_dname varchar2, p_loc varchar2 ) as v_count number(10); begin select count(deptno) into v_count from dept where deptno=p_deptno; if v_count >1 then raise_application_error(-20001,'数据库中存在相同名称的部门编号！'); end if; insert into dept values(p_deptno,p_name,p_loc);-- 在过程体中使用形参 commit; end;
8.使用in模式 create or replace procedure insertdept( p_deptno in number:=55, --定义形式参数，并赋初值 p_dname in varchar2, p_loc in varchar2 ) as v_count number(10); begin -- p_dname:='市场策略部'; -- 错误，不能对in模式参数进行赋值 select count(deptno) into v_count from dept where deptno=p_deptno; if v_count>1 then raise_application_error(-20001,'数据库中存在相同名称的部门编号'); end if; insert into dept values(p_deptno,p_name,p_loc);-- 在过程体中使用形式参数 commit; end; -- 调用 begin insertdept(55,'秦云不','西北'); end;
9.使用out 模式 create or replace procedure OutraiseSalary( p_enpno in number, p_raisedSalary out number -- 定义一个员工加薪后的子女西的输出变量 ) as v_sal number(10,2); -- 定义本地局部变量 v_job varchar2(10); begin p_raisedSalary:=0; -- 变量赋初值 select sal,job into v_sal,v_job from emp where empno=p_empno;-- 查询员工信息 if v_job='职员' then -- 仅对职员加薪 p_raisedSalary :=v_sal*1.12; -- 对out 模式的参数进行赋值时合法的 update emp set sal=p_raisedSalary where empno=p_empno; else p_raisedSalary :=v_sal; -- 否则赋原先的薪资值 end if; exception when no_data_found then --异常处理语句块 dbms_output.put_line('没有找到该员工的记录'); end; --调用 declare v_raisedsalary number(10,2); -- 定义一个变量保存数据值 begin v_ralsedsalary:=100; -- 这个赋值在传入到outraisesalary后呗hulue outRaiseSalary(7369,v_raisedsalary); -- 调用函数 dbms_outpuit.put_line(v_raisedsalary); -- 显示输出参数的值
10.使用 inout 模式 create or replace procedure calcraisedsalary( p_job in varchar2, p_cakary in out number -- 定义输入输出参数 ) as v_sal number(10,2); -- 保存调整后的薪资值 begin if p_job='职员' then -- 根据不同的job进行薪资的调整 v_sal:=p_salary*1.12; elsif p_job='销售人员' then v_sal:=p_salary*1.18; elsif p_job='经理' then v_sal :=p_salary*1.19; else v_sal:=p_salary; end if; p_salary:=v_sal; --将调整后的结果赋给输入/输出参数 end; -- 调用 declare v_sal number(10,2); --薪资变量 v_job varchar2(10); -- 职位变量 begin select sal,job into v_sal,v_jon from emp where empno='7369'; --获取薪资和职位信息 calcraisedsalary(v_job,v_sal); -- 计算调薪 dbms_output.Put_line('计算后的调整薪水为:'||v_sal);-- 获取调薪后的结果 end;
11 使用%type 定义形式参数 create or replace procedure calcraisedsalarywithtype( p_job in emp.job%type, p_salary in out emp.sal%type --定义输入/输出参数 ) as v_sal number(10,2); -- 保存调整后的薪资值 begin if p_job ='职员' then --根据不同的job 进行薪资的调整 v_sal:=p_salary*1.12; elsif p_job='销售人员' then v_sal:=p_salary**1.18; elsif p_job='经理' then v_sal:=p_salary*1.19; else v_sal:=p_salary; end if; p_salary:=v_sal; -- 将调整后的结果给输入/输出参数 end;
12. 指定形式参数的默认值 create or replace procedure newdeptwithdefault( p_deptno dept.deptno%type default 57, -- 部门编号 p_dname dept.dname%type:='管理部', -- 部门名称 p_loc dept.loc%type default '江苏' -- 位置 ) as v_deptcount number; -- 保存是否存在员工编号 begin select count(*) into v-deptcount from dept where deptno=p_deptno; -- 查询在dept表中时否存在部门编号 if v_deptcount>0 -- 如果存在相同的员工记录 then raise_application_error(-20002,'出现了相同的员工记录'); end if; insert into dept(deptno,dname,loc) values(p_deptno,p_dname,p_loc); --插入记录 end;
13.nocopy 使用示例 declare type emptabtyp is table of emp%rowtype; -- 定义嵌套表类型 emp_tab emptavtyp :=emptabtyp(null); -- 定义一个空白的嵌套表变量 t1 number(5); -- 定义保存时间的临时变量 t2 number(5); t3 number(5); procedure get_time(t out number) -- 获取当前时间 is begin select to_char(sysdate,'ssss') into t from dual; -- 获取从午夜到当前的秒数 end; procedure do_nothing1 (tab in out emptabtyp) -- 定义一个空白的过程，具有in out 参数 is begin null; end; begin select * into rmp_tab(1) from rmp where empno=7788;-- 查询emp表中的员工，插入到emp_tab第一个记录 emp_tab.extend(900000,1); -- 复制第一个元素n次 get_time(t1); -- 获取当前时间 do_nothing1(emp_tab); -- 执行不带nocopy的过程 get_time(t2); -- 获取当前时间 do_nothings(emp_tab); -- 执行带nocopy的过程 get_time(t3); -- 获取当前时间 dbms_output.put_line('调用所花费的时间（秒）'); dbms_output.put_line('------------------'); dbms_output.put_line('不带nocopy的调用：'||to_char(t2-t1)); dbms_output.put_line('带nocopy的调用：'||to_char(t3-t2)); end;
14. 定义可被sql语句调用的子程序 create or replace function getempdept( p_empno emp.empno%type )return varchar2 -- 参数必须是Oracle数据库类型 as v_dname dept.dname%type; begin select b.dname into v_dname from emp a,dept b where a.deptno=b.deptno and a.empno=p_empno; return v_dname;-- 查询数据库，获取部门信息 exception when no_data_found then return null; -- 如果查询不到数据，返回null end;
15. 使用嵌套子程序示例 create or replace function getraisedsalary_subprogram(p_empno emp.empno%type) return number is v_salaryratio number(10,2); -- 调薪比率 v_sal emp.sal%type; -- 薪资变量 -- 定义内嵌子函数，返回薪资和调薪比率 function getratio(p_sal out number) return number is n_job emp.job%type; -- 职位变量 n_salaryratio number(10,2); -- 调薪比率 begin -- 获取员工表中的薪资信息 select job,sal into n_job,p_sal form emp where empno=p_empno; case n_job when '职员' then n_salaryratio :=1.09; when '销售问题' then n_salaryratio :=1.11; when '经理' then n_salaryratio :=1.18; else n_salaryratio :=1; end; begin v_salaryratio:=getratio(v_sal); --调用嵌套函数，获取挑衅比率和员工薪资 if v_salaryratio >1 then return round(v_sal * v_salaryratio,2); - -返回调薪后的薪资 else return v_sal; -- 否则不返回薪资 end if; exception when no_data_found then return 0; --如果没有找到员工记录，则返回0 end;
16.嵌套子程序互调用示例 declare v_sal binary_integer:=5; procedure a(p_counter in out binary_integer) is -- 声明嵌套子程序A begin dbms_output.put_line('A('||p_counter||')'); if p_counter>0 then b(p_counter); --在嵌套子程序中调用B p_counter：=p_counter-1; end if; end A; procedure b(p_counter in out binary_integer) is -- 声明嵌套子程序B begin dbms_output.put_line('b('||p_counter||')'); p_counter:=P_counter-1; a(p_counter); -- 在嵌套子程序中调用A end b; begin b(v_val); --调用嵌套子程序b end;
17. 使用前向声明及逆行互调用 declare v_sal binary_integer:=5; procedure b(p_counter in out binary_integer);-- 前向声明嵌套子程序B procedure a(p_counter in out binary_integer) is -- 声明嵌套子程序 a begin dbms_output.put_line('a('||p_counter||')'); if p_counter >0 then b(p_counter); -- 在嵌套子程序中调用b p_counter:=p_counter-1; end if; end a; procedure b(p_counter in out binary_integer) is -- 声明嵌套子程序b begin dbms_output.put_line('b('||p_counter||')'); p_counter:=p_counter-1; a(p_counter); -- 在嵌套子程序中调用A end b; begin b(v_sal); -- 调用嵌套子程序b end;
18.嵌套子程序重载示例 declare procedure getsalary(p_empno in number) is -- 带一个参数的过程 begin dbms_output.put_line('员工编号为：'||p_empno); end; procedure getsalary(p_empname in varchar2) is --重载的过程 begin dbms_output.put_line('员工名称为：'||p_empname); end; procedure getsalary(p_empno in number,p_empname in varchar2) is -- 升薪的过程 begin dbms_output.put_line('员工编号为：'||p_empno||'员工名称为'||p_empnme); end; begin getsalary(7369); -- 调用重载方法 getsalsry('史密斯'); getsalary(7369,'史密斯'); end;
19.自制事务使用示例 declare procedure testAutonomous(p_empno number) as pragma autonomous_transaction; --标记为自治事务 begin -- 现在过程中是自治的事务，主事务被挂起 insert into emp_history select * from emp where empno=p_empno; commit; -- 提交自治事务，不影响主事务 end testautonomous; begin -- 主事务开始执行 insert into emp_history(empno,ename,sal) values(1011,'测试','1000'); testautonomous(7369); --主事务挂起，开始自治事务 rollvack; --回滚主事务 end;
20. plsql 实现递归阶乘 declare v_result integer; function fac(n positive) return integer is -- 阶乘的返回结果 begin if n=1 then -- 如果n=1 则终止条件 dbms_output.put('11!=1*0'); return 1; else dbms_output.put_line(n||'!='||n||'*'); return n*fac(n-1); --否则递归调用自身 end if; end fac; begin v_result :=fac(10); ---调用阶乘函数 dbms_output.put_line('结果是：'||v_result);-- 输出阶乘结果 end;
21.使用递归查询职员列表示例 declare procedure find_staff(mgr_no number, tier number :=1) is boss_name varchar2(10); -- 定义老板的名称 cursor c1(boss_no number) --定义游标来查询emp表中当前编号下的员工列表 is select empno,ename from emp where mgr=boss_no; beign select ename into boss_name form emp where empno=mgr_no; -- 获取管理者名称 if tier =1 -- 如果tier 指定1 ，表示从底层开始查询 then insert into staff values(boss_name ||'是老板'); -- 因为第一层是老板，下面才是经理 end if; for ee in c1(mgr_no) --通过游标for 循环向staff 表中插入员工信息 loop insert into staff values(boss_name ||'管理' ||ee.ename ||'在层次' ||to_char(tier)); find_staff (ee.empno.tier+1); --在游标中，递归调用下层的员工列表 end loop; commit; end find_staff; begin find_staff(7839); -- 查询7839管理下的与纳贡的列表和层次结构 end;
22.子程序依赖性示例 create or replace procedure testdependence as begin -- 向emp表插入测试数据 insert into emp(empno,ename,sal) values(1011,'测试',1000); testsubprog(7369); rollback; end; -- 被另外一个过程调用，用来向emp_history 表插入数据 create or replace procedure testsubprog(p_empno number) as begin insert into emp_history select *from emp where empno=p_empno; end testsubprog;
