oracle的一些语句

仅作为自己笔记用，如果正好别人也需要就更好了

--前提：如果发现输出窗口没有显示我们的输出内容，那就要执行一下下面这句话
set serveroutput on;

--1.执行普通的代码
declare 
arg1 VARCHAR2(222) := 123; --定义对象赋值，oracle里面 := 代表赋值的意思
arg2 VARCHAR2(222) := 333;
begin
    --假设arg1是输入，arg2是输出   
    用户.存储过程(arg1, arg2); --执行存储过程
    dbms_output.put_line(arg1);--输出字符串
end;

--2.查询前n行
select * from 表名 where rownum <= n

--3.建表
create table 表名
(
    id number not null,
    string varchar(20),
    string2 varchar(20)            
);

--4.给上面这张表添加注释
comment on column 表名.字段名 is '注释';

--5.给上面那张表创建主鍵
alter table 用户名.表名 add constraint 主键名 primary key (主键字段)

--6.创建序列（主键自增用）
create sequence 序列名 -- 建议使用表名+字段名+seq后缀
increment by 1 -- 每次加几个
start with 1 -- 从1开始计数
nomaxvalue --不设置最大值
nocycle -- 一直累加，不循环
nocache -- 不建缓冲区

--7.把上面的序列和表绑定(需要启用)
create or replace trigger 触发器名字
before insert on 表名
for each row
begin
select 序列名.nextval into :new.主键名字 from dual;
end 触发器名字;

--8.给上面那张表创建索引，和sql server一样，如果没有建立组合索引，在where string = 'xxx' and string2 = 'xxx'的时候单个索引不起作用，且和where时候使用的字段顺序有关，如果 where string2 = 'xxx' and string = 'xxx'则下面这段索引就不起作用
create index 索引名字 on 表名 (字段1, 字段2);

--9.创建存储过程
create or replace procedure pro_test(string1 in varchar2, string2 in varchar2)--pro_test为存储过程名
is
temp varchar2(128);--temp为存储过程临时变量
begin
    insert into mytesttable (string1, string2) values(string1, string2);
    commit;--切记commit一下（提交）
    select count(*) into temp from mytesttable;--存储过程里面查询必须要有个存放的地方不然会报语法错误
    dbms_output.put_line('行数：'||temp);--orcale里面 || 代表拼接符号
    exception
    when others
    then
    begin
        dbms_output.put_line('出错了：'||SQLERRM);
        rollback;--回滚
    end;
end;

--10.执行带参数存储过程：
exec 存储过程(参数1，参数2);    
--11.执行无参数存储过程：
exec 存储过程名字();

--12.if语句的写法
if (条件)
then
语句一;
语句二;
else--else if要写成 elsif，且写了if一定要补then，比如：elsif 条件 then xxxx
语句三;
语句四;
end if;

--13.查询所有用到某字符串的存储过程、触发器和函数，有时候不是所有的存储过程和触发器都在同一用户下，我们可以使用"all_source"代替"user_source"
select * from user_source where text like '%字符串%';

--14.时间格式转换
to_char(sysdate, "yyyy")--各种时间，yyyyMMdd这种，随意组合

to_date('2010-01-01','yyyy-mm-dd') --字符串转时间

--存储过程中的循环

--创建一个存储过程
create or replace procedure sj_mytestpro as
part_no VARCHAR2 (200);
rate_value number;

CURSOR part_info
IS
SELECT function_name,parame_value FROM SAJET.SYS_MODULE_PARAM
WHERE MODULE_NAME = 'PassCfg' AND PARAME_ITEM = 'Rate'
AND ENABLED = 'Y';--定義集合要放在begin前（就是declar下）
begin
FOR part_info_item IN part_info
LOOP
part_no := part_info_item.function_name;
rate_value := part_info_item.parame_value;
end loop;
end;

--15.查询创建的作业
select job,broken,what,interval,t.* from user_jobs t

--16.创建作业
declare
job number;
BEGIN
DBMS_JOB.SUBMIT(
JOB => job, /*自动生成JOB_ID*/
WHAT => 'proc_add_test;', /*需要执行的存储过程名称或SQL语句*/
NEXT_DATE => sysdate+3/(24*60), /*初次执行时间-下一个3分钟*/
INTERVAL => 'trunc(sysdate,''mi'')+1/(24*60)' /*每隔1分钟执行一次*/
);
commit;
end;


--17.跨数据库读取数据并插入现有数据库的表
SELECT * FROM DBA_DB_LINKS ORDER BY DB_LINK;--获取对应的数据库的DB_LINK连接名,假如叫'TESTDB'

 
INSERT INTO TABEL_A--从名为'TESTDB'库中拉取数据，插入当前数据库
SELECT * FROM TABEL_A@TESTDB WHERE ID = 1000220848;