First Program – PL/SQL

A simple program in PL/SQL Above program executed and printed the output as "PL/SQL procedure successfully completed." but not printed "Hello Learner". We need to execute "SET SERVEROUTPUT ON" if we need to see the output of the code. Declaring and usage of variables in program Here, DECLARE statement is used to declare the variables … Continue reading First Program – PL/SQL


SQL Plus and SQL Developer

SQL Plus SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database Server or Client installation. It has a command-line user interface, a Windows Graphical User Interface (GUI) and the iSQL*Plus web-based user interface. SQL*Plus has its own commands and environment, and it provides access to the Oracle Database. It … Continue reading SQL Plus and SQL Developer

Advantages of PL/SQL

Advantages of PL/SQL Tight Integration with SQL PL/SQL is tightly integrated with SQL, the most widely used database manipulation language. PL/SQL fully supports SQL data types and lets use all SQL data manipulation, cursor control, and transaction control statements, and all SQL functions, operators. High Performance PL/SQL executes block of statements at a time, significantly … Continue reading Advantages of PL/SQL

Types of PL/SQL blocks

      A PL/SQL program cab be written in various types of blocks, they are Anonymous blocks Named blocks Nested Blocks Anonymous blocks:       Anonymous blocks have no name as a result they cannot be stored in database.They are declared at the point in an application where they are to be executed … Continue reading Types of PL/SQL blocks

Comments in PL/SQL

Comments let you include  text within your code to explain what the code does. PL/SQL compiler ignores comments. PL/SQL supports two types of comments. Single line comments (- -) Multi line comments (/*    ..........*/)

Database Normalisation

Database normalization is the process of organising the data in a relational database to minimise data redundancy. Normalization is a systematic approach of decomposing tables to eliminate data redundancy and undesirable characteristics like Insertion, Update and Deletion Anomalies.

System Control Statement

      ALTER SYSTEM statement dynamically alter the database instance and user must have ALTER SYSTEM system privilege. Examples : Restricting sessions      While performing application maintenance, generally all sessions need to close and restricts new session. Use below statement to restrict sessions. SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;       You … Continue reading System Control Statement

Session Control Statements

      Session control statements are dynamically manages the properties of a user session and don't implicitly commit the current transaction. Note : PL/SQL doesn't support session control statements. Following are the Session control statements. ALTER SESSION : This statement is used  set or modify  the conditions or parameters that affect your connection to … Continue reading Session Control Statements

Data Control Language (DCL) Statements

      DCL statements are used to control access to data stored in the database. GRANT statement is used to allow specified users to perform specified task and REVOKE statement is used to cancel the permission which are previously granted. GRANT : It is used provide the access or privileges on database objects to … Continue reading Data Control Language (DCL) Statements

Transaction Control Statements

      Transaction control statements manage changes made by DML statements i.e apply the changes permanently or restore to original. Below are Transaction Control Statements. COMMIT : Use commit statement at end of your transactions to apply the changes permanently. SQL> SELECT * FROM STUDENT; SI_NO FULL_NAME BRAN S PERCENTAGE G ---------- ------------------------------ ---- - ---------- … Continue reading Transaction Control Statements

Data Manipulation Language (DML) Statements

      DML statements are used for managing data within schema objects like retrieving, inserting, update and delete etc. INSERT : Insert statement is used to insert the data into a table. Syntax : insert into table_name(column1,..,columnn) values(value1,..,value2);  insert into table_name values(value1,value2,...,value4); We can use #1 syntax to insert the data into particular fields … Continue reading Data Manipulation Language (DML) Statements

Data Definition Language (DDL) Statements

CREATE Statement : Create statement used to create data base objects like tables, views, indexes, triggers and synonyms etc.. CREATE TABLE : Syntax is create table table_name(column1 data type,column2 data type,.......,cloumnn data type); SQL> CREATE TABLE PROFILE (ID INT,FNAME VARCHAR2(50),LNAME VARCHAR2(50),FULL_NAME VARCHAR2(100), EMAIL VARCHAR2(100),MOBILE_NO NUMBER(10),CREATED_DATE DATE,CREATEDBY VARCHAR2(100)); Table created. SQL> SQL> DESC PROFILE; Name Null? … Continue reading Data Definition Language (DDL) Statements