--解锁用户,需要使用dba权限conn sys/1234 as dba;alert user scott account unlock;/--initcap方法,实现字符串首字符大写,其余字符小写,受空格,逗号等非字母符号限制 select initcap('hello word,da jia hao!') Hello from dual;--平均工资大于2000的分组select avg_sal,deptno from (select avg(sal) avg_sal,deptno from emp group by deptno) where avg_sal >2000;having语句用法:select avg(sal),deptno from emp group by deptno having avg(sal) > 2000;/--求部门中哪些人的薪水最高select ename,sal from emp e join (select max(sal) max_sal,deptno from emp group by deptno) t on (e.deptno = t.deptno and e.sal = t.max_sal)/--求部门平均薪水的等级select deptno,avg_sal,grade from salgrade s join (select avg(sal) avg_sal,deptno from emp group by deptno) t on (t.avg_sal between s.losal and hisal)/--求部门平均的薪水等级select avg(t.grade) avg_grade,t.deptno from (select ename,deptno,grade from emp e join salgrade s on (e.sal between s.losal and s.hisal)) t group by t.deptno/--雇员中哪些人是经理人select ename from emp where empno in (select distinct mgr from emp)/--不用函数求薪水的最高值select distinct ename,sal from emp where sal not in (select distinct e1.sal from emp e1 join emp e2 on (e1.sal <e2.sal))/select t.ename,t.sal from (select ename,sal from emp order by sal desc) t where rownum<2(当最大薪水有多值时不对)/--求平均薪水最高的部门的部门编号select deptno,avg_sal from (select avg(sal) avg_sal,deptno from emp group by deptno) where avg_sal = (select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno))等同于:select deptno,avg_sal from(select avg(sal) avg_sal,deptno from emp group by deptno)where avg_sal = (select max(avg(sal)) from emp group by deptno)/--求平均薪水最高的部门名称select dname from dept where deptno = ( select deptno from ( select deptno,avg(sal) avg_sal from emp group by deptno ) where avg_sal = ( select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno) ))/--求平均薪水的等级最低的部门的部门名称select t.deptno,t.dname from dept t where t.deptno = ( select deptno from ( select deptno,grade,avg_sal from (select avg(sal) avg_sal,deptno from emp group by deptno) join salgrade s on (avg_sal between s.losal and s.hisal) where grade = ( select min(grade) from ( select deptno,grade,avg_sal from (select avg(sal) avg_sal,deptno from emp group by deptno) join salgrade s on (avg_sal between s.losal and s.hisal) ) ) ) )等同于:select t.deptno,dname,avg_sal,grade from( select deptno,avg_sal,grade from (select avg(sal) avg_sal,deptno from emp group by deptno) t1 join salgrade s on (t1.avg_sal between s.losal and s.hisal)) tjoin dept d on (t.deptno = d.deptno)where t.grade = ( select min(grade) from ( select deptno,avg_sal,grade from (select avg(sal) avg_sal,deptno from emp group by deptno) t1 join salgrade s on (t1.avg_sal between s.losal and s.hisal) ) )/--求平均等级最低的部门名称select t.dname from dept t where t.deptno = ( select deptno from ( select deptno,avg(grade) avg_grade from ( select e.deptno,s.grade from emp e join salgrade s on (e.sal between s.losal and s.hisal) ) group by deptno ) where avg_grade = ( select min(avg_grade) from (select deptno,avg(grade) avg_grade from (select e.deptno,s.grade from emp e join salgrade s on (e.sal between s.losal and s.hisal) ) group by deptno ) ) )/--创建视图,首先赋予权限conn sys/1234 as sysdba; sys不校验密码,任意密码即可。grant create table,create view to scott;create view v_dept_avg_sal_info asselect deptno,grade,avg_sal from(select deptno,avg(sal) avg_sal from emp group by deptno) tjoin salgrade s on (t.avg_sal between s.losal and s.hisal);因此,求平均等级最低的部门名称可以用一下语句替换:select t.deptno,d.dname,avg_sal,grade fromv_dept_avg_sal_info t join dept d on (t.deptno = d.deptno)where t.grade = (select min(grade) from v_dept_avg_sal_info);/--求部门经理人中平均薪水最低的部门名称(经理人指empno存在于mgr中的员工)select dname, t.deptno, avg_sal from (select deptno, avg(sal) avg_sal from emp where empno in (select distinct mgr from emp where mgr is not null) group by deptno) t join dept d on (d.deptno = t.deptno) where t.avg_sal = (select min(avg_sal) from (select deptno, avg(sal) avg_sal from emp where empno in (select distinct mgr from emp where mgr is not null) group by deptno))创建视图v_mgr_sal_info,如下: create view v_mgr_sal_info asselect deptno, avg(sal) avg_sal from emp where empno in (select distinct mgr from emp where mgr is not null) group by deptno;select dname, t.deptno, avg_sal from v_mgr_sal_info t join dept d on (d.deptno = t.deptno) where t.avg_sal = (select min(avg_sal) from v_mgr_sal_info);/--求比普通员工的最高薪水还高的经理人名称:select ename from emp where empno in (select distinct mgr from emp where mgr is not null) and sal > (select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null))/--删除用户drop user mc cascade;--备份用户 backup scottexp--创建用户create user mc identified by 1234 default tablespace quota 10M on users;--赋予权限grant create session,create table,create view to mc;--导入数据imp--快速备份表,即创建一个一样的表create table dept1 as select * from dept;--求薪水值最高的前五名雇员;select ename,sal from (select ename,sal from emp order by sal desc ) where rownum<6;/--求薪水值第六名到第十名的雇员;select ename,sal from (select ename,sal,rownum r from (select ename ,sal from emp order by sal desc) )where r>5 and r <11;//*** 有3个表S,C,SC* S(SNO,SNAME)代表(学号,姓名);* C(CNO,CNAME,CTEACHER)代表(课号,课名,教师)* SC(SNO,CNO,SCGRADE)代表(学号,课号,成绩)* 问题:1,找出没选过“黎明”老师的所有学生姓名。* 2,列出2门以上(含2门)不及格学生姓名及平均成绩。* 3,既学过1号课程又学过2号课程所有学生的姓名。* 请用标准SQL语言写出答案。*/答案:1,select sname from s join sc on (s.sno = sc.sno) join c on (c.cno = sc.cno) where c.cteacher <> '黎明';2,--学生姓名select sname from s where s.sno in ( select sno from sc where sc.scgrade < 60 group by sco having count(*) >=2 );--平均成绩select avg(scgrade) from sc where sc.sno in ( select sno from sc where sc.scgrade < 60 group by sco having count(*) >=2 ) group by sc.sno;3,select sname from s where s.sno in (select sno from sc where sc.cno =1 and sno in ( selcet distinct sno from sc where cno = 2 ) );--一个事务(transaction)起始于一条dml语句(insert,update,delete),终止于①commit语句;②ddl语句(create,drop)和dcl语句(grant,remove);正常断开连接时,transaction自动提交,非正常断开连接,transaction自动回滚。--创建一张学生表 --约束类型:不为空约束,主键约束,外键约束,唯一性约束,check约束create table stu1 --字段级约束条件( id number(6), name varchar2(20) constraint stu_name_nn not null,--不为空约束 sex number(1), age number(3), sdate date, grade number(2) default 1, class number(4) references class(id),--外键约束,外键约束被参考条件必须是主键 email varchar2(50) unique --唯一约束条件);create table stu ( id number(6) primary key,--主键约束 name varchar2(20) constraint stu_name_nn not null,--不为空约束 sex number(1), age number(3), sdate date, grade number(2) default 1, class number(4), email varchar2(50), constraint stu_name_email_uni unique(name,email), --表级约束 constraint stu_class_fk_foregin key (class) references class(id)); create table class( id number(4) primary key, name varchar2(20) not null);alert table stu add(addr varchar2(100));alert table stu drop (addr);alert table stu modify (addr varchar2(120));alert table stu add constraint stu_class_fk foreign key (class) references class(id);--创建与删除索引create index idx_stu_email on stu (email);drop index idx_stu_email;/三范式:①要有主键,列不可分;②不能存在部分依赖;(联合主键情况下,非主键列不能部分依赖于主键)即每张表只描述一件事③确保每列都和主键列直接相关,而不是间接相关。--pl/sql结构化语言--变量声明的规则:1,变量名不能使用保留字,如from、select等;2,第一个字符必须是字母;3,变量名最多包含30个字符;4,不要与数据库的表或者列同名;5,每一行只能声明一个变量。--常用变量类型:1,binary_integer,整数,主要用来计数而不是用来表示字段类型;2,number,数字类型;3,char,定长字符串;4,varchar2,变长字符串;5,date,日期;6,long,长字符串,最长2GB;7,boolean,布尔类型,可以取值为true、false和null值。--最简单的语句块begin dbms_output.put_line('HelloWorld');end;/在dos命令窗口输出命令:set serveroutput on;--简单的PL/SQL语句块declare v_name varchar2(20);begin v_name := 'myname'; dbms_output.put_line(v_name);end;/--异常处理declare v_num number := 0;begin v_num := 2/v_num; dbms_output.put_line(v_num);exception when others then dbms_output.put_line('error');end;/--变量声明declare v_temp number(1); v_count binary_integer := 0; v_sal number(7,2) := 4000.00; v_date date := sysdate; v_pi constant number(3,2) := 3.14; v_valid boolean := false; v_name varchar2(20) not null := 'MyName';begin dbms_output.put_line('v_temp value:' || v_sal);end;/注意:dbms_output.put_line命令不能打印布尔类型。--变量声明,使用%type属性declare v_empno number(4); v_empno2 emp.empno%type; v_empno3 v_empno2%type;begin dbms_output.put_line('Test');end;/--Table变量类型,相当于java中的数组。declare type type_table_emp_empno is table of emp.empno%type index by binary_integer; v_empnos type_table_emp_empno;begin v_empnos(0) := 7369; v_empnos(2) := 7839; v_empnos(-1) := 9999; dbms_output.put_line(v_empnos(-1));end;/--Table类型实例讲解使用记录类型变量只能保存一行数据,这限制了SELECT语句的返回行数,如果SELECT语句返回多行就会错。Oracle提供了另外一种自定义类型,也就是表类型,它是对记录类型的扩展,允许处理多行数据,类似于表。创建表类型的语法如下:TYPE table_name IS TABLE OF data_type [ NOT NULL ]INDEX BY BINARY_INTEGER ;语法说明如下:--table_name 创建的表类型名称。--IS TABLE 表示创建的是表类型。--data_type 可以是任何合法的PL/SQL数据类型,例如varchar2。--INDEX BY BINARY_INTEGER 指定系统创建一个主键索引,用于引用表类型变量中的特定行。使用表类型的例子: 例一:直接对表类型变量赋值declaretype my_emp is table of scott.emp%rowtypeindex by binary_integer;new_emp my_emp;beginnew_emp(1).empno:=6800;new_emp(1).ename:='tracy';new_emp(1).job:='clerk';new_emp(1).sal:=2500;new_emp(2).empno:=6900;new_emp(2).ename:='luck';new_emp(2).job:='manager';new_emp(2).sal:=4000;dbms_output.put_line(new_emp(1).empno ||','||new_emp(1).ename||','||new_emp(1).job||','||new_emp(1).sal);dbms_output.put_line(new_emp(2).empno ||','||new_emp(2).ename||','||new_emp(2).job||','||new_emp(2).sal);end;/例二:使用表类型变量的方法:变量名.方法名[索引号]declaretype my_emp is table of scott.emp%rowtypeindex by binary_integer;new_emp my_emp;beginnew_emp(1).empno:=6800;new_emp(1).ename:='tracy';new_emp(1).job:='clerk';new_emp(1).sal:=2500;new_emp(2).empno:=6900;new_emp(2).ename:='luck';new_emp(2).job:='manager';new_emp(2).sal:=4000;dbms_output.put_line(new_emp.first||','||new_emp.count||','||new_emp.last);dbms_output.put_line(new_emp(1).empno ||','||new_emp(1).ename||','||new_emp(1).job||','||new_emp(1).sal);dbms_output.put_line(new_emp(2).empno ||','||new_emp(2).ename||','||new_emp(2).job||','||new_emp(2).sal);--new_emp.delete(1);dbms_output.put_line(new_emp.next(1));end;//例三:与游标结合使用declaretype my_emp is table of scott.emp%rowtypeindex by binary_integer;new_emp my_emp;v_num number:=0;cursor cur_emp is select empno,ename,job,sal from scott.emp;beginfor v_emp in cur_emp loopv_num:=v_num+1;select * into new_emp(v_num) from scott.empwhere ename=v_emp.ename;end loop;for i in 1..new_emp.count loopdbms_output.put_line(new_emp(i).empno ||','||new_emp(i).ename||','||new_emp(i).job||','||new_emp(i).sal);end loop;end;/注意:不能直接对表变量赋值:select * into new_emp from scott.emp where deptno=30; 这种赋值方法是错的,赋值需要使用下标,如上面的的例子。--Record变量类型declare type type_record_dept is record ( deptno dept.deptno%type, dname dept.dname%type, loc dept.loc%type ); v_temp type_record_dept;begin v_temp.deptno := 50; v_temp.dname := 'aaaa'; v_temp.loc := 'bj'; dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);end;/--使用%rowtype声明record变量declare v_temp dept%rowtype;begin v_temp.deptno := '50'; v_temp.dname := 'aaaa'; v_temp.loc := 'bj'; dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);end;/--sql语句的运用--select语句declare v_ename emp.ename%type; v_sal emp.sal%type;begin select ename,sal into v_ename,v_sal from emp where empno = 7369; dbms_output.put_line(v_ename || ' ' || v_sal);end;/--select语句declare v_emp emp%rowtype;begin select * into v_emp from emp where empno = 7369; dbms_output.put_line(v_emp.ename);end;/--insert语句declare v_deptno dept.deptno%type := 50; v_dname dept.dname%type := 'aaaa'; v_loc dept.loc%type := 'bj';begin insert into dept values(v_deptno,v_dname,v_loc); commit;end;/--update语句declare v_deptno emp.deptno%type := 50; v_count number;begin update emp set sal = sal/2 where deptno = v_deptno; --select deptno into v_deptno from emp where empno = 7369; --select count(*) into v_count from emp; dbms_output.put_line(sql%rowcount || '条记录被影响'); commit;end;/注意:sql代表刚刚执行过的语句,rowcount代表刚执行过的语句影响的记录。--PL/SQL中执行DDL语句begin execute immediate 'create table T (nnn varchar2(20) default ''aaa'' )';end;/--if语句--取出7369的薪水,如果<1200,则输出'low',如果<2000,则输出'middle',否则'high';declare v_sal emp.sal%type;begin select sal into v_sal from emp where empno = 7369; if(v_sal<1200) then dbms_output.put_line('low'); elsif(v_sal<2000) then dbms_output.put_line('middle'); else dbms_output.put_line('high'); end if;end;/--循环--loop when循环,相当于java中的do..while循环declare i binary_integer := 1;begin loop dbms_output.put_line(i); i := i+1; exit when (i >= 11); end loop;end;/--while循环declare j binary_integer := 1;begin while j < 11 loop dbms_output.put_line(j); j := j+1; end loop;end;/--for循环declare k binary_integer := 1;begin for k in 1..10 loop dbms_output.put_line(k); end loop;end;/declare k binary_integer := 1;begin for k in reverse 1..10 loop --逆序 dbms_output.put_line(k); end loop;end;/--错误处理declare v_temp number(4);begin select empno into v_temp from emp where deptno = 10;exception when too_many_rows then dbms_output.put_line('太多记录了'); when others then dbms_output.put_line('没数据');end;/declare v_temp number(4);begin select empno into v_temp from emp where empno = 2222;exception when no_data_found then dbms_output.put_line('没数据');end;/--创建错误日志create table errorlog( id number primary key, errcode number, errmsg varchar2(1024), errdate date);/create sequence seq_errorlog_id start with 1 increment by 1;/declare v_deptno dept.deptno%type := 10; v_errcode number; v_errmsg varchar2(1024);begin delete from dept where deptno = v_deptno; commit;exception when others then rollback; v_errcode := SQLCODE; v_errmsg := SQLERRM; insert into errorlog values (seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate); commit;end;/--游标declare cursor c is --声明游标 select * from emp; v_emp c%rowtype;begin open c; --打开游标 fetch c into v_emp; dbms_output.put_line(v_emp.ename); close c; --关闭游标end;/--游标,循环declare cursor c is select * from emp; v_emp c%rowtype;begin open c; loop fetch c into v_emp; exit when (c%notfound); dbms_output.put_line(v_emp.ename); end loop; close c;end;/--while循环declare cursor c is select * from emp; v_emp emp%rowtype;begin open c; fetch c into v_emp; while (c%found) loop dbms_output.put_line(v_emp.ename); fetch c into v_emp; end loop; close c;end;/--for循环declare cursor c is select * from emp;begin for v_emp in c loop dbms_output.put_line(v_emp.ename); end loop;end;/--带参数的游标declare cursor c(v_deptno emp.deptno%type, v_job emp.job%type) is select ename,sal from emp where deptno = v_deptno and job = v_job;begin for v_temp in c(30,'CLERK') loop dbms_output.put_line(v_temp.ename); end loop;end;/--可更新的游标declare cursor c is select * from emp2 for update;begin for v_temp in c loop if (v_temp.sal < 2000) then update emp2 set sal = sal * 2 where current of c; elsif (v_temp.sal = 5000) then delete from emp2 where current of c; end if; end loop; commit;end;/--oracle集合类型的first、next、prior、 last方法创建一个存储过程,用于产生oracle三种集合类型的数据 create or replace procedure plch_mainis TYPE NESTED_T IS TABLE OF NUMBER;--嵌套表类型 TYPE ASSOCIATIVE_T IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; --索引表类型 TYPE VARRAYS_T IS VARRAY(10) OF NUMBER; --变长数组类型 lst_nested nested_t:=nested_t(-2000,null,3500); lst_associative associative_t;--索引表类型的集合不能直接赋值 可以从其特性来理解(可以是稀疏排列的) lst_varrays varrays_t:=varrays_t(-2000,null,3500);begin lst_associative(1):=-2000; lst_associative(3):=null;--注意,这里没有lst_associative数组第二个元素 lst_associative(4):=3500; dbms_output.put_line('以下为嵌套表的输出内容:'); dbms_output.put_line('lst_nested(1) :'||lst_nested(1)); dbms_output.put_line('lst_nested(2) :'||lst_nested(2)); dbms_output.put_line('lst_nested(3) :'||lst_nested(3)); dbms_output.put('lst_nested.first-1:'); dbms_output.put_line(lst_nested.first-1); dbms_output.put_line('lst_nested.first :'||lst_nested.first); dbms_output.put_line('lst_nested.prior(1):'||lst_nested.prior(1)); dbms_output.put_line('lst_nested.next(1):'||lst_nested.next(1)); dbms_output.put_line('lst_nested.next(2):'||lst_nested.next(2)); dbms_output.put_line('lst_nested.next(3):'||lst_nested.next(3)); dbms_output.put_line('lst_nested.last :'||lst_nested.last); dbms_output.put_line('以下为索引表的输出内容:'); dbms_output.put_line('lst_associative(1) :'||lst_associative(1)); dbms_output.put_line('lst_associative(3) :'||lst_associative(3)); dbms_output.put_line('lst_associative(4) :'||lst_associative(4)); dbms_output.put('lst_associative.first-1:'); dbms_output.put_line(lst_associative.first-1); dbms_output.put_line('lst_associative.first :'||lst_associative.first); dbms_output.put_line('lst_associative.prior(1):'||lst_associative.prior(1)); dbms_output.put_line('lst_associative.next(1):'||lst_associative.next(1)); dbms_output.put_line('lst_associative.next(2):'||lst_associative.next(2)); dbms_output.put_line('lst_associative.next(4):'||lst_associative.next(4)); dbms_output.put_line('lst_associative.last :'||lst_associative.last); dbms_output.put_line('以下为变长数组的输出内容:'); dbms_output.put_line('lst_varrays(1) :'||lst_varrays(1)); dbms_output.put_line('lst_varrays(2) :'||lst_varrays(2)); dbms_output.put_line('lst_varrays(3) :'||lst_varrays(3)); dbms_output.put('lst_varrays.first-1:'); dbms_output.put_line(lst_varrays.first-1); dbms_output.put_line('lst_varrays.first :'||lst_varrays.first); dbms_output.put_line('lst_varrays.prior(1):'||lst_varrays.prior(1)); dbms_output.put_line('lst_varrays.next(1):'||lst_varrays.next(1)); dbms_output.put_line('lst_varrays.next(2):'||lst_varrays.next(2)); dbms_output.put_line('lst_varrays.next(3):'||lst_varrays.next(3)); dbms_output.put_line('lst_varrays.last :'||lst_varrays.last);end;/ 存储过程编译成功,以下调用并查看结果。特别注意三种数组中第一个元素的prior方法、最后一个元素的next方法都是可以执行出来的;并且索引表类型中未定义的元素序列也有prior、next方法。 SQL> exec plch_main; 以下为嵌套表的输出内容:lst_nested(1) :-2000lst_nested(2) :lst_nested(3) :3500lst_nested.first-1:0lst_nested.first :1lst_nested.prior(1):lst_nested.next(1):2lst_nested.next(2):3lst_nested.next(3):lst_nested.last :3以下为索引表的输出内容:lst_associative(1) :-2000lst_associative(3) :lst_associative(4) :3500lst_associative.first-1:0lst_associative.first :1lst_associative.prior(1):lst_associative.next(1):3lst_associative.next(2):3lst_associative.next(4):lst_associative.last :4以下为变长数组的输出内容:lst_varrays(1) :-2000lst_varrays(2) :lst_varrays(3) :3500lst_varrays.first-1:0lst_varrays.first :1lst_varrays.prior(1):lst_varrays.next(1):2lst_varrays.next(2):3lst_varrays.next(3):lst_varrays.last :3 PL/SQL procedure successfully completed .first方法是数组中第一个元素的下标值(序列值).prior(n)方法是数组中第n个元素的前一个元素的下标值(序列值)
原标题:oracle基础学习
关键词:oracle