Skip to end of metadata
Go to start of metadata


Overview

In this chapter, declaration of variable and constant will be described.

In addition, initial assignment of value will be mentioned.


Restriction

  • Not Null constraint is not allowed to use in a value declaration clause. 

Example

ORACLE

ALTIBASE HDB

Comments

CREATE OR REPLACE PROCEDURE PROC1(b1 integer)
AS
  b2 CHAR(14) not null := '20120715000000';
BEGIN
   FOR I IN 1 .. B1 LOOP
   INSERT INTO T1 VALUES(I, b2);
   END LOOP;
END;
/

CREATE OR REPLACE PROCEDURE PROC1(b1 integer)
AS
b2 CHAR(14) := '20120715000000';
BEGIN
FOR I IN 1 .. B1 LOOP
INSERT INTO T1 VALUES(I, b2);
END LOOP;
END;
/

Not Null constraint is not allowable in an initialization
clause in the ALTIBASE HDB.

DECLARE
credit PLS_INTEGER RANGE 1000..25000;
debit credit%TYPE;
v_name VARCHAR2(20);
name VARCHAR2(20) NOT NULL := 'JoHn SmItH';
-- If we increase the length of NAME, the other variables become longer also
upper_name name%TYPE := UPPER(name);
lower_name name%TYPE := LOWER(name);
init_name name%TYPE := INITCAP(name);
BEGIN
-- display inherited default values
DBMS_OUTPUT.PUT_LINE('name: ' || name || ' upper_name: ' || upper_name
|| ' lower_name: ' || lower_name || ' init_name: ' || init_name);
-- lower_name := 'jonathan henry smithson'; invalid, character string is too long
-- lower_name := NULL; invalid, NOT NULL CONSTRAINT
-- debit := 50000; invalid, value out of range
END;
/

CREATE OR REPLACE PROCEDURE PROC1 
AS
credit INTEGER;
debit credit%TYPE;
v_name VARCHAR2(20);
name VARCHAR2(20) := 'JoHn SmItH';
-- If we increase the length of NAME, the other variables become longer also
upper_name name%TYPE := UPPER(name);
lower_name name%TYPE := LOWER(name);
init_name name%TYPE := INITCAP(name);
BEGIN
-- display inherited default values
SYSTEM_.PRINTLN('name: ' || name || ' upper_name: ' || upper_name
|| ' lower_name: ' || lower_name || ' init_name: ' || init_name);
-- lower_name := 'jonathan henry smithson'; invalid, character string is too long
-- lower_name := NULL; invalid, NOT NULL CONSTRAINT
-- debit := 50000; invalid, value out of range
END;
/

PL/SQL Datatypes
range

CREATE OR REPLACE PROCEDURE proc1
AS
v1 INTEGER;
r1 t1%ROWTYPE;
BEGIN
INSERT INTO t1 VALUES (3,3,3);
<<s>>
DECLARE
v1 proc1.r1.i1%TYPE;
r1 t1%ROWTYPE;
BEGIN
SELECT i1,i2,i3
INTO s.r1.i1, s.r1.i2, s.r1.i3
FROM t1
WHERE i1 = 1;
INSERT INTO t1 VALUES(s.r1.i1, s.r1.i2, s.r1.i3);
END;
END;
/

CREATE OR REPLACE PROCEDURE proc1
AS
v1 INTEGER;
r1 t1%ROWTYPE;
BEGIN
INSERT INTO t1 VALUES (3,3,3);
<<s>>
DECLARE
v1 proc1.r1.i1%TYPE;
r1 t1%ROWTYPE;
BEGIN
SELECT i1,i2,i3
INTO s.r1.i1, s.r1.i2, s.r1.i3
FROM t1
WHERE i1 = 1;
INSERT INTO t1 VALUES(s.r1.i1, s.r1.i2, s.r1.i3);
END;
END;
/

 



  • No labels