001: package net.sourceforge.squirrel_sql.fw.sql;
002:
003: /*
004: * Copyright (C) 2007 Rob Manning
005: * manningr@users.sourceforge.net
006: *
007: * This library is free software; you can redistribute it and/or
008: * modify it under the terms of the GNU Lesser General Public
009: * License as published by the Free Software Foundation; either
010: * version 2.1 of the License, or (at your option) any later version.
011: *
012: * This library is distributed in the hope that it will be useful,
013: * but WITHOUT ANY WARRANTY; without even the implied warranty of
014: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
015: * Lesser General Public License for more details.
016: *
017: * You should have received a copy of the GNU Lesser General Public
018: * License along with this library; if not, write to the Free Software
019: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
020: */
021: public interface OracleSQL {
022:
023: public final static String SELECT_DUAL = "select 'X' from dual;";
024:
025: public final static String SELECT_DUAL_2 = "select 1/100 from dual;";
026:
027: public final static String CREATE_STORED_PROC = " create procedure fooproc (Person_name IN varchar2) \n"
028: + "AS \n"
029: + "BEGIN \n"
030: + " insert into testdate \n"
031: + " (mydate) \n"
032: + " values \n"
033: + " (sysdate); \n"
034: + "END; \n"
035: + " \n"
036: + "/ \n\n;";
037:
038: public final static String CREATE_OR_REPLACE_STORED_PROC = "create or replace procedure fooproc (Person_name IN varchar2) \n"
039: + "AS \n"
040: + "BEGIN \n"
041: + " insert into testdate \n"
042: + " (mydate) \n"
043: + " values \n"
044: + " (sysdate); \n"
045: + "END; \n"
046: + " \n"
047: + "/ \n\n;";
048:
049: public final static String ANON_PROC_EXEC = "declare \n"
050: + "v_foo number(10); \n"
051: + "v_bar number(10); \n"
052: + "begin \n"
053: + " for usr in \n"
054: + " (select * from foo_bar where user like 'TST%') \n"
055: + " loop \n"
056: + " begin \n"
057: + " update STUDENT set AGE = 13 where SNO = 100000; \n"
058: + " end; \n" + " end loop; \n" + "end; \n"
059: + "/ \n\n";
060:
061: public final static String ANON_PROC_EXEC_2 = "begin "
062: + " for usr in "
063: + " (select mychar from test where mychar like 'TST%') "
064: + " loop " + " begin "
065: + " update test set mychar = 'foo'; "
066: + " end; " + " end loop; " + "end; \n" + "/ \n\n";
067:
068: public final static String CREATE_OR_REPLACE_PACKAGE_SQL = "CREATE OR REPLACE PACKAGE tmk IS \n"
069: + " PROCEDURE test; \n" + "END tmk; \n" + "/ \n";
070:
071: public final static String CREATE_OR_REPLACE_PACKAGE_BODY_SQL = "CREATE OR REPLACE PACKAGE BODY tmk IS \n"
072: + " PROCEDURE test IS \n"
073: + " rec_tmp atmk%ROWTYPE; \n"
074: + " BEGIN \n"
075: + " SELECT * INTO rec_tmp FROM atmk WHERE ROWNUM=1; \n"
076: + " Dbms_Output.Put_Line(rec_tmp.table_name || ',' || "
077: + " rec_tmp.tablespace_name || ',' || rec_tmp.cluster_name || '.'); "
078: + " END test; \n" + "END tmk; \n" + "/ \n";
079:
080: public final static String UPDATE_TEST = "update test "
081: + "set /*PARAM1*/ thing /*C*/ = 'default value' /*/PARAM1*/;";
082:
083: public final static String STUDENTS_NOT_TAKING_CS112 = "select s.sno, s.sname, s.age "
084: + "from student s, take t "
085: + "where s.SNO = t.SNO (+) "
086: + "group by s.sno, s.SNAME, s.AGE "
087: + "having max(case when t.cno = 'CS112' "
088: + " then 1 else 0 end) = 0; ";
089:
090: public final static String NO_SEP_SLASH_SQL = "/*==============================================================*/\n"
091: + "/* Database name: FOOB */\n"
092: + "/* DBMS name: ORACLE Version 8i2 (8.1.6) */\n"
093: + "/* Created on: 4/1/2005 2:51:03 PM */\n"
094: + "/*==============================================================*/\n"
095: + "\n"
096: + "\n"
097: + "\n"
098: + "\n"
099: + "/*==============================================================*/\n"
100: + "/* Table : FOOB_CAB */\n"
101: + "/*==============================================================*/\n"
102: + "\n"
103: + "\n"
104: + "create table FOOB_CAB (\n"
105: + " CABID INTEGER not null,\n"
106: + " Name VARCHAR2(100) not null,\n"
107: + " Description CLOB,\n"
108: + " constraint PK_FOOB_CAB primary key (ALCID)\n"
109: + ")\n"
110: + "/\n"
111: + "\n"
112: + "\n"
113: + "/*==============================================================*/\n"
114: + "/* Table : FOOB_Add12EmakeTypo */\n"
115: + "/*==============================================================*/\n"
116: + "\n"
117: + "\n"
118: + "create table FOOB_Add12EmakeTypo (\n"
119: + " Add12EmakeTypo VARCHAR2(35) not null,\n"
120: + " Description VARCHAR2(255),\n"
121: + " constraint PK_FOOB_Add12EmakeTypo primary key (Add12EmakeTypo)\n"
122: + ")\n" + "/\n";
123:
124: public final static String CREATE_FUNCTION_SQL = "create or replace function airport_city(iata_code in char) "
125: + "return varchar2 "
126: + "is "
127: + " city_name varchar2(50); "
128: + "begin "
129: + " select city "
130: + " into city_name "
131: + " from iata_airport_codes "
132: + " where code = iata_code "
133: + " return (city_name); " + "end; " + "/ ";
134: }
|