plsql基本工作流程

目录

1. 创建用户并设置密码

 2. 授予用户 DBA 角色

3. 创建表空间并指定数据文件

4. 将设置的表空间赋给用户

5.创建表

---

假设用户名为 plsql 、 密码为plsql  、表空间为 my_tablespace

1. 创建用户并设置密码

CREATE USER  plsql IDENTIFIED BY plsql;

 2. 授予用户 DBA 角色

GRANT DBA TO plsql ;

3. 创建表空间并指定数据文件

CREATE TABLESPACE my_tablespace

DATAFILE 'D:/oracle/my_tablespace.dbf'--存放文件的绝对路径

SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

4. 将设置的表空间赋给用户

ALTER USER plsql DEFAULT TABLESPACE my_tablespace;

5.创建表

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';



CREATE TABLE emp (

  empno NUMBER(5) NOT NULL,

  ename VARCHAR2(20) NOT NULL,

  job VARCHAR2(9) NOT NULL,

  mgr NUMBER(5),

  hiredate DATE NOT NULL,

  sal NUMBER(7,2) NOT NULL,

  comm NUMBER(7,2),

  deptno NUMBER(5) NOT NULL

)TABLESPACE my_tablespace;--一定要指定表放入的表空间



INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00, NULL, 20);

INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1991-02-20', 1600.00, 300.00, 30);

INSERT INTO emp VALUES (7521, 'WARD', 'SALESMAN', 7698, '1991-02-22', 1250.00, 500.00, 30);

INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, '1991-04-02', 2975.00, NULL, 20);

INSERT INTO emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1991-09-28', 1250.00, 1400.00, 30);

INSERT INTO emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1991-05-01', 2850.00, NULL, 30);

INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, '1991-06-09', 2450.00, NULL, 10);

INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1997-04-19', 3000.00, NULL, 20);

INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, '1991-11-17', 5000.00, NULL, 10);

INSERT INTO emp VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1991-09-08', 1500.00, NULL, 30);

INSERT INTO emp VALUES (7900, 'JAMES', 'CLERK', 7698, '1991-12-03', 950.00, NULL, 30);

INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, '1991-12-03', 3000.00, NULL, 20);

INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK', 7782, '1992-01-23', 1300.00, NULL, 10);

COMMIT;--一定要提交事务

SELECT * FROM emp;