程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> PostgreSQL >> PostgreSQL教程(十九):SQL語言函數

PostgreSQL教程(十九):SQL語言函數

編輯:PostgreSQL

一、基本概念:

    SQL函數可以包含任意數量的查詢,但是函數只返回最後一個查詢(必須是SELECT)的結果。在簡單情況下,返回最後一條查詢結果的第一行。如果最後一個查詢不返回任何行,那麼該函數將返回NULL值。如果需要該函數返回最後一條SELECT語句的所有行,可以將函數的返回值定義為集合,即SETOF sometype。

    SQL函數的函數體應該是用分號分隔的SQL語句列表,其中最後一條語句之後的分號是可選的。除非函數聲明為返回void,否則最後一條語句必須是SELECT。事實上,在SQL函數中,不僅可以包含SELECT查詢語句,也可以包含INSERT、UPDATE和DELETE等其他標准的SQL語句,但是和事物相關的語句不能包含其中,如BEGIN、COMMIT、ROLLBACK和SAVEPOINT等。

    CREATE FUNCTION命令的語法要求函數體寫成一個字符串文本。通常來說,該文本字符串常量使用美元符($$)圍住,如:
 復制代碼 代碼如下:
    CREATE FUNCTION clean_emp() RETURNS void AS $$
        DELETE FROM emp WHERE salary < 0;
    $$ LANGUAGE SQL;
 
    最後需要說明的是SQL函數中的參數,PostgreSQL定義$1表示第一個參數,$2為第二個參數並以此類推。如果參數是復合類型,則可以使用點表示法,即$1.name訪問復合類型參數中的name字段。需要注意的是函數參數只能用作數據值,而不能用於標識符,如:
 復制代碼 代碼如下:
    INSERT INTO mytable VALUES ($1);   --合法
    INSERT INTO $1 VALUES (42);          --不合法(表名屬於標示符之一)
   
二、基本類型:
    
    最簡單的SQL函數可能就是沒有參數且返回基本類型的函數了,如:
 復制代碼 代碼如下:
    CREATE FUNCTION one() RETURNS integer AS $$
        SELECT 1 AS result;
    $$ LANGUAGE SQL;
 
    下面的例子聲明了基本類型作為函數的參數。
 復制代碼 代碼如下:
    CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$
        SELECT $1 + $2;
    $$ LANGUAGE SQL;
    # 通過select調用函數。
    postgres=# SELECT add_em(1,2) AS answer;
     answer
    --------
          3
    (1 row)
 
    在下面的例子中,函數體內包含多個SQL語句,它們之間是用分號進行分隔的。
 復制代碼 代碼如下:
    CREATE FUNCTION tf1 (integer, numeric) RETURNS numeric AS $$
        UPDATE bank SET balance = balance - $2 WHERE accountno = $1;
        SELECT balance FROM bank WHERE accountno = $1;
    $$ LANGUAGE SQL;

三、復合類型:

    見如下示例:

    1). 創建數據表,這樣與之對應的復合類型也隨之生成。
 復制代碼 代碼如下:
    CREATE TABLE emp (
        name        text,
        salary      numeric,
        age         integer,
    );
 
    2). 創建函數,其參數為復合類型。在函數體內,可以像引用基本類型參數那樣引用復合類型,如$1。訪問復合類型的字段使用點表達式即可,如:$1.salary。
    復制代碼 代碼如下:
 CREATE FUNCTION double_salary(emp) RETURNS integer AS $$
        SELECT ($1.salary * 2)::integer AS salary;
    $$ LANGUAGE SQL;
 
    3). 在select語句中,可以使用emp.*表示emp表的一整行數據。
 復制代碼 代碼如下:
    SELECT name, double_salary(emp.*) AS dream FROM emp WHERE age > 30;
 
    4). 我們也可以使用ROW表達式構造自定義的復合類型,如:
 復制代碼 代碼如下:
    SELECT name, double_salary(ROW(name, salary*1.1, age)) AS dream FROM emp;
 
    5). 創建一個函數,其返回值為復合類型,如:
 復制代碼 代碼如下:
    CREATE FUNCTION new_emp() RETURNS emp AS $$
        SELECT ROW('None', 1000.0, 25)::emp;
    $$ LANGUAGE SQL;
 
    6). 調用返回復合類型的函數。
 復制代碼 代碼如下:
    SELECT new_emp();
 
    7). 調用返回復合類型的函數,同時訪問該返回值的某個字段。
 復制代碼 代碼如下:
    SELECT (new_emp()).name;
 

四、帶輸出參數的函數:

    還有一種方法可以用於返回函數執行的結果,即輸出參數,如:
 復制代碼 代碼如下:
    CREATE FUNCTION add_em2 (IN x int, IN y int, OUT sum int) AS $$
        SELECT $1 + $2
    $$ LANGUAGE SQL;
 
    調用方法和返回結果與add_em(帶有返回值的函數)完全一致,如:
 復制代碼 代碼如下:
    SELECT add_em(3,7);
   
    這個帶有輸出參數的函數和之前的add_em函數沒有本質的區別。事實上,輸出參數的真正價值在於它為函數提供了返回多個字段的途徑。如,
 復制代碼 代碼如下:
    CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int) AS $$
        SELECT $1 + $2, $1 * $2
    $$ LANGUAGE SQL;
 
    調用方式沒有改變,只是返回結果多出一列。
 復制代碼 代碼如下:
    SELECT * FROM sum_n_product(11,42);
     sum | product
    -----+---------
      53 |     462
    (1 row)
 
    在上面的示例中,IN用於表示該函數參數為輸入參數(缺省值,可以忽略),OUT則表示該參數為輸出參數。

五、返回結果作為表數據源:

    所有SQL函數都可以在查詢的FROM子句裡使用。該方法對於返回復合類型的函數而言特別有用,如果該函數定義為返回一個基本類型,那麼該函數生成一個單字段表,如果該函數定義為返回一個復合類型,那麼該函數生成一個復合類型裡每個屬性組成的行。見如下示例:
    1). 創建一個數據表。
 復制代碼 代碼如下:
    CREATE TABLE foo (
        fooid    int,
        foosubid int,
        fooname  text
    );
 
    2). 創建SQL函數,其返回值為與foo表對應的復合類型。
 復制代碼 代碼如下:
    CREATE FUNCTION getfoo(int) RETURNS foo AS $$
        SELECT * FROM foo WHERE fooid = $1;
    $$ LANGUAGE SQL;
 
    3). 在FROM子句中調用該函數。
 復制代碼 代碼如下:
    SELECT *, upper(fooname) FROM getfoo(1) AS t1;
 
六、返回集合的SQL函數:

    如果SQL函數的返回值為SETOF sometype,那麼在調用該函數時,將返回最後一個SELECT查詢的全部數據。這個特性通常用於把函數放在FROM子句裡調用,見如下示例:
    CREATE FUNCTION getfoo(int) RETURNS setof foo AS $$
        SELECT * FROM foo WHERE fooid = $1;
    $$ LANGUAGE SQL;   
    在FROM子句中調用了返回復合類型集合的函數,其結果等同於:SELECT * FROM (SELECT * FROM foo WHERE fooid = 1) t1;
    SELECT * FROM getfoo(1) AS t1;    
   
七、多態的SQL函數:

    SQL函數可以聲明為接受多態類型(anyelement和anyarray)的參數或返回多態類型的返回值,見如下示例:
    1). 函數參數和返回值均為多態類型。
 復制代碼 代碼如下:
    CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS $$
        SELECT ARRAY[$1, $2];
    $$ LANGUAGE SQL;
 
    其調用方式和調用其它類型的SQL函數完全相同,只是在傳遞字符串類型的參數時,需要顯式轉換到目標類型,否則將會被視為unknown類型,如:
 復制代碼 代碼如下:
    SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray;
 
    2). 函數的參數為多態類型,而返回值則為基本類型。
 復制代碼 代碼如下:
    CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS $$
        SELECT $1 > $2;
    $$ LANGUAGE SQL;
 
    3). 多態類型用於函數的輸出參數。
 復制代碼 代碼如下:
    CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 anyarray) AS $$
        SELECT $1, ARRAY[$1,$1]
    $$ LANGUAGE sql;

八、函數重載:

    多個函數可以定義成相同的函數名,但是它們的參數一定要有所區分。換句話說,函數名可以重載,此規則有些類似於面向對象語言中的函數重載,見如下示例:
 復制代碼 代碼如下:
    CREATE FUNCTION test(int, real) RETURNS ...
    CREATE FUNCTION test(smallint, double) RETURNS ...

    由於在PostgreSQL中函數支持重載,因此在刪除函數時,也必須指定參數列表,如:
 復制代碼 代碼如下:
    DROP FUNCTION test(int, real);
    DROP FUNCTION test(smallint,double);
 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved