Talk:Oracle Database/SQL Cheatsheet

Date
i have a table Table1 i have entered the Date field as Dat now i want to change it to Date how can i do that as alter and modify commands are not helping? -- (Anon)


 * It is not a good idea to call any field, Date, since DATE is an Oracle reserved word and dealing with that will complicate your life unnecessarily. However if you insist on calling it, Date, you can do so. Whenever you use the name of the field you will have to enclose it in quotes like this -- "Date" -- so that Oracle knows that you don't mean DATE, you really mean Date. Is it really worth the hassle when you could have avoided it by choosing a different name for the field? -- Derek Ross 23:21, 21 September 2006 (UTC)

On another issue, I notice that the article defines functions and procedures using IS. My experience is that this never works: you have to use AS instead. Comments ? Am I doing something wrong or does the article need fixing? -- Derek Ross 23:21, 21 September 2006 (UTC)


 * You are probably thinking of a "view" instead of a "procedure", which works like the following:

CREATE OR REPLACE VIEW employee_view (EMPLOYEE_ID,NAME,EMAIL) AS SELECT employee_id, name, email FROM employee /


 * I've been using PL/SQL procedures for a couple years and am certain that the use of IS is correct.
 * -- Adreamsoul 21:37, 25 September 2006 (UTC)

Parameters
Need some discussion of IN/OUT/IN OUT - is it copy in/copy out, or call by address (for OUT and IN/OUT), or even Algol60 style call-by-name which requires thunks!

Is an OUT parameter always overwritten on return to the caller even if not assigned within the procedure? What happens when you read from an OUT parameter before you first write to it in a procedure? -- 

In/Out are designates that a parameter is or is not able to be modified within a procedure. Example:

In the CALLING procedure (assuming variables declared to match, wherever they need declaring)

... code snippet MyVar1 := 'Jane Doe'; -- possible employee name MyVar2 := 0; -- age in years of employee MyVar3 := '234598' ; -- an employee id Some_Proc_Name (MyVar1, MyVar3, MyVar2); ... end of snippet

MyVar1 may now still be "Jane Doe" (if that is employee #234598's name). MyVar2 now has Jane's current age.

In the CALLED procedure can be found stuff like ...

(CREATE OR REPLACE) Procedure Some_Proc_Name (Param1 in out ParamType, -- here this is Varchar2                          Param2 in ParamType,      -- either Varchar2 or integer                          Param3 in out nocopy ParamType) -- this is number -- nocopy tells compiler to use same external memory location for this variable -- no need to take more memory for a second copy within the procedure IS ... Variable declarations var_date_Birth Date; -- because Params1 and 3 are "in out" they can be treated as variables WITHIN the procedure -- and there is no need for additional variables.

BEGIN -- start of whole procedure Begin -- isolated trap for retrieval of data select first_name||' '||last_name, DOB into Param1, var_date_Birth from Employees where EmpID = Param2 exception when NO_DATA_FOUND then Param1 := 'Emp no longer exists.'; -- age still zero when others then Param1 := SQLERRM; -- passes back info to calling proc end; if var_date_Birth is not null then -- calculate age Param3 := CASE Sysdate > var_date_Birth then (Sysdate - var_date_birth) ELSE 0 END; -- just in case someone has entered a bad date. -- you can fine tune the date calc to just be years, but I can't remember how just now. end if; END Some_Proc_name;

-- 

DDL
This is the best collection of simplified SQL syntax, that I’ve come across. I would move sections 1.5.1 Setting Constraints on a Table, 1.5.2 Unique Index on a Table, and 1.6 Sequences to the DDL section. Wkeen Wkeen (discuss • contribs) 17:24, 23 May 2011 (UTC)