Basic concepts of SQL PL

--declaring Variables--1. Syntax:--declare <varible-name> <data-type> <default-constant> not changed by default--declare variable name data type constant value--2. For example:--declare x, y int default 0; Defines an x, y variable with a default value of 0--declare myname varchar (ten);--declare z decimal (9,2) default 0.0;--3. Assign values:--method One: Use the SET statement--method Two: Use the values into statement--method Three: Use the SELECT INTO statement--set x=10;--set y= (select SUM (c1) from T1);--values ten into X;--Select SUM (c1) into Y from T1;--4. Reply to global variables--DB2 supports session global variables. It is associated with a specific session, which is global for each stored procedure in this session--A session global variable is declared outside of a stored procedure. --Syntax:--Create variable var_name datatype [default value]--Create variable name data type "Default value"--Create varible myvar interger default 0;--Array--DB2 arrays are supported from 9.5, you can use arrays in stored procedures and applications, but you cannot define tables by using arrays--1. Defining Arrays--syntax: Create type <array-type-name> as <data-type> array[integer-constant]--Create type array type name as data type array[integer constant value]--create type Nar As Integer array[100]--Create type myname as varchar (array[];--declaring an array--Declare <array-name> <array-type-name>--declare MyNumber NAR;--declare Namearr mynames;--declare Namearr mynames;--Assign Value--using the SET statement--using the values into statement--using the SELECT INTO statement--using the Arrar constructor--functions for manipulating arrays--array_delete Deleting an array element--Trim_array Deletes a specified number of elements from the right start--Array_first Returns the first element in the array--Array_last returns the last element in the array--Array_next returns the next element of an array--Unnest converting an array to a table--connecting to a database--Connect to sanple!--Defining Arrays--Create type Intarray As Integer array[10]--create a stored procedure--CREATE PROCEDURE test ()--begin--declare Testarr intarray;--assigned value 1:set testarr=array[1,2,3,4,5,6,7,8,9,10];--Assignment 2:values 1 to testarr[1], values 2 into testarr[2]--Assignment 3:set testarr[1]=1--called--Save the above code as C:\TEST.SQL, and then execute the following command in the DB2 command Window--db2-td!-VF C:\test.sql

Basic concepts of SQL PL