oracle 基础知识总结



set serveroutput on

--登陆scott用户
conn scott/root@orcl;
--创建同义词
create synonym MyEmp for scott.emp;

--赋表emp权限给xiaomei
grant all on emp to xiaomei
--登陆用户xiaomei
con scott/root@orcl;
--查询 表MyEmp
select * from MyEmp;

--创建PL/sql 过程
--查看变量值
set serveroutput on;
--根据 := 给变量赋值
declare
sname varchar2(20) default 'jerry';
begin
sname :=sname||' and tom';
dbms_output.put_line(sname);
end;
/

根据select --- into 给变量赋值
declare
sname varchar2(20) default 'jerry';
begin
select ename into sname from MyEmp where empno=7934;
dbms_output.put_line(sname);
end;
/
--申明常量
declare
 pi constant number :=3.14;
r number default 3;
area number;
begin
area :=pi*r*r;
dbms_output.put_line(area);
end;
/
--申明宿主变量
/*
var emp_name varchar(30);
begin
select ename into :emp_name from emp where empno=7499;
end;

print emp_name;
*/

--登陆scott
conn scott/root@orcl;

--属性数据类型 %type（列数据） 和%rowtype(一行数据，相当于一个对象)
declare
myemp emp%rowtype;
begin
select * into myemp from emp where empno=7934;
dbms_output.put_line(myemp.ename);
end;
/

declare
sal emp.sal%type;
mysal number(4) :=3000;
totalsal mysal%type;
begin
select sal into sal from emp where empno=7934;
totalsal:=sal+mysal;
dbms_output.put_line(totalsal);
end;
/

--if-then-elseif 应用
declare
 newSal emp.sal%type;
begin
select sal into newSal from emp where ename='JAMES';
if newSal>1500 then
update emp set comm=1000 where ename='JAMES';
elsif newSal>1500 then
update emp set comm=800 where ename='JAMES';
else
update emp set comm=400 where ename='JAMES';
end if;
end;

--case 不返回值
declare
 v_grade char(1):=upper('&p_grade');
begin
case v_grade
when 'A' then
dbms_output.put_line('Excellent');
when 'B' then
dbms_output.put_line('Very Good');
when 'C' then
dbms_output.put_line('Good');
else
dbms_output.put_line('No such grade');
end case;
end;

--case 返回值
declare
v_grade char(1):=upper('&grade');
p_grade varchar(20);
begin
p_grade :=
case v_grade
when 'A' then
'Excellent'
when 'B' then
'Very Good'
else
'No'
end;
dbms_output.put_line('Grade:'||p_grade);
end;

--搜索sase
declare
v_grade char(1):=upper('&grade');
p_grade varchar(20);
begin
p_grade :=
case
when v_grade='A' then
'Excellent'
when v_grade='B' then
'Good'
else
'NO'
end;
dbms_output.put_line('Grade:'||p_grade);
end;

--loop循环
declare
counter number(3):=0;
sumResult number:=0;
begin
loop
counter:=counter+1;
sumResult:=sumResult+counter;
if counter>=100 then
exit;
end if;
end loop;
dbms_output.put_line('result is:'||to_char(sumResult));
end;

--while循环
declare
counter number(3):=0;
sumResult number:=0;
begin
while counter<100 loop
counter :=counter+1;
sumResult:=sumResult+counter;
end loop;
dbms_output.put_line('restule is:'||sumResult);
end;

--for 循环
declare
counter number(3):=0;
sumResult number:=0;
begin
for counter in 1..100 loop
sumResult:=sumResult+counter;
end loop;
dbms_output.put_line('result is:'||sumResult);
end;

--goto 和 null
declare
sumsal emp.sal%type;
begin
select sum(sal) into sumsal from emp;
if sumsal>2000 then
goto first_label;
else
goto second_label;
end if;
<<first_label>>
dbms_output.put_line('above 20000:'||sumsal);
<<second_label>>
NULL;
end;

--异常raise_application_error(异常码,异常信息);
declare
sal emp.sal%type;
myexp exception;
begin
select sal into sal from emp where ename='JAMES';
if sal<5000 then
raise myexp;
end if;
exception
when NO_DATA_FOUND THEN
dbms_output.put_line('no');
when myexp then
raise_application_error(-20001,'sal is to less!');
end;
/