توابع SQL

توابع SQL

توابع SQL در پایگاه داده ی ORACLE گنجانده شده (به صورت دورن ساخته و توکار در آن وجود دارند) و برای استفاده در تمامی دستورهای SQL قابل دسترس می باشند. این امکان نیز وجود دارد که شما با استفاده از PL/SQL توابع سفارشی خود را ایجاد کنید.

توابع تک سطری (single row function)

توابع تک سطری تنها یک سطر نتیجه (result row) را به ازای هر سطر از جدول یا نمایه (view) مورد پرس و جو (queried) (که از آن query گرفته شده) برمی گرداند. این دست از توابع می توانند در لیست های select، عبارت های WHERE ، START WITH ، CONNECT BY و نیز HAVING بکار گرفته شوند.

توابع عددی (number functions)

این دست از توابع، ورودی عددی (numeric input) پذیرفته و مقادیر عددی برمی گردانند. تعداد زیادی از این توابع قادرند مقادیری را برگردانند که تا 38 رقم دقت اعشار داشته باشد.
توابع عددی در موجود در ORACLE عبارتند از

• ABS
• ACOS
• ASIN
• ATAN
• ATAN2
• BITAND
• CEIL
• COS
• COSH
• EXP
• FLOOR
• LN
• LOG
• MOD
• POWER
• ROUND
• (number)
• SIGN
• SIN
• SINH
• SQRT
• TAN
• TANH
• TRUNC

تابع توکار ABS

ABS قدر مطلق (absolute value) عدد را برمی گرداند.
مثال زیر قدر مطلق عدد -87 را برمی گرداند.

SELECT ABS(-87) "Absolute" FROM DUAL;‎

        Absolute

        ‎----------‎

        ‎        87‎

تابع ACOS

عنوان داخلی کوچک رده 1

این تابع آرک کوسینوس عدد را برمی گرداند. ورودی ها در برد 1- تا 1 قرار می گیرند و خروجی ها در برد 0 تا pi. ورودی و خروجی هر دو به صورت رادیان نمایش داده می شوند.
مثال زیر آرک کوسینوس .3 را برمی گرداند.

SELECT ACOS(.3)"Arc_Cosine" FROM DUAL;‎

        Arc_Cosine

        ‎----------‎

        ‎1.26610367‎

مانند ACOS، در ORACLE توابعی برای محاسبه ی آرک سینوس (تابع ASIN ( و آرک تانژانت ( (ATANوجود دارد.

تابع CIEL

کم ترین یا کوچکترین عدد صحیح نزدیک به عدد ارائه شده یا را برمی گرداند.

این مثال کوچکترین عدد صحیح نزدیک به (بزرگتر از) 3.456 را برمی گرداند.

select ciel(3.456) “Ciel” from dual;‎

        Ciel

        ‎---------‎

        ‎        4‎

تابع FLOOR

بزرگترین عدد صحیح نزدیک به عدد ارائه شده و کوچکتر از آن عدد را برمی گرداند.

این مثال بزرگترین عدد صحیح نزدیک (به کوچکتر از) 3.456 آن را برمی گرداند.

select floor(3.456) “Floor” from dual;‎

        Floor

        ‎------------‎

        ‎        3‎

تابع COS

این تابع کوسینوس یک زاویه (بر حسب رادیان) را برمی گرداند.

مثال زیر کوسینوس زاویه ی 60 را برمی گرداند.

select  cos(60) “Cosine” from dual;‎

تابع SIN

سینوس یک زاویه (بر حسب رادیان) را برمی گرداند.

select  SIN(60) “Sine” from dual;‎

مثال بالا سینوس زاویه ی 60 را بازمی گرداند.

تابع TAN

تانژانت یک زاویه (بر حسب رادیان) را برمی گرداند.

این مثال تانژانت زاویه ی 60 را برمی گرداند.

select  Tan(60) “Tangent” from dual;‎

تابع MOD

باقی مانده ی تقسیم را برمی گرداند.

این مثال باقی مانده ی تقسیم 30 بر 4 را برمی گرداند.

Select mod(30,4) “MOD” from dual;‎

        MOD

        ‎---------‎

        ‎        2‎

تابع POWER

این تابع عددی را به توان عددی دیگر می برد.

این مثال با استفاده از تابع POWER، 2 را به توان 3 می برد.

select  power(2,3) “Power” from dual;‎

        POWER

        ‎---------‎

        ‎        8‎

تابع EXP

عدد e را به توان دلخواه n می رساند.

این مثال عدد e را به توان 2 برده و نتیجه را برمی گرداند.

select exp(2) “e raised to 2” from dual;‎

        E RAISED TO 2‎

        ‎-------------‎

تابع LN

لگاریتم طبیعی یک عدد را برمی گرداند.

این مثال لگاریتم طبیعی عدد 2 را برمی گرداند.

select ln(2) from dual;‎

        LN

        ‎------------‎

تابع LOG

لگاریتم یک عدد را برمی گرداند.

مثال زیر لگاریتم عدد 100 را برمی گرداند.

select log(10,100) from dual;‎

        LOG

        ‎---------‎

        ‎        2‎

تابع ROUND

یک فیلد عددی را به عددی با تعداد اعشار مشخص گرد می کند.
مثال زیر دو رقم اول اعشار عدد 3.4573 را به 46 گرد کرده و بقیه ی اعشار (73) را حذف می کند.

select round(3.4573,2) “Round” from dual;‎

        Round

        ‎------------‎

        ‎        3.46‎

تابع TRUNC

اعشار یک عدد را به تعداد مشخص شده قطع می کند.
مثال زیر با استفاده از تابع TRUNC اعشار عدد 3.4573 را تا دو رقم اول اعشار قطع می کند.

select round(3.4573,2) “Round” from dual;‎

        Round

        ‎------------‎

        ‎        3.45‎

تابع SQRT

این تابع جذر (ریشه ی دوم) یک عدد را برمی گرداند.

مثال زیر ریشه ی دوم عدد 16 را برمی گرداند.

select  sqrt(16) from dual;‎

        SQRT

        ‎---------‎

        ‎        4‎

توابع کاراکتری ( (Character Functions

توابع کاراکتری روی مقادیر نوع داده های CHAR و VARCHAR عملیات انجام می دهد.

تابع LOWER

یک رشته را با حروف کوچک برگردانده و نمایش می دهد.

select LOWER(‘SAMI’) from dual;‎

        LOWER

        ‎-------------‎

        sami

تابع UPPER

یک رشته را با حروف بزرگ بازیابی کرده و نمایش می دهد.

select UPPER(‘Sami’) from dual;‎

        UPPER

        ‎------------------‎

        SAMI

INITCAP

یک رشته را درحالی که حرف اول آن با حروف بزرگ نوشته شده، بازمی گرداند.

select INITCAP(‘mohammed sami’) from dual;‎

        INITCAP

        ‎------------------‎

        Mohammed Sami

LENGTH

تعداد کاراکترهای (طول) یک رشته را بازگردانی می نماید.

select length(‘mohammed sami’) from dual;‎

        LENGTH

        ‎------------‎

        ‎        13‎

SUBSTR

از این تابع می توان برای برش تعداد معينی از کاراکترهای يک رشته (به تعداد مشخص شده) بين دو نقطه ی مشخص استفاده کرد.
مثال زیر “sam” را از رشته ی “mohammed sami” برش داده و برمی گرداند.

select substr('mohammed sami',10,3) from dual;‎

        Substr

        ‎--------‎

        Sam

تابع INSTR

این تابع بررسی می کند آیا کاراکتر مورد نظر در یک رشته معین وجود دارد یا خیر. در صورت یافت شدن کاراکتر مورد نظر، تابع INSTR اولین مکانی که کاراکتر در آن ظاهر شده را برمی گرداند و در غیر این صورت 0 را برمی گرداند.
مثال زیر بررسی می کند آیا کاراکتر “a” در رشته ی “mohammed sami” وجود دارد یا خیر.

select instr('mohammed sami','a') from dual;‎

        INSTR

        ‎--------‎

        ‎4‎

تابع REPLACE

از اين تابع برای جايگزينی يک حرف يا کلمه خاص در يک رشته و جايگزينی آن با يک مجموعه کاراکتر جديد استفاده می شود .

Query زیر مجموعه کاراکتر “mohd” را جایگزین “mohammed” می کند.

select replace('ali mohd khan','mohd','mohammed') from dual;‎

        REPLACE

        ‎---------‎

        ali mohammed khan

تابع TRANSLATE

از این تابع به منظور کدگذاری کاراکترها استفاده می شود. به عنوان مثال، با استفاده از این تابع می توان کاراکترهای مشخصی را در یک رشته ی مشخص با کاراکترهای کد گذاری خود، جایگزین کرد.

Query مثال زیر، کاراکتر B را جایگزین کاراکتر A، C را جایگزین B، D را جایگزین C، E را جایگزین D ، .... A را جایگزین Z، b را جایگزین a، c را جایگزین b، d را جایگزین c، e را جایگزین d و در نهایت a را با z جایگزین می کند.

select ‎translate('interface','ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',

        ‎      'BCDEFGHIJKLMNOPQRSTUVWXYZAbcdefghijklmnopqrstuvwxyza') “Encrypt” from ‎dual;‎

        Encrypt

        ‎-----------‎

        Joufsgbdf

تابع SOUNDEX

این تابع بجای خود کاراکتراها تلفظ آن ها را بررسی می کند. به عنوان مثال برخی از افراد یک اسم را با املای متفاوت می نویسند، مانند “smith”، “smyth”یا “smythe”، ولی تمامی این ها فقط یک جور تلفظ می شوند. این تابع صرف نظر از املای کلمات، آن کلماتی که دارای تلفظ یکسان هستند را برمی گرداند.

تابع RPAD

این تابع به تعداد مشخص شده، کاراکتر در سمت راست کاراکتر دیگر قرار می دهد.
Query زیر سمت راست فیلد ename را با علامت " * " پر کرده تا تعداد کل کاراکترها به 10 برسد.

select rpad(ename,'*',10) from emp;‎

        Ename

        ‎----------‎

        Smith*****‎

        John******‎

        Mohammed**‎

        Sami******‎

تابع LPAD

این تابع به تعداد مشخص شده، کاراکتر در سمت چپ کاراکتر دیگر قرار می دهد.
Query زیر سمت چپ فیلد ename را با علامت " * " پر کرده تا تعداد کل کاراکترها به 10 برسد.

select lpad(ename,'*',10) from emp;‎

        Ename

        ‎----------‎

        ‎*****Smith‎

        ‎******John‎

        ‎**Mohammed‎

        ‎******Sami‎

تابع LTRIM

فضاهای خالی ای که در سمت چپ یک رشته قرار دارد را حذف می کند.
مثال زیر فضای خالی سمت چپ رشته ی “ Interface “ را حذف کرده، سپس آن را برمی گرداند.

The following query returns string “       Interface        “ left trimmed.‎

        select ltrim('       Interface       ') from dual;‎

        Ltrim

        ‎--------------‎

        Interface ‎

تابع RTRIM

فضاهای خالی ای که در سمت راست یک رشته قرار دارد را حذف می کند.
مثال زیر فضای خالی سمت راست “ Interface “ را حذف کرده، سپس آن را برمی گرداند.

select rtrim('       Interface       ') from dual;‎

        Rtrim

        ‎------------‎

        ‎   Interface ‎

تابع TRIM

تابع trim برای حذف فضای خالی قبل و بعد از یک رشته به کار می رود.
Query زیر با استفاده از تابع مزبور 0 های رشته ی مورد نظر از سمت چپ و راست آن حذف می کند.

Select trim(0 from '00003443500') from dual;‎

        Trim

        ‎----------‎

        ‎34435‎

تابع CONCAT

یک رشته را به رشته ی دیگر متصل می کند.

Select concat(concat(ename,' is a '),job) from emp;‎

        Concat

        ‎----------------‎

        Smith is a clerk

        John is a Manager

        Sami is a G.Manager

سایر توابع تک سطری (single-row functions)

تابع COALESCE

این تابع چندین عبارت از ورودی دریافت می کند و اولین عبارتی را که NULL نباشد را بر می گرداند .

این مثال salary + commission را برمی گرداند. چنانچه commission، null بود، تنها salary را بازمی گرداند و اگر salary هم null بود، 1000 را برمی گرداند.

select empno,ename,salary,comm,coalesce(salary+comm,salary,1000) “Net Sal” from ‎emp;‎

تابع DECODE

دستور نگارش

DECODE(expr, searchvalue1, result1,searchvalue2,result2,..., defaultvalue)‎

این تابع یک عبارت را با مقادیری که جستجو (search) برمی گرداند به صورت یک به یک مقایسه می کند. در صورتی که عبارت مربوط با هیچ یک از مقادیر بازگشتی search همخوانی و تطابق نداشته باشد، مقدار پیش فرض را بر می گرداند. اگر مقدار پیش فرض حذف گردیده باشد، null را برمی گرداند.

این مثال اسم department ها را بر اساس پارامتر deptno برمی گرداند. اگر deptno با هیچ یک از مقادیر بازگشتی search همخوانی نداشت، مقدار “Unknown Department” را بازیابی می نماید.

select decode(deptno,10,'Sales',20,'Accounts,30,'Production,‎

        ‎          40,'R&D','Unknown Dept') As DeptName from emp;‎

        DEPTNAME

        ‎----------‎

        Sales

        Accounts

        Unknown Dept.‎

        Accounts

        Production

        Sales

        R&D

        Unknown Dept.‎

تابع GREATEST

دستور نگارش

‎    GREATEST(expr1, expr2, expr3,expr4...)‎

بزرگترین عبارت را از یک فهرست عبارت (expr list) انتخاب کرده و واکشی می کند.

select greatest(10,20,50,20,30) from dual;‎

        GREATEST

        ‎--------‎

        ‎50‎

        select greatest('SAMI','SCOTT','RAVI','SMITH','TANYA') from dual;‎

        GREATEST

        ‎--------‎

        TANYA

تابع LEAST

ساختار نگارشی

LEAST(expr1, expr2, expr3,expr4...)‎

کوچکترین عبارت را از میان عبارت های موجود در یک لیست بازگردانی می کند.

select least(10,20,50,20,30) from dual;‎

        LEAST

        ‎--------‎

        ‎10‎

        select least('SAMI','SCOTT','RAVI','SMITH','TANYA') from dual;‎

        LEAST

        ‎--------‎

        RAVI

تابع NVL

ساختار نگارشی

NVL2(expr1,expr2)‎

از این تابع اغلب برای بررسی مقادیر null استفاده می شود. در صورتی که عبارت اول (exp1) null باشد، عبارت دوم (exp2) را بازمی گرداند و در غیر این صورت عبارت اول را بارگردانی می نماید.

Select ename,nvl(comm,'Not Applicable') “Comm” from dual;‎

Select ename,nvl(comm,'Not Applicable') “Comm” from dual;‎

تابع NVL2

نحوه ی نگارش تابع

NVL2(expr1,expr2,expr3)‎

این تابع در صورتی که عبارت اول (expr1) خالی (null) باشد، عبارت دوم را بازیابی می کند و در غیر این صورت عبارت سوم را بازگردانی می نماید.

select salary,comm,nvl2(comm,salary+comm,salary) “Income” from emp;‎

تابع NULLIF

ساختار نگارشی

‎  NULLIF(expr1, expr2)‎

تابع Nullif عبارت اول را با عبارت دوم مقایسه می کند. چنانچه دو عبارت با هم برابر بودند، مقدار null را بازگردانی می کند و در غیر این صورت عبارت اول را بازمی گرداند.

Query زیر شغل سابق آن دسته از کارکنانی که کار خود را در شرکت تغییر داده اند نشان می دهد. این کار را از طریق مقایسه ی شغل جاری کارکنان با شغل سابق آن ها در جدول oldemp انجام می دهد.

تابع UID

این تابع session ID جاری (شناسه ی session) کاربری که وارد شده (log on کرده) را برمی گرداند.

Example

        select uid from dual;‎

        UID

        ‎----‎

        ‎20‎

تابع USER

username کاربری که وارد شده را برمی گرداند.

select user from dual;‎

        USER

        ‎---------‎

        SCOTT

تابع SYS_CONTEXT

این تابع مقدار پارامتری که با فضای نام context مرتبط است را برمی گرداند. می توان این تابع را در هر دو دستورات SQL و PL/SQL بکار برد.
مثال زیر username کاربر جاری را برمی گرداند.

Select sys_context('USERENV','SESSION_USER') “Username” from dual;‎

        USERNAME

        ‎---------‎

        SCOTT

مشابه پارامتر SESSION_USER فضای نام USERENV، پارامترهای مهم و پرکاربرد دیگری نیز وجود دارند که به ترتیب زیر می باشند.

• ISDBA : بررسی می کند آیا کاربر دارای امتیازات و مجوزهای لازم مدیر پایگاه داده (DBA) هست یا خیر.
• HOST: اسم رایانه ی میزبان (host machine) که سرویس گیرنده (client) به آن متصل هست را برمی گرداند.
• INSTANCE : شماره ی شناسایی (id number) سطر جاری را بازیابی می نماید.
• IP_ADDRESS: آدرس IP (نشانی پروتکل اینترنت) رایانه ای که سرویس گیرنده به آن متصل می باشد را برمی گرداند.
• DB_NAME : اسم پایگاه داده را همانگونه که در initialization parameter (پارامتری که مقداردهی اولیه می کند) تعریف شده، برمی گرداند.

تابع VSIZE

دستور نگارش

VSIZE(expr)‎

این تابع حجم یک عبارت را بر حسب بایت محاسبه کرده و برمی گرداند.

select ename,vsize(ename) as Bytes from emp;‎

توابع Aggregate

این دست از توابع تنها یک مقدار را براساس داده های یک ستون برمی گردانند. به عبارتی دیگر توابع Aggregate یک مقدار را بجای اینکه بر اساس تنها یک مقدار به ازای هر سطر بازگرداند، آن مقدار را بر مبنای گروه یا مجموعه ای از سطرها بازمی گرداند. از این توابع می توان در لیست های select و همچنین در عبارت های ORDER BY و HAVING استفاده کرد. با این وجود، این دست از توابع اغلب با عبارت های GROUP BY و در دستور SELECT بکار می روند که ORACLE با استفاده از آن ها سطرهای جدول مورد پرس و جو (Queried) یا نمایه (view) را گروه بندی می کند.
پرکاربردترین این توابع عبارتند از

تابع AVG

ساختار نگارشی

AVG( ALL /DISTINCT   expr)‎

میانگین مقادیر یک ستون (یا عبارت) را بر می گرداند.
مثال زیر با استفاده از تابع نام برده، میانگین درآمد تمامی کارکنان شرکت را برمی گرداند.

select avg(sal) “Average Salary” from emp;‎

        Average Salary

        ‎------------------------‎

        ‎2400.40‎

تابع SUM

دستور نگارش

SUM(ALL/DISTINCT   expr)‎

مجموع داده های ستون را بر می گرداند (حاصل جمع مقادیر یک عبارت را بازگردانی می نماید).
مثال زیر حاصل جمع یا مجموع درآمد تمامی کارکنان شرکت را محاسبه کرده و برمی گرداند.

select sum(sal) “Total Salary” from emp;‎

        Total Salary

        ‎------------------------‎

        ‎26500‎

تابع MAX

دستور نگارشی

MAX(ALL/DISTINCT  expr)‎

بزرگترین مقدار ستون (عبارت) را بر می گرداند.

select max(sal) “Max Salary” from emp;‎

        Maximum Salary

        ‎------------------------‎

        ‎4500‎

تابع MIN

ساختار نگارشی

‎   MIN(ALL/DISTINCT     expr)‎

کمترین (کوچکترین) مقدار عبارت انتخابی را بر می گرداند.

این query پایین ترین درآمد بین کارکنان شرکت را برمی گرداند.

select min(sal) “Min Salary” from emp;‎

        Minimum Salary

        ‎------------------------‎

        ‎1200‎

تابع COUNT

دستور نگارشی

 COUNT(*)      OR      COUNT(ALL/DISTINCT     expr)‎

تعداد سطرها (ستون) را در یک query بازگردانی می نماید. اگر یک عبارت مشخص کنید، count مقادیر null را نادیده می گیرد. اگر از علامت " * " در query استفاده کنید، این تابع تمامی سطرها از جمله سطرهای تکراری و خالی (null) را برمی گرداند. خود تابع count هیچگاه null برنمی گرداند.
Query زیر تعداد کارکنان را بازمی گرداند.

Select count(*) from emp;‎

        COUNT

        ‎------‎

        ‎14‎

مثال زیر تعداد کارکنانی که درآمد آن ها null نمی باشد را برمی گرداند.

Select count(sal) from emp;‎

        COUNT

        ‎------‎

        ‎12‎

تابع STDDEV

نحوه ی نگارش

STDDEV(ALL/DISTINCT   expr)‎

این تابع انحراف از معیار عبارت را برمی گرداند.
مثال زیر انحراف از معیار درآمد کارکنان را بازیابی می نماید.

select stddev(sal) from emp;‎

        Stddev

        ‎-------   ‎

        ‎1430‎

تابع VARIANCE

ساختار نگارشی

VARIANCE(ALL/DISTINCT          expr)‎

واریانس عبارت را برمی گرداند.
مثال زیر واریانس درآمد کارکنان را بازمی گرداند.

select variance(sal) from emp;‎

        Variance

        ‎-------   ‎

        ‎1430‎