ORACLE提供可以把PL/SQL程序存儲在數據庫中,并可以在任何地方運行他,這樣就叫做存儲過程或者函數,意思就說白了,就是你之前寫的select,什么update,這些東西存儲起來,我想把這些操作存儲起來,像表,視圖,結構式的,當然他不是對象,這里不叫對象,他也能夠存儲起來,像Function,這是系統提供的,你自己創建的,之前寫過,我們可以刪掉他,這兒說明他確確實實存在,包括像這個觸發器,這里沒有,說明沒有創建,當你創建一個函數,存儲過程的時候,那就能夠保存下來,什么時候想調用,就調用就完了,觸發器,當你想對某個表進行操作的時候,也是類似于一個函數的東西,這個時候叫做觸發器,自動的觸發他,就是這樣,它是可以被保存起來的,過程和函數的唯一區別就是函數總是向調用者返回數據,而過程不返回數據,相當于一個有返回值,一個沒有,函數是有的,過程是沒有的

創建create or replace function,跟前面寫的視圖都一樣,然后這個function的名,如果有參數的話,后邊寫上他整個的參數類型,這相當于是一個小括號,function涉及到的形參一樣,形參你寫到這里邊,先寫一個參數名,再寫參數類型,這里就不需要指定一個參數的大小了,僅僅是幾個類型就行,這個形參什么時候給你填進去,就是通過declare語句,我們上午講了declare,begin,在begin；里面調用這個函數的時候,參數是什么,return緊接著就是你返回函數的返回值類型,然后這個叫is,is里面操作的你可以把它假象成,加上declare,函數在整個的使用當中,你需要定義一個變量,你就定義到這兒,再是你這個函數的執行體,如果有異常的話,我給你寫的這個結構,函數的結構

存儲函數,這個結構怎么寫,他就是create or replace function,取個function名,就寫func_name,括號寫上你參數的類型,參數類型,id number類型的,逗號,name,你想查指定表當中的id,這個id,這個名字的那個人,等等,或者你想查一下id這個人的工資,salary也是number類型的,往那放,這里又具體分,講什么out,一會說到這再說,這是定義這個函數,然后他return,返回值類型,假設你要返回,這里不寫這個id,寫dept_id,我返回指定部門的,所有的工資,指定部門可能有很多人,這些所有人的工資,return也是工資,工資是number類型的,你就返回number類型的,然后is,你在這個過程當中,是不是再涉及到你要定義一些變量,如果需要的話,你寫在is這一塊,就是函數的使用過程中,需要聲明的變量,或者叫記錄類型,寫在這,聲明一個變量,記錄類型,甚至還可以是游標,游標就寫在這,然后呢,begin,開始,這是函數的執行,函數的執行體,在這個過程當中,出現一個異常,考慮異常的處理,處理函數處理過程中的異常,處理完了以后,就給他end,這是存儲函數的一個過程,這里相當于declare,我們寫一個PL/SQL的時候,declare begin end,你就把他當成declare,就是在聲明函數中調用到的變量,這是函數的一個形參,這樣創建好以后,他就有這個了,有這個function以后,大概形式就是這樣create or replace function func_name(dept_id number,salary number)return number;isbeginexceptionend;

22.2 返回一個"helloworld: atguigu"的字符串，其中atguigu 由執行函數時輸入。--函數的聲明(有參數的寫在小括號里) create or replace function hello_func(v_logo varchar2) --返回值類型 return varchar2 is --PL/SQL塊變量的聲明 begin --函數體return 'helloworld'|| v_logo; end;這就是這個函數的作用,函數的helloworld,調用這個函數就返回hellworld,看他怎么寫,create or replace function,叫hello_world,這個函數沒有形參,返回helloworld這個字符串,return varchar2,不用寫那個什么,不用寫分號,也不用寫大小,is聲明,也不用聲明,直接begin,return,這是你函數的執行體,執行后的結果,因為你這個函數是需要有返回值的,我就在這里給你返回,沒有異常,沒有就end,這是一個最簡單的存儲函數,創建了create or replace function hello_worldisbeginreturn "helloworld";end;

創建以后我們在這看,這里就有一個hello_world

正常來寫,declare什么什么,這里也不用declare,直接begin,dbms_output.put_line,調用你hello_world這個函數,end,這就出來了begindbms_output.put_line(hello_world);end;

或者你在這使用select語句也行,select hello_world,from dualselect hello_world from dual;

這就是具體調用這個函數了,這是這個例子,然后我把它寫得復雜一點,給這個函數帶上一個參數,hello_world1,帶個參數,什么參數,加上一個v_log,它是一個varchar2類型的,返回他這個varchar2,然后is是聲明,聲明寫到這里也行,變量的聲明,這里不需要變量聲明,這個變量已經有了不需要聲明,begin,v_log他賦一個值,直接return,這里我定義了一個形參,相當于,然后呢走到這,創建好了create or replace function hello_world1(v_logo varchar2)return varchar2isbeginreturn 'helloworld' || v_logo;end;

然后這個調用,select就這么寫,select hello_world1,這個參數,varchar型的,他,from他select hello_world1('atguigu') from dual;

這是select,如果你想使用PL/SQL句型的話,那你就這樣begindbms_output.put_line(hello_world('atguigu'));end;

一種是使用PL/SQL程式的形式,一種是SELECT的形式,關鍵是你要會寫這個,這個格式,創建一個函數,函數名,函數是否需要參數,需要的話就需要寫上一個參數,參數的話只要指明類型就行,函數一定是有返回值的,一定會return一個東西,然后函數在使用過程中,是否需要聲明一些變量,相當于在JAVA里邊,方法里邊,局部變量,起在is和begin之間,你也可以把它理解成declare,我們之前寫在declare和begin之間的東西可以寫在這兒,begin,你這個函數具體執行的函數體,結束,一定要記住這,那這兒你也可以加上一個輸出語句,這里or replace相當于把它替換了create or replace function hello_world1(v_logo varchar2)return varchar2isbegindbms_output.put_line('人家是函數啦,么么噠');return 'helloworld' || v_logo;end;

select hello_world1('atguigu1') from dual;

就相當于他執行這個函數體,你這個傳入的就放到這兒了,跟你講JAVA的情況一樣,實際上你聲明一個值,給我傳一個值,調用你傳入的這個值,就是這個意思,這個你看返回的這個順序,你看它是先輸出的這個,再打印的這個,因為它是有返回值的,他select先拿到你這個返回值,然后返回完了以后,你要這樣寫的話,或者說有兩種執行方式,另一種begindbms_output.put_line(hello_world1('atguigu'));end;

這個先輸出的這個,是按照你的順序,這個你了解一下就行,重點是你如何來定義這個函數,這個函數就這樣,然后我們看別的例題22.3 創建一個存儲函數，返回當前的系統時間 create or replace function func1 return date is --定義變量 v_date date; begin--函數體--v_date := sysdate;select sysdate into v_date from dual;dbms_output.put_line('我是函數哦');return v_date; end;執行法1： select func1 from dual; 執行法2： declarev_date date; beginv_date := func1;dbms_output.put_line(v_date); end;創建一個存儲函數,返回當前的系統時間,這個就是函數的作用,create or replace function,get_sysdate,這是他的函數名,return是一個date類型的,然后is需要聲明變量嗎,聲明一個也行,聲明一下就這樣,date類型的,這個不需要指明大小,begin,可以這樣寫,v_date := sysdate,然后呢,然后return date,沒有異常,直接end,這樣寫create or replace function get_sysdatereturn dateisv_date date;beginv_date := sysdate;return v_date;end;

然后你在這執行一個select get_sysdate from dual;

23. 定義帶參數的函數: 兩個數相加create or replace function add_func(a number, b number) return number is beginreturn (a + b); end;執行函數begindbms_output.put_line(add_func(12, 13)); end; 或者select add_func(12,13) from dual;定義帶參數的函數,create or replace function,add,add相加的意思,兩個參數相加,定義v_num1,number類型的,v_num2,還是number類型,return返回number類型的,is需要再聲明一個變量不,v_sum計算他們的和,number類型的,指明10個,begin,v_sum等于v_num1加上v_num2,return v_sumcreate or replace function add add_param(v_num1 number,v_num2 number)return numberisv_sum number(10);beginv_sum := v_num1 + v_num2;return v_sum;end;

創建好了,這個,運行一下select add_param(2,4) from dual;

在這也行,這個就是使用PL/SQL塊的形式begindbms_output.put_line(add_param(3,4));end;

24. 定義一個函數: 獲取給定部門的工資總和, 要求:部門號定義為參數, 工資總額定義為返回值.create or replace function sum_sal(dept_id number)return numberiscursor sal_cursor is select salary from employees where department_id = dept_id;v_sum_sal number(8) := 0; beginfor c in sal_cursor loopv_sum_sal := v_sum_sal + c.salary;end loop; --dbms_output.put_line('sum salary: ' || v_sum_sal);return v_sum_sal; end;執行函數begindbms_output.put_line(sum_sal(80)); end;定義一個函數,獲取給定部門的工資總和,要求部門號定義為參數,工資總和定義為返回值,返回總額肯定需要你把一個人一個人的工資加起來,或者再準確點說,所有人都會把工資遍歷一遍,使用游標吧,這就把剛才講的給他結合起來了,function get_sal,dept_id,number類型的,返回值類型,number的,is,需不需要聲明一個變量,聲明sum工資,是一個number類型的,10,分號結束,然后begin,方法體怎么來寫,我們現在這個時候再往下寫,怎么求指定部門所有的和,使用一個游標,游標是不是又得寫在當時的declare里,那你這個就寫這兒唄,指定這個人的部門的工資,cursor給他創建完以后,你就得open,或者我用一個for更簡單,讓他加到這個里邊,c.salary,這個你要先給他指定一個值,游標都不用關了,返回create or replace function get_sal(dept_id number)return numberisv_sumsal number(10) := 0;cursor salary_cursor is select salary from employees where department_id = dept_id;beginfor c in salary_cursor loopv_sumsal := v_sumsal + c.salary;end loop;return v_sumsal;end;

get_sal,select,get_sal,80號部門的select get_sal(80) from dual;

你要在這用declare也行,begin,dbmsdeclarev_deptid number(4) := 80;begindbms_output.put_line(get_sal(v_deptid));end;

25. 關于 OUT 型的參數: 因為函數只能有一個返回值, PL/SQL 程序可以通過 OUT 型的參數實現有多個返回值要求: 定義一個函數: 獲取給定部門的工資總和 和 該部門的員工總數(定義為 OUT 類型的參數). 要求: 部門號定義為參數, 工資總額定義為返回值.create or replace function sum_sal(dept_id number, total_count out number)return numberiscursor sal_cursor is select salary from employees where department_id = dept_id;v_sum_sal number(8) := 0; begintotal_count := 0;for c in sal_cursor loopv_sum_sal := v_sum_sal + c.salary;total_count := total_count + 1;end loop; --dbms_output.put_line('sum salary: ' || v_sum_sal);return v_sum_sal; end; 執行函數:delare v_total number(3) := 0;begindbms_output.put_line(sum_sal(80, v_total));dbms_output.put_line(v_total); end;我們說函數,自變量可以有多個,但是函數返回值永遠只有一個,這樣才叫函數,這里為什么要定義一個out型的參數,因為我們知道return永遠是return一個,但是我又想存儲過程當中,除了要返回工資總額之外,我還想讓你返回點別的,比如說這個,除了要你返回給定部門的工資總額之外,我還想看看這個部門有多少員工,那你這個一個函數,相當于兩個返回值,一個是指定部門的工資總和,一個是該部門的員工總數,你要是變成兩個function,那變成一個里面怎么去做,這個就要使用一個out型的參數,這個參數怎么用,我們就以他舉例說一下,在這個里邊也有一些說明,in默認不寫的話他就是in,比如in,標記表示傳遞給函數的值,在該函數執行過程中不改變,你像我們80號部門的80,就是你在執行的過程當中,只要你用到這個變量了,他都是80,out標記表示一個值在函數中進行計算并通過該參數傳遞給調用語句,這個變量需要你在函數執行之前,來定義一下,而這個值是可以被改變的,我們就寫一個這個例子,定義一個函數,獲取給定部門的工資總額,和該部門的員工總數,把這個定義成一個out型的參數,部門號定義為參數,工資總額定義為返回值,那我們這個怎么寫,定義一個函數,給定部門的工資總額和他,除了把它當做一個參數之外,還得給一個總人數,total_count,也是一個number類型的,然后為了表明他不是一個參數,你要這樣寫的話,那你調用這個函數,需要指定兩個參數,不是一個參數,這個也是一個返回值,但是不是return出來的,加一個out,get_sal1,這個number是工資總和,is,工資總和為0,然后呢,cursor還是指向他,需要指明total_count還是一個值,一開始沒有,所以他的值可以改變,賦值為0,這個是把它工資給累加起來,同時讓他累加,還是返回工資總和,這個變量,就是在你計算工資總和的過程當中,不知不覺的把這個值給他,記錄了一下,那么這個值就相當于記錄到了,然后給他end結束,執行完了,create or replace function get_sal1(dept_id number,total_count out number)return numberisv_sumsal number(10) := 0;cursor salary_cursor is select salary from employees where department_id = dept_id;begintotal_count := 0;for c in salary_cursor loopv_sumsal := v_sumsal + c.salary;total_count := total_count + 1;end loop;return v_sumsal;end;

怎么用這個,多了一個,declare,我定義一個變量,number吧,我在這dbms,調用這個函數,前面是80號部門的,我讓他去記錄,這個先給他賦一個值先,這個打印的結果就是你這個的返回值,80號部門的工資,但是他把我這個值給記錄下來了,那我這個時候打印我這個值先,這個是工資總和,這個是公司的人數declarev_num number(5) := 0;begindbms_output.put_line(get_sal(80,v_sum));dbms_output.put_line(v_num);end;

這個就是講out變量怎么用,正常我們講一個函數就一個返回值,這里同樣也是一個,我就想在運算當中再給我算一個值,其實相當于在看一個因變量,是直接讓你返回的,然后被記錄下來了,我可以通過這種方式,讓你在計算總工資的時候,把這個值給改變了,再后邊就是存儲過程,大家先把我講的存儲函數的給看一看,過程沒有返回值,剛才我們講的都是存儲函數,函數是有返回值的,你把這個格式給記住一下,這是函數的格式,函數的格式有了,你再往里套,過程沒有返回值了,這不叫function了,這個是面向過程26*. 定義一個存儲過程: 獲取給定部門的工資總和(通過 out 參數), 要求:部門號和工資總額定義為參數create or replace procedure sum_sal_procedure(dept_id number, v_sum_sal out number)iscursor sal_cursor is select salary from employees where department_id = dept_id; beginv_sum_sal := 0;for c in sal_cursor loop--dbms_output.put_line(c.salary);v_sum_sal := v_sum_sal + c.salary;end loop; dbms_output.put_line('sum salary: ' || v_sum_sal); end; [執行] declare v_sum_sal number(10) := 0; beginsum_sal_procedure(80,v_sum_sal); end;獲取給定部門的工資總和,然后部門號和工資總額定義為參數,那我寫的時候把工資總額當成返回值了,是吧,他定義為參數,對應的用存儲過程寫,沒有返回值,所以你只能用out,用out來寫,set serveroutput on

這是剛才寫的存儲函數,然后現在讓你寫一個存儲過程,給定部門的工資總額,然后create or replace,然后get_sal2,部門號這是作為一個參數,工資總額sumsal,out型的,也是number類型,沒有return,is就不用再定義了,sumsal賦個值,添加進去以后,for循環完了以后,sumsal,end結束create or replace procedure get_sal2(dept_id number,sumsal out number)iscursor salary_cursor is select salary from employees where department_id = dept_id;beginsumsal := 0;for c in salary_cursor loopsumsal := sumsal + c.salary;end loop;dbms_output.put_line(sumsal);end;

創建好以后調用一下,你不是要傳一個變量嗎,80號部門,你要寫一個變量,那你還得在PL/SQL寫,declare,v_sal,number類型的,冒號等于,給一個初始化值,begin,80號部門的,把他放進去,end結束declarev_sal number(10) := 0;beginget_sal2(80,v_sal);end;

因為他沒有返回值了,就像我們JAVA里面的函數,這就打印80號部門的工資總和,這個就是一個存儲過程,你要是函數搞清楚了,存儲過程,是比較簡單的,把這個改一改,沒有return,這里邊也不要有return,一般里邊都會有一個輸出語句,或者你也不一定是輸出了,也有可能是增刪改,因為增刪改是不需要返回的,增刪改不需要返回,所以把增刪改定義成一個存儲過程27*. 自定義一個存儲過程完成以下操作: 對給定部門(作為輸入參數)的員工進行加薪操作, 若其到公司的時間在 (? , 95) 期間, 為其加薪 %5[95 , 98) %3 [98, ?) %1 得到以下返回結果: 為此次加薪公司每月需要額外付出多少成本(定義一個 OUT 型的輸出參數).create or replace procedure add_sal_procedure(dept_id number, temp out number)iscursor sal_cursor is select employee_id id, hire_date hd, salary sal from employees where department_id = dept_id;a number(4, 2) := 0; begintemp := 0; for c in sal_cursor loopa := 0; if c.hd < to_date('1995-1-1', 'yyyy-mm-dd') thena := 0.05;elsif c.hd < to_date('1998-1-1', 'yyyy-mm-dd') thena := 0.03;elsea := 0.01;end if;temp := temp + c.sal * a;update employees set salary = salary * (1 + a) where employee_id = c.id;end loop; end;對于給定部門,把它作為一個參數,相當于對指定部門進行操作,進行加薪,既然沒有返回值,如果你需要一些返回的情況,必須使用OUT來聲明一下,他這里也確實需要,需要判斷這個部門一個月額外付出多少成本,對每個人都加薪,額外輸出多少錢,create or replace,procedure,加薪,add_sal,使用這個,add_sal加薪,需要在里面給定部門,dept_id,number類型的,逗號,他需要判斷額外需要多少錢,這個作為一個out型的參數,他也是number類型的,沒有return了,is,額外的要定義一個變量,一個部門有很多人,就定義一個cursor,cursor,讓他去遍歷一下,cursor sal_cursor is select,你到底需要查什么,employees,where department_id = dept_id,這都需要查什么,看你都需要什么,一般也是需要的,額外需要付出多少,一般employee_id也用,不對的話再改,employee_id,salary,然后再加上hire_date,我先取這三個變量,從這三個表當中,這是加薪,這是加的一個東西,多定義一個變量,一個是0.03,一個是0.01,比如我再定義一個變量,v_i吧,number類型的,兩位小數,就是用來記錄這個東西,差不多,然后直接begin,用for,省得你open,fetch,close,循環,然后呢,判斷一下hire_date,不同的hire_date所賦的值是不一樣的,如果c的hire_date是在這個時間段的,這個怎么寫啊,讓這個hire_date小于一個時間,字符串和date的一個轉換,那我就這樣一下吧,to_char,是一個date型的,如果這個是小于1995,按說字符串是不可以比大小的,但是如果你字符串是純數字的話,他有一個隱式轉換,隱式轉換純數字,純數字的字符串可以隱式轉換,如果是這樣的話,then,為其加百分之五,v_i記錄一下,加薪的幅度,elsif,1998,0.03,其余的else,直接then了,然后if就是end if,end if完了,還是得更新一下他的工資,下面操作,1更新工資,2付出的成本,先看這個,一開始他的成本在這兒呢,在這給他賦值,temp冒號等于0,付出的成本,temp = temp + c.salary*v_i,這就是額外多的,更新工資,update,update employees,set salary = salary(1+v_i),where employee_id = c.employee_id,然后給他這樣操作了,操作完以后,end loop,endcreate or replace procedure add_sal(dept_id number,temp_sal out number)iscursor sal_cursor is select employee_id,salary,hire_date from employees where department_id = dept_id;v_i number(4,2) := 0;begintemp_sal := 0;for c in sal_cursor loopif to_char(c.hire_date,'yyyy') < '1995' then v_i := 0.05;elsif to_char(c.hire_date,'yyyy') < '1998' then v_i := 0.03;else v_i := 0.01;end if;update employees set salary = salary*(1+v_i) where employee_id = c.employee_id;temp_sal := temp_sal + c.salary*v_i;end loop;end;

創建好以后,然后呢,執行,這里你定義一個變量,declare定義一個變量,定義一下到底要花多少錢,number,10個單位,冒號等于0,begin,add_sal,80號部門的,然后呢v_temp,我把你要花的給你輸出一下create or replace procedure add_sal(dept_id number,temp_sal out number)iscursor sal_cursor is select employee_id,salary,hire_date from employees where department_id = dept_id;v_i number(4,2) := 0;begintemp_sal := 0;for c in sal_cursor loopif to_char(c.hire_date,'yyyy') < '1995' then v_i := 0.05;elsif to_char(c.hire_date,'yyyy') < '1998' then v_i := 0.03;else v_i := 0.01;end if;update employees set salary = salary*(1+v_i) where employee_id = c.employee_id;temp sal := temp_sal + c.salary*v_i;end loop;dbms_output.put_line(temp_sal);end;

declarev_temp number(10) := 10;beginadd_sal(80,_v_temp);end;

這就叫存儲過程,我覺得存儲過程比存儲函數要簡單,你把存儲函數理解理解,這個改一改就了事了,這就是存儲過程,存儲過程就搞定了

?

總結

以上是生活随笔為你收集整理的plsql(轻量版)-存储函数存储过程的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。