This I will intruduce the PLSQL:
First, list the useful of plsql:
1: variable,constant, and data types
2: Control structures such as conditional statement and loops
3: Reusable program units that are written once and executed many times
Plsql structure:
declare (optional)
variables, cursors,user-defined,exception
begin (mandatory)
sql statement
plsql statement
exception (optional)
actions to perform
when errors occur
end; (mandatory)
From above,we can know the declare and exception are optional, the other is mandatory.
Notice , there is a ";" end by "end"
----------
Type:
Anonymous (there is no produce name)(can only use on time)
procedure (this have its name )
Function (this have its name ,and have it return item)
ok, let us start with case of "Anonymous"
Because this is anonyous, when it finished, end with '/', it will completed successfully.
if you want to see the output, you should set below setting:
SQL> declare
2 v_fname varchar2(20);
3 begin
4 select ename into v_fname from emp where empno=7844;
5 end;
6 /
PL/SQL 过程已成功完成。
SQL> set serveroutput on;
(this command function is to enable the dbms_output )
SQL> l (using charactor : l to list the latest command quickly)
1 declare
2 v_fname varchar2(20);
3 begin
4 select ename into v_fname from emp where empno=7844;
5* end;
SQL> /
PL/SQL 过程已成功完成。
SQL> declare
2 v_fname varchar2(20);
3 begin
4 select ename into v_fname from emp where empno=7844;
5 dbms_output.put_line('The result is ' || v_fname);
6 end;
7 /
The result is TURNER
PL/SQL 过程已成功完成。
Class 2:
Step1:
After completing this lesson, you should be able to do the following:
1: Recognize valid and invalid identifiers
2: List the uses of variables
3: Declare and initialize variables
4: List and describe various data types
5: Identify the benefits of using the %TYPE attrubute
6: Declare,use,and print bind variables
Step2:
variables can be used for:
1: Temporary storage of data
2: Manipulation of stored values
3: Reusability
example:
select
first_anme
department_id
into
v_fname,
v_deptno
from
...
A variable name:
1: Must start with a letter
2: Can include letters or numbers
3: Can include special characters (such as $,_,and #)
4: Must contain no more than 30 characters
5: Must not include reserved words
Declaring an initializing PL/SQL:
identifier [CONSTANT] datatype [ not null ]
[ := | DEFAULT expr ];
example:
DECLARE
V_hiredate DATE
V_deptno NUMBER NOT NULL :=10;
V_location varchar2(13) := 'Atlant';
c_comm CONSTANT NUMBER :=1400;
NOTES:
c_comm is keep the values of "1400".
Step3:
Let us start with two example:
SQL> declare
2 v_myname varchar2(20);
3 begin
4 dbms_output.put_line('My name is :'|| v_myname);
5 v_myname :='john';
6 dbms_output.put_line('My name is :'|| v_myname);
7 end;
8 /
My name is :
My name is :john
PL/SQL 过程已成功完成。
SQL> declare
2 v_myname varchar2(20) :='John';
3 begin
4 v_myname := 'Steven';
5 dbms_output.put_line('My name is :'|| v_myname);
6 end;
7 /
My name is :Steven
PL/SQL 过程已成功完成。
Step4:
Delimiters in String Literals:
let us follow with one workshop:
example:
SQL> l
1 declare
2 v_event varchar2(15);
3 begin
4 v_event := q'!Father's day!';
5 dbms_output.put_line('3rd Sunday in June is :'|| v_event );
6 v_event := q'[Mother's day]';
7 dbms_output.put_line('2nd Sunday in May is :'|| v_event )
8* end;
SQL> /
end;
*
第 8 行出现错误:
ORA-06550: 第 8 行, 第 1 列:
PLS-00103: 出现符号 "END"在需要下列之一时:
:= . ( % ;
符号 ";" 被替换为 "END" 后继续。
SQL> 7
7* dbms_output.put_line('2nd Sunday in May is :'|| v_event )
SQL> c/)/);;
7* dbms_output.put_line('2nd Sunday in May is :'|| v_event );
SQL> l
1 declare
2 v_event varchar2(15);
3 begin
4 v_event := q'!Father's day!';
5 dbms_output.put_line('3rd Sunday in June is :'|| v_event );
6 v_event := q'[Mother's day]';
7 dbms_output.put_line('2nd Sunday in May is :'|| v_event );
8* end;
SQL> /
3rd Sunday in June is :Father's day
2nd Sunday in May is :Mother's day
PL/SQL 过程已成功完成。
SQL>
From above case, we learn:
1: How to list one line of procedure.
2: How to clarify the one charactor using "c/".
3: How to voide the charactor spring of "'" of output. (using q'[]' or q'!!' ).