Oracle基本语法及例子

末蓝、 2022-06-13 07:14 205阅读 0赞

--创建表空间
    CREATE TABLESPACE mydb
    DATAFILE 'D:\Oracle\mydb.dbf'
    SIZE 5M
    --创建用户
    CREATE USER admin_1 IDENTIFIED BY 123456  
    DEFAULT Tablespace mydb
    
    --赋值权限
    GRANT CONNECT,RESOURCE TO ADMIN_1
    
    --移出权限
    REMOVE CONNECT,RESOURCE TO ADMIN_1
    --删除用户
    DROP USER ADMIN_1 cascade; 
    --创建表
    CREATE TABLE UserInfo(
           NAME Varchar2(40) NOT NULL,
           sex VARCHAR2(1) DEFAULT 'Y'NOT NULL,
           birthday DATE NOT NULL,
           height Number(3,2),
           weight NUMBER(3,2),
           memo VARCHAR2(100)
    );
    --给列添加描述
    COMMENT ON column userinfo.name IS '姓名';
    COMMENT ON column userinfo.sex IS '性别';
    COMMENT ON column userinfo.birthday IS '生日';
    COMMENT ON column userinfo.height IS '身高';
    COMMENT ON column userinfo.weight IS '体重';
    COMMENT ON column userinfo.memo IS '备注';
    --创建主键
    alter TABLE userinfo ADD CONSTRAINT tb_test_p_name PRIMARY KEY (NAME);
    --提交以上操作
    COMMIT;
    
                      --创建表
                      CREATE TABLE student(
                            sid Varchar2(20) PRIMARY KEY  NOT NULL,
                            sname varchar2(20) NOT NULL,
                            sbirth DATE,
                            sclass varchar2(20) NOT NULL,
                            saddress varchar2(100)
                      )TABLESPACE mydb;
    
    
                      CREATE TABLE department(
                            did Varchar2(20) PRIMARY KEY  NOT NULL,
                            dname varchar2(40),
                            doffice VARCHAR2(100),
                            dtelphone varchar2(20),
                            dprincipal varchar2(20)
                      )TABLESPACE mydb;
    
    
       CREATE TABLE CLASS(
              cid Varchar2(20) PRIMARY KEY  NOT NULL,
                            department varchar2(20), 
                            cname VARCHAR2(20)
                      )TABLESPACE mydb;
                      --提交以上操作
                      COMMIT;
    --插入测试数据:
    INSERT INTO student(SID,sname,sbirth,sclass,saddress)
    VALUES('201005101','杨过',Date'1990-07-11','C001','湖南长沙');
    INSERT INTO student(SID,sname,sbirth,sclass,saddress)
    VALUES('201005102','小龙女',Date'1992-05-11','C002','云南大理');
    INSERT INTO student(SID,sname,sbirth,sclass,saddress)
    VALUES('201005103','赵敏',Date'1995-04-01','C001','湖北武汉');
    INSERT INTO student(SID,sname,sbirth,sclass,saddress)
    VALUES('201005104','李莫愁',Date'1950-11-21','C001','北京');
    INSERT INTO student(SID,sname,sbirth,sclass,saddress)
    VALUES('201005105','段然',Date'1980-12-25','C002','四川成都');
    INSERT INTO student(SID,sname,sbirth,sclass,saddress)
    VALUES('201005106','阿珠',Date'1970-07-10','C002','重庆');
    INSERT INTO student(SID,sname,sbirth,sclass,saddress)
    VALUES('201005107','株珠',Date'1997-07-10','C003','广东东莞');
    
    INSERT INTO CLASS(cID,department,cname)VALUES('C001','d001','软件一班');
    INSERT INTO CLASS(cID,department,cname)VALUES('C002','d001','软件二班');
    INSERT INTO CLASS(cID,department,cname)VALUES('C003','d002','建筑一班');
    
    INSERT INTO department(did,dname,doffice,dtelphone,dprincipal)
    VALUES('d001','计算机系','计算机楼','086-88764278','欧阳锋');
    INSERT INTO department(did,dname,doffice,dtelphone,dprincipal)
    VALUES('d002','建筑工程系','工程楼','086-88764285','黄华华');
    --------------------------------Oracle第三章Oracle模式对象-----------------------------------
     --查看表(编辑表)
    SELECT rowid ,student.* FROM student;
    SELECT * FROM CLASS FOR UPDATE;
    SELECT * FROM department; 
     --约束
    ALTER TABLE class
    ADD CONSTRAINT FK_department
    FOREIGN KEY (department) REFERENCES department(did);
    
    ALTER TABLE student
    ADD CONSTRAINT FK_sclass
    FOREIGN KEY (sclass) REFERENCES class(cid);
    --查询语句
    SELECT student.SID,student.sname,student.sbirth,class.cname,department.dname FROM CLASS 
    INNER JOIN student ON class.cid=student.sclass
    INNER JOIN department ON department.did=class.department;
     --权限
    GRANT CREATE VIEW,CREATE SEQUENCE,CREATE SYNONYM TO admin_1;
    
    --创建视图
    CREATE VIEW view_student AS
    SELECT * FROM student;
    --查看视图
    SELECT * FROM VIEW_student
    --删除视图
           DROP VIEW VIEW_student
    
    --创建索引
    CREATE INDEX index_sname ON student(sname);
    --查看索引
    SELECT * FROM all_indexes;
    --删除索引
    DROP INDEX index_sname;
    
    --创建序列号
    CREATE SEQUENCE seq_student
    START WITH 1
    INCREMENT BY 1
    --添加序列号
    INSERT INTO student(SID,sname,sbirth,sclass,saddress)
    VALUES(seq_student.nextval,'Tony',Date'1995-07-10','C001','广东东莞');
    --查看序列号
    SELECT seq_student.nextval FROM dual;
     --删除序列号
    DROP sequence seq_student 
    
    
     --同义词
    CREATE SYNONYM stu FOR mydb.studnet
     --查看
    SELECT * FROM stu;
     --删除
    DROP SYNONYM stu;
    
    --------------------------------Oracle第四章PL/SQL程序设计-----------------------------------  
    --声明输出
    DECLARE 
        number1 INTEGER:=90;
    BEGIN
        dbms_output.put_line(number1);
    END;
    
    /*
    --------PL/SQL块-----------
    声明(DECLARE)
        变量,游标等
    开始(BEGIN)
        SQL语句或者PL/SQL语句
    异常(EXCEPTION)
        异常处理
    结束(END)
    
    */
    SELECT * FROM STUDENT;
    
    DECLARE
          NAME VARCHAR2(20);  
    BEGIN
          SELECT sname INTO NAME FROM student WHERE SID='201005101';
          dbms_output.put_line(NAME);
    END;
    --if-else条件控制
    DECLARE 
         u1 NUMBER:=50;
         u2 NUMBER:=50;
    BEGIN
         IF u1<u2 THEN
           dbms_output.put_line('u1小于u2');
         ELSIF u1>u2 then
           dbms_output.put_line('u1大于u2');
         ELSE
           dbms_output.put_line('u1等于u2');
         END IF;
    END;
    ---case条件控制
    BEGIN
        CASE '&level'
          WHEN 'A' THEN dbms_output.put_line('优秀');
          WHEN 'B' THEN dbms_output.put_line('良好');
          WHEN 'C' THEN dbms_output.put_line('一般');
          WHEN 'D' THEN dbms_output.put_line('还需努力');
       END CASE;
    END;
    --loop...exit...end loop循环控制
    /*
    loop
      循环语句块
        if 条件 then
        exit
        end if;
    end loop;
    */
    DECLARE
       number1 Integer:=50;
       number2 INTEGER:=60;
       i INTEGER:=0;
    BEGIN
      LOOP
         number1:=number1+1;
         IF number1=number2 THEN
           EXIT;
         END IF;
         i:=i+1;
      END loop;
      dbms_output.put_line('共循环次数:'||to_char(i));
    END;
    
    --loop...exit...when...end loop循环
    DECLARE
         i NUMBER:=0;
    BEGIN
         LOOP
           i:=i+1;
           dbms_output.put_line('共循环次数:'||to_char(i));
           EXIT WHEN i>10;
         END LOOP;
    END;
    
    --for...in..loop..end循环控制
    DECLARE
          i NUMBER:=0;
    BEGIN
      FOR i IN 1..10 LOOP
        dbms_output.put_line(to_char(i));
      END LOOP; 
    END;
    --while...loop..end loop
    DECLARE
       number1 Integer:=50;
       number2 INTEGER:=60;
       i INTEGER:=0;
    BEGIN
      WHILE number1<number2 LOOP
        NUMBER1:=NUMBER1+1;
        i:=i+1;
      END loop;
      dbms_output.put_line('共循环次数:'||to_char(i));
    END;
    
    /*
    九九乘法表
    */
    
    --if循环
    DECLARE
      i NUMBER:=1;
      j NUMBER:=1;
    BEGIN
      FOR i IN 1..9 LOOP
        FOR j IN 1..i LOOP
          dbms_output.put(j||'*'||i||'='||i*j||' ');
    
        END LOOP;
         dbms_output.put_line('');
      END LOOP;
    END;
    --goto语句
      BEGIN
          GOTO put;
      dbms_output.put_line('这句话不会执行');
        <<put>>
           dbms_output.put_line('跳到这里了');
      END;
    --exp01:
    DECLARE
       classname VARCHAR2(20);
    BEGIN
       SELECT cname INTO classname FROM student,CLASS WHERE student.sclass=class.cid AND SID='&stuid';
       IF classname='软件一班'THEN
           dbms_output.put_line('发放奖金500');
       ELSIF classname='软件二班'THEN
            dbms_output.put_line('发放奖金300');
       ELSE
         dbms_output.put_line('无奖金');
        END IF;
    END; 
    --exp02:
    CREATE TABLE temp_table(
          ID NUMBER,
          NAME VARCHAR2(20)
    );
    
    
    DECLARE
     i NUMBER:=1;
     NAME VARCHAR2(20):='HelloWord';
    BEGIN
       FOR i IN 1..6 LOOP
          INSERT INTO temp_table(ID,NAME) VALUES(i,NAME);
         END LOOP;  
    END;
    
    SELECT ROWID,temp_table.* FROM temp_table;
    
    --预定义异常
    DECLARE
      sname VARCHAR2(20);
    BEGIN
     SELECT sname INTO sname FROM student;
      EXCEPTION
        WHEN TOO_MANY_ROWS THEN
           dbms_output.put_line('出现返回了多行的异常'); 
    END;
    
    --自定义异常
    DECLARE
    --定义异常
      not_in EXCEPTION;
      city VARCHAR2(20);
    BEGIN
      city := '&name';
    IF city NOT IN('长沙','常德','北京') THEN
        RAISE not_in;
    END IF;
    EXCEPTION
    WHEN not_in THEN 
        dbms_output.put_line('输入的地名不正确!');
    end;
    
    --------------------------------Oracle第五章游标------------------------------------
    select * from student;
    -------------------------------隐式游标-------------------------------------
    --%found游标:DML语句影响一行或者多行是,%found属性返回true
    begin
      update student set sid='201005108'where sid='1';
      if sql%found then
        dbms_output.put_line('存在记录');
      else
        dbms_output.put_line('不存在记录');
      end if; 
    end;
    --%notfound游标:与%found游标作用相反
    begin
      delete from student where sid='1';
      if sql%notfound then
        dbms_output.put_line('删除失败');
      end if;
    end;
    --%rowcount游标:返回DML语句执行的行数
    begin
     update student set sid='201005109'where sid='2';
      if sql%rowcount=0 then
        dbms_output.put_line('没有查询到数据');
      else
        dbms_output.put_line('查询到'||sql%rowcount||'条数据');
      end if;
    end;
    --Cursor for loop游标遍历查询结果集
    begin
      for s in(select sid,sname,saddress from student)
      loop
        dbms_output.put('学生编号'||s.sid||' ');
        dbms_output.put('学生姓名'||s. sname||' ');
        dbms_output.put_line('学生住址'||s.saddress);
      end loop;
    end;
    --------------------------自定义显示游标------------------------------------
    declare
      cursor cur_class is
      select * from class ;
      cid varchar2(20);
      department varchar2(20);
      cname varchar2(20);
    begin
      open cur_class;
      loop
        fetch cur_class into cid,department,cname; --从游标中提取数据
        exit when cur_class%notfound;
        dbms_output.put('科目编号'||cid||' ');
        dbms_output.put('教师编号'||department||' ');
        dbms_output.put_line('住址'||cname);
      end loop;
      close cur_class;
    end;
    
    -----------显示游标带参(参数不能带长度)--------------
    declare
      cursor cur_class(id class.cid%type) is
           select * from class where cid=id  ;
      crow class%rowtype; --行类型
      id class.cid%type;
    begin
      id := '&id';
      open cur_class(id);
      loop
        fetch cur_class into crow  ; --从游标中提取数据
        exit when cur_class%notfound;
         dbms_output.put('科目编号'||crow.cid||' ');
         dbms_output.put('教师编号'||crow.department||' ');
         dbms_output.put_line('住址'||crow.cname);
      end loop;
      close cur_class;
    end;
    
    
    --游标for循环
    declare
      cursor cur_student is
      select * from student;
    begin
      for s in cur_student
        loop
        dbms_output.put('学生编号'||s.sid||' ');
        dbms_output.put('学生姓名'||s.sname||' ');
        dbms_output.put_line('学生住址'||s.saddress);
        end loop;
    end;
    --游标for循环的上机练习2
    begin
      for showclass_cur in (select * from class) loop
        dbms_output.put_line('班级名:'||showclass_cur.cid);
        for showstu_cur in (select *　from student where sclass=showclass_cur.cid) loop
          dbms_output.put_line('学生名:'||showstu_cur.sname);
        end loop;
      end loop;
    end;
    ----------------游标变量--------------------
    declare
    --声明游标类型
       type stu_cur is ref cursor return student%rowtype;
    --声明游标变量
       stu_cur_type stu_cur ;
    --声明接收结果集变量
      sturow student%rowtype;
    begin
      --打开游标
      open stu_cur_type for select * from student;
      loop
        fetch stu_cur_type into sturow;--接收stu_cur_type游标变量中的结果集
        exit when stu_cur_type%notfound;
        dbms_output.put('学生编号'||sturow.sid||' ');
        dbms_output.put('学生姓名'||sturow.sname||' ');
        dbms_output.put_line('学生住址'||sturow.saddress);
      end loop;
      --关闭游标
      close stu_cur_type;
    end;
    
    
    
    
    ------------第六章存储过程---------------
    select * from student;
    --声明无参proc
    create  procedure showstudent as
    begin
     for student_cur in (select * from student)
     loop
         dbms_output.put('学号:'||student_cur.sid||' ');
         dbms_output.put('姓名:'||student_cur.sname||' ');
         dbms_output.put_line('住址:'||student_cur.saddress);
     end loop;
    end showstudent;
    --调用proc
    begin
      showstudent;
      --exec showstudent;
    end;
    
    --查看存储过程
    select text from user_source where name = 'SHOWSTUDENT';
    --删除proc
    drop procedure showstudent;
    
    
    
    --声明有参proc(in,out参数类型)
    create or replace procedure GetStuInfo(id in varchar2,sname out varchar2,cname out varchar2) is
    begin
      select s.sname,c.cname into sname,cname from 
      student s join class c on s.sclass=c.cid where s.sid=id;
    exception
        --no_data_found异常是在找不到一条记录时报错
        --dup_val_on_index唯一索引重复报错
       when no_data_found then
         dbms_output.put_line('该学生信息不存在!');
       commit;
    end GetStuInfo;
    --调用proc
    declare
      stuname varchar2(20);
      calssname varchar2(20); 
    begin
      GetStuInfo('201005106',stuname,calssname);
      dbms_output.put('学生姓名:'||stuname||' ');
      dbms_output.put_line('班级名称:'||calssname||' ');
    end;
    
    
    --声明有参proc(in out参数类型)
    --给电话号码增加区码
    create or replace procedure add_region(tel_num in out varchar2)
    as
    begin
      tel_num := '0735-'||tel_num;
    end add_region;
    --调用add_region存储过程
    declare
      phone varchar2(15):='84541542';
    begin
      add_region(phone);
      dbms_output.put_line('新电话号码为:'||phone);
    end;
    
    --存储函数
    create or replace function getsnamebyid(id varchar2)
      return varchar2 --声明返回值类型
    as
      name varchar2(20);--接收值的变量
    begin
      select sname into name from student where sid=id;
      return(name); --返回值 
    exception
      when no_data_found then
        dbms_output.put_line('没有数据');
        return(null);
      when others then
        dbms_output.put_line('发生其他错误');
        return(null);
    end;
    --调用函数
    begin
       dbms_output.put_line(getsnamebyid('201005101'));
    end;
    
    
    
    select * from student;
    select * from class;
    --计算当前的年份差select语句
    select floor(to_number(sysdate-to_date(student.sbirth))/365) from student;
    --自定义计算当前的年份差timepoor函数
    create or replace function time_poor (years in varchar2) return varchar2
    as
    timepoor varchar2(20);
    begin
      select floor(to_number(sysdate-to_date(years,'yyyy-mm-dd'))/365) into timepoor from dual;
      return(timepoor);
    end;
    --调用function
    begin
      if time_poor('1998-01-05')>=18 then
        dbms_output.put_line('成年!');
      else
        dbms_output.put_line('未成年!');
      end if;
      --dbms_output.put_line('时间差为:'||time_poor('2001-01-05'));
    end;
    
    
    ------------------程序包--------------------
    create or replace package stuInfo is --声明包头
      function getclassname(id varchar2) return varchar2; ---根据班级号获得班级名称函数
      procedure showstu;  ---获得学生信息列表proc
    
    end; 
    
    create or replace package body stuInfo --包体部分
    as
    ---根据班级号获得班级名称函数--
      function getclassname(id varchar2)
         return varchar2
     is
        classname varchar2(20);
      begin
        select cname into classname from class where cid=id;
        return(classname);
      exception
        when no_data_found then
          dbms_output.put_line('没有数据');
          return(null);
        when others then
          dbms_output.put_line('发生其他错误');
          return(null);
      end;
      ---获得学生信息列表proc--
      procedure showstu
      is
      begin
        for stu in (select * from student) loop
          dbms_output.put('学生编号为:'||stu.sid||' ');
          dbms_output.put('学生姓名为:'||stu.sname||' ');
          dbms_output.put_line('学生住址为:'||stu.saddress||' ');
        end loop;
      end showstu;
    end stuInfo;
    
    --删除程序包
    drop package stuinfo;
    
    --调用程序包
    begin 
      dbms_output.put_line('班级名称为:'||stuinfo.getclassname('C005'));
       dbms_output.put_line('----------------------------------');
      dbms_output.put_line('----学生列表----');
      stuInfo.showstu;
    end;
    
    
    ------------------------第七章触发器-------------------------
    -----------------------表操作----------------------
    create table temp(
      Del_name varchar2(20),
      Del_time date
    )TABLESPACE mydb;
    
    --新增字段 [alter table 表名 add 字段名 字段类型;]
    alter  table temp add v_type varchar2(15);
    --修改字段名 [alter table 表名 rename column 原字段名 to 新字段名;]
    alter table temp rename column  V_TYPE to t_type;
    --删除字段 [alter table 表名 drop column 字段名;]
    alter table temp drop column  t_type;
    --修改表名
    alter table temp rename                                                                                                             .........
                                                                                                                                                                                                                                                                                                                                                                      to recordtable;
    
    --语句级触发器(未使用for each now):不管DML语句影响多少行 也只调用一次触发器
    --创建多条件触发器(当删除学员以后触发插入语句 插入数据到recordtable表中)
    create or replace trigger t_student_trigger
    after --触发时机
    delete or update or insert  --触发事件
    on student --触发目标
    begin
      if deleting then
        insert into recordtable values(user,sysdate,'delete'); --触发操作
      elsif updating then
        insert into recordtable values(user,sysdate,'update'); --触发操作
      elsif inserting then
        insert into recordtable values(user,sysdate,'insert'); --触发操作
      end if;
    end;
    
    --删除student表中数据
    delete from student where sid='201005108';
    --插入student数据
    INSERT INTO student(SID,sname,sbirth,sclass,saddress)
    VALUES('201005108','Tong',Date'1991-07-10','C002','美国纽约');
    --更改student表中数据
    update student set sname='李逍遥' where sid='201005107';
    --查询recordtable表
    select * from recordtable for update;
    
    
    --行级触发器(使用for each now):DML语句影响多少行数据的时候 对其中的每行数据都会调用触发器
    --[标识符:new和:old]
    create or replace trigger t_student_trig
      after delete on student 
      for each row --行内触发器标志
        when(old.sid = '201005108') --条件标识符不能加冒号
    begin
      insert into recordtable values(:old.sid,sysdate,'delete_row'); --触发操作
    end;
    --测试行级触发器
    delete  from student where sid > '201005106';
    
    --instead of触发器(基于多表视图是无法进行insert,update,delete操作)
    --创建学生学分视图
    create or replace view stu_cla_view as
    select sname,cname,cps,sclass
    from class,student
    where class.cid=student.sclass;
     --查看视图
     select * from user_updatable_columns
     where table_name=upper('stu_cla_view');
    
    select * from stu_cla_view;
    
     --测试是否能更新视图 
     update stu_cla_view set cname='软件二班' where sclass='C002';
    -- delete from stu_cla_view  where cname='经管一班';
     select * from student;
     select * from class;
     --更新视图trig
     create or replace trigger update_view
     instead of update
     on stu_cla_view 
     for each row
     begin
          update class
          set cname=:new.cname
          where cid=:new.sclass;
     end;
    
    
    --系统事件触发
    --数据库关闭时记录的关闭时间
    create or replace trigger db_close_trig
    before shutdown
    on database
    begin
      dbms_output.put_line('系统于'||sysdate||'关闭');
    end;
    
    --用户事件触发器:DDL语句或者用户注册登录等事件的时候触发
    create table user_log(
    username varchar2(20),
    log_date timestamp,
    l_type varchar2(20)
    )
    --创建触发器
    --登录触发器
    create or replace trigger logon_trig
    after logon on schema
    begin
      insert into user_log values(user,sysdate,'Logon');
    end;
    --注销
    create or replace trigger logff_trig
    before logoff on schema
    begin
      insert into user_log values(user,sysdate,'logoff');
    end;
    --测试用户事件触发器
    select * from user_log;
    
    --查看触发器
    select trigger_name,trigger_type,triggering_event from user_triggers
    where trigger_name=upper('T_STUDENT_TRIG');
    
    
    ---------------------使用触发器和序列自动增长-----------------------
    create table test(
    id number primary key,
    name varchar2(20)
    )
      --创建序列号
      create sequence seq_test_id
      start with 1
      increment by 1
      --创建触发器
      create or replace trigger test_trig
        before insert
        on test 
        for each row
      declare
        next_id number; --接收序列号变量
      begin
        select seq_test_id.nextval into next_id from dual;
        :new.id := next_id;
      end test_trig;
    
    select * from test;
    
    --测试
    insert into test
      (name)
    values
      ('Pater');
    
    -----------------第八章数据库备份与恢复--------------------
    select name,created,log_mode from V$database;
    
    
    
    select * from test;