Oracle SQL Commands : Part 3

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]) ]

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

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;

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]

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