SQL> conn /as sysdba
已连接。
SQL> grant resource to hr
2 ;
授权成功。
SQL> grant connect to hr;
授权成功。
SQL> conn hr/hr
已连接。
SQL> select table_name from user_tables;
未选定行
SQL> create table employees(
2 last_name varchar(10),
3 department_id varchar(4),
4 salary number(10)
5 )
6 ;
表已创建。
SQL> create table departments
2 (
3 department_id varchar(4),
4 department_name varchar(10)
5 );
表已创建。
SQL> l
1 begin
2 for i in 1..10
3 loop
4 insert into employees values('name'|i,i,20*i);
5 end loop;
6* end;
SQL> l 4
4* insert into employees values('name'|i,i,20*i);
SQL> c /|/||
4* insert into employees values('name'||i,i,20*i);
SQL> /
PL/SQL 过程已成功完成。
SQL> select * from employees;
LAST_NAME DEPA SALARY
---------- ---- ----------
name1 1 20
name2 2 40
name3 3 60
name4 4 80
name5 5 100
name6 6 120
name7 7 140
name8 8 160
name9 9 180
name10 10 200
已选择10
SQL>
1 begin
2 for i in 5..15
3 loop
4 insert into departments values(i,'depart'||i);
5 end loop;
6* end;
SQL> /
PL/SQL 过程已成功完成。
SQL> desc employees;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
LAST_NAME VARCHAR2(10)
DEPARTMENT_ID VARCHAR2(4)
SALARY NUMBER(10)
SQL> desc departments;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
DEPARTMENT_ID VARCHAR2(4)
DEPARTMENT_NAME VARCHAR2(10)
SQL> edit
已写入 file afiedt.buf
1 select last_name,department_name from employees e
2* left outer join departments d on (e.department_id = d.department_id)
SQL> /
LAST_NAME DEPARTMENT
---------- ----------
name5 depart5
name6 depart6
name7 depart7
name8 depart8
name9 depart9
name10 depart10
name1
name3
name2
name4
已选择10行。
SQL> edit;
已写入 file afiedt.buf
1 select last_name,department_name from employees e
2* right outer join departments d on (e.department_id = d.department_id)
SQL> /
LAST_NAME DEPARTMENT
---------- ----------
name5 depart5
name6 depart6
name7 depart7
name8 depart8
name9 depart9
name10 depart10
depart14
depart12
depart15
depart13
depart11
已选择11行。
SQL> edit;
已写入 file afiedt.buf
1 select last_name,department_name from employees e,
2* departments d where e.department_id(+)= d.department_id
SQL> /
LAST_NAME DEPARTMENT
---------- ----------
name5 depart5
name6 depart6
name7 depart7
name8 depart8
name9 depart9
name10 depart10
depart14
depart12
depart15
depart13
depart11
已选择11行。
SQL> edit;
已写入 file afiedt.buf
1 select last_name,department_name from employees e,
2* departments d where e.department_id= d.department_id(+)
SQL> /
LAST_NAME DEPARTMENT
---------- ----------
name5 depart5
name6 depart6
name7 depart7
name8 depart8
name9 depart9
name10 depart10
name1
name3
name2
name4
已选择10行。
SQL>