学习plsql笔记

begin
v_deptno emp.deptno%type:=&deptno;
update emp set sal=sal*1.1 where deptno=v_deptno;
if sql%found then dbms_output.put_line('执行成功') ；else dbms_output.put_line('执行失败');
end;

sql%rowcount -- 修改的sql行数

declare
v_deptno emp.deptno%type;
begin
 v_deptno:=no;
 case v_deptno
    when 10 then update emp set comm=100 where deptno=v_deptno;
    when 20 then update emp set comm=200 where deptno=v_deptno;
   when 30 then update emp set comm=300 where deptno=v_deptno;
 else
    dbms_output.put_line("不存在");
 end case;
 end;
 -- goto
 declare
   i int :=1;
   begin
    loop
     insert into temp values(i);
     if i=10 then
      goto end_loop
     end if;
    i:=i+1;
    end loop;
    <<end_loop>>
     dbms_output.put_line('循环结束');
  end;

-- 复合数据类型
type emp_record_type is record(
  name emp.ename%type,
  sal  emp.sal%type,
  dno  emp.deptno%type
);
emp_record emp_record_type;

declare
 type emp_record_type is record(
  name emp.ename%type,
  sal  emp.sal%type,
  dno  emp.deptno%type);
  emp_record emp_record_type;
  begin
   select ename,sal,deptno into emp_record from emp where empno=&empno;
   dbms_output.put_line(emp_record.name);
end;

-- plsql 单行多列数据
decalre
 dept_record dept%rowtype;
 begin
  dept_record.deptno:=50;
  delete from dept where deptno=dept_record.deptno;
  update dept set row = dept_record where deptno=&deptno;
end;

-- 索引表   在索引表中使用BINARY_INTEGER和PLS_INTEGER
declare
 type ename_table_type is table of emp.ename%type index by binary_integer;
 ename_table ename_table_type;
begin
 select ename into ename_table(-1) from emp where empno=&no;
end;

-- 在PL/SQL块中使用嵌套表:使用嵌套表变量时,必须首先使用构造方法初始化嵌套表变量,然后才能在块内引用嵌套表元素
declare
 type ename_table_type is table of emp.ename%type;
 ename_table ename_table_type;
 empno emp.empno%type;
begin
 empno := 1;
 ename_table := ename_table_type('mary','mary','mary');
 for empno in 1..100 loop
  select ename into ename_table(emono) from emp where empno=empno;
 end loop;
end;

-- VARRAY 变长数组
declare
 type ename_table_type is varray(20) of emp.ename%type;
 ename_table := ename_table_type('mary');;
begin
 select ename into ename_table(1) from emp where empno=&no;
end;
 

-- 多行多列
declare
 type ename_table_type is table of emp%rowtype index by binary_integer;
 emp_table ename_table_type;
begin
 select * into emp_table from emp where empno=&no;
 dbms_output.put_line('雇员名：'||emp_table(1).ename);
 dbms_output.put_line('雇员工资：'||emp_table(1).sal);
end;
 
 
-- 二维索引表
declare
 type a1_table_type is table of number index by binary_integer;
 type nal_table_type is table of a1_table_type by binary_integer;
 nv1 nal_table_type;
begin
 nv1(1)(1):=10;
  nv1(1)(2):=5;
   nv1(2)(1):=199;
    nv1(2)(2):=50;
 dbms_output.put_line('显示二维数组的所有元素！');
 for i in 1..nv1.count loop
  for j in 1..nv1(i).count loop
    dbms_output.put_line('nv1('||i||','||j||')='||nv1(i)(j));
  end loop;
  end loop;
end;

-- 集合赋值 赋null 判断集合是否null
declare
 type name_varry_type is varray(4) of varchar2(10);
 name_array name_varry_type;
 name_empty name_varry_type;
begin
 name_array:=name_varry_type('scott','smith');
 dbms_output.put_line('name_aray的原有元素的个数:'||name_array。count);
 name_array:=name_empty;
 if name_array is null then
     dbms_output.put_line('name_array的现有的元素个数:0');
 end if;
end;

-- set 取消嵌套表的重复
delcare
 type nt_table_type is table of number;
 nt_table nt_table_type := nt_table_type(2,4,3,1,2);
 result nt_table_type;
begin
 result:=set(nt_table);
 for i in 1..result.count loop
  dbms_output.put_line(''||result(i));
 end loop;
 dbms_output.new_line;
end;

--  multiset union 操作符:取得两个嵌套表的并集.结果集中会包含重复值
--  MULTISET UNION DISTINCT 操作符:用于取得两个嵌套表的并集,并取消重复结果.
--  MULTISET INTERSECT 操作符:用于取得两个嵌套表的交集
--  MULTISET EXCEPT 操作符:取得两个嵌套表的差集.在NT1中存在,但在NT2中不存在

-- 检查集合是否为空 null
decalre
 type name_table_type is table of varchar2(10);
 name_table name_table_type;
begin
 if name_table is empty then
  dbms_output.put_line('name_table未初始');
 end if;
end;

-- 比较嵌套表是否相同 用“=” 且不能用于索引表和可变数组varray

-- 使用操作符SUBMULTISET OF:用于确定一个嵌套表是否为另一个嵌套表的子集.
 declare
    type nt_table_type is table of number;
    nt1 nt_table_type:=nt_table_type(1,2,3);
    nt2 nt_table_type:=nt_table_type(1,2,3,4);
  begin
    if nt1 submultiset of nt2 then
       dbms_output.put_line('nt1是nt2的子集);
    end if;
  end;

-- 使用操作符MEMBER OF :用于检测特定数据是否为嵌套表的元素.
declare
    type nt_table_type is table of number;
    nt1 nt_table_type:=nt_table_type(1,2,3,5);
    v1 number:=&v1;
  begin
    if v1 member of nt1 then
      dbms_output.put_line('v1是nt1的元素');
    end if;
end;

-- 使用操作符IS A SET:用于检测嵌套表是否包含重复的元素值
 declare
   type nt_table_type is table of number;
   nt1 nt_table_type:=nt_table_type(1,2,3,5);
 begin
   if nt1 is a set then
     dbms_output.put_line('嵌套表NT1无重复值');
   end if;
 end;

--  FORALL语句 执行批量insert,update,delete操作时,使用forall语句,FORALL不是循环语句
--  insert
 declare
    type id_table_type is table of number(6) index by binary_integer;
    type name_table_type is table of varchar2(10) index by binary_integer;
    id_table id_table_type;
    name_table name_table_type;
  begin
    for i in 1..10 loop
      id_table(i):=i;
      name_table(i):='name'||to_char(i);
    end loop;
  forall i in 1..name_table.count
   insert into demo values(id_table(i),name_table(i));
  end;
 
-- update
declare
    type id_table_type is table of number(6) index by binary_integer;
    type name_table_type is table of varchar2(10) index by binary_integer;
    id_table id_table_type;
    name_table name_table_type;
  begin
    for i in 1..5 loop
      id_table(i):=i;
      name_table(i):='n'||to_char(i);
    end loop;
    forall i in 1..id_table.count
       update demo set name=name_table(i) where id=id_table(i);
 end;

-- delete
declare
    type id_table_type is table of number(6) index by binary_integer;
    id_table id_table_type;
  begin
    for i in 1..3 loop
      id_table(i):=i;
    end loop;
  forall i in 1..id_table.count
   delete from demo where id=id_table(i);
  end;

-- 在FORALL语句上使用INDECES OF子句:用于跳过NULL集合元素
forall i in indices of id_table

-- 在FORALL语句上使用VALUES OF子句
forall i in values of index_pointer

-- 使用SQL%BULK_ROWCOUNT属性:专门为FORALL语句提供,用于取得在执行批量绑定操作时第i个元素所作用的行数
 declare
   type dno_table_type is table of number(3);
   dno_table dno_table_type:=dno_table_type(10,20);
 begin
   forall i in 1..dno_table.count
      update emp set sal=sal*1.1 where deptno=dno_table(i);
      dbms_output.put_line('第2个元素更新的行数:'||sql%bulk_rowcount(2));
 end;
 

-- 在select into 语句中使用BULK COLLECT 子句:可以一次将SELECT语句的多行结果检索到集合变量中
declare
 type ename_table_type is table of emp.ename%type;
 ename_table ename_table_type;
begin
 select ename bulk collect into ename_table from emp;
 for i in 1..ename_table.count loop
  dbms_output.put_line('ename_table('||i||'):'||ename_table(i));
 end loop;
end;

-- 游标 单行数据
declare
 cursor emp_cursor is select ename,sal from emp where deptno = 10;
 v_ename emp.ename%type;
 v_sal emp.ename%type;
 
begin
 open emp_cursor;
 loop
  fetch emp_cursor into v_ename,v_sal;
  eixt when emp_cursor%notfound;
  dbms_output.put_line(v_ename||': '||v_sal);
  end loop;
  close emp_cursor;
end;

-- 游标 多行数据
declare
  cursor emp_cursor is select ename from emp where sal > 10000;
  type ename_table_type is table of emp.ename%type;
  ename_table ename_table_type;
begin
 open emp_cursor;
 loop
  fetch emp_cursor bulk collect into ename_table;
  exit when emp_cursor%notfound;
 end loop;
 close emp_cursor;
 for i in 1..ename_table.count loop
  dbms_output.put_line('工资超过10000员工：'||ename_table(i));
 end loop; 
end;

-- 游标 多行数据 限制读取行数
declare
  cursor emp_cursor is select ename from emp where sal > 10000;
  type ename_table_type is table of emp.ename%type;
  ename_table ename_table_type;
  rows number:=8;
begin
 open emp_cursor;
 loop
  fetch emp_cursor bulk collect into ename_table limit rows;
  exit when emp_cursor%notfound;
 end loop;
 close emp_cursor;
 for i in 1..ename_table.count loop
  dbms_output.put_line('工资超过10000员工：'||ename_table(i));
  dbms_output.put_line('员工数：'||emp_cursor%rowcount);
 end loop; 
end;

-- 基于游标定义记录变量
declare
 coursor emp_coursor is select ename,sal from emp where empno = 10;
 emp_record emp_coursor%rowtype;
begin
 open emp_coursor;
 loop
  fetch emp_coursor into emp_record;
  exit when emp_coursor%notfound;
 end loop;
 close emp_coursor;
end;

-- 参数游标
   定义参数游标时,游标参数只能指定数据类型,而不能指定长度.
declare
 coursor emp_coursor(no number) is select ename,sal from emp where empno = no;
 emp_record emp_coursor%rowtype;
begin
 open emp_coursor(10);
 loop
  fetch emp_coursor into emp_record;
  exit when emp_coursor%notfound;
  dbms_output.put_line('员工姓名：'||emp_record.ename||'员工工资：'emp_record.sal);
 end loop;
 close emp_coursor;
end;

-- 使用游标更新或删除数据
  要通过游标更新或删除数据,在定义游标时必须要带有FOR UPDATE子句
  cursor cursor_name(parameter_name datetype) is select_statement for update [of column_reference] [nowait]
  for update子句用于在游标结果集数据上家行共享锁,防止其他用户在相应行执行DML操作
  of子句确定哪些表要加锁,没有OF子句,则在所引用的全部表上加锁
  nowait子句用于指定不等待锁
  必须在UPDATE后DELETE语句中引用WHERE CURRENT OF子句
  update table_name set column=.. where current of cursor_name;
  delete table_name where current of cursor_name;
 
declare
 cursor emp_cursor is select ename,sal from emp for update;
 v_ename emp.ename%type;
 v_sal emp.sal%type;
begin
 open emp_cursor;
 loop
  fetch emp_cursor into v_ename,v_sal;
  exit when emp_cursor%notfound;
  if v_oldsal < 2000 then
   update emp set sal=sal+1000 where current of emp_cursor;
  end if;
  end loop;
  close emp_cursor;
end;

-- 使用游标for循环
每循环一次提取一次数据，在提取了所有数据后,自动退出循环并隐含的关闭游标
delare
 cursor emp_cursor is select ename,sal from emp;
 begin
  for emp_record in emp_cursor loop
   dbms_output.put_line('第'||emp_cursor%rowcount||'个雇员'||emp_record.ename);
  end loop;
end;

-- 在游标FOR循环中直接使用子查询
begin
 for emp_record in (select ename,sal from emp)loop
  dbms_output.put_line(emp_record.ename);
 end loop;
end;

-- 游标变量使用示例
-- 在定义FEF CURSOR类型时不指定RETURN子句
declare
 type emp_cursor_type is ref cursor;
 emp_cursor emp_cursor_type;
 emp_record emp%rowtype;
begin
 open emp_cursor for select * from emp where deptno=10;
 loop
  fetch emp_cursor into emp_record;
  exit when emp_cursor%notfound;
  dbms_output.put_line('第'||emp_curosr%rowcount||'个雇员: '||emp_record.ename);
 end loop;
 close emp_cursor;
end;

--  在定义FEF CURSOR类型时指定RETURN子句
declare
 type emp_record_type is record(name varchar2(10),salary number(6,2));
 type emp_cursor_type is ref cursor return emp_record_type;
 emp_cursor emp_cursor_type;
 emp_record emp_record_type;
begin
 open emp_cursor for select ename,sal from emp where deptno = 20;
 loop
  fetch emp_cursor into emp_record;
      exit when emp_cursor%notfound;
      dbms_output.put_line('第'||emp_curosr%rowcount||'个雇员: '||emp_record.ename);
    end loop;
    close emp_cursor;
end;

-- 使用纯度级别
-- 为了对包的公用函数加以限制,在定义包规范时,可以使用纯度级别(purity level)限制公用函数
  语法:pragma restrict_references (function_name,wnds[,wnps][,rnds][,rnps]);
  wnds:用于限制函数不能修改数据库数据(禁止DML)
  wnps:用于限制函数不能修改包变量(不能给包变量赋值)
  rnds:用于限制函数不能读取数据库数据(禁止SELECT操作)
  rnps:用于限制函数不能读取包变量(不能将包变量赋值给其他变量)
 
   1.建立包规范
  create or replace package purity is
    minsal number(6,2);
    maxsal number(6,2);
    function max_sal return number;
    function min_sal return number;
    pragma restrict_references(max_sal,wnps);--不能修改
    pragma restrict_references(min_sal,wnps);
  end;
 
  2.建立包体
  create or replace package body purity is
   function max_sal return number
   is
   begin
     return maxsal;
   end;
  
   function min_sal return number
   is
   begin
     return minsal;
   end;
  
   begin
     select min(sal),max(sal) into minsal,maxsal from emp;
   end;
  end purity;

-- 语句触发器
create or replace trigger tr_sec_emp
before insert or update or delete on emp
begin
 if to_char(sysdate,'DY','nls_date_language=AMERICAN') in ('SAT','SUN')
 then
  raise_application_error(-20001,'不能在休息日改变雇员信息');
 end if;
end
 
-- 行触发
-- 建立BEFORE行触发器
create or relpace trigger tr_emp_sal
before update of sal on emp
for each row
begin
  if :new.sal<:old.sal then
     raise_application_error(-20010,'工资只涨不降');
  end if ;
end;

-- 批量动态SQL
create or replace procedure drop_table(table_name varchar2)
is
 sql_statement varchar2(100);
begin
 sql_statement := 'drop table'||table_name;
 execute immediate sql_statement;
end;

exec drop_table('worker');

--  当直接使用EXECUTE IMMEDIATE处理带有RETURNING子句的DML时,只能处理作用在单行上的DML语句
    如果DML语句作用在多行上,则必须要使用BULK子句
declare
 salary number(6,2);
  sql_stat varchar2(100);
begin
 sql_stat := 'update emp set sal=sal*(1+perccent/100)'||' where empno=:eno returning sal into :salary';
 execute immediate sal_stat using &1,&2 returning into salary;
 dbms_output.put_line('新工资:'||salary);
end;

-- 多行查询实例
declare
 type emp_cursor_type is ref cursor;
 type ename_table_type is table of emp.ename%type index by binary_integer;
 type sal_table_type is table of emp.salary%type index by binary_integer;
 ename_table ename_table_type;
 sal_table sal_table_type;
 emp_cursor emp_cursor_type;
 emp_record emp%rowtype;
  sql_stat varchar2(100);
begin
 sql_stat:='select * from emp where deptno=:dno'||'returning ename,sal into :name,:salary';
 open emp_cursor for sql_stat using &dno return ename_table,sal_table;
 loop
  fetch emp_cursor bulk collect into emp_record;
  eixt when emp_cursor%notfound;
  dbms_output.put_line('雇员名:'||emp_record.ename||',工资:'||emp_record.sal);
  end loop;
  close emp_cursor;
end;

-- 对象实体 static、member
-- static方法用于访问对象类型,在对象类型上执行全局操作
-- STATIC方法只能由对象类型访问,不能由对象实例访问
-- MEMBER方法用于访问对象实例的数据,如果在对象类型中需要访问特定对象实例的数据,则必须要定义MEMBER方法.
-- MEMBER方法只能由对象实例调用,而不能由对象类型调用.

-- 建立对象类型
create or repalce type person_type3 as object(
 name varchar2(10),gender varchar2(2),
 birthday date,regdate date,
 static function get_date return date,
 member function get_info return varchar2);
 
-- 建立对象类型实体
create or replace type body person_type3 is
 static function get_date return date is
 begin
  return sysdate;
 end;
 member function get_info return varchar2
 is
 begin
  return '姓名:'||name||',注册日期:'||regdate;
 end;
end;

-- 在对象类型上使用STATIC方法:对象类型调用
  begin
    insert into employee_tab3(eno,sal,job,person) values(&no,&salary,'&title',person_typ3("&name','&sex','&birthdate',person_typ3.getdate()));

-- 使用MEMBER方法:实例对象调用
  declare
    v_person person_typ3;
  begin
    select person into v_person from employee_tab3 where eno=&no;
    dbms_output.put_line(v_person.get_info());
  end;

-- 使用对象类型继承时,在定义父类型时必须要指定NOT FINAL 选项,如果不指定该选项,默认为FINAL,表示该对象类型不能被继承
-- 建立父对象：
create or replace type person_type9 as object(
 name varchar2(10),gender varchar2(2),birthdate date,address varchar2(50),
 member function get_info return varchar2)not final;
 
-- 建立对象类型体
create or replace type body person_type9 as
 member function get_info return varchar2
 is
  begin
   return  '姓名:'||name||',家庭住址:'||address;
  end;
end;

-- 建立子对象类型（继承）
create or replace type employee_type9 under person_type9(
   eno number(6),sal number(6,2),job varchar2(10),
    member function get_other return varchar2);

-- 建立对象的类型的（私有方法）
create or repalce type body employee_type9 as
 member function get_other return varchar2
 is
 begin
  return  '雇员名称:'||name||',工资:'||sal;
 end;
end;

-- 调用建立对象表
sql> create table employee_tab9 of employee_tab9;
sql> insert into employee_tab9 values('马丽','女','01-11月-76','呼和浩特15号',2,2000,'高级钳工');

declare
 v_employee employee_type9;
begin
 select value(a) into v_employee from employee_tab9 a where a.eno=&no;
  dbms_output.put_line(v_employee.get_info);
  dbms_output.put_line(v_employee.get_other);
end;

--  在定义部分定义例外名,然后在例外和ORACLE错误之间建立关联,最终在例外处理部分捕捉并处理例外.
    当定义ORACLE错误和例外之间的关联关系时,需要使用伪过程EXCEPTION_INIT
declare
 e_integrity exception;
 pragma exception_init(e_integrity,-2291);
begin
 update emp set deptno=&dno where empno=&eno;
exception
 when e_integrity then
  dbms_output.put_line('该部门不存在');
end;