dbms manual

Overview of SQL DDL, DML and DCL Commands.

DDL is Data Definition Language statements.

Some examples: CREATE - to create objects in the database

ALTER - alters the structure of the database

DROP - delete objects from the database

TRUNCATE - remove all records from a table, including all spaces allocated for the records

removed

COMMENT - add comments to the data dictionary

GRANT - gives user's access privileges to database

REVOKE - withdraw access privileges given with the

GRANT command

DML is Data Manipulation Language statements.

Some examples:

SELECT - retrieve data from the a database

INSERT - insert data into a table

UPDATE - updates existing data within a table

DELETE - deletes all records from a table, the space for the records remain

CALL - call a PL/SQL or Java subprogram

EXPLAIN PLAN - explain access path to data

LOCK TABLE - control concurrency

DCL is Data Control Language statements. Some examples:

COMMIT - save work done

SAVEPOINT - identify a point in a transaction to which you can later roll back

ROLLBACK - restore database to original since the last

COMMITSET TRANSACTION - Change transaction options like what rollback segment to use

Basic SQL DDL Commands.

To practice basic SQL DDL Commands such as CREATE, DROP, etc.

1. SQL - CREATE TABLE

Syntax: CREATE TABLE tablename (column_name data_ type constraints, …)Example:

INPUT:

SQL>CREATE TABLE Emp ( EmpNo short CONSTRAINT PKey PRIMARY KEY,EName

VarChar(15), Job Char(10) CONSTRAINT Unik1 UNIQUE,Mgr short CONSTRAINT FKey1

REFERENCES EMP (EmpNo),Hiredate Date, DeptNo short CONSTRAINT FKey2

REFERENCES DEPT(DeptNo));

RESULT:

Table created.

SQL>Create table prog20 (pname varchar2(20) not null), doj date not null,dob date not null,sex

varchar(1) not null, prof1 varchar(20),prof2 varchar(20),salary number(7,2) not null);

RESULT:

Table created.

2. SQL - ALTER TABLE INPUT:

SQL>ALTER TABLE EMP ADD CONSTRAINT Pkey1 PRIMARY KEY (EmpNo);

RESULT:

Table Altered.

Similarly, ALTER TABLE EMP DROP CONSTRAINT Pkey1;

3. SQL - DROP TABLE

Deletes table structure

Cannot be recovered

Use with caution

INPUT:

SQL>DROP TABLE EMP;

Here EMP is table name

RESULT:

Table Dropped.

4. TRUNCATE

TRUNCATE TABLE <TABLE NAME>;

Basic SQL DML Commands.

To practice basic SQL DML Commands such as INSERT, DELETE, etc.

1. SQL - INSERT INTO

Syntax: INSERT INTO tablename VALUES (value list)

Single-row insert

INSERT INTO S VALUES (‘S3’,’SUP3’,’BLORE’,10)

Inserting one row, many columns at a time

INSERT INTO S (SNO, SNAME) VALUES (‘S1’, ‘Smith’);S1’ Smith’

Inserting many rows, all/some columns at a time.

INSERT INTO NEW_SUPPLIER (SNO, SNAME)

SELECT SNO, SNAME FROM S

WHERE CITY IN (‘BLORE’,’MADRAS’)

Other examples:

INPUT:

SQL>Insert into prog values (‘kkk’,’05-may-56’);

RESULT:

1 row created.

INPUT:

SQL>Insert into prog20 values(‘Hema’,’25-sept-01’28-jan-85’,’f’,’c’,’c++’,’25000’);

RESULT:

1 row created.

INPUT:

SQL>Insert into prog values(‘&pname’,’&doj’);

SQL> Insert into prog values('&pname','&doj');Enter value for pname: raviEnter value for doj:

15-june-81

RESULT:

old 1: Insert into prog values('&pname','&doj')new 1:

Insert into prog values('ravi','15-june-81')1 row created.

2. SQL - UPDATE

Syntax: UPDATE tablename SET column_name =value [ WHERE condition]

Examples:

UPDATE S SET CITY = ‘KANPUR’ WHERE SNO=‘S1’UPDATE EMP SET SAL = 1.10 * SALSQL>

update emp set sal=20000 where empno=7369;

1 row updated

3. SQL - DELETE FROM

Syntax: DELETE FROM tablename WHERE condition

Examples:

DELETE FROM SP WHERE PNO= ‘P1’DELETE FROM SP

INPUT:

SQL>Delete from emp where empno=7369;

RESULT:

1 row deleted.

Basic SQL DCL Commands.

To practice basic SQL DCL Commands such as COMMIT, ROLLBACK etc.

1. COMMIT

Save changes (transactional).Syntax:

COMMIT [WORK] [COMMENT 'comment_text']

COMMIT [WORK] [FORCE ' force_text ' [,int] ]

FORCE - will manually commit an in-doubt distributed transaction

If a network or machine failure prevents a distributed transaction from committing properly, Oracle will store any

commit comment in the data dictionary along with the transaction ID.

INPUT:

SQL>commit;

RESULT:

Commit complete.

2. ROLLBACK

Undo work done (transactional).

Syntax:

ROLLBACK [WORK] [TO [SAVEPOINT]'savepoint_text_identifier '];

ROLLBACK [WORK] [FORCE 'force_text'];

FORCE - will manually rollback an in-doubt distributed transaction

INPUT:

SQL>rollback;

RESULT:

Rollback complete.

3. SAVEPOINT

Save changes to a point (transactional).Syntax:

SAVEPOINT text_identifier

Example

:UPDATE employees

SET salary = 95000

WHERE last_name = 'Smith';

SAVEPOINT justsmith;

UPDATE employees

SET salary = 1000000;

SAVEPOINT everyone;

SELECT SUM(salary) FROM employees;

ROLLBACK TO SAVEPOINT justsmith;

COMMIT;

2. Get the description DEPT table.

SQL>desc dept;

RESULT:

Name Null? Type

--------------------------------- --------------------- ---------------------------

DEPTNO NOT NULL NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

3.List all employee details.

SQL>select * from emp;

RESULT:

4.List all employee names and their salaries, whose salary lies between1500/- and 3500/-

both inclusive.

INPUT

SQL>select ename from emp where sal between 1500 and 3500;

RESULT

9 rows selected.

5. List all employee names and their and their manager whose manager is7902 or 7566 0r 7789.

INPUT

SQL>select ename from emp where mgr in(7602,7566,7789);

RESULT

ENAME ------- SCOTT FORD

6. List all employees which starts with either J or T.

INPUT

SQL>select ename from emp where ename like ‘J%’ or ename like ‘T%’;

RESULT:

ENAME --------- JONES

TURNER

JAMES

7. List all employee names and jobs, whose job title includes M or P.

INPUT

SQL>select ename,job from emp where job like ‘M%’ or job like ‘P%’;

RESULT:

8. List all jobs available in employee table.

INPUT

SQL>select distinct job from emp;

RESULT:

7 rows selected.

9. List all employees who belong to the department 10 or 20.

INPUT

SQL>select ename from emp where deptno in (10,20);

Writing Queries using GROUP BY and other clauses.

To write queries using clauses such as GROUP BY, ORDER BY, etc. and retrievinginformation by joining

tables.

Source tables: emp, dept, programmer, software, study.

Order by : The order by clause is used to display the results in sorted order.

Group by: The attribute or attributes given in the clauses are used to form groups.

Tuples with the same value on all attributes in the group by clause are placed in one group.

Having: SQL applies predicates (conditions) in the having clause after groups have

been formed, so aggregate function be used.

1. Display total salary spent for each job category

INPUTSQL>select job,sum (sal) from emp group by job;

CONSTRAINTS

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table.

Primary keys must contain unique values.

A primary key column cannot contain NULL values.

Each table should have a primary key, and each table can have only ONE primary key.

SQL PRIMARY KEY Constraint on CREATE TABLE

The following SQL creates a PRIMARY KEY on the "P_Id" column when the "Persons" table is created

CREATE TABLE Persons(P_Id int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255),CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName))

Note: In the example above there is only ONE PRIMARY KEY (pk_PersonID). However, the value of the pk_PersonID is made up of two columns (P_Id and LastName).

SQL PRIMARY KEY Constraint on ALTER TABLE

To create a PRIMARY KEY constraint on the "P_Id" column when the table is already created, use the following SQL:

ALTER TABLE PersonsADD PRIMARY KEY (P_Id)

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

ALTER TABLE PersonsADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created).

To DROP a PRIMARY KEY Constraint

To drop a PRIMARY KEY constraint, use the following SQL:

ALTER TABLE PersonsDROP CONSTRAINT pk_PersonID

1. Table Creations

SQL> create table customer(cname char(10) constraint c1 primary key,cstreet char(15),ccity char(10));

Table created.

SQL> desc customer;

Name Null? Type

----------------------------------------- ------------ ----------------------------

CNAME NOT NULL CHAR(10)

CSTREET CHAR(15)

CCITY CHAR(10)

SQL FOREIGN KEY Constraint

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Let's illustrate the foreign key with an example. Look at the following two tables:

The "Persons" table:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

The "Orders" table:

O_Id OrderNo P_Id

1 77895 3

2 44678 3

3 22456 2

4 24562 1

Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table.

The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.

The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

The FOREIGN KEY constraint also prevents that invalid data form being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

SQL FOREIGN KEY Constraint on ALTER TABLE

To create a FOREIGN KEY constraint on the "P_Id" column when the "Orders" table is already created, use the following SQL:

SQL>ALTER TABLE Orders ADD FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)

To DROP a FOREIGN KEY Constraint

To drop a FOREIGN KEY constraint, use the following SQL:

SQL>ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders

SQL CHECK Constraint

The CHECK constraint is used to limit the value range that can be placed in a column.

If you define a CHECK constraint on a single column it allows only certain values for this column.

If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

SQL CHECK Constraint on CREATE TABLE

The following SQL creates a CHECK constraint on the "P_Id" column when the "Persons" table is created. The CHECK constraint specifies that the column "P_Id" must only include integers greater than 0.

CREATE TABLE Persons(P_Id int NOT NULL CHECK (P_Id>0),LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255))

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

SQL CHECK Constraint on ALTER TABLE

To create a CHECK constraint on the "P_Id" column when the table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

SQL>ALTER TABLE Persons ADD CHECK (P_Id>0)

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

SQL>ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

To DROP a CHECK Constraint

To drop a CHECK constraint, use the following SQL:

SQL Server / Oracle / MS Access:

SQL>ALTER TABLE Persons DROP CONSTRAINT chk_Person

SQL>ALTER TABLE Persons NDROP CHECK chk_Person

INDEX

The CREATE INDEX statement is used to create indexes in tables.

Indexes allow the database application to find data fast; without reading the whole table.

Indexes

An index can be created in a table to find data more quickly and efficiently.

The users cannot see the indexes, they are just used to speed up searches/queries.

Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against.

SQL CREATE INDEX SyntaxCreates an index on a table. Duplicate values are allowed:

SQL>CREATE INDEX index_name ON table_name (column_name)

SQL CREATE UNIQUE INDEX SyntaxCreates a unique index on a table. Duplicate values are not allowed:

SQL>CREATE UNIQUE INDEX index_name ON table_name (column_name)

Note: The syntax for creating indexes varies amongst different databases. Therefore: Check the syntax for creating indexes in your database.

CREATE INDEX Example

The SQL statement below creates an index named "PIndex" on the "LastName" column in the "Persons" table:

SQL>CREATE INDEX PIndex ON Persons (LastName)

If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:

SQL>CREATE INDEX PIndex ON Persons (LastName, FirstName)

Writing Nested Queries.

To write queries using Set operations and to write nested queries.

CREATING TABLES:

SQL> create table acc1(accno number(10), br_name varchar2(10), balance number(10));

Table created.

SQL> desc acc1;

Name Null? Type

--------------------- ---------- ----------------------

ACCNO NUMBER(10)

BR_NAME VARCHAR2(10)

BALANCE NUMBER(10)

SQL> create table acc12(accno number(10), br_name varchar2(10), balance number(10));

Table created.

SQL> desc acc12;

Name Null? Type

--------------------- ---------- ----------------------

ACCNO NUMBER(10)

BR_NAME VARCHAR2(10)

BALANCE NUMBER(10)

TO CREATE A TABLE USING NESTED QUERY:

SQL> create table acc72 as select * from acc1;

Table created.

SQL> select * from acc72;

ACCNO BR_NAME BALANCE

---------- ---------- ----------

1231 Mumbai 50000

1232 Delhi 70000

1233 Jaipur 80000

1234 Hyderabad 45000

TO INSERT TUPLE USING NESTED QUERY:

SQL> insert into acc72 select * from acc12;

2 rows created.

TO DELETE A TUPLE USING NESTED QUEYR:

SQL> delete from acc72 where accno= (select accno from acc1 where balance=45000);

1 row deleted.

TO UPDATE A TUPLE USING NESTED QUERY:

SQL> update acc72 set balance=75000 where br_name= (select br_name from acc1 where accno =1231);

1 row updated.

TO SELECT A TUPLE USING NESTED QUERY:

SQL> select * from acc72 where accno= 2 (select accno from acc12 where balance=34000);

Triggers:

A trigger is a statement that the system executes automatically.Triggers are similar to stored procedures. A trigger stored in the database can include SQL and PL/SQL or Java statements to run as a unit and can invoke stored procedures. However, procedures and triggers differ in the way that they are invoked. A procedure is explicitly run by a user, application, or trigger. Triggers are implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used.

A database trigger has three parts

1. A triggering event 2. A trigger constraint (Optional) 3. Trigger action

A triggering event can be an insert, update, or delete statement or a instance shutdown or startup etc. The trigger fires automatically when any of these events occur A trigger constraint specifies a Boolean expression that must be true for the trigger to fire. This condition is specified using the WHEN clause. The trigger action is a procedure that contains the code to be executed when the trigger fires.

Types of Triggers

The following are the different types of triggers.

Row triggers and statement triggers

A Row trigger fires once for each row affected. It uses FOR EACH ROW clause. They are useful if trigger action depends on number of rows affected.

Statement Trigger fires once, irrespective of number of rows affected in the table. Statement triggers are useful when triggers action does not depend on

Before and afterTriggers

While defining the trigger we can specify whether to perform the trigger action (i.e. execute trigger body) before or after the triggering statement. BEFORE and AFTER triggers fired by DML statements can only be defined on tables.

BEFORE triggers The trigger action here is run before the trigger statement.

AFTER triggers The trigger action here is run after the trigger statement.

INSTEAD of Triggers provide a way of modifying views that can not be modified directly using DML statements.

LOGON triggers fires after successful logon by the user and LOGOFF trigger fires at the start of user logoff.

Trigger uses and limitations:

A trigger cannot include COMMIT, SAVEPOINT and ROLLBACK. We can use only one trigger of a particular type . A table can have any number of triggers. We use correlation names :new and :old can be used to refer to data in command line and

data in table respectively.

Triggers on DDL statements

DDL trigger are of the following types

BEFORE CREATE OR AFTER CREATE trigger is fired when a schema object is created. BEFORE OR AFTER ALTER trigger is fired when a schema object is altered. BEFORE OR AFTER DROP trigger is fired when a schema object is dropped.

A trigger can be enabled means can be made to run or it can disabled means it cannot run. A trigger is automatically enabled when it is created. We need re-enable trigger for using it if it is disabled. To enable or disable a trigger using ALTER TRIGGER command, you must be owner of the trigger or should have ALTER ANY TRIGGER privilege. To create a trigger you must

have CREATE TRIGGER privilege, which is given to as part of RESOURCE privilege at the time of user creation.

Trigger Execution

A trigger is in either of two distinct modes:

Trigger Mode Definition

Enabled An enabled trigger runs its trigger action if a triggering statement is issued and the trigger restriction (if any) evaluates to true.

Disabled A disabled trigger does not run its trigger action, even if a triggering statement is issued and the trigger restriction (if any) would evaluate to true.

TABLES TO BE CREATED:

SQL> create table ledger(actiondate date,item varchar(25),quantity number(3),amount number(5),upperi

tem varchar(25));

Table created.

SQL> create table led_audit(actiondate date,item varchar(25),quantity number(3),amount number(5));

Table created.

SQL> create table account(acc_no varchar(20),branch_name varchar(20),balance number(5));

Table created.

SQL> create table loan(cusname varchar(20),branchname varchar(20),amount number(4));

Table created.

SQL> create table cc(cnt number(2));

Table created.

TABLES DESCRIPTION:

SQL> desc ledger;

Name Null? Type

------------------------------- -------- ----

ACTIONDATE DATE

ITEM VARCHAR2(25)

QUANTITY NUMBER(3)

AMOUNT NUMBER(5)

UPPERITEM VARCHAR2(25)

SQL> desc led_audit;

Name Null? Type

------------------------------- -------- ----

ACTIONDATE DATE

ITEM VARCHAR2(25)

QUANTITY NUMBER(3)

AMOUNT NUMBER(5)

SQL> desc account;

Name Null? Type

------------------------------- -------- ----

ACC_NO VARCHAR2(20)

BRANCH_NAME VARCHAR2(20)

BALANCE NUMBER(5)

SQL> desc loan;

Name Null? Type

------------------------------- -------- ----

CUSNAME VARCHAR2(20)

BRANCHNAME VARCHAR2(20)

AMOUNT NUMBER(4)

SQL> desc cc;

Name Null? Type

------------------------------- -------- ----

CNT NUMBER(2)

SQL> insert into ledger values('01-aug-05','keyboard',1,200,'keyboard');

1 row created.

SQL> insert into led_audit values('01-aug-05','keyboard',1,20000);

1 row created.

TRIGGER 1:

SQL> create or replace trigger led_f before update on ledger

2 for each row

3 when(new.amount/old.amount>1.0)

4 begin

5 insert into led_audit values(:old.actiondate,:old.item,:old.quantity,:old.amount);

6 end;

7 /

Trigger created.

SQL> update ledger set amount=20000;

1 row updated.

TRIGGER 2:

SQL> create or replace trigger led_t before insert or update of item on ledger

2 for each row

3 begin

4 :new.upperitem:=upper(:new.item);

5 end;

6 /

Trigger created.

SQL> alter trigger led_t disable;

Trigger altered.

SQL> update ledger set item='yyy';

1 row updated.

JOIN VIEW:

SQL> create table cust(name varchar2(10),street varchar2(10),age number(10));

Table created.

SQL> desc cust;

Name Null? Type

-------------------------------- -------- ----------------------------

NAME VARCHAR2(10)

STREET VARCHAR2(10)

AGE

SQL> create table customer (c_name varchar2(10),c_street varchar2(10), c_city varchar2(10));

Table Created.

SQL> desc customer;

Name Null? Type

----------------------------- --------------- ----------------------------

C_NAME NOT NULL VARCHAR2(10)

C_STREET VARCHAR2(10)

C_CITY VARCHAR2(10)

SQL> create view v1 as select customer.c_name,customer.c_city,cust.age from customer,cust where customer.c_name=cust.name;

View created.

SQL> desc v1;

Name Null? Type

---------------------------- --------------- ----------------------------

C_NAME NOT NULL VARCHAR2(10)

C_CITY VARCHAR2(10)

AGE NUMBER(10)

GROUP BY VIEW:

SQL> create view v2 as select age from cust group by age;

View created.

SQL> desc v2;

Name Null? Type

--------------------------------- -------- ----------------------------

AGE

PROCEDURES AND FUNCTIONS

PL/SQL subprograms

A subprogram is a named block of PL/SQL. There are two types of subprograms in PL/SQL namely Procedures and Functions. Every subprogram will have a declarative part, an executable part or body, and an exception handling part, which is optional.

Declarative part contains variable declarations. Body of a subprogram contains executable statements of SQL and PL/SQL. Statements to handle exceptions are written in exception part.

When client executes a procedure are function, the processing is done in the server. This reduces network traffic. The subprograms are compiled and stored in the Oracle database as stored programs and can be invoked whenever required. As they are stored in compiled form when called they only need to be executed. Hence they save time needed for compilation.

Subprograms provide the following advantages

1. They allow you to write PL/SQL program that meet our need 2. They allow you to break the program into manageable modules. 3. They provide reusability and maintainability for the code.

Procedures

Procedure is a subprogram used to perform a specific action. A procedure contains two parts specification and the body. Procedure specification begins with CREATE and ends with procedure name or parameters list. Procedures that do not take parameters are written without a parenthesis. The body of the procedure starts after the keyword IS or AS and ends with keyword END.

In the above given syntax things enclosed in between angular brackets (“< > “) are user defined and those enclosed in square brackets (“[ ]”) are optional. OR REPLACE is used to overwrite the procedure with the same name if there any. AUTHID clause is used to decide whether the procedure should execute with invoker (current-user or person who executes it) or with definer (owner or person created) rights .

Parameter Modes

Parameters are used to pass the values to the procedure being called. There are 3 modes to be used with parameters based on their usage. IN, OUT, and IN OUT. IN mode parameter used to pass the values to the called procedure. Inside the program IN parameter acts like a constant. i.e it cannot be modified.

OUT mode parameter allows you to return the value from the procedure. Inside Procedure the OUT parameter acts like an uninitialized variable. Therefore its value cannot be assigned to another variable.

IN OUT mode parameter allows you to both pass to and return values from the subprogram. Default mode of an argument is IN.

Functions:

A function is a PL/SQL subprogram, which is used to compute a value. Function is same like a procedure except for the difference that it have RETURN clause.

Syntax for Function

Examples

Function without arguments

Function with arguments. Different ways of executing the function.

To drop the procedure or function :

Drop procedure procedurename;

Drop function functionname;

For Discovering Errors:

Show errors procedure procedurename;

Show errors function functionname;

Syntax

SQL> create table stu(st_name varchar2(10),st_age number(3));

Table created.

SQL> create table ar(area numeric(5,2),radius numeric(6,2));

Table created.

SQL> create table pupil(name varchar2(10),age number(3));

Table created.

TABLE DESCRIPTION:

SQL> desc stu;

Name Null? Type

----------------------------------------- -------- ----------------------------

ST_NAME VARCHAR2(10)

ST_AGE NUMBER(3)

SQL> desc ar;

Name Null? Type

----------------------------------------- -------- ----------------------------

AREA NUMBER(5,2)

RADIUS NUMBER(6,2)

SQL> desc pupil;

Name Null? Type

----------------------------------------- -------- ----------------------------

NAME VARCHAR2(10)

AGE

PROCEDURE 1:

SQL> create procedure newstu(st_name in varchar,st_age in number)as

2 begin

3 insert into stu(st_name,st_age)

4 values(st_name,st_age);

5 end;

6 /

Procedure created.

SQL> exec newstu('ram','20');

PL/SQL procedure successfully completed.

PROCEDURE 2:

SQL> create or replace procedure stude

2 as

3 begin

4 update stu set st_age='21' where st_name='ram';

5 end;

6 /

Procedure created.

SQL> exec stude;

PL/SQL procedure successfully completed.

FUNCTION 1:

SQL> create or replace function fact(n in number)

2 return number

3 is

4 s number;

5 begin

6 s:=1;

7 if n=0 then

8 return 1;

9 end if;

10 for i in 1..n loop

11 s:=s*i;

12 end loop;

13 return s;

14 end;

15 /

Function created.

VISUAL BASIC FORMS

Aim:

To design a simple form in Visual Basic using Oracle as backend.

Algorithm for creating table:

1.SQL> create table vb1(Name varchar2(20),regno number(20),mark number(10));

Table created.

Table Description

2. Insert all the possible values into the vb1 table.

3. Enter commit command.

Algorithm for ADO Connection:

After creating the table in Oracle, Go to start menu

1. Start------> Settings------> Control Panel ------>Administrative Tools------>

Data Sources (ODBC) ------>User DSN------>Add------> Select Microsoft

ODBC for Oracle------>Finish------>OK

2. One new window will be appeared. In that window type Data Source Name as

table name created in Oracle. Type user name as Scott. Then click O.K.

Algorithm for ADODC in Visual Basic:

1. In Visual Basic create the labels, command buttons and their text boxes.2. In Visual Basic go to Project menu,3. Project---->Components---->Microsoft ADO Data Control 6.0 for OLEDB-->O.K4. Now ADODC Data Control available in toolbox.5. Drag and drop the ADODC Data Control into the form.6. Right click in ADODC Data Control then click the ADODC properties.7. One New window will be appeared. 8. Choose general tab select ODBC Data source name as the table created in Oracle.9. Choose authentication tab and select username, password as scott and tiger.10. Choose record source------>select Command type as adcmdTable. 11. Select Table or Stored procedure name as table created in Oracle.12. Click Apply------>O.K 13. Set the properties of each text box. 14. Select the data source as ADODC1 15. Select the Data field and set the required field name created in table

Coding

1.INSERT:

Private Sub Command1_Click()

Dim rs As New ADODB.Recordset

Dim conn As New ADODB.Connection

Dim sql As String

sql = " insert into name values ('" & Text1.Text & "'," & Text2.Text & "," & Text3.Text & ") "

conn.Open "Provider=MSDAORA.1;Data Source Name=sat;User Id=scott; Password=tiger;"

rs.Open sql, conn, , , adCmdText

MsgBox "inserted"

End Sub

2.DELETE:




Dim sql As String

sql = " delete from name where name=('" & Text1.Text & "') "

Set conn = New ADODB.Connection



Set DataGrid1.DataSource = rs

MsgBox "deleted"

End Sub

3.DISPLAY:




Dim sql As String

sql = " select * from name "



rs.CursorLocation = adUseClient



MsgBox "selected"

4.UPDATE:




Dim sql As String

sql = " update name set name=('" & Text1.Text & "')where name=('" & Text4.Text & "')"





MsgBox "updated"

End Sub

INSERTING VALUES INTO TABLE:

DISPLAYING THE VALUES FROM TABLE:

DELETING ONE ROW FROM TABLE:

UPDATING THE VALUES INTO TABLE:

MENU

Menus in Visual basic 6.0 are created using the Menu object defined in the VB Library. The menu created so are flat Win98 style menus and do not support icons. Further they cannot be created at runtime and require “Menu Editor”, modification though can be made during runtime.

Menu bar is the standard feature of most windows applications. The main purpose of the menus is for easy navigation and control of an application. Some of the most common menu items are File, Edit, View, Tools, Help and more. Each item on the main menu bar also provide a list of options or in the form of a pull-down menu. When you create a Visual Basic 6 program, you need not include as many menu items as a full fledge Windows application such as Microsoft Words. What you need is to include those menu items that can improve the ease of using your program by the user, and not to confuse the user with unnecessary items. Adding menu bar is relatively easy to accomplish in Visual Basic. There are two ways to add menus to your application, one way is to use the Visual Basic's Application Wizard and the other way is to use the menu editor.

Click on “Menu Editor…” under Tools on main menubar. Menu editor is accessible only if a form is focussed.

Adding Menu Bar Using Visual Basic's Application Wizard The easiest

way to add menu bar to your application is by using Visual Basic's Application Wizard. This

wizard allows the user to insert fully customized standard windows menus into his or her

application. To start using Visual Basic's Application Wizard, you click on the Application

Wizard icon at the Visual Basic new project dialog box, as shown below:

REPORT

STEPS TO CREATE DATA REPORT:

Steps for Creating Data Reports

Open the Visual Basic Project Make the connection with the database using either Data Environment or through ADO Add Data Report to the project If using Data Environment then in Report properties

– DataSource : <Data Environment name>– DataMember : <Command name>– Drag the fields from the command into the report

If using ADO then– Create the appropriate no. of textboxes on the report.– In datafield property of textbox write the name of the field name as in Recordset.

Reports are important and useful in many respects because they provide useful and meaningful information concerning a set of data. In this chapter, we will show you how to create a report in Visual Basic 6.

In previous versions of Visual Basic 6, there is no primary reporting . Previous versions of Visual basic 6 uses Crystal Reports tool, a software from Seagate. Fortunately, Microsoft has integrated a good report writer into Visual Basic 6, so you no longer need to use Crystal Report.

Connecting the report to database using Data Environment Designer

Click the Project menu, then select Data Environment. from the drop-down menu. The default data environment will appear, as shown in figure 40.2

Now, to connect to the database, right-click connection1 and select Microsoft OLE DB Provider for Oracle (as we are using MS Access database) from the Data Link Properties dialog (as shown in Figure 40.3), then click next.

Retrieving Information from the Database

In order to use the database in your report, you need to create query to retrieve the information from the database. Here , we will use SQL command to create the query. First of all, right click on MyConnection to add a command to the data environment. The default command is Command1, you can rename it as MyCommand, as shown in Figure 40.5.

In order to use SQL command, right-click MyCommand and you can see its properties dialog. At the General tab, select SQL statement and key in the following SQL statement:

SELECT Au_ID, AuthorFROM Authors ORDER BY Author

This command is to select all the fields from the Authors table in the Biblio.Mdb database. The command ORDER BY Author is to arrange the list in ascending order according to the Authors' Names.

Now, you need to customize a few properties of your data report so that it can connect to the database. The first property to set is the DataSource, set it to MyDataEnvironment. Next, you need to set the DataMember property to MyCommand,as shown in Figure 40.6

To add data to your report, you need to drag the fields from MyCommand in MyDataEnvironment into MyDataReport, as shown in Figure 40.7.Visual Basic 6 will automatically draw a RptTextBox, along with a RptLabel control for each field on the report. You can customize the look of the labels as well as the TextBoxes from the properties window of MyDataReport.

The Final step is to set MydataReport as the Startup form from the Project menu, then run the program. You will see your report as shown in Figure 40.8. You can print out your report.

dbms manual

Documents

adding menu

connection

foreign key

primary key

adcmdtext

recordset

adodc data

user idscott