验证中...
本周日【珠海源创会】一起聊聊:PingCAP分布式事务、支付宝移动端实践、GSBN技术框架选型,点此报名占座
片段 1 片段 2 片段 3 片段 4
PLSQL编程
原始数据 复制代码
--创建视图
create view view_emp_d_xxxx2 as
select p.empno,p.ename,d.deptno,d.dname from emp p left join dept d on p.deptno=d.deptno;
--查询视图
select * from view_emp_d_xxxx2 ;
--删除简单视图的记录
delete from view_emp_d_xxxx where empno=7844;
--创建序列 [自动增长]
create sequence seq_emp_xxxx;
select seq_emp_xxxx.nextval from dual;
--创建索引
create index index_ename_xxxx on emp(ename);
--PLSQL编程
--循环
begin
for v in 1..10 loop
dbms_output.put_line(v);
end loop;
end;
begin
for v in (select * from emp) loop
dbms_output.put_line(v.ename);
end loop;
end;
begin
for v in (select * from emp) loop
if v.sal >3000 then
dbms_output.put_line(v.ename);
end if;
end loop;
end;
--遍历雇员信息表,如果工资>8000,在控制台输出高工资,5000-8000,中等工资,否则低工资
begin
for v in (select * from emp) loop
if v.sal >=3000 then
dbms_output.put_line('高工资');
elsif v.sal >=1000 then
dbms_output.put_line('中等工资');
else
dbms_output.put_line('低工资');
end if;
end loop;
end;
--求雇员表最低工资 和人名 ( into)
declare
v_number emp.sal%type;
v_number2 emp.ename%type;
begin
select sal,ename into v_number,v_number2 from emp
where sal=(select max(sal) from emp) ;
dbms_output.put_line(v_number||v_number2);
end;
--异常
declare
v_a number :=10;
v_b number :=5;
v_c number;
exception_xxxx exception; --自定义一个异常
begin
v_c:=v_a/v_b;
dbms_output.put_line(v_c);
raise exception_xxxx; --抛出异常
exception
when exception_xxxx then dbms_output.put_line(substr(sqlerrm,0,200));
when others then dbms_output.put_line(substr(sqlerrm,0,200));
end;
--编写一个除法的PLSQL如果除数和被除数有一个是0,抛出操作数不为0;
--运算过程错误显示未知异常;没有异常,输出结果
declare
v_a number :=0;
v_b number :=5;
v_c number;
exception_0 exception;
exception_xx exception;
begin
v_c:=v_a/v_b;
if v_a=0 or v_b=0 then
raise exception_0;
elsif v_c is null then
raise exception_xx;
else
dbms_output.put_line(v_c);
end if;
exception
when exception_xx then dbms_output.put_line('未知错误');
when exception_0 then dbms_output.put_line('操作数不能为0');
end;
存储过程加减乘除
原始数据 复制代码
--编写一个存储过程,实现计算器在加减乘除 参数 两个操作 肯结果 ,还有一个符号
create procedure proc_xxxx4444( v_a in number, v_b in number, v_c out number,v_char in varchar2,v_put out varchar2)
as
exception_0 exception;
begin
if v_char='+' then
v_c:=v_a+v_b;
end if;
if v_char='-' then
v_c:=v_a-v_b;
end if;
if v_char='*' then
v_c:=v_a*v_b;
end if;
if v_char='/' then
if v_a=0 or v_b=0 then
raise exception_0;
else
v_c:=v_a/v_b;
end if;
end if;
exception
when exception_0 then v_put:='操作数不能为0';
end;
触发器
原始数据 复制代码
--备份
create table emp_xx444 as select * from test_lanlan where 1=2;
select * from emp_xx444;
--建触发器
create or replace trigger tr_xx444
before delete on test_lanlan
for each row
begin
insert into emp_xx444 values(:old.id,:old.ename,:old.job);
end;
delete from test_lanlan where id=1;
select * from emp_xx444;
select * from test_lanlan;
--创建一个序列自动增长
create sequence seq_xxxx;
create table test_lanlan(
id number,
ename varchar2(28),
job varchar2(28)
)
create or replace trigger tr_insert_xx44
before insert on test_lanlan for each row
begin
select seq_xxxx.nextval into :new.id from dual;
end;
insert into test_lanlan(ename,job) values('name','job');
select * from test_lanlan;
函数
原始数据 复制代码
--编写一个函数,输入一个部门编号,返回一个部门名称
create or replace function fun_showname_xxxx( v_id dept.deptno%type )
return varchar2
as
v_dname varchar2(28);
begin
select dept.dname into v_dname from dept where dept.deptno=v_id;
return v_dname;
end;
select fun_showname_xxxx(10) from dual;
select emp.*,fun_showname_xxxx(emp.deptno) from emp;

评论列表( 0 )

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

搜索帮助

12_float_left_people 12_float_left_close