/* sqlplus blis/blis@tbls40 @test.sq; */
/* create table persons(
id number(2),
lastname varchar2(15),
firstname varchar2(15)
);
create table team(
id number(2),
teamname varchar2(15),
teamleader varchar2(15)
); */
/* insert into persons values ('2','Adams','John');
insert into persons values ('3','Kevin','Li');
insert into persons values ('4','Rafiki','Li');
insert into persons values ('6','Bob','Cao');
insert into persons values ('1','Adams','John');
insert into persons values ('5','Bush','George');
insert into team values ('1','shift1','Rafiki');
insert into team values ('2','shift2','Bob'); */
/* alter table persons add team varchar2(15);
alter table persons drop column firstname ;
--alter table persons alter column team varchar2(30); */
/* update persons set team = 'shift1' where id = '2';
update persons set team = 'shift1' where id = '3';
update persons set team = 'shift1' where id = '4';
update persons set team = 'shift2' where id = '6';
update persons set team = 'shift2' where id = '1';
update persons set team = 'shift2' where id = '5';
update persons set lastname = 'Joan' where id = '2';
update persons set lastname = 'Xiaoxi' where id = '1';
update persons set lastname = 'Sunny' where id = '5';
commit; */
/* -- /
select * from persons;
select * from persons where id='1';
/ */
/* --declare，begin，exception
set serveroutput on;
declare
v_firstname varchar2(12);
--"end" varchar2(12);
begin
select firstname into v_firstname from persons where id = '1';
dbms_output.put_line('输出结果是： ' || v_firstname);
--select firstname into "end" from persons where id = '1';
--dbms_output.put_line('输出结果是： ' || "end");
EXCEPTION
when no_data_found then
dbms_output.put_line('没有对应的数据！');
when too_many_rows then
dbms_output.put_line('对应数据过多，请确认！');
end;
/ */
/* --declare，begin，exception
set serveroutput on;
declare
v_firstname varchar2(12);
v_lastname varchar2(12);
begin
select firstname ,lastname /*多个值的时候顺序一一对应*/ into v_lastname,v_firstname from persons where id in('&abc');
dbms_output.put_line('firstname and lastname 是： ' || v_firstname ||' ' || v_lastname);
EXCEPTION
when no_data_found then
dbms_output.put_line('没有对应的数据！');
when too_many_rows then
dbms_output.put_line('对应数据过多，请确认！');
end;
/
*/
/* --append
select * from persons;
append where lastname ='Kevin' order by id;
/ */
/* --input
select * from persons;
input
order by
id
; */
/* --change
select * from persons;
append where rownum < 3 order by id;
/
change /3/6;
/
change /order by id
/ */
/* --del
create table persons1 as select * from persons;
select * from persons;
del --删除缓冲区上一行
/
select *
from persons
order by id;
del 3 --删除缓冲区上的第三行
/
select *
from persons
order by id
desc;
del 3 * --删除缓冲区上的第三行到当前行
/
drop table persons1;
*/
/* --clear buffer
select * from persons;
/
clear buffer;
/ */
/* -- list lis li l
select *
from persons
order by team asc
, id desc;
list;
list 2;
list 2 last; */
/* -- edit ed 调用记事本保存缓冲区内容 --save file 保存为文件
select * from persons;
ed;
save c:files */
/* -- pagesize，查询结果的格式化
set pagesize 8;--每页显示行数，不是每屏
show pagesize;
set newpage 4;--设置每页之间的页间距行行数
show newpage;
set linesize 80; --设置每行显示的字符数
show linesize;
select * from persons; */
/* -- spool
spool c:workshoporacle20130723.sql
select * from persons order by id desc;
spool off */
/* -- 调用外部sql文件 @path+filename @c:usersweiguli est.sql 或者 start c:usersweiguli est.sql
blis/blis@tbls40 @test.sql
blis/blis@tbls40 start test.sql */
/* -- remark rem 注释命令
spool c:workshoporacle20130723.sql;
rem this is a search;
rem by weiguli;
rem 2013,07,23;
select * from persons;
spool off; */
/* --myfun 自定义函数
create or replace function myfun(num1 in integer, num2 in integer) return integer is
Result integer;
begin
return(num1+num2);
end myfun;
/
select myfun(2,3) from dual;*/
--exit;