SQL is not very flexible and it cannot be made to react differently to differing sutuations easily. In SQL queries we normally tell database what we want but not tell it how to do it.
SQL : give commands, commands complete with ;
PL/SQL : follow the procedure, create programs (use SQL), using If conditions Else Elseif and loops (while loop, for loop, do while, control or check error, exceptions)
eg :
Declare
(to create all variables)
Begin
programs (SQL commands not DDL)
Exception
(execute programs on any error)
End;
note : if there is no need to declare variables, then there's no need to write Declare statement.
the minimal :
Begin
Select * from emp;
End;
write using SQL>
method 1 :
SQL>Begin
SQL> Select * from emp;
SQL>End;
SQL>
method 2 :
create a file (like notepad)
give the name as ABC.sal or ABC.pls(Procedural Language Script)
then you can run this file in SQL
PL/SQL allows you to work with the database with the ease of SQL, while giving you the power and flexibility or procedual constructs such as : Variables, Flow Control (If Else Condition), Error Handling, you can create a block of code containing several SQL statements and send them as a single request to the database. This improves performance.
in SQL :
DDL : Data Denifition Language
eg : Create table/view/sequence, Alter table/view/sequence, Drop table/view/sequence
DML : Data Manipulation Language
eg : Insert, Update, Delete
DCL : Data Control Language
eg : create user, grant user, revoke,
PL/SQL features
1.variables & constants : objects within PL/SQL that are used to store and manipulate values. Several data types are available, the more common ones beings, VARCHAR2, DATE and BOOLEAN.
2.SQL - All DML(Data manipulation language)type of SQL statements can be used directly whin PL/SQL
3.Flow Control : PL/SQL supports flow control statements such as IF, FOR, WHILE. These allow for conditional actions, branching and iterative(loop)control.
4.Built_in Functions : most functions that are available in SQL can ve used in a PL/SQL statemtns
5.Cursor Management : to process data returned from multiple-row queries. we can modify the data according to
6.Block Structure : PL/SQL programs are made up of blocks of code. blocks aare used to separate code.
7.Exception Handling : PL/SQL supports an elegant method for handling exceptions (errors) within code. the programmer can define his own exceptions. eg : trying to change the value od PK.
8.Composite Type : PL/SQL allows you to create composite data types, which typically relate to a row on a database table. eg : a single variable of row type can store the data from the while table record(many columns)
9. Sored Code : PL/SQL programs can ve stored within the database in the form of packages, procedures, functions and triggers. so we can run our programs any time we have stored in database in different forms.
eg :
%type : datatype from table columns
Stud_ID Student_table.student_ID%type;
Stud_ID Number; static Datatype
Dynamic datatype (can be changed)
static datatype variable
dynamic datatype variable
eg : variable_name table_name.column_name%type
eg : Stud_ID Student_table.Student_ID%type
variable to whole record/row %rowtype
eg : variable_name table_name%rowtype
eg : Student_rec Student_table%rowtype
display :
Student_rec.Name
Student_rec.Age
Student_rec.Student_id
variable name datatype
Student_name Varchar(20);
dynamic variable
Student_name Student_table.Name%type
(If someone changes the datatype of name in Student table, you have no need to change your PL/SQL program.)
record or row type variable
Student_row Student_table%rowtype
Cursor --> store many columns, many records
array of %rowtype variable
Cursor --> select * from emp; (store the data inside memory)
open cursor and get the data from cursor, update the data inside cursor, copu the cursor back to table, close the cursot.
Declare
Begin
Exception
End;
PL/SQL programs
1.functions (return something)
2.procedures (similar to function (can or cannot return ))
3.packages (biggest program)
4.cursors
5.triggers
PL/SQL Fundamentals
basic PL/SQL syntax
1. free format language : no given format, we can write the programs in any way.
2. PL/SQL statements can be written in many lines.
3. keywords cannot split in many lines.
4. identifiers (variables) must start with an alpha-character, can be up to 30 characters in length and cannot be a reserved word.
5. character and date literal (data) are enclosed in single quotes. ('')
6. each statement must end with a semicolon (;)
eg : Stud_name := 'ABC'; (to store data)
Stud_name = 'ABC'; (in IF condition)
all PL/SQL programs are made up of blocks. a PL/SQL program must contain at least one block. a block is made up of PL/SQL statements enclosed within the keywords, BEGIN and END, eg :
BEGIN
INSERT INTO table (col) VALUES('XX');
END;
for a variable declaration you can also include a DECLARE section, eg :
DECLARE
v_number NUMBER;
BEGIN
v_number := 10;
END;
for exception handling use the EXCEPTION keyword, eg :
DECLARE
v_number NUMBER;
BEGIN
v_number := 10;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error');
END;
eg :
DECLARE
Stud_ID Number;
BEGIN
Stud_ID := 100;
DBMS_OUTPUT.put_line('Student ID is : ' | | Stud_ID);
END;
(when you execute your program, it will show you the value of Stud_ID. you have to set serveroutput on first.)
SQL> Set serveroutput on / off
SQL> Show serveroutput (display the serveroutput state)
the PL/SQL block - multiple block
a PL/SQL program can consist of several in-line blocks, eg :
DECLARE
v_number NUMBER;
BEGIN
v_number := 10;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error 1');
END;
BEGIN
v_number := 20;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error 2');
END;
BEGIN
v_number := 30;
EXCEPTION
WHEN OTHERS THEN
DBMS.OUTPUT.put_line('Error 3');
END;
note : the blocks with only one declare must be in one file.
PL/SQL blocks can contain nested (inside) blocks. eg :
DECLARE
BEGIN
...outer block statements
DECLATE
... inner block declarations
BEGIN
...inner block statements
END;
... outer block statements
EXCEPTION
... outer block statements
END;
note : you can write nested blocks under BEGIN and EXCEPTION but not DECLARE.
there are several types of blocks :
Anonymous block : have no name and are generally stored in a host file or entered directly into SQL *Plus and executed just once.
Named blocks : very much the same as an anonymous block except the block is given a label.
Subprograms : Packages, Procedures and Functions are blocks of codes stored within the database. these blocks are executed via a specific call via the block name.
Triggers : these are also named blocks that are stored within the database. triggers are executed implicitly whenever the triggering event occurs. (Insert, update or delete)
Goto labele
eg :
Named blocks : name is enclosed in << and >> :
<<ABC>>
DECLARE
Stud_ID Number;
BEGIN
Stud_ID := 100;
if Stud_ID >100
goto <<XYZ>>
else
DBMS_OUTPUT.put_line(' ');
END;
<<my_block>>
DECLARE
v_number NUMBER;
BEGIN
v_number := 100;
END;
Subprograms : function to square a number :
CREATE OR REPLACE FUNCTION square(p_number IN NUMBER)
IS
BEGIN
RETURN p_number *2;
END;
Triggers : these are also named blocks that fire (execute) in response to a database event. (Insert, update or delete)
CREATE OR REPLACE TRIGGER audit
BEFORE DELETE ON items
FOR EACH ROW
BEGIN
INSERT INTO audit_table(item, description)
VALUES(:old.item,:old.description);
END;
Triggers : named PL/SQL blocks
Execute or run at event : insert, update, delete.
before insert, update, delete
after insert, update, delete
before delete on emp;
delete from emp;
before update on emp;
update from emp;
after insert on emp;
scope and visibility
install virtual box
instal second
install windows xp --> start program database 11g
conn scott/tiger
select * from tab;
disc
exit
0806036320 1
Simple Variable
Num_1 number(10);
Constant Variable
Declare
Num_1 Constant number(10) := 100;
Begin
From the database table
Num_1 emp.Empno%
Basic PL/SQL Syntax(rules)
1. free format language
2.PL/SQL statements can be written in many lines
3.keywords cannot split in many lines.
4.identifiers (variables) must start with an alpha-character, can be up to 30 characters in length and cannot be a reserved word.
5.character and date literal (data) are enclosed in single quotes. ('')
6. each statement must end with a semicolon (;)
Scope and visibility
the block in which an object is declared, including any nested blocks is the scope on an object.
if the variable in the inner block was also called x as in the outer block, then whilst in the inner block, x would refer to the x declared in the current block. x in the outer block still exists but has no visibility.
comments
single-line comments : start with two dashes : --
multiple-line comments : begin with : /* and end with : */
note : multiple comments cannot be nested.
eg :
show user
show serveroutput
set serveroutput on
Begin
DBMS_OUTPUT.put_line('Hello world');
END
clear buffer or cl buff
clear screen
using a variable
Declare
msg varchar2(50);
BEGIN
msg := ' Hello World';
DBMS_OUTPUT.put_line('msg');
END;
. : 退出编写程序
／ : run your program
ed : to open the editor
eg : to display the employee name from emp table
emp_name varchar2(25);
select ename into emp_name from emp where empno=7369; DBMS_OUTPUT
eg : to display 'Error' when error happens.
Declare
test number(10);
Begin
test:='ABC';
DBMS.OUTPUT.put_line('test');
Exception
when others then
DBMS_OUTPUT.put_line('Error');
End;
variables
a variable is simply a location in memory where you can store data of different types.
variables can be read or set during execution of a PL/SQL block, values from the database can be stored in variables as can literals and values of other variables.
all variables must be declared before they can be used. the declaration of a variable specifies the type and size of the data that the variable will hold.
variable declaration
all declaration must appear in the DECLARE section of your block.
syntax :
varname TYPE [CONSTANT] [NOT NULL] [:=value];
eg :
DECLARE
v_anumber1 NUMBER(10);
v_anumber2 NUMBER := 100;
v_astring VARCHAR2(1) NOT NULL :='Y';
v_astring2 VARCHAR2(1) NOT NULL DEFAULT 'Y';
variable initialization
if you declare a variable without specifying a default value, PL/SQL automatically initializes all variables without a default value to NULL.
variable type anchoring
if you know the type of a variable is directly related to a column on a table within the database then you can anchor the type of your variable to the type on the database,
eg : %type(single column), %rowtype(whole row or record)
eg : variable_name table_name.column_name%TYPE;
you can also anchor a variable type to another variable in the same program.
eg :
Declare
test emp.empno%type;
test1 test%type;
emp_rec emp%rowtype;
emp_rec1 emp_rec%type;
emp_name emp.ename%type;
Begin
Select ename into emp_name from emp where emp_no=1234;
Select * into emp_rec from emp where emp_no= 1111;
DBMS_OUTPUT.put_line(emp_rec.empno, emp_rec.ename);
DBMS_OUTPUT.put_line(emp_rec1.ename);
End;
eg :
v_item_id items.item_id%TYPE;
v_new_item v_item_id%TYPE;
variable constants
a variable can be declared as a constant (fixed) value by using the CONSTANT keyword within the varaible declaration. any attempt to change a constant value will cause an error to occur.
eg :
DECLARE
c_bad_status CONSTANT NUMBER :=5;
BEGIN
IF v_item_status = c_bad_status THEN
...
eg :
Declare
Const_variable constant number(10) := 25;
Begin
Constant_variable :=30; --ERROR
DBMS_OUTPUT.put_line(Constant_variable);
End;
assignments & expressions
assignments can appear anywhere within a block of code, not just the DECLARE section. an assignmetn is the storing of a value in a variable. assignment has the following basic syntax.
eg :
v_counter := 0;
v_name := 'This is the name';
v_date := TO_DATE('10-JAN-99','DD-MON-RR');
v_processed :- TRUE;
v_total := 5 * 10;
v_count := v_count + 50;
v_first_name := first_name(name_cur.full_name);
v_today := SYSDATE;
v_coolean := 5 > 10;
v_x_is_bigger_than_y : X < Y;
x_continue : A = B AND D > E;
v_valid_type := v_item_type LIKE 'QB%';
v_not_set := IS NULL v_number;
v_in_range := v_id BETWEEN 10 AND 50;
v_in_list : v_id IN(10,10,50,70,90);
note : DECODE and any group functions CANNOT be used.
GOTO Named Block <<name_of_block>>
eg :
Declare
test number(10);
Begin
test := 100;
if test > 75 then
goto ABC;
else
DBMS_OUTPUT.put_line(' less than 75');
end if;
<<ABC>>
Begin
DBMS_OUTPUT.put_line(' greater than 75');
End;
End;
PL/SQL from SQL*Plus
1. enter PL/SQL directly into the SQL buffer and run it.
2. create a PL/SQL script file and then execute that file from SQL*Plus.
eg : to run your program
/ : to run the last program you have just created.(without clear buffer)
ABC.pls (PL/SQL Script)
ABC.sql
2eg :
SAVE file_name.pls
GET file_name.pls
3 different methods to run script file.
/ : run script file
START file_name.pls : run script file
@file_name.pls : run script file
note : if there is any error in your program, you can view errors by querying the USER_ERRORS database view.
viewing the result
1.insert information into a table and query back the table.
2.use a SQL*Plus bind variable.
3.use the oracle supplied package DBMS_OUTPUT to send output to the screen.
1eg :
CREATE TABLE my_debug
(text1 VARCHAR2(500);
DECLARE
l_x NUMBER := 0L
BEGIN
INSERT INTO my_debug
VALUES ('Before='||TO_CHAR(l_x));
l_x := l_x +10;
INSERT INTO my_debug
VALUES ('After='||TO_CHAR(l_x));
END;
.
/
SELECT text1 FROM my_debug;
2eg :
VAR[IABLE] name [NUMBER]CHAR[(N)]|VARCHAR2[(N)]]
VAR result NUMBER
print result;
3eg :
SET serverout[put] ON|OFF [SIZE n]
SER serverout ON SIZE 1000000
note : if you do not give value of size, sometimes it will give an error message.
%type
%rowtype
1.create a table
eg :
Create table stud (stud_id number(10), stud_name varchar2(50), stud_age number(2))
Insert into stud VALUES (1001,'ABC', 20); (1002, 'XYZ', 21)
Declare
student stud%rowtype;
student1 student%type;
Begin
Select * into student from stud where stud_id = 1001;
DBMS_OUTPUT.put_line(student1.stud_id);
DBMS_OUTPUT.put_line(student1.stud_name);
DBMS_OUTPUT.put_line(student1.stud_age);
End;
how to use boolean values
Declare
true_false boolean;
id number(10) := 10;
Begin
--true_false := TRUE;
true_false := 5 > 10;
true_false := 5=5 AND 6,10;
true_false := id between 5 AND 15;
true_false := id in (1, 5, 10, 100);
if (true_false) = true then
DBMS_OUTPUT.put_line('TRUE');
else
DBMS_OUTPUT.put_line('FALSE');
End;