Oracle SQL Commands : Part 3 

In Oracle we can create our own procedure. A procedure is a collection of SQL statement that can be called by any valid object name and create a user-defined procedure we use the command create Procedure.

6. CREATE PROCEDURE Command

 In Oracle we can create our own procedure. A procedure is a collection of SQL statements that can be called by any valid object name. To create a user-defined procedure we use the command "CREATE PROCEDURE".
 
While creating a procedure or function we always state parameters that we can pass in three ways:

Parameters Referenced by Procedure Value overwritten by Procedure
      IN Yes NO
      OUT NO YES
      INOUT YES YES

Syntax

CREATE [OR REPLACE] PROCEDURE procedure_name

       [ (parameter [,parameter]) ]

IS

       Declaration section

BEGIN

       Execution section

EXCEPTION

       Exception_section

END;
 
Example: Assuming the following table:
 

Emp_ID Emp_Name Designation Salary
TCS008 Vivan Project Manager 1Lac
TCS030 Seema Tech Lead 80K


Example
 

CREATE OR REPLACE PROCEDURE

Employee_details

IS

Cursor Emp_Cur IS

SELECT Name, Designation, Salary FROM Employee

emp_rec emp_cur%rowtype;

BEGIN

 

FOR Emp_rec in Sales_Cur

LOOP

Dbms_output.put_line(Emp_cur.Name ||  '||emp_cur.Designation ||' '||Emp_cur.Salary);

END LOOP

END;
 
7. CREATE SCHEMA Command
 
A group of database objects is called a schema and is owned by a database user and must have the name as the database user. The "Create schema" command creates the multiple tables and a view.
 
Syntax

CREATE SCHEMA AUTHORIZATION Schema options; 
 
Options

The options are: 

  • CREATE TABLE
  • CREATE VIEW
  • GRANT

Example

CREATE SCHEMA AUTHORIZATION Student

 

 CREATE TABLE BOOK

  (

   Book_id NUMBER PRIMARY KEY,

   Book_Price NUMBER,

   Author Varchar2(10),

   Seller Varchar2(10),

   Publications Varchar2(30)

  )

 

 CREATE VIEW New_Book AS

 SELECT Book_id, Author FROM Book WHERE Book_Price = 500

 GRANT SELECT ON New_Book TO Scott;
 
8. CREATE FUNCTION Command
 
The CREATE FUNCTION statement creates a new function with a unique name in a specific schema if it has the schema name, otherwise the function is created in the current schema. The owner of the new function is the user that created the same function.
 
Syntax

CREATE [OR REPLACE] FUNCTION function_name [parameter]

 

RETURN return_datatypes;

IS

       Declaration_Section

BEGIN

       Execution_Section

RETURN Return_variable;

 

EXCEPTION

       Exception Section 

RETURN Return_variable;

END;
                           OR
CREATE [OR REPLACE] FUNCTION function_name

       [parameter_name [IN | OUT | INOUT] type [,….])]

RETURN return_datatype {IS | AS} 

BEGIN

      <function_body>

END   [function_name];


 Example
 
Assume the following table (STUDENT):
 

Stu_ID Name Age Stream
1 Rajat 18 Science
2 Jaya 20 Commerce
3 joseph 19 Arts
4 Maria 18 Science

 

CREATE OR REPLACE FUNCTION total_students

RETURN number IS

       total_number(2) :=0;

BEGIN

SELECT count(*) into total

FROM Students;

RETURN total;

END;
 
9. CREATE SEQUENCE Command
 
An Oracle sequence is an object used to generate incrementing and decrementing numbers. It is a data object from which multiple users may generate unique integers. Once a sequence is created its values can be accessed in a SQL statement.

Syntax
 

CREATE SEQUENCE <Sequence_name>

INCREMENT BY <integer>

START WITH <integer>

MAXVALUE <integer> / NOMAXVALUE

MINVALUE <integer> / NOMINVALUE

CYCLE/NOCYCLE

CACHE<#>/NOCACHE

ORDER/NOORDER
 
Example
 

CREATE SEQUENCE dba_seq

MINVALUE 1

MAXVALUE 40000

START WITH 1

INCREMENT BY 1

CACHE 100;
 
Note:
Here, a sequence called dba_seq is created.

10. CREATE SYNONYM Command

To create a duplicate name for any table, view, procedure, function, sequence, package and so on, CREATE SYNONYM is used. It provides both data independency and location transparency. The synonyms are of two types, Public and Private.

Syntax

CREATE [OR REPLACE] [PUBLIC] SYNONYM [Schema.]Synonym

FOR [Schema.] object [@dblink]
 
Example
 

CREATE PUBLIC SYNONYM buyers

FOR app.buyers;
 
11. ALTER TABLE Command
 
In an existing table if we want to do some changes like INSERT, DELETE or MODIFY columns then we only used the command ALTER TABLE.
 
Syntax

ALTER TABLE table_name
RENAME TO new_table_name;
 
Example
 
Assume the following table:

Buyer_Table
 

Buyer_ID ContactNo. Address City
366 3746736476 121, giri colony Lucknow

Employee Table

ALTER TABLE Buyer

RENAME TO Customer;


Vendor Table
 

Buyer_ID ContactNo. Address City
366 3746736476 121, giri colony Lucknow

Employee Table

To ADD Columns
 

Syn: ALTER TABLE table_name

     ADD column_name column-definition;

 

Eg.

    ALTER TABLE Buyer

    ADD Buyer _Name varchar2(50);
 

Buyer_ID Buyer_Name ContactNo. Address City
366 Vikas 3746736476 121, giri colony Lucknow

Employee Table

To ADD multiple columns:
 

Syn: ALTER TABLE table_name

     ADD (col1 column-definition,

          col2 column-definition,

          coln column-definition);

 

Eg.    ALTER TABLE supplier

     ADD (supplier_name varchar2(50),

          Country varchar2(45));
 

Buyer_ID Buyer_Name ContactNo. Address City Country
366 Vikas 3746736476 121, giri colony Lucknow INDIA

Employee Table


To DROP a column:
 

Syn: ALTER TABLE table_name

     DROP COLUMN column_name;

 

Eg.    ALTER TABLE Buyer 

DROP COLUMN Address;
 

Buyer_ID Buyer_Name ContactNo. City Country
366 Vikas 3746736476 Lucknow INDIA


Similarly, to DROP multiple columns:
 

Syn: ALTER TABLE table_name

     DROP COLUMN col1, col2, col3;

 

Eg.    ALTER TABLE Buyer 

     DROP COLUMN Address, Country;
 

Buyer_ID Buyer_Name ContactNo. City
366 Vikas 3746736476 Lucknow


To MODIFY a column name:
 

Syn: ALTER TABLE table_name

     MODIFY column_name column_type;

 

Eg.    ALTER TABLE Buyer

     MODIFY City varchar2(100) not null;
 
To MODIFY multiple column names:
 

Syn:  ALTER TABLE table_name

     MODIFY (col1 column_type,

     col2 column_type,

     coln column_type);

 

Eg.   ALTER TABLE Buyer

    MODIFY (City varchar2(50) not null,

    Country varchar2(50))


12. ALTER VIEW Command
 
This command recompiles an invalid view. It defines, modifies or drops the view constraints. Using ALTER VIEW will retain all the permission settings.

Syntax
 

ALTER VIEW [Schema.] VIEW [(Col1, Col2, Col3...Coln)]

  [WITH [ENCRYPTION] [SCHEMABINDING] [VIEW_METADATA] [,....Coln]]

  [WITH CHECK OPTION]

                             or

ALTER VIEW [schema.]view COMPILE;

ALTER VIEW [schema.]view ADD out_of_line_constraint;

ALTER VIEW [schema.]view MODIFY CONSTRAINT constraint {RELY | NORELY};

ALTER VIEW [schema.]view DROP CONSTRAINT constraint;

ALTER VIEW [schema.]view DROP PRIMARY KEY

ALTER VIEW [schema.]view UNIQUE (column [,column,…]);


Example
 
Assume the following view:
 

CREATE VIEW Emp_details AS

SELECT e.EMP_id, e.name, e.salary, e.depart_id, e.email, e.job_id, d.depart_name,  

       d.location_id

       FROM Employees e, department d

       WHERE e.depart_id = d.depart_id;

 

ALTER VIEW Emp_details

DROP CONSTRAINT Emp_details _read_only;

 Previous article: Oracle Commands: Part 2 
 Next Article: Oracle SQL Commands : Part 4