DATABASE MANAGEMENT SYSTEM ORACLE SQL AND PL/SQL

1
DATABASE MANAGEMENT SYSTEM ORACLE SQL AND PL/SQL

Book Page Number 201 - 220

• CHAPTER 7
• Oracle PL/SQL Basics

2
CHAPTER 7Oracle PL/SQL Basics 1 of 22
Page 201 - 216

• Why PL/SQL?
• Better Performance
• Higher Productivity
• Full Portability
• Tight Integration with SQL
• Security

3
CHAPTER 7Oracle PL/SQL Basics 2 of 22
Page 201 - 216

• Character Set

Type Characters
Upper, lowercase A-Z ,a-z
Digits 0-9
Mathematical and punctuation symbols ! _at_ ( )_ - lt gt ? /
White space Space, tab, carriage return
4
CHAPTER 7Oracle PL/SQL Basics 3 of 22
Page 201 - 216

• PL/SQL Identifiers
• Identifiers are used to name PL/SQL objects such
  as constants, variables, exceptions, procedures,
  cursors, and reserved words
• Identifiers can be up to 30 characters in length,
  must start with a letter, can include dollar
  sign, an underscore, and/or a pound sign
• Identifiers are case sensitive and cannot include
  space, tab, or carriage return. An identifier
  can be enclosed within double quotes

5
CHAPTER 7Oracle PL/SQL Basics 4 of 22
Page 201 - 216

• PL/SQL Literals
• Literals, are specific values and are not
  represented by identifiers
• Can be character, number, or boolean value
• To embed single quotes within a string literal,
  two single
• Quotes next to each other may be placed

6
CHAPTER 7Oracle PL/SQL Basics 5 of 22
Page 201 - 216

• PL/SQL Delimiters
• Delimiters are symbols with special meaning to
  PL/SQL
• Used to separate identifiers from each other

Delimiter Characteristics
-- Single-line comment indicator
/ / multilane comment delimiter
Concatenation operator
.. Range operator
Statement terminator
7
CHAPTER 7Oracle PL/SQL Basics 6 of 22
Page 201 - 216

• PL/SQL Variable

Type Type Purpose
Scalar Numeric NUMBER(p,s) The maximum precision is 38 digits.
Scalar Character VARCHAR2, CHAR, LONG CHAR and VARCHAR2 are up to 32767 bytes
Scalar Date Range is between 01-Jan- 4712BC and 31-Dec-9999AD.
Scalar Boolean Can hold TRUE, FLASE or NULL only
Scalar RAW Similar to CHAR variables LONG RAW is similar to LONG LONG RAW can be up to 2 gigabytes.
8
CHAPTER 7Oracle PL/SQL Basics 7 of 22
Page 201 - 216

• PL/SQL Variable

Type Type Purpose
LOB BFILE File locaters pointing to read only large objects in operating system files.
LOB BLOB BLOB locaters that point to large binary objects inside the database.
LOB CLOB CLOB locaters point to large character objects inside the database.
LOB NCLOB NCLOB locaters point large national character set objects inside the database.
9
CHAPTER 7Oracle PL/SQL Basics 8 of 22
Page 201 - 216

• NULL
• Represents unknown values as NULL values
• NULL is never equal to anything
• NVL, IS NULL or IS NOT NULL
• CONSTANT
• Requires an initial value
• Value Cannot be changed

10
CHAPTER 7Oracle PL/SQL Basics 9 of 22
Page 201 - 216

• Default Value
• A variable is assigned a default value of NULL
  while declaration
• Can be initialized by assignment operator ( )

11
CHAPTER 7Oracle PL/SQL Basics 10 of 22
Page 201 - 216

• PL/SQL Structure
• Declare
• Variables, cursors, constants
• Begin
• Pl/SQL statements
• Exception
• Statements for error handling
• End

12
CHAPTER 7Oracle PL/SQL Basics 11 of 22
Page 201 - 216

• IF Statement
• Allows actions based upon conditions
• IF statement can also be nested
• Three forms of IF statements
• IF THEN
• IF THEN ELSE
• IF THEN ELSIF ELSE .

13
CHAPTER 7Oracle PL/SQL Basics 12 of 22
Page 201 - 216

• Loops in PL/SQL
• Simple Loop
• Loop
• PL/SQL statements
• Exit Condition
• End Loop
• For Loop
• For variable IN Start..End
• PL/SQL Statements
• End Loop
• While Loop
• While ltConditiongt Loop
• PL/SQL Statements
• End Loop

14
CHAPTER 7Oracle PL/SQL Basics 13 of 22
Page 201 - 216

• Scope of Variable in Nested Block
• PL/SQL statements can be nested
• SQL Cursor
• A cursor is a private SQL work area where all
  commands defined in the cursor are executed
• There are two types of cursors
• Implicit cursor
• Explicit cursor

15
CHAPTER 7Oracle PL/SQL Basics 14 of 22
Page 201 - 216

• Implicit Cursor
• It is automatically created and handled by Oracle
  Server
• Support all type of DMLs Insert/Update/Delete
• Supports SELECT statement that returns only
• Explicit Cursor
• Needs to be declared explicitly by the programmer
• It is handled using cursor-related commands

16
CHAPTER 7Oracle PL/SQL Basics 15 of 22
Page 201 - 216

• INTO Clause
• Into clause is mandatory in a PL/SQL program
• It is placed between the SELECT and FROM clauses
• Act as a container with the associated variable
• Query must return only one row
• TYPE Attribute
• Used to declare variables that refer to the
  column
• ROWTYPE Attribute
• Represents a row in a table

17
CHAPTER 7Oracle PL/SQL Basics 16 of 22
Page 201 - 216

• Example EX7_1
• This example demonstrates a simple PL/SQL
  program.
• In Windows environment use Notepad to create
  EX7_1.SQL PL/SQL program.
• Save the file EX7_1.SQL in C/TTP Folder
• SET SERVEROUTPUT ON
• BEGIN
• DBMS_OUTPUT.PUT_LINE('My first program using
  PL/SQL')
• END
• /
• SQLgt _at_ C/TTP/EX7_1
• My first program using PL/SQL

18
CHAPTER 7Oracle PL/SQL Basics 17 of 22
Page 201 - 216

• Example EX7_2
• This example demonstrates use of variable in
  PL/SQL program.
• SET SERVEROUTPUT ON
• SET VERIFY OFF
• DECLARE
• INPUT_NO NUMBER(3)
• INPUT_CHAR VARCHAR2(20)
• INPUT_DATE DATE
• BEGIN
• INPUT_CHAR 'YOUR_NAME'
• INPUT_NO YOUR_AGE
• INPUT_DATE SYSDATE
• DBMS_OUTPUT.PUT_LINE(INPUT_CHAR ' IS '
  INPUT_NO ' YEARS OLD ' '
• ON ' INPUT_DATE)
• END
• /
• SQLgt_at_ C/TTP/EX7_2

19
CHAPTER 7Oracle PL/SQL Basics 18 of 22
Page 201 - 216

• Example EX7_4
• This example displays summing of number from 1
  to I_NUM.
• DECLARE
• I_NUM NUMBER S_NUM NUMBER SUM_NUM
  NUMBER
• BEGIN
• I_NUM I_NUM SUM_NUM 0 S_NUM
  I_NUM
• IF I_NUM gt0 AND I_NUM lt 1001 THEN
• LOOP
• SUM_NUM SUM_NUMI_NUM
• I_NUM I_NUM-1
• EXIT WHEN I_NUM0
• END LOOP
• DBMS_OUTPUT.PUT_LINE('SUM OF NUMBER FROM 1 TO '
  S_NUM ' IS ' SUM_NUM)
• ELSE
• DBMS_OUTPUT.PUT_LINE('Input Number Range is
  from 1 to 1000')
• END IF
• END
• /

20
CHAPTER 7Oracle PL/SQL Basics 19 of 22
Page 201 - 216

• Example EX7_7
• This example is related to NESTED block.
• SET SERVEROUTPUT ON
• DECLARE
• VA NUMBER(4) 10 VB NUMBER(4) 11
  VMSG VARCHAR2(20) ' MAIN '
• BEGIN
• DECLARE
• VA NUMBER(4) 1000 VB NUMBER(4) 1001
• BEGIN
• DBMS_OUTPUT.PUT_LINE('VA IN NESTED BLOCK
  'VA) DBMS_OUTPUT.PUT_LINE('VMSG IN NESTED
  BLOCK 'VMSG) DBMS_OUTPUT.PUT_LINE('VB IN
  NESTED BLOCK 'VB)
• END
• DBMS_OUTPUT.PUT_LINE('VA IN MAIN BLOCK 'VA)
  DBMS_OUTPUT.PUT_LINE('VMSG IN MAIN BLOCK
  'VMSG) DBMS_OUTPUT.PUT_LINE('VB IN MAIN
  BLOCK 'VB)
• END /
• SQLgt_at_ C/TTP/EX7_7
• VA IN NESTED BLOCK 1000
• VMSG IN NESTED BLOCK MAIN
• VB IN NESTED BLOCK 1001

21
CHAPTER 7Oracle PL/SQL Basics 20 of 22
Page 201 - 216

• Example EX7_9
• This example demonstrates use of INTO clause in
  PL/SQL program.
• SET SERVEROUTPUT ON
• DECLARE
• VPID VARCHAR2(6) 'PID'
• VCNT NUMBER
• BEGIN
• SELECT COUNT() INTO VCNT FROM SALE_DETAIL
  WHERE PIDVPID
• DBMS_OUTPUT.PUT_LINE(VPID '- SOLD '
  VCNT ' TIMES')
• END
• /
• SQLgt_at_ C/TTP/EX7_9
• Enter value for pid P3
• P3- SOLD 4 TIMES

22
CHAPTER 7Oracle PL/SQL Basics 21 of 22
Page 201 - 216

• Example EX7_10
• This example demonstrates use of TYPE attribute
  in PL/SQL program.
• SET SERVEROUTPUT ON
• DECLARE
• VCID CUST.CIDTYPE 'CID'
• VCNAME CUST.CNAMETYPE
• VCCITY CUST.CCITYTYPE
• VCNT NUMBER
• BEGIN
• SELECT COUNT() INTO VCNT FROM CUST WHERE
  CIDVCID
• IF VCNT1 THEN
• SELECT CNAME,CCITY INTO VCNAME,VCCITY FROM CUST
  WHERE CIDVCID
• DBMS_OUTPUT.PUT_LINE(VCID '-' VCNAME '
  STAYS IN ' VCCITY)
• ELSE
• DBMS_OUTPUT.PUT_LINE(VCID '-' 'INVALID
  CID')
• END IF
• END
• /

23
CHAPTER 7Oracle PL/SQL Basics 22 of 22
Page 201 - 216

• Example EX7_11
• This example demonstrates use of ROWTYPE
  attribute in PL/SQL program.
• SET VERIFY OFF
• SET SERVEROUTPUT ON
• DECLARE
• VCUST CUSTROWTYPE
• VCID VARCHAR2(6)
• VCNT NUMBER
• BEGIN
• VCID 'CID'
• SELECT COUNT() INTO VCNT FROM CUST WHERE
  CIDVCID
• IF VCNT1 THEN
• SELECT INTO VCUST FROM CUST WHERE CIDVCID
• DBMS_OUTPUT.PUT_LINE(VCID '-' VCUST.CNAME
  ' STAYS IN ' VCUST.CCITY)
• ELSE
• DBMS_OUTPUT.PUT_LINE(VCID '-' 'INVALID
  CID')
• END IF
• END

24
CHAPTER 7Practical Session
Important Guideline to the Faculty/Instructor Ple
ase create User Name corresponding to each
student/ group of students. Ignore if already
created. Please ensure that all the student
practice all the 12 examples given in the
Chapter.

25
CHAPTER 7Short/ Objective Type Question 1

• Q1. Define identifiers, literals and delimiters
  in Oracle PL/SQL.
• Q2. Explain TYPE and ROWTYPE attributes with
  the help of suitable examples.
• Q3. What do you understand by SQL cursors?
• Q4. Write a PL/SQL program which will accept 3
  numbers and print the smallest among them.
• SQLgt_at_ C/TTP/Q4
• Enter value for a 12
• Enter value for b 34
• Enter value for c 12
• Smallest Number is 12
• Q5. Write PL/SQL program using Loop End Loop,
  For Loop and While Loop, which
• to will accept integer from 1 to 10 and print
  factorial.
• Q7. For example Factorial of 5 is 54321120.
• SQLgt_at_ C/TTP/Q5
• Enter value for i_num 6

26
CHAPTER 7Short/ Objective Type Question

• Q8. Write a PL/SQL program which will accept a
  number from 1 to 20 and display following figure.
• SQLgt_at_ C/TTP/Q8
• Enter value for i_num 10
• --
• ----
• ------
• --------
• ----------
• ------------
• --------------
• ----------------
• ------------------
• --------------------
• Q9. The basic programming unit of a PL/SQL code
  is a
• a. Procedure
• b. Sub-program
• c. Module
• d. Block

27
CHAPTER 7Home Assignment

• WORKOUT
• 235 236

28
DATABASE MANAGEMENT SYSTEM ORACLE SQL AND PL/SQL

Book Page Number 221 - 236

• CHAPTER 8
• Function
• Procedure
• Package

29
CHAPTER 8Function 1 of 3
Page 221 - 225

• Function is a subprogram to compute a value
• A function has two parts
• Function specification
• Function body
• CREATE OR REPLACE FUNCTION ltFUNCTION_NAMEgt
  ARGUMENTS
• RETURN DATATYPE IS (VARIABLE DATATYPE)
• BEGIN
• (EXECUTABLE STATEMENTS)
• EXCEPTION
• (EXCEPTION HANDLERS)
• END

30
CHAPTER 8Function 2 of 3
Page 221 - 225

• Example EX8_1
• Write a function that accepts two numbers A B,
  sum it and store the computed value in C.
• CREATE OR REPLACE FUNCTION FSUM(A NUMBER,B
  NUMBER)
• RETURN NUMBER IS C NUMBER
• BEGIN
• CAB
• RETURN C
• END
• /
• SQLgt _at_ C\TTP\EX8_1.SQL
• Function created.
• SQLgt SELECT FSUM(10,15) FROM DUAL
• FSUM(10,15)
• 25
• SQLgt SELECT PNAME,PCOST,FSUM(PCOST,2) NEW_PCOST
  FROM PROD
• PNAME PCOST NEW_PCOST
• PEN 20.5 22.5
• FLOPPY 30 32

31
CHAPTER 8Function 3 of 3
Page 221 - 225

• Example EX8_6
• Write a function to find the maximum PCOST in
  PROD Table.
• CREATE OR REPLACE FUNCTION FORA RETURN NUMBER IS
• VPCOST NUMBER
• BEGIN
• SELECT MAX(PCOST) INTO VPCOST FROM PROD
• RETURN VPCOST
• END
• /
• SQLgt _at_ C\TTP\EX8_6.SQL
• Function created.
• SQLgt SELECT FORA FROM DUAL
• FORA
• 30
• SQLgt SELECT PNAME,FORA FROM PROD WHERE
  PCOSTFORA
• PNAME FORA
• FLOPPY 30

32
CHAPTER 8Procedure 1 of 5
Page 225 - 229

• A procedure is a subprogram that performs a
  specific action
• CREATE OR REPLACE PROCEDURE ltPROCEDURE NAMEgt
  PARAMETER LIST IS
• (VARIABLE DATA TYPE)
• BEGIN
• EXECUTABLE STATEMENTS
• EXCEPTION
• EXCEPTION HANDLERS
• END

33
CHAPTER 8Procedure 2 of 5
Page 225 - 229

• IN Parameter
• Used to pass values to the subprogram when
  invoked
• It acts like a constant and it cannot be assigned
  a value
• IN OUT Parameter
• Used to pass initial values to the procedure when
  invoked
• It also returns updated values to the caller
• Acts like an initialized variable that can be
  assigned to other variables or to itself
• OUT Parameter
• Used to return values to the caller of a
  subprogram
• Since the initial value for an OUT parameter is
  undefined, its value can be assigned to another
  variable
• Procedures with OUT parameter can not be executed
  with SQLgtEXECUTE ltProcedure Namegt
• It must be called from other PL/SQL program.

34
CHAPTER 8Procedure 3 of 5
Page 225 - 229

• Example EX8_8
• This program demonstrates usage of IN parameter.
• SET SERVEROUTPUT ON
• CREATE OR REPLACE PROCEDURE PMINUS(A IN NUMBER,B
  IN NUMBER)
• IS C NUMBER
• BEGIN
• CA-B
• DBMS_OUTPUT.PUT_LINE(A ' - ' B ' IS
  ' C)
• END PMINUS
• /
• SQLgt _at_ C/TTP/EX8_8.SQL
• Procedure created.
• SQLgt EXECUTE PMINUS(78,45)
• 78 - 45 IS 33

35
CHAPTER 8Procedure 4 of 5
Page 225 - 229

• Example EX8_9
• This program demonstrates IN OUT parameter.
• CREATE OR REPLACE PROCEDURE PMULTIPLY(A IN OUT
  NUMBER,
• B IN OUT NUMBER) IS C NUMBER
• BEGIN
• CAB DBMS_OUTPUT.PUT_LINE(A ' '
  B ' ' C)
• END PMULTIPLY
• /
• SQLgt _at_ C/TTP/EX8_9
• Procedure created.
• Example 8_9_CALL.SQL
• DECLARE
• A NUMBER A B NUMBER B
• BEGIN
• PMULTIPLY(A,B)
• END
• /

36
CHAPTER 8Procedure 5 of 5
Page 225 - 229

• Example EX8_10
• This program demonstrates OUT parameter.
• CREATE OR REPLACE PROCEDURE PDIV_OUT(A NUMBER,B
  NUMBER, C OUT NUMBER) IS
• BEGIN
• IF Bltgt0 THEN CA/B ELSE C-1 END
  IF
• END PDIV_OUT
• /
• Example EX8_10_CALL.SQL
• SET SERVEROUTPUT ON
• SET VERIFY OFF
• DECLARE
• A NUMBER A B NUMBER B C
  NUMBER
• BEGIN
• PDIV_OUT(A,B,C) DBMS_OUTPUT.PUT_LINE('D
  IVIDE'C)
• END
• SQLgt _at_ C/TTP/EX8_10
• Procedure created.

37
CHAPTER 8Package 1 of 2
Page 229 - 233

• Packages are created to club relevant objects
  like
• Function, procedures, cursors, etc. in one single
  place
• It consists of two parts, package specification
  and package body
• Package Specification
• In the package specification functions,
  procedures, cursors etc are specified
• CREATE OR REPLACE PACKAGE ltPACKAGE_NAMEgt IS
  ltDECLARATIONSgt
• BEGIN
• (EXECUTABLE STATEMENTS)
• END (PACKAGE_NAME)
• Package Body
• In the package body detailed program
  corresponding to function, procedure, cursors etc
  are written.
• CREATE OR REPLACE PACKAGE BODY ltPACKAGE_NAMEgt IS
  ltDECLARATIONSgt
• BEGIN
• (EXECUTABLE STATEMENTS)
• END (BODY_NAME)

38
CHAPTER 8Package 2 of 2
Page 229 - 233

• Example EX8_13
• In this example the package is encapsulated with
  2 procedures and 4 functions.
• Package Name Description
• PACKSPN.SQL Package specification file name
• PACKBODY.SQL Package body file name
• CALCULATE Package name
• Function Name Description
• EX8_1.SQL Accepts two numbers and returns sum
• EX8_2.SQL Accepts two numbers and returns
  difference
• EX8_4.SQL Accepts two numbers and returns
  multiplied value
• EX8_3.SQL Accepts two numbers and returns
  divided value
• Procedure Name Description
• EX8_7.SQL Accepts two numbers and displays sum
• EX8_8.SQL Accepts two numbers and displays
  difference
• Guideline to the Faculty/Instructor

39
CHAPTER 8Short/ Objective Type Questions 1 of 2
Page 233 - 235

• Q1. What is the difference between function and
  procedure?
• Q2. What is the use of IN OUT parameter in
  procedures? Explain with the help of an example
  Q3. Describe various steps required to create a
  package.
• Q9. Choose the correct statements.
• a. OUT parameters are passed by reference
• b. OUT parameter are passed by value
• c. IN parameters are passed by reference
• d. IN parameters are passed by value
• Q10. Which of the following keywords is not used
  in a PL/SQL procedure creation?
• a. BEGIN
• b. EXCEPTION
• c. END
• d. DECLARE

40
CHAPTER 8 Short/ Objective Type Questions 2 of
2
Page 233 - 235

• Q11. Which of the following comments about the
  RETURN statement (in a PL/SQL function) are not
  correct? (Convert into True/False)
• a. There must be at least one return statement
• b. There must be exactly one return statement
• c. The return statement must return a value whose
  data type is same as the data type specified in
  the declaration.
• d. Return statement can return more than one
  value.
• Q12. Which of the following cannot be anonymous?
• a. Package
• b. Procedure
• c. Function
• d. None of these

41
CHAPTER 8 Home Assignment
Page 235 - 236

• Short/ Objective Type Questions
• 4. to 8.
• Workout
• 235 236

42
DATABASE MANAGEMENT SYSTEM ORACLE SQL AND PL/SQL

Book Page Number 237 - 255

• CHAPTER 9
• Oracle Exception Handler Database Triggers
• Implicit Cursor

43
CHAPTER 9Oracle Exception Handler 1 of 3
Page 237 - 243

• Exceptions and exception handler
• Deals with run time errors in PL/SQL programs
• Exceptions are classified into two types
• PRE-DEFINED EXCEPTION
• USER-DEFINED EXCEPTION
• DECLARE
• Variables, cursor and other declarations
• BEGIN
• Executables comprising SQL and procedural
  statements
• EXCEPTION
• Statements to perform action in case of errors
• END

44
CHAPTER 9Oracle Exception Handler 2 of 3
Page 237 - 243

• Frequently Used exception handler
• NO_DATA_FOUND
• TOO_MANY_ROWS
• ZERO_DIVIDE
• DUP_VAL_ON_INDEX
• INVALID_NUMBER
• INVALID_CURSOR
• CURSOR_ALREADY_OPEN
• LOGIN_DENIED
• PROGRAM_ERROR
• STORAGE_ERROR
• TIMEOUT_ON_RESOURCE
• VALUE_ERROR

45
CHAPTER 9Oracle Exception Handler 3 of 3
Page 237 - 243

• Example EX9_2
• This PL/SQL program demonstrates use of
  exception handler
• DECLARE
• VCID SALE_DETAIL.CIDTYPEUPPER('VCID')
  VSALEDT SALE_DETAIL.SALEDTTYPE
• BEGIN
• SELECT SALEDT INTO VSALEDT FROM SALE_DETAIL
• WHERE CIDVCID DBMS_OUTPUT.PUT_LINE(VCID '
  PURCHASED ON ' VSALEDT)
• EXCEPTION
• WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-
  20001, 'NO DATA FOUND')
• WHEN TOO_MANY_ROWS THEN RAISE_APPLICATION_ERROR(-
  20002, 'MORE THAN ONE ROW FOUND')
• END
• /
• SQLgt _at_ C\TTP\EX9_2
• Enter value for vcid C1
• ORA-20002 MORE THAN ONE ROW FOUND
• SQLgt /
• Enter value for vcid C2
• C2 PURCHASED ON 14-JUL-08

46
CHAPTER 9Triggers 1 of 2
Page 243 - 249

• Oracle Database Trigger
• Stored procedure that is fired when a DML
  statement is issued
• Execution of trigger is irrespective of logged
  user and application
• Primarily used to enforce checks, security and
  backing up data
• Trigger comprises of a statement, restriction
  body
• Triggers are categorized into
• Before Insert/Update/Delete
• For Each Row/Statement
• After Insert/Update/ Delete
• For Each Row/Statement
• CREATE OR REPLACE TRIGGER ltTRIGGER_NAMEgt
• BEFORE / AFTER INSERT / UPDATE / DELETE ON
  ltTABLE_NAMEgt
• FOR EACH STATEMENT/ FOR EACH ROW WHEN ,COND.

47
CHAPTER 9Triggers 2 of 2
Page 243 - 249

• Example EX9_6
• This trigger checks the value of SAL before
  insert or update statement and ensures that SAL
  below 500 is not inserted. It acts BEFORE
  insertion or update.
• CREATE TABLE EMP_TRIG (ENAME VARCHAR2(20), SAL
  NUMBER(8))
• CREATE OR REPLACE TRIGGER MIN_SAL_CHK BEFORE
  INSERT OR UPDATE ON EMP_TRIG FOR EACH ROW
• WHEN (NEW.SALlt500)
• BEGIN
• RAISE_APPLICATION_ERROR(-20000,'SAL must be
  above 500')
• END
• /
• SQLgt _at_ C\TTP\EX9_6.SQL
• Trigger created.
• SQLgt INSERT INTO EMP_TRIG VALUES ('PK',300)
• INSERT INTO EMP_TRIG VALUES ('PK',300)
• ORA-20000 SAL must be above 500 ERROR
• SQLgt INSERT INTO EMP_TRIG VALUES ('PK',3000)
• 1 row created.
• SQLgt COMMIT

48
CHAPTER 9Implicit Cursor 1 of 3
Page 250 - 253

• Automatically created and handled by Oracle
• Reserve an area in main memory to populate the
  data
• Release the memory area after the processing
• Handles all DMLs which effect multiple rows
• SELECT statement which returns exactly one row

49
CHAPTER 9Implicit Cursor 2 of 3
Page 250 - 253

• Attribute Description
• ROWCOUNT Contains the number of records
  processed from the cursor
• FOUND Contains the value TRUE if row was fetched
  successfully, FALSE otherwise
• NOTFOUND Contains the value TRUE if row was not
  fetched successfully, FALSE otherwise
• ISOPEN Contains the value TRUE if cursor is
  open, FALSE otherwise

50
CHAPTER 9Implicit Cursor 3 of 3
Page 250 - 253

• Example EX9_12
• In this example attribute of implicit cursor is
  demonstrated.
• DECLARE
• VCNT NUMBER(4)
• BEGIN
• SELECT COUNT() INTO VCNT FROM CUST
• DBMS_OUTPUT.PUT_LINE('SQLFOUND VALUE')
• IF SQLFOUND THEN DBMS_OUTPUT.PUT_LINE('TRUE')
  ELSE DBMS_OUTPUT.PUT_LINE('FALSE')
• END IF
• DBMS_OUTPUT.PUT_LINE('SQLROWCOUNT VALUE')
• VCNT SQLROWCOUNT
• IF SQLROWCOUNTgt0 THEN DBMS_OUTPUT.PUT_LINE(VCNT)
  ELSE DBMS_OUTPUT.PUT_LINE('No Row found')
• END IF
• END
• /
• SQLgt _at_ C\TTP\EX9_12
• SQLFOUND VALUE
• TRUE

51
CHAPTER 9Practical Session
Important Guideline to the Faculty/Instructor Ple
ase create User Name corresponding to each
student/group of students. Ignore if already
created. Please ensure that the students
practice all the 12 examples given in the
Chapter. This is essentially required to
understand concepts and examples deliberated in
Chapter 10.

52
CHAPTER 9Short Type Questions
Page 253 - 254

• Q1. Describe role of exception handler in PL/SQL
  programming.
• Q2. What is purpose of using OTHERS in exception
  handler?
• Q3. Illustrates different categories of triggers.
• Q4. Elaborate implicit cursors and its
  attributes.
• Q5. Write a PL/SQL program without exception
  handler which accepts two numbers A B,
  calculates C A/B and displays the value of C.
  Input B 0 and see the effect.
• Q6. Write a PL/SQL program with exception handler
  which accepts two numbers A B, calculates C
  A/B and displays the value of C. Input B 0 and
  see the effect.
• Q7. Write a PL/SQL program without exception
  handler which accepts two numbers A B,
  calculates C A/B and displays the value of C.
  If B is 0 trap it using IF END IF and use
  exception handler for any other error. Input B0
  and see the effect.
• Q8. Write a trigger which will convert ENAME to
  upper case at the time of insertion of record.

53
CHAPTER 9 Home Assignment
Page 254

• WORKOUT
• 255

54
DATABASE MANAGEMENT SYSTEM ORACLE SQL AND PL/SQL

Book Page Number 256 - 285

• CHAPTER 10
• Explicit Advance Cursors

55
CHAPTER 10Explicit Advance Cursors 1 of 10
Page 256 - 279

• When a SELECT statement returns more than one row
  explicit cursor is essentially required
• In explicit cursor rows are accessed sequentially
• Use of parameters in explicit cursors is required
  to load records in the active set in the memory
  based on some values

56
CHAPTER 10Explicit Advance Cursors 2 of 10
Page 256 - 279

• The following five steps are required to handle
  an explicit cursor
• Declare the cursor
• Open the cursor
• Fetch rows from the cursor
• Terminate statement
• Close the cursor

57
CHAPTER 10Explicit Advance Cursors 3 of 10
Page 256 - 279

• The following five steps are required to handle
  an explicit cursor
• Declare the cursor
• Open the Cursor
• Fetch rows from the cursor
• Terminate statement
• Close the cursor

58
CHAPTER 10Explicit Advance Cursors 4 of 10
Page 256 - 279

• Cursor Attribute Description
• ROWCOUNT This numeric attribute returns the
  number of rows fetched by the cursor
• FOUND It is a boolean attribute. It returns
  TRUE if the previous FETCH returned a row,
  FALSE otherwise
• NOTFOUND It returns TRUE if the previous FETCH
  does not return a row, FALSE otherwise
• ISOPEN Evaluates to TRUE, if an explicit
  cursor is open, FALSE otherwise.

59
CHAPTER 10Explicit Advance Cursors 5 of 10
Page 256 - 279

• Example EX10_1
• In this PL/SQL program attributes of explicit
  cursor is demonstrated.
• DECLARE
• VREC CUSTROWTYPE VCNT NUMBER(4)
• CURSOR CUST_CURSOR IS SELECT FROM CUST ORDER
  BY CNAME
• BEGIN
• IF CUST_CURSORISOPEN THEN
• DBMS_OUTPUT.PUT_LINE('CUST_CURSORISOPEN -
  Before OPEN - TRUE')
• ELSE
• DBMS_OUTPUT.PUT_LINE('CUST_CURSORISOPEN -
  Before OPEN - FALSE')
• END IF
• OPEN CUST_CURSOR
• IF CUST_CURSORISOPEN THEN
• DBMS_OUTPUT.PUT_LINE('CUST_CURSORISOPEN -
  After OPEN and Before CLOSE - TRUE')
• ELSE
• DBMS_OUTPUT.PUT_LINE('CUST_CURSORISOPEN -
  After OPEN and Before CLOSE - FALSE')
• END IF

60
CHAPTER 10Explicit Advance Cursors 6 of 10
Page 256 - 279

• Example EX10_1
• In this PL/SQL program attributes of explicit
  cursor is demonstrated.
• LOOP
• FETCH CUST_CURSOR INTO VREC EXIT WHEN
  CUST_CURSORNOTFOUND
• DBMS_OUTPUT.PUT_LINE(VREC.CNAME ' lives in '
  VREC.CCITY)
• END LOOP
• VCNT CUST_CURSORROWCOUNT
• IF CUST_CURSORROWCOUNTgt0 THEN
• DBMS_OUTPUT.PUT_LINE('CUST_CURSORROWCOUNT
  VALUE ' VCNT)
• ELSE
• DBMS_OUTPUT.PUT_LINE('No Row found')
• END IF
• CLOSE CUST_CURSOR
• IF CUST_CURSORISOPEN THEN
• DBMS_OUTPUT.PUT_LINE('CUST_CURSORISOPEN -
  After CLOSE - TRUE')
• ELSE
• DBMS_OUTPUT.PUT_LINE('CUST_CURSORISOPEN -
  After CLOSE - FALSE')
• END IF

61
CHAPTER 10Explicit Advance Cursors 7 of 10
Page 256 - 279

• Example EX10_1
• In this PL/SQL program attributes of explicit
  cursor is demonstrated.
• SQLgt _at_ C\TTP\EX10_1
• CUST_CURSORISOPEN - Before OPEN - FALSE
• CUST_CURSORISOPEN - After OPEN and Before CLOSE
  - TRUE
• AASTIK lives in KOLKATA
• ANUMITA lives in INDORE
• ARPAN lives in CHENNAI
• PRADIP lives in MYSORE
• TUSHAR lives in PUNE
• CUST_CURSORROWCOUNT VALUE 5
• CUST_CURSORISOPEN - After CLOSE FALSE

62
CHAPTER 10Explicit Advance Cursors 8 of 10
Page 256 - 279
Example EX10_1 In this PL/SQL program attributes
of explicit cursor is demonstrated.
63
CHAPTER 10Explicit Advance Cursors 9 of 10
Page 256 - 279

• Advance/Parameterized Cursor
• Parameters can be included in a cursor to pass
  values at runtime
• Parameters in cursors are useful when a cursor
  is required to be
• opened based on different set of parameter
  values
• A cursor with parameter can be opened and closed
  several times.
• Each time a new active set is loaded in the
  memory and the
• pointer is placed at first record.
• CURSOR CURSOR_NAME(PARAMETER_NAME DATATYPE, )
  IS SELECT_STATEMENT
• Where
• CURSOR_NAME is name of the explicit cursor
• PARAMETER_NAME is name of parameter (one or
  more)
• DATATYPE is data type of each parameter
• SELECT_STATEMENT is a SELECT statement, which
  must contain parameter name in WHERE clause

64
CHAPTER 10Explicit Advance Cursors 10 of 10
Page 256 - 279
Example This example illustrates basic concepts
associated with advance/parameterized
cursor. CURSOR SALE_CURS(VCID VARCHAR2)
IS SELECT P.PNAME, P.PCOST FROM PROD P,
SALE_DETAIL S WHERE P.PIDS.PID AND
S.CIDVCID In this example CURSOR_NAME is
SALE_CURS PARAMETER_NAME is VCID DATATYPE is
VARCHAR2 SELECT_STATEMENT is SELECT P.PNAME,
P.PCOST FROM PROD P, SALE_DETAIL S WHERE
P.PIDS.PID AND S.CIDVCID Note that
parameter name VCID is present in the SELECT
statement. Guideline to the Faculty/Instructor El
aborate concept relevant to advance cursor with
the help of examples (EX10_9 to EX10_15) given in
this chapter.
65
CHAPTER 10Practical Session
Important Guideline to the Faculty/Instructor P
lease create User Name corresponding to each
student/group of students. Ignore if already
created. Ensure that the students practice all
the 15 examples given in the Chapter. This will
ensure building up rock solid conception relevant
to PL/SQL.

66
CHAPTER 10Short Type Questions 1 of 6
Page 279 - 282
Q6. Find the error in this PL/SQL program.
DECLARE VCNAME CUST.CNAMETYPE VCNT
NUMBER(4) BEGIN SELECT CNAME INTO VCNAME FROM
CUST DBMS_OUTPUT.PUT_LINE('SQLFOUND
VALUE') IF SQLFOUND THEN DBMS_OUTPUT.PUT_LINE
('TRUE') ELSE DBMS_OUTPUT.PUT_LINE('FALSE')
END IF DBMS_OUTPUT.PUT_LINE('SQLROWCOUNT
VALUE') VCNT SQLROWCOUNT IF
SQLROWCOUNTgt0 THEN DBMS_OUTPUT.PUT_LINE(VCNT)
ELSE DBMS_OUTPUT.PUT_LINE ('No Row
found') END IF END /
67
CHAPTER 10Short Type Questions 2 of 6
Page 279 - 282
Q8. What will be the output of this PL/SQL code?
SET SERVEROUTPUT ON DECLARE
VCNAME CUST.CNAMETYPE CURSOR CNAME_CURSOR IS
SELECT CNAME FROM CUST BEGIN OPEN
CNAME_CURSOR LOOP FETCH CNAME_CURSOR INTO
VCNAME DBMS_OUTPUT.PUT_LINE(VCNAME)
EXIT WHEN CNAME_CURSORNOTFOUND END LOOP
CLOSE CNAME_CURSOR END /
68
CHAPTER 10Short Type Questions 3 of 6
Page 279 - 282
Q9. Find error in the PL/SQL program. What will
be the output of this program? SET
SERVEROUTPUT ON DECLARE VCNAME CUST.CNAMETYPE
CURSOR CNAME_CURSOR IS SELECT CNAME FROM
CUST BEGIN OPEN CNAME_CURSOR LOOP FETCH
CNAME_CURSOR INTO VCNAME DBMS_OUTPUT.PUT_LINE
(VCNAME) END LOOP CLOSE CNAME_CURSOR END /
69
CHAPTER 10Short Type Questions 4 of 6
Page 279 - 282
Q10. Find the error in this PL/SQL program. SET
SERVEROUTPUT ON DECLARE VCNAME CUST.CNAMETYPE
CURSOR CNAME_CURSOR IS SELECT CNAME FROM
CUST BEGIN OPEN CNAME_CURSOR LOOP FETCH
CNAME_CURSOR INTO VCNAME EXIT WHEN
CNAME_CURSORNOTFOUND DBMS_OUTPUT.PUT_LINE(VC
NAME) CLOSE CNAME_CURSOR END LOOP END /
70
CHAPTER 10Short Type Questions 5 of 6
Page 279 - 282
Q11. Find the error in this PL/SQL program. SET
SERVEROUTPUT ON DECLARE VCNAME CUST.CNAMETYPE
CURSOR CNAME_CURSOR IS SELECT CNAME FROM
CUST BEGIN LOOP OPEN CNAME_CURSOR
FETCH CNAME_CURSOR INTO VCNAME EXIT WHEN
CNAME_CURSORNOTFOUND DBMS_OUTPUT.PUT_LINE(VC
NAME) CLOSE CNAME_CURSOR END LOOP END /
71
CHAPTER 10Short Type Questions 6 of 6
Page 279 - 282
Q12. What will be the output of this PL/SQL
program? SET SERVEROUTPUT ON DECLARE
VCNAME CUST.CNAMETYPE CURSOR CNAME_CURSOR IS
SELECT CNAME FROM CUST BEGIN OPEN
CNAME_CURSOR LOOP FETCH CNAME_CURSOR INTO
VCNAME FETCH CNAME_CURSOR INTO VCNAME
EXIT WHEN CNAME_CURSORNOTFOUND
DBMS_OUTPUT.PUT_LINE(VCNAME) END LOOP CLOSE
CNAME_CURSOR END /
72
CHAPTER 10Home Assignment
Page 282 - 285
WORKOUT 282 285