CoderZone.org

Category: >> Oracle PL/SQL >> FUNCTIONS: without parameters Bookmark and Share

<< lastnext >>

Snippet Name: FUNCTIONS: without parameters

Description: Not all functions take parameters; some produce output without evaluating anything passed in. This is a basic example of a function that does not use parameters.

Also see:
» FUNCTIONS: Deterministic
» FUNCTIONS: Nested Functions
» FUNCTIONS: IF statement
» FUNCTIONS: date/time
» FUNCTIONS: Sample functions
» FUNCTIONS: drop
» FUNCTIONS: Recompile
» FUNCTIONS: DEBUG mode
» FUNCTIONS: IN OUT parameter
» FUNCTIONS: with output parameters
» FUNCTIONS: with parameters
» FUNCTIONS: Create function
» FUNCTIONS: special restrictions
» FUNCTIONS: System Privileges
» IN Function
» Built-In Functions: CASE
» Built-In Functions: DECODE
» SUBST and INSTR together
» INSTR (InString)
» SUBSTR (SubString)
» String Functions: REVERSE
» String Functions: LENGTH
» String Functions: INSTR
» String Functions: CONCAT
» String Functions: CHAR
» String Functions: INITCAP
» String Functions: LOWER
» String Functions: UPPER
» Date Functions: NUMTOYMINTERVAL
» Date Functions: NUMTODSINTERVAL

Comment: (none)

Author: CoderZone
Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 13th, 2009

CREATE OR REPLACE FUNCTION getuser 
RETURN user_users.username%TYPE IS
-- explain use of %TYPE
vOSUser user_users.username%TYPE;
-- explain INTO and return
BEGIN
  SELECT osuser
  INTO vUser
  FROM gv$session
  WHERE sid = (
    SELECT sid
    FROM gv$mystat
    WHERE ROWNUM = 1);
 
  RETURN vuser; 
EXCEPTION
  WHEN OTHERS THEN
    RETURN 'UNK'; 
END getuser;
/
 
-- test getuser function
SELECT getuser FROM dual;
 
Simple FUNCTION Used IN An INSERT Statement 
 
CREATE TABLE my_table (
col_values   VARCHAR2(10),
insert_by    VARCHAR2(30));
 
INSERT INTO my_stuff VALUES ('ABC', getosuser);
INSERT INTO my_stuff VALUES ('ABCD', 'Harold Stassen');
INSERT INTO my_stuff VALUES ('ABCDE', getosuser);
INSERT INTO my_stuff VALUES ('ABCDEF', 'Mary Lamb');
INSERT INTO my_stuff VALUES ('AB', getosuser);
COMMIT;
 
SELECT * FROM my_table; 
 
There haven't been any comments added for this snippet yet. You may add one if you like.  Add a comment 
© coderzone.org | users online: 9