oracleday19(复合类型 游标变量 pl/sql 进阶 条件分支语句 loop )

复合类型 

相当于高级语言中的数组 但是需要注意的是在高级与语言中数组的下标不能为负数 而pl/sql 是可以为负数 并且表元素的下标没有限制

declare

type sp_table_type is table of emp.ename%type 

index by binary_integer;

sp_table sp_table_type; --定义一个变量:sp_table  :类型

begin

select ename into sp_table(-1) from emp where empno=7788;

dmbs_output.put_line('员工名'||sp_table(-1));

end;

/

sp_table_type 是pl/sql表类型

emp.ename%type 指定了表的元素的类型和长度

sp_table 为pl/sql表变量

sp_table(0)则表示下标为0的元素

type 自己定义的pl/sql 数据类型 is table of emp.sal%type index by binary_integer;

参照变量

参照变量是指 用于存放数值指针的变量 通过使用参照变量 可以使得应用程序 共享共同对象 从而降低 占用的空间 在编写pl/sql 程序时

可以使用游标变量(ref cursor)和 对象类型变量(ref obj_type) 两种参照变量类型

游标变量  

通过游标 可以取得返回结果集(往往是select 语句的结果)的任何一行数据从而 提高共享的效率

ref cursor 的使用

定义游标  type 自定义游标名 is ref curspr ; 变量名 自定义游标名

打开游标 open 游标变量 for select 语句;

取出当前游标指向的行 fetch 游标变量 into 其他变量;

判断游标是否指向记录最后 游标变量%notfound

将 select 和游标结合 输入部门号 返回 该部门的所有员工姓名和他的工资

create or replace procedure pro1(v_in_deptno number) is

--先定义一个游标变量类型

type my_emp_cursor is ref cursor;

--定义一个游标变量

v_emp_cursor my_emp_cursor;

--定义两个变量

v_ename emp.ename%type;

v_sal emp.sal%type;

v_empno emp.empno%type; -- 名字可能相同 但是员工编号是唯一

begin

--执行语句

open v_emp_cursor for select ename,sal,empno from emp;

--取出游标指向的每行数据 用循环语句

loop

fetch  v_emp_cursor into v_ename,_v_sal ,v_empno;--这句话会引起v_emp_cursor向下走

--判断当前游标是否到达最后

exit when v_emp_cursor%notfound;--当游标到末尾时退出

--输出

dbms_output.put_line('用户名：'||v_enma||‘薪水：’||v_sal); --要判断不然是死循环

if v_sal<200 then

update emp set sal = sal+100 where empno = v_empno;

end loop;

--关闭游标(一定要关闭游标  连接越来越多就当机)

close v_emp_cursor;

end;

/

在上面的基础上 为 低于200工资的增加100  添加 if 语句 和加入 个员工编号 

declar 和exception 是可选的  declare 定义返回的变量 begin 必须有

exception when 异常名称 then  对异常的处理;  捕获异常 可以 给出明确提示  有时也可以 利用异常 来进行业务处理

oracle  可以指定是输入还是输出参数 create procedure 过程名(变量名 in 变量类型 变量名 out 变量 类型) in 可以不写默认in  但是out 要写

is --定义变量 begin --执行的语句 end;/

编写过程  show error  来查看具体的错误  oracle 默认不显示

CallableStatement  接口 

select 自己的函数() from emp ;  函数可以直接写在select  且必须要有返回值 过程 不能直接直接写在select 不一定要返回

函数和过程的区别 

1、函数必须有返回值 而过程可以没有

2、 函数和过程在java中调用方式不一样 函数 select 自己的函数名(列) from 表

过程 使用 CallableStatement 去完成调用

包 

为什么需要包 是为了更好的管理自己写的函数、过程 类型

 create package 包名is

--声明函数

function 函数名(参数1) return 返回类型;

--声明过程

procedure 过程吗(参数1...);

end;

包体是用于把包中声明的函数或者过程 实现的数据对象

create or replace package body 包名 is

--实现过程

create procedure 过程名(参数列表) is

--定义变量

begin

--执行语句

end;

--实现函数

create function 函数名(参数列表) return 数据类型

is

--定义变量

begin

--执行

end;

end;

pl/sql 标量类型 可以简单的理解为oracle 数据库的类型

用:=赋初值

%type 让定义的类型和表的类型一样

p/sql 记录 类似于结构体

表 类似于数组

java 中goto 是保留字 现在不能用

pl/sql 的进阶

pl/sql 的控制语句 

条件分支语句 三种 if then ; if then else ; if then  elsif  elsif ... else

简单的条件判断 if then 单重 

if 条件表达式 then --条件表达式可以 很复杂 但是最终都要返回真或假

执行语句;

end if ;

编写 如果雇员名的工资 低于2000 工资增加10%

create or replace procedure pro1(v_in_ename varchar2) is --也可以as 查出多个会报错 建议换成编号

--定义工资变量

v_sal emp.sal%type;

begin 

select sal into v_sal from emp where ename = v_in_ename;

if  sal <2000 then 

update emp  set sal =  sal*1.1 where ename = v_in_ename;

end;

二重条件分支 基本语法 if then else

if 条件表达式 then 

执行语句...;

else

执行语句

end if ;

查雇员 如果补助不是0 就加100 为0 则设为200

create or replace procedure pro1(v_in_ename varchar2) is

--定义补助

v_comm emp.comm%type;

begin 

--查询补助

select comm into v_comm from emp where emp= v_in_enmae;--in 是表示输入

if v_comm<>0 then 

update emp set comm = comm+100 where ename = v_in_ename;

else --如果要处理 null  就加个 elseif is null

update emp set comm = 200 where ename = v_in_ename;

end;

多重条件分支 if then elsif  else

if 条件表达式 then 

执行语句;

elsif 条件表达式 then

执行语句;

--可以有多个elsif 

else --else 可以没有

执行语句;

end if

如果是president 就加1000共i在 是manager 加500 其他加200

create or replace procedure pro1(v_in_empno number) is

--定义职位变量

v_job emp.job%type;

begin 

select job into v_job from emp where empno=v_in_empno;

if v_job = 'PRESIDENT' then  --只有单等 没equals 

update emp set sal = sal+1000 where empno =v_in_empno;

elsif v_job ='MANAGER' then

update emp set sal = sal+500 where empno =v_in_empno;

else 

update emp set sal =sal+200 where empno =v_in_empno;

end if; -- 可以不写内容但要写上去

end;

/

pl/sql字符串的比较是 =  比如  ‘abc’ =‘bbc’

进阶 控制结构

循环结构(循环控制语言) -loop 

最简单的循环语句 以loop 开头 以end loop 结尾 这种循环至少会被执行 一次 先执行后退出 

loo

执行语句 ..;

exit when 条件表达式

end loop;

说明: 这里的条件表达式 如果为 true  则继续 执行 否则退出

循环添加 n 个用户 到users 表中  用户编号从1 开始增加直到n

create table users(

id number primary key,

name varchar2(32)

);

--过程

create  or replace procedure pro1(v_in_ename varchar2 ,n number) is

--定义需要的变量 可以先写下面 需要的时候定义变量

v_empno number:=1;

begin

loop

--执行添加任务 

insert into users5 values (v_empno,v_in_ename);

exit when n<=0; --必须出现在循环 退出的是当前这个loop

--v_empno 自增

v_empno :=v_empno+1;--赋值 单个=号只是判断是否相等

end loop;

end;

循环语句 while 循环

基本循环至少要执行循环体 一次 而对于while循环来说 只有条件为true 时 才会执行循环体语句 

while 循环以 while ...loop 开始 以 end loop 结束

while 条件表达式 loop  --可以完成先判断再执行

执行语句 ...;

end loop ;

输入用户名后循环添加n次 从200编号开始

create or replace procedure pro1(v_in_ename varchar2 , v_in_n number) is 

--定义变量

v_empno number:= 200

begin

while v_empno<=200+v_in_number loop

insert into  users5 values(v_empno,v_in_ename);

v_empnp:=v_empno+1;

end loop;

end;

:= 需要在定义的时候 初始化 赋值 

不能在 is begin 对变量赋值 

不能对输入的值进行赋值

可以在初始化变量的时候 设定它的大小  varchar2(32);

create or replace procedure sp_pro6(spName varchar2) is 

v_test varchar2(40);

v_test='aaa';

begin

dbms_output.put_line(v_test);

end;

过程是错误的因为 在is ...begin间不能对变量赋值 如果要重新赋值  则需要在begin...end 间

create or replace procedure sp_pro6(spName varchar2) is 

v_test varchar2(40) :='aaa';

begin 

spName :='你好';

dbms_output.put_line(v_test||spName);

end;

pl/sql 中不能对输入参数的值 重新赋值

循环语句 for 循环 

begin 

  for i in reverse 1..10 loop --就是2个点点

insert into users values(i,'abc');

end loop;

end;

/

控制变量i 在隐含中 不停的增加 

推荐使用loop循环结构 不推荐使用for 循环

顺序控制语句 goto null

goto 语句用于跳转到特定标号去 执行语句 由于 使用goto 语句会增加程序的复杂性 并使得 应用程序可读性变差

所以一般应用开发中 不建议使用goto 语句 goto lable

declare

i number :=1;

begin

<<start loop>> --标号

loop

dbms_output.put_line('输出i'|| i);

if i=12 then 

goto end_loop;

end if;

i :=i+1;

if i =10 then 

goto start_loop;

end if ; 

end loop;

<<end loop>>

dbms_output.put_line('循环结束');

end;

--?输出什么

-- goto 语句作用是可以直接跳转到指定的标号<<>> 去继续执行

说明

pl/sql 支持 goto 和 标号的使用

goto 语句不要轻易使用

null  语句 不会执行任何操作 并且会直接将控制传递到下一条 语句 使用null语句的主要好处是提高pl/sql 的可读性 其实他什么都不做

declare 

v_sal emp.sal%type;

v_ename emp.ename%type;

begin

select ename,sal into v_ename,e_sal

from emp where empno=&no;

if v_sal<3000 then 

update emp set com = sal*0.1 where ename =v_ename;

else 

null;--null也是个语句 

endif;

end;