网站导航
> 文档中心 > 关于PL/SQL我写了一份从0到1的入门教程

关于PL/SQL我写了一份从0到1的入门教程

网友: 文档中心 2022-04-11 16:12:51

PL/SQL

什么是PL/SQL

​许多时候我们会利用结构化查询语言（SQL）来访问和操作关系型数据库。这种语言的特点就是非过程化。也就是说使用的时候不用指明执行的具体方法和途径，即不用关注任何的实现细节。但这种语言也有一个问题，就是在某些情况下满足不了复杂业务流程的需求。
​所以就出现了PL/SQL，Oracle的PL/SQL语言正是完美的解决了这个问题，也就是过程化语言。和JAVA、C#等语言一样可以关注细节，用它可以实现复杂的业务逻辑。

PL/SQL的优点

​使用PL/SQL可以编写具有很多高级功能的程序，虽然这些功能可以通过多个SQL语句来完成同样的功能，但是PL/SQL具有以下优点：

  • 使一组语句功能形成模块化程序开发
  • 使用过程性语言控制程序结构
  • 可以对程序中的错误进行处理
  • 具有较好的可移植性
  • 集成在数据中，调用更快
  • 减少了网络的交互，有助于提高程序的性能

PL/SQL提供的新特性

PL/SQL提供了一些新的特性，可以进行复杂的信息处理

  1. 软件包
  2. 触发器
  3. 存储过程
  4. 函数
  5. 异常处理

PL/SQL可以使用所有的SQL数据操作，游标控制和事务控制命令，以及所有的SQL函数和运算符。PL/SQL完全支持SQL数据类型

PL/SQL块的基本结构

PL/SQL中起作用的部分都是由基本块组成的：基本块有四个组成部分：--声明部分：DECLARE -- 可选部分-- 变量、常量、游标、用户定义异常声明-- 执行体开始部分BEGIN -- 必要部分-- SQL语句-- PL/SQL语句-- 异常处理部分EXCEPTION -- 可选部分-- 程序出现异常时，捕捉异常并处理异常-- 执行体结束END; -- 必要部分（结尾）

在PL/SQL中处理变量

  • 在声明部分声明和初始化变量
  • 在执行部分为变量赋新值，或在表达中使用变量
  • 在异常处理部分也可以使用变量
  • 通过参数把值传递到PL/SQL块中
  • 通过输出变量或者参数将值传出PL/SQL块
DECLARE-- 在declare部分声明变量，常量等-- 声明 变量的规范：  变量名称 变量类型 [:=缺省值];v_deptno number;BEGIN-- 在 begin部分可以写 SQL语句，PL/SQL语句-- 在 begin部分中可以使用declare部分声明的变量，常量dbms_output.put_line('欢迎使用PL/SQL，执行查询语句之前，v_deptno=' || v_deptno);-- dbms_output.put_line();是输出语句，单引号包起来的将原封不动的给我们，|| 是拼接符select deptno into v_deptno from emp where empno = 7369;-- PL/SQL语句-- 把查询语句查询的结果赋值给v_deptno这个变量dbms_output.put_line('执行查询语句之后，v_deptno=' || v_deptno);delete from emp where deptno = v_daptno;delete from emp where deptno = v_daptno;END;

在PL/SQL中处理变量

record类型

declare    -- 声明记录类型    type aaa is record( empno number(4), ename varchar2(50), sal number(4)    ); -- 这TM不就跟我们创建表一样吗？只是不写约束    --使用记录类型来声明变量    aaa_info aaa; -- aaa_info 是我取得变量名，aaa是我上面声明的记录类型。begin-- 给记录类型的变量赋值，只有类型中声明好的字段，才可以访问该字段select empno,ename,sal into aaa_info  from emp where empno=7aaa_info.empno:=10;aaa_info.ename:='张三';aaa_info.sal:=8000;end;/*recode类型是由多个组件组成的一种类型，包含一个或多个组件，每一个组件称为一个域（fiele）,域的数据类型可以是简单变量类型或是table类型。在使用record变量时把多个域的集合作为一个逻辑单元使用，对记录类型变量（%type）赋值或引用，都需要使用‘记录变量.域名’的方式来实现。主要用于从表中取出查询到的行数据。记录类型可以包含一个或多个域，每个域相当于记录类型变量的一个属性。在使用记录变量类型时，实际上是对记录类型变量的属性进行操作。每个域都可以是不同的数据类型，存放不同类型的数据。*/

%type和%rowtype

/*除了可以使用已经确定的类型来声明变量，还可以使用%type和%rowtype来作为变量的类型。当使用%type来声明变量的时候，%type的前缀可以是一个前面已经声明的简单类型的量，也可以是一个表的字段名称。*/ declare a number(4); -- 这tm就是简单变量 b a%type; -- %type 也tm可以这么用beginend;-- 还能特么这么用declare a emp.empno%type; -- a为我的变量名，emp是我的表名，emp.empno是我表中的字段。后面加个%type，意思就是用emp表中empno这个字段的数据类型作为变量名a的数据类型。beginend;/*%rowtype 前缀可以是一个表名，也可以是前面声明的一个记录类型的变量（该变量必须参照一个表，而不是自定义的记录类型）*/declare    --e是我取的变量名，emp是我的表 row是行，type是类型。那rowtype就是所有行的类型    -- 那这里的emp%rowtype 就是表示 e可以用emp表里行数据中的所有字段    e emp%rowtype;    -- 在使用%rowtype的时候，Oracle做了两件事：    -- 1.用emp表中的字段及其类型来声明了一种记录类型    -- 2.用这种记录来声明变量begin    select * into e  from emp where empno=7499;    dbms_output.put_line(e.ename||e.sal);end;

逻辑关系

IF语句

/*IF语句的逻辑条件可以是一个逻辑表达式，也可以由多个逻辑表达式连接在一起，连接多个表达式的符号就是连接操作符，包括and(与)or(或)not(非)*/declarev_total_sal number(9,2):=0; -- PL/SQL中用 := 赋值c_tax_rate constant number(3,2) :=8.25 -- PL/SQL中的常量只能赋值一次     -- 常量格式  常量名 constant 数据类型 := 默认值    v_gender char(1);    v_valid boolean not null := true;-- 布尔类型    v_b boolean;    v_num1 number(2) := 10;    v_num2 number(2) := 10;begindbms_output.put_line('v_total_sal=' || v_total_sal);-- 可以把变量名通过输出语句来查看值v_b := (v_num1=v_num2);-- 这里的 = 相当于Java中的 == 的功能，执行 逻辑比较  操作if(v_b = true) then -- if 后面的表达式为true就执行 then后面的语句dbms_output.put_line('OK');else -- 反之 执行else中的语句dbms_output.put_line('NOT OK');end if;end;

循环语句

/*循环用于多次执行某些语句。主要由三种循环类型：简单循环for循环while循环循环的四个条件：1、初始条件2、循环条件3、循环体4、迭代条件*/declarei number(3):=1; -- 出生条件beginloopdbms_output.put_line(i); --循环体i := i+1; -- 迭代条件exit when i>10; -- 循环条件end loop;end;/*利用loop循环，向users表中插入100条数据，如果编号是奇数，性别为男，如果编号是偶数，性别为女。*/declarev_sex varchar(5);v_count number(3):=1;v_name varchar(50);beginloopif(mod(v_count,2)=0) thenv_sex:='女';elsev_sex:='男';end if;v_name :='王'||v_count;insert into users values(v_count,v_name,v_sex);v_count := v_count +1;exit when v_count>100;end loop;end;truncate table users;-- 清空表/*while循环*/declarev_count number(3):=1; -- 初始条件beginwhile (v_count <= 10) loop -- 循环条件dbms_output.put_line(v_count); -- 循坏体v_count := v_cout+1; -- 迭代条件end loop;end;/*利用while循环，向users表中插入100条数据，如果编号是奇数，性别为男，如果编号是偶数，性别为女。*/declarev_count number(3):=1; -- 初始条件v_sex varchar2(2);v_name varchar2(50);beginwhile (v_count <= 100) loop -- 循环条件if(mod(v_count,2) = 0) thenv_sex:='女';elsev_sex:='男';end if;v_name :='王'||v_count;insert into users values(v_count,v_name,v_sex);v_count := v_cout+1; -- 迭代条件end loop;commit;end;/*for循环在使用for循环的时候，由Oracle来维护计数器for 变量 in[reverse] 1..100 loop上面的reverse是倒序的意思end loop;*/declarev_sex varchar2(3);v_name varchar2(50);begin-- Oracle会自动的声明计数器，会自动的进行计数器的迭代for v_count in 1..100 loopif(mod(v_count,2) = 0) thenv_sex:='女';elsev_sex:='男';end if;v_name :='王'||v_count;insert into users values(v_count,v_name,v_sex);end loop;commit;end;

SQL游标

/*在执行执行增删改查语句的时候，Oracle都会开辟一块内存空间，用来暂时存放收到SQL语句影响的数据。这块内存空间就被称为游标区域，我们可以借助于游标来分析这些受到影响的数据*//*游标的分类：1、隐式游标：增删改查语句都会由隐式游标，也就是说，我们可以通过隐式游标来分析受到增删改查语句影响的数据。2、显式游标：在PL/SQL种执行select语句的特殊要求：(1):select语句只能返回一条记录(2):必须搭配使用into也就是说：显示游标专门用来从数据库种查询多条数据的；*/

游标的属性

/*游标属性包括四种：%rowcount受SQL影响的行数%foundboolean值，是否还有数据%notfoundboolean值，是否已无数据%isopen游标是否打开显示游标和隐式游标都有这四个属性。但是使用方法和含义却不相同。*/

隐式游标

/*在使用游标的时候，需要使用游标名称作为前缀。但是隐式游标没有名称，所以在使用隐式游标的时候采取统一的一个名称SQL。也就是说：隐式游标同意使用SQL前缀，例如：SQL%rowcount受SQL影响的行数SQL%foundboolean值，是否还有数据SQL%notfoundboolean值，是否已无数据SQL%isopen总是false*/declarev_count number(3);begindelete from emp where deptno = 10;v_count :=SQL%rowcount;dbms_output.put_line('被删除的数据的条数是：'|| v_count);end;

显示游标

/*显式游标的使用：1、可以用于暂存查询取出的多行结果，然后一行一行的处理。2、显示游标就是专门用来查询多条数据的3、按行处理查询返回的多行结果4、显示游标首先将查询出的多行数据暂存在游标区域中，然后在PL/SQL中借助循环语句手动的控制游标的多行操作，每次取出一条进行处理，直到取出游标中所有的数据为止。显式游标和隐式游标不同，在调用隐式游标的时候，通过SQL前缀来调用（SQL%rowcount），而显式游标都有自己的名称，在调用时使用显示游标的名称，作为属性的前缀（游标名%rowcount）。*/declare-- 1、声明游标，一个显示游标，就是和一个有效的select语句绑死的 cursor cur_emp is select * from emp; v_emp emp%rowtype;begin-- 2、打开游标，就是执行了游标绑定的SQL语句，并且把受到影响的数据放入到了游标区域中open cur_emp;-- 3、取出游标中的一条数据装入记录类型的变量中fetch cur_emp into v_emp;-- 从记录类型的变量中取出查询数据dbms_output.put_line(v_emp.empno||','||v_emp.ename||','||    v_emp.job||','||v_emp.mgr||','||v_emp.hiredate||','||    v_emp.sal||','||nvl(v_emp.comm,0)||','||v_emp.deptno);     -- 关闭游标，清空游标区域     close cur_emp;   -- 那么问题来了，我们不是说可以从显式游标中取出多条数据吗？但我们只取了一条，那是因为我们没有用循环。end;-- 使用loop循环和%notfound来遍历游标declare-- 1、声明游标，一个显示游标，就是和一个有效的select语句绑死的 cursor cur_emp is select * from emp; v_emp emp%rowtype;begin--dbms_output.put_line('查询游标的总条数'||cur_emp%rowtype);-- 在游标没有打开之前或者关闭之后，是无法使用的，就会导致无效的游标错误-- 在关闭之后，如果需要重新使用游标，需要重新打开游标-- 2、打开游标，就是执行了游标绑定的SQL语句，并且把受到影响的数据放入到了游标区域中open cur_emp;loop -- 3、取出游标中的一条数据装入记录类型的变量中 fetch cur_emp into v_emp; exit when cur_emp%notfound; -- 当游标中没有数据的时候，退出循环。 dbms_output.put_line(v_emp.empno||','||v_emp.ename||','|| v_emp.job||','||v_emp.mgr||','||v_emp.hiredate||','|| v_emp.sal||','||nvl(v_emp.comm,0)||','||v_emp.deptno);end loop;dbms_output.put_line('查询游标的总条数'||cur_emp%rowcount);close cur_emp;--dbms_output.put_line('查询游标的总条数'||cur_emp%rowtype);end;-- 使用while循环和%found搭配使用分析游标数据declare cursor cur_emp is select * from emp;v_emp emp%rowtype;beginopen cur_emp;fetch cur_emp into v_emp;--取出游标中的一条数据装入记录类型的变量中-- 如果把这句放在while里面，那么游标里面的值就是0while(cur_emp%found) loop-- fetch cur_emp into v_emp;dbms_output.put_line(v_emp.empno||','||v_emp.ename||','||    v_emp.job||','||v_emp.mgr||','||v_emp.hiredate||','||    v_emp.sal||','||nvl(v_emp.comm,0)||','||v_emp.deptno);  fetch cur_emp into v_emp;--迭代，如果没有的话，就会一直存入第一条数据，造成内容溢出。    end loop;dbms_output.put_line('查询游标的总条数'||cur_emp%rowcount);close cur_emp;end;-- 使用for循环，可以简化游标的开发，Oracle会自动的声明记录类型的变量，Oracle会自动的open，fetch、close游标。declare cursor cur_emp is select * from emp;begin-- Oracle会自动的声明记录类型的变量v_emp,类型是emp%rowtype；-- for循环会自动 open for v_emp in cur_emp loopdbms_output.put_line(v_emp.empno||','||v_emp.ename||','||    v_emp.job||','||v_emp.mgr||','||v_emp.hiredate||','||    v_emp.sal||','||nvl(v_emp.comm,0)||','||v_emp.deptno);end loop;end;-- 带参数的游标/*也就是带条件的游标，因为有时候我们不需要查询所有。*/declarecursor cur_emp(v_deptno number) is select * from emp where deptno=v_deptno;v_emp emp%rowtype;beginopen cur_emp(20);--查询20部门loop fetch cur_emp into v_emp;dbms_output.put_line(v_emp.empno||','||v_emp.ename||','||    v_emp.job||','||v_emp.mgr||','||v_emp.hiredate||','||    v_emp.sal||','||nvl(v_emp.comm,0)||','||v_emp.deptno);     exit when cur_emp%notfound; -- 但游标中没有数据的时候，退出循环。end loop;end;

使用游标更新或删除行数据

允许使用游标删除或更新活动集中的行声明游标时必须使用 select....for update 语句declarev_emp emp%rowtype;cursor mycur is select * from emp where deptno=20 for update;beginopen mycur;fetch mycur into v_emp;while mycur%found loop-- where current of 游标名; 对游标读取的所在行进行更新和删除update emp set sal = 100 where current of mycur;fetch mycur into v_emp;end loop;close mycur;end;

REF游标

/*REF 游标*/-- 查询所有的员工信息declare-- 声明 ref 游标类型type myret is ref cursor;-- 声明ref游标类型的变量cur_emp myret;-- 声明变量v_emp emp%rowtype;begin-- 开启 ref 游标open cur_emp for select * from emp;fetch cur_emp into v_emp;while cur_emp%found loopdbms_output.put_line(v_emp.empno||','||v_emp.ename||','||    v_emp.job||','||v_emp.mgr||','||v_emp.hiredate||','||    v_emp.sal||','||nvl(v_emp.comm,0)||','||v_emp.deptno);  fetch cur_emp into v_emp;end loop;close cur_emp;end;

集合类型

/*集合的定义：  集合是相同类型元素的组合。在集合中，使用唯一的下标来标识其中的每个元素。  而集合又分为以下几个类型：1、索引表也叫关联数组2、嵌套表3、可变长度数组*/

索引表

/*索引表：可以使用整数（pls_integer,binary_integer）或字符串当下标，下标可以是不连续的。索引表的元素的个数是无限制的索引表只能用在plsql中，不能保存再数据库定义索引表类型的语法:type 类型名称 is table of 数据类型（是集合中值的数据类型） index by 下标的数据类型声明一个集合变量：集合变量的名字  集合的类型；集合中数据的存取:集合变量(下标)*/declare-- 定义索引表类型type indextable is table of varchar2(20) index by pls_integer;-- 声明集合变量v_indextable indextable;begin-- 使用：集合变量（下标） 存储值或者赋值v_indextable(1):='a';v_indextable(3):='b';v_indextable(5):='c';v_indextable(-8):='d';-- 取值:集合变量（下标）dbms_output.put_line(v_indextable(1));dbms_output.put_line(v_indextable(3));dbms_output.put_line(v_indextable(5));dbms_output.put_line(v_indextable(-8));end;

集合的属性和方法

/*集合名字.属性名   集合名字.方法名1.first取集合的第一元素的下标2.last取集合的最后一个元素的下标3.count 取集合的总长度 4.limit 取集合元素索引的最大值  (索引表和嵌套表是不限个数的，所以返回null，变长数组返回定义时的最大索引 )5.delete([n]) 删除集合中的元素，加n表示下标，删除对应下标的值6.extend(n[,ind]):扩展集合元素 n是一个数字，表示扩展的元素个数，ind是集合中的一个元素的下标，加上它表示扩展集合时，给扩展的元素加上值，值是ind这个下标对应的元素7.next(下标)取当前元素下一个元素的下标8.prior(下标)取当前元素上一个元素的下标 */declare-- 定义索引表类型type indextable is table of varchar2(20) index by pls_integer;-- 声明集合变量v_indextable indextable;begin-- 使用：集合变量（下标） 存储值或者赋值v_indextable(1):='a';v_indextable(3):='b';v_indextable(5):='c';v_indextable(-8):='d';-- 取值:集合变量（下标）dbms_output.put_line(v_indextable.first);dbms_output.put_line(v_indextable.last);dbms_output.put_line(v_indextable.count);--dbms_output.put_line(v_indextable.limit);if v_indextable.limit is null thendbms_output.put_line('v_indextable.limit返回的是个null');end if;v_indextable.dalete(); -- delete()不写下标则删除全部，写下表则表示删除那一个，被删除的下标则不能再次访问dbms_output.put_line(v_indextable.count);v_indextable.extend(2);-- 表示扩展俩个集合元素，但是索引表是无限值的，所以不能扩展dbms_output.put_line(v_indextable.next(-8));-- 返回下一个元素的下标dbms_output.put_line(v_indextable.prior(-8)); -- 返回上一个小标end;

遍历索引表集合

/*loop*/declare  -- 定义索引表类型  type indextable is table of varchar2(20) index by pls_integer;  -- 声明集合变量  v_indextable indextable;  -- 声明变量存储下标  v_index pls_integer;begin  -- 使用：集合变量（下标） 存储值或者赋值  -- 下标不存在就是添加，如果存在就是修改  v_indextable(1):='a';  v_indextable(3):='b';  v_indextable(5):='c';  v_indextable(-8):='d';  -- 从前往后  v_index:=v_indextable.first;-- 获取第一个下标  loop    dbms_output.put_line(v_index);    dbms_output.put_line(v_indextable(v_index));-- 遍历值    exit when v_index=v_indextable.last;    v_index:=v_indextable.next(v_index);  end loop;  /*  从后往前  v_index:=v_indextable.last;-- 获取最后一个下标  loop    dbms_output.put_line(v_index);    dbms_output.put_line(v_indextable(v_index));-- 遍历值    exit when v_index=v_indextable.first;    v_index:=v_indextable.prior(v_index);  end loop;  */end;/*for  使用for循环遍历数组元素下标一定要是连续*/declare  type index_table is table of number(21) index by pls_integer;  t index_table;begin  t(1):=11;  t(2):=23;  t(3):=43;  t(4):=22;  t(5):=21;  t(6):=56;  t(7):=55;  t(8):=32;  dbms_output.put_line('for循环开始了');  for j in t.first..t.last loop    dbms_output.put_line(t(j));  end loop;end;/*while*/declare  type index_table is table of number(21) index by pls_integer;  t index_table;  n number;begin  t(-1):=11;  t(-2):=23;  t(3):=43;  t(1):=22;  t(9):=21;  t(20):=56;  t(8):=55;  t(10):=32;  n:=t.first;  while n<=t.last loop    dbms_output.put_line(t(n));    n:=t.next(n);  end loop;end;/*集合里面的存储元素也可以是记录类型*/declare-- 声明记录类型type type_emp is record(    ename emp.ename%type,    v_emp type_emp;    v_emp2 type_emp;    -- 声明集合类型    type mytable is table of type_emp index by pls_integer;    -- 声明集合变量v mytable;    beginv_emp.ename :='张三';v_emp.sal :='4500';-- 存放记录类型数据v(1):=v_emp;-- 取记录类型数据v_emp2 :=v(1);dbms_output.put_line(v_emp2.ename||','||v_emp2.sal);end;

嵌套表

/*嵌套表使用整数（只能为正数）作为下标，下标是连续的；元素的个数是无限制的，可以在plsql中使用，也可以存储到数据库中。定义嵌套标类型：type 类型名称 is table of 数据类型（存储数据的数据类型）；声明嵌套标类型变量：变量名 类型名称数据的存取和索引表相同嵌套表在赋值之前需要初始化嵌套表的初始化：变量名:=类型名（）； -- 初始化变量名:=类型名(值，值，值);*/declare-- 定义嵌套表类型 type mytable is table of varchar2(20); -- 声明变量 v mytable;begin--为嵌套表初始化 v:=mytable('a');v(1):='aa';dbms_output.put_line(v(1));end;declare-- 定义嵌套表类型 type mytable is table of varchar2(20); -- 声明变量 v1 mytable; v2 mytable;begin--为嵌套表初始化 v:=mytable('a');v(1):='aa';dbms_output.put_line(v(1));-- 扩展集合元素，默认值是nullv.extend(2);  -- 扩展两个元素v.extend(2,1) -- 扩展两个元素，值是下标1的值end;

遍历嵌套表

/*loop*/declare-- 定义嵌套表类型type mytable is table of varchar2(20);-- 声明变量v1 mytable;-- 声明变量 下标n number;beginv1:=mytable('a','b','c');n:=v1.first;loopdbms_output.put_line(n);exit when n=v1.last;n:=v1.next(n);end loop;end;/*while*/declare  -- 定义嵌套表类型  type mytable is table of varchar2(20);  -- 声明变量  v mytable;  n number;begin  v:=mytable('a','b','c');  n:=v.first;  while n<=v.last loop     dbms_output.put_line(v(n));   n:=v.next(n);  end loop;end;/*for  因为他的下边是连续的*/declare-- 定义嵌套表类型type mytable is table of varchar2(20);-- 声明变量v1 mytable;beginv1:=mytable('a','b','c');for n in v1.first..v1.last loopdbms_output.put_line(n);end loop;end;

嵌套表在数据库中的使用

-- 格式create type 类型名称 is table of 数据类型;create type mytable is table of varchar2(20);-- 声明变量 mytable是一个嵌套表类型（已经在我们数据库中）v mytable;beginv:=mytable('a','abc','b');for n in v.first..v.last loopdbms_output.put_line(n);end loop;end;/*创建表的时候也可以使用嵌套表类型create table 表名(列  数据类型，嵌套表列  嵌套表类型)nested table 嵌套表类型列名 store as 表名（是数据库中没有的表）；*/-- 创建create table a(id number,    namelist mytable)nested table namelist store as myname;-- 存值insert into a(id,namelist) values(1,mytable('tom','jack','jerrt'));-- 查询select * from a;-- 查询嵌套表中的数据select * from table(select namelist from a where id =1);-- 删除时要注意，先删除表在删除类型drop table a;drop type mytable;

变长数组

/*使用整数(只能为正)(pls_integer,binary_integer)作为下标，下标也是连续的；元素个数有限制的，可以使用在plsql中也可以存储在数据库中。和嵌套表一样，使用前呢需要初始化*/-- 格式type 类型名称 is varray(长度)|varying array(长度) of 数据类型(保存的数据的数据类型);declare-- 声明变长数组类型type myvar is varray(5) of varchar2(20);-- 声明变量v myvar;beginv:=myvar('a','b','c');dbms_output.put_line(v(1));dbms_output.put_line(v(2));dbms_output.put_line(v(3));-- v.delete()  变长数组不能指定删除，-- v.extend() 扩展时不能超过声明时的长度end;

遍历变长数组

/*loop*/declare-- 声明变长数组类型type myvar is varray(5) of varchar2(20);-- 声明变量v myvar;-- 声明变量n pls_integer;beginv:=myvar('a','b','c','d');n:=v.first;loopdbms_output.put_line(n);exit when n=v.last;n:=v.next(n);end loop;end;/*for*/declare-- 声明变长数组类型type myvar is varray(5) of varchar2(20);-- 声明变量v myvar;beginv:=myvar('a','b','c','d');for n in v.first..v.last loopdbms_output.put_line(n);end loop;end;/*while*/declare-- 声明变长数组类型type myvar is varray(5) of varchar2(20);-- 声明变量v myvar;-- 声明变量n pls_integer;beginv:=myvar('a','b','c','d');n:=v.first;while n<=v.last loopdbms_output.put_line(n);n:=v.next(n);end loop;end;

变长数组在数据库中的使用

/*变长数组在数据库中的使用：create type 类型名称 is varray(长度)|varying array(长度) of 数据类型;*/create type myvarray is varray(5) of varchar2(20);declare-- 声明变量v myvarray;beginv:=myvarray('a','b','c','d');for n in v.first..v.last loopdbms_output.put_line(n);end loop;end;/*创建表create table 表名(列名  数据类型，数组列 数组类型);*/create table b(id number,    abc myvarray);-- 查询select * from b;-- 增加insert into b(id,abc) values(1,myvarray('a','b','c','d'));select * from table(select abc from b where id=1);

bulk collect

/*可以把一组数据取出来存入一个集合类型中我们之前那个select...into 变量：只能查出一条数据保存到变量中*/declarev_ename emp.ename%type;beginselect ename into v_ename  from emp where empno=7369;dbms_output.put_line(v_ename);end;/*而使用 select。。bulk collect into 集合类型的变量：可以取多条数据存储在集合中*/declarev_ename emp.ename%type;-- 定义集合类型嵌套表type mytable is table of emp.ename%type;-- 定义集合变量v mytable;beginselect ename into v_ename from emp where empno=7369;dbms_output.put_line(v_ename);dbms_output.put_line('--------------');select ename bulk collect into v from emp;-- 遍历集合中所以的元素for n in v.first..v.last loopdbms_output.put_line(v(n));end loop;end;/*bulk collect 还可以玩游标fetch 游标 bulk collect into 集合类型变量*/declare  -- 声明游标变量  cursor cur_emp is select * from emp;  -- 声明变量  v_emp emp%rowtype;begin  open cur_emp;  loop    fetch cur_emp into  v_emp;    exit when cur_emp%notfound;    dbms_output.put_line(v_emp.empno);  end loop;  close cur_emp;end;declare-- 声明游标变量cursor cur_emp is select * from emp;-- 声明集合类型嵌套表type mytable is table of emp%rowtype;-- 声明集合变量v mytable;beginopen cur_emp;fetch cur_emp bulk collect into v;for n in v.first..v.last loopdbms_output.put_line(v(n).empno)end loop;close cur_emp;end;

批量绑定

/*格式：forall 变量 in 集合sql语句：insert、delete、update*/-- 删除所有有部门编号的员工信息declare-- 定义集合type mytable is table of dept.deptno%type;-- 定义集合变量v mytable;beginselect deptno bulk collect into v from dept;/*这是一种for n in v.first..v.last loopdelete from emp where deptno = v(n);end loop;*/-- 这是forallforall n in v.first..v.lastdelete from emp where deptno = v(n);--update emp set sal=8000 where deptno=v(n);--insert into emp(empno) values(v(n));end;

动态sql

/*它是指在PL/SQL程序执行时生成的SQL语句，在编译过程中对它不做处理。而是在程序运行时动态构造语句、对语句进行语法分析并执行。我们之前在PL/SQL中只能使用DML、DQL。不能直接使用DDL;但是我们可以通过动态SQL来执行DDL语句命令。格式：execute immediate 字符串参数 [into] 变量 using 参数艾克斯Q得 恩米得厄特*/-- 按照员工编号查询员工的信息declarev_emp emp%rowtype;begin/*select * into v_emp from emp where empno=7369;dbms_output.put_line(v_emp.empno||','||v_emp.ename);*/     execute immediate 'select * from emp where empno=7369' into v_emp;    dbms_output.put_line(v_emp.empno||','||v_emp.ename);end;declarev_emp emp%rowtype;v_sql varchar2(100);begin/*select * into v_emp from emp where empno=7369;dbms_output.put_line(v_emp.empno||','||v_emp.ename);*/ v_sql:='select * into v_emp from emp where empno=7369';    execute immediate v_sql into v_emp;    dbms_output.put_line(v_emp.empno||','||v_emp.ename);end;declarev_emp emp%rowtype;v_sql varchar2(100);begin/*select * into v_emp from emp where empno=7369;dbms_output.put_line(v_emp.empno||','||v_emp.ename);*/ v_sql:='select * into v_emp from emp where empno=:a and deptno=:b';    execute immediate v_sql into v_emp using 7369,20;    dbms_output.put_line(v_emp.empno||','||v_emp.ename);end;--执行DDL语句-- 创建test001表begin  execute immediate 'create table test001(id number(2),name varchar2(10))';end;declarestmt varchar2(200):='create table test002(id number(2),name varchar2(10))';begin  execute immediate stmt;end;总结：PLSQL块中不能直接执行DDL语句，所以可以用动态sql去执行-- excute immediate sql语句--案例1: 编写一个plsql块，往test001中插入一条数据，要求从键盘输入declare    v_id test001.id%type:=&id;    v_name test001.name%type:=&name;begin  execute immediate 'insert into test001 values(:1,:2)' using v_id,v_name;end;--案例2：编写一个pl/sql块，往test001中插入一条记录，要求值是从键盘输入declare    v_id test001.id%type:=&id;    v_name test001.name%type:=&name;begin  execute immediate 'insert into test001 values(:1,:2)' using v_id,v_name;end;总结：execute immediate sql语句 using 值1，值2，…;:1,:2：需要用到变量的地方用:1,:2来代替-- 案例3：查询test001，从键盘接收id，输出其姓名declare    v_id test001.id%type:=&id;    v_name test001.name%type;begin  execute immediate 'select name from test001 where id=:1' into v_name using v_id;  dbms_output.put_line('姓名是：'||v_name);end; 总结：execute immediate sql语句 into 变量1 using 变量2;(1)sql语句中没有select...into...(2)where id=:1:1是占位符，表示这个地方在运行的时候需要有一个值替代--案例4：从键盘输入一个员工编号，查询该员工的姓名和工资，如果工资小于2000元，那么给他增加500，返回增加后的工资。declare    v_empno emp.empno%type:=&no;    v_ename emp.ename%type;    v_sal   emp.sal%type;begin  select ename,sal into v_ename,v_sal from emp where empno=v_empno;  if v_sal<2000 then    execute immediate 'update emp set sal=sal+500 where empno=:1 returning sal into :2' using v_empno returning into v_sal;  end if;  dbms_output.put_line('新工资是：'||v_Sal);end;总结：execute immediate sql语句 using 变量1 returning into 变量2;update emp set sal=sal+500 where empno=:1 returning sal into :2 意思是更新sal,返回更新后的工资。--案例5：从键盘接受一个员工编号，删除该员工信息declarev_empno emp.empno%type:=&no;begin  execute immediate 'delete from emp where empno=:num' using v_empno;end;

异常处理

概述

/*在PL/SQL中出现的异常，如果我们没有处理，异常会被传递给调用环境，中断我们的程序。*/declare  v_job emp.job%type;begin  select job into v_job from emp where empno=8001;  dbms_output.put_line(v_job);-- 异常  没有找到数据  dbms_output.put_line('程序结束');end;/*PL/SQL程序会从发生异常的代码处中断，以后的代码是无法执行的；而Oracle异常分为俩种类型：系统异常、自定义异常。而系统异常又分为预定义异常和非预定义异常。*/

预定义异常

--Oracle以及为这种异常定义好了名称，我们在异常处理部分直接通过异常名称进行了捕获ORACLE定义了它们的错误编号和异常名字，常见的预定义异常处理如下:ACCESS_INTO_NULL:对应ORA-06530为了引用对象属性，必须首先初始化对象。直接引用未初始化的对象属性时，会发生异常CASE_NOT_FOUND:对应ORA-06592，当CASE语句的WHEN子句没有包含必须条件分支或者ELSE子句时，会触发COLLECTION_IS_NULL:对应ORA-06531,在给嵌套表变量或者varrary变量赋值之前，必须首先初始化集合CURSOR_ALREADY_OPEN:ORA-06511，当已打开游标上执行OPEN操作时会触发INVALID_CURSOR:ORA-01001,当视图从未打开游标，提取数据，或者关闭未打开游标时会触发INVALID_NUMBER:ORA-01722,当内嵌SQL语句不能将字符转变成数字时会触发LOGIN_DENIED:ORA-01017,连接Oracle数据库时，如果提供了不正解的用户名和口令时会触发NO_DATA_FOUND:ORA-01403执行SELECT INTO 未返回行或者引用了未初始化的PL/SQL表元素时会触发NOT_LOGGED_ON:ORA-01012没有连接数据库执行SQL时会触发PROGRAM_ERROR:ORA-06501存在PL/SQL内部问题，在这种情况下需要重新安装数据字典视图和PL/SQL包ROWTYPE_MISMATCH:ORA-016504当执行赋值操作时，如果宿主变量和游标变量不兼容的返回类型时，会触发SELF_IS_NULL: ORA-30625,当使用对象类型时，如果在null实例上调用成员方法时，会触发STORAGE_ERROR:ORA-06500当执行PL/SQL块时，如果超出内存空间或者内存被破坏，会触发SUBSCRIPT_BEYOND_COUNT:ORA-06533当使用嵌套或者varray元素的范围进会触发SUBSCRIPT_OUTSIDE_LIMIT:ORA-06532，使用嵌套表或者varray元素时，如果元素下标为负值时，会触发SYS-INVALID_ROWID:ORA-01410当字符串转变为ROWID时如果使用了无效字符串，会触发TIMEOUT_ON_RESOURCE:ORA-00051当等待资源时如果出现超时会触发TOO_MANY_ROWS:ORA-01422当执行SELECT INTO时，如果返回超过一行、会触发VALUE_ERROR:ORA-06502,执行赋值时，如果变量长度不足，会触发ZERO_DIVIDE：ORA-01476如果用数字值除0，会触发/*当plsql程序中，在begin部分的语句出现了异常，就会进入到exception部分执行异常处理功能只要我们采取了处理措施后，就不会产生报错信息*/declare  v_job emp.job%type;  cursor mycur is select * from emp;  v_num number(8);begin  select job into v_job from emp where empno=8001;  dbms_output.put_line(v_job);-- 异常  没有找到数据  dbms_output.put_line(mycur%rowcount);  v_num := 10/0;  insert into dept values(10,'气氛组','青岛');exceptionwhen NO_DATA_FOUND thendbms_output.put_line('执行的select语句没有查询到结果');when TOO_MANY_ROWS thendbms_output.put_line('执行的select语句不能查询多条结果');    when INVALID_CURSOR thendbms_output.put_line('无效的游标');when ZERO_DIVIDE thendbms_output.put_line('0不能作为除数');when DUP_VAL_ON_INDEX then --唯一索引中插入重复值dbms_output.put_line('违反了主键约束');when VALUE_ERROR then dbms_output.put_line('赋值错误');when others then -- 最牛逼的异常处理  不会有一条异常信息；dbms_output.put_line('PL/SQL中发生了异常');end;注意：SQLCODE与SQLERRM为异常处理函数。函数SQLCODE用于取得Oracle错误号，函数SQLERRM用于取得与错误号对应的相关错误消息用法：exceptionwhen NO_DATA_FOUND thendbms_output.put_line('执行的select语句没有查询到结果'||','||sqlcode||','||sqlerrm);

非预定义异常

非预定异常，也是因为违反了Oracle的规则，Oracle会产生报错信息（有错误编号和错误信息），但是Oracle并没有为这类错误定义好异常名称（但有错误编号），那么我们可以自已定义一种异常名称，并将这样异常名称和错误编号进行绑定；帕克码我们使用的时候，先声名一个异常名，通过伪过程 PRAGMA EXCEPTION_INIT，将异常名与错误号关联起来。declare  v  exception; -- 自己定义的一种异常名称  -- 通过 PRAGMA EXCEPTION_INIT(异常名称，错误编码)  pragma exception_init(v,-04098); begin  delete from dept where deptno = 10; exception  when v then    dbms_output.put_line('违反了外键约束');end;-- 在不知道错误编码的时候怎么用begin  delete from dept where deptno = 10; exception  when others then    dbms_output.put_line('违反了外键约束'||sqlcode);end;

自定义异常

程序员从业务角度出发，制定的一些规则和限制。抛出异常：润丝raise 异常名称;declaremyex exception;beginraise myex;-- null;end;-- 完成2个数相除，要求除数不能为5declarev_1 number:=&请输入第一个数:v_2 number:=&请输入第二个数;beginif v_2 = 5 thendbms_output.put_line('除数不能为5');end if;dbms_output.put_line(v_1/v_2);end;这么写的话，我的程序依然会往下执行。declarev_1 number:=&请输入第一个数;v_2 number:=&请输入第二个数;myex exception;beginif v_2 = 5 thendbms_output.put_line('除数不能为5');raise myex; -- 抛个异常 让程序停止end if;dbms_output.put_line(v_1/v_2);end;declarev_1 number:=&请输入第一个数;v_2 number:=&请输入第二个数;myex exception;beginif v_2 = 5 thendbms_output.put_line('除数不能为5');raise myex;end if;dbms_output.put_line(v_1/v_2);exception  when myex then -- 自己定义的异常可以自己处理    dbms_output.put_line('除数不能为5');end;

引发应用程序错误

可能不是很多人知道 RAISE_APPLICATION（哎破tei谁）_ERROR（哎我） 的用途是什么，虽然从字面上已经猜到这个函数是干什么用的。平时用来测试的异常处理我们都是通过dbms_output.put_line来输出异常信息，但是在实际的应用中，需要把异常信息返回给调用的客户端。declare  rate_exception exception;  b number(20);begin  b := &b;  if b not in (2, 1, 3) then    raise rate_exception;  else    dbms_output.put_line('该水果名称为香蕉' || b);  end if;exception  when rate_exception then    raise_application_error(-20003, '这是我自己定义的');end;

存储过程和函数

概述：ORACLE 提供 可以把PL/SQL 程序存储在数据库中，并可以在任何地方来运行它。这样就叫存储过程或函数。存储过程和函数统称为PL/SQL子程序，他们是被命名的PL/SQL块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。

存储过程

存储过程，就是一段存储在数据库中执行某块业务功能的程序模块。它是由一段或者多段的PL/SQL代码块或者SQL语句组成的一系列代码块。--创建存储过程的语法create or replace procedure 存储过程名字 [(参数 in| out|in out 参数的数据类型)]as | is声明部分：beginplsql代码块exception异常处理end;-- 无参数存储过程create or replace procedure p1iscursor cur_emp is select * from emp;beginfor v_emp in cur_emp loopdbms_output.put_line(v_emp.ename);end loop;end;/*如果使用/调用存储过程*/-- 1、在plsql块中调用begin p1;-- p1();end;-- 2、call 命令调用call p1();-- 3、 execute 命令调用  （属于sqlplus命令） 需要通过cmd小黑窗执行 记得 set serverout onexecute p1();/*查询存储过程 userselect * from user_objects  --数据字典*//*删除存储过程drop procedure 存储过程的名字；*/drop procedure  p1;/*参数的三种模式IN  用于接受调用程序的值。默认的参数模式OUT  用于向调用程序返回值 IN OUT  用于接受调用程序的值，并向调用程序返回更新的值*/-- IN  用于接受调用程序的值。默认的参数模式--写一个存储过程，根据传入部门编号，查询该部门下的所有员工，并打印员工信息create or replace procedure p2(v_deptno in number)-- 这里的数据类型不需要加长度，这里in可以省略，默认为inisbegin for v_emp in (select * from emp where deptno = v_deptno) loop dbms_output.put_line(v_emp.empno||','||v_emp.ename); end loop;end;-- 使用beginp2(10);end;beginp2(v_deptno => 10);end;declarev number :=20;beginp2(v); end;create or replace procedure p2_2(v_deptno1 in number,v_deptno2 in number)isbegin dbms_output.put_line(v_deptno1||','||v_deptno1);end;beginp2_2(10,20);end;beginp2(v_deptno2 => 10,v_deptno1 => 20);-- 顺序可以打乱end;declarev1 number :=20;v2 number :=20;beginp2_2(v1,v2); end;-- OUT  用于向调用程序返回值 --根据传入的员工编号，查询出员工信息并以输出参数的方式返回create or replace procedure p3(v_empno in emp.empno%type,v_emp out emp%rowtype)isbeginselect * into v_emp from emp where empno = v_empno;end;-- 使用declarev_e emp%rowtype;beginp3(7369,v_e);dbms_output.put_line(v_e.empno||','||v_e.ename);end;--IN OUT  用于接受调用程序的值，并向调用程序返回更新的值--根据传入的员工编号，查询出员工信息并以输出参数的方式返回create or replace procedure p4(v_emp in out emp%rowtype)isbeginselect * into v_emp from emp where empno = v_emp.empno;end;-- 使用declarev_e emp%rowtype;beginv_e.empno := 7369;p4(v_e);dbms_output.put_line(v_e.empno||','||v_e.ename);end;-- 其他用户调用scott用户的存储过程-- 创建用户create user test6 identified by root;grant connect,resource to test6;-- 使用test6登录begin  scott.p1();end;-- 不能访问-- 使用 scott用户授权才可以grant execute on p1 to test1;注意：如果需要在存储过程中访问其他用户下的表时，必须赋值权限 grant select on 表名 to 用户;

函数

函数是通过关键字function按照自己的需求把复杂的业务逻辑封装进PL/SQL函数中，函数提供一个返回值，返回给使用者。--创建函数的语法create [or replace] function 函数名称[(形式参数 参数类型,...)]return 返回值类型is   声名变量;begin  plsql代码块;  return 返回值;--return后面的内容不执行end;-- 无参数的函数create or replace function  f1return varchar2isbeginreturn '我爱你';end;-- 查看函数select * from user_objects where object_ type = 'FUNCTION';-- 使用函数，调用函数1、用在sql语句中select f1 from dual;select f1 from emp;select * from emp where f1()='我爱你';2、在plsql块中使用begindbms_output.put_line(f1);end;-- 删除函数-- drop function 函数名drop function f1;-- 有参数的函数-- 编写函数，接受一个数，返回1-这个数的和。create or replace function  f2(n number) return numberisv_sum number:=0;beginfor i in 1..n loopv_sum :=v_sum+i;end loop;return v_sum;end;-- 使用select f2(3) from dual;-- 多个函数的写法-- 编写函数，接受2个值，返回最大值create or replace function  f3(n1 number,n2 number) return numberis--v number;beginif n1>n2 then--v:=n1;return n1;else-- v:=n2;return n2;end if;--return v;end;-- 使用select f3(2,3) from dual;

触发器

什么是触发器？

我们生活就有许多触发器，比如：灯的开关，手枪扳机。。。而数据库的触发器与表相关联的、存储的pl/sql程序。每当一个特定的数据操作语言在指定的表中发出时，Oracle自动执行触发器中定义的语句序列。触发器在数据库中以独立的对象存储（就是可以在侧边栏找到），它与存储过程不同的是，存储过程通过其他程序来启动运行或直接运行，而触发器是由一个事件来启动运行。即触发器是当某个时间发生时自动地隐式运行。并且，触发器不能接收参数。Oracle事件指的是对数据库的表进行的 insert、 update、 delete操作或对视图进行类似的操作。-- 触发器可用于1、数据确认2、实施复杂的安全性检查3、做审计，跟踪表上所做的数据操作等4、数据的备份和同步-- 触发器分类1、DML触发器：表级触发器（语句级触发器）行级触发器替换触发器2、模式（DDL）触发器3、数据库级触发器

创建触发器的语法

create or replace trigger 触发器名before | afterdelete [or] | insert[or] | update [of 列名]on 表名[for each row][where(条件)]declare....beginPLSQL块end;for each row 作用时标注此触发器是行级触发器；没有标注就是语句级触发器

在触发器中触发语句与为记录变量的值

触发语句 :old :new
insert 所有的字段都为空（null） 将要插入的值
update 更新以前该行的值 更新后的值
delete 删除以前该行的值 所有字段都为空（null）

案例

表级触发器
create or replace trigger tri1before insert on dept --给这个表插入数据之前begindbms_output.put_line('触发器执行了');end;-- 查看触发器select * from user_objects where object_type='TRIGGER';-- 如何执行触发器，在特定的事件发生的时候执行insert into dept values(50,'财务部','北京');--执行update dept set loc='上海' where deptno=50;--不会执行delete from dept where deptno=50;-- 不会执行-- 如果我要实现增删改都要触发就这么做create or replace trigger tri1before insert or update or delete on dept --给这个表插入数据之前begindbms_output.put_line('触发器执行了');end;-- 也可以指定列名create or replace trigger tri1before insert or update of loc,dname or delete on dept --给这个表插入数据之前begin dbms_output.put_line('触发器执行了');end;update dept set deptno=52 where deptno=50;--不会执行before:表示在sql语句执行前，执行触发器的代码after:表示在sql语句执行之后，执行触发器的代以下三个都是boolean类型的updating:如果触发这个触发器的是一条update语句，它值就是truedeleting:如果触发它是一条delete语句，它值就是trueinserting:如果触发它是一条insert语句，它值就是truecreate or replace trigger tri1before insert or update or delete on dept --给这个表插入数据之前beginif updating thendbms_output.put_line('触发器执行了--改');end if;if deleting thendbms_output.put_line('触发器执行了--删');end if;if inserting thendbms_output.put_line('触发器执行了--增');end if;end;insert into dept values(50,'财务部','北京');update dept set loc='上海' where deptno=50;delete from dept where deptno=50;-- 删除触发器drop trigger 触发名;
行级触发器
对DML语句修改的每个行执行一次,有for each row语句，在begin代码段中可以使用:new和:old。:new --是 一个引用最新的列值:old --是 一个引用以前的列值-- 为dept表创建行级触发器create or replace trigger tri2before update or delete or inserton dept for each rowbeginif updating thendbms_output.put_line('修改了');end if;if deleting thendbms_output.put_line('删除了');end if;if inserting thendbms_output.put_line('增加了');end if;end;insert into dept values(88,'技术部','北京');update dept set loc='销售部' where deptno=56 or deptno=57;delete from dept where deptno=50;:new --是 一个引用最新的列值:old --是 一个引用以前的列值create or replace trigger tri2before update or delete or inserton dept for each rowbeginif updating thendbms_output.put_line('修改了'||:old.dname||:new.dname);end if;if deleting thendbms_output.put_line('删除了'||:old.dname||:new.dname);end if;if inserting thendbms_output.put_line('增加了'||:old.dname||:new.dname);end if;end;insert into dept values(59,'技术部','广州');update dept set dname='公关部' where deptno = 59 or deptno = 52;delete from dept where deptno = 59;before:表示在sql语句执行前，执行触发器的代码（前置）create or replace trigger tri2before update or delete or inserton dept for each rowbeginif updating thendbms_output.put_line('修改了'||:old.dname||:new.dname);end if;if deleting thendbms_output.put_line('删除了'||:old.dname||:new.dname);end if;if inserting then--dbms_output.put_line('增加了'||:old.dname||:new.dname);if :new.deptno <80 then:new.deptno:=80;end if;end if;end;insert into dept values(60,'技术部','广州');after:表示在sql语句执行之后，执行触发器的代码（后置）create or replace trigger tri2after update or delete or inserton dept for each rowbeginif updating thendbms_output.put_line('修改了'||:old.dname||:new.dname);end if;if deleting thendbms_output.put_line('删除了'||:old.dname||:new.dname);end if;if inserting then--dbms_output.put_line('增加了'||:old.dname||:new.dname);if :new.deptno <80 then:new.deptno:=88;end if;end if;end;--一般我们都会用前置触发器-- 可以这么玩：-- 可以生成主键值使用的序列create table seqtab(id number(11) primary key,    name varchar2(30));-- 创建序列create sequence seq start with 1 increment by 1;-- 查看下一个序列select seq.nextval from dual;-- 正常添加insert into seqtab(id,name) values(seq.nextval,'tom');create or replace trigger tri3before insert on seqtab for each rowbegin:new.id := seq.nextval;end;insert into seqtab(name) values('刘德华');
替换触发器
替换触发器使用在视图上，而且是行级的触发器。视图的修改是有限制的：1、视图的修改还是基于基表的2、有约束限制不能修改3、有聚合函数4、有表达式，伪劣。（有的是表达式计算的）5、复杂视图（多表联合查询的视图）键值保存表的列可以修改，非键值保存表不能修改。-----------以下为键值保存表和非键值保存表的理解---------------------- 什么是非键值保存表？create table mytable1(id number,    name varchar2(100));insert into mytable1 values(1,'张三');insert into mytable1 values(2,'李四');select * from mytable1;create table mytable2(a_id number,    address varchar2);insert into mytable2 values(1,'北京');insert into mytable2 values(2,'上海');select * from mytable2;-- 创建视图create or replace view myViewas select * from mytable1,mytable2where mytable1.id = mytable2.a_id;-- 查看视图select * from myView1;-- 普通的表，没有主键约束，不能修改的，都是非键值保存表update myView set name = 'abc' where id = 1;-- 键值保存表create table mytable3(id number primary key,    name varchar2(100));insert into mytable3 values(1,'张三');insert into mytable3 values(2,'李四');select * from mytable3;create table mytable4()a_id number primary key,    address varchar2(100),    m_id number);insert into mytable4 values(1,'北京',1);insert into mytable4 values(2,'上海',2);insert into mytable4 values(3,'广州',1);insert into mytable4 values(4,'深圳',2);select * from mytable3;-- 创建视图create or replace view myView2asselect * from mytable3,mytable4 where mytable3.id=mytable4.id;-- 查看视图select * from myView2;--查询出来的id,name 为非键值保存列。-- 在试图中可以修改键值保存表的列update myView2 set address = 'a' where a_id=1;-- 在视图中无法修改非键值保存表的列update myView2 set name = 'abc' where a_id=1;-----------以上为键值保存表和非键值保存表的理解---------------------/*替换触发器使用在视图上，当视图不能修改，使用这个触发器可以完成修改。而且是行级的触发器语法：  create or replace trigger 触发器名   instead of insert or update or delete on 视图名 for each row  begin  end;*/-- 准备工作-- 创建视图create view de as select d.deptno dno, d.dname,d.loc,e.* from dept d,emp e where d.deptno=e.deptno;-- 查询视图select * from de;-- 完成修改视图，把empno为7369的dname修改为财务部（改不了）update de set dname = '财务部' where empno=7369;-- 创建替换触发器（instead of 触发器）create or replace trigger tri5instead of update on de for each rowbegindbms_output.put_line('替换触发器执行了');end;-- 再次执行的时候，就不会报错了，触发器执行了，但是并没有更新成功update de set dname = '财务部' where empno=7369;-- 需要这个修改触发器-- 创建替换触发器（instead of 触发器）create or replace trigger tri5instead of update on de for each rowv_deptno dept.deptno%type;begindbms_output.put_line('替换触发器执行了');-- 根据7369查询部门编号select deptno into v_deptno from emp where empno = :new.empno;dbms_output.put_line(v_deptno);-- 更改dept表的数据update dept set dname = :new.dname where deptno = v_deptno;end;/*注意：触发器的代码中不能出现 commit，rollback，savapoint*/
模式触发器（作为了解）
在模式中执行DDL语句时执行。-- 创建表CREATE TABLE dropped_obj (obj_name VARCHAR2(30),   obj_type VARCHAR2(20),   drop_date DATE);select * from dropped_obj;-- 创建触发器CREATE OR REPLACE TRIGGER 触发器名字before ALTER or DROP or CREATE ON SCHEMABEGIN  INSERT INTO dropped_obj   VALUES(ORA_DICT_OBJ_NAME,   ORA_DICT_OBJ_TYPE, SYSDATE);END;create table table1(a number);create table table2(a number);常用系统变量：Ora_client_ip_address 返回客户端的ip地址Ora_database_name 返回当前数据库名Ora_login_user 返回登录用户名Ora_dict_obj_name 返回ddl操作所对应的数据库对象名 Ora_dict_obj_type 返回ddl操作所对应的数据库对象的类型
数据库级触发器（作为了解）
在发生打开、关闭、登录和退出数据库等系统事件时执行-- 创建一张表create table event_table(event varchar2(30),    time date);select * from event_table;-- 数据库启动触发器create or replace trigger tr_startupafter startup on databasebegin    insert into  event_table values(ora_sysevent, sysdate);end;-- 现在直接查看就查不出来的，因为我们的数据库是一只打开的-- 利用cmd命令关闭再打开sqlplus sys/sys as sysdba;--登录shutdown immediate;-- 关闭startup;--开启--用户登陆触发器-- 创建表create table log_table(    username  varchar2(20),     logon_time  date);create or replace trigger tr_logonafter logon on databasebegin    insert into log_table(username,logon_time) values(ora_login_user,  sysdate);end;-- 为了方便 我用cmd登录sqlplus scott/scott注：启用、禁用和删除触发器启用和禁用触发器ALTER TRIGGER 触发器名字 DISABLE;ALTER TRIGGER 触发器名字 ENABLE;删除触发器DROP TRIGGER aiu_itemfile;

触发器练习题：如果实现让用户在星期一不能修改数据

select * from cs1;create or replace trigger cscsbefore insert on cs1declarev varchar2(50);begin  select to_char(sysdate,'day') into v from dual;  if v = '星期一' then    raise_application_error(-20001,'星期一不能insert');  end if;end;insert into cs1 values(1,'a');

程序包

概述

程序包是对相关过程、函数、变量、游标和异常等对象的封装程序包由规范(包头)和主体(包体)两部分组成规范中可以声明程序包中公共对象。包括类型、变量、常量、异常、游标规范和子程序规范等其语法格式如下:create [or replace] package 包名 is[declare_variable];-- 规范内声明变量[declare_type];-- 规范内声明类型[declare_cursor];-- 规范内声明游标[declare_function];-- 规范内声明函数 只能定义函数和返回类型不包括函数体[declare_procedure];-- 规范内声明存储过程 只能定义参数不包括存储过程主体end [包名];主体中可以声明程序包私有对象和实现在包规范中声明的子程序和游标与创建‘规范’不同的是，创建‘程序包主体’使用 create [or replace] package body 语句，这一点需要注意：create [or replace] package body 包名 is[inner_variable] -- 程序包内部的变量[cursor_body] -- 游标主体[function_title] -- 从规范中引入的函数头部的声明{beginfun_plsql; -- plsql块[exception] -- 异常部分[dowith_sentences;] -- 异常处理语句end [fun_name] -- 函数名称}[procedure_title] -- 从规范中引入的存储过程的名称{beginpro_plsql; [exception][dowith_sentences;]end [pro_name]....}end [包名];

程序包创建的语法

-- 包头的创建语法CREATE [OR REPLACE] PACKAGE package_name IS|AS[Public item declarations] [Subprogram specification] END [package_name];package_name:包的名称Public item declarations：公共声明部分Subprogram specification：声明PL/SQL子程序-- 程序包主体的常见语法CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS[Private item declarations] [Subprogram bodies] [BEGINInitialization]END [package_name];package_name是包的名称Private item declarations:私有声明部分subprogram specificatons:子程序体-- 举例创建一个简单的-- 创建包头create or replace package pack1is-- 声明公共部分v_n number :=12;-- 声明PL/SQL子程序（过程函数）(注意：再这里创建函数或者过程的时候不用写create or replace)procedure pp;function ff return varchar2;end;-- 创建一个包体(包体是对包头的实现，名字必须和包头名字一致)create or replace package body pack1is--声明私有部分v_n2 number:=23;--声明实现部分（子程序体）procedure ppisbegindbms_output.put_line('Hello');end;function ff return varchar2isbeginreturn '你好';end;end;-- 如和查询包头和包体select * from user_objects;-- 如何使用包？1、可以通过 包名.对象名begindbms_output.put_line(pack1.v_n);-- 只能访问公共的，不能使用私有的pack1.pp();dbms_output.put_line(pack1.ff());end;--创建包头create or replace package pack2isv number :=50;-- 向dept表中添加数据procedure pp_insert(v_dept in dept%rowtype);-- 求2个数的和function ff_add(v1 number,v2 number) return number;end;-- 创建包体create or replace package body pack2is-- 实现过程的代码procedure pp_insert(v_dept in dept%rowtype)isbegininsert into dept values(v_dept.deptno,v_dept.dname,v_dept.loc);end;-- 实现函数的代码function ff_add(v1 number,v2 number) return numberisbeginreturn v1+v2;end;end;-- 使用declarev_dept dept%rowtype;begindbms_output.put_line(pack2.v);v_dept.deptno :=100;v_dept.dname :='研发部';v_dept.loc :='青岛';pack2.pp_insert(v_dept);dbms_output.put_line(pack2.ff_add(5,6));end;-- 删除包drop package pack2;

程序包中的游标

游标的定义分为游标规范和游标主体两部分在包规范中声明游标规范时必须使用 RETURN 子句指定游标的返回类型 return 数据类型;RETURN子句指定的数据类型可以是：用 %ROWTYPE 属性引用表定义的记录类型程序员定义的记录类型，例如 TYPE EMPRECTYP IS RECORD(emp_id INTEGER,salary REAL)来定义的。不可以是number, varchar2， %TYPE等类型。如：-- 创建包头-- 显式游标create or replace package pack3iscursor cur_dept return dept%rowtype;procedure p;end;-- 创建包体create or replace package body pack3 iscursor cur_dept return dept%rowtype is select * from dept;procedure pv_dept dept%rowtype;isbeginopen cur_dept;loopfetch cur_dept into v_dept;dbms_output.put_line(v_dept.deptno||','||v_dept.dname||','||v_dept.loc);exit when cur_dept%notfound;end loop;close cur_dept;end;end;-- 使用beginpack3.p();end;-- ref 游标-- 创建包头create or replace package pack4is-- 声明ref游标类型type cur_type is ref cursor;procedure p;end;-- 创建包体create or replace package body pack4is   procedure p  is    -- 声明ref游标变量    cur_my cur_type;    v_dept dept%rowtype;  begin    open cur_my for select * from dept;    loop      fetch cur_my into v_dept;      exit when cur_my%notfound;      dbms_output.put_line(v_dept.deptno||','||v_dept.dname||','||v_dept.loc);    end loop;    close cur_my;  end;end;--使用beginpack4 p();end;

内置程序包

可以扩展数据库的功能为 PL/SQL 提供对 SQL 功能的访问用户 SYS 拥有所有程序包是公有同义词可以由任何用户访问就是Oracle提供的程序包-- DBMS_RANDOM包的使用，来产生随机的数字、字符、日期--产生随机的整数select DBMS_RANDOM.random from dual;begindbms_output.put_line(DBMS_RANDOM.random);end;-- 产生0-100（不包括100）的随机数select abs(mod(DBMS_RANDOM.random,100))from dual;-- value函数会返回一个大于等于0但是小于1的数select dbms_random.value from dual;/* 对于指定范围内的数，要加入参数low_value和high_value*/select dbms_random.value(0,10) from dual;select floor(dbms_randow.value(0,10)) from dual;--0到100之间的小数SELECT  DBMS_RANDOM.VALUE(0, 100)  FROM  DUAL; --产生0到100之间的整数SELECT TRUNC(DBMS_RANDOM.VALUE(0, 100)) FROM  DUAL;/*STRING函数生成随机文本字符串，可以指定字符串的类型和所希望的长度。  SELECT DBMS_RANDOM.STRING('A', 20) FROM DUAL;      下面是一些类型的代码：‘U’用来生成大写字符，upper‘L’用来生成小写字符，lower‘A’用来生成大小写混合的字符，’P’ 表示 字符串由任意可打印字符构成， +-#$%^^’X’表示字符串由大写字符和数字构成。 */select dbms_random.string('U',10) from dual;select dbms_random.string('L',10) from dual;select dbms_random.string('A',10) from dual;select dbms_random.string('P',10) from dual;select dbms_random.string('X',10) from dual;/*返回某年内的随机日期，分两步：--日期对应的内部整数，用格式'J' -47121,   SELECT TO_CHAR(TO_DATE('01/01/21','mm/dd/yy'),'J')FROM DUAL; 2,  SELECT TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2459216,2459216+365)),'J')  FROM DUAL; */select to_char(sysdate,'J') from dual; -- 2459307select to_date(2459307,'J' from) dual;-- 随机2021年的某一天select to_char(to_date('2021-01-01','yyyy-MM-dd'),'J') from dual;select to_date(trunc(dbms_random.value(2459307,2459306+365)),'J') from dual;/*dbms_job包的用法可以执行调度任务，比如执行定时任务*/-- 任务:每搁一段时间间隔，自动调用存储过程-- 创建测试表create table mytable11(a date);-- 创建一个自定义过程create or replace procedure test11isbegininsert into mytable11 values(sysdate);end;/* submit()参数：1 job     OUT BINARY_INTEGER, 输出的no 编号2 what      IN  VARCHAR2,'存储过程名;' 3 next_date IN  DATE DEFAULT sysdate,执行任务的时间4 interval  IN  VARCHAR2 DEFAULT 'null', 间隔时间 单位是1天*/declare  v binary_integer;begin  ----创建jobdbms_job.submit(v,'test11;',sysdate,'sysdate+1/1440');-- 第一个参数是任务编号。-- 第二个参数是要执行的存储过程名，注意加；号-- 第三个参数是下次执行的时间-- 第四个参数是时间间隔  dbms_output.put_line(v);end;-- 如何查看任务select * from user_jobs;-- 如何删除任务begindbms_job.run(v);end;--运行JOBbegindbms_job.run(v);end;--停止任务begindbms_job.broken(v,true);end;/*UTL_FILE包的用法可以操作系统的文本文件准备工作：在D盘下创建一个文件夹sql*//*创建一个Oracle的目录create or replace directory 目录名称 as '目录的路径';*/create or replace directory filepath as 'd:/sql';/*文件类型：utl_file.file_type打开文件：utl_file.fopen('目录'，'文件名','文件的模式')目录: 值是oracle的目录，需要加单引号文件名：值是文件名，需要加单引号文件的模式:w(可写)，r(读)，a(追加)写入文件内容：utl_file.put_line(文件类型变量，要写的文件内容)读取文件内容：utl_file.get_line(文件类型变量，要读的文件内容)*/-- 写declare   --声名文件类型的变量   f utl_file.file_type;begin   --1.打开文件utl_file.fopen(创建的directory,文件名,打开方式字符串)打开方式分三种 w:写，r：读，a:追加   f:=utl_file.fopen('FILEPATH','test.txt','w');   --2.读或写文件utl_file.put_line(文件类型变量，要写的文件内容)   utl_file.put_line(f,'a');   utl_file.put_line(f,'b');   utl_file.put_line(f,'c');   utl_file.put_line(f,'d');   --3.关闭文件utl_file.fclose(文件类型变量)   utl_file.fclose(f);  end;-- 读declare   --声名文件类型的变量   f utl_file.file_type;   --声名一个变量保存文件中的一行记录   str varchar2(200);begin   --1.打开文件utl_file.fopen(创建的directory,文件名,打开方式字符串)打开方式分三种 w:写，r：读，a:追加   f:=utl_file.fopen('FILEPATH','test.txt','r');   --2.读或写   _file.get_line(f,str);   dbms_output.put_line(str);     utl_file.get_line(f,str);   dbms_output.put_line(str);     utl_file.get_line(f,str);   dbms_output.put_line(str);     utl_file.get_line(f,str);   dbms_output.put_line(str);   /*   loop   utl_file.get_line(f.str);   dbms_output.put_line(str);   end loop;   exception   when no_data_found then   dbms_output.put_line('找不到了');   */      --3.关闭文件utl_file.fclose(文件类型变量)   utl_file.fclose(f);  end;
网络标签：

相关问题