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