oracle常用操作1

--SQL Plus清除屏幕命令
clear screen

--通过system用户登录sqlplus
--通过system用户登录sqlplus
--创建表空间
create tablespace jimmyspace datafile 'D:\oracle\oradata\mydb\accpdb5.dbf' size 100m;
--创建临时表空间
create temporary tablespace jimmytempspace tempfile 'D:\oracle\oradata\mydb\accpdb5_temp.dbf' size 50m;
--创建新用户
create user jimmy identified by jimmy123456 default tablespace jimmyspace temporary tablespace jimmytempspace;

--赋权限给用户
--连接权限，用户创建后，连接sqlplus有问题，因为没有连接权限，所以要先赋予连接权限
grant connect to jimmy;
--RESOURCE角色允许用户使用数据库中的存储空间,赋予资源权限后才能够创建表
grant resource to jimmy;
--此系统权限允许用户在当前模式中创建序列，此权限包含在CONNECT角色中
grant create sequence to jimmy;

--创建数据库表
create table department
(
deptId number(6) primary key,
deptName nvarchar2(30) not null,
description nvarchar2(255)
)
/
create sequence department_sequence increment by 1 start with 1;
select department_sequence.nextval from dual;
select department_sequence.currval from dual;
drop sequence department_sequence;

select * from department;
insert into department(deptId,deptName,description) values(department_sequence.nextval,'开发部','软件开发部门');
insert into department(deptId,deptName,description) values(department_sequence.nextval,'销售部','软件销售部门');
insert into department(deptId,deptName,description) values(department_sequence.nextval,'技术支持部','软件服务部门');

--如果直接关掉sqlplus没有提交commit，则不会插入数据，如果exit sqlplus,会实际插入数据
commit;
update department set deptName='技术部' where deptName='技术支持部';
rollback;
--如果直接关掉sqlplus没有提交commit，则不会修改数据,如果是exit sqlplus,会实际修改数据

delete from department where deptId = 3;
rollback;
--如果直接关掉sqlplus没有提交commit，则不会删除数据,如果是exit sqlplus,会实际删除数据

    事务是最小的工作单元，作为一个整体进行工作
    保证事务的整体成功或失败，称为事务控制
    用于事务控制的语句有：
        COMMIT - 提交并结束事务处理
        ROLLBACK -  撤销事务中已完成的工作
        SAVEPOINT – 标记事务中可以回滚的点
SQL> delete from users where username='sunny';
已删除 1 行。
SQL> savepoint mark1;
保存点已创建。
SQL> update users set password='678001' where username='jack';
已更新0行。
SQL> savepoint mark2;
保存点已创建。
SQL> insert into users values('crystal','123');
已创建 1 行。
SQL> rollback to mark1;
回退已完成。
结果回到mark1之前的结果,即执行了删除操作，但没有提交


create table employee
(
employeeId number(6) primary key,
name nvarchar2(20) not null,
age number(3),
deptId number(6) not null,
description nvarchar2(255),
constraint fk_deptId foreign key(deptId) references department(deptId),
constraint check_age check(age between 18 and 100)
)
/

create sequence employee_sequence increment by 1 start with 1;
--违反了检查约束
insert into employee(employeeId,name,age,deptId,description) values(employee_sequence.nextval,'Judy',12,1,'开发人员');
--违反了外键约束
insert into employee(employeeId,name,age,deptId,description) values(employee_sequence.nextval,'Judy',22,5,'开发人员');
insert into employee(employeeId,name,age,deptId,description) values(employee_sequence.nextval,'Merry',22,1,'开发人员');
insert into employee(employeeId,name,age,deptId,description) values(employee_sequence.nextval,'David',26,1,'设计人员');
insert into employee(employeeId,name,age,deptId,description) values(employee_sequence.nextval,'Jack',28,1,'项目经理');
insert into employee(employeeId,name,age,deptId,description) values(employee_sequence.nextval,'Lily',23,2,'销售人员');

--连接
select a.name, a.age, a.deptId, b.deptName from employee a, department b where a.deptId = b.deptId;
--内连接
select a.name, a.age, a.deptId, b.deptId, b.deptName from employee a inner join department b on a.deptId = b.deptId;
--左外连接
select a.name, a.age, a.deptId, b.deptId, b.deptName from employee a left outer join department b on a.deptId = b.deptId;
select a.name, a.age, a.deptId, b.deptId, b.deptName from employee a, department b where a.deptId = b.deptId(+);
--右外连接
select a.name, a.age, a.deptId, b.deptId, b.deptName from employee a right outer join department b on a.deptId = b.deptId;
select a.name, a.age, a.deptId, b.deptId, b.deptName from employee a, department b where a.deptId(+) = b.deptId;
--完全外连接
select a.name, a.age, a.deptId, b.deptId, b.deptName from employee a full outer join department b on a.deptId = b.deptId;

--从其它表中建表(SQLServer有些不同: select * into newtable from oldtable
--select name, provinceId into t_city from city  (SQLServer语法)
--select * into t_city from city                 (SQLServer语法)
create table dept_table as select * from department;
--从其它表中复制数据到现有表(必须先存在dept_table表)
insert into dept_table select * from department;
--重新命名表名
rename dept_table to department_table;
--查看一个表的结构
desc department_table;

--修改表结构,更改列的字符大小
alter table department_table modify (description nvarchar2(200));
--修改表结构,增加列
alter table department_table add createTime date;
--修改表结构,删除列(注意CREATETIME要大写)
alter table department_table set unused("CREATETIME") cascade constraints;
alter table department_table drop createtime;
--删除内容
truncate table department_table drop storage;(截掉,不可恢复)

--创建表
create table orderitem(productName nvarchar2(20), qty number, price number(6,2), amount number(8,2));
--创建触发器,针对整个表(注意:begin前面不要空格,因为拷贝这个触发器创建脚本到SQL/Plus中执行会有问题)
create or replace trigger orderitem_trigger after insert on orderitem
begin
    update orderitem set amount = qty * price;
end;
/

create table users(userId number(6) primary key, userName nvarchar2(20));
create table goods(goodId number(6) primary key, name nvarchar2(20) not null, buyerId number(6), buyerName nvarchar2(20));


--插入数据
insert into orderitem(productName,qty, price) values('CPU', 10, 1200.00);
--查看触发器执行效果
select * from orderitem;

--创建存储过程
create table users(username nvarchar2(10), password nvarchar2(20));
1.
CREATE OR REPLACE PROCEDURE add_user(user_name IN VARCHAR2,user_pwd IN VARCHAR2)  AS
BEGIN
   INSERT INTO users(username,password) VALUES (user_name ,user_pwd);
END add_user;
/
执行存储过程
exec add_user('jimmy', '123456');

2.
CREATE OR REPLACE PROCEDURE get_userpwd(user_name IN VARCHAR2,user_pwd OUT VARCHAR2)  AS
BEGIN
   SELECT password INTO user_pwd FROM users where userName = user_name;
END get_userpwd;
/

3.
CREATE OR REPLACE PACKAGE mypackage AS TYPE my_cursor IS REF CURSOR;
end mypackage;
/

CREATE OR REPLACE PROCEDURE find_users(my_cursor out mypackage.my_cursor) IS
BEGIN
OPEN my_cursor FOR SELECT * FROM users;
end find_users;
/