Örnek
Şöyle yaparız.
Şöyle yaparız.
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';
ALTER SESSION SET NLS_DATE_LANGUAGE = 'American';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';
ALTER SESSION SET NLS_DATE_LANGUAGE = 'American';
SET SERVEROUTPUT ON ;
DECLARE
STMT1 VARCHAR2(2000);
RESULT1 VARCHAR2(2000);
BEGIN
STMT1:= 'SELECT ...FROM Foo';
EXECUTE IMMEDIATE STMT1 INTO RESULT1;
DBMS_OUTPUT.PUT_LINE(STMT1);
DBMS_OUTPUT.PUT_LINE(RESULT1);
END;
SELECT
status,
CASE
WHEN STATUS IN('a1','a2','a3')
THEN 'Active'
WHEN STATUS = 'i'
THEN 'Inactive'
WHEN STATUS = 't'
THEN 'Terminated'
END AS STATUSTEXT
FROM
STATUS
Örnek - WHEN + ELSECREATE AND COMPILE JAVA SOURCE NAMED SORTSTRING AS
public class SortString {
public static String sort( final String value )
{
final char[] chars = value.toCharArray();
java.util.Arrays.sort( chars );
return new String( chars );
}
};
/
Sütun kullanmadan bu kodu çağırarak şöyle yaparız.SELECT CASE
WHEN SORTSTRING( 'ads' ) = SORTSTRING( 'das' )
THEN 'Equal'
ELSE 'Not Equal'
END
FROM DUAL;
Örnek - WHEN + ELSECREATE TABLE customer
(
id NUMBER,
first_name VARCHAR2(100),
last_name VARCHAR2(100),
birth_date DATE,
gender VARCHAR2(1)
);
CREATE TABLE customer_xml
(
created_date DATE,
row_num NUMBER,
xml_data VARCHAR2(4000)
);
INSERT INTO customer_xml (created_date, row_num, xml_data)
SELECT c.created_date, c.row_num,
CASE
WHEN c.row_num = 1 THEN
'<?xml version="1.0" encoding="UTF-8"?><root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">'
ELSE
''
END ||
'<id>' || TO_CHAR(id) || '</id>' ||
'<first_name>' || first_name || '</first_name>' ||
'<last_name>' || last_name || '</last_name>' ||
'<birth_date>' || TO_CHAR(birth_date, 'YYYY-MM-DD') || '</birth_date>' ||
'<gender>' || gender || '</gender>' ||
CASE
WHEN c.row_num = rc.row_count THEN
'</root>'
ELSE
''
END AS xml_data
FROM customer;