, , ,

SQL WHERE Option

The SQL WHERE clause is used to select data conditionally, by adding it to already existing SQL SELECT query. We are going to use the Customers table from the previous chapter, to illustrate the use of the SQL WHERE command.
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator value
With the WHERE clause, the following operators can be used:
Operator     Description
=     Equal
<>     Not equal
>     Greater than
<     Less than
>=     Greater than or equal
<=     Less than or equal
LIKE     Search for a pattern
IN     If you know the exact value you want to return for at least one of the columns
BETWEEN     Between an inclusive range
“AND” Condition
The AND condition allows you to create an SQL statement based on 2 or more conditions being met. It can be used in any valid SQL statement – select, insert, update, or delete.
The syntax for the AND condition is:
    SELECT columns
    FROM tables
    WHERE column1 = ‘value1’
    and column2 = ‘value2’;
The AND condition requires that each condition be must be met for the record to be included in the result set. In this case, column1 has to equal ‘value1’ and column2 has to equal ‘value2’.
“OR” Condition
The OR condition allows you to create an SQL statement where records are returned when any one of the conditions are met. It can be used in any valid SQL statement – select, insert, update, or delete.
The syntax for the OR condition is:
    SELECT columns
    FROM tables
    WHERE column1 = ‘value1’
    or column2 = ‘value2’;The OR condition requires that any of the conditions be must be met for the record to be included in the result set. In this case, column1 has to equal ‘value1’ OR column2 has to equal ‘value2’.
Combining the “AND” and “OR” Conditions
The AND and OR conditions can be combined in a single SQL statement. It can be used in any valid SQL statement – select, insert, update, or delete. When combining these conditions, it is important to use brackets so that the database knows what order to evaluate each condition.
Example
The first example that we’ll take a look at an example that combines the AND and OR conditions.
    SELECT *
    FROM suppliers
    WHERE (city = ‘New York’ and name = ‘IBM’)
    or (city = ‘Newark’);
LIKE Operator
The LIKE operator is used to search for a specified pattern in a column.
SQL LIKE Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE patternThe LIKE condition can be used in any valid SQL statement – select, insert, update, or delete.
The patterns that you can choose from are:
    % allows you to match any string of any length (including zero length)
    _ allows you to match on a single character
Examples using % wildcard
The first example that we’ll take a look at involves using % in the where clause of a select statement. We are going to try to find all of the suppliers whose name begins with ‘Hew’.
SELECT *
FROM suppliers
WHERE supplier_name like ‘Hew%’;
You can also using the wildcard multiple times within the same string. For example,
SELECT *
FROM suppliers
WHERE supplier_name like ‘%bob%’;
Examples using _ wildcard
Next, let’s explain how the _ wildcard works. Remember that the _ is looking for only one character.
For example,
SELECT *
FROM suppliers
WHERE supplier_name like ‘Sm_th’;
This SQL statement would return all suppliers whose name is 5 characters long, where the first two characters is ‘Sm’ and the last two characters is ‘th’. For example, it could return suppliers whose name is ‘Smith’, ‘Smyth’, ‘Smath’, ‘Smeth’, etc.
IN Function
The IN operator allows you to specify multiple values in a WHERE clause.
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,…)
The following is an SQL statement that uses the IN function:
SELECT *
FROM suppliers
WHERE supplier_name in ( ‘IBM’, ‘Hewlett Packard’, ‘Microsoft’);
This would return all rows where the supplier_name is either IBM, Hewlett Packard, or Microsoft. Because the * is used in the select, all fields from the suppliers table would appear in the result set.
It is equivalent to the following statement:
SELECT *
FROM suppliers
WHERE supplier_name = ‘IBM’
OR supplier_name = ‘Hewlett Packard’
OR supplier_name = ‘Microsoft’;
As you can see, using the IN function makes the statement easier to read and more efficient.
BETWEEN
The BETWEEN condition allows you to retrieve values within a range.
The syntax for the BETWEEN condition is:
SELECT columns
FROM tables
WHERE column1 between value1 and value2;
This SQL statement will return the records where column1 is within the range of value1 and value2 (inclusive). The BETWEEN function can be used in any valid SQL statement – select, insert, update, or delete.
The following is an SQL statement that uses the BETWEEN function:
SELECT *
FROM suppliers
WHERE supplier_id between 5000 AND 5010;
This would return all rows where the supplier_id is between 5000 and 5010, inclusive. It is equivalent to the following SQL statement:
SELECT *
FROM suppliers
WHERE supplier_id >= 5000
AND supplier_id <= 5010;
EXISTS Condition
The EXISTS condition is considered “to be met” if the subquery returns at least one row.
The syntax for the EXISTS condition is:
SELECT columns
FROM tables
WHERE EXISTS ( subquery );
The EXISTS condition can be used in any valid SQL statement – select, insert, update, or delete.
Example1:
Let’s take a look at a simple example. The following is an SQL statement that uses the EXISTS condition:
SELECT *
FROM suppliers
WHERE EXISTS (select *  from orders where suppliers.supplier_id = orders.supplier_id);
This select statement will return all records from the suppliers table where there is at least one record in the orders table with the same supplier_id.
Example 2: NOT EXISTS
The EXISTS condition can also be combined with the NOT operator.
For example,
SELECT *    FROM suppliers  WHERE not exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);
This will return all records from the suppliers table where there are no records in the orders table for the given supplier_id.
Example 3:  DELETE Statement
The following is an example of a delete statement that utilizes the EXISTS condition:
DELETE FROM suppliers  WHERE EXISTS (select * from orders where suppliers.supplier_id = orders.supplier_id);
/by
, , ,

RETURN statement in PL SQL

The use of the RETURN statement is unique to functions. The RETURN statement is used to return some value. In fact, the primary reason for storing a PL/SQL block as a function is to return this value—this is the purpose of the function. For example, if a function is meant to compute the total payments received so far from guest reservations booked on a cruise, then the function will do whatever it needs to do to arrive at this final value and use the RETURN statement at the end to send the result back to the function call.
If you attempt to compile a function that has no RETURN statement, you will succeed, and the function will be stored in the data dictionary with a status of VALID. However, when you attempt to execute the function, you will receive a message like this:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at “[schema.function_name]”, line 6
ORA-06512: at line 1
Therefore, it is the developer’s responsibility to remember the RETURN statement. The compilation process won’t remind you that it’s required.
The function processes its statements until the RETURN statement is reached. Once the RETURN statement is processed, the execution of the function will stop. Any statements that follow will be ignored, and control is returned to the calling source. Therefore, it is considered good design to make the RETURN statement the last executable statement. However, the parser does not require this. Your function will compile without any RETURN statement or with a RETURN statement that precedes other valid PL/SQL statements.
/by
, , ,

Parameters in PL SQL

A parameter is a variable whose value can be defined at execution time and can be exchanged between the procedure and the calling PL/SQL block. Parameter values can be passed in to the procedure from the calling PL/SQL block and can optionally have their values passed back out of the procedure to the calling PL/SQL block upon the completion of the procedure’s execution
Parameters are declared at the top of the procedure within a set of parentheses. Each parameter declaration includes the following:

  • A name, defined by the developer, and adhering to the rules of object names (discussed earlier).
  • The type of parameter, which will either be IN, OUT, or IN OUT. The default is IN.
  • The datatype. Note that no specification or precision is allowed in parameter datatype declarations. To declare something as an alphanumeric string, you can use VARCHAR2, but you cannot use, for example, VARCHAR2(30).
  • Optionally, a parameter may be provided with a default value. This can be done by using the reserved word DEFAULT, followed by a value or expression that is consistent with the declared datatype for the parameter. The DEFAULT value identifies the value the parameter will have if the calling PL/SQL block doesn’t assign a value.

After each parameter declaration, you may place a comma and follow it with another parameter declaration.
The following is an example of a procedure header that uses parameters:
PROCEDURE PROC_SCHEDULE_CRUISE
  ( p_start_date IN DATE DEFAULT SYSDATE
   , p_total_days IN NUMBER
   , p_ship_id IN NUMBER
   , p_cruise_name IN VARCHAR2 DEFAULT ‘Island Getaway’)
IS
… code follows …This procedure declares four parameters. Each parameter is an IN parameter. Each parameter is assigned a datatype. The parameter p_cruise_name is given a datatype of VARCHAR2; the length cannot be specified in a parameter datatype declaration.
Two of the parameters are assigned default values. The first, p_start_date, uses the Oracle pseudocolumn SYSDATE, and the second, p_cruise_name, is assigned the string, ‘Island Getaway’.
Functions parameters
Functions take parameters, just like procedures do, and just like procedures, a parameter for a function can be an IN, OUT, or an IN OUT parameter. The default parameter type is an IN parameter.
However, unlike a procedure, a function always returns a value through its unique RETURN statement, and this value replaces the original call to the function in the expression that calls the function. Given this, functions are not generally used to pass OUT or IN OUT parameters. Furthermore, the OUT and IN OUT parameter will not work with function calls that are made from SQL statements. For example, consider the following function:
FUNCTION FUNC_COMPUTE_TAX
(p_order_amount IN OUT NUMBER)
RETURN NUMBER
IS
BEGIN
  p_order_amount := p_order_amount * 1.05;
  RETURN p_order_amount * .05;
END;
This function has an IN OUT parameter. The parameter comes IN as some dollar amount representing an order; it goes OUT with tax added. The function RETURNS the amount of the tax itself, as a NUMBER datatype

/by
, , ,

Invoking Procedures/Functions

Once a procedure has been created and stored in the database, it can be invoked from

  • An executable statement of a PL/SQL block
  • A command entered in the SQL*Plus command-line interface

Executing a Procedure from a PL/SQL Block
To invoke a procedure from another PL/SQL block, use a single statement that names the procedure. For example, suppose you’ve created a procedure called PROC_UPDATE_CRUISE_STATUS. The following PL/SQL block will execute the procedure:
BEGIN
  PROC_UPDATE_CRUISE_STATUS;
END;
Executing a Procedure from the SQL*Plus Command Line
You can execute a PL/SQL procedure from within the SQL*Plus command line without having to write another PL/SQL block to do it. The SQL command EXECUTE, or EXEC for short, must be used.
For example, if you have already stored a procedure called PROC_RUN_BATCH with no parameters, then the following statement, entered in the SQL*Plus window at the SQL prompt, will invoke the procedure:
EXECUTE PROC_RUN_BATCH;

Invoking Functions
Functions are never called in a stand-alone statement as procedures are. Instead, a function call is always part of some other expression. Valid PL/SQL expressions can incorporate functions anywhere that a variable would be accepted. Valid SQL expressions may also invoke functions, but with a few limitations—only certain types of functions can be invoked from SQL.
The following is a sample of a block that might call our sample FUNC_COUNT_GUESTS function:
PROCEDURE PROC_ORDER_FOOD (p_cruise_number NUMBER)
IS
  v_guest_count NUMBER(10);
BEGIN
  — Get the total number of guests
— for the given cruise
v_guest_count := FUNC_COUNT_GUESTS(p_cruise_number);
— Issue a purchase order
  INSERT INTO PURCHASE_ORDERS
    (PURCHASE_ORDER_ID, SUPPLIER_ID, PRODUCT_ID, QUANTITY)
    VALUES
    (SEQ_PURCHASE_ORDER_ID.NEXTVAL, 524, 1, v_guest_count)
  COMMIT;
END;
Functions Called from PL/SQL Expressions
Any PL/SQL function can be called from a PL/SQL expression of another program unit. Remember that expressions can be found in many places within PL/SQL:

  • The right side of an assignment statement
  • The Boolean expression of an IF … THEN … END IF statement
  • The Boolean expression of a WHILE loop
  • The calculation of a variable’s default value

In short, anywhere you might use a PL/SQL variable, you can issue a function call.
Examples:
1. DECLARE
  v_official_statement VARCHAR2(1000);
BEGIN
  v_official_statement := ‘The leading customer is ‘ ||
                          leading_customer;
END;
2. Functions can even be used as parameter values to other functions. For example,
BEGIN
  IF (leading_customer(get_largest_department) = ‘Iglesias’)
  THEN
    DBMS_OUTPUT.PUT_LINE(‘Found the leading customer’);
  END IF;
END;
3. SELECT     COUNT(SHIP_ID) NUMBER_OF_SHIPS,
           leading_customer
FROM       SHIPS;

/by
, , ,

Create, Alter and Drop

Creating Procedures
The following is a code sample that will create a stored procedure named PROC_RESET_ERROR_LOG:
CREATE PROCEDURE PROC_RESET_ERROR_LOG IS
BEGIN
  — Clean out the ERRORS table
  DELETE FROM ERRORS;
  COMMIT;
END;
The syntax to create a function is similar to the syntax used to create a procedure, with one addition: the RETURN declaration. The following is a sample CREATE FUNCTION statement.
CREATE OR REPLACE FUNCTION FUNC_COUNT_GUESTS
  (p_cruise_id NUMBER)
RETURN NUMBER
IS
  v_count NUMBER(10)
BEGIN
  SELECT COUNT(G.GUEST_ID)
  INTO   v_count
FROM     GUESTS G,
         GUEST_BOOKINGS GB
WHERE    G.GUEST_ID = GB.GUEST_BOOKING_ID
  AND    GB.CRUISE_ID = p_cruise_id;
RETURN   v_count;
END;

This function will take a single parameter, p_cruise_id. This parameter could include the parameter type declaration, such as IN, OUT, or IN OUT, but this example leaves it out, so this parameter is assumed to be the default IN parameter type, just as it would be assumed in a procedure. This function will use the p_cruise_id parameter to query the database and count the total number of guests for a single cruise. The result of the query is then returned to the calling block, using the RETURN statement at the end of the function.
If you think of the entire function as a variable, then think of the RETURN datatype as the function’s datatype.

Altering Procedures
Once a procedure has been created, you can use two methods to “alter” the procedure. If you are replacing the original source code with a new set of source code, use the OR REPLACE option discussed in the previous section. This is true for any code modification at all. If, however, you are recompiling the procedure without changing the code, then use the ALTER PROCEDURE command.
The ALTER PROCEDURE command is required when your stored procedure has not been changed in and of itself, but another database object referenced from within your procedure, such as a table, has been changed. This automatically causes your procedure to be flagged as INVALID.
CREATE OR REPLACE PROCEDURE PROC_RESET_ERROR_LOG IS
BEGIN
  — Clean out the ERRORS table
  DELETE FROM ERRORS;
  COMMIT;
END;
ALTER PROCEDURE PROC_RESET_ERROR_LOG COMPILE;
As with a procedure, a function may reference database objects from within its code. As with a procedure, if those database objects are changed, then the function must be recompiled. To perform this recompilation, use the ALTER FUNCTION …COMPILE command.
ALTER FUNCTION FUNC_COUNT_GUESTS COMPILE;
CREATE OR REPLACE FUNCTION FUNC_COUNT_GUESTS
  (p_cruise_id NUMBER)
RETURN NUMBER
IS
  v_count NUMBER(10)
BEGIN
  Statements;
END;
Dropping Procedures
An example of a command that drops a procedure is shown in the following code listing:
DROP PROCEDURE PROC_RESET_ERROR_LOG;
Once this command is successfully executed, the database response “Procedure dropped” will be displayed.
To drop a function, use the DROP … FUNCTION statement. The following is a sample command that will drop our sample function:
DROP FUNCTION FUNC_COUNT_GUESTS;

/by