Scribd
Upload
19 views2 pages

SQL Script for User Management and Pricing

The document contains SQL statements and functions to create schemas, tables, and functions. It creates a schema called "user" and table called "PRODUCT1" with columns PRODUCT_ID, PRODUCT_NAME, and PRICE. It defines two functions called add_surcharge and apply_discount to perform calculations on decimal values and returns the result. It also contains SELECT statements to view effective privileges and roles for a user and find tables starting with "/BIC/A00".

Uploaded by

mandamadhukar
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
19 views2 pages

SQL Script for User Management and Pricing

The document contains SQL statements and functions to create schemas, tables, and functions. It creates a schema called "user" and table called "PRODUCT1" with columns PRODUCT_ID, PRODUCT_NAME, and PRICE. It defines two functions called add_surcharge and apply_discount to perform calculations on decimal values and returns the result. It also contains SELECT statements to view effective privileges and roles for a user and find tables starting with "/BIC/A00".

Uploaded by

mandamadhukar
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

Create schema "user" owned by <username>;

Create schema "user";


Grant select on schema

-------------------------------------update PRODUCT1 set PRICE = 80 where PRODUCT_ID = 4;


update PRODUCT1 set PRICE = 80 where PRODUCT_ID = 5;
update PRODUCT1 set PRICE = 90 where PRODUCT_ID = 6;
--------------------------------------insert into "U_MMANDA"."PRODUCT1" values(,'',)
-----------------------------------CREATE COLUMN TABLE "U_MMANDA"."PRODUCT1" ("PRODUCT_ID" INTEGER CS_INT,
"PRODUCT_NAME" VARCHAR(100),
"PRICE" DOUBLE CS_DOUBLE,
PRIMARY KEY ("PRODUCT_ID"))
--------------------------------------------Functions:
___________
CREATE FUNCTION add_surcharge(im_var1 decimal(15,2), im_var2 decimal(15,2))
RETURNS result decimal(15,2)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN
result := :im_var1 + :im_var2;
END;
Create function apply_discount(im_var1 decimal(15,2), im_var2 decimal(15,2))
RETURNS result decimal(15,2)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN
result := :im_var1 - (:im_var1 * :im_var2);
end;
------------------------------------------------------[Link]
b1290f/[Link]
[Link]

7700909/[Link]
----------------------------------SELECT * FROM "PUBLIC"."EFFECTIVE_PRIVILEGES" where USER_NAME = 'anyUserName'
SELECT * FROM "PUBLIC"."EFFECTIVE_ROLES" where USER_NAME = 'anyUserName'
---------------------------------------------------------------select * from M_CS_TABLES where TABLE_NAME like '/BIC/A%00'

You might also like