Arrays in PLSQL

September 19, 2015 0 Comments A+ a-


PL/SQL programming language provides a data structure called the ARRAY, which can store a fixed-size sequential collection of elements of the same type. A array is used to store an ordered collection of data, but it is often more useful to think of an array as a collection of variables of the same type.
All arrays consist of contiguous memory locations.

Each element in a varray has an index associated with it. It also has a maximum size that can be changed dynamically.
Creating a Array Type :
A array type is created with the CREATE TYPE statement. You must specify the maximum size and the type of elements stored in the array.

CREATE OR REPLACE TYPE array_type_name IS VARRAY(n) of <element_type>

  • array_type_name is a valid attribute name.
  • n is the number of elements (maximum) in the array.
  • element_type is the data type of the elements of the array.
NOTE:
Maximum size of a varray can be changed using the ALTER TYPE statement.

Example –
SET VERIFY OFF
SET SERVEROUTPUT ON
CL SCR
DECLARE
type namesarray IS VARRAY(5) OF VARCHAR2(10);
type grades IS VARRAY(5) OF INTEGER;
names namesarray;
marks grades;
total integer;
BEGIN
names := namesarray('Nita', 'Harish', 'Sonali');
marks:= grades(88, 75, 3);
total := names.count;
dbms_output.put_line('Total '|| total || ' Students');
FOR i in 1 .. total LOOP
dbms_output.put_line('Student: ' || names(i) || 'Marks: ' || marks(i));
END LOOP;
END;
/
o/p –
Total 3 Students
Student: Nita Marks: 88
Student: Harish Marks: 75
Student: Sonali Marks: 3
PL/SQL procedure successfully completed.

For Loop in PLSQL

September 19, 2015 0 Comments A+ a-


It is a type of looping where initialization, Incrementation and condition checking is automatically done by the system the formula given by user which leads to less amount of coding.
Syntax:
FOR IN [REVERSE] LOWERBOUND .. UPPERBOUND LOOP
STATEMENT ;
......
END LOOP;
/


While Loop in PLSQL

September 19, 2015 0 Comments A+ a-


It is a type of looping where condition is check first then it will execute the statement.
Syntax:
WHILE LOOP
STATEMENT1;
STATEMENT2;
..............
END LOOP;


Basic Loop in PLSQL

September 19, 2015 0 Comments A+ a-


Basic Loop :
In this loop statement is executed first then it will check the statement and looping statement executed at least once

SYNTAX:
LOOP
STATEMENT1;
STATEMENT2;
............
EXIT[WHEN<CONDITION>]
END LOOP;

Control Structure in PLSQL

September 19, 2015 0 Comments A+ a-


It is a block of executable statement which will perform a task once or number of times unless and until a condition is satisfied control structure are basically required to perform condition wise task.
PL/SQL provides following control structure to perform condition wise job.
  • If loop
  • Basic/While loop
  • For loop

Type of Programme in PL/SQL

September 19, 2015 0 Comments A+ a-


It supports two type of program to manipulate the oracle database.
  • Anonymous Block
  • Sub Program
The primary jobs of above program are same to manipulate database but they are categorised in two different types depending on their security and execution.

Advance Data Type in PLSQL

September 19, 2015 0 Comments A+ a-


PL/SQL Large Object (LOB) Data Types :
Large object (LOB) data types refer large to data items such as text, graphic images, video clips and sound waveforms. LOB data types allow efficient, random, piecewise access to this data.
Following are the predefined PL/SQL LOB data types:

User Defined Type in PLSQL

September 19, 2015 0 Comments A+ a-


PLSQL provide user define type, Type is collection of one or more number of own data type .
Syntax to Declare User Defined Type:
DECALRE
TYPE <T_NAME> IS RECORD(
V_NAME1 DATATYPE(SIZE),
V_NAME2 DATATYPE(SIZE),
.......................
.....................N)
;


V_NAME <T_NAME>;

Scalar Attribute in PLSQL

September 18, 2015 0 Comments A+ a-


If you want declare a variable and it’s data type is same as one column’s data type in one some table then you use scalar attribute.
The above data type in PL/SQL provides a scalar attribute %type.

Data Type in PL/SQL

September 18, 2015 0 Comments A+ a-


To design a programme using a language the minimum requirement is a variable and variable minimum requirement is data type.
PL/SQL provides two types of data types :
  • Scalar data type
  • Composite data type
  • Large Object (LOB)

Wildcard Operator

September 18, 2015 0 Comments A+ a-


Wildcard Operator/Like Operator:
The like operator is used to compare a value to similar values using wildcard operators.
It always use Like operator

Comments Line in PL/SQL

September 18, 2015 0 Comments A+ a-


The PL/SQL supports single line and multi-line comments. All characters available inside any comment are ignored by PL/SQL compiler. The PL/SQL single-line comments start with the delimiter – - (double hyphen) and multi-line comments are enclosed by /* and */.
Example -2
DECLARE
/* This programme written for show my website in PL/SQL programme My website is
www.tutorialslife.com*/


website varchar2(20):= 'www.tutorialslife.com';  --my website is assign to website variable
BEGIN
dbms_output.put_line(website);
END;
/

Output – www.tutorialslife.com
PL/SQL procedure successfully completed.

Identifiers in PL/SQL

September 18, 2015 0 Comments A+ a-


PL/SQL identifiers are constants, variables, exceptions, procedures, cursors, and reserved words.
The identifiers consist of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs and should not exceed 30 characters.
By default, identifiers are not case-sensitive. So you can use integer or INTEGER to represent a numeric value. You cannot use a reserved keyword as an identifier.

Overview – PL/SQL

September 18, 2015 0 Comments A+ a-


PL/SQL is pronounced as a language but it is not a language typically rather than it is a mechanism or technology implemented on Oracle server to manipulate Oracle database programmatically using SQL command

What is PL/SQL?
  • PL/SQL stands for Procedural Language extension of SQL.
  • PL/SQL is a combination of SQL along with the procedural features of programming languages.
  • It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.
  • It is a block structure language.

Data Control Language (DCL) in SQL

July 11, 2015 0 Comments A+ a-

ORACLE not only provides several diff. objects for customized database management but also it supports user concept or schema concept to protect those objects from invalid users.
No user can be created or manipulated from a general user rather than user creation and user manipulation is otherwise known as DBA user.

Transaction Control Language(TCL) in SQL

July 11, 2015 0 Comments A+ a-


Whenever DML operation is main in an oracle table, the transaction never be saved unless or until save command is issued or a discard is issued. TCL sublanguage is responsible data to do so TCL provide the following statement.
--> Commit (save)
--> Rollback (discard)
--> Save point

TCL mechanism:
Whenever DML operation is made in oracle table just before a temporary memory is created in side in the sever technical is known as ROLLBACK SEGEMENT and the table on which the DML operation is going to be made is copied temporary to the rollback segment is known as snapshot. If commit command is issued from a sql prompt a rollback segment along with the snapshot gets deleted and the records transfer to main table will be permanent.
If rollback command is issued from after DML operation a snapshot available inside the rollback segment again recopied to the original location with the old data and rollback segment gets deleted.

NOTE:
Data one committed cannot be rollback and rollback cannot be committed.

NOTE:
The basic difference between buffer memory and rollback segment is
Buffer is client side memory it holds statement for a Temporary period on other hand rollback segment is a sever side Temporary memory, which holds a table in the form of snapshot for a Temporary period.
SQL> INSERT;
SQL>commit; (save)
SQL>INSERT;
SQL>rollback; (discard)

Auto commit & Auto rollback:
In some case data are automatically committed and automatically rollback by the system defines which is technically known as auto commit and auto rollback.
Auto commit:
1.      Whenever a user exit out of SQL*PLUS normally after performing DML operation.
2.      Whenever DCL or DDL command issued after any DML operation.
Auto rollback:
1.      Whenever a user exit out of SQL*PLUS abnormally after performing DML operation.
2.      Dirty system crash.
Working with set auto commit on:
Save auto commit is a SQL*PLUS command once it is on, it will save the transaction immediately after DML operation is made. The default behaviour of auto commit is on.
SQL> set auto commit on;
Save point is logical pointer given to individual transaction or group of transaction made by user. Advantage of save point is responsible to keep track on individual transaction save point for rollback propose rollback to a particular point is excluding of that point rollback a particular point doesn’t mean above transaction as commit.

SQLl>insert;
SQL>save point A;
SQL> INSERT;
SQL>save point B;
SQL>INSERT;
SQL>save point C;
If you want second command rollback then
SQL>rollback to B;

NOTE:
Disadvantage of this command is takes random rollback.

Data Manipulated Language in SQL

July 11, 2015 0 Comments A+ a-


DML is also known as RML, which is Responsible to manipulated language, the records of table and statements are given below
Insert, update, delete
Syntaxfor insert
Insert into <t_name> [(col1, col2, col3,….N)
Values (value1, value2, value3……..N);
Above syntax is syntax of data insertion using column list. User has only remembered name of column not the sequence.
Insert NULL value in a column
NULL value can be assigned in a particular column in two different manners.
è  Explicitly using NULL keyword
è  Implicitly using column list
Ex
Insert into sysemp6
Values (2,BBB,NULL,’manger’);

Insert into sysemp6(empno,job, name)
Values (3,’manger’, ‘CCC’);
NOTE:
User is not allowed null value a column explicitly or implicitly if column explicitly, if column is protected with not null constraint.
Data insertion using substation variable
Ex
Insert into sysemp6
Values(&empno,’&name’,&salary,’&job’);

Syntaxrecord using update
Update<T_name>
Set col_name=<value>[col_name=value, …..];
[where <condition>];

Ex
Update  sysemp6
Set  sal=1000, job= ‘manger’
Where empno in (4,5);
When where clues are omitted from update statement then it will update all value of specific column.
Ex – update sysemp6
         Set sal = NULL;

DELETEusing delete

Delete from <t_name>
[Where <condition>];
If where clues are omitted from delete statement then all data are deleting from that table.
Some important queries
To copy a table

Create table <new t_name>
As select col1,col2,…..coln from <old t_name>;

NOTE:
Whenever a table is copied only NOTNULL constraint are copied to the new table except other constraint.
Copy only structure of table without data
Ex –
Create table hi
As select * from emp
Where 1=2;


DELETE identical record from a table:
Whenever ‘n’ number of column in a table is created in oracle database then ‘n+1’ column in a table is created, which is automatically and that column is known as ROWID. ROWID is a physical invisible column and their data are not equal by system.
Ex-
Select rowid, ename, sal from emp;
Ex -
Delete from emp
Where rowed like ‘%c’;

NOTE:
ROWNUMis another column provide by oracle, which is virtual column responsible to display the number of records using serial number .
Ex –
Select rownum, ename, ename from emp;

Constraints in SQL

July 11, 2015 0 Comments A+ a-


Constraint is rule or restriction imposed and individual column to above data enter
Oracle provide the following constraint to do so
1.     Primary key
2.     Not Null
3.     Unique
4.     Check
5.     Foreign Key

1.    PRIMARY KEY:
       This is constraint which in forces not null value and no report value in a column using this constraint the record is unique identify.
2.    NOT NULL
       This is constraint no not null value is column but it accepts duplicate value.
3.    UNIQUE
       Using this constraint no duplicate value is allow in column but accepts null
4.    CHECK
       It is used for check a condition.
NOTE:
       Multiple value null value accept in column the column                     
5.    FOREGIN KEY
       This is constraints which helpful up to make relationship between two or more than two table using a common attribute. It always refers to same table primary key or another table primary key to make a relationship. It is just opposite of primary key that means it can accept duplicate values and null.
It supports the concept of referential integrity.

NOTE :
       Refer integrity is a concept which no child record is allowed in the chid table unless and until the master record is available.
INCOMPOSEMENT OF CONSTRAINCY:
       Constraint can be impossible during table deign using two different levels
1.      Column level
2.      Table level

1.    COLUMN LEVEL
       Column level constraints are those constraints which are imposed in just after column rectification during table deign.
 NOTE :
       The basic different between column and table level constraint is, in column level maximum one constraint is imposed at one point of time whereas in table level multiple constraints can be imposed in table level
No Null -->  table level (incorrect)
Primary key--> column level (incorrect)

        Each and individual constraint is identify by a name either given by user or system. It is advisable to provide the constraint name by user.
User may follow following naming conversion for a constraint.
Tab_Col_Atype
ex
Myemp_sal_ck
NOTE:
NOT NULL always in column level.
FOREGIN KEY always in table level.

WORKING WITH ONDELETE CASCADE:
       On delete cascade is a keyword return with the foreign key which in force a rule that whenever a master record is deleted from master table all corresponding child records are automatically deleted from child table.
Ex
Create table sysemp
(
Empno number(4),
name vachar2(10),
dptno number(4) constraint sysemp_deptno_fk reference sysdept(deptno) on delete cascade);

Above ex is an ex in it’s FK in column level using on delete cascade.

WORKING WITH CONSTRAINT MANIPULATE :
       Constraint manipulation is constraint through which new constraint can be imposed or exiting constraint can be deleting after table deign. A constraint can be added or delete but a constraint can’t be modified.
       It is not mandatory to provide constraint during table deign rather than syntax to add a constraint after table deign.
SYNTAXTO ADD A CONSTRAINT AFTER TABLE DESIGN:
ALTER TABLE <T_NAME>
ADD CONSTRAINT<C_NAME><C_TYPE> (COL_NAME);
Above syntax is required to single column constraint in a single column.
ALTER TABLE<T_NAME>
ADD (CONSTRAINT <C_NAME><C_TYPE> (COL_NAME),
CONTRAINT <C_NAME><C_TYPE> (COL_NAME),
.....................................................N);
Above syntax is imposed multiple constraint using single alter.
SYNTAX TO DELETE:
ALTER TABLE <T_NAME>
DROP CONSTRAINT <C_NAME>

NOTE:
       Any constraint can be added after table design except not null for its syntactical restriction. User can add NOT NULL constraint forcibly during modification of a column but there should be no data in the table.
SYNTAX:
ALTER TABLE SYSEMP7
MODIFY (NAME VARCHAR2 (10) CONSTRAINT SYSEMP_NAME_NT NOT NULL);

Activating and deactivating a constraint using enable and disable command:
       Whenever a constraint is disable mode user can accept invalid data in it. So before enabling the constraint the user must delete all invalid data enter during disable time.

SYNTAXTO ENABLE/DISABLE A CONSTRAINT
ALTER TABLE <T_NAME>
ENABLE/DISABLE CONSTRAINT <C_NAME>;

WORKING WITH SYSTEM CATALOGUE OR DATA DICTIONARY OR METADATA:
       Whenever an object is created in ORACLE database, the information regarding that object will be kept in a system defined table called data dictionary.
       The advantage of data dictionary is it makes object searching faster. Inside a DD information are manipulate automatically by the system.
       ORACLE provides several diff. Types of objects for database management likewise it provides several diff. types of catalogue.
Such as
Table                     -->         user_tables
View                      -->        user_views
Synonym               -->         user_synonyms
Sequence              -->        user_sequences
Index                     -->        user_indexs
Type                      -->        user_types
Constraint            -->        user_constraints

SQL> DESC USER_TABLES;
SQL>SELECT CONSTRAINT_NAME, TABLE_NAMES, STATUS
FROM USER_CONSTRAINTS
WHERE USER_TABLES=T_NAME;
       It condition is omitted from the above statement it will display all constraints. STATUS is a column of user constraint responsible to display whether the constraints is enable or disabled.
SQL> SEARCH_CONDITION
FROM USER_CONSTRAINTS;
       SEARCH_CONDITION is a column of user constraint responsible to display check condition of a constraint.

IMPLICIT NAMING CONVENTION OF A CONSTRAINT:
       It no constraint name is given by a user by default system will provide the constraint name which looks like the following.
Sys_c005510
Sysc00511
Ex
Create table sysemp
(
Empnumber(4) primary key,
Name vachar2(95) not null,
Sal number (7, 2) check (sal between 2000 and 200000));

In the above ex constraint name is automatically given by the system.

NOTE:
       A constraint can be deleted in two diff. manner
-->  Explicitly using alter command
-->  Implicitly whenever a table is dropped

TABLE                   -->          USR_TABLE
VIEW                     -->          USER_VIEW
SYNOMYM            -->          USER_SYNOMYMS
SEQUENCE            -->          USER_SEQUENCE
INDEX                   -->          USER_INDEX
TYPE                     -->          USER_TYPE
CONSTRAINT       -->          USER_CONSTRAINT


1.      SELECT TABLE_NAME FROM USER_TABLE
In above ex table_name is a column where as user_abl is a table
2.      ESCUSER_TABLE
3.      SELECT CONSTRAINT_NAME, TABLE_NAME , STATUS
FROM USER_CONSTRAINS
WHERE TABLE_NAME=’SYSEMP’;
       If condition is omitted in the above statement it will display all constraint status is user_constraint is enable and disable.
4.      SELECT CONSTRAINT_NAME, SEARCH_CONDITION
FROM USER_CONSTRAINTS
WHERE TABLE_NAME=’SYSEMP’;

Search_condition is column of user constraint which is display check condition of constraint.