oracle-遊標-預存程序-函數-包

標籤：package   count   in out   immediate   upd   tab   返回結果   執行函數   pre   

一、預存程序不可以在insert,update,delete中直接使用,可以有return但代表的是退出過程過程有三種類型：不傳回值，可以返回多個值，參數有三種類型，分別如下:        in:只輸入，不返回結果,預設為in        out:只返回結果，不輸入，要想取出輸出變數的值必須通過pl/sql塊的變數取出        in  out:可輸入，又可返回結果,要想取出輸出變數的值必須通過pl/sql塊的變數取出    --文法    create or replace procedure 名稱(a1 in varchar2,a2 out varchar2,a3 in out int)        as         begin        end;    --測試in        create or replace procedure getSex1(p_customerName in varchar2)        as             v_sex varchar2(20);        begin            select sex into v_sex from customer where customerName=p_customerName;            dbms_output.put_line(v_sex);        end;     --測試out      create or replace procedure getSex2(p_customerName in varchar2,p_sex out varchar2)        as         begin          select sex into p_sex from customer where customerName like p_customerName;        exception          when others then raise_application_error(-20001,‘occur error‘);        end;    --測試in out        create or replace procedure getSex3(p_param1 in out varchar2)         as         begin            select sex into p_param1 from customer where customerName=p_param1;        end;    --在pl/sql塊中執行        declare        begin              getSex1(‘a1‘);        end;    --在pl/sql塊中執行        declare            p_sex varchar2(20);        begin            getSex2(‘a1‘,p_sex);            dbms_output.put_line(p_sex);            getSex2(‘a2‘,p_sex);            dbms_output.put_line(p_sex);        end;                declare            p_sex varchar2(20);        begin            p_sex:=‘a1‘;            getSex3(p_sex);            dbms_output.put_line(p_sex);            p_sex:=‘a2‘;            getSex3(p_sex);            dbms_output.put_line(p_sex);        end;二、函數：        只能返回且必須返回一個結果，可以直接用在insert,update,delele,select中    可以有多個return;    文法:        create or replace function 函數名(p1 varchar2) return varchar2        as        begin        exception        end;    執行個體:根據姓名返回姓別        create or replace function getSex(p_customerName varchar2) return varchar2        as            v_sex customer.sex%type;        begin            select sex into v_sex from customer where customerName=p_customerName;            return v_sex;        end;執行函數:        1、在sql(sqlplus)            select getSex(customerName),sex from customer;        2、在pl/sql塊中　　　　　　declare                v_sex customer.sex%type;            begin                v_sex:=getsex(‘a1‘);                dbms_output.put_line(v_sex);            end;預存程序:                  1.聲明處沒有return,可以返回多個值,用輸出變數(out,in out)返回                    return代表退出程式，不返回結果.                  2.不可以在insert,update,delete,select                    中直接使用，只能通過exec中用 函數:                  1.聲明處有return,只可以返回一個值,                    return代表返回一個值.                  2.可以在insert,update,delete,select                    中直接使用.三、觸發器set serveroutput on;create or replace trigger teacher_trigger after insert or update or delete on customer for each row begindbms_output.put_line(‘the table data already has been modified....‘);end;四、遊標        declare                    v_customer customer%rowtype;                    v_customerName customer.customerName%type;                    cursor c1(v_customerName varchar2) is select * from customer     where customerName like v_customerName;              begin                    v_customerName:=‘&aa‘;                    open c1(v_customerName);                    fetch c1 into v_customer;                    while(c1%found) loop                        dbms_output.put_line(v_customer.customerName||‘      ‘||v_customer.sex);                        fetch c1 into v_customer;                    end loop;              end;五、包包:包中可以有多個方法，包包括包聲明與包主體，包聲明中聲明的方法名，參數名，類型，個數必須與包主體的方法完全一樣。       包聲明中聲明的變數是全域變數，大家都可以用       --實現包聲明create or replace package my_p as     function getReverse(v_name varchar2) return varchar2;    procedure teacher_modify_column(teacher_id number,column_name varchar2,column_value1 number);end;create or replace package body my_pasprocedure teacher_modify_column(teacher_id number,column_name varchar2,column_value1 number)      is         v_sql varchar2(200);      begin        v_sql:=‘update teachers set  ‘||column_name||‘ =  ‘||column_value1||‘   where teachers.teacher_id =  ‘||teacher_id;        EXECUTE IMMEDIATE v_sql;    end teacher_modify_column; function getReverse(v_name varchar2) return varchar2asv_title varchar2(200);i int:=1;j int:=0;begin j:=length(v_name); while (j>0) loop v_title:= substr(v_name,i,1)||v_title; i:=i+1; j:=j-1; end loop;return v_title;end;end;      create or replace package MyPackage as           type c_type is ref cursor;           function MyReverse(source varchar2) return varchar2;           procedure splitPage(p_sql varchar2,page int,pageSize int,result out c_type,pageCount out int);       end;       --實現包主體        create or replace package body MyPackage as               function MyReverse(source varchar2) return varchar2               as                  i int;                 j int;                 result varchar2(2000):=‘‘;               begin                 j:=length(source);                 i:=1;                 while(i<=j) loop                   result:=substr(source,i,1)||result;                   i:=i+1;                 end loop;                 return result;               end;             // 查詢語句           頁數                                                                procedure splitPage(p_sql varchar2,page int,pageSize int,result out c_type,pageCount out int)               as                  v_sql varchar2(500);                 startPage int;                 endPage int;                 v_rowCount int;               begin                 v_sql:=‘select count(*) from (‘||p_sql||‘)‘;                 dbms_output.put_line(v_sql);                 execute immediate v_sql into v_rowCount;                 pageCount:=ceil(v_rowCount/pageSize);                 if(page=0) then                    raise_application_error(-20001,‘申請的頁面太小‘);                 end if;                 if(page>pageCount) then                    raise_application_error(-20001,‘申請的頁面太大‘);                 end if;                 startPage:=(page-1)*pageSize;                 endPage:=page*pageSize;                 v_sql:=‘select * from (‘||p_sql||‘) where rowNum<=‘||to_char(endPage);                  v_sql:=v_sql||‘ minus ‘;                   v_sql:=v_sql||‘select * from (‘||p_sql||‘) where rowNum<=‘||to_char(startPage);                  dbms_output.put_line(v_sql);                  open result for v_sql;              end;    end;    --調用包中的方法        select MyPackage.MyReverse(sex) from customer;    --在pl/sql塊中調用    declare      pageCount int;      c1 mypackage.c_type;      v_customer customer%rowtype;    begin      mypackage.splitPage(‘select * from customer‘,1,2,c1,pageCount);      dbms_output.put_line(‘總頁數是‘||pageCount);      fetch c1 into v_customer;      while(c1%found) loop             dbms_output.put_line(v_customer.customername||‘  ‘||v_customer.sex);             fetch c1 into v_customer;      end loop;      close c1;      mypackage.splitPage(‘select * from customer‘,2,2,c1,pageCount);      fetch c1 into v_customer;      while(c1%found) loop             dbms_output.put_line(v_customer.customername||‘  ‘||v_customer.sex);             fetch c1 into v_customer;      end loop;      close c1;      mypackage.splitPage(‘select * from customer‘,3,2,c1,pageCount);      fetch c1 into v_customer;      while(c1%found) loop             dbms_output.put_line(v_customer.customername||‘  ‘||v_customer.sex);             fetch c1 into v_customer;      end loop;      close c1;              mypackage.splitPage(‘select * from customer‘,4,2,c1,pageCount);      fetch c1 into v_customer;      while(c1%found) loop             dbms_output.put_line(v_customer.customername||‘  ‘||v_customer.sex);             fetch c1 into v_customer;      end loop;      close c1;          end;

 

oracle-遊標-預存程序-函數-包