–goto跳转语句
–在goto 后，跳转到相应的语句，然后执行该语句和后面所有语句
begin
dbms_output.put_line(‘goto开始了’);
goto c;
–不被执行
dbms_output.put_line(‘goto’);
dbms_output.put_line(‘goto’);
dbms_output.put_line(‘goto’);
dbms_output.put_line(‘goto’);
<>
dbms_output.put_line(‘goto aaa’);
<>
dbms_output.put_line(‘goto bbb’);
<>
dbms_output.put_line(‘goto ccc’);
end;

–PL/SQL中的sql和rowcount属性：sql代表最近执行的sql语句，rowcount代表最近执行的sql语句影响的行数
declare
vCount number;
vDeptno dept2.deptno%type := 10;
begin
–select count() into vCount from dept2;
–update dept2 set dname = dname || ‘~.~’ where deptno = vDeptno;
–delete from dept2 where deptno = 40;
insert into dept2 select from dept;
dbms_output.put_line(‘count:’ || vCount);
dbms_output.put_line(sql%rowcount || ‘行受影响’);
end;

–PL/SQL执行sql语句
begin
execute immediate ‘create table temp as select * from dept’;
end;

–动态sql，把sql语句放到变量中并传递参数
declare
sqlstr varchar2(100);
begin
sqlstr := ‘insert into scott.dept values(12, :1, :abc)’;
–变量是按照占位符顺序插入的
execute immediate sqlstr using ‘教务部’, ‘武汉’;
end;

select * from scott.dept;

–动态sql,把查询结果赋值给变量
declare
dept_row_data scott.dept%rowtype;
sqlstr varchar2(100);
begin
sqlstr := ‘select * from scott.dept where deptno = 11’;
execute immediate sqlstr into dept_row_data;
dbms_output.put_line(dept_row_data.deptno);
end;

–if 语句
–select * from emp;
declare
vSal emp.sal%type;
begin
select sal into vSal from emp where empno = 7566;
if (vSal < 1200) then
dbms_output.put_line(‘小于1200’);
elsif (vSal < 2000) then
dbms_output.put_line(‘小于2000’);
else
dbms_output.put_line(‘大于2000’);
end if;
end;

–loop循环
declare
i binary_integer := 10;
begin
loop
dbms_output.put_line(i);
i := i - 1;
exit when(i < 1);
end loop;
end;

–while循环
declare
i binary_integer := 1;
begin
while i < 11 loop
dbms_output.put_line(i);
i := i + 1;
end loop;
end;

–for 循环
begin
for i in 1..10 loop
dbms_output.put_line(i);
end loop;
for i in reverse 1..10 loop
dbms_output.put_line(i);
end loop;
end;
版权维护转载hoojo博客的。

Oracle笔记 七、PL/SQL 异常处理
–异常处理
declare
sNum number := 0;
begin
sNum := 5 / sNum;
dbms_output.put_line(sNum);
exception
when others then
dbms_output.put_line(‘is Error!’);
end;

–自定义异常
declare
ex_custom_invaild_age exception; –自定义的异常myerr
age int;
begin
age := &请输入年龄;
if (age < 0) then
raise ex_custom_invaild_age; –引发自定义异常
else
dbms_output.put_line(‘年龄是：’ || age);
end if;
exception
when ex_custom_invaild_age then
dbms_output.put_line(‘非法的年龄’);
end;

–引发应用程序异常
–raise_application_error(异常编号，说明);
declare
age int;
begin
age := &请输入年龄;
if (age < 0) then
raise_application_error(-20500, ‘年龄不能为负数’);
else
dbms_output.put_line(‘年龄是：’ || age);
end if;
end;

–非预定义异常
declare
ex_custom_error exception;
pragma exception_init(ex_custom_error, -1); –把一个编号和一个自定义异常关联，
–相当于把-1编号的异常命名为ex_custom_error，这样就可以捕获这种异常
begin
insert into dept values(10, ‘aaa’, ‘bbb’);
exception
when ex_custom_error then
dbms_output.put_line(‘部门编号已经存在’);
end;

–异常处理
declare
vSal emp.sal%type;
begin
select sal into vSal from emp;
exception
when too_many_rows then
dbms_output.put_line(‘多条数据’);
when others then
dbms_output.put_line(‘Error’);
end;

declare
vSal emp.sal%type;
begin
select sal into vSal from emp where empno = 1;
exception
when no_data_found then
dbms_output.put_line(‘没有数据’);
when others then
dbms_output.put_line(‘Error’);
end;

–异常日志处理
create table errorLog (
id number primary key,
errCode number,
errMsg varchar2(1024),
errDate date
);
–创建序列，从1开始，每次加1
create sequence seq_errorLog_id start with 1 increment by 1;

declare
vDeptno dept.deptno%type := 10;
vErrCode number;
vErrMsg varchar2(1024);
begin
delete from dept where deptno = vDeptno;
commit;
exception
when others then
rollback;
vErrCode := SQLCODE;
vErrMsg := SQLERRM;
insert into errorLog values(seq_errorLog_id.nextval, vErrCode, vErrMsg, sysdate);
commit;
end;

select * from errorLog;

版权维护转载hoojo博客的。

1、简单SQL语句，HellWorld示例
–输出信息
begin
dbms_output.put_line(‘Oracle Hello World!’);
end;

2、变量的定义、使用
–定义变量
declare
sName varchar2(20);
begin
sName := ‘jack’;
dbms_output.put_line(sName);
end;

–常用类型
declare
sNum number(1);
sCount binary_integer := 0;
sSal number(7, 2) := 5000.00;
sDate date := sysdate;
sPI number(3, 2) := 3.14;
sValid boolean := true;
sName varchar2(20) := ‘Jackson’;
begin
dbms_output.put_line(‘sName:’ || sName);
dbms_output.put_line(‘sCount:’ || sCount);
dbms_output.put_line(‘sSal:’ || sSal);
dbms_output.put_line(‘sDate:’ || sDate);
dbms_output.put_line(‘sPI:’ || sPI);
–dbms_output.put_line(‘sValid:’ || sValid);
dbms_output.put_line(‘sName:’ || sName);
end;

–定义Table变量类型
declare
type type_table_emp_empno is table of emp.empno%type index by binary_integer;
empnos type_table_emp_empno;
begin
empnos(0) := 7369;
empnos(2) := 6789;
empnos(-1) := 6543;
dbms_output.put_line(empnos(-1));
end;

–定义record变量类型
declare
type type_record_dept is record (
deptno dept.deptno%type,
dname dept.dname%type,
loc dept.loc%type
);
temp type_record_dept;
begin
temp.deptno := 56;
temp.dname := ‘software’;
temp.loc := ‘gz’;
dbms_output.put_line(temp.deptno || ‘ ‘ || temp.dname || ‘ ‘ || temp.loc);
end;

–使用rowtype声明record变量
declare
temp dept%rowtype;
begin
temp.deptno := 57;
temp.dname := ‘it’;
temp.loc := ‘sz’;
dbms_output.put_line(temp.deptno || ‘ ‘ || temp.dname || ‘ ‘ || temp.loc);
end;

–sql语句完成变量赋值
declare
v$sal emp.sal%type;
v$ename emp.ename%type;
begin
select sal, ename into v$sal, v$ename from emp where rownum = 1;
dbms_output.put_line(v$sal || ‘ ‘ || v$ename);
end;

–sql语句完成rowtype变量赋值
declare
v_row_emp emp%rowtype;
begin
select * into v_row_emp from emp where empno = 7698;
dbms_output.put_line(v_row_emp.sal || ‘ ‘ || v_row_emp.ename);
end;

–sql语句完成变量插入数据
create table dept2 as select * from dept;
declare
deptno dept.deptno%type := 57;
dname dept.dname%type := ‘software’;
loc dept.loc%type := ‘gz’;
begin
insert into dept2 values(deptno, dname, loc);
commit;
end;

Oracle笔记 五、创建表、约束、视图、索引、序列、同义词、表空间
alter table userInfo add(msn varchar2(20));

1、建表
create table userInfo (
id number(6),
name varchar2(20),
sex number(1),
age number(3),
birthday date,
address varchar2(50),
email varchar2(25),
tel number(11)
);

2、创建约束
不带约束名称的：
create table userInfo (
id number(6) primary key,–主键
name varchar2(20) not null,–非空
sex number(1),
age number(3) default 18,
birthday date,
address varchar2(50),
email varchar2(25) unique,–唯一
tel number(11),
deptno number(2) references dept(deptno)—外键
);

带约束名称：
create table userInfo (
       id number(6) constraint id_pk primary key,
       name varchar2(20) constraint name_nn not null,
       sex number(1),
       age number(3) default 18,
       birthday date,
       address varchar2(50),
       email varchar2(25) constraint email_uqe unique,
       tel number(11),
       deptno number(2) constraint dept_deptno_ref references dept(deptno)
);

列模式：
create table userInfo (
       id number(6),
       name varchar2(20),
       sex number(1),
       age number(3) default 18,
       birthday date,
       address varchar2(50),
       email varchar2(25),
       tel number(11),
       deptno number(2),
       constraint id_pk primary key (id),--也可以两个以上，联合主键
       constraint dept_deptno_ref foreign key (deptno) references dept(deptno),
       constraint emial_name_uqe unique (email, name)
);
Alter模式：
    alter table userInfo add(msn varchar2(20));
    alter table userInfo modify(msn varchar2(25));
    alter table userInfo drop(msn);

    alter table userInfo drop constraint id_pk;
    alter table userInfo add constraint id_pk primary key (id);

3、创建视图
create table v$_dept_view
as
select deptno, dname from dept;

--重新编译视图
alter view v$_dept_view compile;
提示：视图一般是一个表或多个表的查询或子查询，这样可以减少代码量，但同时增加了对数据库视图的维护程度，如：某个表字段被删除或是修改，视图也要重新创建或修改，同时占用了数据库的一部分空间；视图就是一个虚拟的表格；

4、创建索引
普通索引：create index idx_dpt_dname on dept(dname);
联合索引：create index idx_dept_dname_deptno on dept(dname, deptno);
–唯一索引
create unique index idx_emp_ename on scott.emp(ename);
–反向键索引
create index idx_emp_rev_no on scott.emp(empno) reverse;
–位图索引
create bitmap index idx_emp_name on scott.emp(dname);
–索引组织表,一定要有主键
create table tab (
id int primary key,
name varchar2(20)
) organization index;
–索引组织表的insert效率非常低

--分区表索引
create index idx_name on table(col) local/global;
--索引分区
提示：当给表创建主键或唯一键约束时，系统也会创建一个约束给该字段；同样创建索引也会占用数据库空间；索引在访问、查询的时候效率有提高，但是在修改表的时候效率就会降低；

5、创建序列
create sequence seq;
select seq.nextval from dual;
insert into tab values(sql.nextval, ‘music’);

create sequence seqtab
start with 2 –从2开始
increment by 3—每次加3
nomaxvalue—没有最大值
minvalue 1—最小值1
nocycle—不循环
nocache;--不缓存

--修改序列 ，不能修改起始值
alter sequence seqtab
maxvalue 1000;

6、创建同义词
同义词，顾名思义就是说别名、或是另一个名字。
create synonym scott_emp for scott.emp;
create public synonym scott_dept for scott.dept;

select * from scott_emp;
select * from scott_dept;

7、创建表空间
create tablespace HooMS
datafile ‘E:\HooMS.dbf’
size 5M
autoextend on next 2M maxsize 10M;

--创建用户、分配可以操作表空间
create user hoo
identified by hoo
default tablespace  HooMS
temporary tablespace temp;

--创建表空间
create tablespace myMS
datafile 'c:\myMS.dbf'
size 1M
autoextend on;

--扩展表空间--修改表空间大小
alter database
datafile 'c:\myMS.dbf'
resize 2M;

--扩展表空间--添加数据文件
alter tablespace myMS
add datafile 'c:\myMS_2.dbf'
size 1M;

--设置dbf文件自动增长
alter database 
datafile 'c:\myMS_2.dbf'
autoextend on next 2M maxsize 4M;

--表空间重命名
alter tablespace myMS
rename to hooMS;

--分离表空间（脱机）
alter tablespace hooMS
offline temporary;

--归档模式下脱机
alter tablespace hooMS
offline immediate;

--使表空间联机
alter tablespace hooMS online;

--删除无数据的表空间
drop tablespace hooMS;

--删除带数据的表空间
drop tablespace hooMS
including contents;

版权维护转载hoojo博客的。

Oracle笔记 四、增删改、事务

1、插入
insert into dept values(50, ‘soft’, ‘Guangzhou’);
insert into dept(deptno, dname) values(60, ‘software test’);
insert into dept select * from dept where deptno = 20;

2、删除
delete from dept where deptno = 20;
delete from dept;

3、修改
update dept set dname = dname || ‘ ^.^’ where deptno = 20;

4、事务
每个修改语句完成后都可以回滚或提交，即rollback、commit；
eg:
insert into dept values(50, ‘soft’, ‘Guangzhou’);
rollback;
这样就回滚了，等于上面的没有插入；如果commit就提交了，此后如果再rollback也无力回天了。
下面这样也是无法回滚的：
insert into dept values(50, ‘soft’, ‘Guangzhou’);
create table temp as select * from dept;
rollback;
再次查询，发现无法回滚；同样增删改后面运行grant/create语句也是无法回滚的；

版权维护转载hoojo博客的。