You normally use the terms in a trigger using :old to reference the old value and :new to reference the new value. Here is an example from the Oracle documentation linked to above CREATE OR REPLACE TRIGGER Print_salary_changes BEFORE DELETE OR INSERT OR UPDATE ON Emp_tab FOR EACH ROW WHEN (new.Empno > 0) DECLARE sal_diff … Read more
Views in Oracle may be updateable under specific conditions. It can be tricky, and usually is not advisable. From the Oracle 10g SQL Reference: Notes on Updatable Views An updatable view is one you can use to insert, update, or delete base table rows. You can create a view to be inherently updatable, or you … Read more
Use sysdate-1 to subtract one day from system date. select sysdate, sysdate -1 from dual; Output: SYSDATE SYSDATE-1 ——– ——— 22-10-13 21-10-13
Assuming that you want to group the data before you generate the key with the sequence, it sounds like you want something like INSERT INTO HISTORICAL_CAR_STATS ( HISTORICAL_CAR_STATS_ID, YEAR, MONTH, MAKE, MODEL, REGION, AVG_MSRP, CNT) SELECT MY_SEQ.nextval, year, month, make, model, region, avg_msrp, cnt FROM (SELECT ‘2010’ year, ’12’ month, ‘ALL’ make, ‘ALL’ model, REGION, … Read more
See the official documentation (http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm#i54330) Variable-length character string having maximum length size bytes or characters. Maximum size is 4000 bytes or characters, and minimum is 1 byte or 1 character. You must specify size for VARCHAR2. BYTE indicates that the column will have byte length semantics; CHAR indicates that the column will have character semantics. … Read more
An easy way: select extract(day from (ts1-ts2)*86400) from dual; The idea is to convert the interval value into days by times 86400 (= 24*60*60). Then extract the ‘day’ value which is actually second value we wanted.
if var is NULL then var :=5; end if;
While MySQL does have similar components, no, you cannot use PL\SQL in MySQL. The same goes for T-SQL used by MS SQL Server. MySQL has plenty of documentation on it at their website. You’ll see that both PL\SQL and T-SQL are Turing-complete, and probably provide slightly more functionality. But MySQL has plenty of ways to … Read more
Your query should be: SELECT T1.DATE1, T1.DATE2, T1.DATE3 INTO V_DATE1, V_DATE2, V_DATE3 FROM T1 WHERE ID=’X’;
While I agree that 99% of the time it is bad practice to silently ignore exceptions without at least logging them somewhere, there are specific situations where this is perfectly acceptable. In these situations, NULL is your friend: […] EXCEPTION WHEN OTHERS THEN NULL; END; Two typical situations where ignoring exceptions might be desirable are: … Read more