Learning: simple programming of plsql for Oracle Database

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

Keywords: SQL sqlplus Oracle Programming

Added by ianitm on Mon, 30 Mar 2020 19:37:27 +0300