Oracle Database

You can convert character types to numeric types in two ways:

► Explicitly by using TO_NUMBER() function.

► Implicitly by putting character data in a numeric operation.

The sample script below shows you how to convert character types to numeric types:

PROCEDURE proc_convert_1 AS

start_time CHAR(5);

finish_time CHAR(5);

elapsed_time NUMBER(5);

BEGIN

start_time := '12052';

finish_time := '15314';

elapsed_time := TO_NUMBER(finish_time)

- TO_NUMBER(start_time);

elapsed_time := finish_time - start_time; -- same as above

END;

If you have a block of codes to be executed repeatedly based a condition, you can use the "WHILE ... LOOP" statement. Here is a sample script on WHILE statements:

DECLARE

total NUMBER;

BEGIN

total := 0;

WHILE total < 10 LOOP

total := total+1;

END LOOP;

DBMS_OUTPUT.PUT_LINE('Total counts: ' || TO_CHAR(total));

END;

This script should print this:

Total counts: 10

If you have a block of codes to be executed repeatedly over a range of values, you can use the "FOR ... LOOP" statement. Here is a sample script on FOR statements:

DECLARE

total NUMBER := 0;

BEGIN

FOR i IN 1..10 LOOP

total := total + i;

END LOOP;

DBMS_OUTPUT.PUT_LINE('Total: ' || TO_CHAR(total));

END;

Note that temporary variable "i" used in the FOR loop needs no declaration. This script should print this:

Total: 55

If you have multiple blocks of codes to be executed based on different conditions, you can use the "IF ... ELSIF" statement. Here is a sample script on IF statements:

DECLARE

day VARCHAR2;

BEGIN

day := 'SUNDAY';

IF day = 'THURSDAY' THEN

DBMS_OUTPUT.PUT_LINE('Checking log files.');

ELSIF day = 'TUESDAY' THEN

DBMS_OUTPUT.PUT_LINE('Helping developers.');

ELSIF day = 'FRIDAY' THEN

DBMS_OUTPUT.PUT_LINE('Rebuild indexes.');

ELSE

DBMS_OUTPUT.PUT_LINE('Reading some papers.');

END IF;

END;

This script should print this:

Reading some papers.

A procedure is a named program unit. It consists of three parts:

► Declaration Part - Defining the procedure name, calling parameters, local variables and local procedures. Declaration part is required.

► Execution Part - Defining execution logic with executable statements. Execution part is required.

► Exception Part - Defining error handling logics. Exception part is optional.

Here how a complete procedure should look like:

PROCEDURE name (parameter_1, parameter_2) AS

-- Declaration statements

BEGIN

-- Executable statements

EXCEPTION

-- Error handling statements

END;

Anonymous procedure is a procedure without any name. If you don't have any variables to declare, you can define an anonymous procedure by using the BEGIN keyword directly in SQL*Plus as shown in the following tutorial script:

SQL> set serveroutput on;

SQL> begin

2 dbms_output.put_line('Welcome to globalguideline!');

3 end;

4 /

Welcome to globalguideline!

PL/SQL procedure successfully completed.

"set serveroutput on;" allows dbms_output.put_line() to work.

"/" runs the anonymous block, which print the "Welcome to globalguideline!" message.

Anonymous procedure is a procedure without any name. If you have some variables to declare, you can define an anonymous procedure by using the DECLARE keyword in SQL*Plus as shown in the following tutorial script:

SQL> set serveroutput on;

SQL> declare

2 site char(80) := 'globalguideline';

3 begin

4 dbms_output.put_line('Welcome to ' || site);

5 end;

6 /

Welcome to globalguideline

PL/SQL procedure successfully completed.

SQL> /

Welcome to globalguideline

PL/SQL procedure successfully completed.

"/" runs the anonymous block, which print the "Welcome to globalguideline" message.

A stored procedure is a procedure with a specified name and stored into the current database. If you want to create a stored procedure, you can use the CREATE PROCEDURE statement. The example script below creates a stored procedure:

SQL> CREATE PROCEDURE Greeting AS

2 BEGIN

3 DBMS_OUTPUT.PUT_LINE('Welcome to globalguideline!');

4 END;

5 /

Procedure created.

If you want to execute a stored procedure, you can use the EXECUTE statement. The example script below shows how to executes a stored procedure:

SQL> set serveroutput on;

SQL> CREATE PROCEDURE Greeting AS

2 BEGIN

3 DBMS_OUTPUT.PUT_LINE('Welcome to globalguideline!');

4 END;

5 /

Procedure created.

SQL> EXECUTE Greeting;

Welcome to globalguideline!

If there is an existing stored procedure and you don't want it any more, you can remove it from the database by using the DROP PROCEDURE statement as shown in the following script example:

SQL> CREATE PROCEDURE Greeting AS

2 BEGIN

3 DBMS_OUTPUT.PUT_LINE('Welcome to globalguideline!');

4 END;

5 /

Procedure created.

SQL> DROP PROCEDURE Greeting;

Procedure dropped.

A stored function is a function with a specified name and stored into the current database. If you want to create a stored function, you can use the CREATE FUNCTION statement. The example script below creates a stored procedure:

SQL> CREATE OR REPLACE FUNCTION GET_SITE

2 RETURN VARCHAR2 AS

3 BEGIN

4 RETURN 'globalguidelinee.com';

5 END;

6 /

Function created.

A stored function can be called as part of expression in any PL/SQL statement. One simplest way to call a stored function is to a dummy SELECT statement as shown in the following tutorial script using SQL*Plus:

SQL> CREATE OR REPLACE FUNCTION GET_SITE

2 RETURN VARCHAR2 AS

3 BEGIN

4 RETURN 'globalguidelinee.com';

5 END;

6 /

Function created.

SQL> SELECT get_site() FROM dual;

GET_SITE()

---------------------------------

globalguidelinee.com

DUAL is not a real table or view. It is commonly used to with SELECT statement to evaluate expressions.

If there is an existing stored function and you don't want it any more, you can remove it from the database by using the DROP FUNCTION statement as shown in the following script example:

SQL> CREATE OR REPLACE FUNCTION GET_SITE

2 RETURN VARCHAR2 AS

3 BEGIN

4 RETURN 'globalguidelinee.com';

5 END;

6 /

Function created.

SQL> DROP FUNCTION GET_SITE;

Function dropped.

You can define a function that takes parameters, provide values to those parameters when calling the function. Here is a good example of a function with a parameter:

SQL> CREATE OR REPLACE FUNCTION GET_DOUBLE(X NUMBER)

2 RETURN NUMBER AS

3 BEGIN

4 RETURN X * 2;

5 END;

6 /

Function created.

SQL> SELECT GET_DOUBLE(36) FROM DUAL;

GET_DOUBLE(36)

--------------

72