Create pattern dynamically and use it in like statement : Like « Select Query « Oracle PL / SQL






Create pattern dynamically and use it in like statement

    

SQL>
SQL>
SQL> CREATE TABLE emp (
  2    id         NUMBER PRIMARY KEY,
  3    fname VARCHAR2(50),
  4    lname  VARCHAR2(50)
  5  );

Table created.

SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (1, 'A', 'B');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (2, 'C', 'D');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (3, 'E', 'F');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (4, 'G', 'H');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (5, 'G', 'Z');

1 row created.

SQL>
SQL> SET SERVEROUTPUT ON ESCAPE OFF
SQL>
SQL> CREATE OR REPLACE PROCEDURE author_sel(i_lname IN emp.lname%TYPE,cv_author IN OUT SYS_REFCURSOR) IS
  2     lnameValue emp.lname%TYPE;
  3  BEGIN
  4
  5     lnameValue := '%'||UPPER(i_lname)||'%';
  6
  7     OPEN cv_author FOR SELECT id, fname, lname FROM emp WHERE UPPER(lname) LIKE lnameValue;
  8
  9  EXCEPTION
 10     WHEN OTHERS
 11     THEN
 12        DBMS_OUTPUT.PUT_LINE(sqlerrm);
 13  END;
 14  /

Procedure created.

SQL>
SQL> COL fname FORMAT A20
SQL> COL lname FORMAT A20
SQL>
SQL> VARIABLE x REFCURSOR
SQL> EXEC author_sel('rin', :x)

PL/SQL procedure successfully completed.

SQL>
SQL> print x

no rows selected

SQL>
SQL>
SQL> drop table emp;

Table dropped.

   
    
    
    
  








Related examples in the same category

1.A function using the LIKE operator to return a phone number's area code
2.Like with '__' (Any two characters)
3.Like with '_' and '%'
4.Use two '%' in Like statement
5.Using a NOT operator with like
6.Convert varchar value to upper case and then use like operator
7.Is Like case sensitive
8.Use % in word ending
9.Use two % signs in one like
10.Use two % signs in like
11.Like '%' function with varchar2 type
12.Like with % on both sides
13.Second letter is A
14.Pattern Matching LIKE '%great%'
15.Using Pattern Matching LIKE '____ %'
16.comments like '%0\%%' escape '\'
17.Escape \
18.Description has 'SQL' substring