Oracle 常用sql语句

-- 去重复值
select distinct age from shp;
-- 字符串连接符
select age||name from shp;
-- 分页
select * from (select s.*, rownum rn from shp s where rownum < 8)where rn >3;


-- 二表关联查询
select ename,dname from emp join dept on (emp.deptno=dept.deptno);

select dname,dname from emp join dept using(deptno);

select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
-- 左连接
select e.ename,d.dname from emp e left join dept d on (e.deptno=d.deptno);

select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno(+);
-- 右连接
select e.ename,d.dname from emp e right join dept d on (e.deptno=d.deptno);

select e.ename,d.dname from emp e,dept d where e.deptno(+)=d.deptno;

-- 自然连接
select ename,dname from emp natural join dept;

--- 存储过程
create or replace procedure s(var_a in varchar2,
var_b in varchar2,
p_code out varchar2,
p_name out varchar2) as
temp_a number;
temp_b varchar2(10);
temp_c varchar2(20);
e_exception exception;
begin
select count(*) into temp_a from shp where name = var_a;
temp_b := var_b;
temp_c := var_a;
p_code := temp_a;
select name into p_name from shp where address = temp_b
and name = temp_c;
temp_c := 'false';
commit;
exception
when others then
p_code := 555;
p_name := temp_c;
end;

---创建触发器
create or replace trigger shptri
before insert on shp for each row
begin
select shpseq.nextval into :new.id from dual;
end;

insert into shp (name, age, address) values('jams', 123, 'shenzhen');


create or replace trigger shptrinext
before insert on shp
for each row
begin
select 'shp' || shpseq.nextval into :new.name from dual;
end;
insert into shp(age, address) values(234, 'shenzhen2');

-- 游标
declare
cursor v_cursor return emp%rowtype is
select * from emp where rownum < 10;
v_emp emp%rowtype;
begin
open v_cursor;
fetch v_cursor into v_emp;
while (v_cursor%found) loop
fetch v_cursor into v_emp;
dbms_output.put_line(v_emp.empno);
end loop;
close v_cursor;
end;

--- 动态的指针游标
declare
type c_type is ref cursor;
aa c_type;
v_sql varchar2(2000):='select * from emp where job=:1 and rownum=10';
v_job emp.job%type;
begin
v_job:='&job';
open aa for v_sql using v_job;
dbms_output.put_line(v_job);
end;

declare
v_sql varchar2(2000);
v_emp emp%rowtype;
v_job emp.job%type;
v_name emp.ename%type;
begin
v_sql:='select * from emp where job=:1 and ename=:2 and rownum=1';
v_job:='&tempone';
v_name:='&temptwo';
execute immediate v_sql into v_emp using v_job,v_name;
dbms_output.put_line(v_emp.sal);
end;


declare
type c_type is ref cursor;
aa c_type;
v_emp emp%rowtype;
v_sql varchar2(2000);
begin
v_sql:='select * from emp where rownum <10';
open aa for v_sql;
fetch aa into v_emp;
while(aa%found)loop
dbms_output.put_line(v_emp.empno);
fetch aa into v_emp;
end loop;
close aa;
end;
---测试数据
create or replace procedure test_iot(p_line in number) is
v_line number;
begin
v_line := p_line;
if (v_line < 1) then
dbms_output.put_line('请输入>=1的整数');
end if;
for v in 1 .. p_line loop
insert into iot_test values (v, 'test', 'test', 'test', 'test');
end loop;
end test_iot;

call test_iot(100000);


create table iot_test1(
id number,
c1 varchar2(20),
c2 varchar2(20),
c3 varchar2(30),
c4 varchar2(20),
constraint pk_iot_test1 primary key(id)
)

create or replace procedure test1(p_line in number) is
v_line number;
begin
v_line := p_line;
if (v_line < 1) then
dbms_output.put_line('请输入>=1的整数');
end if;
for v in 1 .. p_line loop
insert into iot_test1 values (v, 'test', 'test', 'test', 'test');
end loop;
end test1;

call test1(100000);

-- 比较测试速度
select id,c1 from iot_test1 where rownum<1000;
select id,c1 from iot_test where rownum<1000;

--- 函数
create or replace function fun_sal(empno number) return number as
empsal number;
netsal number;
begin
select sal into empsal from emp where empno = empno and rownum = 1;
netsal := empsal * 0.95;
DBMS_OUTPUT.PUT_LINE('雇员' || empno || '的工资是' || netsal);
return netsal;
end;

--- 定时任务
create table a(a date);

create or replace procedure test as
begin
insert into a values(sysdate);
end;


declare job1 number;
begin
dbms_job.submit(job1,'test;',sysdate,'sysdate+1/1440'); --每天1440分钟，即一分钟运行test过程一次
commit;
end;

select * from user_jobs;
select * from a;