学习plsqp过程

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;