\documentclass[10pt,a4paper]{article}

% Packages
\usepackage{fancyhdr}           % For header and footer
\usepackage{multicol}           % Allows multicols in tables
\usepackage{tabularx}           % Intelligent column widths
\usepackage{tabulary}           % Used in header and footer
\usepackage{hhline}             % Border under tables
\usepackage{graphicx}           % For images
\usepackage{xcolor}             % For hex colours
%\usepackage[utf8x]{inputenc}    % For unicode character support
\usepackage[T1]{fontenc}        % Without this we get weird character replacements
\usepackage{colortbl}           % For coloured tables
\usepackage{setspace}           % For line height
\usepackage{lastpage}           % Needed for total page number
\usepackage{seqsplit}           % Splits long words.
%\usepackage{opensans}          % Can't make this work so far. Shame. Would be lovely.
\usepackage[normalem]{ulem}     % For underlining links
% Most of the following are not required for the majority
% of cheat sheets but are needed for some symbol support.
\usepackage{amsmath}            % Symbols
\usepackage{MnSymbol}           % Symbols
\usepackage{wasysym}            % Symbols
%\usepackage[english,german,french,spanish,italian]{babel}              % Languages

% Document Info
\author{juliosueiras}
\pdfinfo{
  /Title (plsql.pdf)
  /Creator (Cheatography)
  /Author (juliosueiras)
  /Subject (PLSQL Cheat Sheet)
}

% Lengths and widths
\addtolength{\textwidth}{6cm}
\addtolength{\textheight}{-1cm}
\addtolength{\hoffset}{-3cm}
\addtolength{\voffset}{-2cm}
\setlength{\tabcolsep}{0.2cm} % Space between columns
\setlength{\headsep}{-12pt} % Reduce space between header and content
\setlength{\headheight}{85pt} % If less, LaTeX automatically increases it
\renewcommand{\footrulewidth}{0pt} % Remove footer line
\renewcommand{\headrulewidth}{0pt} % Remove header line
\renewcommand{\seqinsert}{\ifmmode\allowbreak\else\-\fi} % Hyphens in seqsplit
% This two commands together give roughly
% the right line height in the tables
\renewcommand{\arraystretch}{1.3}
\onehalfspacing

% Commands
\newcommand{\SetRowColor}[1]{\noalign{\gdef\RowColorName{#1}}\rowcolor{\RowColorName}} % Shortcut for row colour
\newcommand{\mymulticolumn}[3]{\multicolumn{#1}{>{\columncolor{\RowColorName}}#2}{#3}} % For coloured multi-cols
\newcolumntype{x}[1]{>{\raggedright}p{#1}} % New column types for ragged-right paragraph columns
\newcommand{\tn}{\tabularnewline} % Required as custom column type in use

% Font and Colours
\definecolor{HeadBackground}{HTML}{333333}
\definecolor{FootBackground}{HTML}{666666}
\definecolor{TextColor}{HTML}{333333}
\definecolor{DarkBackground}{HTML}{A3A3A3}
\definecolor{LightBackground}{HTML}{F3F3F3}
\renewcommand{\familydefault}{\sfdefault}
\color{TextColor}

% Header and Footer
\pagestyle{fancy}
\fancyhead{} % Set header to blank
\fancyfoot{} % Set footer to blank
\fancyhead[L]{
\noindent
\begin{multicols}{3}
\begin{tabulary}{5.8cm}{C}
    \SetRowColor{DarkBackground}
    \vspace{-7pt}
    {\parbox{\dimexpr\textwidth-2\fboxsep\relax}{\noindent
        \hspace*{-6pt}\includegraphics[width=5.8cm]{/web/www.cheatography.com/public/images/cheatography_logo.pdf}}
    }
\end{tabulary}
\columnbreak
\begin{tabulary}{11cm}{L}
    \vspace{-2pt}\large{\bf{\textcolor{DarkBackground}{\textrm{PLSQL Cheat Sheet}}}} \\
    \normalsize{by \textcolor{DarkBackground}{juliosueiras} via \textcolor{DarkBackground}{\uline{cheatography.com/23055/cs/6461/}}}
\end{tabulary}
\end{multicols}}

\fancyfoot[L]{ \footnotesize
\noindent
\begin{multicols}{3}
\begin{tabulary}{5.8cm}{LL}
  \SetRowColor{FootBackground}
  \mymulticolumn{2}{p{5.377cm}}{\bf\textcolor{white}{Cheatographer}}  \\
  \vspace{-2pt}juliosueiras \\
  \uline{cheatography.com/juliosueiras} \\
  \end{tabulary}
\vfill
\columnbreak
\begin{tabulary}{5.8cm}{L}
  \SetRowColor{FootBackground}
  \mymulticolumn{1}{p{5.377cm}}{\bf\textcolor{white}{Cheat Sheet}}  \\
   \vspace{-2pt}Published 17th December, 2015.\\
   Updated 12th May, 2016.\\
   Page {\thepage} of \pageref{LastPage}.
\end{tabulary}
\vfill
\columnbreak
\begin{tabulary}{5.8cm}{L}
  \SetRowColor{FootBackground}
  \mymulticolumn{1}{p{5.377cm}}{\bf\textcolor{white}{Sponsor}}  \\
  \SetRowColor{white}
  \vspace{-5pt}
  %\includegraphics[width=48px,height=48px]{dave.jpeg}
  Measure your website readability!\\
  www.readability-score.com
\end{tabulary}
\end{multicols}}




\begin{document}
\raggedright
\raggedcolumns

% Set font size to small. Switch to any value
% from this page to resize cheat sheet text:
% www.emerson.emory.edu/services/latex/latex_169.html
\footnotesize % Small font.

\begin{multicols*}{4}

\begin{tabularx}{3.833cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{3.833cm}}{\bf\textcolor{white}{Function}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{3.833cm}}{CREATE OR REPLACE FUNCTION function\_name \newline (parameter\_1 data\_type, \newline Parameter\_2 data\_type) \newline RETURN data\_type \newline \{ IS | AS \} \newline {[}declaration\_section{]} \newline BEGIN \newline executable\_section \newline {[}EXCEPTION \newline exception\_section{]} \newline END {[}function\_name{]};} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{3.833cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{3.833cm}}{\bf\textcolor{white}{Procedures}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{3.833cm}}{Create {[} or REPLACE {]} PROCEDURE procedure\_name \newline ( \newline parameter\_name\_1 data\_type, \newline parameter\_name\_2 data\_type \newline ) \newline \{ IS | AS \} \newline pl\_sql\_block \newline Parameter \newline By position \newline By name} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{3.833cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{3.833cm}}{\bf\textcolor{white}{Packages}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{3.833cm}}{CREATE PACKAGE package\_name \{ IS | AS \} \newline procedure\_or\_function\_specification\_1; \newline procedure\_or\_function\_specification\_2; \newline END {[}package\_name{]}; \newline Package body \newline CREATE PACKAGE BODY package\_name \{ IS | AS \} \newline procedure\_or\_function\_body\_1; \newline procedure\_or\_function\_body\_2; \newline END {[}package\_name{]};} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{3.833cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{3.833cm}}{\bf\textcolor{white}{Bind variable}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{3.833cm}}{Need to specify type} \tn 
% Row Count 1 (+ 1)
% Row 1
\SetRowColor{white}
\mymulticolumn{1}{x{3.833cm}}{Need to wrap around quote when assign string value} \tn 
% Row Count 2 (+ 1)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{3.833cm}}{No need quote when reference the variable} \tn 
% Row Count 3 (+ 1)
% Row 3
\SetRowColor{white}
\mymulticolumn{1}{x{3.833cm}}{Value can only be assigned in a PL, via exec or Begin / End block} \tn 
% Row Count 5 (+ 2)
% Row 4
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{3.833cm}}{Use PRINT to list out bind variable} \tn 
% Row Count 6 (+ 1)
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{3.833cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{3.833cm}}{\bf\textcolor{white}{Conditional and Loops}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{3.833cm}}{Declare and use of variable \newline \%TYPE \%ROWTYPE \newline VARCHAR2 NUMBER DATE \newline  \newline Assignment operator := \newline  \newline Nested block variable scope \newline DECLARE \newline myvar number; \newline BEGIN \newline myvar:=1; \newline dbms\_output.put\_line(myvar); \newline DECLARE \newline myvar number; \newline BEGIN \newline myvar:=2; \newline dbms\_output.put\_line(myvar); \newline END; \newline dbms\_output.put\_line(myvar); \newline END; \newline  \newline IF THEN ELSE END IF \newline  \newline DECLARE \newline v\_number NUMBER; \newline BEGIN \newline IF v\_number\textless{}=0 THEN \newline dbms\_output.put\_line('it is less than 0'); \newline ELSIF v\_number\textgreater{}=0 THEN \newline dbms\_output.put\_line('it is greater than 0'); \newline ELSE \newline dbms\_output.put\_line('not either of the case'); \newline END IF; \newline END; \newline  \newline Loops \newline FOR IN .. LOOP \newline \{statements\}; \newline END LOOP; \newline WHILE condition \newline LOOP \newline \{statements\}; \newline END LOOP; \newline LOOP \newline \{statemens\}; \newline EXIT WHEN condition; \newline CONTINUE WHEN condition; \newline END LOOP; \newline  \newline Loops \newline DECLARE \newline i NUMBER :=10; \newline BEGIN \newline FOR i IN 1..5 LOOP \newline dbms\_output.put\_line(i); \newline END LOOP; \newline dbms\_output.put\_line(i); \newline END; \newline CASE – Simple Case \newline CASE expression \newline WHEN value\_1 THEN \newline .. \newline WHEN value\_2 THEN \newline ELSE \newline END CASE; \newline CASE – Searched Case \newline WHEN boolean\_expression THEN \newline ELSE \newline END CASE;} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{3.833cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{3.833cm}}{\bf\textcolor{white}{Function vs Procedures}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{3.833cm}}{Function must return a value. Procedure can not return a value} \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
\mymulticolumn{1}{x{3.833cm}}{Function and procedure can both return data in OUT and IN OUT parameters} \tn 
% Row Count 4 (+ 2)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{3.833cm}}{Function can be called from SQL, but not for procedure} \tn 
% Row Count 6 (+ 2)
% Row 3
\SetRowColor{white}
\mymulticolumn{1}{x{3.833cm}}{Can not perform a DML DDL within function, while allowed in procedure} \tn 
% Row Count 8 (+ 2)
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{3.833cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{3.833cm}}{\bf\textcolor{white}{Trigger}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{3.833cm}}{CREATE {[}OR REPLACE{]} TRIGGER trigger\_name \newline BEFORE | AFTER \newline {[}INSERT, UPDATE, DELETE {[}COLUMN NAME..{]} \newline ON table\_name \newline Referencing {[} OLD AS OLD | NEW AS NEW {]} \newline FOR EACH ROW | FOR EACH STATEMENT {[} WHEN Condition {]} \newline DECLARE \newline {[}declaration\_section{]} \newline BEGIN \newline {[}executable\_section{]} \newline EXCEPTION \newline {[}exception\_section{]} \newline END;} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{3.833cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{3.833cm}}{\bf\textcolor{white}{Substitution variable}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{3.833cm}}{No need to specify type, as it is always character type} \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
\mymulticolumn{1}{x{3.833cm}}{No need to wrap around quote when assign value} \tn 
% Row Count 3 (+ 1)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{3.833cm}}{Need quote when reference the variable} \tn 
% Row Count 4 (+ 1)
% Row 3
\SetRowColor{white}
\mymulticolumn{1}{x{3.833cm}}{ACCEPT implicitly defined a substitution type variable} \tn 
% Row Count 6 (+ 2)
% Row 4
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{3.833cm}}{Use DEFINE to list out substitution variable} \tn 
% Row Count 7 (+ 1)
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{3.833cm}{p{0.3433 cm} x{3.0897 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{3.833cm}}{\bf\textcolor{white}{Procedures Parts}}  \tn
% Row 0
\SetRowColor{LightBackground}
S.N. & Parts \& Description \tn 
% Row Count 1 (+ 1)
% Row 1
\SetRowColor{white}
1 & {\bf{Declarative Part}} It is an optional part. However, the declarative part for a subprogram does not start with the DECLARE keyword. It contains declarations of types, cursors, constants, variables, exceptions, and nested subprograms. These items are local to the subprogram and cease to exist when the subprogram completes execution. \tn 
% Row Count 11 (+ 10)
% Row 2
\SetRowColor{LightBackground}
2 & {\bf{Executable Part}} This is a mandatory part and contains statements that perform the designated action. \tn 
% Row Count 14 (+ 3)
% Row 3
\SetRowColor{white}
3 & {\bf{Exception-handling}} This is again an optional part. It contains the code that handles run-time errors. \tn 
% Row Count 17 (+ 3)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{3.833cm}{p{0.3433 cm} x{3.0897 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{3.833cm}}{\bf\textcolor{white}{Parameter Modes in PL/SQL Subprograms}}  \tn
% Row 0
\SetRowColor{LightBackground}
S.N. & Parts \& Description \tn 
% Row Count 1 (+ 1)
% Row 1
\SetRowColor{white}
1 & {\bf{IN}} An IN parameter lets you pass a value to the subprogram. It is a read-only parameter. Inside the subprogram, an IN parameter acts like a constant. It cannot be assigned a value. You can pass a constant, literal, initialized 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. \tn 
% Row Count 14 (+ 13)
% Row 2
\SetRowColor{LightBackground}
2 & {\bf{OUT}} An OUT parameter returns a value to the calling program. Inside the subprogram, 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. \tn 
% Row Count 22 (+ 8)
% Row 3
\SetRowColor{white}
3 & {\bf{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 corresponding to an IN OUT formal parameter must be a variable, not a constant or an expression. Formal parameter must be assigned a value. Actual parameter is passed by value. \tn 
% Row Count 33 (+ 11)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{3.833cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{3.833cm}}{\bf\textcolor{white}{Packages Code Example}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{3.833cm}}{CREATE OR REPLACE PACKAGE roppkg AS \newline  PROCEDURE ropmall \newline (pi\_city varchar2 default 'Mississauga', \newline  pi\_mall varchar2, \newline  pi\_city\_code out varchar2) ; \newline  \newline FUNCTION roppop \newline (pi\_city varchar2 default'Mississauga')  \newline RETURN NUMBER ; \newline  \newline END; \newline  \newline  \newline CREATE OR REPLACE PACKAGE BODY roppkg AS \newline  PROCEDURE ropmall \newline (pi\_city varchar2 default 'Mississauga', \newline  pi\_mall varchar2, \newline  pi\_city\_code out varchar2)  \newline AS \newline  l\_cnt NUMBER; \newline  l\_cid number; \newline BEGIN \newline  SELECT count(1) INTO l\_cnt from \newline  mall a \newline  WHERE  \newline  a.mall\_name=pi\_mall \newline   ; \newline  \newline  IF l\_cnt = 0  \newline  THEN \newline    SELECT cid into l\_cid \newline    FROM rop \newline    WHERE CITY=pi\_city;  \newline   \newline    INSERT INTO mall VALUES (l\_cid, pi\_mall); \newline  END IF; \newline  \newline  COMMIT; \newline  \newline  pi\_city\_code:=l\_cid; \newline END; \newline  \newline  \newline FUNCTION roppop \newline (pi\_city varchar2 default'Mississauga')  \newline RETURN NUMBER AS \newline  l\_pop NUMBER; \newline BEGIN \newline  SELECT population INTO l\_pop from \newline  rop WHERE city=pi\_city; \newline  RETURN l\_pop; \newline END; \newline  \newline END;} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{3.833cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{3.833cm}}{\bf\textcolor{white}{Function Example}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{3.833cm}}{CREATE or REPLACE FUNCTION roppop \newline (pi\_city varchar2 )  \newline RETURN NUMBER AS \newline  l\_pop NUMBER; \newline BEGIN \newline  SELECT population INTO l\_pop from \newline  rop WHERE city=pi\_city; \newline  RETURN l\_pop; \newline END;} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{3.833cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{3.833cm}}{\bf\textcolor{white}{Procedures Example}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{3.833cm}}{CREATE or REPLACE PROCEDURE ropmall \newline (pi\_city varchar2 default 'Mississauga', \newline  pi\_mall varchar2, \newline  pi\_city\_code out varchar2)  \newline AS \newline  l\_cnt NUMBER; \newline  l\_cid number; \newline BEGIN \newline  \seqsplit{dbms\_output.put\_line(nvl(pi\_city\_code},'NULL')); \newline   \newline  SELECT count(1) INTO l\_cnt from \newline  mall a \newline  WHERE  \newline  a.mall\_name=pi\_mall \newline   ; \newline  \newline  IF l\_cnt = 0  \newline  THEN \newline    SELECT cid into l\_cid \newline    FROM rop \newline    WHERE CITY=pi\_city;  \newline   \newline    INSERT INTO mall VALUES (l\_cid, pi\_mall); \newline  END IF; \newline  \newline  COMMIT; \newline  \newline  pi\_city\_code:=l\_cid; \newline END;} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}


% That's all folks
\end{multicols*}

\end{document}