Oracle PL/SQL学习笔记

Oracle PL/SQL 学习笔记

-- 案例
create or replace procedure sp_pro2 is
begin
-- 执行部分
delete from mytest where name ='name1';
end;

-- 最简单的块
begin  
   qdbms_output.put_line('hello world');
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;
  /* select sal into v_sal from emp where empno = &no;*/
   dbms_output.put_line('employeer: ' || v_ename || ' salary: ' || v_sal);
exception
   -- 异常处理
   when no_data_found then
   dbms_output.put_line('朋友，请重新输入编号');
end;
/

-- 案例4
create procedure sp_pro3(spName varchar2,newSal number) is
begin
       --执行部分-- 根据用户名去修改工资
       update emp set sal = newSal where ename =spName;
end;


-- 函数案例
-- 输入雇员的姓名，返回该雇员的年薪
create function sp_fun2(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;



-- 创建包
-- 创建一个包 sp_package
-- 声明该包有一个过程
-- 声明该包有一个函数
create package sp_package is
       procedure update_sal(name varchar2,newsal number);
       function annual_income(name varchar2) return number;
end;


-- 给包 sp_package 实现包体
create or replace package body sp_package is
procedure update_sal(name varchar2,newsal number)
is
begin
          update emp set sal = newsal where ename = name;
end;
function annual_income(name varchar2)
return number is
annual_salary number;
begin
           select sal*12+nvl(comm,0) into annual_salary from emp
           where ename = name;
           return annual_salary;
end;
end;


-- 案例
declare
   c_tax_rate number(3,2):=0.03;
   --用户名
   v_ename emp.ename%type;
   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('姓名:' || v_ename ||',工资:' || v_sal || '所得税:'|| v_tax_sal);
end;

--pl/sql记录实例
declare
  --定义一个pl/sql记录类型emp_record_type，
  -- 类型保护三个数据name，salary，title
  type emp_record_type is record(
       name emp.ename%type,
       salary emp.sal%type,
       title emp.job%type
  );
  -- 定义了一个sp_record变量，这个变量类型是emp_record_type
  sp_record emp_record_type;
begin
  select ename,sal,job into sp_record
  from emp where empno=7788;
  dbms_output.put_line('员工名:' || sp_record.name || ',工资是' || sp_record.salary);
end;


-- pl/sql 表实例

declare
-- 定义一个pl/sql表类型sp_table_type,该类型是用于存放emp.ename%type
-- index by binary_integer表示下标是整数
type sp_table_type is table
of emp.ename%type index by binary_integer;
-- 定义一个sp_table变量，这个变量的类型是sp_table_type
sp_table sp_table_type;
begin
         select ename into sp_table(0) from emp where empno=7788;
         dbms_output.put_line('员工名：' || sp_table(0));
end;

-- pl/sql cursor实例
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;


-- procedure test
create or replace procedure sp_pro6(spName varchar2) is
       --定义
       v_sal emp.sal%type;
begin
      --执行
      select sal into v_sal from emp where ename=spName;
      --判断
      if v_sal <2000 then
         update emp set sal = sal*1.1 where ename=spName;
      end if;      
end;

-- procedure test2
create or replace procedure sp_pro6(spName varchar2) is
       --定义
       v_comm emp.comm%type;
begin
      --执行
      select comm into v_comm from emp where ename=spName;
      --判断
      if v_comm != 0 then
         update emp set comm =comm + 100 where ename=spName;
      else
         update emp set comm = comm + 200 where ename=spName;
      end if;     
end;

-- procedure if elsif
create or replace procedure sp_pro6(spNo number) is
  -- 定义
  v_job emp.job%type;
begin
  -- 执行
  select job into v_job from emp where empno = spNo;
  if v_job = 'PRESIDENT' then
    update emp set sal = sal + 1000 where empno = spNo;
  elsif v_job = 'MANAGER' then
    update emp set sal = sal + 500 where empno = spNo;
  else
    update emp set sal = sal + 200 where empno = spNo;
  end if;
end;

-- loop test
create or replace procedure sp_pro6(spName varchar2) is
       -- 定义 :=表示赋值
       v_num number:=1;
begin
loop
       insert into users values(v_num,spName);
       -- 判断退出条件
       exit when v_num = 10;
       -- 自增
       v_num:= v_num +1;
end loop;
end;


-- while test;
create or replace procedure sp_pro6(spName varchar2) is
       -- 定义 :=表示赋值
       v_num number:=11;
begin
       while v_num <=20 loop
       -- 执行
       insert into users values(v_num,spName);
       v_num:=v_num+1;
       end loop;
end;

-- goto test
declare
  i int := 1;
begin
  loop
    dbms_output.put_line('i=' || i);
    if i = 10 then
      goto end_loop;
    end if;
    i := i + 1;
  end loop;
  <<end_loop>>
  dbms_output.put_line('end loop');
end;

-- 创建表
create table book(
  bookId number(4,2),
  bookName varchar2(50),
  publishHouse varchar2(50)
);

-- 编写过程
-- in 表示输入参数, 默认为in
-- out 表示输出参数
create or replace procedure sp_pro7(spBookId in number,
                   spbookName in varchar2,
                   sppublishHouse in varchar) is
begin
  insert into book values(spBookId,spbookName,sppublishHouse);
end;
         
-- 有输入和输出的存储过程
create or replace procedure sp_pro8
(spNo in number,spName out varchar2,
 spSal out number,spJob out varchar2) is
begin
      select ename,sal,job into spName,spSal,spJob from emp where empno = spNo;
end;                   

-- 返回结果集的过程
-- 1.创建一个包,在该包中定义一个类型test_cursor,一个游标
create or replace package testpackage as
       type test_cursor is ref cursor;
end testpackage;

-- 2. 创建存储过程
create or replace procedure sp_pro9(spNo in number,
       p_cursor out testpackage.test_cursor) is
begin
       open p_cursor for select * from emp where deptno=spNo;
end;

-- 3. 如何在java中调用


-- oracle 的分页

select rownum rn,t1.* from (select * from emp) t1

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

-- 在分页时，大家可以把下面的sql语句当成模板使用
select * from 
(select rownum rn,t1.* from (select * from emp) t1 where rownum <= 10)
where rn >= 6;


-- 开发一个包
create or replace package testPackage as
       type test_cursor is ref cursor;
end testPackage;

-- 开始编写分页的过程
create or replace procedure fenye
(tableName in varchar2,
 pageSize in number, -- 一页现实记录数
 pageNow in number, 
 myrows out number, -- 总记录数
 myPageCount out number, -- 总页数
 p_cursor out Testpackage.test_cursor -- 返回记录集
 ) is
 -- 定义部分
 -- 定义sql语句 字符串
 v_sql varchar2(1024);
 -- 定义两个整数
 v_begin number:=(pageNow-1)*pageSize + 1;
 v_end number:=pageNow*pageSize;
 begin
 -- 执行部分
 v_sql:='select * from (select rownum rn,t1.* from (select * from '||
 tableName||' order by sal) t1 where rownum <= '||v_end||') where rn >= '||v_begin||'';
 -- 把游标和sql语句关联起来
 open p_cursor for v_sql;
 -- 计算myRows和myPageCount
 -- 组织一个SQL
 v_sql:='select count(*) from ' || tableName;
 -- 执行SQL，并把返回的值，赋给myRows
 execute immediate v_sql into myRows;
 -- 计算myPageCount
  if mod(myRows,pageSize)=0 then
    myPageCount := myRows/pageSize;
  else
    myPageCount := myRows/pageSize + 1;
  end if;
  
  -- 关闭游标
  -- close p_cursor;
 end; 
 -- 使用java测试
 
 
 -- 新的需要，排序

 -- 例外案例
 declare
 -- 定义
 v_name emp.ename%type;
 begin
    select ename into v_name from emp where empno = &no;
    dbms_output.put_line('name: '|| v_name);
 exception
    when no_data_found then
         dbms_output.put_line('no not exist');
 end;
 
 -- 自定义例外
 create or replace procedure ex_test(spNo number) 
 is
 -- 定义一个例外
 myex exception;
 
 begin
   -- 更新用户sal
   update emp set sal = sal + 1000 where empno= spNo;
   -- sql%notfound 这是表示没有update
   -- raise myex; 触发myex
   if sql%notfound then
      raise myex;
   end if;
   exception
       when myex then
       dbms_output.put_line('not update success');
 end;    
 
 
 -- 创建视图，把emp表薪水sal<1000 的雇员映射该视图(view)
 create view myview as select * from emp where sal < 1000;
 
 create view myview as select emp.empno,emp.ename,dept.dname from emp,dept
 where emp.deptno = dept.deptno