Questions and Answers
What is the purpose of variables in PL/SQL?
What is the correct structure of a PL/SQL program block?
Which of the following is NOT a variable type in PL/SQL?
Which operator in PL/SQL is used for string concatenation?
Signup and view all the answers
How do you enable output to be displayed on the screen in PL/SQL?
Signup and view all the answers
Study Notes
Procedural Language SQL Overview
- PL/SQL combines procedural language features with SQL, allowing use of IF statements, loops, and variables alongside declarative SQL.
Variable Types
- SQL supports various column types: BOOLEAN, DATE, NUMBER, CHAR, and VARCHAR2.
- Variables can be declared using the syntax:
variable-name data type
, e.g.,PRICE NUMBER(6,2)
. - Columns can be referenced using
tablename.columnname%TYPE
, e.g.,PRODUCT.PRICE%TYPE
. - Rows as a single type can be declared with
tablename%ROWTYPE
, e.g.,PRODUCT%ROWTYPE
.
PL/SQL Program Blocks
- PL/SQL programs consist of structured blocks:
- DECLARE: for variable declarations.
- BEGIN: contains procedural code.
- EXCEPTION: for error handling.
- END: indicates the end of the PL/SQL block.
Operators in PL/SQL
- Supports various operators:
- Exponentiation:
**
- Not Equals:
!=
- Multiplication:
*
- Division:
/
- Addition:
+
- Subtraction:
-
- Negation:
-
- Comparison operators:
<
,>
,<=
,>=
- Logical OR:
OR
- Assignment:
:=
- String Concatenation:
||
- Exponentiation:
Outputting Text
- To display output in PL/SQL, use
SET SERVEROUTPUT ON;
- Example code to print "Hello World":
BEGIN DBMS_OUTPUT.PUT_LINE('Hello World'); END;
Type Conversion Functions
-
TO_DATE
: Converts string to date. -
TO_NUMBER
: Converts character string to number. -
TO_CHAR
: Converts numbers or dates to character strings.
Character String Functions
-
RTRIM(STR)
: Removes trailing spaces. -
LENGTH(STR)
: Returns length of the string. -
UPPER(STR)
: Converts string to uppercase. -
LOWER(STR)
: Converts string to lowercase. -
INSTR(STR1, STR2)
: Searches forSTR2
withinSTR1
. -
SUBSTR(STR, START, END)
: Returns a substring starting atSTART
.
IF Statements
- Structures for IF statements:
- IF - THEN
- IF - ELSE
- IF ELSIF
Example of IF - THEN Structure
- Syntax:
IF condition THEN program_statements END IF;
- Example of comparing two integers:
DECLARE A NUMBER(6); B NUMBER(6); BEGIN A := 23; B := 89; IF A < B THEN DBMS_OUTPUT.PUT_LINE(A || ' is less than ' || B); END IF; END;
Example of IF - ELSE Structure
- Syntax:
IF condition THEN code_if_true ELSE code_if_false END IF;
- Example comparing two values:
DECLARE A NUMBER(6); B NUMBER(6); BEGIN A := 23; B := 8; IF A < B THEN DBMS_OUTPUT.PUT_LINE(A || ' is less than ' || B); ELSE DBMS_OUTPUT.PUT_LINE(A || ' is greater than ' || B); END IF; END;
IF Statement Nesting
- Allows multiple conditions and comparisons within nested structures:
- Example compares three integers to find the maximum:
DECLARE A NUMBER; B NUMBER; C NUMBER; ABCMAX NUMBER; BEGIN A := 23; B := 89; C := 7; IF A >= B THEN IF A >= C THEN ABCMAX := A; ELSE ABCMAX := C; END IF; ELSE IF B >= C THEN ABCMAX := B; ELSE ABCMAX := C; END IF; END IF; DBMS_OUTPUT.PUT_LINE('Max of: ' || A || ', ' || B || ', and ' || C || ' is ' || ABCMAX); END;
IF ELSIF Structure
- Used for multiple conditions:
- Example to assign letter grades based on numeric grades:
DECLARE NGRADE NUMBER; LGRADE CHAR(2); BEGIN NGRADE := 82.5; IF NGRADE >= 80 THEN LGRADE := 'A'; ELSIF NGRADE >= 70 THEN LGRADE := 'B'; ELSIF NGRADE >= 60 THEN LGRADE := 'C'; ELSIF NGRADE >= 50 THEN LGRADE := 'D'; ELSE LGRADE := 'F'; END IF; DBMS_OUTPUT.PUT_LINE('Grade ' || NGRADE || ' is ' || LGRADE); END;
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.
Description
Explore the fundamentals of Procedural Language SQL in this quiz. Learn about IF statements, loops, variables, and the integration of procedural constructs with declarative SQL. Perfect for students looking to deepen their understanding of database management.