当前位置:网站首页>Common Oracle Commands
Common Oracle Commands
2022-08-09 06:29:00 【Hey hey 潶 dark】
用户管理
创建用户
create user 用户名 identified by 密码;
修改用户密码
alter user 用户名 identified by 密码;
删除用户
drop user 用户名;
-- 关键字cascade
-- 可删除用户所有的对象,And then in the delete user
drop user 用户名 cascade;
用户权限
-- 1、连接权限
-- connect 临时用户,Assigned to a user does not need to build table
-- 2、资源权限
-- resource 提供给用户另外的权限以创建他们自己的表、序列、过程、触发器、索引和簇
-- 3、数据库管理员权限
-- dba Unlimited space limits and to other users with the ability of different authority
授权
grant 权限 to 用户名;
撤权
revoke 权限 from 用户名;
角色
创建角色
create role 角色名;
授权角色
grant 增删改查权限 on 表名 to 角色名;
删除角色
drop role 角色名;
表管理
创建表
CREATE TABLE 表名(
字段名 字段类型 字段约束
)
-- 例子,创建一个用户表,And to add attributes to users table field
CREATE TABLE users(
userid NUMBER(8) PRIMARY KEY,
username VARCHAR2(24) NOT NULL,
usersex VARCHART2(4)
)
COMMENT ON TABLE users IS '用户表';
COMMENT ON COLUMN users.userid IS '用户编号';
COMMENT ON COLUMN users.username IS '用户姓名';
COMMENT ON COLUMN users.usersex IS '用户性别';
表字段操作
-- 例子,Based on the example above the users table
-- 给表增加一个字段
ALTER TABLE users ADD(userphones VARCHAR2(24));
-- To the table fields change name
ALTER TABLE users RENAME COLUMN userphones TO userphone;
-- To table field modifier category
ALTER TABLE users MODIFY userphone VARCHAR2(48);
-- To delete a list of fields
ALTER TABLE users DROP COLUMN userphone;
表数据操作
-- 插入一条数据
INSERT INTO users VALUES(1,'王五','男');
-- 更新一条数据
UPDATE users SET username='李四' WHERE userid = 1;
-- 查询一条数据
SELECT * FROM users;
-- 删除一条数据
DELETE FROM users WHERE userid=1;
视图
CREATE OR REPLACE VIEW 视图名
AS
一条select语句
序列
CREATE SEQUENCE 序列名
minvalue 1 --最小值
nomaxvalue --不设置最大值(Determined by the machine),或 According to the table field value range Settings maxvalue
maxvalue 999 -- 最大值
start with 1 --从1开始计数,数值可变
increment by 1 --每次加1,数值可变
nocycle --一直累加,不循环;cycle:达到最大值后,Will start from scratch accumulation
nocache; --不建缓冲区. 如果建立cacheThe system will automatically readcache值个seq,This will speed up the running;If used in singlecache,或者oracle死了,So next time you readseqValues will be inconsistent,所以不建议使用cache.
-- 例子
drop table book;
--创建表
create table book(
bookId varchar2(4) primary key,
name varchar2(20)
);
--创建序列
create sequence book_seq start with 1 increment by 1;
--创建触发器
create or replace trigger book_trigger
before insert on book
for each row
begin
select book_seq.nextval into :new.bookId from dual;
end ;
--添加数据
insert into book(name) values ('cc');
insert into book(name) values ('dd');
commit;
函数
-- 创建一个函数
CREATE OR REPLACE FUNCTION 函数名
(参数名 参数类型,参数名 参数类型)
RETURN 返回参数类型
IS
参数名 参数类型;
BEGIN
PL/SQL程序块
RETURN 返回参数名;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('data not found');
END;
-- 调用函数
SELECT 函数名(参数) FROM dual;
-- 例子
CREATE OR REPLACE FUNCTION queryUsers(
uid NUMBER
)
RETURN NUMBER
IS
v_sum NUMBER;
BEGIN
SELECT count(*) INTO v_sum from users WHERE userid = uid;
return v_sum;
EXCEPTION
when no_data_found then
dbms_output.put_line('data not found');
end;
SELECT queryUsers(1) from dual;
存储过程
-- 创建存储过程
CREATE OR REPLACE PROCEDURE 过程名(
参数名 IN 参数类型, -- 输入参数
参数名 OUT 参数类型 -- 输出参数
)
IS
cot number:=0; --变量声明
BEGIN
PL/SQL语句;
num:=cot; --赋值语句
END;
-- 调用存储过程
-- 方法一
BEGIN
过程名(参数,...);
END;
-- 方法二
var 变量名 number;
EXEC 过程名(参数,...);
PRINT 变量名;
-- 例子
CREATE OR REPLACE PROCEDURE queryUserBySex(
sex IN VARCHAR2,
num OUT NUMBER
)
IS
cot number:=0;
BEGIN
SELECT COUNT(*) INTO cot FROM users WHERE usersex = 'nan';
num:=cot;
END;
DECLARE
retureCount NUMBER;
BEGIN
queryUserBySex('nan',retureCount);
DBMS_OUTPUT.PUT_LINE('性别nanThe number of students as'||retureCount);
END;
触发器
CREATE OR REPLACE TRIGGER 触发器名
BEFORE/AFTER INSERT/UPDATE/DELETE
ON 表名
FOR EACH ROW
-- DECLARE Design are carried out on the table to add authorization,需要加上
-- PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
PL/SQL语句;
-- COMMIT;
END;
-- 例子:
CREATE OR REPLACE TRIGGER in_del_trigger
AFTER DELETE
ON users
FOR EACH ROW
declare
pragma autonomous_transaction;
BEGIN
dbms_output.put_line(:old.userid);
INSERT INTO copyuser VALUES(:old.userid,'li','nan');
COMMIT;
END;
索引
创建索引
CREATE OR REPLACE 索引名 ON 表名(字段名)
单一索引
-- 创建索引
CREATE INDEX uname ON USERS(username);
-- 使用索引
select * from users where username='hong';
复合索引
CREATE INDEX uname ON USERS(userid,username);
select * from users order by userid,username;
基于函数的索引
CREATE INDEX uname ON USERS(UPPER(username));
select * from users where upper(username) = 'HONG';
If often index column to performDML操作,需要定期重建索引
ALTER INDEX 索引名 REBUILD
游标
DECLARE
CURSOR cur_user IS SELECT * FROM USERS; -- 声明游标
v_user cur_user%ROWTYPE;
BEGIN
OPEN cur_user; -- 打开游标
LOOP
FETCH cur_user INTO v_user; -- 提取数据
EXIT WHEN cur_user%NOTFOUND;
IF cur_user%FOUND THEN
dbms_output.put_line(v_user.userid || ' ' || v_user.username);
ELSE
dbms_output.put_line('none');
END IF;
END LOOP;
CLOSE cur_user; -- 关闭游标
EXCEPTION
WHEN OTHERS THEN
IF cur_user%ISOPEN THEN
CLOSE cur_user;
END IF;
END;
边栏推荐
猜你喜欢
db.sqlite3 has no "as Data Source" workaround
[GO]、数组与切片
带头双向循环链表的增删查改(C语言实现)
PDF不能打印和复制的问题如何解决?
vs番茄助手的方便功能和便捷快捷键介绍
SiO2 / KH550 modified ferroferric oxide nano magnetic particles | PDA package the ferromagnetic oxide nanoparticles (research)
6 states of a thread
Excel受保护的工作表怎么操作?
Go lang1.18入门精炼教程——第一章:环境搭建
A test engineer with an annual salary of 35W was laid off. Personal experience: advice that you have to listen to
随机推荐
力扣刷题180
IQ Products巨细胞病毒CMV感染检测试剂盒的特征和应用
【Wwise】ArgumentException: The specified path is not of a legal form (empty).关于WwiseGlobal中的路径读取错误问题
[R language] interaction test data
Excel受保护的工作表怎么操作?
Search 1688 product interface by image (item_search_img-search 1688 product by image (Politao interface) code docking tutorial
Word文件的只读模式没有密码怎么退出?
中英文说明书丨TRC D-阿卓糖(D-Altrose)
flask创建数据库失败未报错
How to find package information and pin definitions for NXP S32K1xx series microcontrollers
Invalid argument(s) appears when redis runs lua script
Silently start over, the first page is also a new page
简单工厂模式
Adds, deletes, searches, and changes the leading doubly circular linked list (implemented in C language)
Ferric oxide/bismuth sulfide nanocomposites ([email protected]@BSABiS nanoparticles) | dendrimer-stabilized bismuth sulfide nanop
Used to import the data type
pycharm环境包导入到另外一个环境
golang xml 处理动态属性
GNNExplainer应用于节点分类任务
Go lang1.18入门精炼教程——第一章:环境搭建