Notes: PL/SQL

Sunday. December 22, 2019 - 3 mins

Overview

To solve problems:

Define variables to make SQL flexible
Create functions to reduce copy-and-paste
Wrap up a SQL procedure
Export formatted query results (Excel etc.)

PL/SQL

Basic Syntax

Basic Syntax of PL/SQL: block-structured language. Every block ends with a semicolon (;).

DECLARE 
   <declarations section> 
BEGIN 
   <executable command(s)>
EXCEPTION 
   <exception handling> 
END;

The ‘Hello World’ Example:

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

To run the code from the SQL command line, type / at a newline after the block.

Hello World! 

PL/SQL procedure successfully completed.

Function

Syntax:

CREATE OR REPLACE FUNCTION 
<function_name>
(
<parameter IN/OUT <datatype>
)
RETURN <datatype>
[ IS | AS ]
<declaration_part>
BEGIN
<execution part> 
EXCEPTION
<exception handling part>
END; 

Example:

CREATE OR REPLACE FUNCTION welcome_msgJune (p_name IN VARCHAR2) RETURN VAR.CHAR2
IS
BEGIN
RETURN ('Welcome '|| p_name);
END;
/
DECLARE
lv_msg VARCHAR2(250);
BEGIN
lv_msg := welcome_msg_func ('Guru99');
dbms_output.put_line(lv_msg);
END;
SELECT welcome_msg_func('Guru99:') FROM DUAL;

Procedure

Syntax:

CREATE OR REPLACE PROCEDURE 
<procedure_name>
	(
	<parameterl IN/OUT <datatype>
	..
	.
	)
[ IS | AS ]
	<declaration_part>
BEGIN
	<execution part>
EXCEPTION
	<exception handling part>
END;

Example:

CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2) 
IS
BEGIN
dbms_output.put_line ('Welcome '|| p_name);
END;
/
EXEC welcome_msg ('Guru99');

Function vs. Procedure

Difference:

Procedure	Function
To execute certain process	To perform some calculation
Cannot call in `SELECT` statement	`FUNCTION` with no `DML` statements can be called in `SELECT`
Use `OUT` parameter to return the value	Use `RETURN` to return the value
Not mandatory to return the value	Mandatory to return the value
`RETURN` will simply exit the subprogram	`RETURN` will exit the subprogram and also returns the value
No `RETURN` datatype specification at creation	`RETURN` datatype is mandatory at creation

Similarity:

Both can be called from other PL/SQL blocks.
If the exception raised in the subprogram is not handled in the subprogram exception handling section, then it will propagate to the calling block.
Both can have as many parameters as required.
Both are treated as database objects in PL/SQL.

Specific Problems

DROP TABLE IF EXISTS <table_name>: Solution
Embed a variable in table name table_name_&ym: bind variable
export queries or saved tables
oracle in python
run script from shell

Reference

Zhijian Liu

A foodaholic