PLSQL
1.helloword my god
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');
END;
set SERVEROUTPUT ON;显示打印
2.输入雇员编号取得雇员名称
DECLARE
v_ename VARCHAR2(10);
v_eno NUMBER;
BEGIN
v_eno :=&empno;--使用键盘输入
SELECT ename INTO v_ename FROM emp where empno=v_eno;
DBMS_OUTPUT.PUT_LINE('雇员编号为:'||v_eno||'雇员的名字是:'||v_ename);
END;
3.变量的赋值方式
DECLARE
v_resultA NUMBER;
v_resultB NUMBER := 100;--1.直接在声名的地方赋值
BEGIN
v_resulta := 30;--不区分大小写一样可以传入参数2.在执行的时候赋值
DBMS_OUTPUT.PUT_LINE('总和是:'||(v_resultA+v_resultB));--3.此处加法一定要记得打括号
END;
4.添加一个约束
DECLARE
v_resultA NUMBER;
v_resultB CONSTANT NUMBER NOT NULL :=1000;--1.直接在声名的地方赋值,此处添加个约束不能为空
BEGIN
v_resulta := 30;--不区分大小写一样可以传入参数2.在执行的时候赋值
DBMS_OUTPUT.PUT_LINE('总和是:'||(v_resultA+v_resultB));--3.此处加法一定要记得打括号
END;
5.传递参数修改的位置
DECLARE
v_resultA NUMBER;--这种参数的定义是可以通过后期赋值修改的
v_resultB CONSTANT NUMBER NOT NULL :=1000;--1.直接在声名的地方赋值,此处添加个约束不能为空,这种参数传递是不能在执行语句中进行修改参数值的
BEGIN
v_resulta := 30;
v_resulta :=2000;--不区分大小写一样可以传入参数2.在执行的时候赋值
DBMS_OUTPUT.PUT_LINE('总和是:'||(v_resultA+v_resultB));--3.此处加法一定要记得打括号
END;
6.使用%TYPE来定义变量类型
--使用%TYPE定义变量类型
DECLARE
v_resultA EMP.EMPNO%TYPE;--使用%TYPE来定义变量类型,方便以后的数据查询
v_resultB CONSTANT NUMBER NOT NULL:=1000;--1.直接在声名的地方赋值,此处添加个约束不能为空
BEGIN
v_resulta := 30;
v_resulta :=2000;--不区分大小写一样可以传入参数2.在执行的时候赋值
DBMS_OUTPUT.PUT_LINE('总和是:'||(v_resultA+v_resultB));--3.此处加法一定要记得打括号
END;
7.使用%ROWTYPE声明变量类型
DECLARE
v_deptRow dept%ROWTYPE;--获取整张表的字段类型
BEGIN
SELECT * into v_deptRowfrom dept where deptno=30;
DBMS_OUTPUT.put_line('部门编号是:'||v_deptRow.deptno||',名称是'||v_deptRow.dname);
END;
8.范例:使用关系运算符
DECLARE
V_URL VARCHAR2(30):='sxsxsxsxs';
v_num1 number:=30;
v_num2 number:=50;
BEGIN
if v_num1<v_num2 then
DBMS_OUTPUT.PUT_LINE('第二比第一大');
end if;
if V_URL like '%s%' then
DBMS_OUTPUT.PUT_LINE('hahaha');
end if;
END;
9.范例逻辑运算符
--范例:使用关系运算符
DECLARE
v_a1 BOOLEAN := TRUE;
v_a2 BOOLEAN := FALSE;
v_a3 BOOLEAN;
BEGIN
if v_a1 and v_a2 then
DBMS_OUTPUT.PUT_LINE('1假');
end if;
if v_a1 or v_a2 then
DBMS_OUTPUT.PUT_LINE('2真');
end if;
if v_a1 and (NOT v_a2) then
DBMS_OUTPUT.PUT_LINE('3真');
end if;
if v_a1 and v_a3 then
DBMS_OUTPUT.PUT_LINE('4null');
end if;
END;
10.分支结构
Ifelse endif
DECLARE
v_countResult number;
BEGIN
select count(deptno)into v_countResult from dept;
if v_countResult>10then
SYS.DBMS_OUTPUT.PUT_LINE('DEPT表的记录大于10条。');
else
SYS.DBMS_OUTPUT.PUT_LINE('DEPT表的记录小于10条。');
end if;
END;
If- elsif -else – end if
DECLARE
v_empsal emp.sal%TYPE;
v_empname EMP.ENAME%TYPE;
v_emo EMP.EMPNO%TYPE;
BEGIN
v_emo :=&inputEmpno;
select ename,sal intov_empname,v_empsal from emp whereempno=v_emo;
if v_empsal>3000 then
SYS.DBMS_OUTPUT.PUT_LINE(v_empname||'的工资属于最高工资');
elsif v_empsal>2000then
SYS.DBMS_OUTPUT.PUT_LINE(v_empname||'的工资属于中等工资');
else
SYS.DBMS_OUTPUT.PUT_LINE(v_empname||'的工资属于低等工资');
end if;
END;
DECLARE
v_strVARCHAR2(50):='123';
BEGIN
ifREGEXP_LIKE(v_str,'^\d+$') then--使用正则
SYS.DBMS_OUTPUT.PUT_LINE('正则验证通过。');
end if;
END;
Case -when then - when then –else -end case
11.循环结构
--loop先运行再判断
DECLARE
v_i number:=1;
BEGIN
loop
SYS.DBMS_OUTPUT.PUT_LINE('v_i='||v_i);
exit when v_i>=3;
v_i :=v_i+1;
end loop;
end;
--while…loop先判断再运行
DECLARE
v_i number:=1;
BEGIN
while (v_i<=3)loop
SYS.DBMS_OUTPUT.PUT_LINE('v_i='||v_i);
v_i :=v_i+1;
end loop;
end;
--for 循环
DECLARE
v_i number:=1;
BEGIN
for v_i in 1..3 loop
SYS.DBMS_OUTPUT.PUT_LINE('v_i='||v_i);
end loop;
end;
--reverse倒序排列
DECLARE
v_i number:=1;
BEGIN
for v_i in REVERSE 1..3 loop
SYS.DBMS_OUTPUT.PUT_LINE('v_i='||v_i);
end loop;
end;
--使用exit退出循环
DECLARE
v_i NUMBER:=1;
begin
for v_i in 1..10 loop
if v_i=3 then
exit;---退出循环
end if;
dbms_output.put_line('v_i='||v_i);
end loop;
end;
--使用continue跳出当前继续不满足条件的循环
DECLARE
v_i NUMBER:=1;
begin
for v_i in 1..10 loop
if MOD(v_i,2)=0 then
CONTINUE;---退出循环
end if;
dbms_output.put_line('v_i='||v_i);
end loop;
end;
--GOTO进行跳转
DECLARE
v_result NUMBER:=1;
begin
for v_result in 1..10 loop
if v_result=2 then
GOTO endPoint;---退出循环
end if;
dbms_output.put_line('v_i='||v_result);
end loop;
<<endPoint>>
dbms_output.put_line('For循环提前结束。');
end;
---程序块嵌套
DECLARE
v_x number :=30;
begin
DECLARE
v_x VARCHAR2(40) :='MLDNJAVA';
v_y NUMBER:=20;
BEGIN
dbms_output.put_line('内部程序块输出:v_x='||v_x);
dbms_output.put_line('内部程序块输出:v_y='||v_y);
END;
dbms_output.put_line('外部程序块输出:v_x='||v_x);
end;
--处理异常
DECLARE
v_data NUMBER;
v_myexp exception;
pragma exception_init (v_myexp, -20789);
BEGIN
v_data := &inputData;
if v_data>10 and v_data<100 then
raise v_myexp;
end if;
exception
when v_myexp then
dbms_output.put_line('输入数据有错误!');
dbms_output.put_line('SQLCODE='||SQLCODE);
dbms_output.put_line('SQLERRM='||SQLERRM);
END;
异常处理
DECLARE
v_dno DEPT.DEPTNO%TYPE;
v_dna DEPT.DNAME%TYPE;
v_dloc DEPT.LOC%TYPE;
v_deptCount NUMBER;
BEGIN
V_DNO:=&inputdeptno;
V_DNA:='&inputdname';
V_DLOC :='&inputloc';
select count(deptno)into v_deptcount fromDEPT where deptno=V_DNO;
if V_DEPTCOUNT>0 then
raise_application_error(-20888,'我已经有了');--手工抛出一个异常
else
insert intodept(deptno,dname,loc)values(v_dno,v_dna,v_dloc);
DBMS_OUTPUT.PUT_LINE('新增成功');
COMMIT;
end if;
EXCEPTION
when others then
DBMS_OUTPUT.PUT_LINE(SQLERRM);
ROLLBACK;
END;
--集合记录类型
DECLARE
TYPE dept_type is RECORD (
deptno DEPT.DEPTNO%TYPE:=90,
dname DEPT.DNAME%TYPE,
loc DEPT.LOC%TYPE
);--定义记录类型
v_dept dept_type;--定义变量,指定记录类型
BEGIN
V_DEPT.dname :='GD';
V_DEPT.loc:='重庆';
DBMS_OUTPUT.PUT_LINE('部门编号:'||v_dept.deptno||',名称:'||v_dept.dname
||',位置:'||v_dept.loc);
END;
--嵌套记录类型
DECLARE
TYPE dept_type is RECORD (
deptno DEPT.DEPTNO%TYPE:=90,
dname DEPT.DNAME%TYPE,
loc DEPT.LOC%TYPE
);--定义记录类型
type emp_type is RECORD(
empno emp.empno%TYPE,
ename EMP.ENAME%TYPE,
job EMP.JOB%TYPE,
hiredate EMP.HIREDATE%TYPE,
sal EMP.SAL%TYPE,
comm EMP.COMM%TYPE,
dept dept_type --内部定义变量,嵌套记录类型
);
v_emp emp_type;
BEGIN
selecte.empno,e.ename,e.job,e.hiredate,e.sal,e.comm,d.deptno,d.dname,d.loc
intov_emp.empno,v_emp.ename,v_emp.job,v_emp.hiredate,v_emp.sal,v_emp.comm,
v_emp.dept.deptno,v_emp.dept.dname,v_emp.dept.locfrom emp e,dept d
where e.deptno=d.deptno(+) and e.empno=7369;
DBMS_OUTPUT.PUT_LINE('雇员编号:'||v_emp.empno||',姓名:'||v_emp.ename
||',职位:'||v_emp.job);
END;
--将记录类型插入表中
DECLARE
TYPE dept_type is RECORD(
deptno dept.deptno%TYPE,
dname DEPT.DNAME%TYPE,
loc DEPT.LOC%TYPE
);
v_dept dept_type;
BEGIN
v_dept.dname :='数据部';
v_dept.loc :='光大';
v_dept.deptno :=90;
INSERT INTO dept VALUES v_dept;--直接插入记录类型的数据
END;
---修改数据,利用记录类型保存数据。
DECLARE
TYPE dept_type isRECORD(
deptno dept.deptno%TYPE,
dname DEPT.DNAME%TYPE,
loc DEPT.LOC%TYPE
);
v_dept dept_type;
BEGIN
v_dept.dname :='数据部';
v_dept.loc :='重庆';
v_dept.deptno :=90;
UPDATE dept
SET row = v_dept wheredeptno=v_dept.deptno;
END;
select * from dept;
--定义索引表
DECLARE
TYPE info_index is tableof VARCHAR(20)
index by PLS_INTEGER;
v_info info_index;
BEGIN
v_info(1):='MLDN';
v_info(10):='JAVA';
SYS.DBMS_OUTPUT.PUT_LINE(v_info(1));
SYS.DBMS_OUTPUT.PUT_LINE(v_info(10));
END;
--使用exists函数判断是否存在索引号
DECLARE
TYPE info_index is tableof VARCHAR(20)
index by PLS_INTEGER;
v_info info_index;
BEGIN
v_info(1):='MLDN';
v_info(10):='JAVA';
if v_info.exists(10)then
SYS.DBMS_OUTPUT.PUT_LINE(v_info(1));
end if;
if v_info.exists(30)then
SYS.DBMS_OUTPUT.PUT_LINE(v_info(30));
else
SYS.DBMS_OUTPUT.PUT_LINE('索引号为30的数据不存在!');
end if;
END;
--以VARCHAR2作为索引的下标
DECLARE
TYPE info_index is tableof VARCHAR(50)
index by VARCHAR2(30);
v_info info_index;
BEGIN
v_info('公司名称'):='光大';
v_info('培训项目'):='不动产登记项目';
if v_info.exists('公司名称') then
SYS.DBMS_OUTPUT.PUT_LINE(v_info('公司名称'));
end if;
if v_info.exists('培训项目')then
SYS.DBMS_OUTPUT.PUT_LINE(v_info('培训项目'));
else
SYS.DBMS_OUTPUT.PUT_LINE('未开展培训');
end if;
END
--定义rowtype类型的索引表
DECLARE
TYPE dept_index is tableof dept%rowtype index by PLS_INTEGER;
v_dept dept_index;
BEGIN
v_dept(0).deptno:=80;
v_dept(0).dname:='王八';
v_dept(0).loc:='重庆';
if v_dept.exists(0) then
SYS.DBMS_OUTPUT.PUT_LINE('部门编号:'||v_dept(0).deptno||',名称:'||
v_dept(0).dname||',位置:'||v_dept(0).loc);
end if;
END;
---使用记录类型来定义类型
DECLARE
type dept_type isRECORD(
deptnodept.deptno%type:=80,
dname dept.dname%type,
loc DEPT.LOC%type
);
type dept_index is tableof dept_type index by PLS_INTEGER;
v_dept dept_index;
BEGIN
v_dept(0).deptno:=80;
v_dept(0).dname:='绿豆';
v_dept(0).loc:='王八';
if v_dept.exists(0) then
SYS.DBMS_OUTPUT.PUT_LINE('部门编号:'||v_dept(0).deptno||',名称是:'||
v_dept(0).dname||',位置:'||v_dept(0).loc);
end if;
END;
--关于数组类型那些事
create or replace type project_varray as VARRAY(3) OFVARCHAR2(50);--创建数组类型
CREATE TABLE department(
did number,
deptname VARCHAR2(30) not null,
projects project_varray,--将数组类型赋给变量projects
CONSTRAINT pk_did PRIMARY KEY(did)
);
INSERT INTO department(did,deptname,projects) VALUES (60,'研发',project_varray('不动产',
'OA','房产'));--增加数据的时候要记得加上数组的类型
select * from DEPARTMENT;
--查找一个部门的所有项目
select * from table(
select projects from department where did=60);
--修改一个部门的项目
update DEPARTMENT set PROJECTS=project_varray('不动产','测绘','房产');
--查询更新后的数据
select * from table(
select projects from department where did=60);
---定义复合类型的可变数组
--创建表示项目类型的对象
create or replace type project_type as object(
projectid number,
projectname VARCHAR2(50),
projectfunds number,
pubdate date
);
---定义新的数组类型
CREATE OR REPLACE TYPE project_varray1 as VARRAY(3) ofproject_type;
drop table department purge;
create table department(
did number,
deptname VARCHAR2(50) not null,
projects project_varray1,--将数组类型赋给变量projects
CONSTRAINT pk_did PRIMARY KEY(did)
);
insert into department(did,deptname,projects)values(61,'研发组',project_varray1(
project_type(60,'OA',1212,to_date('2014-02-14','yyyy-mm-dd')),
project_type(62,'房产',1112,to_date('2014-12-14','yyyy-mm-dd'))
));
select * from department;
select * from table(select projects from department wheredid=60);
--在pl/sql中使用可变数组
DECLARE
TYPE project_varray isvarray(3) of VARCHAR2(50);
v_projectsproject_varray :=project_varray(null,null,null);
BEGIN
v_projects(1):='JAVAaaaSE';
v_projects(2):='JAVASEe';
v_projects(3):='JAVaSEr';
for x inv_projects.first .. v_projects.last loop
dbms_output.put_line(v_projects(x));
end loop;
END;
--定义复合结构的可变数组
create or replace type project_type1 as object(
projectid number,
projectname VARCHAR2(50),
projectfunds number,
pubdate date
);
DECLARE
type project_varray isvarray(3) of project_type1 not null;
v_projectsproject_varray :=project_varray(
project_type1('20','王八',82,to_date('1992-10-16','yyyy-mm-dd')),
project_type1('21','绿豆',22,to_date('1993-10-16','yyyy-mm-dd')),
project_type1('23','乌龟',82,to_date('1992-10-16','yyyy-mm-dd'))
);
BEGIN
for x inv_projects.first .. v_projects.last loop
SYS.DBMS_OUTPUT.PUT_LINE('项目编号'||v_projects(x).projectid||',项目名称'||
v_projects(x).projectname||',金额:'||v_projects(x).projectfunds||',日期:'||
v_projects(x).pubdate);
end loop;
END;
--关于集合运算符的那些事
--验证CARDINLITY运算符,计算集合的长度
DECLARE
type list_nested istable of VARCHAR2(50)not null;
v_all list_nested:=list_nested('a','a','b','c','c','d','e');
BEGIN
DBMS_OUTPUT.PUT_LINE('集合长度:'||CARDINALITY(v_all));
END;
--验证CARDINLITY运算符,使用set运算符取消重复数据
DECLARE
type list_nested istable of VARCHAR2(50)not null;
v_all list_nested:=list_nested('a','a','b','c','c','d','e');
BEGIN
DBMS_OUTPUT.PUT_LINE('集合长度:'||CARDINALITY(set(v_all)));
END;
--验证empty运算符是否为空
DECLARE
type list_nested istable of varchar2(50) not null;
v_allA list_nested:=list_nested('cq','gd','bdc');
v_allB list_nested:=list_nested();
BEGIN
if v_allA is not emptythen
dbms_output.put_line('v_allA不是一个空集合');
end if;
if v_allB is empty then
dbms_output.put_line('v_allB是一个空集合');
end if;
END;
--使用member of 运算符,验证子串是否是父串的成员
DECLARE
type list_nested istable of VARCHAR2(50)not null;
v_all list_nested :=list_nested('cq','gd','bdc');
v_strVARCHAR2(10):='cq';
BEGIN
if v_str member of v_allthen
dbms_output.put_line('我是它的成员哦');
end if;
END;
--验证MULTISET EXCEPT运算符,除去except后面的集合剩下的集合
DECLARE
type list_nested istable of VARCHAR2(50)not null;
v_allA list_nested:=list_nested('cq','gd','bdc');
v_allB list_nested:=list_nested('gd','bdc');
v_newlist list_nested;
BEGIN
v_newlist:=V_ALLAMULTISET except v_allB;
for x in1..v_newlist.count loop
dbms_output.put_line(v_newlist(x));
end loop;
END;
--验证multiset intersect运算符,求两个集合的交集
DECLARE
type list_nested istable of VARCHAR2(50)not null;
v_allA list_nested:=list_nested('cq','gd','bdc');
v_allB list_nested:=list_nested('gd','bdc');
v_newlist list_nested;
BEGIN
v_newlist:=V_ALLAMULTISET intersect v_allB;
for x in1..v_newlist.count loop
dbms_output.put_line(v_newlist(x));
end loop;
END;
---验证multiset union 运算符,将两个集合都展示出来
DECLARE
type list_nested istable of VARCHAR2(50)not null;
v_allA list_nested:=list_nested('cq','gd','bdc');
v_allB list_nested:=list_nested('gd','bdc');
v_newlist list_nested;
BEGIN
v_newlist:=V_ALLAMULTISET union v_allB;
for x in1..v_newlist.count loop
dbms_output.put_line(v_newlist(x));
end loop;
END;
--set运算符,验证是否是一个集合
DECLARE
type list_nested istable of VARCHAR2(50)not null;
v_allA list_nested :=list_nested('cq','gd','bdc');
BEGIN
if v_allA is a set then
dbms_output.put_line('我是一个集合哟');
end if;
END;
--验证submultiset运算符,验证B是不是A的子集
DECLARE
type list_nested istable of varchar2(50)not null;
v_allA list_nested:=list_nested('cq','gd','bdc');
v_allB list_nested:=list_nested('gd','bdc');
BEGIN
if V_ALLB submultisetv_allA then
SYS.DBMS_OUTPUT.PUT_LINE('v_allB是v_allA的一个子集合。');
end if;
END;
--关于集合运算符的那些事
--验证CARDINLITY运算符,计算集合的长度
DECLARE
type list_nested istable of VARCHAR2(50)not null;
v_all list_nested:=list_nested('a','a','b','c','c','d','e');
BEGIN
DBMS_OUTPUT.PUT_LINE('集合长度:'||CARDINALITY(v_all));
END;
--验证CARDINLITY运算符,使用set运算符取消重复数据
DECLARE
type list_nested istable of VARCHAR2(50)not null;
v_all list_nested:=list_nested('a','a','b','c','c','d','e');
BEGIN
DBMS_OUTPUT.PUT_LINE('集合长度:'||CARDINALITY(set(v_all)));
END;
--验证empty运算符是否为空
DECLARE
type list_nested istable of varchar2(50) not null;
v_allA list_nested:=list_nested('cq','gd','bdc');
v_allB list_nested:=list_nested();
BEGIN
if v_allA is not emptythen
dbms_output.put_line('v_allA不是一个空集合');
end if;
if v_allB is empty then
dbms_output.put_line('v_allB是一个空集合');
end if;
END;
--使用member of 运算符,验证子串是否是父串的成员
DECLARE
type list_nested istable of VARCHAR2(50)not null;
v_all list_nested:=list_nested('cq','gd','bdc');
v_strVARCHAR2(10):='cq';
BEGIN
if v_str member of v_allthen
dbms_output.put_line('我是它的成员哦');
end if;
END;
--验证MULTISET EXCEPT运算符,除去except后面的集合剩下的集合
DECLARE
type list_nested is table of VARCHAR2(50)not null;
v_allA list_nested:=list_nested('cq','gd','bdc');
v_allB list_nested:=list_nested('gd','bdc');
v_newlist list_nested;
BEGIN
v_newlist:=V_ALLAMULTISET except v_allB;
for x in1..v_newlist.count loop
dbms_output.put_line(v_newlist(x));
end loop;
END;
--验证multiset intersect运算符,求两个集合的交集
DECLARE
type list_nested istable of VARCHAR2(50)not null;
v_allA list_nested:=list_nested('cq','gd','bdc');
v_allB list_nested:=list_nested('gd','bdc');
v_newlist list_nested;
BEGIN
v_newlist:=V_ALLAMULTISET intersect v_allB;
for x in1..v_newlist.count loop
dbms_output.put_line(v_newlist(x));
end loop;
END;
---验证multiset union 运算符,将两个集合都展示出来
DECLARE
type list_nested istable of VARCHAR2(50)not null;
v_allA list_nested:=list_nested('cq','gd','bdc');
v_allB list_nested:=list_nested('gd','bdc');
v_newlist list_nested;
BEGIN
v_newlist:=V_ALLAMULTISET union v_allB;
for x in1..v_newlist.count loop
dbms_output.put_line(v_newlist(x));
end loop;
END;
--set运算符,验证是否是一个集合
DECLARE
type list_nested istable of VARCHAR2(50)not null;
v_allA list_nested :=list_nested('cq','gd','bdc');
BEGIN
if v_allA is a set then
dbms_output.put_line('我是一个集合哟');
end if;
END;
--验证submultiset运算符,验证B是不是A的子集
DECLARE
type list_nested istable of varchar2(50)not null;
v_allA list_nested:=list_nested('cq','gd','bdc');
v_allB list_nested:=list_nested('gd','bdc');
BEGIN
if V_ALLB submultisetv_allA then
SYS.DBMS_OUTPUT.PUT_LINE('v_allB是v_allA的一个子集合。');
end if;
END;
--集合函数
--使用count函数获取元素个数
DECLARE
type list_nested istable of VARCHAR2(50)not null;
v_all list_nested :=list_nested('cq','gd','sjb','ljp','oracle');
BEGIN
SYS.DBMS_OUTPUT.PUT_LINE('集合长度:'||v_all.count);
END;
--使用delete函数删除一个数据
DECLARE
type list_nested istable of VARCHAR2(50)NOT NULL;
v_all list_nested :=list_nested('wb','lv','wbd','ebw');
BEGIN
v_all.delete(1);
for x in v_all.first..v_all.last
loop
dbms_output.put_line(v_all(x));
end loop;
END;
--删除指定范围的数据
DECLARE
type list_nested istable of VARCHAR2(50)NOT NULL;
v_all list_nested :=list_nested('wb','lv','wbd','ebw');
BEGIN
v_all.delete(1,3);
for x in v_all.first..v_all.last
loop
dbms_output.put_line(v_all(x));
end loop;
END;
--判断某个索引位置数据是否存在
DECLARE
type list_nested istable of VARCHAR2(50)NOT NULL;
v_all list_nested :=list_nested('wb','lv','wbd','ebw');
BEGIN
if v_all.exists(1)then
dbms_output.put_line('索引为1的数据存在');
end if;
if notv_all.exists(10)then
dbms_output.put_line('索引为10的数据不存在');
end if;
END;
--扩充集合长度
DECLARE
type list_nested istable of VARCHAR2(50)NOT NULL;
v_all list_nested :=list_nested('wb','lv','wbd','ebw');
BEGIN
v_all.extend(1);
v_all(5):='2b';
dbms_output.put_line('集合长度:'||v_all.count);
for x in v_all.first..v_all.last
loop
dbms_output.put_line(v_all(x));
end loop;
END;
--扩充集合长度,并使用已有内容进行填充
DECLARE
type list_nested istable of VARCHAR2(50)NOT NULL;
v_all list_nested :=list_nested('wb','lv','wbd','ebw');
BEGIN
v_all.extend(2,1);
dbms_output.put_line('集合长度:'||v_all.count);
for x in v_all.first..v_all.last
loop
dbms_output.put_line(v_all(x));
end loop;
END;
--使用LIMIT取得集合的最高下标
declare
type list_varray isvarray(8) of varchar2(50);
v_info list_varray:=list_varray('as','asa','hh','jj','ee','rr','hh','dd');
begin
dbms_output.put_line('数据的集合长度:'||v_info.limit);
dbms_output.put_line('数据的集合数据量:'||v_info.count);
end;
--验证next函数
DECLARE
type info_index istable of VARCHAR2(20)index by PLS_INTEGER;
v_info info_index;
v_foot number;
BEGIN
v_info(1):='wb';
v_info(10):='wg';
v_info(-10):='wb';
v_info(-20):='ld';
v_info(30):='gng';
v_foot:=v_info.first;
while(v_info.exists(v_foot))loop
dbms_output.put_line('v_info('||v_foot||')='||v_info(v_foot));
v_foot:=v_info.next(v_foot);
end loop;
dbms_output.put_line('索引为1的索引是。。。'||v_info.first);
dbms_output.put_line('索引为10的下一个索引是。。。'||v_info.next(10));
dbms_output.put_line('索引为的-10的上一个索引是。。。'||v_info.prior(-10));
END;
--验证trim函数
declare
type list_varray isvarray(8) of varchar2(50);
v_info list_varray :=list_varray('cq','gd','sjb','ljp');
begin
dbms_output.put_line('删除集合之前的数据量:'||v_info.count);
v_info.trim;
dbms_output.put_line('v.info.trim删除集合后的数据量'||v_info.count);
v_info.trim(2);
dbms_output.put_line('v_info.trim(2)删除集合后的数据量'||v_info.count);
end;
--集合异常处理
DECLARE
type list_varray isvarray(8) of varchar2(50);
v_info list_varray;--集合初始化的位置
BEGIN
v_info(0):=10;--集合未初始化,所以产生异常
EXCEPTION
when collection_is_nullthen
DBMS_OUTPUT.PUT_LINE('集合未初始化,无法使用!');
END;
---处理索引超过集合长度的异常
DECLARE
type list_varray is varray(8)of varchar2(50);
v_infolist_varray:=list_varray('a','b');--集合初始化的位置
BEGIN
SYS.DBMS_OUTPUT.PUT_LINE(v_info(3));
EXCEPTION
whensubscript_beyond_count then
DBMS_OUTPUT.PUT_LINE('索引值超过定义的元素个数,无法检索!');
END;
--处理访问索引超过集合最大定义长度的异常
DECLARE
type list_varray isvarray(8) of varchar2(50);
v_info list_varray :=list_varray('aa','bb');
BEGIN
DBMS_OUTPUT.PUT_LINE(v_info(30));
exception
whensubscript_outside_limit then
dbms_output.put_line('索引值超过定义集合类型的最大元素个数');
END;
--设置错误的索引数据
declare
type list_varray is varray(8) of varchar2(50);
v_info list_varray :=list_varray('aa','bb');
begin
DBMS_OUTPUT.PUT_LINE(v_info('1'));--可以自动转换为数字PLS_INTEGER
DBMS_OUTPUT.PUT_LINE(v_info('a'));--a无法转换为数字
EXCEPTION
when value_error then
dbms_output.put_line('索引值类型错误');
end;
---处理索引表集合中访问已删除数据集合的异常
DECLARE
type info_index istable of varchar2(50) index by PLS_INTEGER;
v_info info_index;
BEGIN
v_info(1):='aa';
v_info(2):='bb';
v_info(3):='cc';
v_info.delete(1);
dbms_output.put_line(v_info(1));
dbms_output.put_line(v_info(2));
dbms_output.put_line(v_info(3));
EXCEPTION
when no_data_foundthen
dbms_output.put_line('此数据已经被删除');
END;
--使用forall批量绑定
--通过程序块来更新数据库
DECLARE
type emp_varray is varray(8)of EMP.EMPNO%TYPE;
v_empno emp_varray:=emp_varray(7369,7566,7788,7839,7902);
BEGIN
for x in v_empno.first.. v_empno.last
loop
update emp set sal=9000where empno=v_empno(x);
end loop;
END;
--利用forall向数据库一次性发出多条语句
DECLARE
type emp_varray isvarray(8) of EMP.EMPNO%TYPE;
v_empno emp_varray:=emp_varray(7369,7566,7788,7839,7902);
BEGIN
forall x inv_empno.first..v_empno.last
update emp set sal=9000where empno=v_empno(x);
for x inv_empno.first..v_empno.last loop
dbms_output.put_line('雇员编号:'||v_empno(x)||'更新受影响的行数'||sql%BULK_ROWCOUNT(X));
end loop;
END;
--bulkcollect 批量接收数据
--批量接收查询数据
DECLARE
type ename_varray isvarray(8) of EMP.ENAME%TYPE;
v_ename ename_varray;
BEGIN
select ename bulkcollect into v_ename from emp where deptno=10;
for x inv_ename.first..v_ename.last loop
dbms_output.put_line('10部门雇员姓名:'||v_ename(x));
end loop;
END;
---游标
--ROWCOUNT 返回查询记录的行数
DECLARE
v_count number;
BEGIN
select count(*) intov_count from dept;
DBMS_OUTPUT.PUT_LINE('sql%rowcount='||SQL%ROWCOUNT);
END;
--验证rowcount,增加新数据并返回行数
DECLARE
BEGIN
INSERT INTOdept(deptno,dname,loc) VALUES (60,'gd','cq');
DBMS_OUTPUT.PUT_LINE('SQL%ROWCOUNT='||sql%rowcount);
END;
--单行隐式游标
DECLARE
v_emprow emp%ROWTYPE;
BEGIN
select * into v_emprowfrom emp WHERE empno=7369;
if sql%found then--found当用户使用dml操作数据时,该属性返回true
DBMS_OUTPUT.PUT_LINE('雇员姓名:'||v_emprow.ename||',职位:'||v_emprow.job);
end if;
END;
--多行隐式游标
BEGIN
update emp setsal=sal*1.2;--更新多行记录
if sql%found then
dbms_output.put_line('更新记录行数:'||SQL%rowcount);
else
SYS.DBMS_OUTPUT.PUT_LINE('没有记录被修改!');
end if;
END;
--显示游标
DECLARE
cursor cur_emp is select* from emp;
v_emprow emp%rowtype;
BEGIN
if cur_emp%isopen then--游标已经打开
null;
else--游标未打开
open cur_emp;--打开游标
end if;
FETCH cur_emp INTOv_emprow;--取出游标当前行数据
while cur_emp%foundloop--判断是否能找到数据
SYS.DBMS_OUTPUT.PUT_LINE(cur_emp%rowcount||'、雇员姓名:'||v_emprow.ename
||',职位:'||v_emprow.job||',工资:'||v_emprow.sal);
fetch cur_emp intov_emprow;--把游标指向下一行
end loop;
close cur_emp;--关闭游标
END;
---使用loop循环输出游标
DECLARE
cursor cur_emp is select* from emp;
v_emprow emp%rowtype;
BEGIN
if cur_emp%isopen then
null;
else
open cur_emp;--打开游标
end if;
loop
fetch cur_emp intov_emprow;--取出游标当前行数据
exit whencur_emp%notfound;--如果没有找到数据则退出循环
SYS.DBMS_OUTPUT.PUT_LINE(cur_emp%rowcount||'、雇员姓名:'||v_emprow.ename
||',职位:'||v_emprow.job||',工资:'||v_emprow.sal);
end loop;
close cur_emp;--关闭游标
END;
--尽量使用for循环操作游标重要
DECLARE
cursor cur_emp is select* from emp;
BEGIN
for emp_row in cur_emp
loop
SYS.DBMS_OUTPUT.PUT_LINE(cur_emp%rowcount||'、雇员姓名:'||emp_row.ename
||',职位:'||emp_row.job||',工资:'||emp_row.sal);
end loop;
END;
---将游标数据保存在索引表中。
DECLARE
CURSOR cur_emp is select* from emp;---定义游标取得emp表数据
type emp_index is tableof emp%rowtype index by PLS_INTEGER;--定义索引表数据类型为emp行结构
v_emp emp_index;--定义索引表变量
BEGIN
for emp_row in cur_emp--利用循环取得每一行记录
loop
v_emp(emp_row.empno):=emp_row;--将雇员编号作为索引表下标
end loop;
SYS.DBMS_OUTPUT.PUT_LINE('雇员姓名:'||v_emp(7369).empno
||',职位:'||v_emp(7369).job||',工资:'||v_emp(7369).sal);
END;
--在动态select中使用游标
DECLARE
v_lowsalemp.sal%type:=&inputlowsal;
v_highsalemp.sal%type:=&inputhighsal;
CURSOR cur_emp is select* from emp where sal between v_lowsal and v_highsal;
BEGIN
for emp_row in cur_emploop
SYS.DBMS_OUTPUT.PUT_LINE(cur_emp%rowcount||'、雇员姓名:'||emp_row.ename
||',职位:'||emp_row.job||',工资:'||emp_row.sal);
end loop;
END;
--定义参数游标
DECLARE
cursor cur_emp(p_dnoemp.deptno%type)is select *from emp where DEPTNO=p_dno;--定义参数
BEGIN
for emp_row incur_emp(&inputdeptno)loop--传递参数
SYS.DBMS_OUTPUT.PUT_LINE(cur_emp%rowcount||'、雇员姓名:'||emp_row.ename
||',职位:'||emp_row.job||',工资:'||emp_row.sal);
end loop;
END;
--使用嵌套表接收游标数据
DECLARE
type dept_nested istable of dept%rowtype;--定义dept的嵌套表类型
v_dept dept_nested;
cursor cur_dept isselect * from dept;--定义游标
BEGIN
if cur_dept%isopenthen--游标是否打开,如果是打开的就为空,不是就打开游标
null;
else
open cur_dept;
end if;
fetch cur_dept bulkcollect into v_dept;
close cur_dept;
for x inv_dept.first..v_dept.last loop
dbms_output.put_line('部门编号:'||v_dept(x).deptno||',部门名称:'||v_dept(x).dname);
end loop;
END;
--取得部分数据保存在数组中
DECLARE
type dept_varray isvarray(8) of dept%rowtype;
v_dept dept_varray;
v_rows number :=4;--每次提取的行数,从四行数据中取前三行
v_count number :=1;--每次至少显示一条记录
cursor cur_dept isselect * from dept;--定义游标
BEGIN
if cur_dept%isopen then
null;
else
open cur_dept;
end if;
fetch cur_dept bulkcollect into v_dept limit v_rows;--保存指定行数
close cur_dept;
for x inv_dept.first..(v_dept.last-v_count)loop
dbms_output.put_line('部门编号:'||v_dept(x).deptno||',部门名称:'||v_dept(x).dname);
end loop;
END;
---修改游标数据
DECLARE
cursor cur_emp is select* from emp;--emp表游标数据
BEGIN
for emp_row in cur_emploop
if emp_row.deptno=10then
ifemp_row.sal*1.15<5000 then
update emp setsal=sal*1.15 where empno=emp_row.empno;
else
update emp set sal=5000where empno=emp_row.empno;
end if;
elsif emp_row.deptno=20then
ifemp_row.sal*1.22<5000 then
update emp setsal=sal*1.22 where empno=emp_row.empno;
else
update emp set sal=5000where empno=emp_row.empno;
end if;
elsif emp_row.deptno=30then
ifemp_row.sal*1.39<5000 then
update emp setsal=sal*1.39 where empno=emp_row.empno;
else
update emp setsal=5000 where empno=emp_row.empno;
end if;
else
null;
end if;
end loop;
EXCEPTION when othersthen
SYS.DBMS_OUTPUT.PUT_LINE('SQLCODE='||SQLCODE);
SYS.DBMS_OUTPUT.PUT_LINE('SQLERRM='||SQLERRM);
ROLLBACK;
END;
select * from emp e,dept d where E.DEPTNO=D.DEPTNO;
--使用where current of子句
DECLARE
cursor cur_emp is SELECT* FROM emp where deptno=10 for update of sal,comm;
BEGIN
for emp_row in cur_emploop
update emp set sal=9999where current of cur_emp;
end loop;
END;
---使用游标删除数据
DECLARE
cursor cur_emp is select* from emp where deptno=10 for update of sal,comm;
BEGIN
for emp_row in cur_emploop
delete from emp wherecurrent of cur_emp;
end loop;
END;
--创建一个新的游标使用for update,采用多表查询
DECLARE
cursor cur_emp is selecte.ename,e.job,e.sal,d.dname,d.loc from emp e,dept d
where e.deptno=10 andd.deptno=E.DEPTNO for update of sal;---要想更新成功数据必须要加上更新的数据列
BEGIN
for emp_row in cur_emploop
update emp set sal=9999where current of cur_emp ;
end loop;
END;
DECLARE
type dept_ref is refcursor return dept%rowtype;--定义游标类型
cur_dept dept_ref;--定义游标变量
v_deptrowdept%rowtype;--定义行类型
BEGIN
open cur_dept forselect * from dept;--打开游标
loop
fetch cur_dept intov_deptrow;--取得游标数据
exit whencur_dept%notfound;--如果没有数据则退出
SYS.DBMS_OUTPUT.PUT_LINE('雇员姓名:'||v_deptrow.dname ||',工资:'||v_deptrow.loc);
end loop;
close cur_dept;
END;