1、Sqlplus的使用,执行一个SQL脚本文件,start file_name file_name,常见命令,对当前的输入进行编辑 Edit 重新运行上一次运行的sql语句 / 将显示的内容输出到指定文件 SPOOL file_name 关闭spool输出 SPOOL OFF 只有关闭spool输出，才会在输出文件中看到输出的内容,COL命令,主要格式化列的显示形式。 该命令有许多选项，具体如下： COLUMN column|expr option . Option选项可以是如下的子句: ALIAS alias CLEAR FOLD_AFTER FOLD_BEFORE FORMAT format
2、 HEADING text JUSTIFY LEFT|CENTER|CENTRE|RIGHT LIKE expr|alias NEWLINE NEW_VALUE variable NOPRINT|PRINT NULL text OLD_VALUE variable ON|OFF WRAPPED|WORD_WRAPPED|TRUNCATED,Col的使用(1/4),改变缺省的列标题 col LOC heading location 将列名ENAME改为新列名EMPLOYEE NAME并将新列名放在两行上： COLUMN ENAME HEADING Employee|Name 改变列的显示长度：
3、col ename format a40 设置列标题的对齐方式 JUSTIFY LEFT|CENTER|CENTRE|RIGHT col ename justify center 不让一个列显示在屏幕上 NOPRINT|PRINT col job noprint,Col的使用(2/4),格式化NUMBER类型列的显示： COLUMN SAL FORMAT $99,999 显示列值时，如果列值为NULL值，用text值代替NULL值 COMM NULL text COL COMM NULL text 设置一个列的回绕方式 WRAPPED|WORD_WRAPPED|TRUNCATED SQLCOL
4、 COL1 FORMAT A5 SQLCOL COL1 WRAPPED COL COL1 WORD_WRAPPED 显示列的当前的显示属性值 COLUMN column_name 将所有列的显示属性设为缺省值 CLEAR COLUMNS,Col的使用(3/4),屏蔽掉一个列中显示的相同的值 BREAK ON break_column BREAK ON DEPTNO SELECT DEPTNO, ENAME, SAL FROM EMP WHERE SAL 2500 ORDER BY DEPTNO; 在上面屏蔽掉一个列中显示的相同的值的显示中，每当列值变化时在值变化之前插入n个空行。 BREAK
5、ON break_column SKIP n BREAK ON DEPTNO SKIP 1 显示对BREAK的设置 BREAK,Col的使用(4/4),删除BREAKS的设置 CLEAR BREAKS,Set 命令,该命令包含许多子命令： SET system_variable value system_variable value 可以是如下的子句之一： APPINFOON|OFF|text ARRAYSIZE 15|n AUTOCOMMITON|OFF|IMMEDIATE|n AUTOPRINT ON|OFF AUTORECOVERY ON|OFF AUTOTRACE ON|OFF|TRA
6、CEONLY EXPLAIN STATISTICS BLOCKTERMINATOR .|c CMDSEP ;|c|ON|OFF COLSEP _|text COMPATIBILITYV7|V8|NATIVE CONCAT .|c|ON|OFF COPYCOMMIT 0|n COPYTYPECHECK ON|OFF DEFINE &|c|ON|OFF DESCRIBE DEPTH 1|n|ALLLINENUM ON|OFFINDENT ON|OFF ECHO ON|OFF EDITFILE file_name.ext EMBEDDED ON|OFF ESCAPE |c|ON|OFF,FEEDBA
7、CK 6|n|ON|OFF FLAGGER OFF|ENTRY |INTERMEDIATE|FULL FLUSH ON|OFF HEADING ON|OFF HEADSEP |c|ON|OFF INSTANCE instance_path|LOCAL LINESIZE 80|n LOBOFFSET n|1 LOGSOURCE pathname LONG 80|n LONGCHUNKSIZE 80|n MARKUP HTML ON|OFF HEAD text BODY text ENTMAP ON|OFF SPOOL ON|OFF PREFORMAT ON|OFF NEWPAGE 1|n|NON
8、E NULL text NUMFORMAT format NUMWIDTH 10|n PAGESIZE 24|n PAUSE ON|OFF|text RECSEP WRAPPED|EACH|OFF RECSEPCHAR _|c SERVEROUTPUT ON|OFF SIZE n FORMAT WRAPPED|WORD_ WRAPPED|TRUNCATED SHIFTINOUT VISIBLE|INVISIBLE,SHOWMODE ON|OFF SQLBLANKLINES ON|OFF SQLCASE MIXED|LOWER|UPPER SQLCONTINUE |text SQLNUMBER
9、ON|OFF SQLPREFIX #|c SQLPROMPT SQL|text SQLTERMINATOR ;|c|ON|OFF SUFFIX SQL|text TAB ON|OFF TERMOUT ON|OFF TIME ON|OFF TIMING ON|OFF TRIMOUT ON|OFF TRIMSPOOL ON|OFF UNDERLINE -|c|ON|OFF VERIFY ON|OFF WRAP ON|OFF,Set的使用,1). 设置当前session是否对修改的数据进行自动提交 SQLSET AUTOCOMMIT ON|OFF|IMMEDIATE| n 2)在用start命令执行
10、一个sql脚本时，是否显示脚本中正在执行的SQL语句 SQL SET ECHO ON|OFF 3).是否显示当前sql语句查询或修改的行数 SQL SET FEEDBACK 6|n|ON|OFF 默认只有结果大于6行时才显示结果的行数。如果set feedback 1 ，则不管查询到多少行都返回。当为off 时，一律不显示查询的行数 4).是否显示列标题 SQL SET HEADING ON|OFF 当set heading off 时，在每页的上面不显示列标题，而是以空白行代替 5).设置一行可以容纳的字符数 SQL SET LINESIZE 80|n 如果一行的输出内容大于设置的一行可容纳
11、的字符数，则折行显示。,6).设置页与页之间的分隔 SQL SET NEWPAGE 1|n|NONE 当set newpage 0 时，会在每页的开头有一个小的黑方框。 当set newpage n 时，会在页和页之间隔着n个空行。 当set newpage none 时，会在页和页之间没有任何间隔。 7).显示时，用text值代替NULL值 SQL SET NULL text 8).设置一页有多少行数 SQL SET PAGESIZE 24|n 如果设为0，则所有的输出内容为一页并且不显示列标题 9).是否显示用DBMS_OUTPUT.PUT_LINE包进行输出的信息。 SQL SET SE
12、RVEROUTPUT ON|OFF 在编写存储过程时，我们有时会用dbms_output.put_line将必要的信息输出，以便对存储过程进行调试，只有将serveroutput变量设为on后，信息才能显示在屏幕上。,10).当SQL语句的长度大于LINESIZE时，是否在显示时截取SQL语句。 SQL SET WRAP ON|OFF 当输出的行的长度大于设置的行的长度时（用set linesize n命令设置），当set wrap on时，输出行的多于的字符会另起一行显示，否则，会将输出行的多于字符切除，不予显示。 11).是否在屏幕上显示输出的内容，主要用与SPOOL结合使用。 SQL S
13、ET TERMOUT ON|OFF 在用spool命令将一个大表中的内容输出到一个文件中时，将内容输出在屏幕上会耗费大量的时间，设置set termspool off后，则输出的内容只会保存在输出文件中，不会显示在屏幕上，极大的提高了spool的速度。 12).将SPOOL输出中每行后面多余的空格去掉 SQL SET TRIMSOUT ON|OFF,13)显示每个sql语句花费的执行时间 set TIMING ON|OFF 14修改sql buffer中的当前行中，第一个出现的字符串 CHANGE /old_value/new_value SQL l 1* select * from dept
14、 SQL c/dept/emp 1* select * from emp 15编辑sql buffer中的sql语句 EDIT 16显示sql buffer中的sql语句，list n显示sql buffer中的第n行，并使第n行成为当前行 LIST n,18将指定的文本加到sql buffer的当前行后面 APPEND SQL select deptno, 2 dname 3 from dept; SQL L 2 2* dname SQL a ,loc 2* dname,loc SQL L 1 select deptno, 2 dname,loc 3* from dept,19将sql bu
15、ffer中的sql语句保存到一个文件中 SAVE file_name 20将一个文件中的sql语句导入到sql buffer中 GET file_name 21再次执行刚才已经执行的sql语句 RUN 或 / 22执行一个存储过程 EXECUTE procedure_name 23在sql*plus中连接到指定的数据库 CONNECT user_name/passwddb_alias 24设置每个报表的顶部标题 TTITLE 25设置每个报表的尾部标题 BTITLE,26写一个注释 REMARK text 27将指定的信息或一个空行输出到屏幕上 PROMPT text 28将执行的过程暂停，等
16、待用户响应后继续执行 PAUSE text SqlPAUSE Adjust paper and press RETURN to continue.,29将一个数据库中的一些数据拷贝到另外一个数据库（如将一个表的数据拷贝到另一个数据库） COPY FROM database | TO database | FROM database TO database APPEND|CREATE|INSERT|REPLACE destination_table (column, column, column, .) USING query sqlCOPY FROM SCOTT/TIGERHQ TO JOHN
17、/CHROMEWEST create emp_temp USING SELECT * FROM EMP,30不退出sql*plus，在sql*plus中执行一个操作系统命令： HOST Sql host hostname 该命令在windows下可能被支持。 31在sql*plus中，切换到操作系统命令提示符下，运行操作系统命令后，可以再次切换回sql*plus： ! sql! $hostname $exit sql 该命令在windows下不被支持。,32显示sql*plus命令的帮助 HELP 显示sql*plus系统变量的值或sql*plus环境变量的值 Syntax:SHOW opti
18、on ALL BTITLE ERRORS FUNCTION|PROCEDURE|PACKAGE|PACKAGE BODY| TRIGGER|VIEW|TYPE|TYPE BODY schema.name LNO PARAMETERS parameter_name PNO RELEASE REPFOOTER REPHEADER SGA SPOOL SQLCODE TTITLE USER,Show的例子,1) . 显示当前环境变量的值： Show all 2) . 显示当前在创建函数、存储过程、触发器、包等对象的错误信息 Show error 当创建一个函数、存储过程等出错时，变可以用该命令查看在
19、那个地方出错及相应的出错信息，进行修改后再次进行编译。 3) . 显示初始化参数的值： show PARAMETERS parameter_name 4) . 显示数据库的版本： show RELEASE 5) . 显示SGA的大小 show SGA 6). 显示当前的用户名 show user,PL/Sql,数据方案对象,管理方案对象,编程方案对象,查看方案对象,Oracle的标量数据类型,数据 表类型,创建表,CREATE TABLE “SCOTT“.“DIRECTOR“ (“DIRECTOR_ID“ NUMBER(6) NOT NULL,“NAME“ VARCHAR2(10) NOT N
20、ULL,“ZHICHENG“ VARCHAR2(20) NOT NULL,“ZHIWU“ VARCHAR2(20) NOT NULL, CONSTRAINT “导师编号主码“ PRIMARY KEY(“DIRECTOR_ID“) USING INDEX);,CREATE TABLE “SCOTT“.“STUDENT“ (“STUDENT_ID“ NUMBER(8) NOT NULL,“NAME“ VARCHAR2(10) NOT NULL,“PROFESSIONAL“ VARCHAR2(10) NOT NULL,“BIRTHDAY“ DATE NOT NULL,“DIRECTOR_ID“ NU
21、MBER(6) NOT NULL, CONSTRAINT “研究生编号主码“ PRIMARY KEY(“STUDENT_ID“) USING INDEX,CONSTRAINT “导师编号外码“ FOREIGN KEY(“DIRECTOR_ID“) REFERENCES “SCOTT“.“DIRECTOR“(“DIRECTOR_ID“);,修改表,修改数据类型长度 ALTER TABLE “SCOTT“.“DIRECTOR“ MODIFY(“NAME“ VARCHAR2(12) 修改字段名 ALTER TABLE “SCOTT“.“DIRECTOR“ RENAME COLUMN “ZHIWU“
22、TO “ZHIWU_1“ 删除列 ALTER TABLE “SCOTT“.“DIRECTOR“ DROP (“ZHIWU“) CASCADE CONSTRAINTS 添加列 ALTER TABLE “SCOTT“.“DIRECTOR“ ADD (“REAMRK“ VARCHAR2(10) NOT NULL) ;,插入数据,INSERT INTO “SCOTT“.“DIRECTOR“ (“DIRECTOR_ID“ ,“NAME“ ,“ZHICHENG“ ,“ZHIWU“ ) VALUES (200201 ,张三丰 ,博导 ,系主任); INSERT INTO “SCOTT“.“DIRECTOR“
23、 (“DIRECTOR_ID“ ,“NAME“ ,“ZHICHENG“ ,“ZHIWU“ ) VALUES (200202 ,张翠山 ,硕导 ,室主任); INSERT INTO “SCOTT“.“DIRECTOR“ (“DIRECTOR_ID“ ,“NAME“ ,“ZHICHENG“ ,“ZHIWU“ ) VALUES (200203 ,张无忌 ,硕导 ,所主任 ); INSERT INTO “SCOTT“.“DIRECTOR“ (“DIRECTOR_ID“ ,“NAME“ ,“ZHICHENG“ ,“ZHIWU“ ) VALUES (200204 ,宋远桥 ,博导 ,系副主任 );,查询数
24、据,select * from scott.student;,更新数据,UPDATE “SCOTT“.“STUDENT“ SET DIRECTOR_ID = 200204WHERE STUDENT_ID = 20030103;,删除数据,DELETE FROM “SCOTT“.“STUDENT“ WHERE STUDENT_ID = 20030101; truncate table scott.director drop storage;,删除数据表,DROP TABLE SCOTT.DIRECTOR CASCADE CONSTRAINTS;,创建索引,CREATE INDEX “SCOTT“
25、.“INNN“ ON “SCOTT“.“BONUS“ (“JOB“)CREATE INDEX “SCOTT“.“姓名字段索引“ ON “SCOTT“.“STUDENT“(“NAME“) TABLESPACE “INDX“,视图,CREATE OR REPLACE VIEW “SCOTT“.“STUDENT数据表视图“ AS select student_id,name from scott.student WITH READ ONLY,查询视图,select “SCOTT“.“STUDENT数据表视图“.“STUDENT_ID“, “SCOTT“.“STUDENT数据表视图“.“NAME“ f
26、rom “SCOTT“.“STUDENT数据表视图“,约束条件,ALTER TABLE “SCOTT“.“STUDENT“ ADD (CONSTRAINT “研究生编号检查约束条件“ CHECK(student_id=20020101 and student_id=20030909),测试约束,INSERT INTO “SCOTT“.“STUDENT“ (“STUDENT_ID“ ,“NAME“ ,“PROFESSIONAL“ ,“BIRTHDAY“ ,“DIRECTOR_ID“ ) VALUES (20010101 ,纪晓芙 ,软件工程 ,TO_DATE(15-7月 -1971, dd-Mo
27、n-yyyy HH:MI:SS AM) ,200201);,SQL编程,PL/SQL程序结构,定义部分 程序部分 异常处理部分 可以省略,定义常量,declarepi constant number(9):=3.1415926; begincommit; end;,基本数据类型,定义变量,语法declareage number(3):=26; begincommit; end;,复合数据变量,%typeDeclaremydate tempuser.testtable.currentdate%type; begincommit; end;,记录类型变量,set serveroutput on d
28、eclaretype myrecord is record(myrecordnumber int,mycurrentdate date);srecord myrecord; beginselect * into srecord from tempuser.testtable where recordnumber=68;dbms_output.put_line(srecord.mycurrentdate); end;,%rowtype,%type获取字段的类型 %rowtype获取记录的类型Declaremytable testtable%rowtype; beginselect * into
29、mytable from tempuser.testtablewhere recordnumber=88;dbms_output.put_line(mytable.currentdate); end;,定义一维表类型变量,表类型变量与数据表是不同的概念:Declaretype tabletype1 is table of varchar2(4) index by binary_integer;type tabletype2 is table of tempuser.testtable.recordnumber%type index by binary_integer;table1 tablet
30、ype1;table2 tabletype2; begintable1(1):=大学;table1(2):=大专;table2(1):=88;table2(2):=55;dbms_output.put_line(table1(1)|table2(1);dbms_output.put_line(table1(2)|table2(2); end;,多维表类型 变量,Declaretype tabletype1 is table of testtable%rowtype index by binary_integer;table1 tabletype1; beginselect * into tab
31、le1(60)from tempuser.testtablewhere recordnumber=60;dbms_output.put_line(table1(60).recordnumber|table1(60).currentdate); end;,表类型变量的属性,set serveroutput on Declaretype tabletype1 is table of varchar2(9) index by binary_integer;table1 tabletype1; begintable1(1):=成都市;table1(2):=北京市;table1(3):=青岛市;dbms
32、_output.put_line(总记录数：|to_char(table1.count);dbms_output.put_line(第一条记录：|table1.first);dbms_output.put_line(最后条记录：|table1.last);dbms_output.put_line(第二条的前一条记录：|table1.prior(2);dbms_output.put_line(第二条的后一条记录：|table1.next(2); end;,数值表达式,set serveroutput on Declareresult integer; beginresult:=10+3*4-20
33、+5*2;dbms_output.put_line(运算结果是：|to_char(result); end;,字符表达式,|,关系表达式,关系表达式与SQL中一样,逻辑表达式,函数,if流程控制,set serveroutput on declarenumber1 integer:=90;number2 integer:=60; beginif number1=number2 thendbms_output.put_line(number1大于等于number2);end if; end;,set serveroutput on declarenumber1 integer:=80;numbe
34、r2 integer:=90; beginif number1=number2 thendbms_output.put_line(number1大于等于number2);elsedbms_output.put_line(number1小于number2);end if; end;,set serveroutput on declarenumber1 integer:=80;number2 integer:=90; beginif number1=number2 thenif number1=number2 thendbms_output.put_line(number1等于number2);e
35、lsedbms_output.put_line(number1小于number2);end if; elsedbms_output.put_line(number1大于number2);end if; end;,loop,set serveroutput on declarenumber1 integer:=80;number2 integer:=90;i integer:=0; beginloopnumber1:=number1+1;if number1=number2 thenexit;elsei:=i+1;end if; end loop;dbms_output.put_line(共循环
36、次数:|to_char(i); end;,set serveroutput on declarenumber1 integer:=80;number2 integer:=90;i integer:=0; beginloopnumber1:=number1+1;i:=i+1;exit when number1=number2;end loop;dbms_output.put_line(共循环次数:|to_char(i); end;,while,set serveroutput on declarenumber1 integer:=80;number2 integer:=90;i integer:
37、=0; beginwhile number1number2 loopnumber1:=number1+1;i:=i+1;end loop;dbms_output.put_line(共循环次数:|to_char(i); end;,事务处理,事务的自动提交设置 Set auto off Rollback select * from scott.emp; delete from scott.emp; rollback;,Savepoint点,insert into scott.emp(empno,ename,sal) values(9000,wang,2500); savepoint insertp
38、oint; rollback to insertpoint;,游标,set serveroutput on declaretempsal scott.emp.sal%type;cursor mycursor isselect * from scott.empwhere saltempsal; begintempsal:=800;open mycursor; end;,打开游标,提取游标数据,set serveroutput on declaretempsal scott.emp.sal%type;cursor mycursor isselect * from scott.empwhere sa
39、ltempsal;cursorrecord mycursor%rowtype; begintempsal:=800;open mycursor;fetch mycursor into cursorrecord;dbms_output.put_line(to_char(cursorrecord.deptno); end;,关闭游标,游标属性,%isopen属性 set serveroutput on declaretempsal scott.emp.sal%type;cursor mycursor isselect * from scott.empwhere saltempsal;cursorr
40、ecord mycursor%rowtype; begintempsal:=800;if mycursor%isopen thenfetch mycursor into cursorrecord;dbms_output.put_line(to_char(cursorrecord.deptno);elsedbms_output.put_line(游标没有打开！);end if; end;,%found属性,set serveroutput on declaretempsal scott.emp.sal%type;cursor mycursor isselect * from scott.empw
41、here saltempsal;cursorrecord mycursor%rowtype; begintempsal:=800;open mycursor;fetch mycursor into cursorrecord;if mycursor%found thendbms_output.put_line(to_char(cursorrecord.deptno);elsedbms_output.put_line(没有数据！);end if; end;,%notfound,set serveroutput on declaretempsal scott.emp.sal%type;cursor
42、mycursor isselect * from scott.empwhere saltempsal;cursorrecord mycursor%rowtype; begintempsal:=800;open mycursor;fetch mycursor into cursorrecord;if mycursor%notfound thendbms_output.put_line(to_char(cursorrecord.deptno);elsedbms_output.put_line(发现数据！);end if; end;,%rowcount,Set serveroutput on dec
43、laretempsal scott.emp.sal%type;cursor mycursor isselect * from scott.empwhere saltempsal;cursorrecord mycursor%rowtype; begintempsal:=800;open mycursor;fetch mycursor into cursorrecord;dbms_output.put_line(to_char(mycursor%rowcount); end;,创建过程,语法,set serveroutput on create or replace procedure tempu
44、ser.tempprocedure astempdate tempuser.testtable.currentdate%type; beginselect currentdate into tempdatefrom testtablewhere recordnumber=88;dbms_output.put_line(to_char(tempdate); end;,执行过程,set serveroutput on begintempuser.tempprocedure; end;,带参数的过程,Set serveroutput on create or replace procedure sc
45、ott.tempprocedure(tempdeptno in scott.dept.deptno%type,tempdname out scott.dept.dname%type,temploc in out scott.dept.loc%type)asloc1 scott.dept.loc%type;dname1 scott.dept.dname%type; beginselect loc into loc1from scott.deptwhere deptno=tempdeptno;select dname into dname1from scott.deptwhere deptno=t
46、empdeptno;temploc:=地址:|loc1;tempdname:=姓名|dname1; end;,使用带参数的存储过程,set serveroutput on declare myno scott.dept.deptno%type;mydname scott.dept.dname%type;myloc scott.dept.loc%type; beginmyno:=10;mydname:=;myloc:=;scott.tempprocedure(myno,mydname,myloc);dbms_output.put_line(myno);dbms_output.put_line(m
47、ydname);dbms_output.put_line(myloc); end;,异常处理,区别错误与异常 ORACLE中错误可以触发异常 set serveroutput on declare tempno integer:=100; begintempno=tempno+1; end;,定义异常,触发异常,Raise 异常名,处理异常,异常例子,set serveroutput on declaresalaryerror exception;tempsal scott.emp.sal%type; beginselect sal into tempsal from scott.emp where empno=7566;if tempsal2600 thenraise salaryerror;end if; exceptionwhen salaryerror thendbms_output.put_line(薪水超出范围); end;,
