TECH GUIDE BLOG

In PL/SQL* Oracle, we can use conditional statement like other programming language, such as CASE and IF statement. How to do it in PL/SQL? Here’s the syntax for IF statement.

IF condition THEN
  statements;
[ELSIF condition THEN
  statements;]
[ELSE
  statements;]
END IF;

Just remember, I write ELSIF,  it’s on purpose and it’s not mistyping, in this case we write “else if” in PL/SQL as ELSIF. There is an example for IF statement usage in PL/SQL. This statement will ask your age and show output based on your age input.

SET SERVEROUTPUT ON
SET VERIFY OFF
ACCEPT age PROMPT 'Insert your age'
DECLARE
  age NUMBER := &age;
BEGIN
  IF age < 17 THEN
    DBMS_OUTPUT.PUT_LINE('You are teen');
  ELSIF age < 56 THEN
    DBMS_OUTPUT.PUT_LINE('You are adult');
  ELSE
    DBMS_OUTPUT.PUT_LINE('You are old');
END;
/

Just a little different than IF, CASE expression selects a result and returns it. The values returned by CASE is used to select one of several alternatives. The CASE syntax is shown below.

CASE selector
  WHEN axpression1 THEN result1
  WHEN expression2 THEN result2
  ...
  WHEN expressionN THEN resultN
  [ELSE resultN+1]
END;
/

For the example of CASE expression, we can use IF statement example above, just change it to CASE expression. We add text variable to store the return values from CASE expression since CASE return values.

SET SERVEROUTPUT ON
SET VERIFY OFF
ACCEPT age PROMPT 'Insert your age'
DECLARE
  age NUMBER := &age;
  text VARCHAR2(20);
BEGIN
  text:=
    CASE
      WHEN age < 17 THEN 'You are teen'
      WHEN age < 56 THEN 'You are adult'
      ELSE 'You are old'
    END;
  DBMS_OUTPUT.PUT_LINE(text);
END;
/

Example above didn’t use selector after CASE expression, you can use selector as alternatives way. I don’t really use CASE with selector anyway, but I will give you an example with selector below, I hope this can help.

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
  grade CHAR(1) := UPPER('&grade');
  text VARCHAR2(20);
BEGIN
  text :=
    CASE grade
      WHEN 'A' THEN 'Excellent'
      WHEN 'B' THEN 'Very Good'
      WHEN 'C' THEN 'Good'
      WHEN 'D' THEN 'Bad'
      WHEN 'E' THEN 'Poor'
      ELSE 'Not valid grade'
    END;
  DBMS_OUTPUT.PUT_LINE(text);
END;
/

I think it’s enough for Conditional in PL/SQL Oracle tutorial, maybe this can help you little, feel free to give comment to improve my blog.

Popularity: 7% [?]

After a long sleep, I’m back and I want to start with a new topic, database. Okay, the database I’m talking about is Oracle, with using PL/SQL commands. The first time of this topic, I will talk about making sequence in Oracle. What is sequence? We can imagine sequence as a table that keep, of course, sequence numbers, this will be useful when you need to make sequence of numbers automatically, so you don’t have to make a new column that save those numbers or insert it manually.

To make a sequence we can use some options:

1. START WITH n. When we first create the sequence, that sequence will be started with n.
2. INCREMENT BY n. Define the increment value of your sequence, if it isn’t declared, it will set to 1.
3. MINVALUE n. Define the minimum value of the sequence can generate, if it isn’t declared, the sequence is assumed have no minimal value (NOMINVALUE).
4. MAXVALUE n. Define the maximum value of the sequence can generate, if it isn’t declared, the sequence is assumed have no maximum value (NOMAXVALUE).
5. CYCLE. Allow sequence to back with MINVALUE if sequence has reached its MAXVALUE. If it isn’t declared, sequence will not back to MINVALUE (NOCYCLE).
6. CACHE. Allow object sequence to do caching from the values that will be generated to increase performance. If it isn’t declared, sequence will not use cache (NOCACHE).

How to declare it

CREATE SEQUENCE sequence_name
START WITH 2
INCREMENT BY 3
MAXVALUE 98
CYCLE;

To use a sequence, we have to know that sequence is similar to struct variable. It have two virtual variable, CURVAL and NEXTVAL.  When we read the value of NEXTVAL, the CURVAL field will be filled with NEXTVAL, and NEXTVAL value will increase automatically by 1 or your setting with INCREMENT BY n. This is a example how to do it

INSERT INTO table_name values(sequence_name.NEXTVAL);

To change the settings, you can use alter command

ALTER SEQUENCE sequence_name
INCREMENT BY 1
START WITH 1;

To delete a sequence you can use drop command

DROP SEQUENCE sequence_name;

Well, that’s it for now, hope this tutorial can help you. Feel free to comment here if you have any questions.

Popularity: 54% [?]

What can I get by validating XHTML in my blog? Well, some people say it can help Uncle Google to read and crawl your site, because Uncle is older and older and more hard to do reading. Before I use this code, my blog home page is always not valid due to a reason, after I insert a video from YouTube, some warnings and errors always occur. I search and find some tips to make YouTube Embed Video that you’ve insert is valid. Let’s take a look first with YouTube default Embed Code.

<object width="425" height="344">
<param name="movie" value="http://www.youtube.com/v/sJi6amKnNNE&hl=en&fs=1&"></param>
<param name="allowFullScreen" value="true"></param>
<param name="allowscriptaccess" value="always"></param>
<embed src="http://www.youtube.com/v/sJi6amKnNNE&hl=en&fs=1&" type="application/x-shockwave-flash" allowscriptaccess="always" allowfullscreen="true" width="425" height="344"></embed>
</object>

That code above is not valid by W3C XHTML validator. So you have to change that code into something different than above.

<object type="application/x-shockwave-flash" style="width:310px; height:260px;"
data="http://www.youtube.com/v/XFcLU7hOeQI&hl=en&fs=1">
<param name="movie" value="http://www.youtube.com/v/XFcLU7hOeQI&hl=en&fs=1" />
</object>

This format above now is valid by XHTML Validator, but as you can see now, every YouTube Embed Code are always have ‘&’ ampersand. This is another problem that make your Embed Video is still not valid by XHTML Validator. For this problem, you have to change every ‘&’ to ‘&’

<object type="application/x-shockwave-flash" style="width:310px; height:260px;"
data="http://www.youtube.com/v/XFcLU7hOeQI&amp;hl=en&amp;fs=1">
<param name="movie" value="http://www.youtube.com/v/XFcLU7hOeQI&amp;hl=en&amp;fs=1" />
</object>

That’s it. It’s working on my blog, may this tips can help you to validate your site when you insert an Embed Video from YouTube. You can comment if you want to ask or share about this article.

Popularity: 12% [?]