Skip to main content

PL SQL Cheatsheet

PL/SQL Syntax for reference

Basic PL/SQL block
 DECLARE  
  --variable declarations go here  
 BEGIN  
  --program logic goes here  
 END;  

Functions - Named pl/sql blocks mainly used for computation and not for write operations. They return value to the caller as defined in function.
 CREATE OR REPLACE FUNCTION myFunc(  
  arg1_in IN VARCHAR2,  
  arg2_in VARCHAR2)  
  RETURN BOOLEAN  
 AS  
  RETURN true;  
 END;  

Procedure - Named pl/sql block used for write operations.
 CREATE OR REPLACE PROCEDURE myProc(  
  arg1_in IN VARCHAR2,  
  arg2_in IN VARCHAR2)  
 AS  
  arg3 VARCHAR2(2048);  
  arg4 BOOLEAN;  
 BEGIN  
  --logic  
 END;  

Variable Declarations 
 arg VARCHAR2(2048) := 'myString';  
 arg BOOLEAN := true;  
 arg NUMBER := 10;  

IF ELSE Conditions
 IF (condition) THEN  
  --logic 1  
 ELSIF (condition2) THEN  
  --logic 2  
 ELSE  
  --logic3  
 END IF;  

SWITCH Statements
With selector
 arg1 NUMBER := 0;  
 CASE arg1  
  WHEN 0 THEN  
   --logic;  
  WHEN 1 THEN  
   --logic;  
  WHEN 2 THEN  
   --logic;  
  ELSE  
   --logic;  
 END CASE;  

Without selector - it behaves just like multiple if-else conditions
 CASE  
  WHEN (arg1 = 0) THEN  
   --logic;  
  WHEN (arg1 = 1 ) THEN  
   --logic;  
  ELSE  
   --logic;  
 END CASE;  

WHILE loop
 WHILE (condition)  
 LOOP  
  --logic;  
 END LOOP;  

Exception Handling
 BEGIN  
 EXCEPTION  
 --catches all exceptions  
 WHEN OTHERS THEN  
  --outputs error message  
  dbms_output.put_line(SUBSTR(SQLERRM, 1, 100));  
 END;  

Custom Exception
 AS  
  --custom exception  
  missing_parameter EXCEPTION;  
  pragma exception_init(missing_parameter, -20001);  
 BEGIN  
  --raising a custom exception with error message  
  raise_application_error(-20001,'Missing parameter');  
 END;  

Cursor
 --cursor declaration  
 CURSOR entries  
  IS  
  SELECT *  
  FROM entrytable;  
 BEGIN  
 --opens cursor  
  OPEN entries;  
  LOOP  
 --fetch rows one by one, entry is rowtype of entrytable  
  FETCH entries INTO entry;  
 -- if no rows found then exit  
  EXIT  
  WHEN entries%notfound;  
 END LOOP;  
  CLOSE entries;  

User records - custom user defined records
 --definition of customer record  
 type customer  
 IS  
  record  
  (  
  username VARCHAR2(2048),  
  email VARCHAR2(2048),  
  firstname VARCHAR2(2048),  
  lastname VARCHAR2(2048));  
 --variable of type customer record defined above  
 customer_rec customer;  

Rowtype - variable in pl/sql which have same structure as a table row.
 entry entrytable%rowtype;  

Dynamic Procedure Calling
 proc VARCHAR2(2048);  
 execute_scenario VARCHAR2(2048) := 'myProc';  
 proc := 'BEGIN SCENARIO'|| execute_scenario || ' (:email, :firstname); END;';  
 execute immediate (proc) USING entry.email, entry.firstname;  

Comments

Popular posts from this blog

Guide : Spring Boot with Apache CXF for REST services

In this series of guide, we are going to explore writing REST services with Apache CXF using Spring Boot. The project is build using maven. I assume that you already know how to use maven. Step 1 : Adding dependencies for Spring Boot By default you have to inherit the parent pom of spring boot, but that cannot be followed everytime, so I use an alternative to that. I basically add spring boot pom as dependency so that it brings all the dependencies. <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> <java.version>1.8</java.version> <spring.version>1.4.3.RELEASE</spring.version> <cxf.version>3.1.10</cxf.version> </properties> <dependencies> <dependency> <!-- Alternative to inheriting from parent spring pom --> <groupId>org.springframework.boot&l

Enabling CXF goodies in Spring Boot

In this post we are going to add some of the CXF features to our existing app that we developed in  previous post . These features are : ID Logging Jackson Provider for POJO to JSON conversion Swagger 2 documentation Step 1: Configuration class Create a RestServer class in config package as shown below package org . blog . config ; import com.fasterxml.jackson.jaxrs.json.JacksonJsonProvider ; import org.apache.cxf.feature.LoggingFeature ; import org.apache.cxf.jaxrs.swagger.Swagger2Feature ; import org.springframework.context.annotation.Bean ; import org.springframework.context.annotation.Configuration ; /** * Created by Anand_Rajneesh on 3/23/2017. */ @Configuration public class RestServer { @Bean public JacksonJsonProvider jsonProvider (){ return new JacksonJsonProvider (); } @Bean public LoggingFeature loggingFeature (){ return new LoggingFeature (); } @Bean public Swagger2Featur

Power of declarative syntax of Lambda

Since Java has introduced lambda, writing code has become a breeze especially when it comes to expressing what you want to computer to do rather than how to do it. The following is a question from Project Euler and is solved using lambdas in Java8. Let d( n ) be defined as the sum of proper divisors of n (numbers less than n which divide evenly into n ). If d( a ) = b and d( b ) = a , where a ≠ b , then a and b are an amicable pair and each of a and b are called amicable numbers. For example, the proper divisors of 220 are 1, 2, 4, 5, 10, 11, 20, 22, 44, 55 and 110; therefore d(220) = 284. The proper divisors of 284 are 1, 2, 4, 71 and 142; so d(284) = 220. Evaluate the sum of all the amicable numbers under 10000.  A simple brute force solution is to check every number from 2 to 10000 and if its  an amicable number then add it to sum. Listing it in steps you are basically doing 3 things: Take numbers from 2 to 10000 If number is amicable Add it In Java 7 one will