Show Menu
Cheatography

PLSQL

Function

CREATE OR REPLACE FUNCTION function_name
(parameter_1 data_type,
Parameter_2 data_type)
RETURN data_type
{ IS | AS }
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [function_name];

Procedures

Create [ or REPLACE ] PROCEDURE procedure_name
(
parameter_name_1 data_type,
parameter_name_2 data_type
)
{ IS | AS }
pl_sql_block
Parameter
By position
By name

Packages

CREATE PACKAGE package_name { IS | AS }
procedure_or_function_specification_1;
procedure_or_function_specification_2;
END [package_name];
Package body
CREATE PACKAGE BODY package_name { IS | AS }
procedure_or_function_body_1;
procedure_or_function_body_2;
END [package_name];

Bind variable

Need to specify type
Need to wrap around quote when assign string value
No need quote when reference the variable
Value can only be assigned in a PL, via exec or Begin / End block
Use PRINT to list out bind variable

Condit­ional and Loops

Declare and use of variable
%TYPE %ROWTYPE
VARCHAR2 NUMBER DATE

Assignment operator :=

Nested block variable scope
DECLARE
myvar number;
BEGIN
myvar:=1;
dbms_output.put_line(myvar);
DECLARE
myvar number;
BEGIN
myvar:=2;
dbms_output.put_line(myvar);
END;
dbms_output.put_line(myvar);
END;

IF THEN ELSE END IF

DECLARE
v_number NUMBER;
BEGIN
IF v_number<=0 THEN
dbms_output.put_line('it is less than 0');
ELSIF v_number>=0 THEN
dbms_output.put_line('it is greater than 0');
ELSE
dbms_output.put_line('not either of the case');
END IF;
END;

Loops
FOR IN .. LOOP
{statements};
END LOOP;
WHILE condition
LOOP
{statements};
END LOOP;
LOOP
{statemens};
EXIT WHEN condition;
CONTINUE WHEN condition;
END LOOP;

Loops
DECLARE
i NUMBER :=10;
BEGIN
FOR i IN 1..5 LOOP
dbms_output.put_line(i);
END LOOP;
dbms_output.put_line(i);
END;
CASE – Simple Case
CASE expression
WHEN value_1 THEN
..
WHEN value_2 THEN
ELSE
END CASE;
CASE – Searched Case
WHEN boolean_expression THEN
ELSE
END CASE;
 

Function vs Procedures

Function must return a value. Procedure can not return a value
Function and procedure can both return data in OUT and IN OUT parameters
Function can be called from SQL, but not for procedure
Can not perform a DML DDL within function, while allowed in procedure

Trigger

CREATE [OR REPLACE] TRIGGER trigger_name
BEFORE | AFTER
[INSERT, UPDATE, DELETE [COLUMN NAME..]
ON table_name
Referencing [ OLD AS OLD | NEW AS NEW ]
FOR EACH ROW | FOR EACH STATEMENT [ WHEN Condition ]
DECLARE
[declaration_section]
BEGIN
[executable_section]
EXCEPTION
[exception_section]
END;

Substi­tution variable

No need to specify type, as it is always character type
No need to wrap around quote when assign value
Need quote when reference the variable
ACCEPT implicitly defined a substi­tution type variable
Use DEFINE to list out substi­tution variable
 

Procedures Parts

S.N.
Parts & Descri­ption
1
Declar­ative Part It is an optional part. However, the declar­ative part for a subprogram does not start with the DECLARE keyword. It contains declar­ations of types, cursors, constants, variables, except­ions, and nested subpro­grams. These items are local to the subprogram and cease to exist when the subprogram completes execution.
2
Executable Part This is a mandatory part and contains statements that perform the designated action.
3
Except­ion­-ha­ndling This is again an optional part. It contains the code that handles run-time errors.

Parameter Modes in PL/SQL Subpro­grams

S.N.
Parts & Descri­ption
1
IN An IN parameter lets you pass a value to the subpro­gram. It is a read-only parameter. Inside the subpro­gram, an IN parameter acts like a constant. It cannot be assigned a value. You can pass a constant, literal, initia­lized variable, or expression as an IN parameter. You can also initialize it to a default value; however, in that case, it is omitted from the subprogram call. It is the default mode of parameter passing. Parameters are passed by reference.
2
OUT An OUT parameter returns a value to the calling program. Inside the subpro­gram, an OUT parameter acts like a variable. You can change its value and reference the value after assigning it. The actual parameter must be variable and it is passed by value.
3
IN OUT An IN OUT parameter passes an initial value to a subprogram and returns an updated value to the caller. It can be assigned a value and its value can be read. The actual parameter corres­ponding to an IN OUT formal parameter must be a variable, not a constant or an expres­sion. Formal parameter must be assigned a value. Actual parameter is passed by value.
 

Packages Code Example

CREATE OR REPLACE PACKAGE roppkg AS
 PROCEDURE ropmall
(pi_city varchar2 default 'Mississauga',
 pi_mall varchar2,
 pi_city_code out varchar2) ;

FUNCTION roppop
(pi_city varchar2 default'Mississauga') 
RETURN NUMBER ;

END;


CREATE OR REPLACE PACKAGE BODY roppkg AS
 PROCEDURE ropmall
(pi_city varchar2 default 'Mississauga',
 pi_mall varchar2,
 pi_city_code out varchar2) 
AS
 l_cnt NUMBER;
 l_cid number;
BEGIN
 SELECT count(1) INTO l_cnt from
 mall a
 WHERE 
 a.mall_name=pi_mall
  ;

 IF l_cnt = 0 
 THEN
   SELECT cid into l_cid
   FROM rop
   WHERE CITY=pi_city; 
 
   INSERT INTO mall VALUES (l_cid, pi_mall);
 END IF;

 COMMIT;

 pi_city_code:=l_cid;
END;


FUNCTION roppop
(pi_city varchar2 default'Mississauga') 
RETURN NUMBER AS
 l_pop NUMBER;
BEGIN
 SELECT population INTO l_pop from
 rop WHERE city=pi_city;
 RETURN l_pop;
END;

END;

Function Example

CREATE or REPLACE FUNCTION roppop
(pi_city varchar2 ) 
RETURN NUMBER AS
 l_pop NUMBER;
BEGIN
 SELECT population INTO l_pop from
 rop WHERE city=pi_city;
 RETURN l_pop;
END;

Procedures Example

CREATE or REPLACE PROCEDURE ropmall
(pi_city varchar2 default 'Mississauga',
 pi_mall varchar2,
 pi_city_code out varchar2) 
AS
 l_cnt NUMBER;
 l_cid number;
BEGIN
 dbms_output.put_line(nvl(pi_city_code,'NULL'));
 
 SELECT count(1) INTO l_cnt from
 mall a
 WHERE 
 a.mall_name=pi_mall
  ;

 IF l_cnt = 0 
 THEN
   SELECT cid into l_cid
   FROM rop
   WHERE CITY=pi_city; 
 
   INSERT INTO mall VALUES (l_cid, pi_mall);
 END IF;

 COMMIT;

 pi_city_code:=l_cid;
END;
 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.