oracle学习--基本语法

1，大小写转换

select upper('Hello') 转大写,lower('Hello') 转小写,initcap('hello') 首字母大写 from dual;

2，截取字符串

--从第二位开始取
select substr('hello',2) from dual; --ello
--从第二位开始取3位
select substr('hello',2,3) from dual; --ell
--第一个o的位置
select instr('hello world','o') 位置 from dual;--5
--长度
select length('你好') 字符,lengthb('你好') 字节 from dual; --2，4

3，替换指定字符

select replace('Hello World' ,'l','*') from dual; --He**o Wor*d

4，四舍五入

select round(45.926,2) 两位小数,round(45.926,1) 一位小数 from dual;
select trunc(45.926,2) 保留二位,trunc(45.926,1) 保留一位 from dual;---不四舍五入

5，时间

select sysdate  from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')  from dual;
select add_months(sysdate,56) from dual;--56 个月后
select last_day(sysdate) from dual;
select next_day(sysdate,'星期日') from dual;--下一个星期日

6，nvl2(a,b,c) 当a=null的时候，返回c；否则返回b

select sal*12+nvl2(comm,comm,0) from emp;

nullif(a,b) 当a=b的时候，返回null；否则返回a

select nullif('abc','abc') 值 from dual;

7，分页查询

select *
 from 	(select rownum r,e1.*
	 from (select * from emp order by sal) e1
 	 where rownum <=8
	)
 where r >=5;

8，创建视图

create or replace view empinfoview
as
select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname
from emp e, dept d
where e.deptno=d.deptno
with read only;

9，创建索引

create index myindex
on emp(deptno);

10，创建同义词

 create synonym myemp1 for hr.employees;

11，存储过程

--普通变量(char,varchar2,date,number,boolean,long)
	--变量赋值
		-- :=
		-- select 值 into 变量名 from dual;
--特殊变量类型(引用型变量,记录型变量)
	--引用型变量
		--通过表名.列名%type 指定变量的类型和长度
	--记录型变量
		--变量名称  表名%rowtype
declare 
  username varchar2(200) :='张三';
  age number;
  addr varchar2(200);
  userid tm_user.s_userid%type;
  tuser tm_user%rowtype;
begin
  age:=18;
  select '中国' into addr from dual;
  select s_userid into userid from ftzfe.tm_user where s_username='sh001';
  select * from tuser from ftzfe.tm_user where s_username='sh001';
  if age>18 then
  	dbms_output.put_line(userid||'-'||username||'-'||'太大了');
  elsif age<18 then
  	dbms_output.put_line(userid||'-'||username||'-'||'太小了');
  else
  	dbms_output.put_line(userid||'-'||username||'-'||'刚刚好');
  end if;
end;

-- 循环
declare
	my_num number:=1;
begin
	loop
	exit when my_num>10;
		dbms_output.put_line(my_num);
		my_num:=my_num+1;
	end loop;
end;

--游标
--游标使用
	--声明-》打开-》读取-》关闭
--语法
	--cursor 游标名[(参数列表)] is 查询语句
declare
	cursor tmuser is select s_userid,s_username from ftzfe.tm_user;
	suserid tm_user.s_userid%type;
	susername tm_user.s_username%type;
begin
	open tmuser;
		loop
			fetch tmuser into suserid,susername;
		exit when tmuser%notfound;
			dbms_output.put_line(suserid||susername);
		end loop;
	close tmuser;
end;

--带参游标
declare
	cursor tmuser(ctype tm_user.c_usertype%type) is select s_userid,s_username 
		from ftzfe.tm_user where c_usertype=ctype;
	suserid tm_user.s_userid%type;
	susername tm_user.s_username%type;
begin
	open tmuser('0000000003');
		loop
			fetch tmuser into suserid,susername;
		exit when tmuser%notfound;
			dbms_output.put_line(suserid||susername);
		end loop;
	close tmuser;
end;

--存储过程
--语法（过程中没有declare）
--create or replace procedure 过程名称[(参数列表)] is或as
--begin
--end;
--无参存储过程
create or replace procedure test is
begin
	dbms_output.put_line('hello world');
end;
begin
	test
end;

--带输入参数的存储过程
create or replace procedure test(in_username in tm_user.s_username%type) is
	username tm_user.s_username%type;
	userid tm_user.s_userid%type;
begin
	select s_userid,s_username into userid,username from ftzfe.tm_user where s_username=in_username;
	dbms_output.put_line(userid||username);
end;
begin
	test('sh001');
end;

--带输出参数的存储过程
create or replace procedure test(in_username in tm_user.s_username%type,
								 out_userid out tm_user.s_userid%type) is
begin
	select s_userid into out_userid  from ftzfe.tm_user where s_username=in_username;
end;
declare
	userid tm_user.s_userid%type;
begin
	test('sh001',userid);
	dbms_output.put_line(userid);
end;