Oracle基本语法及例子

来源：互联网发布：linux 目录剩余空间编辑：程序博客网时间：2024/05/17 04:40
--创建表空间CREATE TABLESPACE mydbDATAFILE '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 classADD CONSTRAINT FK_departmentFOREIGN KEY (department) REFERENCES department(did);ALTER TABLE studentADD CONSTRAINT FK_sclassFOREIGN 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.sclassINNER JOIN department ON department.did=class.department; --权限GRANT CREATE VIEW,CREATE SEQUENCE,CREATE SYNONYM TO admin_1;--创建视图CREATE VIEW view_student ASSELECT * 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_studentSTART WITH 1INCREMENT 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 loopDECLARE   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;EXCEPTIONWHEN not_in THEN     dbms_output.put_line('输入的地名不正确!');end;--------------------------------Oracle第五章游标------------------------------------select * from student;-------------------------------隐式游标---------------------------------------%found游标:DML语句影响一行或者多行是,%found属性返回truebegin  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循环的上机练习2begin  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;--声明无参proccreate  procedure showstudent asbegin 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;--调用procbegin  showstudent;  --exec showstudent;end;--查看存储过程select text from user_source where name = 'SHOWSTUDENT';--删除procdrop procedure showstudent;--声明有参proc(in,out参数类型)create or replace procedure GetStuInfo(id in varchar2,sname out varchar2,cname out varchar2) isbegin  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;--调用procdeclare  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)asbegin  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 varchar2astimepoor varchar2(20);begin  select floor(to_number(sysdate-to_date(years,'yyyy-mm-dd'))/365) into timepoor from dual;  return(timepoor);end;--调用functionbegin  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;  ---获得学生信息列表procend; 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_triggerafter --触发时机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 asselect sname,cname,cps,sclassfrom class,studentwhere 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_trigbefore shutdownon databasebegin  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_trigafter logon on schemabegin  insert into user_log values(user,sysdate,'Logon');end;--注销create or replace trigger logff_trigbefore logoff on schemabegin  insert into user_log values(user,sysdate,'logoff');end;--测试用户事件触发器select * from user_log;--查看触发器select trigger_name,trigger_type,triggering_event from user_triggerswhere 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;
阅读全文
0 0