Procedures, Functions, Triggers

Question 1

What is the basic syntax for creating a procedure?

Answer 1

CREATE OR REPLACE
PROCEDURE name(parameters) AS
– Declare any variables here
BEGIN

EXCEPTION

END;

Question 2

What is the basic syntax for creating a function?

Answer 2

CREATE OR REPLACE
   FUNCTION name(parameters)
      RETURN datatype AS
   -- Declare any variables here
   BEGIN
      -- Return statement must be included
   EXCEPTION

END;

Question 3

Do these need declare statements (Y/N)?

• Procedures
• Functions
• Triggers

Answer 3

• N
• N
• Y

Question 4

When do triggers execute?

Answer 4

DML statements

INSERT, UPDATE, DELETE

Question 5

What are the 2 types of triggers?

Answer 5

• Statement Level

- Row Level

Question 6

What are statement level triggers?

Answer 6

Fires once for a statement

EX. We update 10 rows, then it will fire 1 time

Question 7

What are row-level triggers?

Answer 7

Will fire for every row that’s effected by a statement

EX. We update 10 rows, then it will fire 10 times

Question 8

:OLD and :NEW bind variables only for ___

Answer 8

Row-level triggers

Question 9

:OLD is meaningless in a ___

Answer 9

INSERT

Question 10

:NEW is meaningless in a ___

Answer 10

DELETE

Question 11

:OLD and :NEW are used in ___

Answer 11

Triggers

Question 12

What is the basic syntax for creating a trigger?

Answer 12

CREATE OR REPLACE TRIGGER triggerName
BEFORE -- OR AFTER
INSERT -- OR DELETE, UPDATE
ON tableName
FOR EACH ROW --if row-level trigger
-- PL/SQL block

Question 13

What is the basic sequence for creating a sequence?

Answer 13

CREATE SEQUENCE sequenceName START WITH num;

SELECT sequenceName.nextval FROM DUAL;

Question 14

How would you use a sequence in a trigger

Answer 14

CREATE OR REPLACE TRIGGER triggerName
BEFORE INSERT ON tableName
FOR EACH ROW
BEGIN
	SELECT sequenceName.nextval INTO :NEW.columnName FROM DUAL;
END;

Question 15

In a ___, a return value must be used

Answer 15

Function