验证中...
gistfile1.txt
原始数据 复制代码
--创建视图
create or replace view view_emp as select * from emp
select * from view_emp
create or replace view view_1 as select p.*,d.dname from emp p left join dept d on p.deptno=d.deptno
select * from view_1
delete from view_1 where
create sequence view_2
--创建序列
--自增长
select view_2.nextval from dual
--当前序列
select view_2.currval from dual
select sys_guid from dual
select * from emp
insert into emp() values( view_2.nextval,'wjx','nam','235','sysdate()','5695','56248','30');
--创建索引
create index indes_enames on emp2(job)
select * from emp2 where job=4
--SQL优化
select * from emp p where p.deptno in(10)
--PLSQL语句块
declare
begin
for s in(select * from emp) loop
dbms_output.put_line(s.ename);
end loop;
end;
begin
for s in(select * from emp) loop
if s.sal>1000 then
dbms_output.put_line(s.ename||s.sal);
end if;
end loop;
end;
--
begin
for s in (select * from emp)loop
if s.sal>8000 then
dbms_output.put_line(s.ename || s.sal ||'高工资');
elsif s.sal in(5000,8000) then
dbms_output.put_line(s.ename || s.sal ||'中等工资');
else
dbms_output.put_line(s.ename || s.sal ||'低工资');
end if;
end loop;
end;
declare
v_emp emp.sal%type;
begin
select sum(sal) into v_emp from emp;
dbms_output.put_line(v_emp);
end;
--查询最低工资并显示名字
declare
v_name emp.ename%type;
v_min_sal emp.sal%type;
begin
select ename,sal into v_name, v_min_sal from emp where sal=(select min(sal)from emp );
dbms_output.put_line(v_name||v_min_sal);
end;
--异常处理 others 相当于异常父类
declare
v_a number :=100;
v_b number :=0;
v_c number ;
begin
v_c:=v_a/v_b;
exception
when others then
dbms_output.put_line(sqlerrm);
end;
--自己定义异常并抛出异常
declare
v_a number :=100;
v_b number :=0;
v_c number ;
v_sal number;
money_not_enought exception;
begin
select max(sal) into v_sal from emp ;
if v_sal>500 then
raise money_not_enought;--抛出异常
end if;
v_c:=v_a/v_b;
exception
when money_not_enought then-- 捕捉异常
dbms_output.put_line('钱不够了');
when others then
dbms_output.put_line(substr(sqlerrm,0,200)); --截取0-200个字符
end;
--实例
declare
v_a number :=100;
v_b number :=0;
v_c number ;
cao_zuo exception;
run_able exception;
begin
if v_a<=0 or v_b<=0 then
raise cao_zuo;
end if;
v_c:=v_a/v_b;
dbms_output.put_line(v_c);
raise run_able;
exception
when cao_zuo then
dbms_output.put_line('操作异常') ;
when run_able then
dbms_output.put_line('未知异常');
end;
--创建一个存储过程
create or replace procedure pro_add(v_a in number,v_b in number, v_c out v_c)
is --定义变量
begin
v_c:=v_a+ v_b;
end;

评论列表( 0 )

你可以在登录后,发表评论

搜索帮助