pl/sql学习笔记

--pl/sql
--块：由函数（pl语法，sql语句）组成；异常，过程，函数，包，触发器，事务控制（对数据库产生改变时）
--支持流程控制，块的嵌套
/*块的三个部分其中第一三部分可以省略
  1.声明部分
    declare
  2.执行部分
    begin
      end;
  3.异常处理部分
      exception
        when    ??  then ??;
  
*/
--块可以命名和匿名
--set serveroutput on;
begin
    dbms_output.put_line('hello');
end;
create procedure sp_insert is
begin
  insert into mytable values (25, 'jack');
end;
/
exec sp_insert; create or replace procedure sp_delete is
begin
delete from mytable where id = 25;
end;
/
exec sp_delete;

set serveroutput on --打开输出选项；
begin
dbms_output.put_line('hello,word');
end;
/

declare v_ename varchar2(5);
begin
select ename into v_ename from emp where empno = &no; dbms_output.put_line('雇员名：' || v_ename);
end;
/

  -------------
declare v_ename varchar2(5); v_sal number(7, 2);
begin
select ename, sal into v_ename, v_sal from emp where empno = &no; dbms_output.put_line('雇员名：' || v_ename || ';工资：' || v_sal);
--异常处理
exception
when no_data_found then dbms_output.put_line('编号找不到，或输入有误!');
end;
/

  --过程
  /*
      过程用于执行特定的，当建立过程时，既可以指定输入参数in，
      也可以批定输出out，通过地过程中使用输入参数，可以次数据
      传递到执行部分；通过使用输出参数，要以快乐幸福执行部分的
      数据传递到用，在sqlplus中要create procedura命令来建立过程
                                                                                                                       
  */

create procedure sp_update(spName varchar2, newSal number) is
begin
  --执行部分，根据用户名支无话不谈工资，
update myemp set sal = newSal where ename = spName;
end;
/

  --函数：输入雇员的姓名，返回该雇员的年薪
create function sp_fun1(spName varchar2) return number is
yearSal number(7, 2);
begin
select sal * 12 + nvl(comm, 0) * 12 into yearSal from emp where ename = spName; return yearSal;
end;
/
var ac number; call sp_fun1('SCOTT') into :ac;

  --包
  /*
       包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成
       create package sp_package is 
              procedure     update_sal(name varchar2,newSal number);
              function      sp_fun1(name varchar2) return number;
       end;
  */

  --触发器
  /*
       指隐含的执行的存储过程，当定义触发器时，必须要指定触发的事件
       和触发的操作，常用的触发事件包括insert,update,delete语句
       面触发实际就是一个pl/sql块，可以使用create tigger 来建立触发器
  */

  --变量类型
  /*
       scalar标量类型
       v_ename                     varchar2(10);
       v_sal                       number(6,2):=5.4;                       
       v_hiredate                  date;
       v_valid                     boolean not null default false;
  */

declare c_tax_rate number(3, 2) := 0.03; v_ename emp.ename%type; --v_ename的类型和表emp.ename的类型一样
v_sal number(7, 2); v_tax_sal number(7, 2);
begin
select ename, sal into v_ename, v_sal from emp where empno = &no; v_tax_sal := v_sal * c_tax_rate; dbms_output.put_line('name=' || v_ename || '--sal' || v_sal || '==tax' || v_tax_sal);
end;
/

  ---------------------------
declare
--定义一种数据类型，类似于c语言中的结构体
type emp_record_type is
record(name emp.ename%type, salary emp.sal%type);

  --声明一个emp_record_type类型的变量sp_record
sp_record emp_record_type;
begin
select ename, sal into sp_record from emp where empno = &no; dbms_output.put_line('姓名=' || sp_record.name || '；工资=' || sp_record.salary);
end;
/
-----------------------
declare
--定义一种数据类型，类似于c语言中的结构体
type emp_record_type is
table of  emp.ename%type index by binary_integer;

  --声明一个emp_record_type类型的变量sp_record
sp_record emp_record_type;
begin
select ename into sp_record(1) from emp where empno =7369;
dbms_output.put_line('姓名=' || sp_record(1) );
end;
/




---------输入部门号，显示所有员工
declare 
--定义游标
type sp_emp_cursor is ref cursor;
--定义游标变量
test_cursor        sp_emp_cursor;

--定义变量
v_ename            emp.ename%type;
v_sal              emp.sal%type;

begin
     --把test_cursor和一个select结合
     open test_cursor for select ename,sal from emp where deptno=&no;
     --循环取出
     loop
          fetch       test_cursor into v_ename,v_sal;
          --判断test_cursor是否为空
          exit when test_cursor%notfound;
          dbms_output.put_line('姓名：'||v_ename||'工资：'||v_sal);
     end loop;
end;

--编写一个过程，可以输入一个雇员名，如果亥雇员的工资低于2000，
--就给该雇员式资增加10%；
create or replace procedure  sp_addSal(spName varchar2) is
v_sal  myemp.sal%type;
begin
       select sal into v_sal from emp where ename=spName;
        if v_sal<2000 then
       update myemp set sal = sal + sal*0.1 where ename = spName;
       end if ;
end;

--编写一个过程，可以输入一个雇员名，如果该雇员
--的补助不是0就在原来的基础上加100，是0就加200；
--
create or replace procedure sp_addCommit(spName varchar2) is
v_comm  myemp.comm%type;
begin
        select nvl(comm,0) into v_comm from myemp where ename=spName;
       
        if  v_comm<>0 then
              update myemp set comm=comm+100 where ename=spName;
        else  
               dbms_output.put_line(v_comm||'');
              update myemp set comm=nvl(comm,0)+200 where ename=spName;
        end if;
end;

/*
    编写一个过程，可以输入一个雇员编号，如果该雇员的职位是president就给他的
    工资增加1000，如果该雇员的职位是manger就给他的工资增加500，其它职位的
    雇员工资增加200
*/

create or replace procedure sp_updateSal(spEmpno number) is
v_job myemp.job%type;
begin
       select job into v_job from myemp where empno = spEmpno;
       if v_job='PRESIDENT' then
          update myemp set sal = sal + 1000 where empno=spEmpno;
       elsif  v_job='MANAGER'then
          update myemp set sal = sal + 500 where empno=spEmpno;
       else
          update myemp set sal = sal + 200 where empno=spEmpno;
       end if;
       
end;

------循环语句
create table userTest(
       id              number(4),
       name            varchar(10)
);
create or replace procedure sp_For(spName varchar2) is
       v_i     number:=1;
       
begin 
       /*loop
              insert into userTest values(v_i,spName);
              exit when v_i=10;
              v_i:=v_i+1;
       end loop;*/
       <<start_loop>>
       while v_i<=10 loop
              insert into userTest values(v_i,spName);
              exit when v_i=10;
              v_i:=v_i+1;
              if v_i=5 then
                            go to start_loop;
               else
                            null;--空语句
               end if;
       end loop;
end;

--编写分页过程
create table book 
(
       id                   number,
       name                 varchar2(50),
       publishhouse         varchar2(50)
);

create or replace sp_insertBook(spId number,spName  varchar2,spHouse varchar2)         
is     
begin
       insert into book values(spId,spName,spHouse);
end;

select rownum,e.* from (select * from emp) e where rownum<=10 ;
select * from emp;
select * from (select rownum rn,e.* from (select * from emp) e where rownum<=10) where rn>5;

select * from (select t1.*,rownum rn from (select * from emp  where rownum<=10) t1) where rn>=5;

--------------------------------------------------------------
create or replace package testpackage as
 type test_cursor is ref cursor;
end testpackage;

/*
2
1  1,2
2  3,4

3
1  1,3
2  4,6
3  7,8

4(m)
1  1,4
2  5,8
3  9,12
4  13,16
.  .,.
n  s,e
e=n*m;
s=(n*m)-m+1=n*m-m+1;
13=4*4-4+1;

3=2*2-2+1;

9=4*3-4+1;
*/
create or replace procedure fenye(tableName       in     varchar2,
                                     mypagesize      in      number,--一页显示的记录数
                                     pagenow         in      number,--向显示第几页
                                     myrows          out     number,--总记录数
                                     mypagecount     out     number,--总页数
                                     p_cursor        out     testpackage.test_cursor--返回的记录数
                                     )               is
v_sql varchar2(1000);                                                                  
v_begin     number:=pagenow*mypagesize-mypagesize+1;--开始记录次序
v_end       number:=pagenow*mypagesize;--结束记录次序
begin
      v_sql:=   'select * from (select t1.*,rownum rn from (select * from '
               ||tableName||'  where rownum<='||v_end||') t1) where rn>='||v_begin||';';
      open p_cursor for v_sql;--自动转换为sql语句   
      --计算总记录数和总也数；
      v_sql:='select count(*) from '||tableName;
      execute immediate v_sql into myrows;
      if mod(myrows,mypagesize)=0 then
         mypagecount = myrows/mypagesize;
      else
         mypagecount = myrows/mypagesize+1;
      end if;
end;

--异常处理：预定义例外，非预定义例外和自定义例外三种
set serveroutput on --打开输出选项；
create or replace procedure sp_query
is
v_name            emp.ename%type;
begin
       select ename into v_name from emp where empno=&no;
       dbms_output.put_line('姓名是：'||v_name);
       exception
             when no_data_found then dbms_output.put_line('编号找不到，或输入有误!');     
end;
/

declare 
v_name       emp.ename%type;
begin
             select ename into v_name from emp where empno=&no;
                    dbms_output.put_line('姓名是：'||v_name);
             exception 
                    when   no_data_found then --no_data_found  预定义列外
                      dbms_output.put_line('编号找不到，或输入有误!');          
                    
end;
/

declare 
    v_sal emp.sal%type;
begin
    select sal into v_sal from emp where empno=&no;
    case
           when v_sal<1000 then
                         dbms_output.put_line('<10000');
           when v_sal<2000 then
                         dbms_output.put_line('<20000');
    end case;
    exception 
        when case_not_found    then
             dbms_output.put_line('不再范围呃逆');
end;
/

--dup_val_on_index在唯一索引所对应的列上插入重复的值时，会隐含的触发例外

--invalid_cursor当试图在不合法的游标上报告操作时，会触发该例外，例如；试
--图、从没有打开的游标提取数据，或是关关闭没有打开的游标则会该例外

--invalid_number当输入的数据有误时

--too_many_rows当执行 select into语句时，如果返回超过一行刚会触发该例外

--zero_divide    10/0

--value_error当在执行同仁时，如果变量的长度不足以容纳实际数据，则会触发该例外   v_ename   varchar2(5)
--ename varchar2(50)；

--自定义例外
create or replace procedure ex_test(spNo number) 
is
       myex exception;
begin
       update myemp set sal = sal +1000  where empno=spNo;
       if sql%notfound then --这是表示没有update成功
          raise myex;--raise触发myex例外
       end if;
       when     myex then
                dbms_output.put_line('没有更新成功！');
end;

-------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
create or replace package testpackage as
 type test_cursor is ref cursor;
end testpackage;

/*
2
1  1,2
2  3,4

3
1  1,3
2  4,6
3  7,8

4(m)
1  1,4
2  5,8
3  9,12
4  13,16
.  .,.
n  s,e
e=n*m;
s=(n*m)-m+1=n*m-m+1;
13=4*4-4+1;

3=2*2-2+1;

9=4*3-4+1;
*/
create or replace procedure fenye(tableName       in     varchar2,
                                     mypagesize      in      number,--一页显示的记录数
                                     pagenow         in      number,--向显示第几页
                                     myrows          out     number,--总记录数
                                     mypagecount     out     number,--总页数
                                     p_cursor        out     testpackage.test_cursor--返回的记录数
                                     )               is
v_sql       varchar2(1000);                                                                  
v_begin     number:=pagenow*mypagesize-mypagesize+1;--开始记录次序
v_end       number:=pagenow*mypagesize;--结束记录次序
begin
      v_sql:= 'select * from (select t1.*,rownum rn from (select * from '
               ||tableName||'  where rownum<='||v_end||') t1) where rn>='||v_begin||';';
      open p_cursor for v_sql;--自动转换为sql语句   
      --计算总记录数和总也数；
      v_sql:='select count(*) from '||tableName;
      execute immediate v_sql into myrows;
      if mod(myrows,mypagesize)=0 then
         mypagecount := myrows/mypagesize;
      else
         mypagecount := myrows/mypagesize+1;
      end if;
end; 
/