PL/SQl ORACLE

https://www.guru99.com/pl-sql-tutorials.html

#Difference SQL and ORacle

SQL	ORACLE
single query	blok kode,Prosedure,
Execute as a single	Execute as a whole block.
Mainly used to manipulate data	Mainly Create Application

#PL/SQL Block Syntax

#IF dan deklarasi variabel

next

#CASE

CASE (expression)
 WHEN <valuel> THEN action_blockl;
 WHEN <value2> THEN action_block2;
 WHEN <value3> THEN action_block3;
 ELSE action_block_default;
END CASE;

#CEK SPASI DI AWAL DAN AKHIR KALIMAT

DECLARE

MY_NAME VARCHAR2(100) := 'RIKI ALDI PARI';

VTMP VARCHAR2(100);
VTMP2 VARCHAR2(100);
VCECK NUMBER;


BEGIN

VTMP := INSTR(MY_NAME, ' ');
VTMP2 := INSTR(MY_NAME, ' ', LENGTH(MY_NAME)-1);

IF VTMP = 1 OR VTMP2 = LENGTH(MY_NAME) THEN
DBMS_OUTPUT.PUT_LINE('Nama : ' || MY_NAME || ' - Mengandung Spasi di Awal atau di Akhir');
ELSE
DBMS_OUTPUT.PUT_LINE('Nama : ' || MY_NAME || ' - Benar');
END IF;

END;

#LOOP 

#FUNCTION and PROSEDURAL

CONVERSI FUNCTION

TO_CHAR : konversi tipe dalainya ke type car : to_char(123)

TO_DATE : konversi string ktggal ,ssuai format tgl contoh :

      TO_DATE('2015-JAN-15', 'YYYY-MON-DD');

      Output: 1/15/2015

TO_NUMBER :mengubah string mnjadi angka : contoh

     TO_NUMBER('1,234.45','9,999.99') from dual;

    Output: 1234

#LOOP

basic loop ada : loop  end loop;

ada 2 : The '<<OUTER_LOOP>>' and '<<INNER_LOOP>>', and digunakan EXIT when a > b

#FOR LOOP

ada llopp di daalam for

#WHILE  LOOP

#PROSEDURE

exec : adaalah unutk memnagil prosedure

#FUNCTION

- TO_CHAR(123); digunakan unutk konvert data ke type data char

- TO_DATE('2015-JAN-15', 'YYYY-MON-DD'); convrt string to date,

- TO_NUMBER('1,234.45','9,999.99') from dual; : type to number

STRING FUNCTION

- INSTR(text, string, start, occurance)

 - substr('aeroplane',1,7) : aeropla

- UPPER ( text ) : mngubah kecil ke huruf besar

- LOWER(tetx) : besar ke kecil

- ENGTH ( text ) : mencari panjang string

DATE FUNCTION

-TRUNC(sysdate) : ke nilai yg lebih rendaj

-ROUND (date) : membulatkan ke angka terdekat , angka tingi atau rendah

-BETWEEN (sysdate+60, sysdate)  : mengembalikan ke bulan : output 2 , krna 60 adalah 2 bulan

nect : https://www.guru99.com/pl-sql-cursor.html

Share on Google Plus

About riki aldi pari

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.