oracle随手笔记

oracle随手笔记 **?ØÓÚoracle×Ô?øµÄ?í***********************************8 emp?º empno?ºÔ????àºÅ?? ename?ºÔ???Ãû×Ö?? job?ºÔ?????ÖÖ?? mgr: ÉÏË??? hiredate?ºÈëÖ?Ê??ä??sal?º ?ù????×Ê?? comm?º ??Ìù?? deptno?ºËùÊô??ÃÅ?àºÅ?? dept?º deptno?º??ÃÅ?àºÅ?? dname?º??ÃÅÃû?Æ?? loc?ºµØÀíÎ?ÖÃ?? salgrade?º grade?º ??×ʵÈ???? losal?º×îµÍÏÞ?î?? hisal?º×î?ßÏÞ?î?? dual?º ϵÍ?×Ô?øµÄÒ?ÕÅ?Õ?í?? ?ÉÓÃÓÚ?ÆËãÊý?Ý?ºselect 2*3 from dual; **sql_function1********************************************************** select lower(ename) from emp; È??öµÄÃû×ÖÈ????ä?ÉÐ?Ð??? select ename from emp where lower(ename) like '_a%';È??öµÄÃû×Ö?ä?ÉÐ?Ð?ºó ??º?×ÖÄ?a select substr(ename, 2, 3) from emp;?ÓµÚ?þ?ö×Ö?û?Ø???ØÈ?Èý?ö×Ö?û?? select cha(65) from dual; ??Êý×Öת??Ϊ×Ö?û??ÏÔÊ?Ϊa???? select ascii('A') from dual; ??×Ö?ûת??ΪÊý×Ö?? select round(23.652) from dual; (ÏÔÊ?24) select round(23.652, 2) from dual; ??ÏÔÊ?23.65?? select round(23.652, -1) from dual; ??ÏÔÊ?20?? select to_char(sal, '$99,999.9999')from emp;Ç?ÖÆת??ΪÖ???µÄ?ñÊ??? select to_char(sal, 'L0000.0000')from emp;Í?ÉÏ?? select to_char(hiredate, YYYY-MM-DD HH:MI:SS) from emp; ?ÔÊ??ä?ñÊ?ÏÔÊ??? Àí?? select to_char(sysdate, YYYY-MM-DD HH:MI:SS) from emp; 12?øÖÆ?? select to_char(sysdate, YYYY-MM-DD HH24:MI:SS) from emp; 24?øÖÆ?? *********************************************************************** **sql_function2******************************************************* select ename, hiredate from emp where hiredate > to_date('1981-2-20' 12:34:52, 'YYYY-MM-DD HH24:MI:SS'); º?Êýto_date ??×Ö?ûת??ΪÊ??ä?ñÊ??? select sal from emp where sal > to_number('$1,250.00', '$9,999.99'); º?Êýto_number??×Ö?ûת??ΪÊý×Ö?ñÊ???ÒÔ×??È?Ï?? select ename sal*12 + nvl(comm 0) from emp; º?Êýnvl×?ÓÃΪµ?commΪnullµÄÊ?ºòµ?×???Àí???ÜÃâÁËcommΪnull?ø?á?û?øÀ?µÄ???ã?? ************************************************************************* **group_function******************************************************* select max(sal) from emp;Êä?öÐ?Ë?Öµ×î?ßµÄ?? select min(sal) from emp;Êä?öÐ?Ë?Öµ×îµÍµÄ?? select avg(sal) from emp;Êä?öÆ??ùÐ?Ë?Öµ?? select to_char(avg(sal),'99999999.99') from emp;??ÕÕÖ????ñÊ?Êä?öÆ??ùÐ?Ë? Öµ?? select round(avg(sal),2) from emp; ??È?µ?Ð?ÊýµãºóÃæ2Î??? select sum(sal) from emp; Êä?öÐ?Ë?ÖµµÄ×ܺÍ?? select count(*) from emp;Çó?öÒ???ÓÐ?àÉÙÌõ?ÇÂ??? select count(*) from emp where deptno = 10; Çó??ÃÅΪ10ºÅµÄ?ÇÂ?ÌõÊý?? select count(ename) from emp; ÇóÒ???ÓÐ???öÃû×Ö?? select count(comm) from emp; Çó?Ç?ÕcommµÄ?ÇÂ?ÌõÊý?? select count(deptno) from emp; select count(distinct deptno) from emp; ************************************************************************* **group_by************************************************************* select deptno, avg(sal) from emp group by deptno; ????ÃÅÐ?Ë?Æ??ù?Ö×é?? select deptno, job, max(sal) from emp group by deptno; ??×éºÏ?Ö×é?? select ename, max(sal) from emp where sal = (select max(sal) from emp); select ename max(sal) from emp group by deptno; ÕâÑùÊÇ?íÎóµÄ?? select deptno max(sal) from emp group by deptno; ÕâÑù?ÉÐÐ?? ************************************************************************ **having********************************************************** ****** select avg(sal), deptno from emp group by deptno; select avg(sal), deptno from emp group by deptno having avg(sal) > 2000; havingÊÇ?Ô?Ö×é?øÐÐÏÞÖÆ?? 1 select avg(sal) Ñ?Ôñ 2 from emp ?íÔ, 3 where sal > 1200 Ìõ?þ?ýÂË 4 group by deptno ?Ö×é 5 having avg(sal) > 1500 ?Ô?á?û?øÐÐÏÞÖÆ 6 order by avg(sal) desc ?Ô?úÉúµÄ?á?û?øÐÐÅÅÐò ********************************************************************** **×Ó?éÑ?************************************************************** select Óï?äÀïÃæÌ×ÁíÍâÒ??öselectÓï?ä?? select ename, sal from emp where sal > (select avg(sal) from emp); select ename,sal from emp join(select max(sal) max_sal, deptno from emp group by deptno) t on (emp.sal = t.max_sal and emp.deptno = t.deptno); **self_table********************************************************** ×ÔÁ??Ó?ºselect e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno; ?ÑÒ??ö?íµ??ÉÁ??öÀ?Ê?ÓÃ?? *********************************************************************** **sql1999_table_connections******************************************** 1999Äê?ê×??ºselect ename, dname from emp cross join dept; ?É?º ÐÂ?ºselect ename, dname from emp join dept on (emp.deptno = deptno); select ename, dname from emp join dept using(deptno);(ÁË?â???É????ÍÆ?öÊ? ÓÃ) select ename, grade from emp e join salgrade s on (e.sal between s.losal and s.hisal); ×óÍâÁ??Ó?ºselect e1.ename, e2.ename from emp e1 left join emp e2 on(e1.mgr = e2.empno); ÓÒÍâÁ??Ó?ºselect ename, dname from emp e right outer join dept d (e.deptno = d.deptno); È?ÍâÁ??Ó?ºselect ename, dname from emp e full join dept d (e.deptno = d.deptno); ************************************************************************ **Çó??ÃÅÆ??ùÐ?Ë?µÈ??**************************************************** select deptno, avg(grade) from (select deptno, ename, grade from emp join salgrade s on (t.avg_sal between s.losal and s.hisal)) t group by deptno; ************************************************************************* **??ÃÅÖÐÄÇÐ?ÈËÊÇ?,ÀíÈË************************************************** select ename from emp where empno in (select distinct mgr from emp); ************************************************************************ **??ÓÃ×éº?ÊýÇóÐ?Ë?µÄ×î?ßÖµ??ÃæÊÔÌâ??************************************ select distinct sal from emp where sal not in (select distinct e1.sal from emp e1 join emp e2 on (e1.sal =6 and r<=10; ÇóÐ?Ë?×î?ߵĵÚ6µ?µÚ10Ãû?ÍÔ??? ************************************************************************ **update******************************************************* update emp2 set sal = sal*2, ename=ename||'-' where deptno = 10; ?ü?Ä?? delete from emp2; É??ý?? **************************************************************** **????ÐÂ?íÓëÔ?Êø**************************************************** create table stu ( id number(6) primary key, //Ö??üÔ?Êø??Ö??ü?Ç?ÕÇÒÎ?Ò???Ò??ÉÒÔÐ?ÔÚºóÃæ constraint stu_id_pk primary key(id), name varchar2(20) constraint stu_name_nn not null, //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(email, name) //emailÓëname×éºÏ Î?Ò? ) Íâ?ü?Î??µÄÖµ?Î?ØÐëÊÇÖ??ü???ÓÉÏconstraint stu_class_fk foreign (class) reference class(id), ************************************************************************* **alterÐÞ?Ä?í?á??*************************************************** alter table stu add(addr varchar2(100)); //Ìí?Ó×Ö?Î alter table stu drop(asddr); alter table stu modify(asddr varchar2(50)); alter table stu drop constraint stu_class_fk; //É??ýÔ?ÊøÌõ?þ alter table stu add constraint stu_class_fk foreign key (class) reference class (id); //ÖØÐÂÌí?ÓÔ?ÊøÌõ?þ **************************************************************** **?éÑ?ϵÍ?µÄ?í********************************************** select table_name from user_tables; //µ?Ç?ÓÃ??ÏÂËùÓеÄ?í select view_name from user_views; //µ?Ç?ÓÃ??ϵÄÊÓÍ? select constraint_name??table_name from user_constraints; desc dictionary //Êý?Ý×Öµä?í ****************************************************************** **Ë?Òý******************************************************** create index idx_stu_email on stu(email); //????Ë?Òý drop index idx_stu_email; //É??ýË?Òý select index idx_name from user_indexes; select view_name from user_views; desc (ÊÓÍ?µÄÃû?Æ) create view v$_stu as select id, name, age from stu; Ö??øÓè?é??id??name??age µÄÈ?Á???????Ë?ÓÐÊý?Ý?? ********************************************************************* **sequenceÐòÁÐ***************************************************** create sequence seq; //????ÐòÁÐ select seq.nextval from dual; insert into article values (seq.nextval, 'a', 'b'); //Ìí?ÓÊý?Ý******************************************************** ************* **Èý??Ê?********************************************************** µÚÒ???Ê??ºÒªÓÐÖ??üÇÒÁÐ???É?Ö?? µÚ?þ??Ê??º??ÄÜ?æÔÚ???ÖÒÀÀµ?º?ÇÖ??üµÄ×Ö?Î??ÄÜÒÀÀµÓÚ×éºÏÖ??üµÄÒ????Ö ?? µÚÈý??Ê??º??ÄÜ?æÔÚ??µÝÒÀÀµ?? ****************************************************************** * **PL_SQLÓï?ä****************************************************** ******* ?òµ?Ð??ÌÐò?º SQL> set serveroutput on; SQL> begin dbms_output.put_line('HelloWorld!'); end; / HelloWorld! ??ÏÔÊ?µÄ?á?û?? ÔËÐÐÖÐdcomcnfgÃüÁîÊÇ?é??ϵÍ?×é?þ?þÎñ ?äÁ?ÉêÃ?µÄ?æÔò?º ?äÁ?Ãû??ÄÜÊ?ÓÃ??Áô×Ö??Èçfrom??selectµÈ µÚÒ??ö×Ö?û?ØÐëÊÇ×ÖÄ??? ?äÁ?Ãû×î?à?üº?30?ö×Ö?û ??ÒªÓëÊý?Ý?âµÄ?í?òÕßÁÐÍ?Ãû Ã?Ò?ÐÐÖ?ÄÜÉêÃ?Ò??ö?äÁ? ??ÓÃ?äÁ?µÄÀàÐÍ?º binary_integer: ÕûÊý??Ö?ÒªÓÃÀ??ÆÊý?ø??ÊÇÓÃÀ??íÊ?×Ö?ÎÀàÐÍ number?ºÊý×ÖÀàÐÍ char?º????×Ö?û?? varchar2?º?ä??×Ö?û?? date?ºÈÕÆÚ long?º??×Ö?û????×î??2G boolean?º???ûÀàÐÍ???ÉÈ?true??falseºÍnullÖµ?? ---Table?äÁ?ÀàÐÍ declare type_table_emp_empno is table of emp.empno%type index by binary_integer; begin v_empnos(0) :=7369; v_empnos(2) :=7839; v_empnos(-1) :=9999; dbms_output.put_line(v_empnos(-1)); end; ---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; ---Ê?ÓÃ%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; ---------------------------- 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; ebms_output.put_line(v_ename || ' ' || v_sal); end; / (ÏÔÊ?µÄ?á?ûΪ SMITH 800) ---------------------------- declare v_deptno emp2.deptno%type :=10; v_count number; begin --update emp2 set sal = sal/2 where deptno = v_deptno; select count(*) into v_count from emp2; dbms_output.put_line(sql%rowcount || 'Ìõ?ÇÂ???Ó?Ïì'); commit; end; --ddlÓï?ä--------------------------- begin execute immediate 'create table T (nnn varchar2(20) default "aaa")'; end; / --------------------------------------- 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'); elseif (v_sal <2000) then dbms_output.put_line('middle'); else dbms_output.put_line('hign'); end if; end; / ----------------------------------------------- declare v_sal emp.sal%type; begin select sal into v_sal from emp where empno =7839; if(v_sal < 2500) then update emp set sal = sal*2 where empno = 7839; dbms_output.put_line(sal); if(v_sal = 2500) then dbms_output.put_line(sal); then update emp set sal = sal/2 where empno = 7839; dbms_output.put_line(sal); end if; end; --?íÎó??Àí-------------------------------------------------------- -- 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_errsmg :=SQLRRM; insert into errorlog values (seq_errorlog_id.nextval, v_errmsg, sysdate); commit; 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; / --Ê?ÓÃforÑ,??µÄÓÎ?ê declare cursor c is select * from emp; begin for v_emp in c loop dbms_output.put_line(v_emp.ename) (v_empÔÚÇ?ÃæÒÑ?,ÉêÃ?) 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; --v_temp c%rowtype; begin for v_temp in c(30, 'CLERK') loop dbms_output.put_line(v_temp.ename); --?æ???ý?Ì µ?ÓÐ?àÒëµÄ?íÎóÊ? ??ÓÃÃüÁîshow error ?ÉÏÔÊ??ö?íµÄµØ???? create or replace procedure p is cursor c is select * from emp2 for update; begin for v_emp in c loop if (v_emp.deptno = 10) then update emp2 set sal = sal +10 where current of c; elseif (v_emp.deptno = 20) then update emp2 set sal = sal +20 where current of c; else update emp2 set sal = sal +20 where current of c; end if; end loop; commit; end; select * from emp2; begin p; end; --?ø?ÎÊýµÄ?æ???ý?Ì create or replace procedure p (v_a in number, v_b number, v_ret out number, v_temp in out number) is . . . ********************************************************************* **trigger************************************************************ ????????Æ??º create or replace trigger trig after update on dept for each row begin update emp set deptno = :NEW.deptno where deprno = :OLD.deptno; end; / È?ºó?Í?ÉÒÔÕâÑù×Ó?ü?ÄÁË?º update emp set deptno = 99 where deptno = 10; ************************************************************************ **Ê?×??á??µÄ?æ??ÓëÕ?Ê?************************************************** create table article ( id number primary key, cont varchar2(4000) pid number, isleaf number(1), --0?ú?í?ÇÒ?×Ó?Úµã??1?ú?íÒ?×Ó?Úµã alevel number(2), ); insert into article values (1, 'ÂìÒÏ?óÕ??óÏó', 0, 0, 0); insert into article values(2,'?óÏó???òÅ?ÏÂÁË'??1??0??1)?? insert into article values(3,'ÂìÒÏÒ???ºÃ?ý'??2??1??2)?? insert into article values(4,'Ï?˵'??2??0??2)?? insert into article values(5,'Ã?ÓÐÏ?˵'??4??1??3)?? insert into article values(6,'ÔõÃ??ÉÄÜ'??1??0??1)?? insert into article values(7,'ÔõÃ?Ã?ÓÐ?ÉÄÜ'??6??1??2)?? insert into article values(8,'?ÉÄÜÐÔÊǺÜ?óµÄ'??6??1??2)?? insert into article values(9,'?óÏó?øÒ?ÔºÁË'??2??0??2)?? insert into article values(10,'??Ê?ÊÇÂìÒÏ'??9??1??3)?? commit;