Prior to Oracle9i, user defined functions can be only single-row functions. Starting with Oracle9i, user-defined functions can also be defined as aggregate functions.
Case in SQL : 1) All of the expressions ( expr, comparison_expr, and return_expr) must be of the same data type, which can be CHAR, VARCHAR2, NCHAR, or NVARCHAR2. 2) Default is not must. If no match and no default leg then it returns NULL.
You can use sub queries not only in the WHERE clause, but also in the HAVING clause.
So whenever null values are likely to be part of the results set of a subquery, do not use the NOT IN operator.
A NOT NULL database column constraint does not apply to variables that are declared using %TYPE.
Therefore, if you declare a variable using the %TYPE attribute that uses a database column defined as NOT
NULL, you can assign the NULL value to the variable.
The following functions are not available in procedural statements:
• DECODE. • Group functions: AVG, MIN, MAX, COUNT, SUM, STDDEV, and VARIANCE. Group functions apply to groups of rows in a table and therefore are available only in SQL statements in a PL/SQL block.
DECLARE
last_name VARCHAR2(25) := ’King’;
BEGIN
DELETE FROM employees WHERE last_name = last_name;
end; -- deletes all rows where last_name is not null
Use these cursor attributes in PL/SQL statements, but not in SQL statements.
Statement-Level Rollbacks : Part of a transaction can be discarded by an implicit rollback if a statement execution error is detected.
The Oracle Server locks data at the lowest level possible by default .
BETWEEN … AND … is actually translated by Oracle server to a pair of AND conditions: (a >= lower limit) AND (a <= higher limit). So using BETWEEN … AND … has no performance benefits, and it is used for logical simplicity.
IN ( ... ) is actually translated by Oracle server to a set of OR conditions: a = value1 OR a = value2 OR a = value3. So using IN ( ... ) has no performance benefits, and it is used for logical simplicity.
When you commit a transaction, the Oracle server releases the rollback information but does not immediately destroy it. The information remains in the undo segment to create read-consistent views of pertinent data for queries that started before the transaction committed.
--------------Restrictions on User-Defined Functions:
1) User-defined functions cannot be used in situations that require an unchanging definition. Thus, you cannot use user-defined functions:
- In a CHECK constraint clause of a CREATE TABLE or ALTER TABLE statement
- In a DEFAULT clause of a CREATE TABLE or ALTER TABLE statement
2) In addition, when a function is called from within a query or DML statement, the function cannot:
- Have OUT or IN OUT parameters
- Commit or roll back the current transaction, create a savepoint or roll back to a savepoint, or alter the session or the system. DDL statements implicitly commit the current transaction, so a user-defined function cannot execute any DDL statements.
- Write to the database, if the function is being called from a SELECT statement. However, a function called from a subquery in a DML statement can write to the database.
- Write to the same table that is being modified by the statement from which the function is called, if the function is called from a DML statement.
Except for the restriction on OUT and IN OUT parameters, Oracle Database enforces these restrictions not only for function when called directly from the SQL statement, but also for any functions that function calls, and on any functions called from the SQL statements executed by function or any functions it calls.
-- it should only return sql datatypes.
3) To be callable from SQL expressions, a user-defined function must:
• Be a stored function • Accept only IN parameters • Accept only valid SQL data types, not PL/SQL specific types, as parameters • Return data types that are valid SQL data types,not PL/SQL specific types • Parameters to a PL/SQL function called from a SQL statement must use positional notation. Named notation is not supported.
- can't be used to specify a default value for a column.
• Functions called from SQL expressions cannot contain DML statements. • Functions called from UPDATE/DELETE statements on a table T cannot contain DML on the same table T. • Functions called from a DML statement on a table T cannot query the same table. • Functions called from SQL statements cannot contain statements that end the transactions. • Calls to subprograms that break the previous restriction are not allowed in the function.
--------------------
There are no predefined data types for PL/SQL records, as there are for scalar variables. Therefore, you must create the record type first and then declare an identifier using that type. The size of a INDEX BY table is unconstrained.INDEX BY tables can have one column and a primary key, neither of which can be named.
The NOT NULL constraint prevents nulls from being assigned to the PL/ SQL table of that type. Do not initialize the INDEX BY table. An INDEX BY table is
not populated at the time of declaration. It contains no keys or no values. An explicit executable statement is required to initialize (populate) the INDEX BY table.
INDEX-BY tables are initially sparse. That enables you, for example, to store reference data in an INDEX-BY table using a numeric primary key as the index.
At a given point of time, a INDEX BY table can store only the details of any one of the columns of a database table. ( Thats why we use Index By table of Records)
identifier reference%ROWTYPE;
Reference: is the name of the table, view, cursor, or cursor variable on which the record is to be based. The table or view must exist for this reference to be valid.
You can also assign one record to another if they have the same data type. A user-defined record and a %ROWTYPE record never have the same data type.
Before the first fetch, %NOTFOUND evaluates to NULL. So, if FETCH never executes successfully, the loop is never exited.
To be safe, use the following EXIT statement instead:
EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;
If a cursor is not open, referencing it with %NOTFOUND raises INVALID_CURSOR.
Before the first fetch (just after OPEN), %ROWCOUNT yields 0. Thereafter, it yields the number of rows fetched so far.
If using %ROWCOUNT, add a test for no rows in the cursor by using the %NOTFOUND attribute, because the row count is not incremented if the fetch does not retrieve any rows.
you can establish an active set several times by reopening a cursor ( it must be closed before that)
Cursor For Loops with Select substatement: Because the cursor is not declared with a name, you cannot test its attributes.
Because the Oracle Server releases locks at the end of the transaction, you should not commit across fetches from an explicit cursor if FOR UPDATE is
used. The FOR UPDATE clause is the last clause in a select statement, even after the ORDER BY, if one exists.
------------ Exception ---------
Define several exception handlers, each with its own set of actions, for the block. When an exception occurs, PL/SQL processes only one handler before leaving the block.
Exceptions cannot appear in assignment statements or SQL statements
OTHERS can trap application specific errors as well.
SQLCODE Value Description
0 No exception encountered
1 User-defined exception
+100 NO_DATA_FOUND exception
negative number Another Oracle server error number
Use the RAISE_APPLICATION_ERROR procedure to communicate a predefined exception interactively by returning a nonstandard error code and error message.
raise_application_error (error_number, message[, {TRUE | FALSE}])
error_number is a user-specified number for the exception between –20000 and –20999.
TRUE | FALSE is an optional Boolean parameter (If TRUE, the error is placed on the stack of previous errors. If FALSE, the default, the error replaces all previous errors.)
PL/SQL declares predefined exceptions in the STANDARD package.
PRAGMA (also called pseudoinstructions) is the keyword that signifies that the statement is a compiler directive, which is not processed when the PL/SQL block is executed.
In general, a block is either an anonymous block or a named block (known as a subprogram or program unit).
An OUT or IN OUT parameter must be assigned a value before returning to the calling environment.
By default, the IN parameter is passed by reference and the OUT and IN OUT parameters are passed by value. To improve performance with OUT and IN OUT parameters, the compiler hint NOCOPY can be used to request to pass by reference.
NOCOPY is a hint and Oracle does not guarantee a parameter will be passed by reference when explicitly mentioned. Here are some places where this is not possible:
1. When the call is a remote procedure call 2. When the actual parameter being passed is an expression 3. When there is an implicit conversion involved
Attempts to change the value of an IN parameter result in an error.
You can initialize IN parameters to default values. That way, you can pass different numbers of actual parameters to a subprogram, accepting or overriding the default values as you please. Moreover, you can add new formal parameters without having to change every call to the subprogram.
All the positional parameters should precede the named parameters in a subprogram call.
Declaring local
subprograms enhances the clarity of the code by assigning appropriate business-rule identifiers to blocks of code. You must declare the subprogram in the declaration section of the block, and it must be the last item, after all the other program items.
If the exception is handled in the calling procedure, all DML statements in the calling procedure and in the called procedure remain as part of the transaction.
If the exception is unhandled in the calling procedure, the calling procedure terminates and the exception propagates to the calling environment. All the DML statements in the calling procedure and the called procedure are rolled back along with any changes to any host variables. The host environment determines the outcome for the unhandled exception.
You cannot reference host or bind variables in the PL/SQL block of a stored function.
There can be a RETURN statement in the exception section of the program also. -- Function [295]
To have a function return multiple values is poor programming practice.
In a function, there must be at least one execution path that leads to a RETURN statement. Otherwise, you get a Function returned without value error at 'run time'.
The CREATE OR REPLACE syntax is equivalent to dropping a function and recreating it. Privileges granted on the function remain the same when this syntax is used.
Running the CREATE PROCEDURE statement stores the source code in the data dictionary even if the procedure contains compilation errors.
Benefits of Subprograms : • Easy maintenance • Improved data security and integrity • Improved performance • Improved code clarity
There are more than 80 system privileges. Privileges that use the word CREATE or ANY are system privileges; for example, GRANT ALTER ANY TABLE TO green;. System privileges are assigned by user SYSTEM or SYS.
To create a PL/SQL subprogram, you must have the system privilege CREATE PROCEDURE. You can alter, drop, or execute PL/SQL subprogram without any further privileges being required.
If a PL/SQL subprogram refers to any objects that are not in the same schema, you must be granted access to these explicitly, not through a role.
If the ANY keyword is used, you can create, alter, drop, or execute your own subprograms and those in another schema. Note that the ANY keyword is optional only for the CREATE PROCEDURE privilege.
By defalust its AUTHID DEFINER. To ensure that the procedure executes using the security of the executing user, and not the owner, use AUTHID CURRENT_USER.
USER_OBJECTS, USER_SOURCE, USER_ERRORS
ALL_OBJECTS and DBA_OBJECTS views, contains the additional OWNER column, for the owner of the object.
-------------- Pkg ----
When you call a packaged PL/SQL construct for the first time, the whole package is loaded into memory. Thus, later calls to constructs in the same package require no disk input/output (I/O).
The Oracle server stores the specification and body of a package separately in the database. This enables you to change the definition of a program construct in the package body without causing the Oracle server to invalidate other schema objects that call or reference the program construct.
If you redeclare a built-in subprogram in a PL/SQL program, your local declaration overrides the global declaration.
Note: In the slide, the private function is shown above the public procedure. When you are coding the package body, the definition of the private function has to be above the definition of the public procedure. [26/392]
Only subprograms and cursors declarations without body in a package specification have an underlying implementation in the package body. So if a specification declares only types, constants, variables, exceptions, and call specifications, the package body is unnecessary. However, the body can still be used to initialize items declared in the package specification.
Variables declared in the package specification are initialized to NULL by default.
In package specification: Initialize a variable with a constant value or formula within the declaration, if required; otherwise, the variable is initialized implicitly to NULL.
All private constructs must be declared before they are used in the public constructs.
It is quite common in the package body to see all private variables and subprograms defined first and the public subprograms defined last.
You can code and compile a specification without its body. Then stored subprograms that reference the package can compile as well. You need not define the package body fully until you are ready to complete the application.
Packaged public variables and cursors persist for the duration of a session. Thus, they can be shared by all subprograms that execute in the environment. They also enable you to maintain data across transactions without having to store it in the database. Private constructs also persist for the duration of the session, but
can only be accessed within the package.
Only local or packaged subprograms can be overloaded. Stand-alone subprograms cannot be overloaded.
Requires the formal parameters of the subprograms to differ in number, order, or datatype family.
For like-named subprograms at the same level of scope, the compiler needs an exact match in number, order, and datatype between the actual and formal parameters.
Forward Declaration if : • Define subprograms in logical or alphabetical order • Define mutually recursive subprograms • Group subprograms in a package
One-Time-Only Procedures : to initialize public/private pkg variables. No 'End' Keyword user at the end of them.
A one-time-only procedure is executed only when the package is first invoked within the other user session. You can use this feature to initialize variables only once per session.
Note: If you are using Oracle versions prior to 8i, you need to assert the purity level of the function in the package specification by using PRAGMA RESTRICT_REFERENCES. If this is not specified, you get an
error message saying that the function TAX does not guarantee that it will not update the database while invoking the package function in a query.
Persistence or Gloabal variable/cursor: The state of a package variable or cursor persists across transactions within a session. The state does not persist from session to session for the same user, nor does it persist from user to user.
The EXECUTE IMMEDIATE statement can perform dynamic single-row queries. Also, this is used for functionality such as objects and collections, which are not supported by DBMS_SQL. If the statement is a multirow SELECT statement, you use OPEN-FOR, FETCH, and CLOSE statements.
Dynamic SQL supports all the SQL data types. For example, define variables and bind arguments can be collections, LOBs, instances of an object type, and REFs. As a rule, dynamic SQL does not support
DBMS_DDL.
PL/SQL-specific types. For example, define variables and bind arguments cannot be Booleans or indexby tables. The only exception is that a PL/SQL record can appear in the INTO clause.
EXECUTE IMMEDIATE reprepares the dynamic string before every execution.
------------- Triggers --------
If multiple triggers are defined for a table, be aware that the order in which multiple triggers of the same type fire is arbitrary. To ensure that triggers of the same type are fired in a particular order, consolidate the triggers into one trigger that calls separate procedures in the desired order.
Statement: The trigger body executes once for the triggering event. This is the default. A statement trigger fires once, even if no rows are affected at all.
Row: The trigger body executes once for each row affected by the triggering event. A row trigger is not executed if the triggering event affects no rows.
Note: The size of a trigger cannot be more than 32 K.
If the logic for the trigger is very lengthy, create stored procedures with the logic and invoke them in the trigger body.
Use triggers to guarantee that when a specific operation is performed, related actions are performed.
Use database triggers only for centralized, global operations that should be fired for the triggering statement, regardless of which user or application issues the statement.
Trigger names must be unique with respect to other triggers in the same schema. Trigger names do not need to be unique with respect to other schema objects, such as tables, views, and procedures.
Using column names along with the UPDATE clause in the trigger improves performance, because the trigger fires only when that particular column is updated and thus avoids unintended firing when any other column is updated.
When a database trigger fails, the triggering statement is automatically rolled back by the Oracle server.
You can combine several triggering events into one by taking advantage of the special conditional predicates INSERTING, UPDATING, and DELETING within the trigger body.
There is no colon (:) prefix for OLD and New qualifiers if the qualifiers are referenced in the WHEN restricting condition.
The NEW qualifier cannot be prefixed with a colon in the WHEN clause because the WHEN clause is outside the PL/SQL blocks.
Note: If a view is inherently updatable and has INSTEAD OF triggers, the triggers take precedence.
INSTEAD OF triggers are row triggers.
The CHECK option for views is not enforced when insertions or updates to the view are performed by using INSTEAD OF triggers. The INSTEAD OF trigger body must enforce the check.
COMMIT, ROLLBACK, and
SAVEPOINT statements are not allowed within the trigger body. It is possible to commit or rollback indirectly by calling a procedure, but it is not recommended because of side effects to transactions.
Disable a trigger to improve performance or to avoid data integrity checks when loading massive amounts of data by using utilities such as SQL*Loader. You may also want to disable the trigger when it references a database object that is currently unavailable, owing to a failed network connection, disk crash, offline data file, or offline tablespace.
A triggering event or a statement within the trigger can cause one or more integrity constraints to be checked.
Triggers can also cause other triggers to fire (cascading triggers).
CREATE [OR REPLACE] TRIGGER trigger_name
timing
[ddl_event1 [OR ddl_event2 OR ...]]
ON {DATABASE|SCHEMA}
trigger_body
DDL triggers fire only if the object being created is a cluster, function, index, package, procedure, role, sequence, synonym, table, tablespace, trigger, type, view, or user.
No comments:
Post a Comment