Oracle笔记
消除重复行 SELECT DISTINCT DEPTNO FROM EMP 查看表结构 (CMD命令中)DISCRIBE EMP 优先级 NOT>AND>OR ORDER BY ASC升序 DESC降序 LOWER 全部转换成小写 UPPER 全部转换成大写 INNITCAP 首字母大写其他小写 CONCAT 连接两个值等同于|| SUBSTR (expression,n1,n2)截取字符从n1开始,n2个字符,如果n1为负数则从右往左数 LENGTH(expression) 获取字符长度 INSTR (s1,s2,n1,n2)返回s2在s1中从n1开始第n2次出现的位置,n1,n2可以不写,默认值为1 LPAD (S1,N1,S2)返回S1被S2从左面填充到N1长度的字符串 SELECT LPAD(‘DE’,6,’*’) FROM DUAL 结果 ****DE RPAD(S1,N1,S2) 返回S1被S2从右面填充到N1长度的字符串 SELECT LPAD(‘DE’,6,’*’) FROM DUAL 结果 DE**** TRIM去除字符串头部或尾部(头尾)的字符 SELECT TRIM(‘S’FROM ‘SSMITH’) FROM DUAL 结果 MITH REPLACE(S1,S2,S3)把S1中的S2用S3替换 ROUND(expression,n1) 四舍五入到n1位,正数表示小数点后面,负数表示小数点前面 TRUNC(expression,n1) 截取到第n1位正数表示小数点后面,负数表示小数点前面
select to_char(23.45,'0000.000') from dual 结果: 0023.450
select to_char(23.45,'9999.999') from dual 结果: 23.450
select to_char(123123,'99,999,999.99') from dual 结果: 123,123.00
select to_char(123123.3,'FM99,999,999.99') from dual 结果:123,123.3
select to_char(123123.3,'$99,999,999.99') from dual 结果: $123,123.30
select to_char(123123.3,'L99,999,999.99') from dual 结果: ?23,123.30
select to_char(123123.3,'99,999,999.99C') from dual 结果: 123,123.30GBP MOD(m,n)返回m除以n的余数 日期函数(格式碼CC世纪,YY,MM,DD,HH,MI,SS) SYSDATE 返回系统日期 MONTHS_BETWEEN 返回两个日期相差的月数 SELECT MONTHS_BETWEEN(HIREDATE1,HIREDATE2) FROM DUAL ADD_MONTHS 返回指定日期加上相应月数后的日期 SELECT ADD_MONTHS(HIREDATE,3)FROM DUAL NEXT_DAY 返回某一日期指定的下一指定日期 SELECT NEXT_DAY(‘1,6月,2017’,‘星期一’)FROM DUAL 返回2017年6月1号的下一个星期一是几号 LAST_DAY 返回指定日期当月最后一天的日期 SELECT LAST_DAY(‘1,6月,2017’)FROM DUAL ROUND(date[,’fmt’]) 将date按照fmt指定的格式四舍五入,如果没有指定fmt则默认为DD,将date四舍五入为最近的一天 TRUNC(date[,’fmt’]) 将date按照fmt指定的格式截断,如果没有指定fmt则默认为DD,将date四舍五入为最近的一天 EXTRACT 返回年或月或日 SELECT EXTRACT(MONTH FROM HIREDATE) FROM EMP SELECT TO_CHAR(HIREDATE,'YY/MM/DD')FROM EMP指定格式转换 YYYY 完整数字表示的年份 YEAR 年份的英文表示 MM 两位数字表示月份 MONTH 月份的全名 DAY 星期几 DY 三个英文缩写表示星期 通用函数 NAL(N1,N2)如果N1不为null则返回N1,否则返回N2 NAL(N1,N2,N3)如果N1不为null则返回N2,否则返回N3 NULLIF(N1,N2)比较两个表达式,如果相等返回null,否则返回N1 SELECT COALESCE(COMM,0) COMM,DEPTNO FROM EMP返回第一个不为空的参数,参数个数不受限制 ************************************************** select ENAME,DEPTNO, (CASE DEPTNO WHEN 10 THEN '销售部' WHEN 20 THEN '技术部' WHEN 30 THEN '管理部' else 'WU' END) DEPTNAME FROM DEPT *************************************************** select ENAME,DEPTNO, DECODE( DEPTNO 10, '销售部' 20, '技术部' 30, '管理部' 'WU' ) FROM EMP 99语法: SELECT TABLE1.COLUM1 ,TABLE2. COLUM2 FROM TABLE1 CROSS JOIN TABLE2 交叉连接 ,产生笛卡尔积 NATURAL JOIN TABLE2 自然连接 JOIN TABLE2 USING COLUM JOIN TBLE2 ON TABLE1.COLUM=TABLE2.COLUM LEFT/RIGHT/FULL OUTER /JOIN TABLE2 ON TABLE1.COLUM=TABLE2.COLUM 全外连接为99独有 92语法 SELECT TABLE1.COLUM1 ,TABLE2. COLUM2 FROM TABLE1 WHERE TABLE1.COLUM=TABLE.COLUM 自然连接 WHERE TABLE1.COLUM=TABLE.COLUM(+) 左外连接 WHERE TABLE1.COLUM(+)=TABLE.COLUM 右外连接 授予视图权限: 登陆system 账户,密码oracle 语法:grant create view to scott DDL(schema data definition)数据库模式定义语言create alter drop truncate DCL(data control language) 数据库控制语言grant deny revoke DML数据操作语言 insert delete update select 数值类型及函数 number是oracle中的数据类型 Precision代表精度,sacle代表小数位数;Precision范围[1,38],scale范围[-84,127] 常用方法: abs()求绝对值:select abs(-3) as absvalue from dual round()四舍五入:select round(33.54,1) as roundvalue from dual,第二个参数为保留到小数第几位 ceil()向上取整:select ceil(33.34) as roundvalue from dual 结果:234 floor()向下取整:select floor(33.34) as roundvalue from dual 结果:33 mod()取模:select mod(5,3) as roundvalue from dual 结果:2 Sign()正负性:select sign(-4) as roundvalue from dual正数为1,负数为-1 Sqrt()求平方根:select sqrt(9) as sqrtvalue from dual 结果:3 Power()求乘方:select power(2,3) as powervalue from dual 结果:8 Trunc截取:select trunc(274364.3645,3) as truncvalue from dual 结果:274364.364
select to_char(23.45,'0000.000') from dual 结果: 0023.450 select to_char(23.45,'9999.999') from dual 结果: 23.450 select to_char(123123,'99,999,999.99') from dual 结果: 123,123.00 select to_char(123123.3,'FM99,999,999.99') from dual 结果:123,123.3 select to_char(123123.3,'$99,999,999.99') from dual 结果: $123,123.30 select to_char(123123.3,'L99,999,999.99') from dual 结果: ?23,123.30 select to_char(123123.3,'99,999,999.99C') from dual 结果: 123,123.30GBP
set serverout on;让控制台输出信息 例1: set serverout on; begin dbms_output.put_line('hello'); end; / 例2: set serverout on; --声明变量 declare n number:=1; v varchar2(20):='world'; begin dbms_output.put_line('hello'||n||v); end; / 例3: set serverout on; declare emp_count number; begin select count(*) into emp_count from emp where sal>=3000; if emp_count>0 then dbms_output.put_line('有薪资大于3000的员工'||emp_count||'个'); else dbms_output.put_line('没有薪资大于3000的员工'||emp_count||'个'); end if; end; / 例4:(switch case) set serverout on; declare emp_count number; begin select count(*) into emp_count from emp where sal>=3000; case emp_count when 0 then dbms_output.put_line('没有薪资超过3000的员工'); when 1 then dbms_output.put_line('有一个薪资超过3000的员工'); when 2 then dbms_output.put_line('有两个薪资超过3000的员工'); when 3 then dbms_output.put_line('有三个薪资超过3000的员工'); else dbms_output.put_line('有超过三个薪资超过3000的员工'); end case; end; / 例5:(if条件) set serverout on; declare g_id number:=2; g_losal number; g_hisal number; begin loop if(g_id>4) then exit; end if; select hisal,losal into g_hisal,g_losal from salgrade where grade=g_id; dbms_output.put_line('id号'||g_id||'最低工资'||g_losal|| '最高工资'||g_hisal); g_id:=g_id+1; end loop; end; / 例6:(while循环) set serverout on; declare g_id number:=2; g_losal number; g_hisal number; begin while g_id<5 loop select hisal,losal into g_hisal,g_losal from salgrade where grade=g_id; dbms_output.put_line('id号'||g_id||'最低工资'||g_losal|| '最高工资'||g_hisal); g_id:=g_id+1; end loop; end; / 例7:(for循环) set serverout on; declare g_id number:=2; g_losal number; g_hisal number; begin for g_id in 2..4 loop select hisal,losal into g_hisal,g_losal from salgrade where grade=g_id; dbms_output.put_line('id号'||g_id||'最低工资'||g_losal|| '最高工资'||g_hisal); end loop; end; / 游标简介:使用游标我们可以具体操作数据,比如对查询的结果,行,列进行更加细致的处理,以及对其他DML进行判断等操作。 显示游标: 例1: set serverout on; declare cursor cu_emp is select empno,ename,sal from emp; e_no number; e_name varchar2(10); e_sal number; begin open cu_emp; fetch cu_emp into e_no,e_name,e_sal; while cu_emp%found loop dbms_output.put_line('编号: '||e_no||', 姓名: '||e_name||', 薪资: '||e_sal); fetch cu_emp into e_no,e_name,e_sal; end loop; close cu_emp; end; / 例2: set serverout on; declare cursor cu_emp is select empno,ename,sal from emp; --动态指定类型 e_no emp.empno%type; e_name emp.ename%type; e_sal emp.sal%type; begin open cu_emp; fetch cu_emp into e_no,e_name,e_sal; while cu_emp%found loop dbms_output.put_line('编号: '||e_no||', 姓名: '||e_name||', 薪资: '||e_sal); fetch cu_emp into e_no,e_name,e_sal; end loop; close cu_emp; end; / 例3: set serverout on; declare cursor cu_emp is select * from emp; e emp%rowtype; begin open cu_emp; fetch cu_emp into e; while cu_emp%found loop dbms_output.put_line('编号: '||e.empno||', 姓名: '||e.ename||', 薪资: '||e.sal); fetch cu_emp into e; end loop; close cu_emp; end; / 例4: set serverout on; declare cursor cu_emp is select * from emp where sal>2000 and sal<3000; e emp%rowtype; begin open cu_emp; fetch cu_emp into e; while cu_emp%found loop dbms_output.put_line('编号: '||e.empno||', 姓名: '||e.ename||', 薪资: '||e.sal); fetch cu_emp into e; end loop; close cu_emp; end; / 隐式游标: 隐式游标的属性 返回值类型 意义 SQL%ROWCOUNT 整型 代表DML语句成功执行的行数 SQL%FOUND 布尔型 值为TRUE代表插入,删除,更新或单行查询操作成功 SQL%NOTFOUND 布尔型 与SQL%FOUND返回值属性相反 SQL%ISOPEN 布尔型 DML执行过程中为真,结束为假 例1: set serverout on; begin update emp set sal=1000 where empno=1002; if sql%rowcount=1 then dbms_output.put_line('更新成功'); else dbms_output.put_line('更新失败'); end if; end; 动态游标 强类型动态游标 弱类型动态游标 例1: set serverout on; declare type customType is ref cursor; e_count number; e emp%rowtype; s salgrade%rowtype; cType customType; begin select count(*) into e_count from emp where job='PRESIDENT'; if e_count=0 then open cType for select * from salgrade; fetch cType into s; while cType%found loop dbms_output.put_line('等级: '||s.grade||', 最低薪资:'||s.losal||', 最高薪资:'||s.hisal); fetch cType into s; end loop; close cType; else open cType for select * from emp; fetch cType into e; while cType%found loop dbms_output.put_line('编号: '||e.empno||', 姓名: '||e.ename||', 薪资: '||e.sal); fetch cType into e; end loop; close cType; end if; end; / 触发器 例1: create trigger tr_book before insert or update on book begin if user!='cc' then raise_application_error(-2001,'权限不足'); end if; end; / 例2: create trigger tr_book_log after insert or update or delete on book begin if inserting then insert into book_log values(user,'insert',sysdate); else if updating then insert into book_log values(user,'update',sysdate); else if deleting then insert into book_log values(user,'delete',sysdate); end if; end if; end if; end; / 例3: create trigger tr_bkk_add after insert on book for each row begin update booktype set num=num+1 where id=:new.typeid; end; / 例4: create or replace trigger tr_bkk_delete after delete on book for each row begin update booktype set num=num-1 where id=:old.typeid; end; / 禁用触发器:alter trigger triggerName disabled; 解禁触发器:alter trigger triggerName enable; 自定义函数: 例1: create function getBookCount return number as begin declare book_count number; begin select count(*) into book_count from book; return book_count; end; end getBookCount; 调用: set serveroutput on; begin dbms_output.put_line('表boo有 '||getBookCount()||'条数据。'); end; 例2: create function getRecordCount(tablename varchar2) return number as begin declare record_count number; query_sql varchar2(100); begin query_sql:='select count(*) from '||tablename; execute immediate query_sql into record_count; return record_count; end; end getRecordCount; 调用: set serveroutput on; begin dbms_output.put_line('表boo有 '||getRecordCount('book')||'条数据。'); end; 存储过程: 例1: create procedure addBook(bookname in varchar2,typeid in number) as begin declare maxid number; begin select max(id) into maxid from book; insert into book values(maxid+1,bookname,typeid); commit; end; end addBook; / 调用存储过程: execute addBook('德语',2); 例2: create or replace procedure addBook2(bn in varchar2,typeid in number) as begin declare maxid number; n number; begin select count(*) into n from book where bn=bookname; if n>0 then return; end if; select max(id) into maxid from book; insert into book values(maxid+1,bn,typeid); commit; end; end addBook2; /