/****************************************************************/ /* Document : PL/SQL examples */ /* Doc. Versie : 30 */ /* File : plsql.txt */ /* Date : 11-06-2005 */ /* Content : just a series of handy DBA pl-sql examples */ /* Compiled by : Albert */ /****************************************************************/ ================================= 1. CHARACTER FUNCTIONS: ================================= 1.1 Basic character functions: =============================== A character function is a function that takes one or more character values as parameters and returns either a character value or a number value. Here some functions along with examples. ASCII() AND CHR(): ------------------ ASCII() Returns the ASCII code of a character. select ASCII('b') from dual; ASCII('B') ---------- 98 The "inverse" function, chr(), is very usefull, like in this example: translate(l_output , chr(13)||chr(10), 'XY' ) to have carriage returns (chr(13)) turned into X (or whatever of course) and linefeeds (chr(10)=newline) turned into Y (or whatever) CONCAT: ------- Concatenates two strings into one. SELECT ename||', who is the '||concat(job,' for our company') as "Name and role" FROM emp; Name and role --------------------------------------------- SMITH, who is the CLERK for our company ALLEN, who is the SALESMAN for our company WARD, who is the SALESMAN for our company etc.. CONCAT ('abc', 'defg') ==> 'abcdefg' CONCAT (NULL, 'def') ==> 'def' INITCAP: -------- Sets the first letter of each word to uppercase. All other letters are set to lowercase. INITCAP ('this is lower') ==> 'This Is Lower' INITCAP ('wHatISthis_MESS?') ==> 'Whatisthis_Mess?' INSTR: ------ The INSTR function searches a string to find a match for the substring and, if found, returns the position, in the source string, of the first character of that substring. If there is no match, then INSTR returns 0. Use INSTR like INSTR(string1,string2,start_position,nth_appearance) INSTR ('bug-or-tv-character?archie', 'archie') ==> 21 INSTR ('bug-or-tv-character?archie', 'ar', 14) ==> 21 INSTR ('bug-or-tv-character?archie', 'archie', 1, 2) ==> 0 LENGTH: ------- Returns the length of a string. LENGTH (NULL) ==> NULL LENGTH ('') ==> NULL -- Same as a NULL string. LENGTH ('abcd') ==> 4 LENGTH ('abcd ') ==> 5 LOWER: ------ Converts all letters to lowercase. LOWER ('BIG FAT LETTERS') ==> 'big fat letters' LOWER ('BIG fat LETters') ==> 'big fat letters' LPAD: ----- Pads a string on the left with the specified characters. LPAD ('55', 10, '0') ==> '0000000055' LPAD ('HITOP TIES', 45, 'sell!') ==> 'sell!sell!sell!sell!sell!sell!sell!HITOP TIES' SQL> select ename, lpad(ename,20,'-') from emp; ENAME LPAD(ENAME,20,'-') ---------- --------------------------------------- SMITH ---------------SMITH ALLEN ---------------ALLEN WARD ----------------WARD JONES ---------------JONES MARTIN --------------MARTIN Note that the middle number specifies the total length. LTRIM: ----- Trims the left side of a string of all specified characters. LTRIM (' Way Out in Right Field') ==> 'Way Out in Right Field' my_string := '123123123LotsaLuck123'; LTRIM (my_string, '123') ==> 'LotsaLuck123' my_string := '70756234LotsaLuck'; -- pay attention to this one ! LTRIM (my_string, '0987612345') ==> 'LotsaLuck' LTRIM ('abcabcccccI LOVE CHILI', 'abc') ==> 'I LOVE CHILI' You can also use it in DML queries, like INSERT INTO IOB_KITAP_STAGING_HND (Volgnummer,RecordId,DatumTijd,Uitgevernummer,Automaatnummer,Afnemersupervisornummer, Pasvolgnummersupervisor,Landcodepas,Landcodeautomaat,Systeemcode, Tanknummer15,Produktnummer15,Litertotaal15, Tanknummer26,Produktnummer26,Litertotaal26, Tanknummer37,Produktnummer37,Litertotaal37, Tanknummer48,Produktnummer48,Litertotaal48) SELECT Volgnummer, RecordId, to_date(DatumTijd,'DD-MM-YYYY;HH24:MI:SS'), Uitgevernummer, Automaatnummer, Afnemersupervisornummer, Pasvolgnummersupervisor, Landcodepas, Landcodeautomaat, Systeemcode, Tanknummer15, Produktnummer15, to_number(LTRIM(Litertotaal15,'-')), Tanknummer26, Produktnummer26, to_number(LTRIM(Litertotaal26,'-')), Tanknummer37, Produktnummer37, to_number(LTRIM(Litertotaal37,'-')), Tanknummer48, Produktnummer48, to_number(LTRIM(Litertotaal48,'-')) FROM IOB_KITAP_IMPORT_HND WHERE AUTOMAATNUMMER IS NOT NULL; REPLACE: -------- Replaces a character sequence in a string with a different set of characters. Use as REPLACE (string1 IN VARCHAR2, match_string IN VARCHAR2 [, replace_string IN VARCHAR2]) REPLACE ('CAT CALL','C','K') ==> 'KAT KALL' REPLACE ('CAT CALL', 'C') ==> 'AT ALL' REPLACE (INITCAP ('ALMOST_UNREADABLE_VAR_NAME'), '_', NULL) ==> 'AlmostUnreadableVarName' You can also use it in DML queries like UPDATE CI_MD_CTL_L SET DESCR=REPLACE(DESCR,'''',CHR(7)); RPAD: ----- Pads a string on the right with the specified characters. Similar to LPAD. RPAD ('55', 10, '0') ==> '5500000000' RPAD ('-', 60, '-')==>'------------------------------------------------------------' RPAD (null, 1, '0') RTRIM: ------ Trims the right side of a string of all specified characters. Similar to LTRIM. RTRIM (`Way Out in Right Field ')==> 'Way Out in Right Field' my_string := 'Sound effects: BAM!ARGH!BAM!HAM'; RTRIM (my_string, 'BAM! ARGH!') ==> 'Sound effects:' SOUNDEX: -------- Returns the "soundex" of a string. SUBSTR: ------ Returns the specified portion of a string. SUBSTR ('now_or_never', 0, 3) ==> 'now' Can also be used in DML queries like: INSERT INTO IOB_COLOR_STAGING SELECT substr(importrecord,1,3), substr(importrecord,4,8), substr(importrecord,32,8) FROM IOB_COLOR_IMPORT WHERE substr(importrecord,1,3) IN ('101','104') AND importrecord IS NOT NULL; select FLD_NAME from CI_MD_FLD_L where substr(FLD_NAME,1,1)='''' TRANSLATE: ---------- Translates single characters in a string to different characters. TRANSLATE ('abcd', 'ab', '12') ==> '12cd' TRANSLATE ('12345', '15', 'xx') ==> 'x234x' TRANSLATE ('grumpy old possum', 'uot', '%$*') ==> 'gr%mpy $ld p$ss%m' TRANSLATE ('my language needs the letter e', 'egms', 'X') ==> 'y lanuaX nXXd thX lXttXr X'; TRANSLATE ('please go away', 'a', NULL) ==> NULL declare x varchar2(64); y varchar2(64); begin x:='hallo klote zeg'; y:=translate(x,' ','$'); dbms_output.put_line(x); dbms_output.put_line(y); end; / hallo klote zeg hallo$klote$$$zeg PL/SQL procedure successfully completed. declare x varchar2(64); y varchar2(64); begin x:='hallo klote zeg'; y:=replace(translate(x,' ','$'),'$',' '); dbms_output.put_line(x); dbms_output.put_line(y); end; / hallo klote zeg hallo klote zeg PL/SQL procedure successfully completed. declare x varchar2(64); y varchar2(64); z varchar2(64); begin x:='hallo klote zeg'; y:=translate(x,' ','$'); z:=replace(translate(x,' ','$'),'$'); dbms_output.put_line(x); dbms_output.put_line(y); dbms_output.put_line(z); end; / hallo klote zeg hallo$klote$$$zeg halloklotezeg PL/SQL procedure successfully completed. UPPER: ------ Converts all letters in the string to uppercase. UPPER ('short little letters no more') ==> 'SHORT LITTLE LETTERS NO MORE' UPPER ('123abc') ==> '123ABC' 1.2 Other functions: ==================== nvl function: ------------- Substitutes a value for null in a column Example: 9:55 AM 2/16/2008 SELECT empno, ename, nvl(mgr,0) as MGR FROM emp; DECODE function: ---------------- Example: SELECT ename ||' does the ' || decode(JOB, 'ANALYST', 'analyzing', 'CLERK', 'filing', 'goofing off') FROM emp; ARITHMETIC functions: --------------------- Some arithmetric functions abs(x) round(x,y) ceil(x) floor(x) mod(x,y) sign(x) sqrt(x) trunc(x,y) vsize(x) trunc(123.232, 2)= 123.23 mod(10,2)=0 mod(55,4)=3 ====================== 2. DATE functions: ===================== 2.1 Over NLS Settings: ====================== Bij Server: 1. De database characterset wordt gespecificeerd bij CREATE DATABASE, maar: 2. De Sever kan wel meerdere locale in runtime laden uit files gespecificeerd in $ export ORA_NLSxx=$ORACLE_HOME/ocommon/nls/admin/data 3. Set de default NLS init.ora parameters t.b.v. de USER SESSIONS. The database has a set of session-independent NLS parameters that are specified when the database is created. Two of the parameters specify the database character set and the national character set, that is an alternate Unicode character set that can be specified for NCHAR, NVARCHAR2, and NCLOB data. The parameters specify the character set that is used to store text data in the database. Other parameters, like language and territory, are used to evaluate check constraints. If the client session and the database server specify different character sets, then the Oracle9i database converts character set strings automatically. From a globalization support perspective, all applications are considered to be clients, even if they run on the same physical machine as the Oracle instance. For example, when SQL*Plus is started by the UNIX user who owns the Oracle software from the Oracle home in which the RDBMS software is installed, and SQL*Plus connects to the database through an adapter by specifying the ORACLE_SID parameter, SQL*Plus is considered a client. Its behavior is ruled by client-side NLS parameters. client: 1. client heeft lokaal een NLS environment setting 2. client connect naar database, een session wordt gevormd, en de NLS enviroment wordt gemaakt aan de hAND van de NLS init.ora parameters. Is bij de clent de NLS_LANG environment variable gezet, dan communiceerd de client dat naar de server session. Hierdoor zijn beide hetzelfde. Is er geen NLS_LANG, dan gelden de init.ora NLS parameters voor de server session 3. De session NLS kan worden verANDert via ALTER SESSION. Dit heeft alleen effect op de PL/SQL en SQL statements executed op de server init.ora parameters bij server : invloed op sessions op server environment variables bij client : locale bij client, overrides session alter session statement : verANDert de session, overides init.ora expliciet in SQL statement : overides alles Voorbeeld van override: in init.ora: NLS_SORT=ENGLISH bij client: ALTER SESSION SET NLS_SORT=FRENCH; ALTER SESSION SET nls_date_format = 'dd/mm/yy' ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY' ALTER SESSION SET NLS_LANGUAGE='ENGLISH'; priority: 1. expliciet in SQL 2. ALTER SESSION 3. environment variable 4. init.ora NLS parameters, te zetten via: NLS_CALENDAR init.ora, env, alter session NLS_COMP init.ora, env, alter session NLS_CREDIT - env - NLS_CURRENCY init.ora, env, alter session NLS_DATE_FORMAT init.ora, env, alter session NLS_DATE_LANGUAGE init.ora, env, alter session NLS_DEBIT - env - NLS_ISO_CURRENCY init.ora, env, alter session NLS_LANG - env - NLS_LANGUAGE init.ora, - , alter session NLS_LIST_SEPERATOR - env - NLS_MONETARY_CHARACTERS - env - NLS_NCHAR - env - NLS_NUMMERIC_CHARACTERS init.ora, env, alter session NLS_SORT init.ora, env, alter session NLS_TERRITORY init.ora, - , alter session NLS_DUAL_CURRENCY init.ora, env, alter session SELECT sysdate FROM dual; 15-MAR-01 2.2 Change Date format: ======================= ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; ALTER SESSION SET NLS_DATE_FORMAT='dd/mm/yyyy'; ALTER SESSION SET NLS_DATE_FORMAT = 'dd/mm/yy' ALTER SESSION SET NLS_DATE_FORMAT = 'dd-mm-yyyy' ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY' ALTER SESSION SET NLS_LANGUAGE='ENGLISH'; Example 1: --------- SQL> select verbruikdatum from verbruik where verbruikid=859624; VERBRUIKD --------- 11-JUN-02 SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'dd-mm-yyyy'; Session altered. SQL> select verbruikdatum from verbruik where verbruikid=859624; VERBRUIKDA ---------- 11-06-2002 SQL> ALTER SESSION SET NLS_DATE_FORMAT ='dd-MON-yyyy'; Session altered. SQL> select verbruikdatum from verbruik where verbruikid=859624; VERBRUIKDAT ----------- 11-JUN-2002 Example 2: ---------- ALTER SESSION SET NLS_LANGUAGE=Italian; Enter a SELECT statement: SQL> SELECT last_name, hire_date, ROUND(salary/8,2) salary FROM employees; You should see results similar to the following: LAST_NAME HIRE_DATE SALARY ------------------------- --------- ---------- Sciarra 30-SET-97 962.5 Urman 07-MAR-98 975 Popp 07-DIC-99 862.5 Note that the month name abbreviations are in Italian. Immediately after the connection has been established, if the NLS_LANG environment setting is defined on the client side, then an implicit ALTER SESSION statement synchronizes the client and session NLS environments. 2.3. Language- Territory support, and Date time formats: ======================================================== Language Support The Oracle9i database enables you to store, process, and retrieve data in native languages. The languages that can be stored in an Oracle9i database are all languages written in scripts that are encoded by Oracle-supported character sets. Through the use of Unicode databases and datatypes, Oracle9i supports most contemporary languages. Territory Support The Oracle9i database supports cultural conventions that are specific to geographical locations. The default local time format, date format, and numeric and monetary conventions depend on the local territory setting. By setting different NLS parameters, the database session can use different cultural settings. For example, you can set British pound sterling (GBP) as the primary currency and the Japanese yen (JPY) as the secondary currency for a given database session even when the territory is defined as AMERICA. Date and Time Formats Different conventions for displaying the hour, day, month, and year can be handled in local formats. For example, in the United Kingdom, the date is displayed using the DD-MON-YYYY format, while Japan commonly uses the YYYY-MM-DD format. 2.4 ABOUT DATE FUNCTIONS: ========================= All SQL functions whose behavior depends on globalization support conventions allow NLS parameters to be specified. These functions are: TO_CHAR TO_DATE TO_NUMBER NLS_UPPER NLS_LOWER NLS_INITCAP NLSSORT Explicitly specifying the optional NLS parameters for these functions enables the functions to be evaluated independently of the session's NLS parameters. This feature can be important for SQL statements that contain numbers and dates as string literals. You can only get into trouble when using literals like '01-JAN-1990'. Take a look at the following examples and situations: -- ---------------------------------------------------------------------------- Example: SQL> alter session set nls_date_language='ITALIAN'; Session altered. SQL> SELECT ename, hiredate FROM scott.emp WHERE hiredate > '01-JAN-1981'; SELECT ename, hiredate FROM scott.emp WHERE hiredate > '01-JAN-1981' * ERROR at line 1: ORA-01858: a non-numeric character was found where a numeric was expected -- ---------------------------------------------------------------------------- Example: SQL> alter session set nls_date_language='AMERICAN'; Session altered. SQL> SELECT ename, hiredate FROM scott.emp WHERE hiredate > '01-JAN-1982'; ENAME HIREDATE ---------- ---------- ALLEN 20-02-1981 WARD 22-02-1981 JONES 02-04-1981 etc.. -- ---------------------------------------------------------------------------- Example: SQL> alter session set nls_date_language='ITALIAN'; Session altered. SQL> SELECT ename, hiredate FROM scott.emp WHERE hiredate > '01-SET-81'; ENAME HIREDATE ---------- ---------- SMITH 17-12-1980 ALLEN 20-02-1981 WARD 22-02-1981 etc.. -- ---------------------------------------------------------------------------- Example: But queries can be made independent of the current date language by using a statement similar to the following: SQL> alter session set nls_date_language='ITALIAN'; Session altered. SELECT ename, hiredate FROM scott.emp WHERE hiredate > TO_DATE('01-JAN-1982','DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN'); ENAME HIREDATE ---------- ---------- SCOTT 19-04-1987 ADAMS 23-05-1987 MILLER 23-01-1982 The following NLS parameters can be specified in SQL functions: NLS_DATE_LANGUAGE NLS_NUMERIC_CHARACTERS NLS_CURRENCY NLS_ISO_CURRENCY NLS_SORT -- ---------------------------------------------------------------------------- Example: SQL> alter session set nls_date_language='ITALIAN'; Session altered. SQL> SELECT ename, to_char(hiredate, 'DD-MM-YYYY;HH24:MI') 2 FROM scott.emp WHERE hiredate > '01-SET-81'; ENAME TO_CHAR(HIREDATE ---------- ---------------- SMITH 17-12-1980;00:00 ALLEN 20-02-1981;00:00 WARD 22-02-1981;00:00 etc.. SQL> SELECT ename, to_char(hiredate, 'DD-MM-YYYY;HH24:MI') 2 FROM scott.emp WHERE hiredate > '01-JAN-81'; FROM scott.emp WHERE hiredate > '01-JAN-81' * ERROR at line 2: ORA-01858: a non-numeric character was found where a numeric was expected -- ---------------------------------------------------------------------------- Example: SQL> alter session set nls_date_language='ITALIAN'; Session altered. SQL> select ADD_MONTHS ('28-JAN-1994', 2) from dual; select ADD_MONTHS ('28-JAN-1994', 2) from dual * ERROR at line 1: ORA-01843: not a valid month SQL> alter session set nls_date_language='AMERICAN'; Session altered. SQL> select ADD_MONTHS ('28-JAN-1994', 2) from dual; ADD_MONTH --------- 28-MAR-94 -- ----------------------------------------------------------------------------- Example: connect scott/tiger SQL> CREATE TABLE EMP2 2 ( 3 EMPNO NUMBER(4) NULL, 4 ENAME VARCHAR2(10 BYTE) NULL, 5 JOB VARCHAR2(9 BYTE) NULL, 6 MGR NUMBER(4) NULL, 7 HIREDATE DATE NULL, 8 HIREDATE2 DATE NULL 9 ); Table created. SQL> insert into EMP2 2 select empno, ename, job, mgr, hiredate, hiredate 3 from emp; 14 rows created. SQL> alter session set nls_date_language='ITALIAN'; Session altered. SQL> select * from emp2 where hiredate2 > '01-JAN-1981'; select * from emp2 where hiredate2 > '01-JAN-1981' * ERROR at line 1: ORA-01843: not a valid month SQL> select * from emp2 where hiredate2>hiredate; no rows selected SQL> alter session set nls_date_language='AMERICAN'; Session altered. SQL> select * from emp2 where hiredate2 > '01-JAN-1981'; EMPNO ENAME JOB MGR HIREDATE HIREDATE2 ---------- ---------- --------- ---------- --------- --------- 7499 ALLEN SALESMAN 7698 20-FEB-81 20-FEB-81 7521 WARD SALESMAN 7698 22-FEB-81 22-FEB-81 7566 JONES MANAGER 7839 02-APR-81 02-APR-81 etc.. -- ---------------------------------------------------------------------------- Example: create or replace procedure datetest(datum IN date) as employee emp2%rowtype; begin select * into employee from emp where hiredate=datum; dbms_output.put_line(employee.hiredate); end; / SQL> alter session set nls_date_language='AMERICAN'; Session altered. SQL> exec datetest('17-DEC-80'); 17-DEC-80 PL/SQL procedure successfully completed. SQL> alter session set nls_date_language='ITALIAN'; Session altered. SQL> exec datetest('17-DEC-80'); BEGIN datetest('17-DEC-80'); END; * ERROR at line 1: ORA-01843: not a valid month ORA-06512: at line 1 Explicit Conversion To convert values from one datatype to another, you use built-in functions. For example, to convert a CHAR value to a DATE or NUMBER value, you use the function TO_DATE or TO_NUMBER, respectively. Conversely, to convert a DATE or NUMBER value to a CHAR value, you use the function TO_CHAR. For more information about these functions, see Oracle9i SQL Reference. Implicit Conversion When it makes sense, PL/SQL can convert the datatype of a value implicitly. This lets you use literals, variables, and parameters of one type where another type is expected. In the example below, the CHAR variables start_time and finish_time hold string values representing the number of seconds past midnight. The difference between those values must be assigned to the NUMBER variable elapsed_time. So, PL/SQL converts the CHAR values to NUMBER values automatically. For instance, PL/SQL can convert the CHAR value '02-JUN-92' to a DATE value but cannot convert the CHAR value 'YESTERDAY' to a DATE value. Similarly, PL/SQL cannot convert a VARCHAR2 value containing alphabetic characters to a NUMBER value. 2.5 The TO_CHAR and TO_DATE functions and examples: =================================================== Several of the conversion functions (TO_CHAR, TO_DATE, and TO_NUMBER) use format models to determine the format of the converted data. Format models convert between strings and dates, and strings and numbers. This section discusses these format models, which are then put to use in the function descriptions. The to_char function converts a number or date to a string. The syntax for the to_char function is: to_char (value, [format_mask], [nls_language] ) value can either be a number or date that will be converted to a string. The format_mask is optional. This is the format that will be used to convert value to a string. The nls_language is optional. This is the nls language used to convert value to a string. Examples: --------- to_char (1210.73, '9999.9') would return '1210.7' to_char (1210.73, '9,999.99') would return '1,210.73' to_char (1210.73, '$9,999.00') would return '$1,210.73' to_char (21, '000099') would return '000021' to_char (sysdate, 'yyyy/mm/dd'); would return '2003/07/09' to_char (sysdate, 'Month DD, YYYY'); would return 'July 09, 2003' to_char (sysdate, 'FMMonth DD, YYYY'); would return 'July 9, 2003' to_char (sysdate, 'MON DDth, YYYY'); would return 'JUL 09TH, 2003' to_char (sysdate, 'FMMON DDth, YYYY'); would return 'JUL 9TH, 2003' to_char (sysdate, 'FMMon ddth, YYYY'); would return 'Jul 9th, 2003' to_char (SYSDATE,'HH:MM') ; would return '02:07' create or replace procedure test1 is begin if TO_CHAR(SYSDATE, 'DAY')='WEDNESDAY' then dbms_output.put_line('Today is wednesday'); else dbms_output.put_line('other day'); end if; end; / SQL> select to_char(SYSDATE,'HH:MM') from dual; TO_CH ----- 01:07 SQL> select replace(to_char(SYSDATE,'HH:MM'),':',null) from dual; REPLA ----- 0107 You can use the TO_CHAR function with a 'date_format_mask': TO_CHAR(SYSDATE, 'x') DD DAY MON MONTH YY YYYY RR RRRR HH (am/pm) HH24 etc.. It tells the TO_CHAR function *HOW* to display the string. Examples with the TO_DATE function: ----------------------------------- Example 1: ---------- SQL> select TO_DATE ('123188', 'MMDDYY') from dual; TO_DATE(' --------- 31-DEC-88 Example 2: ---------- SQL> select TO_DATE ('123188', 'DDMMYY') from dual; select TO_DATE ('123188', 'DDMMYY') from dual * ERROR at line 1: ORA-01843: not a valid month So you see, the mask tells TO_DATE *HOW* to make a DATE from the string. Here, the mask is obviously wrong. Example 3: ---------- select TO_DATE ('123188', 'DDMMYY') from dual; insert into MKM_KPI_WAARDEN ( day_code, kpi, kpi_id, kleur, ool_id, trend, kleur_trend, vorige_kleur, vorige_kpi) ( select day_code, 0.880, 1081, 'GROEN', 901, 'STABIEL', 'GROEN', 'GROEN', 1 from mkm_days where day_code >= TO_DATE('01-07-2004', 'dd-mm-yyyy') AND day_code <= TO_DATE('31-07-2004', 'dd-mm-yyyy')); Example 4: ---------- INSERT INTO IOB_KITAP_STAGING_AUT (Volgnummer,RecordId,DatumTijd,Landcodepas,automaatnummer, Tanknummer1,Produktnummer1,Litertotaal1, Tanknummer2,Produktnummer2,Litertotaal2, Tanknummer3,Produktnummer3,Litertotaal3, Tanknummer4,Produktnummer4,Litertotaal4, Tanknummer5,Produktnummer5,Litertotaal5) SELECT Volgnummer, RecordId, to_date(DatumTijd,'DD-MM-YYYY;HH24:MI:SS'), Landcodepas, automaatnummer, Tanknummer1, Produktnummer1, to_number(LTRIM(Litertotaal1,'-')), Tanknummer2, Produktnummer2, to_number(LTRIM(Litertotaal2,'-')), Tanknummer3, Produktnummer3, to_number(LTRIM(Litertotaal3,'-')), Tanknummer4, Produktnummer4, to_number(LTRIM(Litertotaal4,'-')), Tanknummer5, Produktnummer5, to_number(LTRIM(Litertotaal5,'-')) FROM IOB_KITAP_IMPORT_AUT WHERE AUTOMAATNUMMER IS NOT NULL; 2.6 Other DATE functions: ========================= add_months(x,y) -- levert een datum op al x + y maanden last_day(x) -- levert de laatste dag van de maand months_between(x,y) -- aantal maanden tussen x en y new_time(x,y,z) -- date-time x in zone y for zone z next_day(x) -- naam van de volgende dag bij datum x SQL> select LAST_DAY(ADD_MONTHS(LAST_DAY(SYSDATE+1),1)) from dual; LAST_DAY( --------- 31-AUG-04 SQL> select LAST_DAY(ADD_MONTHS(LAST_DAY(SYSDATE+1),1))+1 from dual; LAST_DAY( --------- 01-SEP-04 2.7 TRUNC Function: =================== If you are not sure about the time components of your date fields and variables and want to make sure that your operations on dates disregard the time component, TRUNCate them: IF TRUNC (request_date) BETWEEN TRUNC (start_date) AND TRUNC (end_date) THEN .. 2.8 Some handy functions: ========================= CREATE OR REPLACE FUNCTION WB_Cal_Yr ( v_date IN DATE ) RETURN NUMBER IS BEGIN RETURN ( TO_NUMBER( TO_CHAR( v_date, 'YYYY' ) ) ); END WB_Cal_Yr; / CREATE OR REPLACE FUNCTION time24HMMSS ( v_date IN DATE ) RETURN VARCHAR2 IS BEGIN RETURN ( TO_CHAR( v_date, 'HH24:MI' ) ); END timepart; / CREATE OR REPLACE FUNCTION time24HHMM ( v_date IN DATE ) RETURN VARCHAR2 IS BEGIN RETURN ( TO_CHAR( v_date, 'HH24:MI' ) ); END time24HHMM; / select timepart(verbruiktijd) from verbruik where verbruikid=859624; TIMEPART(VERBRUIKTIJD) ---------------------- 15:22 CREATE OR REPLACE FUNCTION WB_Day_of_Month ( v_date IN DATE ) RETURN NUMBER IS BEGIN RETURN ( TO_NUMBER( TO_CHAR( v_date, 'DD' ) ) ); END WB_Day_of_Month; / CREATE OR REPLACE FUNCTION WB_Day_of_Week ( v_date IN DATE ) RETURN NUMBER IS BEGIN RETURN ( TO_NUMBER( TO_CHAR( v_date, 'D' ) ) ); END WB_Day_of_Week; / create or replace procedure a is x number; begin x:=WB_CAL_Yr(01/01/2004); end; / create or replace procedure no_op is myvar number; begin myvar:=WB_Cal_Yr('14-FEB-2004'); dbms_output.put_line(TO_CHAR(myvar)); end; / create or replace procedure no_op (v_date IN DATE) is myvar number; begin myvar:=WB_Cal_Yr(v_date); dbms_output.put_line(myvar); end; / How to use? for example: ------------------------ SQL> exec no_op('05-05-2003'); 2003 PL/SQL procedure successfully completed. FUNCTION WB_Cal_Year_Name ( v_date IN DATE ) RETURN VARCHAR2 IS BEGIN RETURN ( TO_CHAR( v_date, 'fmYear' ) ); END WB_Cal_Year_Name; / FUNCTION WB_Day_of_Month ( v_date IN DATE ) RETURN NUMBER IS BEGIN RETURN ( TO_NUMBER( TO_CHAR( v_date, 'DD' ) ) ); END WB_Day_of_Month; / FUNCTION WB_Day_of_Week ( v_date IN DATE ) RETURN NUMBER IS BEGIN RETURN ( TO_NUMBER( TO_CHAR( v_date, 'D' ) ) ); END WB_Day_of_Week; / FUNCTION WB_Day_of_Year ( v_date IN DATE ) RETURN NUMBER IS BEGIN RETURN ( TO_NUMBER( TO_CHAR( v_date, 'DDD' ) ) ); END WB_Day_of_Year; / FUNCTION WB_Hour12 ( v_date IN DATE ) RETURN NUMBER IS BEGIN RETURN ( TO_NUMBER( TO_CHAR( v_date, 'HH12' ) ) ); END WB_Hour12; / -- ---------------------------------------------------------------------- 2.9 Weird example of filling a table with dates: ------------------------------------------------ declare i number := 1; j date; k varchar2(30); l varchar2(30); m varchar2(30); n number(9,2); o number; p varchar2(30); begin while i<100000 loop j:=sysdate+i; k:=TO_CHAR(j,'DAY'); o:=sin(i); n:=abs(round(o,4)*100); begin if TO_CHAR(j,'DAY') LIKE 'WED%' then l:='DSM'; p:='Utrecht'; else if TO_CHAR(j,'DAY') LIKE 'THURS%' then l:='AKZO'; p:='Alkmaar'; else if TO_CHAR(j,'DAY') LIKE 'FRI%' then l:='McDonalds'; p:='Den Haag'; else l:='MACRO'; p:='Amsterdam'; end if; end if; end if; end; insert into CUSTOMER (CUST_ID,CUST_NAME,CUST_CITY,ORDER_DATE,DAY,AMOUNT) values (i,l,p,j,k,n); i := i + 1; commit; end loop; commit; end; / Or use something much better in unix and perl, like for example: #!/usr/bin/perl for ($i=1; $i < 10000; $i++ ) { print "insert into access_log values ( '$i', 'yada $i','klsdfkjl');\n" } ========= 3. JOINS: ========= 3.1. Create three sample tables: -------------------------------- In order to demonstrate the joins and summarizations in the next sections, let us first create some example tables. create table LOC -- table of locations ( LOCID int, CITY varchar2(16), constraint pk_loc primary key (locid) ); create table DEPT -- table of departments ( DEPID int, DEPTNAME varchar2(16), LOCID int, constraint pk_dept primary key (depid), constraint fk_dept_loc foreign key (locid) references loc(locid) ); create table EMP -- table of employees ( EMPID int, EMPNAME varchar2(16), DEPID int, constraint pk_emp primary key (empid), constraint fk_emp_dept foreign key (depid) references dept(depid) ); -- --------------------------------------------------------------------- 3.2. Now insert some sample records: ------------------------------------ INSERT INTO LOC VALUES (1,'Amsterdam'); INSERT INTO LOC VALUES (2,'Haarlem'); INSERT INTO LOC VALUES (3,null); INSERT INTO LOC VALUES (4,'Utrecht'); INSERT INTO DEPT VALUES (1,'Sales',1); INSERT INTO DEPT VALUES (2,'PZ',1); INSERT INTO DEPT VALUES (3,'Management',2); INSERT INTO DEPT VALUES (4,'RD',3); INSERT INTO DEPT VALUES (5,'IT',4); INSERT INTO EMP VALUES (1,'Joop',1); INSERT INTO EMP VALUES (2,'Gerrit',2); INSERT INTO EMP VALUES (3,'Harry',2); INSERT INTO EMP VALUES (4,'Christa',3); INSERT INTO EMP VALUES (5,null,4); INSERT INTO EMP VALUES (6,'Nina',5); INSERT INTO EMP VALUES (7,'Nadia',5); -- ---------------------------------------------------------------------- 3.3. Show whats in these tables: -------------------------------- SELECT * FROM emp; SELECT * FROM dept; SELECT * FROM loc; empid empname depid ----------- ---------------- ----------- 1 Joop 1 2 Gerrit 2 3 Harry 2 4 Christa 3 5 NULL 4 6 Nina 5 7 Nadia 5 (7 row(s) affected) depid deptname locid ----------- ---------------- ----------- 1 Sales 1 2 PZ 1 3 Management 2 4 RD 3 5 IT 4 (5 row(s) affected) locid city ----------- ---------------- 1 Amsterdam 2 Haarlem 3 NULL 4 Utrecht (4 row(s) affected) -- ---------------------------------------------------------------------- 3.4. Let's try some join statements: ------------------------------------ Query 1: -------- SELECT deptname, city FROM dept, loc WHERE dept.locid=loc.locid; -- or next, what is essentially the same query: SELECT deptname, city FROM dept INNER JOIN loc ON dept.locid=loc.locid; Result 1: -------- deptname city ---------------- ---------------- Sales Amsterdam PZ Amsterdam Management Haarlem RD NULL IT Utrecht Query 2: -------- SELECT e.empid, e.empname, d.depid, d.deptname FROM emp e, dept d WHERE e.depid=d.depid; SELECT e.empid, e.empname, d.depid, d.deptname FROM emp e INNER JOIN dept d ON e.depid=d.depid; Result 2: --------- empid empname depid deptname ----------- ---------------- ----------- ---------------- 1 Joop 1 Sales 2 Gerrit 2 PZ 3 Harry 2 PZ 4 Christa 3 Management 5 NULL 4 RD 6 Nina 5 IT 7 Nadia 5 IT So for example, Nina and Nadia are both in the IT department. Query 3: -------- SELECT e.empid, e.empname, d.depid, d.deptname, l.locid, l.city FROM emp e INNER JOIN dept d ON e.depid=d.depid INNER JOIN loc l ON d.locid=l.locid; or the same query with parenthesis: SELECT e.empid, e.empname, d.depid, d.deptname, l.locid, l.city FROM ((emp e INNER JOIN dept d ON e.depid=d.depid) INNER JOIN loc l ON d.locid=l.locid); Result 3: --------- empid empname depid deptname locid city ----------- ---------------- ----------- ---------------- ----------- ---------------- 1 Joop 1 Sales 1 Amsterdam 2 Gerrit 2 PZ 1 Amsterdam 3 Harry 2 PZ 1 Amsterdam 4 Christa 3 Management 2 Haarlem 5 NULL 4 RD 3 NULL 6 Nina 5 IT 4 Utrecht 7 Nadia 5 IT 4 Utrecht So both Nina and Nadia are in the IT department in Utrecht Most time, inner joins deliver the correct answer in business problems. An outer join, which can be a 'left outer join' or a 'right outer join', accommodates the situation in which you want to display data in a join statement, WHERE records FROM one table don't necessarilly all have corresponding records in the other. For example, table EMP contains 7 records. If we put the departmentid depid of Christa to NULL, the following effect is visible: UPDATE EMP SET depid=null WHERE empname='Christa'; SQL> select * from EMP; EMPID EMPNAME DEPID ---------- ---------------- ---------- 1 Joop 1 2 Gerrit 2 3 Harry 2 4 Christa 5 4 6 Nina 5 7 Nadia 5 7 rows selected. >> Now watch this INNER JOIN: SQL> SELECT e.empid, e.empname, d.depid, d.deptname 2 FROM emp e INNER JOIN dept d ON e.depid=d.depid; EMPID EMPNAME DEPID DEPTNAME ---------- ---------------- ---------- ---------------- 1 Joop 1 Sales 2 Gerrit 2 PZ 3 Harry 2 PZ 5 4 RD 6 Nina 5 IT 7 Nadia 5 IT 6 rows selected. The data of Christa is completely left out from the resultset ! >> Now watch this LEFT JOIN: Query 4: -------- SQL> SELECT e.empid, e.empname, d.depid, d.deptname 2 FROM emp e LEFT JOIN dept d ON e.depid=d.depid; EMPID EMPNAME DEPID DEPTNAME ---------- ---------------- ---------- ---------------- 1 Joop 1 Sales 3 Harry 2 PZ 2 Gerrit 2 PZ 5 4 RD 7 Nadia 5 IT 6 Nina 5 IT 4 Christa 7 rows selected. So the LEFT JOIN says that we also want to see all records from the "left table" which do not neccesarily have a matching id. Some more examples: Query 5: -------- SELECT e.empid, e.empname, d.depid, d.deptname, l.locid, l.city FROM emp e LEFT JOIN dept d ON e.depid=d.depid LEFT JOIN loc l ON d.locid=l.locid; Result 5: --------- EMPID EMPNAME DEPID DEPTNAME LOCID CITY ---------- ---------------- ---------- ---------------- ---------- ---------------- 2 Gerrit 2 PZ 1 Amsterdam 3 Harry 2 PZ 1 Amsterdam 1 Joop 1 Sales 1 Amsterdam 5 4 RD 3 6 Nina 5 IT 4 Utrecht 7 Nadia 5 IT 4 Utrecht 4 Christa 7 rows selected. In general, use a LEFT JOINT to see all values FROM the "left" table even if it has possible NULL values in the common key. In general, use a RIGHT JOINT to see all values FROM the "right" table even if it has possible NULL values in the common key. We can also extent such queries easily with other clausules, like - IN, NOT IN, or subqueries, or WHERE EXISTS: Query 5. (use of IN or NOT IN) ------------------------------ SQL> SELECT e.empid, e.empname, d.depid, d.deptname 2 FROM emp e, dept d 3 WHERE e.depid=d.depid 4 and d.depid IN (1,5); EMPID EMPNAME DEPID DEPTNAME ---------- ---------------- ---------- ---------------- 6 Nina 5 IT 7 Nadia 5 IT 1 Joop 1 Sales Query 6 (use of subquery) ------------------------- select e.ename, d.location, d.depid from emp e, dept d where e.depid=d.depid and d.depid IN (SELECT depid from location where city='Amsterdam'); ename salary dname location deptno ---------- ----------- -------------------- -------------------- ----------- Joop 2000 sales Amsterdam 1 Klaas 1500 management Amsterdam 2 Miranda 7000 management Amsterdam 2 Nadia 1000 sales Amsterdam 1 (4 records) Query 7 (use of WHERE EXISTS) ----------------------------- SELECT e.ename, e.salary FROM employee e WHERE exists (SELECT d.deptno FROM department d WHERE d.location='Amsterdam' and e.deptno=d.deptno); ename salary ---------- ----------- Joop 2000 Klaas 1500 Miranda 7000 Nadia 1000 3.3 SELFJOIN: ============= This is a join using the same table, with 2 aliases in the query. You can use this when there is a possibility that some slight differences exists between some rows that would otherwise be duplicate records. SELECT e.empno, e.ename, e.job FROM emp e, emp e2 WHERE e.empno=e2.empno SELECT e.empno, e.ename, e.job FROM emp e, emp e2 WHERE e.empno<>e2.empno and e.ename=e2.ename; 3.4 Tree query: =============== CREATE TABLE STUDENTS ( StudentID NUMBER(5,0) NOT NULL, Name VARCHAR2(25), Major VARCHAR2(15), GPA NUMBER(6,3), tutorid NUMBER(5,0), CONSTRAINT pk_studentid primary key (studentid) ); INSERT INTO students VALUES (101, 'Bill', 'CIS', 3.45, 102); INSERT INTO students VALUES (102, 'Mary', 'CIS', 3.10, NULL); INSERT INTO students VALUES (103, 'Sue', 'Marketing', 2.95, 102); INSERT INTO students VALUES (104, 'Tom', 'Finance', 3.5, 106); INSERT INTO students VALUES (105, 'Alex', 'CIS', 2.75, 106); INSERT INTO students VALUES (106, 'Sam', 'Marketing', 3.25, 103); INSERT INTO students VALUES (107, 'Jane', 'Finance', 2.90, 102); CREATE TABLE COURSES ( StudentID NUMBER(5,0) NOT NULL, CourseNumber VARCHAR2(15) NOT NULL, CourseName VARCHAR2(25), Semester VARCHAR2(10), Year NUMBER(4,0), Grade VARCHAR2(2), CONSTRAINT FK_STUDENTID foreign key (studentid) references students(studentid) ); INSERT INTO courses VALUES (101, 'CIS3400', 'DBMS I', 'FALL', 1997, 'B+'); INSERT INTO courses VALUES (101, 'CIS3100', 'OOP I', 'SPRING', 1999, 'A-'); INSERT INTO courses VALUES (101, 'MKT3000', 'Marketing', 'FALL', 1997, 'A'); INSERT INTO courses VALUES (102, 'CIS3400', 'DBMS I', 'SPRING', 1997, 'A-'); INSERT INTO courses VALUES (102, 'CIS3500', 'Network I', 'SUMMER', 1997, 'B'); INSERT INTO courses VALUES (102, 'CIS4500', 'Network II', 'FALL', 1997, 'B+'); INSERT INTO courses VALUES (103, 'MKT3100', 'Advertizing', 'SPRING', 1998, 'A'); INSERT INTO courses VALUES (103, 'MKT3000', 'Marketing', 'FALL', 1997, 'A'); INSERT INTO courses VALUES (103, 'MKT4100', 'Marketing II', 'SUMMER', 1998, 'A-'); Another form of recursive query is the tree query. A tree query decomposes the table such that each row is a node the tree and nodes are related in levels. Consider the Students table defined above. Bill tutors Alex, Mary and Sue. Mary tutors Liz and Ed Sue tutors Petra Using the SQL SELECT statements CONNECT BY and START WITH clauses, we can form a set of relationships between the rows of the table that form a tree structure. START WITH - indicates which row the tree should start with. CONNECT BY - indicates how successive related rows are to be identified and included in the result. LEVEL - a pseudo-column that indicates which level of the tree the current row is assigned to. The following example prints a tree structure modeled after the tutoring relationships in the Students table. We will start with Mary's student id (102) since no one tutors her. SELECT LPAD(' ',2*(LEVEL-1)) || students.name As TutorTree FROM students START WITH studentid = '102' CONNECT BY PRIOR studentid = tutorid; TUTORTREE -------------------------------------------------------------------------------- Mary Bill Sue Sam Tom Alex Jane 7 rows SELECTed. FROM the tree we can see that Mary tutors Bill, Sue and Jane. In turn, Sue tutors Sam. Finally, Sam tutors both Tom and Alex. 3.5 Some extended UPDATE examples: ================================== In SQL Server you can have really easy UPDATE statements where 2 tables are involved. Suppose you have the following: create table a ( id int not null, name varchar(10)) create table b ( id int not null, name varchar(10)) alter table a add constraint pk_a primary key (id) alter table b add constraint pk_b primary key (id) insert into a values (1, 'joop'); insert into a values (2, 'joop'); insert into b values (1, 'karel'); insert into b values (2, 'karel'); Now it's possible to update a from b, with a very easy statement: update a set name=b.name from a,b where a.id=b.id In Oracle it's a little different. Example 2: ---------- I have 2 tables and i need to update data in the first table with data from the second table. The following statement will not work but it will give you an idea of what i am trying to do. UPDATE table1 SET table1.code_id = table2.system_id WHERE table1.code_id = table2.code_id The statement does not work. Now try: UPDATE (SELECT table1.code_id t1_code, table2.system_id t2_sys FROM table1, table2 WHERE table1.code_id = table2.code_id) SET t1_code = t2_sys; This seems to be heading in the right direction. However now i'm getting error "ORA-01779 cannot modify a cloumn which maps to a non key-preserved table" Answer: You need a primary key/unique constraint on system_id in table2 to ensure that each row in table1 joins to AT MOST 1 row in table2 Try: update table1 set code_id = (select system_id from table2 where table2.code_id = table1.code_id) where exists (select system_id from table2 where table2.code_id = table1.code_id) This works. Example 2: ---------- You want this: update a set name=b.name from a,b where a.id=b.id In Oracle you use a statement like this: UPDATE a SET a.name = (SELECT b.name FROM b WHERE a.id = b.id ); Example 3: ---------- update (select x1.name as old_name, y1.name as new_name from x1 inner join y1 on x1.name=y1.name) set old_name=new_name; Example 4: ---------- UPDATE (SELECT x.details AS old_details, y.details AS new_details FROM x INNER JOIN y ON x.NAME=y.NAME and y.dept_id = 112) SET old_details=new_details; Example 5: ---------- UPDATE emp e SET (ename, job, mgr, hiredate, sal, comm, deptno) = (SELECT ename, job, mgr, hiredate, sal, comm, deptno FROM emp_load el WHERE e.empno = el.empno) WHERE e.empno IN (SELECT empno FROM emp_load); UPDATE /*+ USE_NL(e) INDEX(e) */ emp SET (ename, job, mgr, hiredate, sal, comm, deptno) = (SELECT ename, job, mgr, hiredate, sal, comm, deptno FROM emp_load el WHERE e.empno = el.empno ) WHERE e.empno IN ( SELECT empno FROM emp_load) UPDATE /*+ USE_NL(e) INDEX(e) */ emp SET (ename, job, mgr, hiredate, sal, comm, deptno) = (SELECT ename, job, mgr, hiredate, sal, comm, deptno FROM emp_load el WHERE e.empno = el.empno ) WHERE e.empno IN ( SELECT empno FROM emp_load) Example 6: ---------- update verbruik set verbruiktijd=null; update VERBRUIK set verbruiktijd = (select verbruiktijd from RESERVE_VERBRUIK where RESERVE_VERBRUIK.verbruikid = VERBRUIK.verbruikid) where exists (select verbruiktijd from RESERVE_VERBRUIK where RESERVE_VERBRUIK.verbruikid = VERBRUIK.verbruikid); 3.6 IN, EXISTS, AND NOT IN, NOT EXISTS: ======================================= You Asked (Jump to Tom's latest followup) Hi Tom, Can you pls explain the diff between IN and EXISTS and NOT IN and NOT EXISTS. Because I have read that EXISTS will work better than IN and NOT EXISTS will work better than NOT IN (read this is Oracle server tunning). and we said... see http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:953229842074 It truly depends on the query and the data as to which is BEST. Note that in general, NOT IN and NOT EXISTS are NOT the same!!! select count(*) from emp where empno not in ( select mgr from emp ); COUNT(*) ---------- 0 apparently there are NO rows such that an employee is not a mgr -- everyone is a mgr (or are they) select count(*) from emp T1 where not exists ( select null from emp T2 where t2.mgr = t1.empno ); COUNT(*) ---------- 9 Ahh, but now there are 9 people who are not managers. Beware the NULL value and NOT IN!! (also the reason why NOT IN is sometimes avoided). NOT IN can be just as efficient as NOT EXISTS -- many orders of magnitude BETTER even -- if an "anti-join" can be used (if the subquery is known to not return nulls) Tom, Instead of select count(*) from emp T1 where not exists ( select null from emp T2 where t2.mgr = t1.empno ); you could have used select count(*) from emp T1 where not exists ( select mgr from emp T2 where t2.mgr = t1.empno ); Could you tell what circumstances do we use "select null" instead of "select ". Are there any advantages Followup: why select mgr? I find select null to be semantically more meaningful. You are NOT selecting anything really -- so say that. Hi Tom, Your answer is superb. Can you tell us why there is no record selected for NOT IN when there is NULL? Followup: Because NULL means -- gee, I don't know. (litterally, null means Unknown) So, the predicate where x not in ( NULL ) evaluates to neither TRUE, nor FALSE ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from dual where dummy not in ( NULL ); no rows selected ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from dual where NOT( dummy not in (NULL) ); no rows selected (you would think one of the two queries would return a row -- but there is a third state for a boolean expression in sql -- "I don't know what the answer is") Other example of IN, NOT IN, EXISTS, NOT EXISTS: ================================================ create table A (id int, name varchar2(10), datum date); create table B (id int, name varchar2(10), datum date); insert into A values (1,'piet',sysdate); insert into A values (2,'piet',sysdate-1); insert into A values (3,'klaas',sysdate-2); insert into A values (4,'gerrit',sysdate-3); insert into B values (1,'piet',sysdate); insert into B values (2,'piet',sysdate-1); insert into B values (3,'snoopy',sysdate-2); insert into B values (4,'gerrit',sysdate-3); SQL> select * from A; ID NAME DATUM ---------- ---------- --------- 1 piet 14-SEP-04 2 piet 13-SEP-04 3 klaas 12-SEP-04 4 gerrit 11-SEP-04 SQL> select * from B; ID NAME DATUM ---------- ---------- --------- 1 piet 14-SEP-04 2 piet 13-SEP-04 3 snoopy 12-SEP-04 4 gerrit 11-SEP-04 select * from A where id NOT IN (select id from B where id is not null) AND name NOT IN (select name from B where name is not null) ; no rows selected select * from A where id NOT IN (select id from B where id is not null) OR name NOT IN (select name from B where name is not null) ; ID NAME DATUM ---------- ---------- --------- 3 klaas 29-MAR-05 select * from B where id NOT IN (select id from A where id is not null) AND name NOT IN (select name from A where name is not null) ; no rows selected select * from B where id NOT IN (select id from A where id is not null) OR name NOT IN (select name from A where name is not null) ; ID NAME DATUM ---------- ---------- --------- 3 snoopy 29-MAR-05 update A set ID=5 where name='klaas'; SQL> select * from A; ID NAME DATUM ---------- ---------- --------- 1 piet 14-SEP-04 2 piet 13-SEP-04 5 klaas 12-SEP-04 4 gerrit 11-SEP-04 select * from A where id NOT IN (select id from B where id is not null) AND name NOT IN (select name from B where name is not null) ; ID NAME DATUM ---------- ---------- --------- 5 klaas 12-SEP-04 select * from A where id NOT IN (select id from B where id is not null) AND name NOT IN (select name from B where name is not null) AND trunc(datum) NOT IN (select trunc(datum) from B where datum is not null) ; no rows selected select * from A where id IN (select id from B where id is not null) AND name IN (select name from B where name is not null) ; ID NAME DATUM ---------- ---------- --------- 4 gerrit 11-SEP-04 1 piet 14-SEP-04 2 piet 13-SEP-04 ------------------------------------------------------- select * from A where id NOT IN (select id from B where id is not null) AND name NOT IN (select name from B where name is not null) ; ID NAME DATUM ---------- ---------- --------- 5 klaas 12-SEP-04 select * from A where NOT EXISTS (select id from B where a.id=b.id) AND NOT EXISTS (select name from B where a.name=b.name) ; ID NAME DATUM ---------- ---------- --------- 5 klaas 12-SEP-04 ================================= 4. Group and aggregate functions: ================================= 4.1 simple use: =============== avg(x) count(x) max(x) min(x) stddev(x) sum(x) variance(x) etc.. SELECT avg(sal) FROM emp; SELECT count(*) FROM emp; Those functions do not include NULL columns values. 4.2 Using the "GROUP BY" clause: ================================ SELECT deptno, avg(sal) FROM emp GROUP BY deptno; SELECT deptno, job, avg(sal) FROM emp GROUP BY deptno, job ORDER BY job; SELECT deptno, job, avg(sal) FROM emp GROUP BY deptno, job ORDER BY avg(sal); SELECT deptno, sum(sal) FROM emp GROUP BY deptno; SELECT deptno, job, sum(sal) FROM emp GROUP BY deptno, job; 4.3 Rollup and cube: ==================== Rollup: (1 dimension) xx j1 -- xx j2 -- -- tot xx yy j1 -- yy j2 -- yy j3 -- -- tot yy tot xy This is OK: SELECT deptno, job, sum(sal) FROM emp GROUP BY rollup(deptno, job); This is wrong: SELECT deptno, job, sum(sal) FROM emp GROUP BY rollup(deptno); Cube: (n dimensions) xx j1 -- xx j2 -- -- tot xx yy j1 -- yy j2 -- yy j3 -- -- tot yy totj1 aa totj2 bb totj3 cc SELECT deptno, job, sum(sal) FROM emp GROUP BY cube(deptno, job); 4.4 Having clause: ================== Once the data is grouped using the "GROUP BY" statement, it is sometimes usefull to weed out unwanted data. The HAVING clause, acts for the GROUP BY clause, as the WHERE clause. SELECT deptno, avg(sal) FROM emp GROUP BY deptno HAVING deptno>10; SELECT deptno, avg(sal) FROM emp GROUP BY deptno HAVING avg(sal)>2000; Notice the difference with the following: SELECT deptno, avg(sal) FROM emp WHERE sal>2000 -- WHERE avg(sal)>2000: not allowed GROUP BY deptno; ============== 5. Subqueries: ============== 5.1 General: ============ This is a SELECT statement within a SELECT statement, designed to limit the resultset. In most cases you can find the second query in the WHERE clause of the parent query. But also in the FROM clause is possible. SELECT ename, deptno, sal FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE loc='NEW YORK'); This query resolves to a query like SELECT ename, deptno, sal FROM emp WHERE deptno=10; SELECT ename, deptno, sal FROM emp WHERE deptno in (SELECT deptno FROM dept WHERE loc='NEW YORK'); 5.2 With the "WHERE exists" clause: =================================== These type of subqueries resolve INTO the statement: SELECT .. FROM .. WHERE exists (is TRUE) SELECT ename, deptno, sal FROM emp WHERE exists (SELECT deptno FROM dept WHERE loc='NEW YORK'); This query will NOT deliver the correct result, because it actually resolves in SELECT ename, deptno, sal FROM emp WHERE exists (is TRUE); which will turn up much more records than we are interrested in. This one works: SELECT e.ename, e.job, e.sal FROM emp e WHERE exists (SELECT d.deptno FROM dept d WHERE d.loc='NEW YORK' and e.deptno=d.deptno); 5.3 Types of subqueries: ======================== 5.3.1 Single row subqueries: ---------------------------- The main query expects the subquery to return only one values. We have seen these before. SELECT ename, deptno, sal FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE loc='NEW YORK'); We have use the "=' operator and therefore the subquery MUST return ONLY 1 value. 5.3.2 Multi row subqueries: ---------------------------- In this type of query, the parent query can expect more than one values. SELECT ename, job, sal FROM emp WHERE deptno in (SELECT deptno FROM dept WHERE dname in ('ACCOUNTING','SALES')); SELECT deptno, job, avg(sal) FROM emp GROUP BY deptno, job HAVING avg(sal)>(SELECT sal FROM emp WHERE ename='MARTIN'); 5.3.3 Null values: ------------------ Suppose ename='KING' has a deptno=null SELECT deptno, ename, job, sal FROM emp WHERE (deptno, sal) in (SELECT deptno, max(sal) FROM emp GROUP BY deptno); Een subquery geeft geen null terug. Daarom zal KING in de resultset niet te zien zijn. Herschrijf de query naar een correlated subquery: select e.deptno, e.ename, e.job, e.sal from emp e where e.sal=(select max(e2.sal) from emp e2 where e.deptno=e2.deptno) ========== 6. PL/SQL: ========== 6.1 SIMPLE FUNCTIONS AND PROCEDURES: ==================================== 6.1.1 Identifiers: ------------------ You use identifiers to name PL/SQL program items and units, which include constants, variables, exceptions, cursors, cursor variables, subprograms, and packages. Some examples of identifiers follow: X t2 phone# credit_limit LastName oracle$number program5 An identifier consists of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs. Other characters such as hyphens, slashes, and spaces are illegal, as the following examples show: this is wrong: mine&yours -- illegal ampersand debit-amount -- illegal hyphen on/off -- illegal slash user id -- illegal space this is OK: money$$$tree SN## try_again_ 6.1.2 VARIABLE DECLARATION: --------------------------- Variable declaration: --------------------- part_no NUMBER(4) ; in_stock BOOLEAN ; constrained: itty_bitty_# NUMBER(1); unconstrained: no_limits_here NUMBER; When you declare a scalar variable (a variable with a scalar or noncomposite datatype), you can provide a default or initial value for that variable. In the following example, I declare the total_sales variable and initialize it to zero using both the DEFAULT syntax and the assignment operator: total_sales NUMBER (15,2) := 0; Constant declaration: --------------------- pi constant number:=3.14; next_tax_filing_date CONSTANT DATE := '15-APR-96'; 6.1.3 ASSIGNING VALUES TO VARIABLES: ------------------------------------ - Via assignment operator := tax := price * tax_rate; bonus := current_salary * 0.10; amount := TO_NUMBER(SUBSTR('750 dollars', 1, 3)); valid := FALSE; - via SELECT SELECT sal * 0.10 INTO bonus FROM emp WHERE empno = emp_id; Default value taken from Oracle Forms bind variable: call_topic VARCHAR2 (100) DEFAULT :call.description; Default value is the result of the expression: order_overdue CONSTANT BOOLEAN := ship_date > ADD_MONTHS (order_date, 3) OR priority_level (company_id) = 'HIGH'; not null clause: company_name VARCHAR2(60) NOT NULL DEFAULT 'PCS R US'; 6.1.4 SIMPLE FUNCTIONS: ----------------------- Example 1: ---------- create or replace function area_of_circle(p_radius in number) return number as my_area number default 0; pi constant number:=3.14; begin my_area:=p_radius*p_radius*pi; return my_area; end area_of_circle; / Output: SELECT area_of_circle(5) FROM dual; It's NOT !!! possible to execute area_of_circle like this: SQL>execute area_of_circle(5); NOTE 1: So, functions are most of the time specialized code units, to be used from within other procedures. Example 2: ---------- create or replace procedure area_proc(p_radius in number) is my_area number default 0; begin my_area:=area_of_circle(p_radius); dbms_output.put_line('Dit is het resultaat: '||my_area); end; / SQL> exec area_proc(7); Dit is het resultaat: 153,86 PL/SQL-procedure is geslaagd. 6.1.5 SIMPLE PROCEDUREs: ------------------------ create or replace procedure no_op(p_var in number, p_var2 out number) is begin p_var2:=p_var; dbms_output.put_line(TO_CHAR(p_var2)); end; / Now try this: SQL> exec no_op(1); BEGIN no_op(1); END; * ERROR at line 1: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'NO_OP' ORA-06550: line 1, column 7: PL/SQL: Statement ignored !!! procedures die GEEN return values geven kun je simpleweg zo starten: SQL>execute no_op; !!! procedures DIE WEL return values ( out ) terug geven: declareer altijd een variabele om daarin de return value van de procedure te plaatsen. -- -------------------------------------------------------- create or replace procedure no_op (p_var1 in number, p_var2 out number) is begin p_var2:=2 * p_var1; end; / declare myvar1 number; myvar2 number; begin no_op(5, myvar2); dbms_output.put_line(TO_CHAR(myvar2)); end; / -- -------------------------------------------------------- create or replace procedure no_op (p_var1 in number, p_var2 out number, p_var3 out number) is begin p_var2:=2 * p_var1; p_var3:=3 * p_var1; end; / declare myvar2 number; myvar3 number; begin no_op(5, myvar2, myvar3); dbms_output.put_line(TO_CHAR(myvar2)); dbms_output.put_line(TO_CHAR(myvar3)); end; / -- -------------------------------------------------------- create or replace procedure no_op (p_var1 in number, p_var2 out number, p_var3 out number) is begin p_var2:=2 * p_var1; p_var3:=3 * p_var1; end; / declare myvar2 number; myvar3 number; begin no_op(5, myvar2, myvar3); dbms_output.put_line(TO_CHAR(myvar2)); dbms_output.put_line(TO_CHAR(myvar3)); end; / Example: -------- create or replace procedure test6 (p_var1 in number) is p_var2 number; begin p_var2:=10 * p_var1; dbms_output.put_line(TO_CHAR(p_var2)); end; / This you can execute right away: SQL> exec test6(4); 40 PL/SQL procedure successfully completed. Example: -------- create or replace procedure test8 is p_var2 number; begin p_var2:=area_of_circle(10); dbms_output.put_line(TO_CHAR(p_var2)); end; / Example: -------- create or replace procedure ins_sales_parm(id in number, name in varchar2) as begin insert into sales values (id,name); end; / Procedure is aangemaakt. SQL> exec ins_sales_parm(5,'piet'); Example: -------- FUNCTION clearImportTable RETURN boolean IS v boolean; BEGIN BEGIN DELETE FROM EMP; EXCEPTION WHEN OTHERS THEN v:=false; return v; END; v:=true; return v; END clearImportTable; FUNCTION clearImportTable RETURN boolean IS BEGIN DELETE FROM EMP; EXCEPTION WHEN OTHERS THEN return false; END; return true; END clearImportTable; 6.2 IF THEN ELSE: ================= Example 1: ---------- IF caller_type = 'VIP' THEN generate_response ('EXPRESS'); ELSIF caller_type = 'BILL_COLLECTOR' THEN generate_response ('THROUGH_CHICAGO'); ELSIF caller_type = 'INTERNATIONAL' THEN generate_response ('AIR'); ELSE generate_response ('NORMAL'); END IF; Example 3: ---------- IF THEN IF THEN ELSE IF THEN ELSIF THEN END IF; END IF; END IF; Here inside checking will not be done if the outer test is not true. Example 4: ---------- set serveroutput on begin if TO_CHAR(SYSDATE, 'DAY')='WEDNESDAY' then dbms_output.put_line('Today is wednesday'); else if TO_CHAR(SYSDATE, 'DAY')='FRIDAY' then dbms_output.put_line('Today is friday'); else if TO_CHAR(SYSDATE, 'DAY')='MONDAY' then dbms_output.put_line('Today is saturday'); end if; end if; end if; end; Example 5: ---------- begin for mynum in 0..4 loop if mynum=1 then my_team(mynum):='SMITH'; elsif mynum=2 then my_team(mynum):='JONES'; elsif mynum=3 then my_team(mynum):='TURNER'; elsif mynum=4 then my_team(mynum):='KING'; end if; end loop; Also inserts, updates, deletes to tables are possible DECLARE TEMP_COST NUMBER(10,2); BEGIN SELECT COST FROM JD11.BOOK INTO TEMP_COST WHERE ISBN = 21; IF TEMP_COST > 0 THEN UPDATE JD11.BOOK SET COST = (TEMP_COST*1.175) WHERE ISBN = 21; ELSE UPDATE JD11.BOOK SET COST = 21.32 WHERE ISBN = 21; END IF; COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, 'ISBN 21 NOT FOUND'); END; 6.3 LOOPS: ========== loop statements end loop; 6.3.1 loop - if condition then exit ----------------------------------- create or replace procedure test_1 AS x number:=5; begin loop dbms_output.put_line('Ik heb dit '||TO_CHAR(x)||' gedaan.'); x:=x-1; if x=0 then exit; end if; end loop; end; / create or replace procedure test2(x in number) AS y number; begin y:=x; loop dbms_output.put_line('Ik heb dit '||TO_CHAR(x)||' gedaan.'); y:=y-1; if y=0 then exit; end if; end loop; end; / 6.3.2: loop - exit when condition --------------------------------- declare x number:=5; begin loop dbms_output.put_line('Ik heb dit '||TO_CHAR(x)||' gedaan.'); x:=x-1; exit when x=0; end loop; end; / 6.3.3: while condition loop --------------------------- declare x number:=5; begin while x>0 loop dbms_output.put_line('Ik heb dit '||TO_CHAR(x)||' gedaan.'); x:=x-1; end loop; end; / declare i number := 1000000; begin while i>1 loop insert INTO customers values (1, 'joop'); i := i - 1; commit; end loop; commit; end; / 6.3.4: for condition loop ------------------------- declare -- nothing to declare begin for x in 0..4 loop dbms_output.put_line('Ik heb dit '||TO_CHAR(x)||' gedaan.'); end loop; end; / -- Note that you do not need to declare x NESTED LOOP: create or replace procedure loop4 is begin for x in 0..3 loop for y in 0..2 loop dbms_output.put_line(TO_CHAR(x)||','||TO_CHAR(y)); end loop; end loop; end; / 6.3.5: Goto statements ---------------------- Statements.. if condition then goto label statements.. <