--连接管理员
conn / as sysdba;
sys;--系统管理员，拥有最高权限
system;--本地管理员，次高权限
scott;--普通用户，密码默认为tiger,默认未解锁
--创建临时表空间
create temporary tablespace user_temp
tempfile 'D:\user_temp.dbf'
size 50M
autoextend on
next 50m maxsize 1000m
extent management local;
--创建数据表空间
create tablespace user_data
logging
datafile 'D:\user_data.dbf'
size 50m
autoextend on
next 50 maxsize 1000m
extent management local;
--创建用户和密码并指定表空间
create user liuqiang identified by liuq
default tablespace user_data
temporary tablespace user_temp;
--授予权限
grant connect,resource to liuqiang;
--授权dba权限
grant dba to liuqiang;
--删除用户以及用户所有的对象
drop user liuqiang cascade;
--修改密码
alter user liuqiang identified by liuqiang;
--查询liuqiang用户
select * from user_sys_privs where username='liuqiang';
--授予连接的权限
grant connect to liuqiang;
--授予对表的所有操作的角色
grant resource to liuqiang;
--授予liuqiang用户创建session的权限
grant create session to liuqiang;
--授予liuqiang用户使用表空间的权限
grant unlimited session to liuqiang;
--授予创建表的权限
grant create table to liuqiang;
--回收连接权限
revoke connect from liuqiang;
--创建表
create table T_user
(
c_id raw(16) primary key,
c_name varchar2(50) not null,
c_password varchar2(50) not null
);
create table T_userType
(
c_typeId raw(16) primary key not null,
c_typeName varchar2(50) not null
);
--复制表
create table userinfo as(select * from T_user);
--添加字段
alter table T_user add c_age smallint;
alter table T_user add c_typeId raw(16) not null;
--查看表的详细信息
desc T_user;
--字段添加检查约束约束
alter table T_user add constraint CK_age check(c_age>0 and c_age<=100);
--字段修改为非空
alter table T_user modify(c_age not null);
--添加唯一约束
alter table T_user add constraint UQ_name unique(c_name);
--删除唯一约束
alter table T_user drop constraint UQ_name;
--添加外键约束
alter table T_user add constraint FK_c_typeId foreign key(c_typeId) references T_userType(c_typeId);
--修改字段属性
alter table T_user modify(c_age int);
--创建序列
create sequence T_user_SEQ
increment by 1--自增1
nomaxvalue--无最大值
nocache--无缓存
start with 1--从1开始
nocycle--不循环
noorder;--不排序
create sequence T_userType_SEQ
increment by 1
nomaxvalue
nocache
nocycle
noorder;
--插入guid类型的数据
insert into T_userType(c_typeId,c_Typename) values(
sys_guid(),'会员');
insert into T_userType(c_typeId,c_Typename) values(
sys_guid(),'普通');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'liuqiang','liuqiang',23,'B41826760A624EB692A4A92E1C4FB821');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'张三','张三',23,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'李四','李四',22,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'tim','tim',22,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'lily','lily',15,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'lucy','lucy',17,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'王二','王二',22,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'王丽丽','王丽丽',22,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'红红','红红',22,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'小绿','小绿',36,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'小黄','小黄',33,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'企鹅','企鹅',27,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'你好','你好',19,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'新年','新年',22,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'蛇','蛇',22,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'电视','电视',22,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'小甜甜','小甜甜',22,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'小黑','小黑',14,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'旺旺','旺旺',17,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'旺财','旺财',22,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'豆子','豆子',22,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'田娃','田娃',19,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'田鸡','田鸡',22,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'赵','赵',22,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'呵呵','呵呵',22,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'哈哈','哈哈',22,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'可以','可以',22,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'的','的',44,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'伤心','伤心',22,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'额','额',22,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'dfsd','dsf',22,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'dd','ddd',22,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'eee','eee',27,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'李www四','李www四',22,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'qqq','eeee',22,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'rrr','tttt',33,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'yyy','uuuu',21,'BDF0229F86234043AB1B81E9F314FF6F');
insert into T_user(c_id,c_Name,c_Password,c_Age,c_Typeid) values(sys_guid(),'李iii四','iiii',14,'BDF0229F86234043AB1B81E9F314FF6F');
--插入number的数据
insert into T_userType(c_typeId,c_Typename) values(
T_USERTYPE_SEQ.Nextval,'普通');
--查询当前插入的编号
select T_USERTYPE_SEQ.Currval from T_userType;
--分页查询
select * from (select U.*,rownum r from (select * from T_user) U
where rownum<10) T
where t.r>=5;
--打开输出
set serveroutput on;
--查询输入的值
declare in_name t_user.c_name%type;
begin
select c_name into in_name from T_user
inner join T_Usertype
on T_Usertype.c_Typeid=T_user.c_Typeid
where T_user.c_Name='&c_Name';
if in_name='liuqiang'then
dbms_output.put_line('登录成功！'||in_name);
else
dbms_output.put_line('登录失败');
end if;
end;
--创建视图
create or replace view view_user
as
select c_id,c_name,c_password,c_age,c_typeName from T_user
inner join T_Usertype
on T_Usertype.c_Typeid=T_user.c_Typeid;
--删除视图中的值表中的数据也会删除
delete view_user where c_name='&c_name';
--创建唯一索引字段包括c_name，c_Age，c_Typeid
create unique index index_user on T_user(c_name asc,c_Age desc,c_Typeid);
--创建存储过程处理事务
create or replace procedure usp_user
(
in_name T_user.c_Name%type,--自适应类型
in_age T_user.c_Age%type
)
is
begin
savepoint aa;
delete from T_user where c_name=in_name;
insert into T_user(c_Id,c_Name,c_password,c_Age,c_Typeid) values(sys_guid(),in_name,in_name,in_age,'BDF0229F86234043AB1B81E9F314FF6F');
dbms_output.put_line('成功');
exception --异常
when others then
rollback to savepoint aa;
dbms_output.put_line('失败');
return;
end;
--调用存储过程
call usp_user('rrr');
--执行存储过程
execute usp_user('rrr');
--创建函数返回值为varchar2(类型)含有输出参数
create or replace function Fun_user(in_name T_user.c_Name%type,out_name out T_user.c_Name%type )
return T_user.c_Name%type is
begin
select c_name into out_name from T_user where c_name=in_name;
return '用户名为'||out_name;
end;
--调用带有输出参数的函数
declare
b_name T_user.c_Name%type;
a_name T_user.c_Name%type;
begin
a_name:=Fun_user('liuqiang',b_name);
dbms_output.put_line(a_name);
end;
--创建包
create or replace package pag_user is
procedure usp_user(in_name in T_user.c_Name%type);--声明存储过程
function Fun_user(in_name in T_user.c_Name%type,out_name out T_user.c_Name%type) return T_user.c_Name%type;--声明函数返回为T_user.c_Name%type类型
end;
--创建包主体
create or replace package body pag_user is
procedure usp_user(in_name in T_user.c_Name%type) is
begin
delete from T_user where c_name=in_name;
end;
function Fun_user(in_name in T_user.c_Name%type,out_name out T_user.c_Name%type) return T_user.c_Name%type is
begin
select c_name into out_name from T_user where c_name=in_name;
return out_name;
end;
end;
--调用包中的存储过程和函数
execute pag_user.usp_user('dd');
declare
in_name T_user.c_Name%type;
out_name T_user.c_Name%type;
begin
out_name:=pag_user.Fun_user('liuqiang',in_name);
dbms_output.put_line(out_name);
end;
--复合类型
create or replace procedure usp_select(in_name in T_user.c_Name%type) is
--定义一个复合类型即数组
type listUser is record(out_name T_user.c_Name%type,out_age T_user.c_Age%type);
user_info listUser;
begin
select c_name,c_age into user_info from T_user where c_name=in_name;
dbms_output.put_line('姓名'||user_info.out_name);
end;
--%rowtype
create or replace procedure usp_select(in_name in T_user.c_Name%type) is
user_info T_user%rowtype;--输出表的行
begin
select * into user_info from T_user where c_name=in_name;
dbms_output.put_line('姓名'||user_info.c_name||',年龄'||user_info.c_age);
end;
--执行
execute usp_select('liuqiang');
--游标
create or replace procedure usp_info is
type type_name is ref cursor;--定义游标
user_info type_name;--定义游标变量
c_name T_user.c_Name%type;
c_age T_user.c_age%type;
begin
open user_info for select c_name,c_age from T_user;--打开游标
loop--循环
fetch user_info into c_name,c_age;--遍历游标
exit when user_info%notfound;--没有找到数据就退出循环
dbms_output.put_line('姓名'||c_name||',年龄'||c_age);--输出信息
end loop;
close user_info;--关闭游标
end;
--调用
execute usp_info;
--创建触发器
create or replace trigger tri_user
before--触发前
insert or delete or update on T_user--增删改
begin
case
when inserting then
raise_application_error(-20002,'请不要添加');
when deleting then
raise_application_error(-20003,'请不要修改');
when updating then
raise_application_error(-20004,'请不要删除');
end case;
end;
--使用：old和：new
create or replace trigger tri_user
before
delete on T_user
for each row
begin
insert into userinfo(c_id,c_Name,c_Password,c_Age,c_Typeid) values(:old.c_id,:old.c_Name,:old.c_Password,:old.c_Age,:old.c_Typeid);
end;
--导出表空间表table1中的字段filed1以"00'打头的数据导出
exp liuqiang/liuqiang@orcl file='d:\user_temp.dmp' tables=(table1) query=" where filed1 like '00%'"
--导入表空间表table1
imp liuqiang/liuqiang@orcl file='d:\user_temp.dmp' full=y ignore=y
tables=(table1)
--包中创建游标
create or replace package pag_pagecount is
type user_info is ref cursor;
end;
--创建分页过程
create or replace procedure usp_page(in_pagesize in number,in_pageIndex in number) is
user_type pag_pagecount.user_info;--游标
out_name userinfo.username%type;--名字
out_pwd userinfo.userpwd%type;--密码
page_size number;--结束
page_index number;--开始
begin
page_size:=in_pageIndex*in_pagesize;--结束
page_index:=(in_pageIndex-1)*in_pagesize;--开始
dbms_output.put_line(page_size||page_index);
open user_type for select username,userpwd from (select u.username,u.userpwd,rownum r from (select username,userpwd from userinfo) u where rownum<page_size) T where T.r>=page_index;
loop
fetch user_type into out_name,out_pwd;
exit when user_type%notfound;
dbms_output.put_line(out_name||out_pwd);
end loop;
end;
--得到总页数
create or replace function fun_page return number is
out_pagCount number;
begin
select count(*) into out_pagCount from userinfo;
return out_pagCount;
dbms_output.put_line(out_pagCount);
end;
