PL/SQL

Question 1

What is package?

Answer 1

1. Schema object logically groups PL/SQLs
2. package specification - interface, public objects
3. package definition - not in package specification but only in package definition are private objects
4. PL/SQL created inside package can only be dropped with the package (DROP PACKAGE)

Question 2

What is type?

Answer 2

TODO

Question 3

What is PL/SQL?

Answer 3

SQL with procedural features of programming language,such as for loop, conditions

https://www.tutorialspoint.com/plsql/index.htm

Question 4

PL/SQL Basic structure

Answer 4

1. block-structured language, each block contains: declarations, execution commands, exception handling
   2.EXAMPLE
   DECLARE

BEGIN

EXCEPTION

END;

EXAMPLE2
DECLARE 
   message  varchar2(20):= 'Hello, World!'; 
BEGIN 
   dbms_output.put_line(message); 
END; 
/

Question 5

What is a trigger?

Answer 5

1. Special stored procedure will run when action (event) is performed (INSERT, DELETE, UPDATE), used to enforce some action, usually 1 table 1 trigger
2. Types: After triggers, Instead of triggers(replace the original operation action)

Question 6

What is a (stored) procedure?

Answer 6

Procedure:

1. like a subprogram that is a ‘unit/module’ of the whole program
2. used to perform a actions
3. Can take input/output parameters

Function:
1. used to compute a value, must return(a single value or a table)

EXAMPLEs:

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;
with params

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;

Question 7

What is a Function ?

Difference with stored procedure?

Answer 7

1. a set of SQL statements that accept only input parameters, perform actions and return the result.
2. A function can return an only a single value or a table.

CREATE OR REPLACE FUNCTION totalCustomers 
RETURN number IS 
   total number(2) := 0; 
BEGIN 
   SELECT count(*) into total 
   FROM customers; 

RETURN total;
END;
/

Difference

1. Procedure can call a function, function can not call a stored procedure
2. Function have to return a value, for SP it is optional
3. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
4. Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.

Question 8

What is cursor?

Answer 8

1. A pointer to the SQL (contect area/memory area)
2. Holds a set of rows returned by a SQL statement - called active set
3. create a cursor that hold the returned rows of SQL statements

Working with an explicit cursor includes the following steps −

Declaring the cursor for initializing the memory
Opening the cursor for allocating the memory
Fetching the cursor for retrieving the data
Closing the cursor to release the allocated memory

CURSOR c_customers IS
SELECT id, name, address FROM customers;
OPEN c_customers;
FETCH c_customers INTO c_id, c_name, c_addr;
CLOSE c_customers;