What is PL/SQL

Basic Structure of PL/SQL

PL/SQL stands for Procedural Language/SQL. PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL. The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks, which can be nested within each other. Typically, each block performs a logical action in he program. A block has the following structure:

DECLARE

/* Declarative section: variables, types, and local subprograms. */

BEGIN

/* Executable section: procedural and SQL statements go here. */

/* This is the only section of the block that is required. */

EXCEPTION

/* Exception handling section: error handling statements go here. */

END;

Only the executable section is required. The other sections are optional. The only SQL statements allowed in a PL/SQL program are SELECT, INSERT, UPDATE, DELETE and several other data manipulation statements plus some transaction control. However, the SELECT statement has a special form in which a single tuple is placed in variables; more on this later. Data definition statements like CREATE, DROP, or ALTER are not allowed. The executable section also contains constructs such as assignments, branches, loops, procedure calls, and triggers, which are all described below (except triggers). PL/SQL is not case sensitive. C style comments (/* ... */) may be used.

To execute a PL/SQL program, we must follow the program text itself by

* A line with a single dot ("."), and then
* A line with run;

As with Oracle SQL programs, we can invoke a PL/SQL program either by typing in sqlplus.

Read More

even or odd Numbers

declare
  num number(5);
begin
  num:=#
  if num mod 2 =0 then
    dbms_output.put_line('Accepted Number '||num||' is even');
  else
    dbms_output.put_line('Accepted Number '||num||' is odd');
  end if;
end;

Read More

Write PL/SQL Block Which Will Find Maximum Number Between Three Numbers?

Q. Write PL/SQL Block Which Will Find Maximum Number Between Three Numbers?

DECLARE
a NUMBER:=&Input_First_no.;
b NUMBER:=&Input_Second_no.;
c NUMBER:=&Input_Third_no.;
BEGIN
IF (a>b) THEN
IF(a>c)THEN
DBMS_OUTPUT.PUT_LINE(a||' is greatest !');
ELSE
DBMS_OUTPUT.PUT_LINE(c||' is greatest !');
END IF;
ELSIF(b>c)THEN
DBMS_OUTPUT.PUT_LINE(b||' is greatest !');
ELSIF(a=b)AND(b=c) THEN
DBMS_OUTPUT.PUT_LINE(a||' '||b||' '||c||' All Numbers Are Same !');
ELSE
DBMS_OUTPUT.PUT_LINE(c||' is greatest !');
END IF;
END;
/
Enter value for input_first_no: 0.55

Enter value for input_second_no: 0.56

Enter value for input_third_no: 0.54

Read More

nested Procedure

In general, something that is nested is fully contained within something else of the same kind.

In programming, nested describes code that performs a particular function and that is contained within code that performs a broader function.

One well-known example is the procedure known as the nested do-loop .A Procedure  within a  Procedure  is a nested  Procedure.

DECLARE
      PROCEDURE P2;   --  forward declaration
      PROCEDURE P3;    --  forward declaration
      PROCEDURE P1 IS
      BEGIN
         dbms_output.put_line('From procedure p1');
         p2;
      END P1;
      PROCEDURE P2 IS
      BEGIN
         dbms_output.put_line('From procedure p2');
         p3;
      END P2;
      PROCEDURE P3 IS
      BEGIN
         dbms_output.put_line('From procedure p3');
      END P3;
BEGIN
     p1;
END;

Output:-
From procedure p1
From procedure p2
From procedure p3

Read More

Even & odd numbers

--Write a PL_SQL block to insert numbers into temp table  Using For Loop
Example :-
Insert the numbers 1..10 excludining 6

BEGIN
FOR i in 1..10 LOOP
  IF i = 6  THEN
    null;
  ELSE
    INSERT INTO  temp(results)
    VALUES (i);
 END IF;
END LOOP;
 COMMIT;
END;

Execute a  PLSQL statement to verify that your PLSQL BLock Worked.

Select * from temp;

Read More

Concatenate Function

Concatenate Function

Sometimes it is necessary to combine together (concatenate) the results from several different fields. Each database provides a way to do this:

CONCAT(str1, str2, str3, ...): Concatenate str1, str2, str3, and any other strings together. Please note the Oracle CONCAT() function only allows two arguments -- only two strings can be

put together at a time using this function. However, it is possible to concatenate more than two strings at a time in Oracle using '||'.

Let's look at some examples. Assume we have the following table:
Employees

SQL> desc employees
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

create or replace function mycat(f varchar2,l varchar2)
return varchar2
is
begin
return f||'  '||l;
end;
/
Function created.

SQL> select mycat(first_name,last_name)
  2  from employees
  3  where salary>10000;


Output:-
MYCAT(FIRST_NAME,LAST_NAME)
----------------------------------
Michael  Hartstein
Shelley  Higgins
Neena  Kochhar
Lex  De Haan
Nancy  Greenberg
Den  Raphaely
John  Russell
Karen  Partners
Alberto  Errazuriz
Gerald  Cambrault
Eleni  Zlotkey

Read More

Raise Application_Error

What is Raise Application_Error

The procedure RAISE_APPLICATION_ERROR lets user to issue user-defined ORA- error messages from stored subprograms. That way, user can report errors to your
application and avoid returning unhandled exceptions.

the syntax is
raise_application_error(error_number, message[, {TRUE | FALSE}]);

DECLARE
num_of_employees NUMBER;
BEGIN
  SELECT COUNT (*)
  INTO num_of_employees
  FROM employee;

  IF num_of_employees < 100
    THEN
/* Issue your own error code (ORA-20509) with your own error message. */
    raise_application_error (- 20509, 'Expecting at least 100 employees');
  ELSE
    NULL; -- Do the rest of the processing (for the non-error case).
  END IF;
END;

Read More

Mutating Table Errors ?

What is Mutating Table Errors ?

A mutating table error occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement).

sql>CREATE OR REPLACE FUNCTION rec_call(sal NUMBER)
   RETURN NUMBER IS
BEGIN
  INSERT INTO employees(employee_id, last_name,  email, hire_date, job_id, salary)
  VALUES(1, 'Sharma', 'sharma@gmail.com',
         SYSDATE, 'SA_REP', sal);
  RETURN (sal + 100);
END;

Function created.

sql>UPDATE employees
  SET salary = Rec_call(2000)
WHERE employee_id = 197



Note:- The Rec_call function  contains an INSERT statement that inserts a new record into the EMPLOYEES table and returns the input salary value incremented by 100. This function is invoked in the UPDATE statement that modifies the salary of employee 197 to the amount returned from the function.The UPDATE statement fails with an error indicating that the table is mutating (that is, changes are already in progress in the same table). In the following example, the query_call_sql function queries the SALARY column of the EMPLOYEES table

Read More

Implicit Cursor & Explicit Cursor

Implicit Cursor are declared and used by the oracle internally oracle automatically opens  and closes the cursor .Where as the explicit cursors are declared and used by the user to .process multi row select statement.

Implicit CURSOR Example
--CREATE TABLE employees (empid  NUMBER,empname varchar2(256));

CREATE OR REPLACE PROCEDURE test_implict_cursor
AS
BEGIN
  INSERT INTO employees
  VALUES ('1', 'GEORGE'); --INSERT
 
  INSERT INTO employees
  VALUES ('2', 'THOMOS'); --INSERT
 
  INSERT INTO employees
  VALUES ('3', 'DAVID'); --INSERT
 
  FOR items IN (SELECT empid, empname
                FROM employees)
    LOOP
    DBMS_OUTPUT.put_line ( 'empid= '
                           || items.empid
                           || ', empname = '
                           || items.empname
                           );
  END LOOP;
END;
/

SHOW ERROR;
SET SERVEROUTPUT ON;

EXEC TEST_IMPLICT_CURSOR;
Output:

empid = 1, empname = GEORGE
empid = 2, empname = THOMOS
empid = 3, empname = DAVID

Explicit CURSOR example 1

--CREATE TABLE employees (empid  NUMBER,empname varchar2(256));

CREATE OR REPLACE PROCEDURE test_explict_cursor
AS
CURSOR cursor1
IS
SELECT empid, empname
FROM employees;
BEGIN
  INSERT INTO employees
  VALUES ('1', 'GEORGE'); --INSERT
 
  INSERT INTO employees
  VALUES ('2', 'THOMOS'); --INSERT
 
  INSERT INTO employees
  VALUES ('3', 'DAVID'); --INSERT
 
  FOR items IN cursor1
    LOOP
    DBMS_OUTPUT.put_line ( 'empid= '
                           || items.empid
                           || ', empname = '
                           || items.empname
                           );
  END LOOP;
END;
/

SHOW ERROR;
SET SERVEROUTPUT ON;

EXEC TEST_EXPLICT_CURSOR;

Output:

empid = 1, empname = GEORGE
empid = 2, empname = THOMOS
empid = 3, empname = DAVID



Explicit CURSOR example 2
CREATE TABLE employees (empid NUMBER, empname VARCHAR2(256));
/
INSERT INTO employees
VALUES ('1', 'GEORGE')
/
INSERT INTO employees
VALUES ('2', 'THOMOS')
/
INSERT INTO employees
VALUES ('3', 'DAVID')
/

CREATE OR REPLACE PROCEDURE test_explict_cursor
AS
CURSOR cursor1
IS
SELECT empid, empname
FROM employees;

empid employees.empid%TYPE;
empname employees.empname%TYPE;
employees_details employees%ROWTYPE;
BEGIN
  OPEN cursor1;
 
  LOOP
    FETCH cursor1
    INTO employees_details; --
   
    DBMS_OUTPUT.put_line ( 'empid= '
                           || employees_details.empid
                           || ' empname = '
                           || employees_details.empname
                           );
    EXIT WHEN cursor1%NOTFOUND;
-- process data record
  END LOOP;
END;
/

SHOW ERROR;
SET SERVEROUTPUT ON;

EXEC TEST_EXPLICT_CURSOR;

Read More

WRITE A PROGRAM TO FIND SUM OF NUMBERS FROM 1 TO 100

-- WRITE A PROGRAM TO FIND SUM OF NUMBERS FROM 1 TO 100

DECLARE
 N NUMBER(3):=1;
 S NUMBER(4):=0;
 BEGIN
 WHILE N<=10
 LOOP
 S := S+N;
 N :=N+1;
 END LOOP;
 DBMS_OUTPUT.PUT_LINE('THE SUM IS '||S);
 END;

Read More

Create View

SQL Views

A VIEW is a virtual table, through which a selective portion of the data from one
 or more tables can be seen. Views do not contain data of their own. They are
used to restrict access to the database or to hide data complexity. A view is stored
as a SELECT statement in the database. DML operations on a view like INSERT, UPDATE,
DELETE affects the data in the original table upon which the view is based.
view_name is the name of the VIEW.

 The SELECT statement is used to define the columns and rows
that you want to display in the view.

For Example: to create a view on the product table the sql query would be like

CREATE VIEW Emp_view
AS
SELECT Employee_id, First_name
FROM Employees;

Read More

Alter Table

SQL ALTER TABLE Statement

The SQL ALTER TABLE command is used to modify the definition (structure) of a table by modifying the definition of its columns. The ALTER command is used to perform the following functions.

1) Add, drop, modify table columns
2) Add and drop constraints
3) Enable and Disable constraints

Syntax to add a column

ALTER TABLE table_name ADD column_name datatype;

For Example: To add a column "experience" to the employee table, the query would be like

ALTER TABLE employees  ADD experience number(3);

Syntax to drop a column

ALTER TABLE table_name DROP column_name;

For Example: To drop the column "location" from the employees  table,
the query would be like


ALTER TABLE employees DROP location;

Syntax to modify a column

ALTER TABLE table_name MODIFY column_name datatype;

For Example: To modify the column salary in the employees  table, the query would be like

ALTER TABLE employees MODIFY salary number(15,2);


SQL RENAME Command

The SQL RENAME command is used to change the name of the table or a database object.

If you change the object's name any reference to the old name will be affected. You have to manually change the old name to the new name in every reference.
Syntax to rename a table

RENAME old_table_name To new_table_name;

For Example: To change the name of the table employees to my_employee, the query would be like

RENAME employees  TO my_emloyee;

Read More

WRITE A PROGRAM TO PRINT EVEN NUMBERS FROM 1 TO 100 DECLARE

Pl -sql : Programming Concept.

--WRITE A PROGRAM TO PRINT EVEN NUMBERS FROM 1 TO 100
 DECLARE
 N NUMBER(3) :=0;
 BEGIN
 WHILE N<=100
 LOOP
 N :=N+2;
 DBMS_OUTPUT.PUT_LINE(N);
 END LOOP;
 END;

Read More

Hr-Schema

Read More

Distinct Clause

The SQL SELECT DISTINCT Statement
In a table, some of the columns may contain duplicate values. This is not a problem, however,
 sometimes you will want to list only the different (distinct) values in a table.

The DISTINCT keyword can be used to return only distinct (different) values.

SQL SELECT DISTINCT Syntax
SELECT DISTINCT column_name(s)
FROM table_name

SELECT DISTINCT City FROM emp_info;


SQL WHERE Clause

The WHERE clause is used to extract only those records that fulfill a specified criterion

SELECT * FROM Emp_info
WHERE City='Mumbai'
Use Quotes Around Text Fields
SQL uses single quotes around text values (most database systems will also accept double quotes).

However, numeric values should not be enclosed in quotes.

Read More

Where

SQL HAVING Clause

Having clause is used to filter data based on the group functions. This is similar to WHERE condition but is used with group

functions. Group functions cannot be used in WHERE Clause but can be used in HAVING clause.

For Example: If you want to select the department that has total salary paid for its employees more than 25000, the sql

query would be like;

SELECT departmet_id, SUM (salary)
FROM employees
GROUP BY departmets
HAVING SUM(salary) > 25000

When WHERE, GROUP BY and HAVING clauses are used together in a SELECT statement,
the WHERE clause is processed first, then the rows that are returned after the WHERE clause is executed are grouped

based on the GROUP BY clause. Finally, any conditions on the group functions in the HAVING clause are applied to the

grouped rows before the final output is displayed.
SQL ORDER BY

The ORDER BY clause is used in a SELECT statement to sort results either in ascending or descending order. Oracle sorts

query results in ascending order by default.

For Example: If you want to sort the employee table by salary of the employee, the sql query would be.

SELECT first_name, salary FROM employee ORDER BY salary;



The query first sorts the result according to name and then displays it.

You can also use more than one column in the ORDER BY clause.
he query first sorts the result according to name and then displays it.

You can also use more than one column in the ORDER BY clause.

Read More

Sql-Fundamental

SQL Tutorial



SQL (Structured Query Language) is used to modify and access data or information from a storage area called database.

This beginner sql tutorial website teaches you the basics of SQL and how to write SQL queries. I will be sharing my

knowledge on SQL and help you learn SQL better. The sql concepts discussed in this tutorial can be applied to most of

database systems. The syntax used to explain the concepts is similar to the one used in Oracle database.

Some Of the most important Sql Commands are as follwes:

SQL SELECT Statement

The most commonly used SQL command is SELECT statement. The SQL SELECT statement is used to query or retrieve data

from a table in the database. A query may retrieve information from specified columns or from all of the columns in the

table. To create a simple SQL SELECT Statement, you must specify the column(s) name and the table name. The whole

query is called SQL SELECT Statement.

Note: SQL is not case sensitive. SELECT is the same as select.

Syntax of SQL SELECT Statement:

SELECT column_list FROM table-name
[WHERE Clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause];

Table-name is the name of the table from which the information is retrieved.
    column_list includes one or more columns from which data is retrieved.

The SQL SELECT Statement

The SELECT statement is used to select data from a database.

The result is stored in a result table, called the result-set.

SELECT * Example

Now we want to select all the columns from the "Employees" table.
 
SQL> select * from employees;

Now we want to select the content of the columns named

"Employee_id", "LastName" and "FirstName" from the table above.

SQL> select employee_id,first_name,last_name from employees;

                                                                                                               Next

Read More