oracle学习笔记

#安装完oracle后执行
ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION=FALSE; /*关闭空表不分配空间*/
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE; /*关闭大写敏感*/
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;/*关闭用户口令过期*/
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;/*关闭口令尝试错误次数限制*/
alter system set processes=800 scope=spfile;
alter system set sessions=885 scope=spfile;
alter user SYSTEM identified by manager;
grant ALTER SESSION to connect;
grant CREATE DATABASE LINK,CREATE VIEW,CREATE SYNONYM to resource; 

oracle中的sql语句和mysql中的sql语句还是有一些不同的，先简单学习一下oracle中的sql语句

1.创建用户

--创建用户
create user test2022 identified by 1234 
default tablespace users temporary tablespace test;
grant resource,connect to test2022;

test2022 ：用户名
1234：密码
test：表空间名称
grant resource,connect to test2022：给test2022 用户配置权限resource和connect

--删除用户
drop user lnn cascade;

创建/删除用户需要登录到system用户下操作。

--查询所有用户
select * from all_users;

2.创建表

CREATE TABLE schema_name.table_name (
    column_1 data_type column_constraint,
    column_2 data_type column_constraint,
    ...
    table_constraint
);

eg：

/*创建私教表*/
create table dn_coach(
  cid number(4),
  username varchar2(60), /*姓名*/
  nikename varchar2(60), /*昵称*/
  sex char(10), /*性别*/
  tele varchar2(11), /*联系方式*/
  age number(3), /*年龄*/
  good_at varchar2(60) /*擅长领域*/
);

--创建一个和dn_coach一样的表en_coach 
create table en_coach as
select * from dn_coach;

3.表的增删改查

3.1增

INSERT INTO table_name (column_1, column_2, column_3, ... column_n)
VALUES( value_1, value_2, value_3, ..., value_n);

eg：

insert into dn_coach(cid,username,nikename,sex,tele,age,good_at)
values(2,'李华','华华','女','18611111111','27','增肌减脂');

table_name 后面的column最好要写上，不要偷懒。

3.2删

DELETE
FROM
    table_name
WHERE
    condition;

eg：

delete from dn_coach where cid='4';

删除语句最好带有where，否则用truncate。truncate是DDL语句，有清空表的作用。执行后无法回滚，所以比delete效率高。而且它会重置表的自增值。

truncate table dn_coach;

3.3改

UPDATE
    table_name
SET
    column1 = value1,
    column2 = value2,
    column3 = value3,
    ...
WHERE
    condition;

eg：

update dn_coach set sex='男' where cid='4';

可用select * from dn_coach for update ;，但不推荐。

3.4查

SELECT
  column_1, 
  column_2, 
  ...
FROM
  table_name;

eg：

select * from dn_coach;

4.子查询

Oracle子查询就是嵌套查询，他把select 查询的结果作为另外一个select、update或delete语句的条件，它的本质就是where条件查询中的一个条件表达式。
eg：
先创建一个表，并给表加入一些数据。

--创建表
create table dn_good_at(
  gid number(4),
  name varchar2(60) /*名称*/
);
--插入记录
insert all
into dn_good_at(gid, name) values ('2','增重增肌')
into dn_good_at(gid, name) values ('3','增肌减脂')
select * from dual;

子查询：
查找cid为5的私教的擅长领域的gid（题目没有意义，仅为了练习）

select gid from dn_good_at
where name=(
select good_at from dn_coach
where cid='5'
);

5.函数

create [or replace] function 函数名 
([p1,p2...pn])
return datatype
is|as
--声明部分 
begin
--PL/SQL程序块
end

eg：

create or replace function fun(
  p_in_cid in varchar2,--id
  p_in_name in varchar2--姓名
)
return varchar2 is Result varchar2(60);
p_nickname varchar2(60);
begin
  select nikename into p_nickname from dn_coach
  where cid=p_in_cid and username=p_in_name;
  return p_nickname;
end;

调用函数：

select fun('2','李华') from dual;

6.存储过程

create [or replace] procedure 过程名
( p1 in|out datatype,
  p2 in|out datatype,
  ...
  pn in|out datatype
    
) is 
    
    ....--声明部分

    begin
    
    ....--过程体

    end;

eg：

create or replace procedure p_fun(
  p_in_cid in varchar2,--id
  p_in_name in varchar2--姓名
)
is
p_nickname varchar2(60);
begin
  select nikename into p_nickname from dn_coach
  where cid=p_in_cid and username=p_in_name;
  DBMS_OUTPUT.put_line(p_nickname);
end;

调用存储过程：

declare
begin
  p_fun('2','李华');
end;

7.异常

declare
   v_num number(5) := -1;
   exp_data_range exception; -- 异常定义
begin
   --v_num:=1/0;
   if v_num < 0 then
      raise exp_data_range; -- 异常抛出
   end if;
exception     --异常捕获
   when exp_data_range then
      dbms_output.put_line('数据范围不能为负数！');
   when others then
     dbms_output.put_line('other error');
end;

运行结果：

declare
   v_num number(5) := -1;
   exp_data_range exception; -- 异常定义
begin
   v_num:=1/0;
   if v_num < 0 then
      raise exp_data_range; -- 异常抛出
   end if;
exception     --异常捕获
   when exp_data_range then
      dbms_output.put_line('数据范围不能为负数！');
   when others then
     dbms_output.put_line('other error');
end;

运行结果：

8.包