advanced sql Functions in Oracle 10G phần 8

Để quay trở lại các lập luận "thêm" chúng tôi thảo luận trước đó, chúng tôi ghi nhận rằng các tham số đối số tùy chọn cho phép sử dụng đặc biệt của các siêu ký tự thời gian. Hãy nghiên cứu kỹ hơn nữa vào việc sử dụng những lập luận. Giả sử chúng ta đã có một bảng gọi là Test_clob | Collection and OO SQL in Oracle Which gives EMPNO NAME ADDRESS STREET CITY STATE ZIP 101 Adam ADDRESS_OBJ 1 A St. Mobile AL 36608 102 Baker ADDRESS_OBJ 2 B St. Pensacola FL 32504 103 Charles ADDRESS_OBJ 3 C St. Bradenton FL 34209 Addressing specific columns works as well. Specific columns including the composite are addressed by their name in the result set SELECT empno name address -- you can use discrete attribute -- names FROM emp Gives EMPNO NAME ADDRESS STREET CITY STATE IP 101 Adam ADDRESS_OBJ 1 A St. Mobile AL 36608 102 Baker ADDRESS_OBJ 2 B St. Pensacola FL 32504 103 Charles ADDRESS_OBJ 3 C St. Bradenton FL 34209 276 Chapter I COLUMN Formatting in SELECT Since the above output looks sloppy some column formatting is in order SQL COLUMN name FORMAT a9 SQL COLUMN empno FORMAT 999999 SQL COLUMN address FORMAT a50 SQL Now the above query would give EMPNO NAME ADDRESS STREET CITY STATE ZIP 101 Adam ADDRESS_OBJ 1 A St. Mobile AL 36608 102 Baker ADDRESS_OBJ 2 B St. Pensacola FL 32504 103 Charles ADDRESS_OBJ 3 C St. Bradenton FL 34209 Note that here we formatted the entire address field and not the individual attributes of the column objects. SELECTing Only One Column in the Composite Fields within the column object may be addressed individually. A query that recalls names and cities in our example might look like this SELECT name FROM emp e Giving NAME Adam Mobile Baker Pensacola Charles Bradenton 277 Collection and OO SQL in Oracle You must use a table alias and the qualifier ADDRESS with the alias. If the alias is not used the query will fail with a syntax error. SELECT with a WHERE Clause In a WHERE clause alias and qualifier are also used SELECT name FROM emp e WHERE FL Gives NAME Baker Pensacola Charles Bradenton Using UPDATE with TYPEed Columns To use UPDATE the alias must also be used UPDATE emp SET 34210 WHERE like Brad Gives UPDATE emp set 34210 .