验证中...
语言: SQL
分类: 编程语言基础
最后更新于 2018-06-18 16:46
片段 1 片段 2 片段 3 片段 4 片段 5 片段 6
索引
原始数据 复制代码
select * from emp
select * from dept
--创建一个试图
create or replace view view_emp_dept as select e.*,d.dname from emp e left join dept d on e.empno =d.deptno
select * from view_emp_dept--查询试图
delete from view_emp_dept where empno=7788--简单表可以删除
--锁
create view view_emp_dept as select e.*,d.dname from emp e left join dept d on e.empno =d.deptno only bu read
select * from view_emp_dept
delete from view_emp_dept where empno=7788--简单表可以
--创建一个序列
--oracle中没有自动增长的关键字,oracle有序列可以实现自动增长
create sequence seq_emp1
select seq_emp1. from dall--序列
select sys_guid() from dual
select * from emp
insert into emp(emp.ename) values (seq_emp.nextval,'zhangsan')
--索引
create table emp2 as select * from emp --创建一个新表
create index index_name on emp2(ename);--创建索引
select * from emp2 where ename='zhangsan'
select * from emp2 where job='jaioshi'--没有建立索引
--基于函数索引
create index index_substr on emp2(substr (name,1));--创建索引
select * from User_tables
select * from EMPLOYEES
select * from JOB_HISTARY
--换过工作的人的信息
select * from EMPLOYEES e where e.employee_id in (select employee_id from job_history)
select * from EMPLOYEES e where e.department_id in (10,20,30)
--
select * from EMPLOYEES e
where
exisits(
select employee_id from job_histor h where h.employee_id=e.employee_id
)
PLSQL编程(过程化语言) -------循环
原始数据 复制代码
--循环
begin
for v in 1..100 loop
dbms_output.put_line(v);
end loop;
end;
begin
for v in (select * from employees) loop
if v.salary>3000 then
dbms_output.put_line(v.first_name||v.salary);
end if;
end loop;
end;
begin
for u in 1..20 loop
dbms_output.put_line(u);
end loop;
end;
begin
for u in (select * from employees) loop
if u.salary<6000 then
dbms_output.put_line(u.first_name||u.salary);
end if;
end loop;
end;
--遍历雇员信息表,如果雇员的工资>8000,在控制台输出高工资,如果8000-5000之间,中等工资,否则就是低等工资
begin
for s in(select * from employees) loop
if s.salary>8000 then
dbms_output.put_line(s.salary||'高等工资');
elsif s.salary>5000 and s.salary<=8000 then
dbms_output.put_line(s.salary||'中等工资');
else
dbms_output.put_line(s.salary||'低等工资');
end if;
end loop;
end;
--declare 求雇员的最高工资
declare
v_number employees.salary%type;
v_el employees%rowtype;
begin
select max (salary) into v_number from employees;
dbms_output.put_line(v_number);
end;
--declare 求雇员的低工资和人的名称
declare
v_salary employees.salary%type;
v_name employees.last_name%type;
begin
select salary ,last_name into v_salary, v_name from employees where employees.salary=(select min (salary) into v_salary from employees);
dbms_output.put_line(v_salary||v_name);
end;
--异常处理 others相当于所有异常的父类
declare
v_a number:=10;
v_b number:=0;
v_c number;
begin
v_c:=v_a/v_b;
exception
when others then
dbms_output.put_line('出现异常');
end;
--(2)
declare
v_a number:=10;
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:=10;
v_b number:=0;
v_c number;
begin
v_c:=v_a/v_b;
exception
when others then
dbms_output.put_line(substr(sqlerrm,0,200));
end;
自定义异常
原始数据 复制代码
--自定义异常
declare
v_a number:=10;
v_b number:=0;
v_c number;
v_salary number;
money_not_enough exception ;--------自定义异常
begin
select max(salary) into v_salary from employees;
if v_salary >1000 then
raise money_not_enough; --抛出异常
end if;
v_c:=v_a/v_b;
exception
when money_not_enough then-------捕捉异常
dbms_output.put_line('余额不足');
when others then
dbms_output.put_line(substr(sqlerrm,0,200));
end;
--自定义异常
--判断是否相等 =
declare
v_a number:=10;
v_b number:=0;
v_c number;
v_salary number;
money_not_enough exception ;--------自定义异常
begin
select max(salary) into v_salary from employees;
if v_salary >1000 then
raise money_not_enough; --抛出异常
end if;
v_c:=v_a/v_b;
exception
when money_not_enough then-------捕捉异常
dbms_output.put_line('余额不足');
when others then
dbms_output.put_line(substr(sqlerrm,0,200));
end;
---使用pl编程 ,编写一个除法的plsql,如果除数和被除数有一个是<=0,抛出操作数不能为0,如果运算过程中出现异常
--捕捉异常,如果没有异常输出结果
declare
s_a number:=10;
s_b number:=0;
s_c number;
number_not_0 exception ;--------自定义异常
begin
if s_a <0 or s_b <=0 then
raise number_not_0; --抛出异常
end if;
s_c:=s_a/s_b;
exception
when number_not_0 then-------捕捉异常
dbms_output.put_line(sqlerrm);
end;
存储过程
原始数据 复制代码
--创建爱一个存贮过程 f8是编译的意思 执行是调用
create or replace procedure pro_thansfer(v_a in number,v_b in number ,v_c out number)
is --=declare
begin
v_c:=v_a+v_b;
end;
------编写一个存储过程实现计算器的加减乘除 参数 两个数操作 和结果 还有一个符号
create or replace procedure pro_thansfer_zsl(v_a in number,v_b in number ,v_c out number,v_d in varchar2 ,v_f out varchar2)
is --=declare
operator_not_vaild exception ;--------自定义异常
begin
if v_d='+' then
v_c:=v_a+v_b;--------------加
elsif v_d='-' then
v_c:=v_a-v_b;--------减
elsif v_d='*' then
v_c:=v_a*v_b;---------乘
elsif v_d='/' then
v_c:=v_a/v_b;----------
else
raise operator_not_vaild ; --抛出异常
end if;
exception
when operator_not_vaild then-------捕捉异常
dbms_output.put_line(sqlerrm);
end;
-----转账的存储过程
--账户用户信息表(用户名、密码,如果用户名、密码错抛出异常,金额不足抛出异常)
--2个账户 1个密码 转账人的金额,交易的成败
触发器
原始数据 复制代码
--删除前备份
select * from employees;
create or replace trigger tr_back
before delete on employees
for each row
begin
insert into emp_bak values(:old.employees_id,:old.first_name,:old.last_name,:old.email,:old.phone_number,:old.hire_date,:old.job_id,:old.salary,:old.commisson_pct,:old.manager_id );
end;
--测试
select * from emp_bak
delete from emp_bak
select * from employees
--自动增长
create or replace trigger tr_insert1-----
before insert on test_shun
for each row
begin
select seq_shun.nextval into :new.id from dual;
end;
create sequence seq_shun------创建序列,
create table test_shun(--------创建一个新表 test_shun
id number,
name varchar2(10),
sex varchar(2)
)
---测试
select * from test_shun
insert into test_shun(name,sex) values('周顺利','男');
函数
原始数据 复制代码
--函数
create or replace function(id employees.employee_id%type);
is
s_result departments.department_id%type
begin
select * from employees e left join departments d on e.employee_id=
return s_result;
end;
--编写一个函数,输入一个部门编号,返回一个部门名称,且吧这个函数和雇员表一起使用
create or replace function fun_dept_name(v_id departments.department_id%type) return varchar
is
v_name departments.department_name%type
begin
select d.department_name into v_name from departments d where d.department_id=v_id;
return s_result;
end;

评论列表( 0 )

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

搜索帮助