1.SQLPLUS : a tool that execute sql ,sqlplus ,sqlplus command.
1.sqlplus command-line cmd-sqlplus /nolog
2.sqlplus GUI sqlplusw /nolog
3.sqlplus web http://127.0.0.1:5560/isqlplus/
isqlplusctl start
how to start sqlplus dba login
[oracle@Toshiba ~]$ JAVA_HOME=$ORACLE_HOME/jdk
[oracle@Toshiba ~]$ export JAVA_HOME
[oracle@Toshiba ~]$ cd $ORACLE_HOME/oc4j/j2ee/isqlplus/application-deployments/isqlplus
[oracle@Toshiba isqlplus]$ $ORACLE_HOME/jdk/bin/java -Djava.security.properties=$ORACLE_HOME/sqlplus/admin/iplus/provider -jar $ORACLE_HOME/oc4j/j2ee/home/jazn.jar -shell
RealmLoginModule username: admin
RealmLoginModule password:******** welcome
JAZN:> adduser "iSQL*Plus DBA" oracle oracle
JAZN:> grantrole webDba "iSQL*Plus DBA" oracle
JAZN:> listusers
iSQL*Plus DBA/admin
iSQL*Plus DBA/oracle
JAZN:> exit
2.sqlplus -h -v -s
3.sqlplus /log ;login sqlplus
4.sqlplus / as sysdba (oracle 10g) sqlplus "/ as sysdba" (oracle 9i)
sqlplus /
sqlplus hr
5.run / start @
start mysql.sql p1 p2
@mysql p1 p2
6.accep
7.list :display the contents of SQL buffer ,the last line is the current line.
l2: the current line is number 2line.
c: changeor replace the current line character. c /t/emp
a: appent the text to the end of the current line. a where id='1'
i: insert a line after the current line.
0 /*hhhhhhhhhhhhhhhhhh*/ inser a line before the first line.
del: del n m
del * last
del *
del * n
del 2
list *
list n m
list m
remark: add comments rem this is comments
/* this is comment */
--thjisssdsds
8.save/get filename
9.SQL buffer:store recent sql and pl/sql command.
10.?=$ORACLE_HOME
11.$ORACLE_HOME/sqlplus/admin/help/hlpbld.sql helpus.sql create sqlplus help command
$ORACLE_HOME/sqlplus/admin/help/helpdrop.sql
12.help save: get help infor about sqlplus command
help index : display all sqlplus command
13.quit,exit,ctrl+d,ctrl+z
14.desc :display defination about table,procedure,function
15.; / run sql command
16. . :terminate pl/sql / : run pl/sql
17.show display the content about sqlplus command
18.set serveroutput on/off
19.col id format $99.999 -
heading "your ID"
20.ctrl+c: stop a command while it is running
21.! or $or host:use os command in the sqlplus
22. set linesize
set pagesize
23.set pause on/off :pause display
set pau ''
25.set autocommit on :save change to database automatic
26.@ or @@ or start : run script
27.define _editor=vi in sqlplus ,edit script
ed lab1
28.set echo on/off
set verify on/off suppress the display of each line before and after substitution:
29.run script as you start sqlplus
1.sqlplus hr/hr @con.sql
2.vi con.sql
hr/hr
select * from dept;
sqlplus @con.sql
30.spool query.sql
select * from dept
select 'drop table ' || object_name || ';' from user_objects where object_type='TABLE'
spool off
31. substitution variables
define d_loc=CHICAGO
define d_dname=&dname
define d_deptno=&1
select * from dept where loc=&d_loc and dname=&d_name and deptno=&d_deptno;
select * from emp where deptno=&d_deptno
32. Using Bind Variables
var val number
begin
:val :=1;
end;
print val
var emp_infor refcursor
begin
open :emp_infor for select * from emp;
end;
create or replace package myemppkg as
type empinforcur is ref cursor;
procedure pmyempkg(res in out empinforcur);
end;
create or replace package body myemppkg as
procedure pmyempkg(res in out empinforcur) as
begin
open res for select * from emp;
end;
end;
var emps refcursor;
exec myemppkg.pmyempkg(emps);
create or replace function femp
return restype is reset restype;
begin
open reset for select * from emp;
return(reset);
end;
33.column
column empno heading '--empno--'
column empno format a30
column empno format $999,999,999