Comments -
Single line comment -- ...
Multip line comment /* ... */
You cannot nest comments
Declartions -
PL/SQL treats any zero-length string like a null
%TYPE - datatype of variable or database column
%ROWTYPE - Record data type or database record type
DECLARE
acct_name VARCHAR2(10);
acct_id INTEGER(4) NOT NULL := 9999;
hours_worked INTEGER DEFAULT 40;
credit_limit CONSTANT REAL := 5000.00;
sal_val EMP.SAL%TYPE;
emp_rec EMP%ROWTYPE
CURSOR c1 IS select * from dept;
dept_rec c1%ROWTYPE;
SUBTYPE BirthDate IS DATE NOT NULL;
SUBTYPE pinteger IS PLS_INTEGER RANGE -9 .. 9;
When subtypes are passed to the procedure, it enforces the
Using SUBTYPE With %TYPE and %ROWTYPE - inherits the precision, but will not inherit NOT NULL and DEFAULT Values.
CASE
WHEN condition THEN
WHEN condition THEN
ELSE
END CASE;
- condition is evaluated sequentially, if it satisfies condition, then it wont evaluate other conditions.
LOOP
EXIT WHEN condition;
END LOOP;
WHILE condition LOOP
END LOOP;
<>
do something
IF cond THEN GO TO label1; END IF;
<>
do something
GOTO statement cannot branch into an IF statement, CASE statement, LOOP statement, or sub-block
Name Resolution :-
The names of database columns take precedence over the names of local variables and formal parameters. If a variable and a column with the same name are both used in a WHERE clause, SQL considers that both cases refer to the column.
You can use the block name or subprogram name for Name resolution.
<>
DECLARE
last_name VARCHAR2(10) := 'King';
BEGIN
-- deletes everyone, because both LAST_NAMEs refer to the column
DELETE FROM employees2 WHERE last_name = last_name;
DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.');
-- OK, block name specifies that 2nd last_name is a variable
DELETE FROM employees2 WHERE last_name = main.last_name;
END;
NULL :-
- Comparing null gives NULL
- Applying Logical operator NOT null = NULL
- IN & NOT IN Operator - ignores NULL.
- PL/SQL treats any zero-length string like a null
- Concatenation of NULL is similar to concatenating empty string
- If the condition evaluates to NULL , then it wont run the commands
IF NOT x > y THEN high := y; ELSE high := x; END IF -- it results in x
- DECODE(abcd, NULL, 'hello') --decode matches NULL
- REPLACE(oldstr, NULL, newstr) --NULL is a valid argument here
DataTypes -
- A composite type has internal components that can be manipulated individually. array, record, or table
- A LOB type holds values, called lob locators, that specify the location of large objects, such as text blocks or graphic images, that are stored separately from other database data. LOB types include BFILE, BLOB, CLOB, and NCLOB. See "PL/SQL LOB Types".
- A reference type holds values, called pointers, that designate other program items. ThEF CURSORS and REFs to object types.
- A scalar type has no internal components. number, character, Boolean, and date/time data.
ROWID :-
ROWID - can store physical rowids
UROWID - can store physical, logical and foreign rowids
ROWIDTOCHAR () - converts rowid to char string
CHARTOROWID ( ) -converts char string to rowid
If the conversion fails because it is not a valid rowid, it raises SYS_INVALID_ROWID exception.
Logica ROWID -
Oracle uses them to construct secondary indexes and index organized tables. It can move across data blocks when new rows are inserted. Even if the physical location (physical rowid) changes, logical rowid wont change.
Oracle uses the guess to search for the block directly. if there are lot of inserts, then guesses can become stale and slow performance. To obtain fresh guesses, you can rebuild the secondary indexes. You use ROWID psedocolumn to select logical rowids
ANALYZE ccan help you to track staleness of guesses.
VARCHAR2
For a VARCHAR2 that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes, PL/SQL preallocates the full declared length of the variable
VARCHAR2 in the PL/SQL can hold 32767, but in the database the max value is 2000
Single line comment -- ...
Multip line comment /* ... */
You cannot nest comments
Declartions -
PL/SQL treats any zero-length string like a null
%TYPE - datatype of variable or database column
%ROWTYPE - Record data type or database record type
DECLARE
acct_name VARCHAR2(10);
acct_id INTEGER(4) NOT NULL := 9999;
hours_worked INTEGER DEFAULT 40;
credit_limit CONSTANT REAL := 5000.00;
sal_val EMP.SAL%TYPE;
emp_rec EMP%ROWTYPE
CURSOR c1 IS select * from dept;
dept_rec c1%ROWTYPE;
SUBTYPE BirthDate IS DATE NOT NULL;
SUBTYPE pinteger IS PLS_INTEGER RANGE -9 .. 9;
When subtypes are passed to the procedure, it enforces the
NOT NULL constraint, but does not enforce the size.Using SUBTYPE With %TYPE and %ROWTYPE - inherits the precision, but will not inherit NOT NULL and DEFAULT Values.
CASE
WHEN condition THEN
WHEN condition THEN
ELSE
END CASE;
- condition is evaluated sequentially, if it satisfies condition, then it wont evaluate other conditions.
LOOP
EXIT WHEN condition;
END LOOP;
WHILE condition LOOP
END LOOP;
<
do something
IF cond THEN GO TO label1; END IF;
<
do something
GOTO statement cannot branch into an IF statement, CASE statement, LOOP statement, or sub-block
Name Resolution :-
The names of database columns take precedence over the names of local variables and formal parameters. If a variable and a column with the same name are both used in a WHERE clause, SQL considers that both cases refer to the column.
You can use the block name or subprogram name for Name resolution.
<
DECLARE
last_name VARCHAR2(10) := 'King';
BEGIN
-- deletes everyone, because both LAST_NAMEs refer to the column
DELETE FROM employees2 WHERE last_name = last_name;
DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.');
-- OK, block name specifies that 2nd last_name is a variable
DELETE FROM employees2 WHERE last_name = main.last_name;
END;
NULL :-
- Comparing null gives NULL
- Applying Logical operator NOT null = NULL
- PL/SQL treats any zero-length string like a null
- Concatenation of NULL is similar to concatenating empty string
IF NOT x > y THEN high := y; ELSE high := x; END IF -- it results in x
- DECODE(abcd, NULL, 'hello') --decode matches NULL
- REPLACE(oldstr, NULL, newstr) --NULL is a valid argument here
DataTypes -
- A composite type has internal components that can be manipulated individually. array, record, or table
- A LOB type holds values, called lob locators, that specify the location of large objects, such as text blocks or graphic images, that are stored separately from other database data. LOB types include BFILE, BLOB, CLOB, and NCLOB. See "PL/SQL LOB Types".
- A reference type holds values, called pointers, that designate other program items. ThEF CURSORS and REFs to object types.
- A scalar type has no internal components. number, character, Boolean, and date/time data.
ROWID :-
ROWID - can store physical rowids
UROWID - can store physical, logical and foreign rowids
ROWIDTOCHAR () - converts rowid to char string
CHARTOROWID ( ) -converts char string to rowid
If the conversion fails because it is not a valid rowid, it raises SYS_INVALID_ROWID exception.
Logica ROWID -
Oracle uses them to construct secondary indexes and index organized tables. It can move across data blocks when new rows are inserted. Even if the physical location (physical rowid) changes, logical rowid wont change.
Oracle uses the guess to search for the block directly. if there are lot of inserts, then guesses can become stale and slow performance. To obtain fresh guesses, you can rebuild the secondary indexes. You use ROWID psedocolumn to select logical rowids
ANALYZE ccan help you to track staleness of guesses.
VARCHAR2
For a VARCHAR2 that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes, PL/SQL preallocates the full declared length of the variable
VARCHAR2 in the PL/SQL can hold 32767, but in the database the max value is 2000
Comments