创建存储过程

create or replace  procedure sp_pro2 is
--执行部分
begin
  delete from mytest where name='lgd';
end;
 ------------------------------------------------------------------------------------------------------------------
--创建一个新表根据已经有的表mytest来创建一个名字为mytest2的新表

create table mytest2  as select * from mytest;

set serveroutput on --打开输出选项
begin
     dbms_output.put_line('hello');
end;
----------------------------------------------------------------------------------------------------------
 --定义部分和执行部分的pl/sql块
 declare
     v_ename varchar2(5);
  begin
      select ename into v_ename from emp   where empno=&no;
      dbms_output.put_line('雇员名:'||v_ename);
  end;
 ------------------------------------------------------------------------------------------------------------ 
--create or replace procedure sp_pro3 is 
  --定义部分
  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;
-----------------------------------------------------------------------------------------------------------------   
   --案例4  输入参数，输出
   create or replace procedure  sp_pro3 (sp_name varchar2,sp_newsal number) is
   begin
     update emp set sal=sp_newsal where ename=sp_name;
   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 abc number;
call sp_fun1('KING') into:abc;
------------------------------------------------------------------------------------------------------- --------- 
--计算一个人的个人所得税
--假设税率为0.03
declare 
c_tax_rate number(3,2):=0.03;
--用户名
v_ename emp.ename%type;
v_sal   emp.sal%type;
v_sal_tax number(7,2);
begin
  --开始执行
  select ename,sal  into v_ename,v_sal from emp where empno=&no;
  v_sal_tax:=v_sal*c_tax_rate;
  dbms_output.put_line('客户姓名为:'||v_ename||'工资为:'||v_sal||'应交税为:'||v_sal_tax);
end;
-----------------------------------------------------------------------------------------------------
--pl/sql 记录实例1
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);
 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;
begin
  select ename into sp_table(0) from emp where empno=7788;
  dbms_output.put_line('员工名:'||sp_table(0));
 end;
-------------------------------------------------------------------------------------------------------- 
 --请使用pl/sql编写一个块，可以输入部门号，并显示该部门所有员工的姓名和工资
 declare
 --定义游标类型 sp_emp_cursor
 type sp_emp_cursor  is ref cursor;
 --定义一个游标变量
 test_cursor sp_emp_cursor;
 --定义变量
 v_ename emp.ename%type;
 v_sal emp.sal%type;
begin
  --执行 
  --把test_crusor和一个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;
-----------------------------------------------------------------------------------------------------

 

--编写一个过程，可以输入一个雇员名，如果该雇员的工资低于1000，就
--给该雇员的工资增加10%
create or replace  procedure sp_pro4(spName varchar2) is
--定义变量
v_sal emp.sal%type;
begin
  --执行
  select sal into v_sal from emp where ename=spName;
  --判断
  if v_sal<1000 then
    update emp set sal=sal*1.1 where ename=spName;
  end if;
end;
--------------------------------------------------------------------------------------------------------------------------------
--二重条件分支 if-then-else
--编写一个过程，可以输入一个雇员名，如果该雇员的补助不是0，就在原来的基础上增加100；如果补助为0则
--补助设置为200

create or replace procedure sp_pro5(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=200 where ename=spName;
  end if;
end;
----------------------------------------------------------------------------------------------------------------------
--编写一个过程，可以输入一个雇员编号，如果该雇员的职位为
--PRESIDENT 就给他的工资增加1000，如果该雇员的职位为MANAGER
--就给他的工资增加500，其他职位的工资增加200

create or replace procedure sp_pro6(sNo number) is
--定义变量
v_job emp.job%type;
begin
  --执行
  select job into v_job from emp where empno=sNo;
  if v_job='PRESIDENT' then
         update emp set sal=sal+1000 where empno=sNo;
  elsif v_job='MANAGER'
     then update emp set sal=sal+500 where empno=sNo;
  else
      update emp set sal=sal+200 where empno=sNo;
  end if;      
end;
---------------------------------------------------------------------------------------------------------
create or replace procedure sp_pro7(sNumber number) is
--定义变量
v_jobb emp.job%type;
begin
  --执行
  select job into v_jobb from emp where empno=sNumber;
  if v_jobb='PRESIDENT' then
       update emp set sal=sal+1000 where empno=sNumber;
       elsif v_jobb='MANAGER'
          then update emp set sal=sal+500 where empno=sNumber;
       else
          update emp set sal=sal+200 where empno=sNumber;
       end if;
   end; 
------------------------------------------------------------------------------------------------------------
--创建一个存储过程，就是根据输入的一个用户名字，来插入10个用户，当插入10个用户的时候，则退出循环
--首先创建一个users表
create table users(userNo number,userName varchar2(20));  
--创建一个存储过程

create or replace procedure sp_pro8(name varchar2) is
v_no number:=1;
begin
  loop
      insert into users values(v_no,name);
      exit when v_no=10;
      v_no:=v_no+1;
  end loop;
    
  end;
-----------------------------------------------------------------------------------------------------------
--用while循环来判断

create or replace procedure sp_pro9(name varchar2) is
v_no number:=11;
begin
  while v_no<=20 
      loop
      insert into users values(v_no,name);
      v_no:=v_no+1;
end loop;
end;  
----------------------------------------------------------------------------------------------------------
--day7
--创建一个book表
create table book(bookId number,bookName varchar2(50),publishHouse varchar2(50));
--编写过程
create or replace procedure sp_pro10(spbookId in number,spbookName in varchar2,sppublishHouse in varchar2) is
begin
   insert into book values(spbookId,spbookName,sppublishHouse);  
end;
--------------------------------------------------------------------------------------------------
--有输入和输出的存储过程
create or replace procedure sp_pro11(spno in number,spName out varchar2) is
begin
    select ename into spName 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_pro12(spNo in number,p_cursor out testpackage.test_cursor) is
begin
  open p_cursor for select * from emp where deptno=spNo;
end;
--3 如何在java中调用存储过程

------------------------------------------------------------------------------------------------------------
--编写一个存储过程，要求输入表名，每页显示记录数，当前页，返回总记录数，总页数，和返回的结果集

create or replace procedure  sp_page(sp_tableName in varchar2,sp_perpageRecord in number,
sp_currentPage in number,sp_alltotalRecord out number,sp_totalPage out number,sp_resultcusor
out testpackage.test_cursor) is
declare 
--开始索引位置
v_firstrow  number;
--一页的末尾索引位置
v_lastrow  number;
begin
  select  count(*) into sp_alltotalRecord from sp_tableName;
  sp_totalPage=ceil(sp_alltotalRecord/sp_perpageRecord);
  --当分页的时候为整页的时候
  if sp_currentPage<=sp_totalPage
    then 
   --   sp_totalPage=sp_alltotalRecord/sp_perpageRecord;
     --    v_startindex=sp_currentPage*sp_perpageRecord+1;
      --   v_endindex=v_startindex+sp_perpageRecord;
      v_firstrow = (sp_currentPage–1)*sp_perpageRecord+1;
      v_lastrow = sp_currentPage * sp_perpageRecord; 
      open sp_resultcusor for select * from sp_tableName where 

  --当分页的时候不为整页时
  /**
  else 
    then
      sp_totalPage=sp_alltotalRecord/sp_perpageRecord+1;
      v_startindex=sp_currentPage*sp_perpageRecord+1;
      v_endindex=v_startindex+v_allpagetemp;
      */
  end if;
end;
--------------------------------------------------------------------------------------------------------------
--创建显示索引的格式
select t1.*,rownum rn from(select * from emp) t1;
SQL> select t1.*,rownum rn from(select * from emp) t1;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO         RN
----- ---------- --------- ----- ----------- --------- --------- ------ ----------
 7369 SMITH      CLERK      7902 1980-12-17     880.00    200.00     20          1
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    400.00     30          2
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30          3
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20          4
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30          5
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30          6
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10          7
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20          8
 7839 KING       PRESIDENT       1981-11-17    6000.00               10          9
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30         10
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20         11
 7900 JAMES      CLERK      7698 1981-12-3     1045.00               30         12
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20         13
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10         14
 
 上面多了rn这列数值
----------------------------------------------------------------------------------------------------------
--选择一个小于等于10的记录
select t1.*,rownum rn from(select * from emp) t1 where rownum<=10;
--分页时可以使用下面的模板
select * from(select t1.*,rownum rn from(select * from emp) t1 where rownum<=10) where rn>=6;
--开始编写分页的过程
create or replace procedure fenye
(tableName in varchar2,
Pagesize in number,
pageNow in number,
myrows out number, --总记录数
myPageCount out number,--总页数
sp_cursor out testpackage.test_cursor) is
--定义部分
v_begin number:=(pageNow-1)*Pagesize+1;
v_end   number:=pageNow*Pagesize;
v_sql  varchar2(1000);
--定义
begin
   --执行部分
  v_sql:='select * from(select t1.*,rownum rn from(select * from '||tableName||') t1 where rownum<='||v_end||') where rn>='||v_begin||'';
--把游标和sql关联起来
  open sp_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;
end;

 

--列外实例
--定义
declare 
v_ename emp.ename%type;
begin
  select ename into v_ename from emp where empno=&gno;
  dbms_output.put_line('名字:'||v_ename);
exception
     when no_data_found  then  
       dbms_output.put_line('该客户编号没有');
end;
----------------------------------------------------------------------------------------------
declare
 cursor emp_cursor is select ename,sal from emp;
 emp_record  emp_cursor%rowtype;
begin
    open emp_cursor;
    loop
       fetch emp_cursor into emp_record;
       EXIT WHEN emp_cursor%NOTFOUND; 
       dbms_output.put_line('客户名:'||emp_record.ename||'薪水:'||emp_record.sal);
    end loop;
end; 
----------------------------------------------------------------------------