Oracle学习05【持续更新】

Oracle继续学习中。。。。。。

/*

以下代码是对emp表进行显示宽度设置
*/
col empno for 9999;
col ename for a10;
col job for a10;
col mgr for 9999;
col hiredate for a12;
col sal for 999999;
col comm for 9999;
col deptno for 99;

//--------------------------------------------------------------------------------------------------------------------------------

写一个PLSQL程序，输出"hello world"字符串
begin 
dbms_output.put_line('hello world');
end;
/

SQL> begin
  2  dbms_output.put_line('hello world');
  3  end;
  4  /

PL/SQL 过程已成功完成。

设置显示PLSQL程序的输出结果，默认情况下，不显示PLSQL程序的输出结果
set serveroutput on;

使用基本类型变量和常量，求10+100的和
declare
  psum number(3);
  msg  varchar2(20); 
begin
  psum:=100+10;
  msg:='100+10的和为:';
  dbms_output.put_line(msg||psum);
end;
/

SQL> declare
  2    psum number(3);
  3    msg  varchar2(20);
  4  begin
  5    psum:=100+10;
  6    msg:='100+10的和为:';
  7    dbms_output.put_line(msg||psum);
  8  end;
  9  /
100+10的和为:110

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.04

使用loop循环显示1-10
declare
   pnum number(2); 
begin
   pnum:=1;  
   --以下是循环语句
   loop
      --只有当条件成立后，立即退出
      exit when pnum>10;
      --如果条件没成立，继续循环
      dbms_output.put_line(pnum);
      pnum:=pnum+1;
   end loop;
end;
/

SQL> declare
  2     pnum number(2);
  3  begin
  4     pnum:=1;
  5  loop
  6  exit when pnum>10;
  7  dbms_output.put_line(pnum);
  8  pnum:=pnum+1;
  9  end loop;
 10  end;
 11  /
1
2
3
4
5
6
7
8
9
10

PL/SQL 过程已成功完成。

使用while循环显示10-20
declare
    pnum number(2):=10;
begin
    while pnum<=20
    loop
       dbms_output.put_line(pnum);
       pnum:=pnum+2;
    end loop; 
end;
/

SQL> declare
  2      pnum number(2):=10;
  3  begin
  4      while pnum<=20
  5      loop
  6         dbms_output.put_line(pnum);
  7         pnum:=pnum+2;
  8      end loop;
  9  end;
 10  /
10
12
14
16
18
20

PL/SQL 过程已成功完成。

使用for循环显示20-30
declare
    pnum number(2):=20;
begin
    for pnum in 20..30
    loop
      dbms_output.put_line(pnum);
    end loop;
end;
/

SQL> declare
  2      pnum number(2):=20;
  3  begin
  4      for pnum in 20..30
  5      loop
  6        dbms_output.put_line(pnum);
  7      end loop;
  8  end;
  9  /
20
21
22
23
24
25
26
27
28
29
30

PL/SQL 过程已成功完成。

使用光标，查询所有员工的姓名和工资，如果需要保存多行记录时，使用光标cursor。
declare
   --光标中装着多条记录，类似于ResultSet集合
   cursor cursor_emp is select ename,sal from emp;
   --声明自定义变量  
   pename emp.ename%type;
   psal   emp.sal%type;
begin
   --开打光标
   open cursor_emp; 
   --循环取出每一条记录的值
   loop      
       --取得每一条的记录，同时向下移动光标
       fetch cursor_emp into pename,psal;                   
       --当光标没有找到有用的记录时，就退出
       exit when cursor_emp%notfound;
       --显示
       dbms_output.put_line(pename||'的工资是'||psal);
   end loop;   
   --关闭光标，释放资源
   close cursor_emp;
end;
/
create table person(
  id number(2),
  name varchar2(10) default 'xxxx',
  salary number(7,2) default 99999.99,
  birthday date default sysdate
);
--7表示整数+小数，除小数点外，一共显示7位
insert into person(id,name) values(1,'jack');
insert into person(id,name) values(2,'marry');
insert into person(id,name) values(3,'sisi');

SQL> create table person(
  2    id number(2),
  3    name varchar2(10) default 'xxxx',
  4    salary number(7,2) default 99999.99,
  5    birthday date default sysdate
  6  );

表已创建。

SQL> insert into person(id,name) values(1,'jack');

已创建 1 行。

SQL> insert into person(id,name) values(2,'marry');

已创建 1 行。

SQL> insert into person(id,name) values(3,'sisi');

将emp表中的数据复制到new_emp表中
create table new_emp as select * from emp;

增加image列

alter table person
add image blob; 
修改name列的长度为20
alter table person
modify name varchar2(20);
删除image列
alter table person
drop column image;
重名列名name为username
alter table person rename column name to username;
删除person表，oracle会将删除后的表，放入回收站中，用户都有回收站
drop table person;

查询回收站
show recyclebin;

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------

EMP              BIN$knFF4cVYTSap1sgcipWpCw==$0 TABLE        2016-06-22:22:48:13

EMP              BIN$b7kx/dCtSIa2fnQHSeK9QQ==$0 TABLE        2016-06-22:22:45:34

NEW_EMP          BIN$2R70hRf0SpO34bu7QxpQ0Q==$0 TABLE        2016-06-22:22:53:29

清空回收站
purge recyclebin;

SQL> purge recyclebin;

回收站已清空。

彻底删除person表，oracle不会将删除后的表，放入回收站中
drop table person purge;
表/视图/数据库对象/重命名
rename emp to xx;

创建表，使用primary key/not null/unique/foreign key约束

create table customers(
 id number primary key,
 name varchar2(20) not null
);
insert into customers(id,name) values(1,'jack');
insert into customers(id,name) values(2,'marry');


create table orders(
 id number primary key,
 orderno varchar2(20) not null unique,
 cid number,
 constraint cid_FK foreign key(cid) references customers(id) on delete set null
);
insert into orders(id,orderno,cid) values(1,'order100',1);
insert into orders(id,orderno,cid) values(2,'order200',1);
insert into orders(id,orderno,cid) values(3,'order300',1);
insert into orders(id,orderno,cid) values(4,'order400',2);
insert into orders(id,orderno,cid) values(5,'order500',2);
constraint cid_FK foreign key(cid) references customers(id) on delete cascade

显示当前用户
show user;
切换为管理员sysdba
conn / as sysdba;

SQL> show user;
USER 为 "SCOTT"
SQL> conn / as sysdba;
已连接。

切换普通用户scott
conn scott/scott;

SQL> conn scott/scott
已连接。