Search This Blog

Thursday, October 31, 2013

Playing with PL record type

The %ROWTYPE attribute provides a record type that represents a row in a database table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable. Variables declared using %ROWTYPE are treated like those declared using a datatype name. You can use the %ROWTYPE attribute in variable declarations as a datatype specifier.

see thoses examples, it will speak to you.

PROCEDURE INSERT_REC
AS
V_EMP_NUMBER NUMBER := 124;
V_EMP_REC EMPLOYEES%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('INSERT_REC');
SELECT * INTO V_EMP_REC FROM EMPLOYEES WHERE EMPLOYEE_ID = V_EMP_NUMBER;

INSERT INTO RETIRED_EMPS
(EMP_NO, FIRST_NAME, LAST_NAME,
EMAIL, PHONE_NUMBER, HIRE_DATE,
JOB, SAL, COMM,
MGR, DEPT_NO)
VALUES
(V_EMP_REC.EMPLOYEE_ID ,V_EMP_REC.FIRST_NAME ,V_EMP_REC.LAST_NAME ,
V_EMP_REC.EMAIL,V_EMP_REC.PHONE_NUMBER ,V_EMP_REC.HIRE_DATE ,
V_EMP_REC.JOB_ID,V_EMP_REC.SALARY ,V_EMP_REC.COMMISSION_PCT ,
V_EMP_REC.MANAGER_ID, V_EMP_REC.DEPARTMENT_ID);

END INSERT_REC;

PROCEDURE INSERT_REC_BULK
AS
V_EMP_NUMBER NUMBER := 124;
V_EMP_REC EMPLOYEES%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('INSERT_REC_BULK');
SELECT * INTO V_EMP_REC FROM EMPLOYEES WHERE EMPLOYEE_ID = V_EMP_NUMBER;
V_EMP_REC.COMMISSION_PCT := 0.1;
V_EMP_REC.EMPLOYEE_ID:= V_EMP_NUMBER*10;
INSERT INTO RETIRED_EMPS VALUES V_EMP_REC;

END INSERT_REC_BULK;

PROCEDURE UPDATE_REC_BULK
AS
V_EMP_NUMBER NUMBER := 1240;
V_EMP_REC RETIRED_EMPS%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('UPDATE_REC_BULK');
SELECT * INTO V_EMP_REC FROM RETIRED_EMPS WHERE EMP_NO = V_EMP_NUMBER;
V_EMP_REC.COMM := 0.9;

UPDATE RETIRED_EMPS SET ROW = V_EMP_REC WHERE  EMP_NO = V_EMP_NUMBER;

END UPDATE_REC_BULK;

PL/SQL Variables Scope

PL/Scope is a compiler-driven tool that collects data about identifiers in PL/SQL source code at program-unit compilation time and makes it available in static data dictionary views. The collected data includes information about identifier types, usages (declaration, definition, reference, call, assigment) and the location of each usage in the source code.

PL/Scope enables the development of powerful and effective PL/Scope source code browsers that increase PL/SQL developer productivity by minimizing time spent browsing and understanding source code. PL/Scope is intended for application developers, and will usually be used in the environment of a development database.

PROCEDURE PROC

AS
V_DESC_SIZE INTEGER(5);
V_PROD_DESCRIPTION VARCHAR2(70) := 'You ';
V_DATE1 DATE DEFAULT SYSDATE;
V_DATE2 V_DATE1%TYPE DEFAULT SYSDATE+360;
V_DATE_DIFF INTEGER;
V_DT_STR DATE := '05-FEB-2013';
V_DT_STR2 DATE := '03-JAN-2013';
V_OUTER VARCHAR2(20) :='Global variable';
BEGIN <<OUTER_LBL>>
DBMS_OUTPUT.PUT_LINE('PROCEDURE PROC');
V_DESC_SIZE := LENGTH(V_PROD_DESCRIPTION);
DBMS_OUTPUT.PUT_LINE('V_DESC_SIZE : '||V_DESC_SIZE);
V_DATE_DIFF := MONTHS_BETWEEN(V_DATE1, V_DATE2);
DBMS_OUTPUT.PUT_LINE('V_DATE_DIFF : '||V_DATE_DIFF );
DBMS_OUTPUT.PUT_LINE('V_DT_STR : '||V_DT_STR );
DBMS_OUTPUT.PUT_LINE('V_DT_STR2 : '||V_DT_STR2 );
    DECLARE 
        V_INNER VARCHAR2(20) := 'Local Var';
        V_OUTER VARCHAR2(20) :='V_OUTER LOCAL var';
    BEGIN <<INNER_LBL>>
        DBMS_OUTPUT.PUT_LINE('V_OUTER : '||V_OUTER );
        DBMS_OUTPUT.PUT_LINE('V_INNER : '||V_INNER );
        DBMS_OUTPUT.PUT_LINE('OUTER_LBL.V_OUTER : '||OUTER_LBL.V_OUTER );
        DBMS_OUTPUT.PUT_LINE('PROC.V_OUTER : '||PROC.V_OUTER );
        V_OUTER :=V_OUTER;
    END;
    DBMS_OUTPUT.PUT_LINE('V_OUTER : '||V_OUTER );
  
    
END PROC;

The out put will be
PROCEDURE PROC
V_DESC_SIZE : 4
V_DATE_DIFF : -12
V_DT_STR : 05-FEB-13
V_DT_STR2 : 03-JAN-13
V_OUTER : V_OUTER LOCAL var
V_INNER : Local Var
OUTER_LBL.V_OUTER : V_OUTER LOCAL var
PROC.V_OUTER : Global variable
V_OUTER : Global variable

Reference for extra reading:
http://docs.oracle.com/cd/B13789_01/appdev.101/b10807/02_funds.htm#i15754

PL/SQL MERGE Statement

The MERGE statement inserts some rows and updates others in a single operation. The decision about whether to update or insert into the target table is based upon a join condition: rows already in the target table that match the join condition are updated; otherwise, a row is inserted using values from a separate subquery.
    
PROCEDURE PROC_MERGE
AS

BEGIN
  DBMS_OUTPUT.PUT_LINE('PROC_MERGE');
  MERGE INTO COPY_EMP C
        USING EMPLOYEES E
        ON(C.EMPLOYEE_ID = C.EMPLOYEE_ID)
    WHEN MATCHED THEN
        UPDATE SET 
            C.FIRST_NAME = E.FIRST_NAME,
            C.LAST_NAME = E.LAST_NAME,
            C.EMAIL = E.EMAIL,
            C.PHONE_NUMBER = E.PHONE_NUMBER,
            C.HIRE_DATE = E.HIRE_DATE,
            C.JOB_ID = E.JOB_ID,
            C.SALARY = E.SALARY,
            C.COMMISSION_PCT = E.COMMISSION_PCT,
            C.MANAGER_ID = E.MANAGER_ID,
            C.DEPARTMENT_ID = E.DEPARTMENT_ID
             
    WHEN NOT MATCHED THEN
        insert values (e.EMPLOYEE_ID,e.FIRST_NAME,e.LAST_NAME,e.EMAIL,e.PHONE_NUMBER,e.HIRE_DATE,e.JOB_ID,e.SALARY,e.COMMISSION_PCT,e.MANAGER_ID,e.DEPARTMENT_ID);
END PROC_MERGE;

Wednesday, October 30, 2013

Associative Arrays, Nested Tables, and Varrays two minutes drill

Associative Arrays


Associative arrays are sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array. The key can be either integer- or character-based. The array value may be of the scalar data type (single value) or the record data type (multiple values).
Because associative arrays are intended for storing temporary data, you cannot use them with SQL statements such as INSERT and SELECT INTO.

The following methods make associative arrays easier to use:
EXISTS
COUNT
FIRST
LAST
PRIOR
NEXT
DELETE

DECLARE
V_EMP_NUMBER NUMBER := 124;
V_EMP_REC EMPLOYEES%ROWTYPE;
BEGIN
SELECT * INTO V_EMP_REC FROM EMPLOYEES WHERE EMPLOYEE_ID = V_EMP_NUMBER;

INSERT INTO RETIRED_EMPS
(EMP_NO, FIRST_NAME, LAST_NAME,
EMAIL, PHONE_NUMBER, HIRE_DATE,
JOB, SAL, COMM,
MGR, DEPT_NO)
VALUES
(V_EMP_REC.EMPLOYEE_ID ,V_EMP_REC.FIRST_NAME ,V_EMP_REC.LAST_NAME ,
V_EMP_REC.EMAIL,V_EMP_REC.PHONE_NUMBER ,V_EMP_REC.HIRE_DATE ,
V_EMP_REC.JOB_ID,V_EMP_REC.SALARY ,V_EMP_REC.COMMISSION_PCT ,
V_EMP_REC.MANAGER_ID, V_EMP_REC.DEPARTMENT_ID);
END;
/
DECLARE
V_EMP_NUMBER NUMBER := 124;
V_EMP_REC EMPLOYEES%ROWTYPE;
BEGIN
SELECT * INTO V_EMP_REC FROM EMPLOYEES WHERE EMPLOYEE_ID = V_EMP_NUMBER;
V_EMP_REC.COMMISSION_PCT := 0.1;
V_EMP_REC.EMPLOYEE_ID:= V_EMP_NUMBER*10;

INSERT INTO RETIRED_EMPS VALUES V_EMP_REC;
END;
/
DECLARE
V_EMP_NUMBER NUMBER := 1240;
V_EMP_REC RETIRED_EMPS%ROWTYPE;

BEGIN<
SELECT * INTO V_EMP_REC FROM RETIRED_EMPS WHERE EMP_NO = V_EMP_NUMBER;
V_EMP_REC.COMM := 0.9;

UPDATE RETIRED_EMPS SET ROW = V_EMP_REC WHERE  EMP_NO = V_EMP_NUMBER;
END;
/

Nested Tables


A nested table holds a set of values. In other words, it is a table within a table. Nested tables are unbounded; that is, the size of the table can increase dynamically. Nested tables are available in both PL/SQL and the database. Within PL/SQL, nested tables are like one-dimensional arrays whose size can increase dynamically.

Varrays



Variable-size arrays, or varrays, are also collections of homogeneous elements that hold a fixed number of elements (although you can change the number of elements at run time). They use sequential numbers as subscripts. You can define equivalent SQL types, thereby allowing varrays to be stored in database tables.

Setting up SyntaxHighlighter on Google Blogger

  1. First, take backup of your blogger template.
  2. After that open your blogger template (In Edit HTML mode) & copy the all css given in this link before closing of b:skin tag.
    .dp-highlighter
    {
     font-family: "Consolas", "Monaco", "Courier New", Courier, monospace;
     font-size: 12px;
     background-color: #E7E5DC;
     width: 99%;
     overflow: auto;
     margin: 18px 0 18px 0 !important;
     padding-top: 1px; /* adds a little border on top when controls are hidden */
    }
    
    /* clear styles */
    .dp-highlighter ol,
    .dp-highlighter ol li,
    .dp-highlighter ol li span 
    {
     margin: 0;
     padding: 0;
     border: none;
    }
    
    .dp-highlighter a,
    .dp-highlighter a:hover
    {
     background: none;
     border: none;
     padding: 0;
     margin: 0;
    }
    
    .dp-highlighter .bar
    {
     padding-left: 45px;
    }
    
    .dp-highlighter.collapsed .bar,
    .dp-highlighter.nogutter .bar
    {
     padding-left: 0px;
    }
    
    .dp-highlighter ol
    {
     list-style: decimal; /* for ie */
     background-color: #fff;
     margin: 0px 0px 1px 45px !important; /* 1px bottom margin seems to fix occasional Firefox scrolling */
     padding: 0px;
     color: #5C5C5C;
    }
    
    .dp-highlighter.nogutter ol,
    .dp-highlighter.nogutter ol li
    {
     list-style: none !important;
     margin-left: 0px !important;
    }
    
    .dp-highlighter ol li,
    .dp-highlighter .columns div
    {
     list-style: decimal-leading-zero; /* better look for others, override cascade from OL */
     list-style-position: outside !important;
     border-left: 3px solid #6CE26C;
     background-color: #F8F8F8;
     color: #5C5C5C;
     padding: 0 3px 0 10px !important;
     margin: 0 !important;
     line-height: 14px;
    }
    
    .dp-highlighter.nogutter ol li,
    .dp-highlighter.nogutter .columns div
    {
     border: 0;
    }
    
    .dp-highlighter .columns
    {
     background-color: #F8F8F8;
     color: gray;
     overflow: hidden;
     width: 100%;
    }
    
    .dp-highlighter .columns div
    {
     padding-bottom: 5px;
    }
    
    .dp-highlighter ol li.alt
    {
     background-color: #FFF;
     color: inherit;
    }
    
    .dp-highlighter ol li span
    {
     color: black;
     background-color: inherit;
    }
    
    /* Adjust some properties when collapsed */
    
    .dp-highlighter.collapsed ol
    {
     margin: 0px;
    }
    
    .dp-highlighter.collapsed ol li
    {
     display: none;
    }
    
    /* Additional modifications when in print-view */
    
    .dp-highlighter.printing
    {
     border: none;
    }
    
    .dp-highlighter.printing .tools
    {
     display: none !important;
    }
    
    .dp-highlighter.printing li
    {
     display: list-item !important;
    }
    
    /* Styles for the tools */
    
    .dp-highlighter .tools
    {
     padding: 3px 8px 3px 10px;
     font: 9px Verdana, Geneva, Arial, Helvetica, sans-serif;
     color: silver;
     background-color: #f8f8f8;
     padding-bottom: 10px;
     border-left: 3px solid #6CE26C;
    }
    
    .dp-highlighter.nogutter .tools
    {
     border-left: 0;
    }
    
    .dp-highlighter.collapsed .tools
    {
     border-bottom: 0;
    }
    
    .dp-highlighter .tools a
    {
     font-size: 9px;
     color: #a0a0a0;
     background-color: inherit;
     text-decoration: none;
     margin-right: 10px;
    }
    
    .dp-highlighter .tools a:hover
    {
     color: red;
     background-color: inherit;
     text-decoration: underline;
    }
    
    /* About dialog styles */
    
    .dp-about { background-color: #fff; color: #333; margin: 0px; padding: 0px; }
    .dp-about table { width: 100%; height: 100%; font-size: 11px; font-family: Tahoma, Verdana, Arial, sans-serif !important; }
    .dp-about td { padding: 10px; vertical-align: top; }
    .dp-about .copy { border-bottom: 1px solid #ACA899; height: 95%; }
    .dp-about .title { color: red; background-color: inherit; font-weight: bold; }
    .dp-about .para { margin: 0 0 4px 0; }
    .dp-about .footer { background-color: #ECEADB; color: #333; border-top: 1px solid #fff; text-align: right; }
    .dp-about .close { font-size: 11px; font-family: Tahoma, Verdana, Arial, sans-serif !important; background-color: #ECEADB; color: #333; width: 60px; height: 22px; }
    
    /* Language specific styles */
    
    .dp-highlighter .comment, .dp-highlighter .comments { color: #008200; background-color: inherit; }
    .dp-highlighter .string { color: blue; background-color: inherit; }
    .dp-highlighter .keyword { color: #069; font-weight: bold; background-color: inherit; }
    .dp-highlighter .preprocessor { color: gray; background-color: inherit; }
    
  3. Paste the followig code before closing of head tag.
    
    
    
    
    
    
    
    
    
    
    
    
    
    
  4. Paste the following code before closing of body tag.
    <pre class="js" name="code">
    <script language="javascript">
    dp.SyntaxHighlighter.BloggerMode();
    dp.SyntaxHighlighter.HighlightAll('code');
    </script>
    </pre>
    
  5. Save Blogger Template.
  6. Now syntax highlighting is ready to use you can use it as following.
    ...Your html-escaped code goes here...
    
        echo "I like PHP";
    
  7. You can Escape your code here.
  8. Here is list of supported language for class attribute.
LanguageAliases
C++cppcc++
C#c#c-sharpcsharp
CSScss
Delphidelphipascal
Javajava
Java Scriptjsjscriptjavascript
PHPphp
Pythonpypython
Rubyrbrubyrailsror
Sqlsql
VBvbvb.net
XML/HTMLxmlhtmlxhtmlxslt