в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:08:48Z

1 версия

Новая страница

== LIKE==

<OL><LI>LIKE is PL/SQL"s pattern-matching operator.</LI><LI>LIKE is used to compare a character string against a pattern.</LI><LI>LIKE is useful for performing wildcard searches.</LI><LI>LIKE can only be used with character strings.</LI><LI>LIKE checks to see if the contents of string_variable match the pattern definition.</LI><LI>If the string matches the pattern, a result of true is returned;</LI><LI>otherwise, the expression evaluates to false.</LI></OL>
<p>The Syntax for LIKE</p>



<source lang="sql">
string_variable LIKE pattern</source>

== Use like in PL/SQL==



<source lang="sql">
SQL> SET ECHO ON
SQL>
SQL> CREATE OR REPLACE FUNCTION area_code (phone_number IN VARCHAR2)
2 RETURN VARCHAR2 AS
3 BEGIN
4 IF phone_number LIKE "___-___-____" THEN
5 RETURN SUBSTR(phone_number,1,3);
6 ELSE
7 RETURN "none";
8 END IF;
9 END;
10 /
Function created.
SQL>
SQL></source>

== Use the LIKE operator==



<source lang="sql">
SQL>
SQL> set feedback on escape ~
SQL>
SQL> CREATE TABLE authors (
2 id NUMBER PRIMARY KEY,
3 first_name VARCHAR2(50),
4 last_name VARCHAR2(50)
5 );
Table created.
SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
2 VALUES (1, "Marlene", "Theriault");
1 row created.
SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
2 VALUES (2, "Rachel", "Carmichael");
1 row created.
SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
2 VALUES (3, "James", "Viscusi");
1 row created.
SQL>
SQL> COMMIT;
Commit complete.
SQL>
SQL>
SQL> SET SERVEROUTPUT ON ESCAPE OFF
SQL>
SQL> CREATE OR REPLACE PROCEDURE author_sel (
2 i_last_name IN AUTHORS.LAST_NAME%TYPE,
3 cv_author IN OUT SYS_REFCURSOR)
4 IS
5 v_last_name AUTHORS.LAST_NAME%TYPE;
6 BEGIN
7
8 v_last_name := "%"||UPPER(i_last_name)||"%";
9
10 OPEN cv_author FOR
11 SELECT id, first_name, last_name
12 FROM authors
13 WHERE UPPER(last_name) LIKE v_last_name;
14
15 EXCEPTION
16 WHEN OTHERS
17 THEN
18 DBMS_OUTPUT.PUT_LINE(sqlerrm);
19 END;
20 /
Procedure created.
SQL>
SQL> COL first_name FORMAT A20
SQL> COL last_name FORMAT A20
SQL>
SQL> VARIABLE x REFCURSOR
SQL> EXEC author_sel("u", :x)
PL/SQL procedure successfully completed.
SQL>
SQL> print x

ID FIRST_NAME LAST_NAME
---------- -------------------- --------------------
1 Marlene Theriault
3 James Viscusi
2 rows selected.
SQL> drop table authors;
Table dropped.</source>

== Validate a zip code==



<source lang="sql">
SQL> SET ECHO ON
SQL>
SQL> CREATE OR REPLACE FUNCTION area_code (phone_number IN VARCHAR2)
2 RETURN VARCHAR2 AS
3 BEGIN
4 IF phone_number LIKE "___-___-____" THEN
5 RETURN SUBSTR(phone_number,1,3);
6 ELSE
7 RETURN "none";
8 END IF;
9 END;
10 /
Function created.
SQL>
SQL></source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/PL SQL Operators/Like - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия