MIS 325 Lecture Notes - Lecture 14: Sql*Plus, Oracle Database, Result Set
2 May 2018
School
Department
Course
Professor
Chapter 13: How to Write PL/SQL Code
● Script: A series of SQL statements that you can store in a file
● PL/SQL (Procedural Language/SQL):
○ Oracle's extension to standard SQL that allows you to write procedural SQL code
that works with an Oracle database
○ Allows writing procedural code such as IF statements and loops
● Anonymous PL/SQL Block:
○ Unnamed block of PL/SQL code coded within a script
○ Need semicolons at the end of each statement
○ Syntax
■ SET SERVEROUTPUT ON;
■ DECLARE statement: Used to declare variables
● Declaring a variable: variable DATA_TYPE;
● Declaring a variable with the same data type as a column: variable
table.column%TYPE;
● Setting a variable to a literal value or the result of an expression:
variable := literal_value_or_expression
■ BEGIN: Main body of script
● SELECT column
● INTO variable
● Body statements
■ EXCEPTION: Used to handle exceptions
● WHEN most_specific_exception THEN
○ Statements;
● WHEN less_specific_exception THEN
○ Statements;
■ END;
■ /
● Controlling the flow of execution
○ Controlling the flow of execution based on a condition
■ IF...ELSIF...ELSE
■ CASE...WHEN...ELSE
○ Repeating statements when a condition is true
■ FOR...IN...LOOP
■ WHILE...LOOP
■ LOOP...EXIT WHEN
○ Defining a result set that can be processed by a loop: CURSOR...IS
○ Executing DDL statements within a script and executing dynamic SQL
statements: EXECUTE IMMEDIATE
● Working with scripts
○ CONNECT: Connects to the database as the specified user
○ SET SERVEROUTPUT ON: Enables printing to SQL Developer’s Script Output
window or the SQL*Plus command line
Document Summary
Script: a series of sql statements that you can store in a file. Oracle"s extension to standard sql that allows you to write procedural sql code that works with an oracle database. Allows writing procedural code such as if statements and loops. Unnamed block of pl/sql code coded within a script. Need semicolons at the end of each statement. Declaring a variable with the same data type as a column: variable table. column%type; Setting a variable to a literal value or the result of an expression: variable := literal_value_or_expression. Controlling the flow of execution based on a condition. Repeating statements when a condition is true. Defining a result set that can be processed by a loop: cursoris. Executing ddl statements within a script and executing dynamic sql statements: execute immediate. Connect: connects to the database as the specified user. Set serveroutput on: enables printing to sql developer"s script output window or the sql*plus command line.
