Sql: count function

Пример — использование GROUP BY с функцией COUNT

В некоторых случаях вам потребуется использовать оператор GROUP BY с функцией COUNT. Это происходит, когда в операторе SELECT указаны столбцы, которые не являются частью функции COUNT. Давайте рассмотрим это дальше. Опять же, используя таблицу employees, заполненную следующими данными.


employee_number first_name last_name salary dept_id
1001 Justin Bieber 62000 500
1002 Selena Gomez 57500 500
1003 Mila Kunis 71000 501
1004 Tom Cruise 42000 501

Введите следующий SQL оператор.

PgSQL

SELECT dept_id, COUNT(*) AS total FROM employees WHERE salary > 50000 GROUP BY dept_id;

1 2 3 4 5

SELECTdept_id,

COUNT(*)AStotal

FROMemployees

WHEREsalary>50000

GROUP BYdept_id;

Будет выбрано 2 записи. Вот результаты, которые вы должны получить.

dept_id total
500 2
501 1

В этом примере функция COUNT будет возвращать количество сотрудников, которые зарабатывают более 50000 $ для каждого dept_id. Поскольку столбец dept_id не включен в функцию COUNT, он должен быть указан в операторе GROUP BY.

Пример совпадения единичного символа

Рассмотрим простейший пример. Давайте посчитаем, сколько раз символ ‘a’ появляется в строке. Например:

Oracle PL/SQL

SELECT REGEXP_COUNT (‘Aller Anfang ist schwer’, ‘a’) FROM dual;

—Результат: 1

1 2 3 4

SELECTREGEXP_COUNT(‘Aller Anfang ist schwer’,’a’)

FROMdual;   —Результат: 1

Этот пример вернет 1, потому что он подсчитывает количество вхождений ‘a’ в строке. Так как мы не указали значение match_parameter, то функция REGEXP_COUNT выполнит поиск с учетом регистра, что означает, что символ ‘A’ не будет включен в счет.

Если бы мы хотели включить в наш результат как ‘a’, так и ‘A’, и выполнить поиск без учета регистра, то изменим наш запрос следующим образом:

Oracle PL/SQL

SELECT REGEXP_COUNT (‘Aller Anfang ist schwer’, ‘a’, 1, ‘i’) FROM dual;

—Результат: 3

1 2 3 4

SELECTREGEXP_COUNT(‘Aller Anfang ist schwer’,’a’,1,’i’)

FROMdual;   —Результат: 3

Теперь, поскольку мы предоставили start_position = 1 и match_parameter = ‘i’, то запрос в качестве результата вернет 3. На этот раз значения ‘a’ и ‘A’ будут включены в счет.

Если бы мы хотели подсчитать количество ‘a’ в столбце, мы могли бы попробовать что-то вроде этого:

Oracle PL/SQL

SELECT REGEXP_COUNT (last_name, ‘a’, 1, ‘i’) AS total FROM contacts;

1 2

SELECTREGEXP_COUNT(last_name,’a’,1,’i’)AStotal

FROMcontacts;

Этот запрос будет подсчитывать количество значений ‘a’ или ‘A’ в поле last_name из таблицы contacts.

Синтаксис

Синтаксис функции Oracle/PLSQL REGEXP_COUNT:

REGEXP_COUNT( string, pattern ] )

Параметры или аргументы

string Строка для поиска. Строкой могут быть CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB или NCLOB.pattern Шаблон. Регулярное выражение для сопоставления. Это может быть комбинацией следующих значений:

Значение Описание
^ Соответствует началу строки. При использовании match_parameter с m, соответствует началу строки в любом месте в пределах выражения.
$ Соответствует концу строки. При использовании match_parameter с m, соответствует концу строки в любом месте в пределах выражения.
* Соответствует нолю или более вхождений.
+ Соответствует одному или более вхождений.
? Соответствует нолю или одному вхождению.
. Соответствует любому символу, кроме NULL.
| Используется как «OR», чтобы указать более одной альтернативы.
Используется для указания списка совпадений, где вы пытаетесь соответствовать любому из символов в списке.
Используется для указания списка nonmatching, где вы пытаетесь соответствовать любому символу, за исключением тех кто в списке.
( ) Используется для групповых выражений в качестве подвыражений.
{m} Соответствует m раз.
{m,} Соответствие как минимум m раз.
{m,n} Соответствие как минимум m раз, но не более n раз.
\n n представляет собой число от 1 до 9. Соответствует n-му подвыражению находящемуся в ( ) перед \n.
Соответствует одному сопоставлению элемента, который может быть более одного символа.
Соответствует классу символов.
Соответствует классу эквивалентности
\d Соответствует цифровому символу.
\D Соответствует не цифровому символу.
\w Соответствует текстовому символу.
\W Соответствует не текстовому символу.
\s Соответствует символу пробел.
\S Соответствует не символу пробел.
\A Соответствует началу строки или соответствует концу строки перед символом новой строки.
\Z Соответствует концу строки.
*? Соответствует предыдущему шаблону ноль или более вхождений.
+? Соответствует предыдущему шаблону один или более вхождений.
?? Соответствует предыдущему шаблону ноль или одному вхождению.
{n}? Соответствует предыдущему шаблону n раз.
{n,}? Соответствует предыдущему шаблону, по меньшей мере n раз.
{n,m}? Соответствует предыдущему шаблону, по меньшей мере n раз, но не более m раз.

start_position Необязательный. Это позиция в строке, откуда начнется поиск. Если этот параметр опущен, по умолчанию он равен 1, который является первой позицией в строке.

match_parameter Необязательный. Он позволяет изменять поведение соответствия для функции REGEXP_COUNT. Это может быть комбинацией следующих значений:

Значение Описание
‘c’ Выполняет чувствительное к регистру согласование.
‘i’ Выполняет не чувствительное к регистру согласование.
‘n’ Позволяет период символа (.) для соответствия символа новой строки. По умолчанию, период метасимволы.
‘m’ Выражение допускает, что есть несколько строк, где ^ это начало строки, а $ это конец строки, независимо от позиции этих символов в выражении. По умолчанию предполагается, что выражение в одной строке.
‘x’ Игнорируются символы пробелов. По умолчанию, символы пробелов совпадают, как и любой другой символ.

Функция REGEXP_COUNT возвращает числовое значение.

Example — COUNT Function only includes NOT NULL Values

Not everyone realizes this, but the COUNT function will only count the records where the expression is NOT NULL in . When the expression is a NULL value, it is not included in the COUNT calculations. Let’s explore this further.

In this example, we have a table called customers with the following data:

customer_id last_name first_name favorite_website
4000 Jackson Joe techonthenet.com
5000 Smith Jane digminecraft.com
6000 Ferguson Samantha bigactivities.com
7000 Reynolds Allen checkyourmath.com
8000 Anderson Paige NULL
9000 Johnson Derek techonthenet.com

Enter the following SELECT statement that uses the COUNT function:

Try It

SELECT COUNT(customer_id)
FROM customers;

There will be 1 record selected. These are the results that you should see:

COUNT(customer_id)
6

In this example, the query will return 6 since there are 6 records in the customers table and all customer_id values are NOT NULL (ie: customer_id is the primary key for the table).

But what happens when we encounter a NULL value with the COUNT function? Let’s enter this next SELECT statement that counts the favorite_website column which can contain NULL values:

Try It

SELECT COUNT(favorite_website)
FROM customers;

There will be 1 record selected. These are the results that you should see:

COUNT(favorite_website)
5

This second example will return 5. Because one of the favorite_website values is NULL, it would be excluded from the COUNT function calculation. As a result, the query will return 5 instead of 6.

Построение динамического запроса с помощью динамического SQL

Для обработки динамического многострочного запроса вы используете три оператора: OPEN-FOR, FETCH и CLOSE. Сначала вы открываете переменную курсора для многострочного запроса. Затем вы выбираете строки из набора результатов по одной за раз. Когда все строки обработаны, вы закрываете (CLOSE) курсорную переменную.

В следующем примере показано, как вы можете извлечь строки из результирующего набора динамического многострочного запроса в запись:

Oracle PL/SQL

DECLARE TYPE EmpCurTyp IS REF CURSOR; emp_cv EmpCurTyp; emp_rec emp%ROWTYPE; sql_stmt VARCHAR2(200); my_job VARCHAR2(15) := ‘CLERK’; BEGIN sql_stmt := ‘SELECT * FROM emp WHERE job = :j’; OPEN emp_cv FOR sql_stmt USING my_job; LOOP FETCH emp_cv INTO emp_rec; EXIT WHEN emp_cv%NOTFOUND; — запись процесса END LOOP; CLOSE emp_cv; END;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

DECLARE

TYPEEmpCurTypISREFCURSOR;

emp_cvEmpCurTyp;

emp_recemp%ROWTYPE;

sql_stmtVARCHAR2(200);

my_jobVARCHAR2(15):=’CLERK’; BEGIN

sql_stmt:=’SELECT * FROM emp WHERE job = :j’;

OPENemp_cvFORsql_stmtUSINGmy_job;

LOOP

FETCHemp_cvINTOemp_rec;

EXITWHENemp_cv%NOTFOUND;

— запись процесса

ENDLOOP;

CLOSEemp_cv;

END;

Примеры динамического SQL для типов объектов и коллекций

Следующий пример иллюстрирует использование объектов и коллекций. Предположим, вы определили тип объекта Person и VARRAY тип Hobbies следующим образом:

Oracle PL/SQL

CREATE TYPE Person AS OBJECT (name VARCHAR2(25), age NUMBER); CREATE TYPE Hobbies IS VARRAY(10) OF VARCHAR2(25);

1 2

CREATETYPEPersonASOBJECT(nameVARCHAR2(25),ageNUMBER);

CREATETYPEHobbiesISVARRAY(10)OFVARCHAR2(25);

Используя динамический SQL, вы можете создать пакет, который использует эти типы:

Oracle PL/SQL

—создать спецификацию пакета CREATE OR REPLACE PACKAGE teams AS PROCEDURE create_table (tab_name VARCHAR2); PROCEDURE insert_row (tab_name VARCHAR2, p Person, h Hobbies); PROCEDURE print_table (tab_name VARCHAR2); END;

—создать тело пакета CREATE OR REPLACE PACKAGE BODY teams AS PROCEDURE create_table (tab_name VARCHAR2) IS BEGIN EXECUTE IMMEDIATE ‘CREATE TABLE ‘ || tab_name || ‘ (pers Person, hobbs Hobbies)’; END;

PROCEDURE insert_row ( tab_name VARCHAR2, p Person, h Hobbies) IS BEGIN EXECUTE IMMEDIATE ‘INSERT INTO ‘ || tab_name || ‘ VALUES (:1, :2)’ USING p, h; END;

PROCEDURE print_table (tab_name VARCHAR2) IS TYPE RefCurTyp IS REF CURSOR; cv RefCurTyp; p Person; h Hobbies; BEGIN OPEN cv FOR ‘SELECT pers, hobbs FROM ‘ || tab_name; LOOP FETCH cv INTO p, h; EXIT WHEN cv%NOTFOUND; — print attributes of ‘p’ and elements of ‘h’ END LOOP; CLOSE cv; END; END;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

—создать спецификацию пакета

CREATEORREPLACEPACKAGEteamsAS

PROCEDUREcreate_table(tab_nameVARCHAR2);

PROCEDUREinsert_row(tab_nameVARCHAR2,pPerson,hHobbies);

PROCEDUREprint_table(tab_nameVARCHAR2);

END;   —создать тело пакета

CREATEORREPLACEPACKAGEBODYteamsAS

PROCEDUREcreate_table(tab_nameVARCHAR2)IS

BEGIN

EXECUTEIMMEDIATE’CREATE TABLE ‘||tab_name||

‘ (pers Person, hobbs Hobbies)’;

END;  


PROCEDUREinsert_row(

tab_nameVARCHAR2,

pPerson,

hHobbies)IS

BEGIN

EXECUTEIMMEDIATE’INSERT INTO ‘||tab_name||

‘ VALUES (:1, :2)’USINGp,h;

END;  

PROCEDUREprint_table(tab_nameVARCHAR2)IS

TYPERefCurTypISREFCURSOR;

cvRefCurTyp;

pPerson;

hHobbies;

BEGIN

OPENcvFOR’SELECT pers, hobbs FROM ‘||tab_name;

LOOP

FETCHcvINTOp,h;

EXITWHENcv%NOTFOUND;

— print attributes of ‘p’ and elements of ‘h’

ENDLOOP;

CLOSEcv;

END;

END;

Из анонимного блока вы можете вызвать процедуры из пакета TEAMS:

Oracle PL/SQL

DECLARE team_name VARCHAR2(15); BEGIN team_name := ‘Notables’; teams.create_table(team_name); teams.insert_row(team_name, Person(‘John’, 31), Hobbies(‘skiing’, ‘coin collecting’, ‘tennis’)); teams.insert_row(team_name, Person(‘Mary’, 28), Hobbies(‘golf’, ‘quilting’, ‘rock climbing’)); teams.print_table(team_name); END;

1 2 3 4 5 6 7 8 9 10 11

DECLARE

team_nameVARCHAR2(15); BEGIN

team_name:=’Notables’;

teams.create_table(team_name);

teams.insert_row(team_name,Person(‘John’,31),

Hobbies(‘skiing’,’coin collecting’,’tennis’));

teams.insert_row(team_name,Person(‘Mary’,28),

Hobbies(‘golf’,’quilting’,’rock climbing’));

teams.print_table(team_name);

END;

SQL Учебник

SQL ГлавнаяSQL ВведениеSQL СинтаксисSQL SELECTSQL SELECT DISTINCTSQL WHERESQL AND, OR, NOTSQL ORDER BYSQL INSERT INTOSQL Значение NullSQL Инструкция UPDATESQL Инструкция DELETESQL SELECT TOPSQL MIN() и MAX()SQL COUNT(), AVG() и …SQL Оператор LIKESQL ПодстановочныйSQL Оператор INSQL Оператор BETWEENSQL ПсевдонимыSQL JOINSQL JOIN ВнутриSQL JOIN СлеваSQL JOIN СправаSQL JOIN ПолноеSQL JOIN СамSQL Оператор UNIONSQL GROUP BYSQL HAVINGSQL Оператор ExistsSQL Операторы Any, AllSQL SELECT INTOSQL INSERT INTO SELECTSQL Инструкция CASESQL Функции NULLSQL ХранимаяSQL Комментарии

SQL References

SQL Keywords ADD ADD CONSTRAINT ALTER ALTER COLUMN ALTER TABLE ALL AND ANY AS ASC BACKUP DATABASE BETWEEN CASE CHECK COLUMN CONSTRAINT CREATE CREATE DATABASE CREATE INDEX CREATE OR REPLACE VIEW CREATE TABLE CREATE PROCEDURE CREATE UNIQUE INDEX CREATE VIEW DATABASE DEFAULT DELETE DESC DISTINCT DROP DROP COLUMN DROP CONSTRAINT DROP DATABASE DROP DEFAULT DROP INDEX DROP TABLE DROP VIEW EXEC EXISTS FOREIGN KEY FROM FULL OUTER JOIN GROUP BY HAVING IN INDEX INNER JOIN INSERT INTO INSERT INTO SELECT IS NULL IS NOT NULL JOIN LEFT JOIN LIKE LIMIT NOT NOT NULL OR ORDER BY OUTER JOIN PRIMARY KEY PROCEDURE RIGHT JOIN ROWNUM SELECT SELECT DISTINCT SELECT INTO SELECT TOP SET TABLE TOP TRUNCATE TABLE UNION UNION ALL UNIQUE UPDATE VALUES VIEW WHERE

MySQL Functions String Functions ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE

Date Functions ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DATE_SUB DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK

Advanced Functions BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION

SQL Server Functions String Functions ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN

Date Functions CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR

Advanced Functions CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME

MS Access Functions String Functions Asc Chr Concat with & CurDir Format InStr InstrRev LCase Left Len LTrim Mid Replace Right RTrim Space Split Str StrComp StrConv StrReverse Trim UCase

Numeric Functions Abs Atn Avg Cos Count Exp Fix Format Int Max Min Randomize Rnd Round Sgn Sqr Sum Val

Date Functions Date DateAdd DateDiff DatePart DateSerial DateValue Day Format Hour Minute Month MonthName Now Second Time TimeSerial TimeValue Weekday WeekdayName Year

Other Functions CurrentUser Environ IsDate IsNull IsNumeric

SQL OperatorsSQL Data TypesSQL Quick Ref

Пример — функция COUNT включает только значения NOT NUL

Не все это понимают, но функция COUNT будет подсчитывать только те записи, в которых expressions НЕ равно NULL в COUNT(expressions). Когда expressions является значением NULL, оно не включается в вычисления COUNT. Давайте рассмотрим это дальше.

В этом примере у нас есть таблица customers со следующими данными:

customer_id first_name last_name favorite_website
4000 Justin Bieber google.com
5000 Selena Gomez bing.com
6000  Mila Kunis yahoo.com
7000 Tom Cruise oracle.com
8000 Johnny Depp NULL
9000 Russell Crowe google.com

Введите следующий запрос SELECT, которая использует функцию COUNT.

PgSQL

SELECT COUNT(customer_id) FROM customers;

1 2

SELECTCOUNT(customer_id)

FROMcustomers;

Будет выбрана 1 запись. Вот результаты, которые вы должны получить.

COUNT(customer_id)
6

В этом примере запрос вернет 6, поскольку в таблице customer есть 6 записей, а все значения customer_id НЕ равны NULL (т.е. customer_id является первичным ключом для таблицы). Но что происходит, когда мы сталкиваемся со значением NULL с помощью функции COUNT? Давайте введем следующий оператор SELECT, который посчитает столбец favourite_website, который может содержать значения NULL.

PgSQL

SELECT COUNT(favorite_website) FROM customers;

1 2

SELECTCOUNT(favorite_website)

FROMcustomers;

Будет выбрана 1 запись. Вот результаты, которые вы должны получить.

COUNT(favorite_website)
5

Во втором примере будет возвращено значение 5. Поскольку одно из значений favourite_website равно NULL, оно будет исключено из вычисления функции COUNT. В результате запрос вернет 5 вместо 6.Подсказка: Используйте первичный ключ в функции COUNT или COUNT(*), если вы хотите быть уверены, что записи не исключены в расчетах.

Примеры:

Некоторые примеры динамического SQL

Oracle PL/SQL

DECLARE sql_stmt VARCHAR2(200); plsql_block VARCHAR2(500); emp_id NUMBER(4) := 7566; salary NUMBER(7,2); dept_id NUMBER(2) := 50; dept_name VARCHAR2(14) := ‘PERSONNEL’; location VARCHAR2(13) := ‘DALLAS’; emp_rec emp%ROWTYPE; BEGIN —EXECUTE IMMEDIATE c SQL предложением EXECUTE IMMEDIATE ‘CREATE TABLE bonus (id NUMBER, amt NUMBER)’; —присвоим sql_stmt строковое SQL предложение с заполнителями :1, :2, :3 sql_stmt := ‘INSERT INTO dept VALUES (:1, :2, :3)’; —запустим EXECUTE IMMEDIATE с sql_stmt используя аргументы связывания dept_id, dept_name, location EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location; —присвоим sql_stmt SQL предложение с заполнителем :id sql_stmt := ‘SELECT * FROM emp WHERE empno = :id’; —запустим EXECUTE IMMEDIATE с sql_stmt используя аргумент связывания emp_id и сохраним результат в emp_rec EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id; —присвоим plsql_block запуск анонимного блока с подпрограммой raise_salary пакета emp_pkg с заполнителями :id, :amt plsql_block := ‘BEGIN emp_pkg.raise_salary(:id, :amt); END;’; —запустим EXECUTE IMMEDIATE с plsql_block используя аргументы связывания :id, :amt EXECUTE IMMEDIATE plsql_block USING 7788, 500; —присвоим sql_stmt SQL предложение с заполнителем :1, :2 sql_stmt := ‘UPDATE emp SET sal = 2000 WHERE empno = :1 RETURNING sal INTO :2’; —запустим EXECUTE IMMEDIATE с sql_stmt используя аргументы связывания emp_id, salary EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary; —EXECUTE IMMEDIATE c SQL предложение с заполнителем :num и аргументом связывания dept_id EXECUTE IMMEDIATE ‘DELETE FROM dept WHERE deptno = :num’ USING dept_id; —EXECUTE IMMEDIATE c SQL предложением EXECUTE IMMEDIATE ‘ALTER SESSION SET SQL_TRACE TRUE’; END;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

DECLARE

sql_stmtVARCHAR2(200);

plsql_blockVARCHAR2(500);

emp_idNUMBER(4):=7566;

salaryNUMBER(7,2);

dept_idNUMBER(2):=50;

dept_nameVARCHAR2(14):=’PERSONNEL’;

locationVARCHAR2(13):=’DALLAS’;

emp_recemp%ROWTYPE; BEGIN —EXECUTE IMMEDIATE c SQL предложением

EXECUTEIMMEDIATE’CREATE TABLE bonus (id NUMBER, amt NUMBER)’; —присвоим sql_stmt строковое SQL предложение с заполнителями :1, :2, :3

sql_stmt:=’INSERT INTO dept VALUES (:1, :2, :3)’; —запустим EXECUTE IMMEDIATE с sql_stmt используя аргументы связывания dept_id, dept_name, location

EXECUTEIMMEDIATEsql_stmtUSINGdept_id,dept_name,location; —присвоим sql_stmt SQL предложение с заполнителем :id

sql_stmt:=’SELECT * FROM emp WHERE empno = :id’; —запустим EXECUTE IMMEDIATE с sql_stmt используя аргумент связывания emp_id и сохраним результат в emp_rec

EXECUTEIMMEDIATEsql_stmtINTOemp_recUSINGemp_id; —присвоим plsql_block запуск анонимного блока с подпрограммой raise_salary пакета emp_pkg с заполнителями :id, :amt

plsql_block:=’BEGIN emp_pkg.raise_salary(:id, :amt); END;’; —запустим EXECUTE IMMEDIATE с plsql_block используя аргументы связывания :id, :amt  

EXECUTEIMMEDIATEplsql_blockUSING7788,500; —присвоим sql_stmt SQL предложение с заполнителем :1, :2

sql_stmt:=’UPDATE emp SET sal = 2000 WHERE empno = :1 RETURNING sal INTO :2′; —запустим EXECUTE IMMEDIATE с sql_stmt используя аргументы связывания emp_id, salary

EXECUTEIMMEDIATEsql_stmtUSINGemp_idRETURNINGINTOsalary; —EXECUTE IMMEDIATE c SQL предложение с заполнителем :num и аргументом связывания dept_id

EXECUTEIMMEDIATE’DELETE FROM dept WHERE deptno = :num’USINGdept_id; —EXECUTE IMMEDIATE c SQL предложением   

EXECUTEIMMEDIATE’ALTER SESSION SET SQL_TRACE TRUE’;

END;

Пример процедуры динамического SQL, которая принимает имя таблицы и предложение WHERE

В этом примере автономная процедура принимает имя таблицы базы данных и необязательное условие предложения WHERE. Если вы пропустите условие, процедура удалит все строки из таблицы. В противном случае процедура удаляет только те строки, которые соответствуют условию.

Oracle PL/SQL

CREATE OR REPLACE PROCEDURE delete_rows ( table_name IN VARCHAR2, condition IN VARCHAR2 DEFAULT NULL) AS where_clause VARCHAR2(100) := ‘ WHERE ‘ || condition; BEGIN IF condition IS NULL THEN where_clause := NULL; END IF; EXECUTE IMMEDIATE ‘DELETE FROM ‘ || table_name || where_clause; END;

1 2 3 4 5 6 7 8

CREATEORREPLACEPROCEDUREdelete_rows(

table_nameINVARCHAR2,

conditionINVARCHAR2DEFAULTNULL)AS

where_clauseVARCHAR2(100):=’ WHERE ‘||condition; BEGIN

IFconditionISNULLTHENwhere_clause:=NULL;ENDIF;

EXECUTEIMMEDIATE’DELETE FROM ‘||table_name||where_clause;

END;

SQL References

SQL Keywords ADD ADD CONSTRAINT ALTER ALTER COLUMN ALTER TABLE ALL AND ANY AS ASC BACKUP DATABASE BETWEEN CASE CHECK COLUMN CONSTRAINT CREATE CREATE DATABASE CREATE INDEX CREATE OR REPLACE VIEW CREATE TABLE CREATE PROCEDURE CREATE UNIQUE INDEX CREATE VIEW DATABASE DEFAULT DELETE DESC DISTINCT DROP DROP COLUMN DROP CONSTRAINT DROP DATABASE DROP DEFAULT DROP INDEX DROP TABLE DROP VIEW EXEC EXISTS FOREIGN KEY FROM FULL OUTER JOIN GROUP BY HAVING IN INDEX INNER JOIN INSERT INTO INSERT INTO SELECT IS NULL IS NOT NULL JOIN LEFT JOIN LIKE LIMIT NOT NOT NULL OR ORDER BY OUTER JOIN PRIMARY KEY PROCEDURE RIGHT JOIN ROWNUM SELECT SELECT DISTINCT SELECT INTO SELECT TOP SET TABLE TOP TRUNCATE TABLE UNION UNION ALL UNIQUE UPDATE VALUES VIEW WHERE

MySQL Functions String Functions ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE

Date Functions ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DATE_SUB DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK

Advanced Functions BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION

SQL Server Functions String Functions ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN

Date Functions CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR

Advanced Functions CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME

MS Access Functions String Functions Asc Chr Concat with & CurDir Format InStr InstrRev LCase Left Len LTrim Mid Replace Right RTrim Space Split Str StrComp StrConv StrReverse Trim UCase

Numeric Functions Abs Atn Avg Cos Count Exp Fix Format Int Max Min Randomize Rnd Round Sgn Sqr Sum Val

Date Functions Date DateAdd DateDiff DatePart DateSerial DateValue Day Format Hour Minute Month MonthName Now Second Time TimeSerial TimeValue Weekday WeekdayName Year

Other Functions CurrentUser Environ IsDate IsNull IsNumeric

SQL OperatorsSQL Data TypesSQL Quick Ref

Application of COUNT() function

In the subsequent pages, we have discussed how to apply COUNT() with various SQL clauses. For those applications, we have used Oracle 10g Express Edition.

COUNT with DISTINCT page discusses how to apply COUNT function with DISTINCT and also discusses how to apply COUNT function with ALL clause. Unlike using *, when ALL is used, NULL values are not selected.

COUNT HAVING page discusses how to apply COUNT function with HAVING clause and HAVING and GROUP BY .

COUNT with GROUP BY page discusses how to apply COUNT function with GROUP BY in ascending order and in descending order.

Note: Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition

Here is a slide presentation of all aggregate functions.


С этим читают