SQL*Plus

Q. How do you get the previous commands in the SQL prompt?

A. With windows clients if you use sqlplus.exe instead of sqlplusw.exe
(which is the default), you can make use of arrow keys.
Otherwise, you can display the contents of afiedt.buf line by line
in the sql prompt.

In unix clients you cannot help it.

Q. How do you run a script?

A. At the prompt type:-
SQL>@(path_to_script)/(script_name)

for example:-
SQL>@C:\projects\currentProject\sql\users.sql

Q. How do you create a new user in a database?

A.
SQL>create user (user) identified by [password]
2 default tablespace users
3 temporary tablespace temp;

User created.

SQL>grant connect, resource to (NewUser)

Grant succeeded.

Oracle Sequence for Auto-Increment
Example Number 1 ...
create sequence product_seq start with 1 increment 1
/
create or replace trigger product_insert before insert for each row begin
select productseq.nextval
into :new.product_id
from dual;
end;
/
Example Number 2 ...
How to create an autoincrement field in a table with a sequence ...
SQLWKS> create table bob(a number , b varchar2(21));
Statement processed.
First create a sequence
SQLWKS> create sequence x ;
Statement processed.
Then create the trigger.
create trigger y before insert on bob
for each row
when (new.a is null)
begin
select x.nextval into :new.a from dual;
end;
/
Example Number 3 ...
First create a sequence:
create sequence emp_no_seq;
By default it increments by 1 starting at 0.
Use its values when inserting data into the table:
insert into t_emp values (emp_no_seq.nexval, 'Joe Black');

Write up : http://jen.fluxcapacitor.net/geek/autoincr.html

Check out : http://www.techonthenet.com/oracle/primary_keys.htm





PL/SQL : http://www-db.stanford.edu/~ullman/fcdb/oracle/or-plsql.html