Tools: CMD sqlplus
Content: basic table operation, plsql programming (branch structure, loop, judgment, etc.)
Account: scott
preparation in advance
Tips for using cmd
Because the author uses sqlplus and enters in the cmd environment. It's not easy to edit the text in cmd environment, so I'll give you some tips.
As shown in the picture:
cmd shortcut key: alt+space+k tag
enter copy after selection
alt+space+p paste
!! Tags can be selected in any range. Compared with the fixed mode of selecting multiple lines in office, they are super easy to use and have wood!!!
C:\Users\Administrator>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Thursday 26 April 08:13:18 2018
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Please enter your user name: scott
Enter password:
ERROR:
ORA-12560: TNS: protocol adapter error
If the protocol adapter error is encountered, check whether the service is enabled. 
Please enter your user name: scott
Enter password:
Connect to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from user_role_privs;
USERNAME GRANTED_ROLE ADM DEF OS_
SCOTT CONNECT NO YES NO
SCOTT RESOURCE NO YES NO
SQL> select sysdate,systimestamp from dual;
SYSDATE
SYSTIMESTAMP
26-4 months -18
26-april-18 08.20.24.358000 am + 08:00
Text:
SQL> print sname;
SP2-0552: Bound variable is not declared "SNAME".
SQL> set serveroutput on
SQL> declare
2 sname varchar2(20) :='abcd';
3 begin
4 sname :=sname||'and tom';
5 dbms_output.put_line(sname);
6 end;
7 /
abcdand tom
PL/SQL The process completed successfully.
SQL> declare
2 ename varchar2(20) default 'abc';
3 begin
4 select sname into ename from student where sno ='2015
SQL>set serveroutput on
SQL>declare
2 pi constant number :=3.14;
3 r number default 3;
4 area nubmer;
5 begin
6 area :=pi*r*r;
7 dbms_output.put_line(area);
8 end;
9 /
****
PL/SQL Process completed successfully
SQL>var emp_name varchar2(30);
SQL>begin
2 select sname into : emp_name from student where sno='2018001';
3 end;
4 /
SQL> insert into student values('2018001','zhangsan','male','20');
//Already created 1 That's ok.
SQL> select * from student;
SNO SNAME SSEX SAGE
---------- -------------------- -------------------- ----------
2018001 zhangsan male 20
SQL> var emp_name varchar2(30);
SQL> begin
2 select sname into :emp_name from student where sno='2018001';
3 end;
4 /
PL/SQL The process completed successfully.
SQL> print emp_name;
EMP_NAME
--------------------------------
zhangsan
----------
//Modify scott account permissions
//Prompt ora when logging in as scott/tiger in plsql developer-28000 the account is locked.
//terms of settlement:
//After the Oracle10g is newly installed and tested with scott/tiger, the following error prompts will appear:
oracle10g the account is locked
oracle10g the password has expired
//Reason: by default, scott of Oracle10g cannot log in.
//Solve:
(1)conn sys/sys as sysdba; //Log in as DBA
(2)alter user scott account unlock;// Then unlock it.
(3)conn scott/tiger //Pop up a dialog box to change the password. Just change the password
//In the operation, enter cmd, enter sqlplus in DOS mode, and log in as system user name. The password is the password that you filled in when you first installed oracle, orcl. After logging in.
SQL> conn sys/sys as sysdba; (Semicolons are necessary, but I use system The sign in location should not write conn sys/sys as sysdba Should write conn system/orcl as sysdba;)
Connected.
SQL> alter user scott account unlock;
User altered.
SQL> commit;
Commit complete.
SQL> conn scott/tiger//Please enter the new password and confirm it, then OK
Password changed
Connected.
----------
SQL> update student set sname='Zhang San' where sno='2018001';
//Updated 1 That's ok.
SQL> select * from student;
SNO SNAME SSEX SAGE
---------- -------------------- -------------------- ----------
2018001 Zhang San male 20
SQL>
SQL> declare
2 myemp student%rowtype;
3 begin
4 select * into myemp from student where sno='2018001';
5 dbms_output.put_line(myemp.sname);
6 end;
7 /
zhangsan
----------
//Query the salary of james, if it is greater than900，Bonus
declare emp.sal % type;
//Define variables consistent with sal field types
select sal into newSal from emp
where ename='james';
if newSal>900 then
update emp
set comm=800
where ename='james';
end if;
commit;
end;
----------
//if else
SQL> declare
2 newSal emp.sal % type;
3 begin
4 select sal into newSal from emp where ename='ALLEN';
5 if newSal > 1000 then
6 update emp set comm = 800 where ename ='ALLEN';
7 else
8 update emp set comm = 400 where ename ='ALLEN';
9 end if;
10 end;
11 /
PL/SQL The process completed successfully.
SQL> select * from emp;
ENAME SAL COMM ID
-------------------- ---------- ---------- ----------
SMITH 800
ALLEN 1600 800
WARD 1250 500
JONES 2975
MARTIN 1250 1400
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500 0
ADAMS 1100
ENAME SAL COMM ID
-------------------- ---------- ---------- ----------
JAMES 950
FORD 3000
MILLER 1300
//Have chosen14That's ok.
----------
//Selection structure
SQL> set serveroutput on
SQL>
SQL> declare
2 v_grade char(1):=upper('&grade');
3 begin
4 case v_grade
5 when 'A' then
6 dbms_output.put_line('Excellent');
7 when 'B' then
8 dbms_output.put_line('Very Good');
9 when 'C' then
10 dbms_output.put_line('Good');
11 else
12 dbms_output.put_line('No such grade');
13 end case;
14 end;
15 /
//Enter a value for grade: a
//Original value 2: v_grade char(1):=upper('&grade');
//New value 2: v_grade char(1):=upper('a');
Excellent
PL/SQL The process completed successfully.
----------
//Selection structure
SQL> set serveroutput on
SQL> declare
2 v_grade char(1):=upper('&grade');
3 p_grade varchar(20);
4 begin
5 p_grade :=
6 case v_grade
7 when 'A' then
8 'Excellent'
9 when 'B' then
10 'Very Good'
11 when 'C' then
12 'Good'
13 else
14 'No such grade'
15 end;
16 dbms_output.put_line('Grade:'||v_grade||',the result is '||p_grade);
17 end;
18 /
//Enter a value for grade: a
//Original value 2: v_grade char(1):=upper('&grade');
//New value 2: v_grade char(1):=upper('a');
Grade:A,the result is Excellent
PL/SQL The process completed successfully.
----------
//loopCycle calculation1+2+3+...+100And
SQL> set serveroutput on
SQL>
SQL> declare
2 counter number(3):=0;
3 sumResult number:=0;
4 begin
5 loop
6 counter := counter+1;
7 sumResult :=sumResult+counter;
8 if counter>=100 then exit;
9 end if;
10 end loop;
11 dbms_output.put_line('result is:'||to_char(sumResult));
12 end;
13 /
result is:5050
PL/SQL The process completed successfully.
----------
forloop
SQL> set serveroutput on
SQL> declare
2 counter number(3):=0;
3 sumResult number:=0;
4 begin
5 while counter<100 loop
6 counter:=counter+1;
7 sumResult:=sumResult+counter;
8 end loop;
9 dbms_output.put_line('result is:'||sumResult);
10 end;
11 /
result is:5050
PL/SQL The process completed successfully.
----------
SQL> set serveroutput on
SQL> declare
2 sumsal emp.sal % type;
3 begin
4 select sum(sal) into sumsal from emp;
5 if sumsal>20000 then
6 goto first_label;
7 else
8 goto second_label;
9 end if;
10 <<first_label>>
11 dbms_output.put_line('ABOVE 20000:'||sumsal);
12 <<second_label>>
13 null;
14 end;
15 /
ABOVE 20000:29025
PL/SQL The process completed successfully.
----------
SQL> select * from emp;
ENAME SAL COMM ID
-------------------- ---------- ---------- ----------
SMITH 800
ALLEN 1600 800
WARD 1250 500
JONES 2975
MARTIN 1250 1400
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500 0
ADAMS 1100
ENAME SAL COMM ID
-------------------- ---------- ---------- ----------
JAMES 950
FORD 3000
MILLER 1300
//Have chosen14That's ok.
SQL> select sum(sal) from emp;
SUM(SAL)
----------
29025