Oracle Class Notes

/*
* Create tablespace (as System identity)
*/
CREATE tablespace spc_myself--Table space Name (spc_myself)
DataFile ' E:\datafiles\MYSELF. DBF '--datafile specifies one or more data files for the tablespace, ' path and name of the data file '
Size 30m--file sizes, M-megabyte byte size
Autoextend off--prohibit automatic extension of data files

/*
* Delete Table space
*/
DROP tablespace spc_myself

/*
* Create user
*/
CREATE User one--username (one)
Identified by orcl--password (ORCL)
Default Tablespace spc_myself--for the user to determine the tablespace, not write defaults to the "User table space"

/*
* Delete User
*/
DROP USER one CASCADE;

/*
* Empower Users
*/
GRANT Connect,resource to one;

/*
* Switch Users
*/
--Verify that the user is successfully switched
SELECT * from User_tables;

/*
* Create a table
*/
--Create an EMP table
CREATE TABLE EMP
(
Empno Number (8) Not NULL PRIMARY KEY,
Ename VARCHAR2 (32),
Hirdate DATE,
Sal Number (8,2),
Empic BLOB,
Deptno Number (4)
);

CREATE TABLE Dept
(
Deptno Number (4) Not NULL PRIMARY KEY,
Dname VARCHAR2 (32),
Loc VARCHAR2 (64)
);

/*
* Add sequence
*/
CREATE SEQUENCE sq_dept;
CREATE SEQUENCE sq_emp;

/*
* Add data
*/
BEGIN
For I in 1..100 LOOP
INSERT into Dept VALUES (Sq_dept.nextval, ' pose with ' | | i| | ' Haha ', ' la La ');
END LOOP;
END;

SELECT * FROM Dept;

COMMIT;

DECLARE
CURSOR Dept_list is a SELECT * from dept;
BEGIN
For dept in Dept_list LOOP
For I in 1..100 LOOP
INSERT into emp VALUES (sq_emp.nextval,dept.dname| | ' Office ' | | I,SYSDATE,5000.00,NULL,DEPT.DEPTNO);
END LOOP;
END LOOP;
END;

SELECT * from EMP;

COMMIT;

Oracle Class Notes