, , , ,

BULK COLLECT

Bulk Collect concept help us reducing the Iterations between SQL Engine and PL/SQL Block.

Note:- When ever, we excute any DML (INSERT or UPDATE or DELETE) Operations in the PL/SQL block then It will move from PL/SQL Block to SQL Engine to excute the DML Operation in SQL Engine. And if we have some SELECT statement in the PL/SQL Block, then it will move from SQL Enginer to get the data from SQL Enginer to the PL/SQL Block. For the SELECT statement, we use the BULK COLLECT to record the Iteration from the SQL Enginer to the PL/SQL Block.

If I have to put my self in other word then, I should say, BULK COLLECT will process group of SELECT statements at one short.

Similarly for the DML Operations, we use FORALL Concept, please refer the FORALL Concept for more details.

Note:- This Example is based on the EMP table in the SCOTT schema. Here say, EMP table have 90 Records and if we do it in the Normal way (Without BULK COLLECT concept, then It will iterate SQL enginer to the PL/SQL Block for 90 Times). By using BULK COLLECT, we can completly reduce the Iterations.

In the following Example, I have limited to 10, so for 90 records, this will iterate for 9 times.

Example:-
———–

declare
type array is table of number index by binary_integer;
l_data array;
cursor c is select empno from emp;
begin
open c;
loop
fetch c bulk collect into l_data limit 10;
if ( c%notfound )
then
dbms_output.put_line
( ‘Cursor returned NOT FOUND but array has ‘ || l_data.count
|| ‘ left to process’ );
else
dbms_output.put_line
( ‘We have ‘ || l_data.count
|| ‘ to process’ );
end if;
exit when c%notfound;
end loop;
close c;
end;

Returning clause with the Bulk collect with the DML opeartions:-
—————————————————————————

DELETE FROM emp WHERE num = 30
RETURNING empno, ename BULK COLLECT INTO p_num, p_ename;
/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
, , ,

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
, , ,

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