oracle使用笔记

1.备份表
create table SY_FORM_BUTTON_bak as select * from SY_FORM_BUTTON 2.将搜到的记录插入另一张表
insert into SY_USER
select * from SY_USER_bak
3.更新一个表的字段，字段数据来源于另外的一张表，如下：
update FA_FAULT_RECODE_NEW a set a.CONFIRM_USER=(select user_name from sy_user b where er_code=a.CONFIRM_USER)
where exists (select 1
from sy_user b
where er_code=a.CONFIRM_USER)
4.删除重复数据只保留一条的sql：
　create table IT_SUPPLIERS_one as
select a.supp_name,MAX(a.ROWID) dataid from IT_SUPPLIERS a GROUP BY a.supp_name;
delete from IT_SUPPLIERS a
where a.rowid !=
(
select b.dataid from IT_SUPPLIERS_one b
where a.supp_name = b.supp_name
);
5.oracle里面有没有统计某个字符串中某个字符个数的函数
没有直接的统计的函数，但是可以这么做：
s0:源字符串
s1:某个字符串
则s1在s0中的个数为：
length(s0)-length(replace(s0,s1,''))
6.创建自增序列
-- Create sequence
create sequence SEQ_GZ_ZDSG
minvalue 1
maxvalue 999999999999999999999999999
start with 30
increment by 1
nocache
order;
7.通过触发器来为插入的数据自动添加主键字段--触发器插入主键
CREATE OR REPLACE TRIGGER trigger_GZ_ZDSG
BEFORE
INSERT
ON GZ_ZDSG
FOR EACH ROW
BEGIN
SELECT SEQ_GZ_ZDSG.NEXTVAL
INTO :NEW.ID
FROM DUAL;
END;
8.创建存储过程
--存储过程
create or replace procedure print11(msg varchar2)
is
begin
dbms_output.put_line(msg);
end;
第二个存储过程
create or replace procedure USER_SYN as
cursor sy_orgs is
select * from test_org1;
begin
for sy_org_one in sy_orgs loop
EXECUTE IMMEDIATE 'insert into test_org2(org_code,org_name,beizhu) values (:1,:2,:3)'
USING sy_org__code, sy_org__name, sy_org__code;
COMMIT;
end loop;
end;
9.存储过程的执行
--调用存储过程
execute print11('helloworld');(注意：这种执行存储过程的方式只能在sqlpus命令模式下执行）；
10.传递参数的存储过程
--传递参数
create or replace procedure myabs(p_num1 IN number,p_num2 OUT number)
is
begin
if p_num1 >0 then
p_num2 := p_num1;
else
p_num2 := (0 - p_num1);
end if;
end;
--调用
declare
v_result number;
begin
myabs(-987,v_result);
print11(v_result);
end;
11.执行打印输出函数如下，要求必须在begin，end之间打印输出，否则报错
begin
dbms_output.put_line('helloword');
end;
12.查看相应用户所建立的触发器语句
select owner,trigger_name,table_name
from dba_triggers
where table_name='SY_USER';
13.oracle 截取字符(substr)，检索字符位置(instr) case when then else end语句使用常用函数：substr和instr
1.SUBSTR（string,start_position,[length]）求子字符串，返回字符串
解释：string 元字符串
start_position 开始位置（从0开始）
length 可选项，子字符串的个数
For example:
substr("ABCDEFG", 0); //返回：ABCDEFG，截取所有字符
substr("ABCDEFG", 2); //返回：CDEFG，截取从C开始之后所有字符
substr("ABCDEFG", 0, 3); //返回：ABC，截取从A开始3个字符
substr("ABCDEFG", 0, 100); //返回：ABCDEFG，100虽然超出预处理的字符串最长度，但不会影响返回结果，系统按预处理字符
串最大数量返回。

substr("ABCDEFG", 0, -3); //返回：EFG，注意参数-3，为负值时表示从尾部开始算起，字符串排列位置不变。

2.INSTR（string,subString,position,ocurrence）查找字符串位置
解释：string：源字符串
subString：要查找的子字符串
position：查找的开始位置
ocurrence：源字符串中第几次出现的子字符串
For example:
INSTR('CORPORATE FLOOR','OR', 3, 2)中，源字符串为'CORPORATE FLOOR', 目标字符串为'OR'，起始位置为3，取第2个匹配项的位
置；返回结果为 14 '
14.转换函数
字符串转为数字用to_number函数，例如
to_number('1210.73')就得到1210.73
数字转为字符串用to_char函数，例如
to_char(1210.73)就得到'1210.73'
两个函数都可以设定转换的格式，例如
to_char(1210.73, '$9,999.00')就得到'$1,210.73'
15.游标的使用
在大多数时候我们在设计程序的时候都遵循下面的步骤： 1、打开游标 2、开始循环 3、从游标中取值 4、检查那一行被返回 5、处理 6、关闭循环 7、关闭游标
例如：
DECALRE
CURSOR c_dept IS SELECT deptno,dname FROM dept ORDER BY deptno;
CURSOR c_emp (p_dept VARACHAR2) IS SELECT ename,salary FROM emp WHERE deptno=p_dept ORDER BY ename
v_tot_salary EMP.SALARY%TYPE;
BEGIN
FOR r_dept IN c_dept
LOOP
DBMS_OUTPUT.PUT_LINE('Department:'||
r_dept.deptno||'-'||r_dept.dname);
v_tot_salary:=0;
FOR r_emp IN c_emp(r_dept.deptno)
LOOP
DBMS_OUTPUT.PUT_LINE('Name:'|| v_ename||' salary:'||v_salary);
v_tot_salary:=v_tot_salary+v_salary;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:'|| v_tot_salary);
END LOOP;
END;
16.FOR循环的使用
FOR i IN 1 .. v_tabArr.count LOOP
EXECUTE IMMEDIATE 'delete from ' || v_tabArr(i)
.v_tableName || ' where ' || v_tabArr(i)
.v_ry_code || ' = :1'
USING v_ryCode;
END LOOP;。