oracle sql

ORACLE sql#
create unique index index_id_unique on customers(customer_id);–创建id唯一索引
create bitmap index index_territory_bit on customers(nls_territory);–创建位图索引
create index index_xm_xl on customers(cust_first_name,cust_last_name);–创建组合索引

declare
id_count number(5);–定义变量
begin
select count(*) into id_count from emp;–执行查询
dbms_output.put_line(id_count);—打印输出变量
end;

declare
type table_type is table of varchar2(20) index by binary_integer; --定义类型
v_names table_type;
begin
select ename into v_names(0) from emp where empno =7369;
select ename into v_names(1) from emp where empno =7499;
dbms_output.put_line(v_names(0));
dbms_output.put_line(v_names(1));
end;
declare
type row_type is record(v_id binary_integer,v_name varchar2(20));
v_record row_type;
begin
select empno,ename into v_record from emp where empno=7369;–&no
dbms_output.put_line(v_record.v_id);
dbms_output.put_line(v_record.v_name);
end;

–补贴
declare
v_comm emp.comm%type;
begin
select comm into v_comm from emp where empno=&no;
if v_comm is null then
dbms_output.put_line(‘没有补贴’);
else
dbms_output.put_line(‘补贴为:’+v_comm);
end if;
end;

–岗位 if
declare
v_job emp.job%type;
begin
select job into v_job from emp where empno=&no;
if v_job=‘MANAGER’ then
dbms_output.put_line(‘部门经理’);
elsif v_job=‘SALESMAN’ then
dbms_output.put_line(‘推销员’);
elsif v_job=‘CLERK’ then
dbms_output.put_line(‘秘书’);
else
dbms_output.put_line(‘其他岗位’);
end if;
end;

-case
declare
v_job emp.job%type;
begin
select job into v_job from emp where empno=&no;
case v_job
when ‘MANAGER’ then
dbms_output.put_line(‘部门经理’);
when ‘SALESMAN’ then
dbms_output.put_line(‘推销员’);
when ‘CLERK’ then
dbms_output.put_line(‘秘书’);
else
dbms_output.put_line(‘其他岗位’);
end case;
end;

–查询
select
ename,
case job
when ‘MANAGER’ then
‘部门经理’
when ‘SALESMAN’ then
‘推销员’
when ‘CLERK’ then
‘秘书’
else
‘其他岗位’
end china_job from emp;

declare
v_name emp.ename%type;
begin
select ename into v_name from emp;
exception
when too_many_rows then
dbms_output.put_line(‘too many rows’);
when no_data_found then
dbms_output.put_line(‘no_data_found’);
end;

–游标
declare
cursor c_emp is select empno,ename from emp;
v_id emp.empno%type;
v_name emp.ename%type;
begin

–打开游标
open c_emp;
loop
fetch c_emp into v_id,v_name;
exit when c_emp%notfound;
dbms_output.put_line(v_id||’ '||v_name);

     end loop;
     close c_emp;
     end;

—计算员工KING所交税金
declare
v_sal emp.ename%type;–工资
v_sum number;–应交税金
v_num number :=3500; —起征点
begin
select sal into v_sal from emp where ename=‘KING’;
if (v_sal-v_num)<=1500 then–级别1 不超过1500
v_sum :=(v_sal-v_num)*0.03-0;
dbms_output.put_line(‘KING所交税金:’||v_sum);
elsif (v_sal-v_num)>1500 and (v_sal-v_num)<=4500 then–级别2 超过1500 不超过4500
v_sum :=(v_sal-v_num)*0.1-105;
dbms_output.put_line(‘KING所交税金:’||v_sum);
elsif (v_sal-v_num)>4500 and (v_sal-v_num)<=9000 then–级别3 超过4500 不超过9000
v_sum :=(v_sal-v_num)*0.2-555;
dbms_output.put_line(‘KING所交税金:’||v_sum);
elsif (v_sal-v_num)>9000 and (v_sal-v_num)<=35000 then–级别4 超过9000 不超过35000
v_sum :=(v_sal-v_num)*0.25-1005;
dbms_output.put_line(‘KING所交税金:’||v_sum);
elsif (v_sal-v_num)>35000 and (v_sal-v_num)<=55000 then–级别5 超过35000 不超过55000
v_sum :=(v_sal-v_num)*0.3-2755;
dbms_output.put_line(‘KING所交税金:’||v_sum);
elsif (v_sal-v_num)>55000 and (v_sal-v_num)<=80000 then–级别6 超过55000 不超过80000
v_sum :=(v_sal-v_num)*0.35-5505;
dbms_output.put_line(‘KING所交税金:’||v_sum);
elsif (v_sal-v_num)>80000 then–级别7 超过80000
v_sum :=(v_sal-v_num)*0.45-13505;
dbms_output.put_line(‘KING所交税金:’||v_sum);
end if;
end;

—根据员工SCOTT入职时间修改发放奖金列（comm列），大于等于6年的，奖金为2000元，小于6年的，奖金为1500元；
declare
v_hiredate emp.hiredate%type;–入职时间
v_comm number;–奖金
v_commm emp.comm%type;–修改后查看comm奖金
begin
select hiredate into v_hiredate from emp where ename=‘SCOTT’;
if (sysdate-v_hiredate)>=3656 then
v_comm :=2000;
elsif (sysdate-v_hiredate)<3656 then
v_comm :=1500;
end if;
update emp set comm=v_comm where ename=‘SCOTT’;–修改
select comm into v_commm from emp where ename=‘SCOTT’;–修改后查询
dbms_output.put_line(v_commm);
end;

–查询员工SCOTT相应的工资级别并显示所在部门名称，薪水和所在级别
declare
v_dname dept.dname%type;–部门名称
v_sal emp.sal%type;–工资
v_num number;–级别
begin
select emp.sal,dept.dname into v_sal,v_dname from emp
left join dept on dept.deptno=emp.deptno
where emp.ename=‘SCOTT’;
if v_sal>700 and v_sal<=3200 then
v_num :=1;–级别1
elsif v_sal>3201 and v_sal<=4400 then
v_num :=2;–级别2
elsif v_sal>4401 and v_sal<=5000 then
v_num :=3;–级别3
elsif v_sal>5001 and v_sal<=7000 then
v_num :=4;–级别4
elsif v_sal>7001 and v_sal<=9999 then
v_num :=5;–级别5
end if;
dbms_output.put_line(‘部门名称:’||v_dname||’,薪水:’||v_sal||’,所在级别:’||v_num);
end;

–为员工SCOTT增加工资，每次增加100元，直到10000元停止
declare
  v_sal emp.sal%type;–工资
begin
  select sal into v_sal from emp where ename=‘SCOTT’;
  loop --loop循环
    v_sal :=v_sal+100;–每次增加工资
    exit when v_sal=10000;–满足10000元跳出循环
  end loop;
  update emp set sal=v_sal where ename=‘SCOTT’;–修改工资
  select sal into v_sal from emp where ename=‘SCOTT’;
  dbms_output.put_line(‘增加后的工资为:’||v_sal);
end;